MySQL
SQL
SQLServer
PostgreSQL
oracle

SQLにおける複合INDEXの順番について


はじめに

SQLのチューニングについて勉強していたら「これは意識しないとな」というものがあったので書き留めたいと思います。


要約

複合INDEXを貼る場合はSQLの評価順序を意識して貼らないとダメみたいです。


そもそもINDEXとは

DBを触っていればINDEXについてはみなさんご存知だと思いますが、ご存じでなければ以下のページが参考になるかもしれません。

MySQLでインデックスを貼る時に読みたいページまとめ(初心者向け)

簡潔に言えば、検索する際に高速化のために使うもので、カラム(列)に対して貼るものです。厳密なところは私も把握しきれてないと思いますので言及は避けたいと思います(すみません)。


なんでINDEXを使うと検索が速くなるのか(本論から逸れます)

これも厳密なところは他の書籍や記事に譲ります(すみません)。

INDEXの仕組みとして、INDEXが貼られているカラムのデータと一緒に実データのアドレスが格納されているようなイメージだと私は捉えています。そのため、INDEXを貼っているカラムで条件指定して検索すると、探す先はINDEXを貼っているカラムだけが存在するところへ行き、「探すときに必要なのは指定したカラムのデータだけで、目的のレコードが見つかったら実データも一緒に取ってこれるよ」的な感じで高速化が図れます(キャッシュするデータの量が少ない、という捉え方でもいいと思います)。

一方、INDEXを使わない場合だと、「探すときに指定したカラムのデータだけ存在するところへ行っても、実データは一緒に取ってこれない」です。したがって、しぶしぶ探す先を全カラムのデータが入っているところへ行って取ってきます。


複合INDEXとは

複合INDEXとは複数のカラムに貼るものです。先述の「INDEXを貼っているカラムだけが存在するところ」におけるカラムが1つではなく2つ以上になるということです。

(ちなみに複合INDEXは結合INDEXとか複数INDEXとか言ったりすることもあるそうです。ややこしいですね)


複合INDEXのカラムの順番

本論です。複合INDEXはカラムの順番が大事です。なぜなら、(COL1カラム,COL2カラム)というような順番でINDEXを貼っている場合、「COL1カラムを条件とする⇒COL2カラムを条件とする」というような順序でしか使われないことになっているからです(厳密に言えば使われるのかもしれませんが、実行速度はかなり落ちると思います)。

ここで、SQLの評価順序を意識することが大事になります。

以下がSQLの評価順序です。



  1. FROM

  2. ON

  3. JOIN

  4. WHERE

  5. GROUP BY

  6. HAVING

  7. SELECT

  8. DISTINCT

  9. ORDER BY

  10. TOP(LIMIT)

SELECT文の評価順序の話より引用



複合INDEXが有効に働く例と働かない例

それでは実際に複合INDEXが有効に働く場合と働かない場合を見ていきましょう。

まずは有効に働く場合です。


複合INDEXが有効に働く場合

XテーブルにINDEXがCOL1カラム,COL2カラムの順序で貼られているとしましょう

CREATE INDEX ON X(COL1,COL2)

このとき以下のようなSQL文は複合INDEXが効果的に使われ、検索が高速で行われます。

SELECT * FROM X WHERE COL1 = '検索条件' ORDER BY COL2

この場合、WHERE句でCOL1を検索条件として用い、そのあとでORDER BY句でCOL2を並び替え条件として用いています。ここで、評価順序はWHERE句(上のリストでは4番目)⇒ORDER BY句(9番目)であり、複合INDEXの順番と評価順序が一致しているため有効に働きます。


複合INDEXが有効に働かない場合

次に有効に働かない場合です。複合INDEXの順番は先ほどと同一とします。

SELECT * FROM X WHERE COL2 = '検索条件' ORDER BY COL1

先ほどとカラムが逆のパターンです。この場合、WHERE句でCOL2を検索条件として用い、そのあとでORDER BY句でCOL1を並び替え条件として用いています。この場合だと複合INDEXの順番と評価順序が一致していないため、有効に働きません。

SELECT COL1,COL2,COUNT(COL3) FROM X GROUP BY COL2 HAVING COL1 = '検索条件'

この場合、GROUP BY句でCOL2を条件として用い、そのあとでHAVING句でCOL1を検索条件として用いています。ここで、評価順序はGROUP BY句(5番目)⇒HAVING句(6番目)であり、複合INDEXの順番と評価順序が一致していないため、有効に働きません。

以上、複合INDEXの順番について解説しました。これを意識するとクエリ実行速度がめっちゃ変わるかもしれません。