DBインデックス設計入門 — 仕組みから落とし穴まで

TL;DR

• インデックスは「データの索引」。なくても動くが、あると検索が桁違いに速くなる
• カラムを関数や CASE 式で加工した場合、インデックスは効かない
• 遅いクエリは EXPLAIN ANALYZE で実行計画を確認してから判断する

インデックスとは

インデックス（Index）はテーブルの特定カラムに対して作成する「索引」のこと。
本の巻末索引と同じ原理で、目的のデータをページ全体を読まずに素早く見つけられる。

インデックスがない場合、DBは条件に合うレコードを見つけるためにテーブルの全行を先頭から順に読む（フルテーブルスキャン）。
1万行なら問題ないが、100万・1000万行になると数秒〜数十秒単位の差になる。

インデックスの仕組み（B-tree）

PostgreSQLのデフォルトインデックスは B-tree（バランス木）構造で管理される。
値が昇順に並んだ木構造を二分探索するため、O(log N) でデータを見つけられる。

graph TD
    Root["50"]
    L["25"]
    R["75"]
    LL["10"]
    LR["35"]
    RL["60"]
    RR["90"]

    Root --> L
    Root --> R
    L --> LL
    L --> LR
    R --> RL
    R --> RR

id = 35 を探す場合：50 → 25 → 35 と3ステップで到達できる。
フルスキャンなら全7行を見るところを、B-tree なら深さ分（log₂N）で済む。

インデックスの作成

実務では CREATE TABLE の直後に手作業で CREATE INDEX を打つ、というシーンはあまりない。実際には次のどちらかの形で作られることが多い。

• マイグレーションファイルに含める：ORM・マイグレーションツール（Prisma の @@index、Rails の add_index、TypeORM の @Index() など）でテーブル定義と一緒にインデックスも宣言し、マイグレーション実行時に CREATE INDEX が発行される。外部キーや「検索条件になるとわかっているカラム」は設計段階でここに含めておく
• 後から追加する：運用開始後にクエリが遅くなり、EXPLAIN ANALYZE で実行計画を確認した結果 Seq Scan が原因だとわかったタイミングで、追加のマイグレーションとしてインデックスを足す

つまり CREATE INDEX 自体を直接手で打つ機会がなくても、マイグレーションの中で間接的に実行されていることがほとんどである。以下ではその生成される SQL の中身を見ていく。

単一カラムのインデックス

-- usersテーブルのemailカラムにインデックスを作成
CREATE INDEX idx_users_email ON users (email);

-- ユニーク制約（重複を禁止しつつインデックスも張る）
CREATE UNIQUE INDEX idx_users_email_unique ON users (email);

インデックス名は慣習として idx_<テーブル名>_<カラム名> の形式にすると管理しやすい。

複合インデックス（複数カラム）

複数カラムをまとめてインデックスにしたものを 複合インデックス（Composite Index）という。

-- user_id と ordered_at の複合インデックス
CREATE INDEX idx_orders_user_date ON orders (user_id, ordered_at);

複合インデックスにはカラム順が重要というルールがある。

graph LR
    subgraph ok ["効くパターン"]
        A["user_id = 1"] --> B["idx_orders_user_date"]
        C["user_id = 1<br/>AND ordered_at > X"] --> B
    end
    subgraph ng ["効かないパターン"]
        D["ordered_at > X のみ"] -. "先頭カラムなし" .-> E["フルスキャン"]
    end

インデックスの確認・削除

-- テーブルに張られているインデックスを確認（psql）
\d orders

-- インデックスの削除
DROP INDEX idx_orders_user_date;

インデックスが有利な場面・不要な場面

そもそもどういう流れで必要になるか

インデックスは最初から全テーブルに付けるものではなく、多くの場合「困ってから気づく」形で必要になる。典型的な流れは次の通り。

graph TD
    A["orders テーブルを作成\n（この時点では数百行）"] --> B["WHERE user_id = ? で\n注文履歴を検索するAPIを実装"]
    B --> C["リリース直後は速い\n（行数が少ないのでフルスキャンでも一瞬）"]
    C --> D["サービスが成長し\norders が数十万〜数百万行に増える"]
    D --> E["同じSELECT文が\n数百ms〜数秒かかるようになる"]
    E --> F["EXPLAIN ANALYZE で確認すると\nSeq Scan になっている"]
    F --> G["user_id にインデックスを追加\n（CREATE INDEX / マイグレーション）"]
    G --> H["Index Scan に変わり\n数msまで短縮"]

• 1〜3の時点ではインデックスが無くても問題は表面化しない（行数が少ないためフルスキャンでも速い）
• 4〜5でテーブルが育つにつれて「同じクエリなのに徐々に遅くなる」という形で問題が顕在化する
• 6の EXPLAIN ANALYZE で Seq Scan を確認して初めて「このカラムにインデックスが要る」と判断できる

