インデックスが効く条件
InnoDBのセカンダリインデックスは、インデックスに定義した列の値、または関数インデックスなら式の計算結果を、ソート順に並べたB-treeです。オプティマイザがこれを使えるのは、次の条件が揃ったときです。
- WHERE句で比較している対象が、インデックスに格納されている値そのもの、つまり列の生の値、または関数インデックスが定義した式の計算結果であり、それを加工した別の値ではない
- 比較する値の型・文字セット・照合順序が、インデックスの並び順と食い違っていない
- その条件から、ソート順のどこを起点に読み始めればよいかが決まる
この3つを満たしても、インデックスが効くとは限りません。開始位置が決まっても読む件数がテーブル全体と大差ないと見積もられる場合や、関数インデックスを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に対する範囲条件で表せば、idx_created_user（created_at, user_id）が使えます。
EXPLAIN SELECT * FROM orders
WHERE created_at >= '2024-06-15 00:00:00' AND created_at < '2024-06-16 00:00:00'\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になっており、候補にすらなっていません。
暗黙の型変換で列側が変換される
order_noはVARCHARですが、これを数値の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列側が変換されるか、定数側が変換されるかは、列の型と定数の型の組み合わせによってMySQLの規則で決まります。ここで押さえておきたいのは、列側が変換されるとインデックスに格納されている値の並びと比較後の値の並びが噛み合わなくなり、位置を決められなくなることです。定数側だけが変換される場合は、列の値の並びもインデックスの並びも変わらないため、そのまま使えます。
文字セット・照合順序の不一致
同じ文字セットでも照合順序が違う列同士を比較すると、変換ではなくエラーになります。
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 '='utf8mb4_0900_ai_ciとutf8mb4_general_ciは同じutf8mb4ですが照合順序が異なり、比較そのものが拒否されます。
latin1とutf8mb4など、文字セット自体が違う列同士を比較するときは、暗黙の変換が入ります。照合順序の変換可能性により、片方がUnicode、もう片方が非UnicodeならUnicode側が優先され、非Unicode側が変換されます。次の例では、latin1のorders_latin1を先に読み、各行のuser_idをutf8mb4に変換した値でordersを検索しています。検索される側のordersはutf8mb4のままインデックスを使えるためrefが残ります。先に読むorders_latin1側にWHERE条件はなく全件を読む必要があるため、type: indexになっているのは変換の影響ではなく、インデックスを先頭から末尾までたどる方がテーブル本体を読むよりコストが低いと判断されたからです。
EXPLAIN SELECT o.id FROM orders o JOIN orders_latin1 l ON o.user_id = l.user_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: l
partitions: NULL
type: index
possible_keys: idx_user_id
key: idx_user_id
key_len: 18
ref: NULL
rows: 9859
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: o
partitions: NULL
type: ref
possible_keys: idx_user_created
key: idx_user_created
key_len: 66
ref: innodb_verify.l.user_id
rows: 2152
filtered: 100.00
Extra: Using where; Using index文字セット違いが常にインデックスを無効にするとは限らず、データ分布や結合順序によって結果が変わります。
関数インデックスは式の結果しか持たない
関数インデックスは、列そのものではなく、定義した式の計算結果を格納するインデックスです。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このインデックスは式の結果しか持たないため、範囲条件に書き換えても、生のcreated_at列自体にインデックスがなければ使えません。インデックスに格納されているのはCAST(created_at AS DATE)の計算結果であり、生のcreated_atとは値の形が違うからです。
EXPLAIN SELECT id FROM t_func_only
WHERE created_at >= '2024-06-15 00:00:00' AND created_at < '2024-06-16 00:00:00'\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%'のように先頭にワイルドカードがあると、name列のどの位置から一致し得るかがソート順から決まりません。
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とあります。Skip Scanは先頭列の取り得る値の種類が少ないときに有効な最適化で、常に使えるわけではありません。左端列を条件に含められるなら、それが最も確実です。
複合インデックスの途中列を飛ばす
(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. 条件を満たしても、それ以外の理由で選ばれない
ここまでは探索できるかどうかでしたが、ここからは探索はできるが、それでも選ばれない場合です。B-treeの構造上の制約ではなく、読む件数の見積もりやJOINの結合方式など、オプティマイザ側の判断によるものです。
低選択性の等価条件
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このテーブルではdeleted = 0が全体の9割を占めますが、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です。等価条件は特定の値へのパスを直接たどれるのに対し、否定条件は「その値以外すべて」という範囲になり、結局大半を読むと見積もられてテーブル走査に切り替わっています。同じ低選択性でも、等価か否定かでオプティマイザの判断が変わります。
別の列をまたぐ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: NULL同一列に対するINやORはこの問題を起こしません。別列をまたぐときだけ注意します。
プレフィックスインデックスが短すぎる
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までのほぼ全行に一致します。プレフィックス長は、実際のデータでどこまで絞り込めるかを見て決める必要があります。
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)のような並びのインデックスが別途必要です。
関数インデックスを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 (式) STOREDで定義する、値が自動計算される通常の列です。列として存在するため、生成カラムには関数インデックスのような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でインデックスを使いたいなら、関数インデックスではなく生成カラムのインデックスを使う必要があります。
補足: 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 conditionshipped_atは全体の5%がNULLですが、typeはrefです。NULLもソート順の中の1つの値として扱われ、他の値と同じように位置が決まります。効かないのはNULLだからではなく、これまでの節で見た、比較する値の形や開始位置の決め方に問題があるときです。