SQLチューニングをしている中で、なぜかインデックスをうまく使ってくれないクエリがあり、色々調べたところ『最もカーディナリティの高いカラムをインデックスの一番左に置けばよいわけではない』ということがわかったので、メモとして残したいと思います。
先に結論
複合インデックスではカーディナリティが高いカラムを左側にすべき。
ただし、範囲検索されるカラムはカーディナリティが高くてもインデックスの右側にすべき!
カーディナリティとは?
カラムに格納されているデータの種類がどのくらいあるのか(カラムの値の種類の絶対値)を、カーディナリティといいます。 カーディナリティの値によって、「カーディナリティが低いカラム」「カーディナリティが高いカラム」という言い方をします。
カーディナリティが低いカラム
例えば性別というカラムがあり、中身が男か女の二種類しかないのであれば、性別カラムのカーディナリティは2です。 他にもフラグのような0か1しかないようなカラムも、カーディナリティが低いと言えます。
カーディナリティが高いカラム
IDや日付のようなカラムであれば、たくさんのバリエーションの値が存在しています。 こういったカラムをカーディナリティが高いと言います。
カーディナリティとインデックス
インデックスはカーディナリティが高いカラムに対して作成すべき。
範囲検索されるカラムを含む複合インデックスについて
以下のようなuserテーブルを題材に説明します。
id | name | class | birthday |
---|---|---|---|
1 | 佐藤 | A | 04/29 |
2 | 鈴木 | B | 06/27 |
3 | 高橋 | B | 04/11 |
4 | 田中 | A | 08/12 |
5 | 伊藤 | B | 10/15 |
6 | 渡辺 | C | 10/28 |
7 | 山本 | A | 03/31 |
8 | 中村 | B | 02/11 |
9 | 小林 | C | 01/24 |
10 | 加藤 | A | 05/08 |
11 | 吉田 | B | 05/30 |
12 | 山田 | C | 07/11 |
13 | 佐々木 | A | 05/03 |
14 | 山口 | B | 12/19 |
15 | 松本 | C | 04/26 |
16 | 井上 | A | 01/08 |
17 | 木村 | B | 11/26 |
18 | 林 | C | 11/16 |
範囲検索するにしても検索条件が1つであれば簡単です。 birthday カラムのインデックスを作成すればよいだけです。
-- 1月から6月生まれの人を抽出する SELECT name FROM user WHERE birthday '01/01' AND '06/30'
では検索条件が複数ある場合はどうでしょうか?
-- Bクラスで1月から6月生まれの人を抽出する SELECT name FROM user WHERE class = 'B' AND birthday BETWEEN '01/01' AND '06/30'
classはA,B,Cしかないため、カーディナリティは3です。一方birthdatyのカーディナリティは日付なので多いはずです。今回だと全員日付が違うので18です。 「インデックスはカーディナリティが高いカラムに対して作成すべき」というセオリーの通りであれば、 birthday->class 順の複合インデックスを作ることになります。 以下の図は、birthday->classの複合インデックスの場合の、Bクラスで1月から6月生まれの人を抽出するSQLを実行した際のインデックス走査の様子です。
なおインデックスの走査方法については以下の記事をご参照ください。 tomozo6.hatenablog.jp
図の通り、日付が最初に一致するエントリから、 最後に一致するエントリまでがスキャンの範囲になります。今回だと実に4つのリーフノードを走査する必要があります。 ❌印のエントリは、class=Bではないので抽出しません。つまり無駄に走査していることになり非効率です。
一方以下の図は、セオリーとは違う class -> birthday の複合インデックスの場合です。
class=Bとインデックスの最初のカラムが1つの値に定まっています。 classがBの範囲内でbirthdayのカラムが順番に並んでいるので、無駄な走査がありません。 リーフノードも2つの走査で済みます。
日付の範囲が広がれば広がるほど、パフォーマンスの違いは大きくなっていきます。
まとめ
複合インデックスではカーディナリティが高いカラムを左側にすべき。ただし、範囲検索されるカラムは、カーディナリティが高くてもインデックスの右側にすべき!
赤文字部分が今回判明した新しいセオリーです。 等号(=)で1つの値に定められるカラムは、範囲検索される前に先にインデックスで走査させる。とも言い換えれます。
インデックスがあるはずなのに、 範囲検索しているSQLが遅い! ということがあれば、これが原因の可能性がありますので、皆さんも是非一度自身の環境のインデックスを見てみてください!