なぜ「複合インデックス」のカラム順を適当に決めてはいけないのか？

1. 「インデックスを貼ったのに遅い」——現場で繰り返される悲劇

Webアプリケーションの運用フェーズで最も頻発するトラブルの1つが、データ増加に伴う検索クエリの遅延です。開発環境の数十件〜数千件のデータでは数ミリ秒で返っていたクエリが、本番環境で数十万〜数百万レコードに達した途端、秒単位のスロークエリへと化け、コネクションプールを食い潰していきます。

そんなとき、多くのエンジニアが反射的に行う対応があります。「スロークエリログを見て、WHERE句とORDER BYに指定されているカラムをそのまま括って CREATE INDEX する」という対症療法です。

-- 注文テーブルから「特定ユーザーの特定ステータス、かつ直近1ヶ月以内の注文」を最新順に20件取得したい
SELECT id, user_id, status, ordered_at, total_amount
FROM orders
WHERE user_id = 1042
  AND status = 'COMPLETED'
  AND ordered_at >= '2026-08-24 00:00:00'
ORDER BY ordered_at DESC
LIMIT 20;

-- 現場で安易に作られがちな複合インデックス（アンチパターン）
CREATE INDEX idx_orders_bad ON orders (ordered_at, user_id, status);

「ordered_at も user_id も status もすべてインデックスに含まれているのだから、爆速になるはずだ」——そう信じて本番に適用した結果、何が起きるでしょうか。

EXPLAIN（実行計画）を実行すると、そこには残酷な現実が突きつけられます。

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
         type: range
possible_keys: idx_orders_bad
          key: idx_orders_bad
      key_len: 5                    -- ★ ordered_at 分しか使われていない！
         rows: 420000               -- ★ 42万行もスキャン対象になっている！
     filtered: 1.00
        Extra: Using index condition; Using filesort -- ★ メモリ上で全件ソートが発生！

「インデックスは効いている（key: idx_orders_bad）のに、なぜか40万行以上スキャンされ、あろうことか Using filesort（追加のソート処理）まで発生している……？」

この不可解な現象の原因は、インデックスが壊れているからでも、RDBMSのオプティマイザが阿呆だからでもありません。複合インデックスの「物理的な並び順」と「B+Treeの探索アルゴリズム」に対する理解を欠いたまま、適当な順序でカラムを並べてしまった設計者自身のミスです。

2. 複合インデックスの物理構造: それは1次元の「電話帳」である

複合インデックスの挙動を理解する最短ルートは、RDBMSが内部で構築している B+Tree（またはB-Tree）のリーフノードにおけるデータの物理配置 を知ることです。

複合インデックスは、魔法の多次元マトリクスではありません。それは紙の「電話帳」と全く同じ、厳格な辞書順で並べられた1次元のソートリストに過ぎません。

【電話帳のインデックス構造】
第1キー: 苗字（LastName）
第2キー: 名前（FirstName）
第3キー: 生年月日（BirthDate）

[佐藤, 一郎, 1985-04-01]
[佐藤, 健,   1990-11-15]
[佐藤, 健,   1995-02-20]
[鈴木, 次郎, 1988-08-08]
[田中, 太郎, 1992-05-05]

この電話帳を想像してみてください。

「苗字が佐藤で、名前が健の人」を探すのは極めて簡単です。第1キー（佐藤）で絞り込み、そのブロックの中で第2キー（健）を二分探索できます（最左プレフィックスの完全一致）。
しかし、「名前が健の人（苗字は不問）」を探そうとしたらどうなるでしょうか？佐藤健、鈴木健、田中健は電話帳のあちこちに分散しています。電話帳を最初から最後まで全ページめくるしかありません（Full Index Scan / Full Table Scan）。

これが、いわゆる 最左プレフィックス則（Leftmost Prefix Rule） の本質です。インデックスの先頭カラム（左端）を指定しないクエリでは、B+Treeの木構造を辿る二分探索（O(log N)）を開始することすらできないのです。

3. 最大の地雷: 範囲検索（Range）が引き起こす「インデックスの突然死」

最左プレフィックス則は、多くの開発者が知っています。「だから user_id を左端にして、CREATE INDEX (user_id, ordered_at, status) に修正しました！」と胸を張るエンジニアも少なくありません。

