第19章インデックス設計入門 — 検索を速くする張り方と落とし穴

この章で学ぶこと

インデックスが検索を速くする仕組みを、全表走査との対比で説明できる
「自動で作られるインデックス」と「自分で張るインデックス」を区別できる
どのカラムに張るか (WHERE / JOIN / ORDER BY) を、クエリを見て判断できる
複合インデックスのカラム順序を設計できる
EXPLAIN でインデックスが使われたかを確認できる
インデックスの張りすぎがなぜ害になるかを説明できる

前提知識

第 10 章キー概念と第 13 章設計の実践を理解していること。本章は「インデックスの内部実装」ではなく「設計時にどう張るか」に絞ります。

インデックスがないと何が起きるか

インデックスのないテーブルで WHERE email = 'yamada@example.com' を検索すると、データベースは全行を先頭から順に読んで条件に合うか調べます。これを全表走査 (sequential scan) と呼びます。

10 行のテーブルなら一瞬ですが、100 万行なら 100 万回の比較が必要です。ユーザーが増えるほど検索は遅くなり、「リリース直後は速かったのに、半年後に急に遅くなった」という典型的な性能問題になります。

インデックス (index) は、特定のカラムの値を並び替えて保持する索引データです。本の巻末索引で「用語 → ページ番号」を引くように、「値 → 行の位置」を直接引けるため、全行を読む必要がなくなります。

PostgreSQL の標準インデックスは B-Tree という木構造です。値がソートされた状態で保持されるため、辞書を引くときのように「真ん中を開いて、前半か後半かを判断する」操作を繰り返して目的の値に到達します。比較回数は行数 N に対して log N のオーダーで済み、100 万行でも 20 回程度の比較で目的の行を特定できます (木構造の詳細な内部実装は本ガイドの範囲外です。設計判断には「ソート済みだから等値検索・範囲検索・並び替えに強い」という性質だけ覚えれば足ります)。

インデックスの作成 (PostgreSQL)
CREATE INDEX idx_users_email ON users(email);

自動で作られるインデックス、作られないインデックス

すべてのインデックスを自分で張るわけではありません。制約によっては自動で作られます。

対象	インデックス	根拠
`PRIMARY KEY`	自動で作られる	一意性の検査に索引が必要なため (PostgreSQL: CREATE TABLE)
`UNIQUE` 制約	自動で作られる	同上
`FOREIGN KEY` (参照する側)	作られない	PostgreSQL は外部キー列に索引を張らない (PostgreSQL: 制約)。MySQL (InnoDB) は自動で張る

見落としやすいのは 3 行目です。orders.user_id のような外部キー列は「このユーザーの注文一覧」のような JOIN・絞り込みで頻繁に使われるのに、PostgreSQL では自動で索引が張られません。さらに、親側 (users) の行を削除・更新するとき、データベースは「参照している子がいないか」を子側で探すため、外部キー列に索引がないと親の削除まで遅くなります。