つまりインデックスが必要になるのは「データ量が増えて、かつそのカラムで頻繁に絞り込みや結合をしている」テーブルであり、原因になるのは大抵 WHERE / JOIN / ORDER BY で使われているのにインデックスが無いカラムである。

有利な場面

• WHERE で絞り込む頻度が高いカラム（外部キー、status、ユーザーID など）
• ORDER BY や JOIN の結合キーに使うカラム
• 大量レコード（数十万〜）のテーブル

インデックスが不要・逆効果な場面

インデックスはデータ変更（INSERT / UPDATE / DELETE）のたびに更新コストが発生する。
以下のケースでは付けないほうがよい。

カーディナリティとは「カラムの値の多様度」。id は全行で異なるため高カーディナリティ、is_deleted（true/false の2値）は低カーディナリティとなる。

よくある落とし穴

1. カラムを関数で加工している

WHERE 句でカラムを関数に渡すと、インデックスの対象外になる。

-- NG: カラムを関数で包んでいる → インデックス使えない
SELECT * FROM users WHERE LOWER(email) = 'taro@example.com';

-- OK: 検索値のほうを変換する
SELECT * FROM users WHERE email = LOWER('TARO@EXAMPLE.COM');

どうしても関数を使いたい場合は 関数インデックス（Expression Index）を作成する：

CREATE INDEX idx_users_email_lower ON users (LOWER(email));

2. LIKE の前方一致以外

LIKE は前方一致（'田中%'）のみインデックスを使える。
後方・中間一致（'%田中%'）はフルスキャンになる。

-- OK: 前方一致はインデックスが効く
SELECT * FROM users WHERE name LIKE '田中%';

-- NG: 中間一致はインデックスが効かない
SELECT * FROM users WHERE name LIKE '%田中%';

全文検索が必要な場合は pg_trgm 拡張や Elasticsearch などの専用手段を検討する。

3. WHERE 句に CASE 式を使っている

WHERE 句の中で カラムを CASE 式で加工した場合、そのカラムのインデックスは効かない。
CASE 式は行を1件ずつ評価するため、インデックスで事前に絞り込む余地がなくなるからだ。

-- NG: statusカラムをCASEで変換してから比較している
--     → statusのインデックスが使えずフルスキャンになる
SELECT * FROM orders
WHERE CASE status
  WHEN 1 THEN 'active'
  WHEN 2 THEN 'pending'
  ELSE 'other'
END = 'active';

-- OK: カラムをそのまま比較する
SELECT * FROM orders WHERE status = 1;

複数条件の動的切り替えに CASE を使っているパターンも同様に問題になる。

-- NG: 検索対象カラムをCASEで切り替えている
--     → どちらのカラムにもインデックスが効かない
SELECT * FROM orders
WHERE 1 = CASE :target_type
  WHEN 'user'    THEN (user_id    = :target_id)::int
  WHEN 'product' THEN (product_id = :target_id)::int
  ELSE 0
END;

4. DATE 関数で日付を切り捨てて BETWEEN している

タイムスタンプ型のカラムに対して DATE() で日付部分だけ取り出してから BETWEEN で範囲検索するパターンも、カラムを関数で加工しているためインデックスが効かない。

-- NG: DATE()でカラムを加工している → インデックス無効
SELECT * FROM orders
WHERE DATE(ordered_at) BETWEEN '2024-01-01' AND '2024-01-31';

カラムをそのまま使い、範囲を不等号で直接指定するとインデックスが効く。

-- OK: カラムを加工せず、範囲を不等号で指定する
SELECT * FROM orders
WHERE ordered_at >= '2024-01-01'
  AND ordered_at  < '2024-02-01';

終端を <= ではなく < 翌日 にすることで 23:59:59 のレコードも確実に含められる。
BETWEEN '2024-01-01' AND '2024-01-31' だと 2024-01-31 00:00:00 より後のレコードが漏れるため、タイムスタンプ型では不等号形式のほうが安全でもある。

EXPLAIN ANALYZE で実行計画を確認する

インデックスが本当に使われているかは EXPLAIN ANALYZE コマンドで確認できる。

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 1;

出力例（インデックスが効いている場合）：

Index Scan using idx_orders_user_date on orders
  Index Cond: (user_id = 1)
  Actual Rows: 42  Loops: 1
  Actual Total Time: 0.089 ms

出力例（フルスキャンになっている場合）：

Seq Scan on orders
  Filter: (user_id = 1)
  Actual Rows: 42  Loops: 1
  Actual Total Time: 320.4 ms

Seq Scan で時間が長ければインデックスの追加を検討する。
Index Scan でも遅い場合は、インデックスの選択や複合インデックスの順序を見直す。

関連

• PostgreSQL入門 — 基本SQL完全ガイド