しかし、ここに第2の、そして最も凶悪な地雷が潜んでいます。それが 範囲検索（Range Scan）によるインデックス探索の強制終了 です。

-- 改良したつもりのインデックス
CREATE INDEX idx_orders_middle ON orders (user_id, ordered_at, status);

-- 実行するクエリ
SELECT * FROM orders
WHERE user_id = 1042
  AND status = 'COMPLETED'
  AND ordered_at >= '2026-08-24 00:00:00'
ORDER BY ordered_at DESC;

一見すると完璧に見えます。user_id は等価比較で先頭にあり、範囲検索の ordered_at も含まれています。しかし、このインデックスにおける物理的なソート順を思い浮かべてください。

【idx_orders_middle のリーフノード並び順】
(user_id=1042, ordered_at='2026-08-20', status='PENDING')
(user_id=1042, ordered_at='2026-08-25', status='CANCELLED')
(user_id=1042, ordered_at='2026-08-28', status='COMPLETED')
(user_id=1042, ordered_at='2026-09-01', status='PENDING')
(user_id=1042, ordered_at='2026-09-10', status='COMPLETED')

何が起きているか分かりますでしょうか。

user_id = 1042 のブロックへB+Treeを辿って直行します（ここは等価一致なのでピンポイント）。
次に ordered_at >= '2026-08-24' の条件によって、'2026-08-25' の行から右側へと連続走査（Range Scan）が始まります。
問題は第3キーの status です。 ordered_at が異なる値を持つ複数の行にまたがっているため、status はもはや綺麗にソートされていません（CANCELLED, COMPLETED, PENDING, COMPLETED とランダムに散らばっている）。

つまり、一度範囲検索（不等号、BETWEEN、前方一致以外のLIKEなど）に突入した瞬間、B+Treeの二分探索キーとしての効力はそこで完全に途切れ、それ以降のカラムはインデックス探索（Index Seek）に一切使えなくなります。

MySQLの EXPLAIN で key_len（インデックスとして使われたバイト数）を確認すると、user_id（8バイト）と ordered_at（5バイト）の合計13バイト分しか消費されておらず、status の定義長（例: 82バイト）が全く加算されていないことが確認できます。status = 'COMPLETED' の判定は、インデックスを走査しながら1件ずつ評価するフィルター（Index Condition Pushdown）に格下げされてしまうのです。

4. クエリ爆速化の金科玉条:「ESR原則（Equality -> Sort -> Range）」

では、複数条件・ソート・範囲検索が混在するクエリにおいて、どのような順序で複合インデックスを組むのが数学的・物理的に正解なのでしょうか。

データベースエンジニアの間で語り継がれている普遍の鉄則、それが 「ESR原則（Equality, Sort, Range）」 です。

【複合インデックス設計の金科玉条: ESR原則】
1. E (Equality / 等価比較): WHERE col = ... で完全一致させるカラムを左端に並べる
2. S (Sort / ソート順): ORDER BY col で指定するカラムを次に並べる
3. R (Range / 範囲検索): WHERE col >= ... などの範囲条件カラムを末尾に並べる

先ほどのクエリをESR原則に当てはめてみましょう。

-- 対象クエリ
SELECT id, user_id, status, ordered_at, total_amount
FROM orders
WHERE user_id = 1042                        -- [E] Equality
  AND status = 'COMPLETED'                  -- [E] Equality
  AND ordered_at >= '2026-08-24 00:00:00'   -- [R] Range
ORDER BY ordered_at DESC                    -- [S] Sort
LIMIT 20;

この場合、等価条件（Equality）は user_id と status です。ソート（Sort）と範囲（Range）はいずれも ordered_at です。

したがって、作成すべき最強のインデックスは以下の並び順になります。

-- ESR原則に基づいた理想的な複合インデックス
CREATE INDEX idx_orders_esr ON orders (user_id, status, ordered_at);

このインデックスを定義した状態で EXPLAIN を実行すると、劇的な変化が起きます。

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
         type: range
