🔨
WHERE 句の「範囲検索」と複合インデックスを100%活用する方法

2025/06/27に公開
 はじめにMySQL でインデックスをどう貼るべきなのか考え・調査している時に知ったことを記事にしました。

（今回私が取り扱った MySQL のバージョンは8系でした）

 結論複合インデックスで「範囲検索」（>、<、BETWEEN）を使った列があると、その列以降のインデックスキーは絞り込み（フィルタリング）には利用されなくなる。
パフォーマンスを最大化するには、インデックスの定義順を「等価検索（=, <=>, IS NULL）で絞り込む列を先に、範囲検索で絞り込む列を後に」するのが鉄則。

 本題先日、インデックスが貼られていないことでパフォーマンスがかなり悪くなっているテーブルの設計をすることになりました。

その際、複合インデックスを貼るのが一番良いだろうというのが一旦自分の中で結論が出ました。

ただ、複合インデックスが「左側の列から順に使われる」（左端プレフィックスの原則）というのは知っていましたが、パフォーマンス問題の本質を見誤りそうになる出来事がありました。

 貼るべきインデックスとは※以下例に出すテーブル・仕様等は例え話です
今回、私は数千万件のレコードを持つ巨大な orders（注文）テーブルにインデックスを貼る必要がありました。

要件は「特定のショップの未発送の注文を、過去3ヶ月分表示する」というものです。
この要件を満たすクエリは、このようになります。
SELECT *
FROM orders
WHERE
    shop_id = 123
    AND ordered_at >= '2025-03-01' -- 3ヶ月前の日付
    AND status = 'pending' -- 'pending'は未発送
ORDER BY
    ordered_at DESC;
このクエリを高速化するため、当初私は次のような複合インデックスを考えました。
インデックス案: (shop_id, ordered_at, status)
左から順に使うという原則は守っています。

shop_id は必ず指定されるし、ordered_at で並び替えるから、この順番は理にかなっているように見えました。

しかし、インデックスについて調査していく中で、このインデックスが実は良く無いことに気づきました。
それは「範囲検索の後は、それ以降のインデックスキーは絞り込みには使えなくなる」という原則でした。

 複合インデックスの仕組みと「範囲検索の壁」
 「等価検索」と「範囲検索」ここで重要なのが、WHERE 句の検索条件にはインデックスの使われ方が全く異なる2種類があるという点です。
等価検索 (=, <=>, IS NULL): shop_id = 123 や status = 'pending' のように、値が特定できる検索。インデックスのツリーを効率的に深く潜っていき、候補をシャープに絞り込めます。
範囲検索 (>、<、BETWEEN、LIKEの前方一致以外): ordered_at >= '2025-03-01' のように、ある範囲を指定する検索。
この範囲検索に関して私の知識が漏れていました。

インデックスは B-Tree という構造で、データがソートされた状態で格納されています。

ordered_at で範囲検索する場合、データベースはインデックスツリーをたどり、範囲の開始点（'2025-03-01'）を見つけます。そして、そこから末尾までインデックスのリーフノードを順番にスキャンしていきます。
この「順番にスキャン」している間、DB はその先にあるインデックスキー（この場合は status ）のことは考慮できません。

なぜなら、ordered_at の範囲内では status の値はソートされている保証がないからです。

結果として、DB は範囲検索でヒットしたすべての行データを一度ディスクから読み込み、その上で status = 'pending' の条件を一つずつチェックするという非効率な「総当たり戦」を仕掛けることになります。

 修正案先ほどのクエリ WHERE shop_id = 123 AND ordered_at >= '2025-03-01' AND status = 'pending' を例に、インデックス設計・クエリを見直しました。

 インデックス
idx_shop_status_date (shop_id, status, ordered_at)

shop_id = 123: 等価検索。インデックスが使えます

status = 'pending': 等価検索。ステップ1で絞られた候補の中から、さらに「未発送」の注文だけに候補を絞り込みます

ordered_at >= '...': 範囲検索。インデックスが使えます。すでに「特定のショップの未発送注文」という非常に少ない候補に絞り込まれているため、この範囲チェックは極めて高速に完了します。


 クエリSELECT *
FROM orders
WHERE
    shop_id = 123
    AND status = 'pending'
    AND ordered_at >= '2025-03-01' -- 最後に範囲検索の where 句を指定
ORDER BY
    ordered_at DESC;
この通り、等価検索の列を前に持ってくるようにインデックスとクエリを修正しました。

 補足： EXPLAIN で確認しようこの動きは、EXPLAIN を使うとよくわかります。

悪い例のインデックスでクエリを実行すると、EXPLAIN の結果の Extra 列に Using where が表示されることがあります。

これは、インデックスだけでは条件を処理しきれず、ストレージから読み込んだデータに対して追加のフィルタリングを行っていることを示唆しています。

良い例のインデックスでは、この Using where が消え、より効率的にインデックスが使われていることが確認できるはずです。

 まとめ複合インデックスの設計では、WHERE 句の条件をよく分析することが何よりも重要。
等価検索(=, <=>, IS NULL)で使われる列を前に、範囲検索(>)で使われる列を後ろに置くのがパフォーマンスチューニングの基本
この原則の背景には、「範囲検索を使った時点で、それ以降のインデックスキーは絞り込みに使えなくなる」というデータベース（MySQL）の仕様がある。

 参考資料https://dev.mysql.com/doc/refman/8.0/ja/range-optimization.html#range-access-multi-part
はじめに

結論

本題

貼るべきインデックスとは

複合インデックスの仕組みと「範囲検索の壁」

「等価検索」と「範囲検索」

修正案

インデックス

クエリ

補足： EXPLAIN で確認しよう

まとめ

参考資料

Discussion
