少し昔の話ですが、『YAPC::Asia Tokyo 2014』のイベントに参加したとき、『Where狙いのキー、order by狙いのキー』という発表を聞きました。
当時の私はデータベースのインデックスについての知識が皆無だったので、発表を聞いた際は衝撃を受けたことを覚えてます。
www.slideshare.net
WHERE狙いのキー、ORDER BY狙いのキー
キーと書いてありますが、主にインデックスのことです。 「WHEREとORDER BYの、どちらに向けた高速化インデックスなのか？」 これを意識する必要があります。
- WHEREによるデータの絞り込みを高速化するためのインデックス
- ORDER BYによる並び替えの性能を向上するためのインデックス
MySQLにおけるインデックスの場合、まったく同一のテーブル定義であっても、実際に入っているデータの偏りによって有効なインデックスは異なります。 高速化したいSQLを、EXPLAINを付与するなどして実行計画を確認しつつ、本当に必要なインデックスを見定めます。
ちなみにソフトウェア工学における名言のひとつに、「推測するな、計測せよ」という名言があります。 インデックスは、最たる例のひとつかもしれません。
WHEREを狙うか、ORDER BYを狙うかの判断基準
『Where狙いのキー、order by狙いのキー』のスライドのまとめには、次の記述があります。
- WHERE句で十分絞り込める場合はWHERE狙い
- WHERE句がほとんど機能しないような場合はORDER BY狙い
WHEREのためのインデックス
たとえば少女マンガのイベント参加者を絞り込む場合、参加者の多くは女性であることが予想されます。
このケースで WHERE 性別=女性 のWHERE句を高速化しようとして性別にインデックスを張ったとしても、そもそも参加者の多くが女性なので役に立ちません。
反対に WHERE 性別=男性 のWHERE句であれば、少女マンガのイベント参加者に男性は少ないと予想されるので、インデックスによる絞り込みの恩恵を受ける可能性が高まります。
ORDER BYのためのインデックス
かるぱねるらさんのブログに、『ORDER BY狙いのキーが何故速いか』という記事がありました。
記事内で紹介されていたMySQLのリファレンスにも、『LIMIT + ORDER BY』のケースで、ORDER BYを使うととても早くなると書いてあります。
ORDER BY とともに LIMIT row_count を使用した場合、MySQL では、結果全体をソートするのではなく、ソートされた結果の最初の row_count 行が見つかるとすぐにソートを終了します。 インデックスを使用して順序付けが行われている場合、これはきわめて高速になります。 filesort を実行する必要がある場合、最初の row_count を見つける前に、LIMIT 句を使用しないクエリーに一致するすべての行が選択され、それらのほとんどまたはすべてがソートされます。 初期の行が見つかったら、MySQL は結果セットの残りをすべてソートしません。
https://dev.mysql.com/doc/refman/5.6/ja/limit-optimization.html
LIMITで指定された数の行だけ見つかったら、残りの結果セットのソートを省くことで高速化を成し遂げるという理屈です。 インデックスを使ってあらかじめ順序付けが行われていれば、対象レコードの発見が高速化されるので、超高速になります。
インデックスはむやみに張らない
インデックスはSQL次第ではありますが、多大な恩恵があります。 そのため一度インデックスのメリットを理解してしまうと、たくさんインデックスを貼りたくなってきます。 以前の私はそうだったのですが、これで先輩に怒られた記憶があります。
理由は単純で、インデックスには少なからずデメリットも存在するからです。
- データ量が多くないテーブルでは、そもそもインデックスの効果が低い
- インデックスを張ることによって、INSERTにかかる負担が増えてしまう
- ...etc
インデックスは日本語に略すと索引という意味ですが、当然ながら索引を生成するためのコストが必要です。 これはデータを挿入する際に行われるため、たくさんインデックスを張ってしまうと、その分INSERTが大変になります。
つまりインデックスを貼りすぎてしまうと、MySQL側にかかる負担が増えてしまうのです。
さいごに
データベースにおけるインデックスは、 『WHERE句やORDER BYの性能向上と、インデックス（索引）生成コストに対する負担増』 という、二面性のあるメリットとデメリットを兼ね備えています。
私もまだまだ発展途上なので、インデックスを張るのが上手い人はすごく尊敬してます。 データベースやSQLのパフォーマンスチューニング関連は体系的に学んでいないのですが、枯れている分野で名著も何冊か存在するので、頑張って勉強していこうと思います。