possible_keys: idx_orders_esr
          key: idx_orders_esr
      key_len: 95                   -- ★ user_id + status + ordered_at の全バイトを探索に使用！
         rows: 20                   -- ★ LIMIT 20 に応じて最小限の行しか読まない！
     filtered: 100.00
        Extra: Using index condition -- ★ Using filesort が完全に消滅！

なぜ Sort を Range より前に置くべきなのか？

ここで疑問に思うかもしれません。「なぜ Range よりも先に Sort カラムをインデックスに含めなければならないのか？」と。

仮に、ソート対象のカラムが範囲条件のカラムと異なっていたケースを考えてみましょう。

-- 範囲条件とソート条件が異なるクエリ
SELECT * FROM orders
WHERE user_id = 1042
  AND status = 'COMPLETED'
  AND total_amount >= 5000       -- [R] Range
ORDER BY ordered_at DESC         -- [S] Sort
LIMIT 20;

もし (user_id, status, total_amount, ordered_at) という「E → R → S」の順でインデックスを作ってしまうと、total_amount の範囲検索に入った時点でソート順（ordered_at）の並びが破壊されます。その結果、DBエンジンは条件に合致するレコードをすべてメモリ（sort_buffer）に吸い上げ、CPUを使ってクイックソートを行う Using filesort を発動せざるを得ません。10万件ヒットすれば、10万件すべてをソートした後に LIMIT 20 件を取り出すという、極めて無駄なI/Oが発生します。

しかし、ESR原則に従って 「E → S → R」、すなわち (user_id, status, ordered_at, total_amount) の順でインデックスを組むとどうなるでしょうか。

user_id = 1042 AND status = 'COMPLETED' で絞り込まれた領域の中では、全レコードがすでに物理的に ordered_at 順で並んでいます。DBエンジンはインデックスを逆順に舐めながら（Backward index scan）、total_amount >= 5000 を満たすレコードを20件見つけた瞬間に、クエリの実行を打ち切ることができます。ソートコストは完全にゼロ。スキャン行数も最小限で済みます。

5. 究極の最適化: テーブルアクセスを消し去る「カバリングインデックス」

ESR原則を適用するだけでもクエリは劇的に速くなりますが、大規模トラフィック下ではもう一段階上の壁にぶつかります。それが 「セカンダリインデックスからテーブル本体へのランダムアクセス（Bookmark Lookup / Clustered Key Lookup）」 です。

セカンダリインデックスの裏側で起きていること

MySQLのInnoDBストレージエンジンを例にとると、主キー（PRIMARY KEY）以外のインデックス（セカンダリインデックス）のリーフノードには、「インデックス対象カラムの値」と「対応する主キー（id）」 しか格納されていません。

そのため、クエリが SELECT total_amount のようにインデックスに含まれていないカラムを要求している場合、DBは以下の2ステップを踏む必要があります。

[ステップ1] セカンダリインデックスのB+Treeを探索し、合致する主キー（id）を取得する
    ↓ （idリストを取得）
[ステップ2] その主キーを使って、テーブル実体（クラスタ化インデックスのB+Tree）を再検索する（行フェッチ）

もし合致する件数が1万件あれば、1万回のランダムI/O がバッファプールやSSDに対して発生します。インデックスで絞り込んでいるにもかかわらずディスクI/Oがボトルネックになるのは、この「テーブルへの引き戻し」が原因です。

カバリングインデックス（Covering Index / Index-Only Scan）の実装

このランダムI/Oを完全に撲滅する武器が、カバリングインデックス です。クエリの実行に必要なすべてのカラム（SELECT句、WHERE句、ORDER BY句、GROUP BY句）をインデックスの中に包含させてしまいます。

-- SELECT句に含まれる total_amount もインデックスの末尾に追加する
CREATE INDEX idx_orders_covering ON orders (user_id, status, ordered_at, total_amount);

このインデックスを貼ると、クエリ実行に必要なデータがすべてセカンダリインデックスのリーフノード内に揃うため、テーブル本体へのアクセスが1回も発生しなくなります。

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
         type: range
          key: idx_orders_covering
        Extra: Using where; Using index -- ★ 「Using index」こそがカバリングインデックス達成の証！

