MySQLの複合INDEX

MySQLの複合インデックスについて。

MySQLを使いこなしている人には常識の範疇にはいる知識だと思うのですが、かつての私のように、知らないとドツボにはまるので、ここにまとめておきます。

タイトルでどういう内容か、もしかしたら大体、想像がつくかなと思うのですが、

MySQLは1つのクエリーで1つのテーブルに対し、1つのインデックスしか機能しない

ということです。

ようするに、あるテーブルに対して、普通に大量のインデックスを張ってもあまり意味がありません。

そこで、今回は、最適な複合インデックスはどのようにしてはることができるか考えていこうじゃないか、という趣旨で書いていきたいと思います。

まずは分析。テスト環境でインデックスが使われていないSQLを集める

まずは、/etc/my.cnfのパラメータを以下のように設定し、インデックスが使われていないSQLをスロークエリーに吐き出します。

slow_query_log = 1
long_query_time = 5
log_queries_not_using_indexes = 1

また、上記の場合、5秒以上かかったSQLも一緒に記録しています。本来であれば1秒以上かかるSQLはすべて収集すべきなのですが、ここではとりあえずインデックスが使われていないSQLを取得したいので、上記のようにしています。（5秒以上、というのはそうとうまずいSQLだと思いますが、、）

また、MySQL5.1以降の場合、下記のようにlong_query_time=0とすれば、すべてのSQLが記録されます。

slow_query_log = 1
long_query_time = 0

時間があるのであれば、上記のように、いったん、すべてのSQLを出力して精査してみてもよいかなと思います。（特に直にSQLなどを書かずにO/Rマッパーを使っている場合）

SQLを見てインデックスを張っていく

実際に発行されているSQLを見て、それぞれのSQLに最適な複合INDEXを考えます。まずは、以下を念頭に置いてください。

いくつかのSQLに対して、どのようなインデックスが最適なのかを論じていきますが、基本的にはEXPLAINコマンドで確認したほうがいいことは言うまでもありません。
インデックスが増えれば検索速度も向上しますが、同時にINSERT/UPDATEが遅くなります。むやみやたらとインデックスを張ればいいというものではなく、この辺はバランスの問題となります。
SQLのWHERE句の順序などを変えることで、インデックスの効率が変わります。SQLを見ながら最適な複合インデックスを考える際に、合わせてSQLを少し変えることで最適化できないか考えてみましょう。
MySQLの場合、インデックスは昇順・降順 (ASC/DESC) を指定することができますが、実際にはこの指定は無視されて常に昇順 (ASC) になります。将来の拡張に備えてDDLのファイルなどにはDESCと書いてもいいかも知れませんが、まぁ、とにかく無視されます。

以下、とりあえず基本となる考え方について。

１．まずは普通のSQL

以下のSQLを見てください。

ユーザーのメールアドレスによるログインを想定してみましたが、いたって普通のSQLだと思います。

で、MySQLの場合、一つのSQLに一つのインデックスしか使われませんので、これの場合、email,passwd,statusに個別にインデックスを張ると、emailのみしか使われません。

そこで、上記のようにemail,passwd,statusの順に複合インデックスを張ることによって、上記のSQLに最適なインデックスが張られます。

２．ORDER BY を含むSQLのインデックス

単純なSQLでも、複数のレコードを取得する場合、WHERE句に加えてORDER BY を指定することが多くなるかと思います。

そこで、WHERE句で条件を指定し、ORDER BYで順序を指定するSQLの場合、以下のようにORDER BYのカラムも複合インデックスに含めます。

インデックスの指定をする際に、ASC/DESCの指定ができます。実際には無視されますが・・・

３．降順(DESC)のインデックスだけどfilesortを出したくないとき

上記のSQL、実際に数万件以上のデータで試してみればわかりますが、EXPLAINで分析すると、using filesortがでます。

これは実は簡単な話で、実際にはインデックスにDESCを指定しても、無視してASCでインデックスを作成しているからです。本来であれば、並べ替えにもインデックスが使われるはずが、インデックスの並び順がASCなので、各レコードのデータを読み込んでソートします。この時に、一時ファイルを使ってソートを行っているのですが、これがusing filesortの正体です。

つまり、using filesortが出ているということはソートにインデックスが使われていないことを意味します。

しかし、SNSにおける日記や伝言板、ミニメールなど、扱うデータが膨大で参照される頻度も高い場合には（できるかぎりテーブルの分割なども行うのですが）filesortはできれば避けたいところです。

そういう場合には、以下のようにします。（以下は一般的な日記を想定してみました）