インデックスの仕組みと設計原則：入門

概要

「SELECT が遅い」「件数が増えたら急に重くなった」——こうした悩みの多くはインデックス設計で解決できます。本記事では、RDB（MySQL / PostgreSQL を想定）のインデックスがどう動くのかを、B-Tree の仕組みから設計の判断軸まで入門者向けに整理します。公開情報と一般的な挙動に基づいた内容です。

そもそもインデックスとは

インデックスは、テーブルのデータ本体とは別に持つ「検索用の索引」です。本の巻末索引と同じで、「この値はどの行にあるか」という対応関係を、検索しやすい順序で保持します。

インデックスが無い場合、条件に合う行を探すにはテーブルを先頭から全部なめる必要があります（フルテーブルスキャン）。1万件なら一瞬でも、1000万件になると致命的に遅くなります。インデックスがあれば、索引をたどって目的の行へ最短で到達できます。

B-Tree の仕組み

多くのRDBの標準インデックスは B-Tree（正確には B+Tree）です。次の性質を持ちます。

• データがソートされた状態でツリー状に保持される
• ルートから葉へ数ホップで目的の値に到達する（計算量は O(log n)）
• 葉ノードが横に連結されているため、範囲検索やソート済み取得に強い

            [ 50 ]                 ← ルート
           /      \
      [20 35]    [70 90]           ← 中間ノード
      /  |  \    /  |  \
   葉: 10→15→20→...→95  (横に連結 = 範囲スキャンが速い)

100万件でもツリーの深さは数段で済むため、全件なめるのに比べて圧倒的に少ないアクセスで目的の行に届きます。

B-Tree が効く条件・効かない条件

強いのは以下です。

• 等価検索：WHERE user_id = 100
• 範囲検索：WHERE created_at >= '2026-01-01'
• 前方一致：WHERE name LIKE 'tanaka%'
• ソート：ORDER BY created_at

逆に以下は効きません。

• 後方一致・中間一致：LIKE '%keyword%'
• 列を加工した条件：WHERE YEAR(created_at) = 2026
• 否定条件：!= や NOT IN

YEAR(created_at) = 2026 は、created_at >= '2026-01-01' AND created_at < '2027-01-01' と範囲で書き換えるとインデックスが使えます。

原因の見方：実行計画

遅いクエリは、まず実行計画を見て「インデックスが使われているか」を確認します。

-- MySQL
EXPLAIN SELECT * FROM orders WHERE user_id = 100;

-- PostgreSQL（実測値つき）
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 100;

MySQL の EXPLAIN では、type が ALL（フルスキャン）か ref / range（インデックス利用）か、key にどのインデックスが選ばれたか、rows に何行調べる見込みかが分かります。ALL かつ rows が巨大なら要対策です。

設計方針：複合インデックスと列の順序

複数列で絞り込む場合、1列ずつ個別に張るより複合インデックスが効くことが多いです。ここで最重要なのが列の順序です。

複合インデックスは「電話帳」で考えると分かりやすいです。電話帳は「姓→名」の順に並んでいます。

• 「姓が佐藤」で引く：速い
• 「姓が佐藤 かつ 名が太郎」で引く：速い
• 「名が太郎」だけで引く：遅い（先頭の姓が決まらない）

これが 左端プレフィックス（leftmost prefix）ルールです。複合インデックス (A, B, C) は、A、A+B、A+B+C の条件には効きますが、B 単独や C 単独には効きません。

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

-- 効く：先頭列 user_id から使える
SELECT * FROM orders
 WHERE user_id = 100 AND created_at >= '2026-01-01';

-- 効きにくい：先頭の user_id が条件に無い
SELECT * FROM orders WHERE created_at >= '2026-01-01';

列順の原則は次のとおりです。

1. 等価条件（=）を先に、範囲条件（> < BETWEEN）を後に置く
2. 範囲条件の列より後ろの列は、ソート・絞り込みに効きにくい
3. カーディナリティ（値の種類の多さ）が高く、よく使う条件を優先する

実装例：ソート対応とカバリングインデックス

ソートまでインデックスに任せる

CREATE INDEX idx_posts_user_created ON posts (user_id, created_at);

-- インデックスが既にその順序で並んでいるのでソート不要
SELECT id, title, created_at
  FROM posts
 WHERE user_id = 100
 ORDER BY created_at DESC
 LIMIT 20;

カバリングインデックス

クエリが必要とする列がすべてインデックスに含まれていると、テーブル本体を見に行かずにインデックスだけで結果を返せます。

CREATE INDEX idx_posts_cover ON posts (user_id, created_at, title);

SELECT title, created_at
  FROM posts
 WHERE user_id = 100
 ORDER BY created_at DESC;

MySQL の EXPLAIN で Extra に Using index と出ればカバリングが効いています。含める列を増やすほどインデックスは太るので、使いどころは絞ります。

確認方法

• EXPLAIN / EXPLAIN ANALYZE で type / key / rows / Extra を確認する
• 想定したインデックスが key に選ばれているか
• Using filesort / Using temporary が出ていないか
• 本番相当のデータ量で検証する（少量データだとインデックスが使われず判断を誤る）

注意点（インデックスのコスト）

• 書き込みが遅くなる：INSERT / UPDATE / DELETE のたびにインデックスも更新される
• ディスクを消費する：実データとは別に容量を使う
• 使われないインデックスは純粋な負債：更新コストだけ払う
• カーディナリティが低い列は効きにくい：値が数種類しかない列は絞り込み効果が薄い
• オプティマイザが選ばないこともある：対象行が全体の大きな割合だとフルスキャンが選ばれる

原則は「よく実行され、かつ遅いクエリから逆算してインデックスを設計する」こと。先回りで何でも張るのではなく、実行計画で裏を取りながら必要なものを足していきます。

まとめ

• インデックスの主役は B-Tree。等価・範囲・前方一致・ソートに強い
• 関数加工・中間一致・否定条件では効かない
• 複合インデックスは列の順序が命。等価→範囲、左端プレフィックスを意識する
• ORDER BY やカバリングまで含めると、ソートやテーブルアクセスを省ける
• インデックスは書き込み・容量のコストを伴う負債でもある。遅いクエリから逆算し、EXPLAIN で裏を取る

まず自分のアプリで一番遅いクエリに EXPLAIN を打つところから始めると、設計の勘所がつかめます。