PostgreSQLの場合、実行計画に Index Only Scan と表示されます。ディスクI/Oを完全にパスし、メモリ上のインデックスブロックを連続ストリーミング読み出しするだけになるため、スループットは通常のインデックス検索の数倍〜数十倍に跳ね上がります。

PostgreSQL ユーザー必携の「INCLUDE 句」

PostgreSQL 11以降を使用している場合、さらに洗練された手法が使えます。それが INCLUDE 句です。

-- PostgreSQL: 探索キーとペイロード（非キーカラム）を分離する
CREATE INDEX idx_orders_pg ON orders (user_id, status, ordered_at) 
INCLUDE (total_amount);

INCLUDE に指定されたカラムは、B+Treeのルートノードやブランチノード（分岐条件）には含まれず、最下層のリーフノードにのみ「おまけのデータ」として格納されます。

これにより、インデックスの木構造サイズが肥大化して探索効率が落ちるのを防ぎつつ、Index Only Scan の高速性を100%享受できるという、極めて合理的なアーキテクチャを実現できます。

6. 「とりあえず全部インデックス」の代償: 更新性能の劣化と断捨離

複合インデックスが強力だからといって、「あらゆる検索パターンの複合インデックスを量産する」のは極めて危険なアンチパターンです。

インデックスは無料の銀の弾丸ではありません。インデックスを1つ追加するごとに、以下のコストを払い続けることになります。

書き込み増幅（Write Amplification）: INSERT, UPDATE, DELETE が走るたび、テーブル本体だけでなくすべてのセカンダリB+Treeに対してノードの再配置、リーフの分割（Page Split）、ログ書き込みが発生します。更新ヘビーなテーブルにインデックスを貼りすぎると、書き込みスループットが激減します。
バッファプールの圧迫: インデックスのデータ量が増加すると、InnoDBバッファプール（メモリ）の貴重な領域をインデックスキャッシュが占有し、ホットなデータページがメモリから追い出されてディスクI/Oが頻発します。

重複・冗長インデックスを断捨離せよ

特に現場でよく見かける無駄が、最左プレフィックスで包含されている冗長な単一カラムインデックスです。

-- 冗長なインデックス定義の例
CREATE INDEX idx_user_id ON orders (user_id);
CREATE INDEX idx_user_status_date ON orders (user_id, status, ordered_at);

idx_user_status_date が存在する場合、WHERE user_id = 1042 単体のクエリであっても、この複合インデックスの最左カラムを使って全く問題なく高速探索できます。

したがって、idx_user_id という単一カラムインデックスは100%不要なゴミです。ディスク容量とINSERT性能を無駄に浪費しているだけなので、即座に DROP INDEX すべきです。

7. まとめ: 勘とノリのインデックス定義に別れを告げよ

データベースのチューニングにおいて、インデックスの貼り方ほど「エンジニアの設計力と基礎知識」が如実に現れる領域はありません。

フレームワークのORM（ActiveRecord、Prisma、TypeORMなど）がどれほど進化しても、物理的なRDBMSエンジンがB+Tree上でデータを走査しているという事実は変わりません。

複合インデックスは1次元の電話帳である: 最左プレフィックスを外したクエリには効かない。
範囲検索（Range）はインデックス探索の終着点である: 範囲条件を指定した瞬間、それ以降のカラムのB+Tree二分探索は停止する。
ESR原則（Equality → Sort → Range）を徹底せよ:
- 完全一致（`=`）のカラムを最左に
- ソート（`ORDER BY`）のカラムを次に
- 範囲条件（`>=`, `<`, `LIKE`）のカラムを末尾に
カバリングインデックスでテーブルI/Oを消滅させよ: SELECTに必要なカラムをリーフに含め、ランダムアクセスを根絶する。
冗長なプレフィックスインデックスは躊躇なく捨てよ: 書き込み性能とメモリ効率を犠牲にしてはならない。

スロークエリに直面したときは、闇雲にインデックスを増やすのをやめ、まず EXPLAIN を叩いて key_len と Extra を確認してください。そして、クエリの各条件を「E」「S」「R」に分類し、データがB+Tree上をどのように流れるかを頭の中でイメージする。その一手間が、あなたのシステムを何倍、何十倍もの高負荷に耐えうる堅牢なアーキテクチャへと押し上げるのです。