はじめに
クエリには、インデックスが効かなくなるアンチパターンが存在します。
気づかず激遅クエリを爆誕させないように、今一度クエリのあれこれを身体に叩き込みましょう。
なお、本記事ではMySQL（InnoDB）を前提としています。
インデックスの実態
そもそもインデックスとは何でしょうか。
'張ると検索が早くなるやつ'として認識している人は多いと思いますが、その実態をしっかりと認識している人は少ないのではないでしょうか。
インデックスの構造
インデックスは、一般的には木構造を用いて構築されます。
（ハッシュやビットマップなども存在しますが、今回は割愛）
B木（B-Tree)
多分木のバランス木で、O(logN) で目的のデータまで到達できるアルゴリズムです。
※B木自体の解説は省略します。
B+木（B-Plus Tree)
B木の派生であり、MySQLやPostgreSQLなどのメジャーなRDBのインデックスに用いられる構造です。
基本的な構造や仕組みはB木と一緒ですが、シーケンシャルなデータを葉ノードに持つため、B木に比べて範囲検索が得意になっています。
葉ノードは、レコードの実体への参照を持ちます（後述）
【参考】
B木の基礎を学ぶ
MySQLのインデックスの使用の仕組み
インデックスの分類
InnoDBにおいて、インデックスには2種類存在します。
クラスタインデックス
主キーによって作成されるインデックスであり、1テーブルに付き1つ存在します。
上記で "レコードの実体への参照を持つ" と記載しましたが、クラスタインデックスがそれに当たります。
つまり、主キーと実データの参照が組となって葉ノードに格納されています。
クラスタインデックスはテーブルの物理的な格納順序を制御する役割を担っており、キー値（主キー）の順序に従って格納されます。
これにより、高速な範囲検索や等値検索が実現しています。
セカンダリインデックス
主キー以外のカラムによって作成されたインデックスをセカンダリインデックスや非クラスタインデックスなどと呼びます。
セカンダリインデックスでは、葉ノードにレコードへの直接参照を持ちません。
代わりに、クラスタインデックスのキー値を持ちます。
なので、セカンダリインデックスを用いて検索を行う場合は、クラスタインデックスを経由することになります。
【参考】
索引構成表とクラスタ化インデックス
アンチパターン
インデックスが効かなくなるやりがちなアンチパターンをいくつかご紹介します。
否定系を使う
インデックスカラムに対し否定形を用いて検索を行った場合、インデックスは適用されません。
SELECT * FROM table_a WHERE index_col <> 100 ;
- <>
- !=
- NOT IN
など
NULL系の比較を使う
NULLはインデックスデータに存在しないため、IS NULLなどを使用すると全表検索になります。
SELECT * FROM table_a WHERE index_col IS NULL ;
インデックス列の演算
インデックスが張られているカラムに対して加工を行うとインデックスが適用されなくなります。
これはだめ
SELECT * FROM table_a WHERE index_col * 3.14 > 100 ;
こうすればOK
SELECT * FROM table_a WHERE index_col > 100 / 3.14 ;
後方一致、中間一致のLIKE
B+木の葉ノードの特性上、前方一致以外のLIKEではインデックスが適用されません。
これらはだめ
SELECT * FROM table_a WHERE index_col LIKE '%hoge' ;
SELECT * FROM table_a WHERE index_col LIKE '%hoge%' ;
これはOK
SELECT * FROM table_a WHERE index_col LIKE 'hoge%' ;
暗黙の型変換を行っている
カラムの型と違う値で比較やINSERT、UPDATEを行った場合インデックスが適用されません。
index_colが文字列で定義されている場合...
これはだめ
SELECT * FROM table_a WHERE index_col = 10 ;
これはOK
SELECT * FROM table_a WHERE index_col = '10' ;
orを使う
where句でorを使うとインデックスが使われず全表検索になります。
SELECT * FROM table_a WHERE a = 1 OR b = 2 ;
これついては検証をしている方がいらっしゃったので、そちらをご参照ください。
結論としては以下のようです。
- Where句内のOR演算子で、インデックスが張られていないカラムが検索条件に混ざっている場合にフルテーブルスキャンになる（これは自然）
- Where句内のOR演算子で、検索条件に指定してるカラムすべてに個別にインデックスを張っている場合はフルテーブルスキャンにならない（MySQL5.6よりは前はフルテーブルスキャンになったらしいが、MySQL5.6以降ではインデックスマージという最適化が入る。ただし5.6でも全文検索において例外あり。）
- 全文検索時のFULLTEXTインデックス（転置インデックス）を使用している場合は、Where句内でOR演算子を使うことでフルテーブルスキャンになる
【参考】
MySQLでWhere句内でOR演算子を使うと「インデックスが効かなくなる」は本当か
複合インデックスの列の順番が違う
複合インデックスを使いたい時、列の順番を間違うとインデックスが適用されません。
(col_a, col_b)に複合インデックスが設定されていた時...
これはだめ
SELECT * FROM table_a WHERE col_b = 'b' AND col_a = 'a';
これならOK
SELECT * FROM table_a WHERE col_a = 'a' AND col_b = 'b';
インデックス起因以外の要因
今回はインデックスに絞ってご紹介しましたが、クエリが重くなる要因はインデックス起因のみではありません。
以下の観点にも注意しましょう
- 不適切なクエリの設計
- データ量の増加
- テーブル設計の問題
- 統計情報の不足
- ハードウェアリソースの不足
- クエリのオプティマイザの不備
- ネットワーク遅延
- データベースのロックと競合
- 高同時ユーザーアクセス
- クエリの冗長性
- サブクエリの多用
- 複数のJOIN操作
- 高度な条件演算子の使用
- データの欠損
- キャッシングの不適切な使用
- クエリの再コンパイル
- レコードのフルスキャン
- 重複削除のソートの発生
など
終わりに
今回はインデックスに絞ってクエリのアンチパターンをご紹介しました。
今後クエリを書く際に上記の観点を意識してみてください。
参考になった方はいいねをお願いいたします。