外部キー列には自分でインデックスを張る
CREATE TABLE orders (
    id           BIGSERIAL PRIMARY KEY,                    -- 自動でインデックスあり
    user_id      INTEGER NOT NULL REFERENCES users(id),    -- 自動では張られない
    status       VARCHAR(20) NOT NULL DEFAULT 'pending',
    ordered_at   TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_orders_user_id ON orders(user_id);        -- 自分で張る

どのカラムに張るか

「とりあえず全カラムに張る」は誤りです (理由は後述の「張りすぎの害」)。判断の起点は実際に発行するクエリで、次の 3 つの場所に登場するカラムが候補になります。

クエリでの登場場所	例	張る理由
`WHERE` の絞り込み条件	`WHERE email = ?` / `WHERE ordered_at >= ?`	全表走査を索引検索に変える
`JOIN` の結合条件 (≒ 外部キー)	`JOIN orders ON orders.user_id = users.id`	結合のたびに相手行を高速に特定する
`ORDER BY` の並び替え	`ORDER BY created_at DESC LIMIT 10`	ソート済みの索引を先頭から読むだけで済み、全件ソートを省ける

逆に、登場しても効果が薄いカラムがあります。値の種類が極端に少ないカラムです。たとえば is_active (TRUE / FALSE の 2 種類) 単独に張っても、TRUE の行が全体の半分あるなら索引で絞れるのは半分だけで、結局大量の行を読むことになります。データベースはこの見積もりを統計情報から行い、「索引を使うより全表走査のほうが速い」と判断すれば索引を無視します。

設計時の目安

テーブル設計の段階では「主キー (自動) + 外部キー (手動) + UNIQUE な業務キー (自動)」を押さえておき、それ以外は主要クエリが決まった時点で WHERE / ORDER BY を見て追加するのが現実的です。設計テンプレート集のインデックスチェックリストをレビュー時に使ってください。

複合インデックス — カラムの順序が命

複数カラムをまとめた 1 本のインデックスを複合インデックス (composite index) と呼びます。

ユーザーごとの注文履歴を新しい順に出すクエリ
SELECT * FROM orders
WHERE  user_id = 42
ORDER BY ordered_at DESC
LIMIT 10;

このクエリに最適な複合インデックス
CREATE INDEX idx_orders_user_ordered ON orders(user_id, ordered_at);

複合インデックスは「姓、名」の順で並んだ名簿と同じ構造です。名簿は「姓が同じ人の中で、名の順に並ぶ」ので、姓だけの検索や姓 + 名の検索には使えますが、名だけの検索には使えません (全ページをめくることになる)。

これを左端プレフィックスの原則と呼びます。(user_id, ordered_at) のインデックスは:

クエリ	効率よく使えるか
`WHERE user_id = 42`	◯ 左端のカラムだけの検索
`WHERE user_id = 42 AND ordered_at >= '2026-01-01'`	◯ 左端から順に使う検索
`WHERE user_id = 42 ORDER BY ordered_at DESC`	◯ 絞り込み + 並び替えを 1 本でカバー
`WHERE ordered_at >= '2026-01-01'`	✕ 左端 (`user_id`) を飛ばした検索は、この索引をほぼ使わない (全体を舐める非効率な使い方は理論上可能だが、実行計画はまず選ばない)

順序を決める実務上の目安は「等値 (=) で絞るカラムを先、範囲 (>= / BETWEEN) や並び替えに使うカラムを後」です。先に等値で 1 ユーザーに絞れば、その中は ordered_at 順に並んでいるので範囲もソートもそのまま索引で処理できます。逆順の (ordered_at, user_id) にすると、日付範囲に該当する全ユーザーの行を読んでから user_id で選り分けることになり、絞り込みの効率が落ちます。

なお、(user_id, ordered_at) があれば user_id 単独のインデックスは不要です (左端プレフィックスとして機能するため)。重複した索引は後述の「張りすぎの害」だけを増やします。

部分インデックス — 条件付きで小さく張る

WHERE 条件付きで作るインデックスを部分インデックス (partial index) と呼びます。論理削除 (第 13 章) を採用したテーブルと相性がよく、「生きている行」だけを索引に載せられます。

未削除の行だけを対象にする部分インデックス
CREATE INDEX idx_users_email_active ON users(email) WHERE deleted_at IS NULL;

削除済みの行が積み上がっても索引は小さいまま保たれ、WHERE deleted_at IS NULL AND email = ? というアプリの定番クエリにそのまま効きます。第 14 章で使った部分一意インデックス (UNIQUE INDEX ... WHERE is_primary = TRUE) は、この仕組みを一意性制約に応用したものです。

EXPLAIN — インデックスが効いているか確かめる

張ったインデックスが実際に使われているかは、推測ではなく EXPLAIN で確認します。クエリの前に付けるだけで、データベースの実行計画が表示されます。

EXPLAIN SELECT * FROM orders WHERE user_id = 42;

実行計画の例 (要点のみ)
Index Scan using idx_orders_user_id on orders  (cost=0.43..8.45 rows=1 width=52)
  Index Cond: (user_id = 42)

最初に読むのは 1 行目の走査方式です。

表示	意味
`Seq Scan`	全表走査。大きいテーブルの絞り込みでこれが出たら、インデックス追加を検討する合図
`Index Scan`	インデックスで行を特定してから本体を読んでいる
`Index Only Scan`	インデックスだけで結果を返せている (本体を読まない、最速のパターン)

EXPLAIN ANALYZE にすると実際に実行して実測時間も表示されます (UPDATE / DELETE に付けると本当に実行されるので、トランザクションで包んで ROLLBACK してください)。

インデックスがあるのに Seq Scan になる典型は 3 つあります。

行数が少ない: 数百行程度なら全表走査のほうが速いと判断されます。正常な動作で、対処は不要です
カラムに関数をかけている: WHERE lower(email) = ? は email のインデックスを使えません (索引には元の値が並んでいるため)。式インデックス CREATE INDEX ... ON users(lower(email)) を張るか、クエリ側を変えます
先頭ワイルドカードの LIKE: WHERE name LIKE '%山田%' は「並び順の先頭」が決まらないため B-Tree を使えません (全文検索の領域になり、本ガイドの範囲外です)

張りすぎの害

インデックスは読み取りを速くする代わりに、書き込みのたびに索引自体の更新コストを払います。INSERT されるたび、すべてのインデックスに新しいエントリを正しい位置へ挿入する必要があるためです。インデックスが 10 本あるテーブルへの INSERT は、本体 1 回 + 索引 10 回の書き込みになります。

害	内容
書き込みの低速化	`INSERT` / `UPDATE` / `DELETE` のたびに全インデックスを更新する
ストレージ消費	インデックスは実体を持つデータ構造で、本体と同規模まで膨らむこともある
使われない索引の放置	クエリが変わって使われなくなっても、コストだけ払い続ける

「検索で使うと分かっているカラムに張る。使うか分からないカラムには張らず、遅いクエリが観測されてから EXPLAIN で確認して追加する」が基本姿勢です。どのインデックスが使われていないかの調査 (統計ビューの監視) は運用の話になるため、本ガイドでは範囲外とします。

UUID 主キーとインデックス局所性

第 10 章で「URL に ID を出すなら連番より UUID」という使い分けを紹介しました。インデックスの観点では、UUID には注意点が 1 つあります。

連番 (SERIAL / IDENTITY) の主キーは値が単調に増えるため、B-Tree の末尾に追記され続けます。一方、gen_random_uuid() が生成する UUIDv4 は完全にランダムなため、挿入位置が索引全体に散らばります。ページ (索引の内部ブロック) のあちこちで分割が発生し、書き込みが多いテーブルでは索引の断片化と肥大化が進みます。

この弱点を解消するのが UUIDv7 (RFC 9562、2024 年 5 月) です。先頭 48 ビットにタイムスタンプを持つため、生成順にほぼ整列した値になり、連番と同じように索引の末尾へ追記されます。PostgreSQL 18 からは組み込みの uuidv7() 関数で生成でき、それ以前のバージョンでもアプリケーション側のライブラリで生成できます。

使い分けの目安

推測されない ID が必要 + 書き込みが多い → UUIDv7
既存システムで UUIDv4 を使用中 → 性能問題が観測されるまでは移行不要 (EXPLAIN と書き込み速度で判断)
内部だけで使う ID → 連番 (BIGSERIAL / IDENTITY) が最も単純で高速

演習

演習 1: どこに張るか

ブログサービスの posts テーブルに対して、アプリは次の 3 つのクエリを頻繁に発行します。追加すべきインデックスを考えてください (主キーは id BIGSERIAL PRIMARY KEY で定義済み)。

-- (a) 著者の記事一覧を新しい順に
SELECT * FROM posts WHERE author_id = ? ORDER BY published_at DESC LIMIT 20;

-- (b) スラッグで 1 件取得
SELECT * FROM posts WHERE slug = ?;

-- (c) 公開済み記事の総数
SELECT COUNT(*) FROM posts WHERE status = 'published';

解答例

-- (a) 向け: 等値で絞る author_id を先、並び替えの published_at を後にした複合
CREATE INDEX idx_posts_author_published ON posts(author_id, published_at);

-- (b) 向け: slug は 1 記事 1 つの業務キーなので UNIQUE 制約で張る (索引も自動で付く)
ALTER TABLE posts ADD CONSTRAINT uq_posts_slug UNIQUE (slug);

-- (c) 向け: status は値の種類が少ないので単独インデックスの効果は限定的。
--     「公開済みだけ」を数えるなら部分インデックスが選択肢
CREATE INDEX idx_posts_published ON posts(published_at) WHERE status = 'published';

なぜそうなるか:

(a) は「等値 → 並び替え」の順序で 1 本にまとめると、絞り込みとソートの両方を索引だけで処理できる
(b) は検索の高速化と「スラッグの重複禁止」という業務ルールを UNIQUE 制約 1 つで両取りできる
(c) の status のような低カーディナリティ列は、単独で張っても絞り込み効果が薄い。部分インデックスにすれば「公開済みの行」だけの小さな索引になる

ありがちな間違い: (a) 用に author_id と published_at の単独インデックスを 2 本張ること。単独索引 2 本では「絞り込んでからソート済み」という状態を作れず、複合 1 本より効率が落ちます。また (author_id, published_at) があれば author_id 単独の索引は左端プレフィックスで代用できるため不要です。

演習 2: 使われるか判定

CREATE INDEX idx_users_email ON users(email); があるとき、次のクエリはこのインデックスを使えるでしょうか。

SELECT * FROM users WHERE email = 'a@example.com';
SELECT * FROM users WHERE lower(email) = 'a@example.com';
SELECT * FROM users WHERE email LIKE '%@example.com';

解答例

番号	判定	理由
1	◯ 使える	索引に並んでいる値そのままの等値検索
2	✕ 使えない	索引には `lower()` をかける前の値が並んでいる。`lower(email)` の式インデックスを別途作れば使える
3	✕ 使えない	先頭が `%` だと並び順の起点が決まらず、B-Tree を辿れない

確かめ方: 推測せず EXPLAIN を付けて実行し、Index Scan / Seq Scan のどちらが表示されるかを見るのが確実です。

まとめ

この章で学んだことを整理します。

インデックスは「並び替え済みの索引」で、全表走査を log N オーダーの検索に変える
主キー・UNIQUE 制約には自動で索引が付くが、外部キー列には PostgreSQL では付かない (自分で張る)
張る候補は WHERE / JOIN / ORDER BY に登場するカラム。値の種類が少ないカラム単独は効果が薄い
複合インデックスは左端プレフィックスの原則に従い、「等値で絞るカラムを先、範囲・並び替えを後」に置く
効いているかは EXPLAIN で確認する (Seq Scan が出たら見直しの合図)
インデックスは書き込みコストとストレージを消費する。使うと分かっているものだけ張り、あとは観測してから足す
書き込みの多いテーブルで推測不能な ID が必要なら、UUIDv4 より UUIDv7 が索引に優しい

この章で学ぶこと​

インデックスがないと何が起きるか​

自動で作られるインデックス、作られないインデックス​

どのカラムに張るか​

複合インデックス — カラムの順序が命​

部分インデックス — 条件付きで小さく張る​

EXPLAIN — インデックスが効いているか確かめる​

張りすぎの害​

UUID 主キーとインデックス局所性​

演習​

まとめ​