はじめに
テーブルカラムへの効果的なインデックス(索引)の張り方
この記事を書いていた時に、
今まで自分が調査した内容は、単一インデックスの張り方だけだったな〜と思い、
今度は複合インデックス(コンポジット索引)の効果的な張り方に着目してみたいと思います。
（基本的に、「インデックス」は、一般的なB-TREEインデックスで話しています。）
複合インデックスとはそもそも？
単一カラムインデックスってのは、1つのカラムをキーとして索引を作り、検索効率化を図る目的でしたが、
要するに、
テーブルの複数のカラムを組み合わせて1つのインデックスを張るってことですな。
適切な複合インデックスを定義するには、
インデックスがどのように動作するかだけでなく、
DBにどのようにデータ問い合わせするか
つまり、WHERE句にどんなカラムの組合せで条件を指定するか、
までも考えた方がいい。
複合インデックス(コンポジット索引)は順番が命
複合インデックスを張るときは、SQLの評価順序を意識すべし！！
複合インデックスのカラムの設定順（評価させる順番）や、
実際にSQL文法の評価順を意識したクエリの記述が、
複合インデックスを効かせるのに大事。
カラム順序
例えば、
column1、column2という順番でインデックスを張る場合に、
- column1を条件でスキャン
- column2を条件でスキャン
っていう順序でしか索引スキャンは走らないので、
どっちのカラムを先に評価させるか、考える必要がある。
SQLクエリ(SELECT)の評価順序
SELECT※⑦
count(DISTINCT※⑧ t1.no)
FROM※①
tbl1 t1
LEFT JOIN※③
tbl2 t2
ON※② t1.id = t2.t1_id
WHERE※④
t1.delete_flg = 0
GROUP BY※⑤
t1.id
HAVING※⑥ count(*) > 2
ORDER BY※⑨
t1.id DESC
LIMIT※⑩ 1000
FROMONJOINWHEREGROUP BYHAVINGSELECTDISTINCTORDER BYTOP(LIMIT)
というような順序で評価されるので、
複合インデックスとして設定しているカラムの読み込ませる順番も考慮に入れる必要がある。
例えば、
column1 → column２の順番の複合インデックスの場合、
これは有効
SELECT * FROM table WHERE column1 = '検索条件' ORDER BY column2
次のは有効じゃない
SELECT * FROM table WHERE column2 = '検索条件' ORDER BY column1
具体例で紐解きながら複合インデックスを理解する
家具の例で考えていきましょー。
| 商品No | 商品名 | 商品カテゴリ | 商品グループ | 価格 |
|---|---|---|---|---|
| 1 | すごいテーブル | テーブル | リビング | 30,000 |
| 2 | かっこいいイス | イス | ダイニング | 12,500 |
| 3 | かわいいソファ | イス | リビング | 5,6000 |
| 4 | えぐい枕 | 寝具 | ベッドルーム | 8,800 |
| 5 | 美しいライト | 照明 | リビング | 25,000 |
こんな商品テーブルがあった場合に、
３カラムで複合インデックスを張る例として、
1.商品No
2.商品カテゴリ
3.商品グループ
の順番に設定してみます。
CREATE INDEX index_name ON products (product_no, product_category, product_group);
ALTER TABLE products ADD INDEX index_name (product_no, product_category, product_group);
※飽くまで複合インデックスの例です。
本来ならカーディナリティの低いカラムを後ろ側に設定すると、
データ重複が発生して、その分インデックスのデータ量も大きくなってしまい、
リソースの圧迫やデータ更新におけるパフォーマンスの悪化が懸念されるので注意が必要。
絞り込めるレコード数の多いカラムを複合インデックスの最初に設定すると良い◎
上記で言うと、
商品Noですね〜
理由として、
カーディナリテイが高いカラム（絞り込めるレコード数が多い）の場合に、
足きりが多くできるので一気に対象レコードを絞り込めるメリットがあります。
商品Noは更新履歴とか持たない限り、
重複することはあまりないと思うので、一本釣りですな。
まあ、人間として、
商品Noを覚えていて、それでWHERE句に指定して、検索実行なんてことは少ないと思いますが、
実際にAPIとかでクエリを投げるときは、自然と商品Noも含めていると思います。
検索クエリの条件でよく使われるカラムを複合インデックスに設定する
検索条件としてよく使用するカラムもインデックス登録しておくといいですね。
上記で言うと、
商品カテゴリ 商品グループ
とかでしょうか。
カーディナリティ的には、
商品カテゴリ >= 商品グループ
だと思うので、
（グループはせいぜい4つくらいですかね：Living Dining Kitchen Bathroom）
商品カテゴリを2番目の複合インデックス、商品グループを3番目の複合インデックスとして設定しますか。
（本来なら、もっとカーディナリティが欲しい。）
等価比較(＝)するカラムを先に設定する
もし仮に、価格を複合インデックスに設定する場合、
価格は範囲検索をする可能性が高いので、
価格よりも前に商品カテゴリなんかの等価比較するカラムをインデックス設定しないと
インデックスがうまく効かないことも。
CREATE INDEX index_name ON products (product_price, product_category);// 良くない
product_priceを先にインデックスに設定した場合に、
SELECT * FROM products WHERE product_price >= 10000 AND product_category = 'テーブル';
としてしまうと、product_categoryのインデックスが効かない恐れがある。
二分木走査する時に、
二個目のインデックスである商品カテゴリが、最初の価格の条件が満たすかどうか判別できず、
結局、商品カテゴリは、product_price >= 10000の条件下で、
全走査されてしまい、商品カテゴリとしては、走査数が減らない事に。
つまり、商品価格で単一インデックスを貼っているのと変わらない事態になるので、
こっちがイイね👍
CREATE INDEX index_name ON products (product_category, product_price);// ベター
冗長なインデックスは避ける
例えば、テーブルに、単一+複合みたいに2つのインデックスを張るときに、
カラムが重複してたら、それは冗長なので、単一の方を消しちゃおう。
CREATE INDEX index_name1 ON products (product_no);
CREATE INDEX composite_index_name ON products (product_no, product_category, product_group);
というのも、
複合インデックスの方で、product_noの方のインデックスが効くから、単一のほうは不要。
3つのカラムの複合インデックスの挙動
ざっくりですが、
3カラムに対する複合インデックスが張られたときは、
- 1番目の列だけで検索する場合
- 1番目と2番目の列で検索する場合
- 全ての列で検索する場合
にインデックスが効果を発揮します。（詳細は後述）
※WHERE句で左から順に評価されるので、問題なくインデックスが効きます。
SELECT * FROM products WHERE product_no = 1;
SELECT * FROM products WHERE product_no = 1 AND product_category = 'テーブル';
SELECT * FROM products WHERE product_no = 1 AND product_category = 'テーブル' AND product_group = 'リビング';
WHERE句に各カラムを設定した時の挙動
複合インデックスキーに設定されているカラムの全てが検索条件に無くても、
キーの先頭から途中までのカラムが指定されていれば、インデックスが使える
イメージはこんな感じです。
| 商品 No |
商品 カテゴリ |
商品 グループ |
複合インデックス利用 可不可 |
備考 |
|---|---|---|---|---|
| ○ | ○ | ○ | 可 | |
| ○ | ○ | × | 可 | |
| ○ | × | × | 可 | |
| ○ | × | ○ | 可 | ※商品Noのみ、商品グループは無視 |
| × | ○ | ○ | 可 | ※索引スキップ・スキャン |
| × | ○ | × | 可 | ※索引スキップ・スキャン |
| × | × | ○ | 可 | ※索引スキップ・スキャン |
| × | × | × | 不可 |
<注意事項>
- 使用するDBの種類（例：MySQL, Oracle, Postgre etc）によって、上記の挙動は変わります。
- 使用するDBのバージョンによっても上記の挙動は変わる
- 例えば、
Oracle 8i以前は複合インデックスの第1キーをWHEREに持たない検索はインデックスが使用不可だったが、Oracle 9iから、インデックススキップスキャンと呼ばれる高速化の仕組みによってインデックスを利用でき、パフォーマンスが向上する可能性がある
- 例えば、
- スキップスキャンとは、インデックスをたどるときに、先頭列を無視する（スキップする）ことから、
INDEX SKIP SCANと呼ばれる - 自分より前のカラムのカーディナリティによって、スキップスキャンかフルスキャンがされるか別れる
- オプティマイザーの判断による
ちなみに、WHERE句の書く順番はDBによってはインデックスを効かせてくれる。
DBによってはどちらの書き方でもよしなに対応してくれて、インデックスは使用されるが、
やはり、複合インデックスを張ったカラムの順番と同じ順番でWHERE句にも書いた方が、パフォーマンスが上がるそう。
#インデックスの定義 column1, column2
WHERE句
①WHERE column1 = 1 AND column2 = 2
②WHERE column2 = 2 AND column1 = 1
まとめ
複合インデックスのクセがすごい。
シンプルに、クセでした。
以上、
ありがとうございました！