DBチューニングできますか？(3)

MySQL

概要

前回インデックスを使うとよく使うブロックをキャッシュするから高速になりますよーという内容の記事を書いたのですが、
その前にシーケンシャルアクセスとランダムアクセスの違いを書いてないのに気づいたので順番前後しますがその話です。

シーケンシャルアクセスとランダムアクセスとカセットテープとCD

私がまだ小学生の頃は音楽をカセットテーブで聞いていました。書き込みを禁止する場合はツメを折る、アレです。
自分の聞きたい曲を探すときには早送りボタンを押したままボーとしていたものです。

そのうちCDというものが発売され、好きな曲を瞬時に再生できる、というとんでもなく便利なものが発売されるようになりました。
とても懐かしい話です。この時、2000曲以上を持ち歩く生活を、一体誰が想像できたでしょうか。

CDで好きな曲を瞬時に選択できるは、どこから読めばいいのかを知っているからです。つまりデータを読み取る住所が存在していて、
CDプレイヤーは曲がマッピングされた情報が書かれている場所を調べ、そこから目的の曲がどこから始まっているのかを調べれば良いわけです。

ストリームの向こう側

ここでストリームの概念についておさらいします。以下のコマンドは何をしているのか。このブログをご覧になっているのは技術者ばかりなのでだいたいわかると思います。

% cat hoge.txt

このとき標準出力にはバイト列が送り込まれてきますが、このバイト列はどこからやってくるのでしょう？ちょっとそこを考えてみます。

セクタとブロック

HDD側では読み取り、書き取りする最小単位をセクタと呼び、たいてい512バイトだったりします。

ファイルの中身はこのセクタに保存されます。このセクタがHDD上にたくさんあるので空いているセクタを使ってデータを記憶します。
ただし、私たちはデータの読み書きはファイルシステムを通じて行います。
これはいくつかのセクタをまとめて一つのブロックとして使用します。たいてい4096バイトです。

私のMacだとこんな感じで作成したファイルのst_blksize(ファイルシステム I/O 操作での最適なブロックサイズは4096Byteです。
試しに空のファイルを作成してみます。st_blksize=4096となっています。

% touch hoge.txt                                                         
% stat -s hoge.txt                                                  
st_dev=234881026 st_ino=27489840 st_mode=0100644 st_nlink=1 st_uid=501 st_gid=20 st_rdev=0 st_size=0 st_atime=1311758217 st_mtime=1311758217 st_ctime=1311758217 st_birthtime=1311758217 st_blksize=4096 st_blocks=0 st_flags=0

helloという文字を保存してみます。

% stat -s hoge.txt                                                        
st_dev=234881026 st_ino=27489915 st_mode=0100644 st_nlink=1 st_uid=501 st_gid=20 st_rdev=0 st_size=6 st_atime=1311758459 st_mtime=1311758458 st_ctime=1311758458 st_birthtime=1311758449 st_blksize=4096 st_blocks=8 st_flags=0

st_blocks=8 となっていて複数のセクタが使用されてる事が確認できます。

ランダムアクセスでHDDが目的のセクタにたどり着くには

だいたい次のような公式で表す事ができます。

平均シークタイム　＋　平均回転待ち時間　＋　データ転送時間
  • ランダムアクセスでは目的のセクタがどこに保存されているか、位置を知っているのでそこに向かって磁気ヘッドが移動します。
  • 移動が完了したら、磁気ヘッドが待っている場所にセクタがたどり着くまで待ちます
  • 読み取ったデータを転送します。

で、ファイルシステムはそのセクタをさらにブロック単位で扱います。

シーケンシャルアクセスのほうが速い場合もある

実はシークタイムがHDDからデータを読み取る際にコストがかかっています。場合によってはシーケンシャルアクセスのほうが速い事もあります。
次のようなSQLだとランダムアクセスを10000回繰り返すよりもシーケンシャルアクセスのほうが速い可能性があるのでMySQLサーバーのオプディマイザがフルテーブルスキャンを選択する可能性が高いです。

select id, name from user where id < 10000

まとめ

ということで駆け足で大分省略している箇所がありますがインデックスを作成していないテーブルをフルスキャンする場合と、
目的のデータがどこに保存されているかを調べてからランダムアクセスする場合との違いをまとめてみました。

で、インデックスの場合はルートノードやブランチノードはキャッシュの対象になりやすいのでインデックスから目的のデータブロックの位置を知る際にもディスクI/Oをしなくてすむ可能性が高いです。

というわけで今度からは「SQLを書くときは、インデックスを有効活用する事によりシーケンシャルではなくランダムアクセスにより効率よくデータブロックへのアクセスができるように意識して書くように心がけています(キリッ)」と答えてみたいと思います。