MySQLのインデックスを学ぶ (1)

実践ハイパフォーマンスMySQL 第2版とLinux-DBシステム構築運用入門を読んで、 MySQL のインデックスについて勉強しなおしている。理解が曖昧だった部分の知識を深められたり、自分の間違いに気づけたりして、とても収穫が多い。

フルテーブルスキャンとフルインデックススキャン

Linux-DBシステム構築運用入門 P185 に書いてあるケース。インデックスを利用してても対象レコード数が多いとランダムI/Oが大量に発生して遅くなる。読むべきレコード数が多いのならばフルテーブルスキャンのほうがI/O一回で多くのブロックを読み込めるので速い。 IGNORE INDEX ヒントを与えてパフォーマンスを改善するという例があった。

マルチカラムインデックスと範囲検索

SELECT * FROM users WHERE a = ? AND b >= ? and (c IS NULL OR c >= ?) ORDER BY b DESC LIMIT 10;

とかいうクエリの場合に、 (a, b, c) というマルチカラムインデックスをはれば WHERE 句 + ORDER BY 句すべてを満たせると思ってたけど間違ってた。 b が範囲検索なのでマルチカラムインデックスのうち最後の c の部分は利用できない。この場合、検索のことだけを考えるならば (a, b) というインデックスのほうが容量を食わずに同じことができるのでより良いといえそう。でもカバリングインデックスと組み合わせるならば (a, b, c) というインデックスに分がある。

カバリングインデックス

カバリングインデックスは名前だけぼんやりと知ってて内容をちゃんと理解できていなかった。クエリが必要とするカラムがすべてインデックスに含まれている場合、インデックスだけを読めば良いのでとても速い、というもの。特に InnoDB ではかなり効果がありそう。 MySQL ならば EXPLAIN したとき Extra: の項に Using index と出るとインデックススキャンのみで済んでいることになるのでこれを目印にするとよい。 type: index とは全く意味が違うので注意。

カバリングインデックスを利用できない例 (1)

SELECT * from t WHERE a = ?;

こういうクエリは (a) というインデックスがはってあったとしても結果セットを返すためにレコードのすべてのカラムが必要なので、 WHERE 句を満たすレコードの実データをすべてランダムI/Oで読むことになる。

カバリングインデックスを利用できる例 (1)

SELECT id FROM t WHERE a = ?;

上のクエリを改良して、取得するカラムを絞ったケース。id は PRIMARY KEY とする。 InnoDB はクラスタインデックスなので PRIMARY KEY で引けば実データも同時に手に入りランダムI/Oが発生しないので PRIMARY KEY で引くクエリを多く発行できたほうが有利だ。そしてセカンダリインデックスには常に PRIMARY KEY の値も含まれるので、 (a) というインデックスがはってあると a と id の値がインデックスに含まれるのでこのクエリはインデックススキャンのみで完結する。これをカバリングインデックスという。

カバリングインデックスを利用できない例 (2)

SELECT id FROM t WHERE a = ? AND b = ?;

上のクエリの WHERE 句が増えたケース。このクエリはカバリングインデックスを利用できない。 (a) というインデックスに b カラムは含まれていないので、まず (a) インデックスで絞り込んで、次にマッチしたレコードの実データをランダムI/Oですべて読み込み、それぞれのレコードの b カラムの値を比較し、マッチしたレコードの id を返す、ということになる。せっかく実データを読んだのに id 以外のカラムの値を捨ててしまっているもったいないクエリ。この場合は (a, b) というインデックスをはればよい。

カバリングインデックスを利用できない例 (3)

SELECT id FROM t WHERE a = ? AND b = ? ORDER BY c DESC LIMIT 10;

上の例に ORDER BY 句がついたケース。 (a, b) というインデックスがはってあっても、 ORDER BY 句で指定している c のカラムはこのインデックスに含まれていないので結局実データをランダムI/Oで読むことになる。インデックスを使ってレコードを絞り込んだあと、実データを読み込んで、 c の値でソートする。おそらく Extra: には Using filesort; が出ると思う。このクエリは (a, b, c) というマルチカラムインデックスをはるとカバリングインデックスを利用できるので改善が期待できるはず。

カバリングインデックスを利用できる例 (2)

SELECT id FROM t WHERE a = ? AND b >= ? ORDER BY c DESC LIMIT 10;

WHERE 句の条件に範囲検索が含まれるケース。この場合、 (a, b, c) というマルチカラムインデックスをはっても b が範囲検索なのでインデックスは a, b までしか利用されず、 c でのソートにはインデックスが使えないので (a, b) というインデックスをはれば十分、と考えたくなるが、そうしてしまうと一つ上の例のように c の値を実データから取得しなければならないのでランダムI/Oが発生してしまう。この場合も (a, b, c) というインデックスをはっておくのが正解で、 EXPLAIN による実行計画には差が出なくてもカバリングインデックスによりI/Oが減らせる。

実践ハイパフォーマンスMySQL 第2版

Linux-DB システム構築/運用入門 (DB Magazine SELECTION)