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

概要

MySQLサーバを効率よく活用するにはインデックスについて知る必要があると思います。
今回はインデックスを使うとなぜSQLが高速になるのかを考えたいと思います。

ディスクI/Oの回数を減らす工夫

以前ファイルについての記事をちょっと書いたのですが
目的の情報を取得するためにHDDは目的のセクタをめざしてシークします。

この時、目的の情報だけでなく、そのセクタ毎まとめて情報を取得します。
まとめて情報を取得する事によって、一定時間その情報をメモリに保持しておいて同じセクタにシークする必要がないようにできるからです。

キャッシュされやすい仕組みはディスクI/Oを減らす事ができます。
そしてインデックス化されたテーブルはキャッシュされやすい構造になっています。

インデックスの構造

目的の情報を取得するために、概ね次の手順でディスクI/Oが発生します。
下の例はInnoDBでPrimaryKeyで検索したケースです。

• 最初にルートブロックへアクセス。ブランチブロックの場所を入手。
• 次にブランチブロックへアクセス。リーフブロックの場所を入手。
• リーフブロックへアクセス。このブロック内に必要な情報が入っているので丸ごと取り出す

ルートブロックへのアクセスは頻繁に行われるのでよほどひどい設定にしないかぎりキャッシュされるはずですし、ブランチブロックもキャッシュされる可能性が高いです。

クラスタインデックス

primary keyで検索した場合、リーフブロックにアクセスした段階で次のようなデータが格納されています。

	PK		列値
	1		cola='a1', colb='b1', ...
	2		cola='a2', colb='b2', ...
	3		cola='a3', colb='b3', ...

さて、次のようにPKを条件句に指定して検索をしたとします。

select * from table where pk = 1

この場合、必要な情報を入手する為に、次のような感じでルートを経由します。

• PKのルートブロックにアクセス
• PKのブランチブロックにアクセス
• PKのリーフブロックにアクセス

もし、ルートブロックとブランチブロックがメモリにキャッシュされていればディスクI/Oは1回で済みます。

セカンダリインデックス

セカンダリインデックスのリーフブロックは次のようにPKの値を格納しています。

	Key1		PK
	1		10
	2		5
	3		1

次のようにKey1を条件句に指定して検索をしたとします。

select * from table where key1 = 3

この場合、必要な情報を入手する為に、次のような感じでルートを経由します。

• key1のルートブロックにアクセス
• key1のブランチブロックにアクセス
• key1のリーフブロックにアクセス
• PKのルートブロックにアクセス
• PKのブランチブロックにアクセス
• PKのリーフブロックにアクセス

もし、key1,PKともにルートブロックとブランチブロックがメモリにキャッシュされていればディスクI/Oは2回で済みます。

まとめ

前回の記事では 「高速なSQLが書けますか？」という問いに対して適切なクエリを書けているか、explainによる検証によって客観的に妥当なクエリを書けるという返事をできるようになりました。

今回の記事では「それだと何故高速なのですか？」という問いに対しても返事ができるようになったと思います。
innodb_buffer_pool_sizeを最大80%に設定すればいいと言われている理由を聞かれてもこれでばっちりですね。たぶん。