インデックスが効く条件
InnoDBのセカンダリインデックスはB-treeです。オプティマイザがB-treeを有効に使える条件はいくつかあります。
- WHERE句で比較している対象が、インデックスに格納されている値そのものであること。つまり、列の生の値または関数インデックスが定義した式の計算結果であり、それを加工した値ではないこと。
- 比較する値の型・文字セット・照合順序が、インデックスの並び順と食い違っていないこと。
- インデックス上で探索の開始位置が決まること。
これらを満たしても、インデックスが効くとは限りません。探索の開始位置が決まっても読む件数がテーブル全体と大差ないと見積もられる場合や、関数インデックスをJOINの結合条件に使う場合など、効かないケースはほかにもあります。
インデックスを張った約10万行の注文テーブルordersで実際に検証した上で、どんなケースでインデックスが効かないのかを整理しました。MySQLのバージョンは8.4.11です。
1. 格納されている値と比較する値の形が違う
列に関数をかける
created_at列そのものではなくDATE(created_at)という計算結果を比較すると、インデックスに格納されているのは生のcreated_atの値なのでインデックスを使った効率的な検索ができなくなります。
EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = '2024-06-15'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 96849
filtered: 100.00
Extra: Using wherepossible_keysがNULLになっており、利用できるインデックスの候補になっていません。列に関数をかけるのをやめ、created_at >= '2024-06-15' AND created_at < '2024-06-16'のように生のcreated_atに対する範囲条件で指定すれば、インデックスが有効になります。
EXPLAIN SELECT * FROM orders
WHERE created_at >= '2024-06-15' AND created_at < '2024-06-16'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: range
possible_keys: idx_created_user
key: idx_created_user
key_len: 5
ref: NULL
rows: 143
filtered: 100.00
Extra: Using index conditionJSON列をその場で抽出する
JSON型のpayload列から->>演算子でその場で値を取り出して比較すると、インデックスが効きません。->>はJSON_UNQUOTE(JSON_EXTRACT(...))の短縮記法なので、列に関数をかけている点でDATE(created_at)のケースと同じです。
EXPLAIN SELECT id FROM orders WHERE payload->>'$.order_no' = 'A-000001'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 96849
filtered: 100.00
Extra: Using wherepossible_keysがNULLになっており、利用できるインデックスの候補になっていません。
暗黙の型変換で列側が変換される
VARCHAR型のorder_noを数値の1と比較すると、インデックスに格納されている文字列の値を1件ずつ数値に変換してから比較してしまいます。
EXPLAIN SELECT id FROM orders WHERE order_no = 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: index
possible_keys: idx_order_no
key: idx_order_no
key_len: 82
ref: NULL
rows: 96849
filtered: 10.00
Extra: Using where; Using indextypeがindexになっており、インデックスは使っているものの、先頭から末尾まで全件を読んでいます。比較時の型変換により、文字列列と数値定数の比較は浮動小数点として行われます。'1'、' 1'、'1a'のように同じ数値に変換される文字列が複数あるため、変換後の値から元の文字列の位置を逆算できず、インデックス上で探索の開始位置を決められません。
EXPLAIN SELECT id FROM orders WHERE order_no = 'A-000001'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ref
possible_keys: idx_order_no
key: idx_order_no
key_len: 82
ref: const
rows: 1
filtered: 100.00
Extra: Using index一方、shop_idのような数値列を文字列定数と比較する場合は、列側ではなく定数側が変換されます。定数畳み込み最適化により、'3'は先に整数の3として読み替えられ、列の値はそのまま整数として比較されます。
EXPLAIN SELECT * FROM orders WHERE shop_id = '3'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ref
possible_keys: idx_shop_user_created
key: idx_shop_user_created
key_len: 4
ref: const
rows: 9234
filtered: 100.00
Extra: NULL列側が変換されるか、定数側が変換されるかは、列の型と定数の型の組み合わせによって決まります。
文字セット・照合順序の不一致
同じ文字セットでも照合順序が違う列同士を比較すると、変換ではなくエラーになります。utf8mb4_0900_ai_ciとutf8mb4_general_ciは同じutf8mb4ですが照合順序が異なり、比較そのものが拒否されます。
EXPLAIN SELECT u.user_id FROM users_utf8 u JOIN users_ci c ON u.user_id = c.user_id\G
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='latin1とutf8mb4など、文字セット自体が違う列同士を比較するときは、暗黙の変換が入ります。照合順序の変換可能性により、片方がUnicode、もう片方が非UnicodeならUnicode側が優先され、非Unicode側が変換されます。変換される列は、インデックスに格納されている値のソート順と比較後のソート順が合わなくなるため、その列ではインデックス上で探索の開始位置を決められません。
関数インデックスは式の結果しか持たない
関数インデックスは、列そのものではなく、定義した式の計算結果を格納するインデックスです。WHERE句で式が一致する形で書かれていれば、通常の列と同じように使えます。
EXPLAIN SELECT id FROM t_func_only WHERE (CAST(created_at AS DATE)) = '2024-06-15'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_func_only
partitions: NULL
type: ref
possible_keys: idx_func
key: idx_func
key_len: 4
ref: const
rows: 143
filtered: 100.00
Extra: NULLインデックスに格納されているのはCAST(created_at AS DATE)の計算結果であり、生のcreated_at列に対する検索では有効ではありません。
EXPLAIN SELECT id FROM t_func_only
WHERE created_at >= '2024-06-15' AND created_at < '2024-06-16'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_func_only
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 99750
filtered: 11.11
Extra: Using where2. インデックスを途中から読もうとする
先頭ワイルドカードのLIKE
LIKE '%Alice%'のように先頭にワイルドカードがあると、どの位置から一致し得るかがソート順から決まりません。インデックス上で探索の開始位置を決められないため、テーブル全体を読むことになります。
EXPLAIN SELECT * FROM orders WHERE name LIKE '%Alice%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 96849
filtered: 11.11
Extra: Using where前方一致であれば、先頭からの並びがそのまま使えます。
EXPLAIN SELECT id FROM orders WHERE order_no LIKE 'A-0000%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: range
possible_keys: idx_order_no
key: idx_order_no
key_len: 82
ref: NULL
rows: 99
filtered: 100.00
Extra: Using where; Using index複合インデックスの左側の列を使用しない
複合インデックスは、複数の列をつなげた1本のキーとしてソート順に並びます。(shop_id, user_id, created_at)なら、shop_idが同じ範囲の中でuser_idが並び、さらにその中でcreated_atが並ぶ、という入れ子の順序です。実際の並びは次のようになります。
shop_id user_id created_at
1 alice01 2024-06-01
1 alice01 2024-06-03
1 bob02 2024-06-02
2 alice01 2024-06-01
2 carol03 2024-06-04shop_idが1の範囲の中だけを見るとuser_idがalice01→bob02の順に並び、さらにalice01の中ではcreated_atが日付順に並んでいます。shop_idが2に変わると、user_idやcreated_atの大小関係はいったんリセットされ、また先頭から並び直しです。この構造上、先頭列であるshop_idの条件がないと、ソート順のどこから読み始めればよいか決まりません。
(created_at, user_id)という並びのインデックスを持つt_created_userテーブルに対して、先頭列created_atを指定せずuser_idだけで検索した場合は次のようになります。
EXPLAIN SELECT id, created_at FROM t_created_user WHERE user_id = 'alice01'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_created_user
partitions: NULL
type: index
possible_keys: idx
key: idx
key_len: 71
ref: NULL
rows: 98481
filtered: 10.00
Extra: Using where; Using indexkeyにはidxが入っており、インデックスそのものは使われています。しかしtypeはindexで、rowsはテーブルの全10万件に近い98481件です。先頭列created_atに条件がないため、どこから読み始めればよいかが決まらず、インデックスを先頭から末尾まで丸ごとたどりながらuser_idで1件ずつふるいにかけています。読む件数だけで見ればテーブル全体を読むのとほぼ変わらず、位置決めの役には立っていません。
ただし、最適化によってインデックスが効くケースもあります。(user_id, created_at)のインデックスに対してuser_idを指定せずcreated_atだけで検索すると、直前の例と同じく開始位置が決まらないはずですが、MySQL 8.0.13以降のSkip Scanが、先頭列の取り得る値ごとに区切って読むことでこのインデックスを使えるようにします。
EXPLAIN SELECT id, user_id FROM t_user_created
WHERE created_at BETWEEN '2024-06-10 00:00:00' AND '2024-06-12 00:00:00'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_user_created
partitions: NULL
type: range
possible_keys: idx
key: idx
key_len: 71
ref: NULL
rows: 11777
filtered: 100.00
Extra: Using where; Using index for skip scanExtraにUsing index for skip scanとあります。この例はidとuser_idだけを読んでおり、どちらもインデックスに含まれています。Skip Scanは、クエリがインデックス内の列だけで完結し、先頭列の取り得る値の種類が少ないときに効く最適化です。SELECT *のようにテーブル本体の列まで読もうとすると最適化の対象外になります。
複合インデックスの途中列を飛ばす
(shop_id, user_id, created_at)でuser_idを飛ばしてshop_idとcreated_atだけを条件にすると、連結キーの途中に穴が開くため、created_atは探索の開始位置を決められません。
EXPLAIN SELECT * FROM orders
WHERE shop_id = 3 AND created_at >= '2024-06-10 00:00:00'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ref
possible_keys: idx_shop_user_created,idx_created_user
key: idx_shop_user_created
key_len: 4
ref: const
rows: 9234
filtered: 50.00
Extra: Using index conditiontypeがrefなので効いているように見えますが、refが示しているのは先頭列shop_idだけで開始位置が決まったことです。key_lenはshop_idのINT1列分、4バイトしかなく、created_atは位置決めには使われていません。shop_idで絞った範囲の行をすべて読みながら、created_atの条件はUsing index conditionでフィルタしているだけの状態です。
範囲条件を複合インデックスの先頭に置く
(created_at, user_id)のように範囲条件になりがちな列を左端に置く場合と(user_id, created_at)のように等価条件になりがちな列を左端に置く場合では、パフォーマンスに大きな差が生まれます。
EXPLAIN ANALYZE SELECT * FROM orders FORCE INDEX (idx_created_user)
WHERE created_at >= '2024-06-10 00:00:00' AND user_id = 'alice01'\G
-> Index range scan on orders using idx_created_user over ('2024-06-10 00:00:00' <= created_at AND 'alice01' <= user_id), with index condition: ((orders.user_id = 'alice01') and (orders.created_at >= TIMESTAMP'2024-06-10 00:00:00')) (cost=0.71 rows=1) (actual time=0.801..17.6 rows=1428 loops=1)EXPLAIN ANALYZE SELECT * FROM orders FORCE INDEX (idx_user_created)
WHERE created_at >= '2024-06-10 00:00:00' AND user_id = 'alice01'\G
-> Index range scan on orders using idx_user_created over (user_id = 'alice01' AND '2024-06-10 00:00:00' <= created_at), with index condition: ((orders.user_id = 'alice01') and (orders.created_at >= TIMESTAMP'2024-06-10 00:00:00')) (cost=0.71 rows=1) (actual time=0.0153..1.78 rows=1428 loops=1)クエリの結果として同じ1428件を返すのに、created_atが先頭のインデックスはactual timeが17.6ms、user_idが先頭のインデックスは1.78msでした。(created_at, user_id)ではcreated_at >= ?という条件が全ユーザー分の連続領域になるため、その範囲すべてを読みながらuser_idの条件をインデックスの中で判定してふるいにかけています。(user_id, created_at)ではuser_id = 'alice01'でまず該当ユーザーの区画まで絞り込み、そのわずかな区画の中だけを範囲で読むため、読む量そのものが桁違いに少なくなります。
3. 低選択性の等価条件
deletedやstatusのようなカーディナリティが低い列でも、InnoDBは条件に一致する分だけをインデックス経由で読みます。ヒットする行の割合が大きくても、いきなりテーブル全体の走査に切り替わるわけではありません。
EXPLAIN SELECT * FROM orders WHERE deleted = 0\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ref
possible_keys: idx_deleted
key: idx_deleted
key_len: 1
ref: const
rows: 48424
filtered: 100.00
Extra: NULL一致する行が多くても、typeはrefのままです。一方、否定条件status <> 'canceled'は状況が違います。
EXPLAIN SELECT * FROM orders WHERE status <> 'canceled'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ALL
possible_keys: idx_status
key: NULL
key_len: NULL
ref: NULL
rows: 96849
filtered: 50.00
Extra: Using wherepossible_keysにidx_statusは挙がりますが、keyはNULLです。等価条件は特定の値へのパスを直接たどれるのに対し、否定条件は「その値以外すべて」という範囲になり、結局大半を読むと見積もられてテーブル走査に切り替わっています。同じ低選択性でも、等価か否定かでオプティマイザの判断が変わります。
4. 別の列をまたぐOR
user_id = 'alice01' OR shop_id = 3のように別々の列にまたがるORは、それぞれの条件が十分に絞り込めていても、まとめて1回のインデックスアクセスにはなりません。
EXPLAIN SELECT * FROM orders WHERE user_id = 'alice01' OR shop_id = 3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ALL
possible_keys: idx_user_created,idx_shop_user_created
key: NULL
key_len: NULL
ref: NULL
rows: 96849
filtered: 7.65
Extra: Using where両方とも候補には挙がっていますが、Index Mergeによる合算よりテーブル走査の方がコストが低いと判断されています。UNION ALLに分割すると、それぞれの枝で個別にインデックスが使われます。
EXPLAIN SELECT * FROM orders WHERE user_id = 'alice01'
UNION ALL
SELECT * FROM orders WHERE shop_id = 3\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: orders
partitions: NULL
type: ref
possible_keys: idx_user_created
key: idx_user_created
key_len: 66
ref: const
rows: 2000
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 2
select_type: UNION
table: orders
partitions: NULL
type: ref
possible_keys: idx_shop_user_created
key: idx_shop_user_created
key_len: 4
ref: const
rows: 9234
filtered: 100.00
Extra: NULLUNION ALLは両方の条件に当たる行を二重に返すので、結果を元のORと同じにするなら重なりの扱いに注意が必要です。同一列に対するINやORなら1つのインデックスで済むため、そもそも分割する必要はありません。
5. プレフィックスインデックスが短すぎる
order_no(3)のように先頭数文字だけをインデックス化すると、開始位置は決まりますが、その数文字が一致する行すべてを読むことになります。
EXPLAIN SELECT * FROM orders_prefix WHERE order_no = 'A-000001'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders_prefix
partitions: NULL
type: ref
possible_keys: idx_order_no_prefix
key: idx_order_no_prefix
key_len: 14
ref: const
rows: 50172
filtered: 100.00
Extra: Using wheretypeはrefで開始位置は決まっていますが、このシードのorder_noはA-000001からA-100000までなので、先頭3文字A-0はA-099999までのほぼ全行に一致します。プレフィックス長は、実際のデータでどこまで絞り込めるかを見て決める必要があります。
6. ORDER BYがインデックスの並びと一致しない
WHERE句の絞り込みに使ったインデックスが、ORDER BYの並び順まで満たしているとは限りません。
EXPLAIN SELECT * FROM orders WHERE user_id = 'alice01' ORDER BY name\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ref
possible_keys: idx_user_created
key: idx_user_created
key_len: 66
ref: const
rows: 2000
filtered: 100.00
Extra: Using filesorttypeはrefで絞り込み自体はうまくいっていますが、ExtraにUsing filesortが出ています。idx_user_createdはuser_idのあとにcreated_atが並ぶインデックスで、nameの順序とは無関係だからです。ORDER BYまでインデックスで満たしたい場合は、(user_id, name)のような並びのインデックスが別途必要です。
7. 関数インデックスをJOINの結合条件に使う
関数インデックスはWHERE句の定数比較で使えますが、JOINの結合条件では、同じ式であっても関数インデックスは候補になりません。
EXPLAIN SELECT o.id FROM report_days d JOIN t_func_only o
ON (CAST(o.created_at AS DATE)) = d.d\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: d
partitions: NULL
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: NULL
rows: 3
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: o
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 99750
filtered: 100.00
Extra: Using where; Using join buffer (hash join)o側はidx_funcを持っていますが、possible_keysはNULLです。ハッシュ結合を切ってもFORCE INDEXを付けても、候補にはなりません。ハッシュ結合は結合条件に使えるインデックスがないあとの代替であり、インデックスを無視して選ばれているわけではありません。
関数インデックスは式の計算結果をインデックスの中だけに持ちます。同じ計算結果を生成カラム、つまりGENERATED ALWAYS AS (式)で定義する列として持てば、列同士の結合になるため関数インデックスのようなJOINでの制限はありません。同じCAST(created_at AS DATE)でも、結果を生成カラムとして持ち、そこに通常のインデックスを張って列同士で結合するとrefになります。
EXPLAIN SELECT o.id FROM orders_func o JOIN report_days d
ON o.created_date = d.d\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: d
partitions: NULL
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: NULL
rows: 3
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: o
partitions: NULL
type: ref
possible_keys: idx_created_date
key: idx_created_date
key_len: 4
ref: innodb_verify.d.d
rows: 145
filtered: 100.00
Extra: Using indexJOINでインデックスを使いたいなら、関数インデックスではなく生成カラムのインデックスを使う必要があります。
8. IS NULL
最後に、よくある誤解を1つ検証します。NULLは値がない状態ですが、InnoDBのセカンダリインデックスにはNULLの行もエントリとして載ります。IS NULLの検索でも、必ずしもインデックスが効かないとは限りません。
EXPLAIN SELECT * FROM orders WHERE shipped_at IS NULL\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ref
possible_keys: idx_shipped_at
key: idx_shipped_at
key_len: 6
ref: const
rows: 5000
filtered: 100.00
Extra: Using index conditiontypeはrefです。NULLもインデックス上の1つの値として並ぶため、等価条件と同じように位置が決まります。