このすみろぐ

とあるWebエンジニアが、技術や趣味について書くブログです。

MySQLのWHERE狙いのキー(インデックス)と、ORDER BY狙いのキーによるSQLの高速化

少し昔の話ですが、『YAPC::Asia Tokyo 2014』のイベントに参加したとき、『Where狙いのキー、order by狙いのキー』という発表を聞きました。

当時の私はデータベースのインデックスについての知識が皆無だったので、発表を聞いた際は衝撃を受けたことを覚えてます。

www.slideshare.net

WHERE狙いのキー、ORDER BY狙いのキー

キーと書いてありますが、主にインデックスのことです。 「WHEREとORDER BYの、どちらに向けた高速化インデックスなのか?」 これを意識する必要があります。

  1. WHEREによるデータの絞り込みを高速化するためのインデックス
  2. ORDER BYによる並び替えの性能を向上するためのインデックス

MySQLにおけるインデックスの場合、まったく同一のテーブル定義であっても、実際に入っているデータの偏りによって有効なインデックスは異なります。 高速化したいSQLを、EXPLAINを付与するなどして実行計画を確認しつつ、本当に必要なインデックスを見定めます。

ちなみにソフトウェア工学における名言のひとつに、「推測するな、計測せよ」という名言があります。 インデックスは、最たる例のひとつかもしれません。

WHEREを狙うか、ORDER BYを狙うかの判断基準

『Where狙いのキー、order by狙いのキー』のスライドのまとめには、次の記述があります。

  • WHERE句で十分絞り込める場合はWHERE狙い
  • WHERE句がほとんど機能しないような場合はORDER BY狙い

引用:https://www.slideshare.net/yoku0825/whereorder-by

WHEREのためのインデックス

たとえば少女マンガのイベント参加者を絞り込む場合、参加者の多くは女性であることが予想されます。 このケースで WHERE 性別=女性 のWHERE句を高速化しようとして性別にインデックスを張ったとしても、そもそも参加者の多くが女性なので役に立ちません。

反対に WHERE 性別=男性 のWHERE句であれば、少女マンガのイベント参加者に男性は少ないと予想されるので、インデックスによる絞り込みの恩恵を受ける可能性が高まります。

ORDER BYのためのインデックス

かるぱねるらさんのブログに、『ORDER BY狙いのキーが何故速いか』という記事がありました。

techblog.karupas.org

記事内で紹介されていた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のパフォーマンスチューニング関連は体系的に学んでいないのですが、枯れている分野で名著も何冊か存在するので、頑張って勉強していこうと思います。