インデックス設計のトレードオフと設計レビュー
索引は書き込み・容量・バッファ・メンテの 4 コストを払う。本数の目安と未使用監視、レビュー用チェックリスト。
索引が払う 4 つのコスト
索引は「読み取りを速くする」代わりに複数のコストを払います。設計段階で 4 つを意識しておきましょう。
| コスト | 影響 | 軽減策 |
|---|---|---|
| 書き込みコスト | INSERT / UPDATE / DELETE ごとに索引を更新。索引数に比例 | 本数を絞る、更新しない列を選ぶ |
| ディスク容量 | テーブル本体と同等以上のサイズになり得る | 部分インデックス、INCLUDE の絞り込み |
| バッファプール圧迫 | 索引ページがキャッシュを食い、テーブル本体が追い出される | 未使用索引の削除、索引サイズの監視 |
| メンテナンス | REINDEX / CONCURRENTLY / オンライン DDL ツールの運用工数 | 本数を絞る、分割して実施 |
読み取り側の恩恵より書き込み側の負担が大きい索引は、むしろ有害です。「貼る前に、この索引を使うクエリは何か」を必ず答えられる状態で設計に入れましょう。
本数の目安 — 1 テーブル 5〜7 本
目安として、1 テーブルあたりの索引は PK を含めて 5〜7 本程度が上限感覚です。ワークロードで変動しますが、これを超えると書き込み性能の劣化やバッファプール圧迫が顕在化しやすくなります。
- read-heavy（集計・レポート系）: やや多めでも可。ただし書き込み性能を定期計測
- write-heavy（ログ・IoT・メッセージング）: 索引は最小限に。3〜4 本で踏みとどまる
- mixed（一般的な業務）: 5〜7 本を上限とし、追加するたびに 1 本削れないか検討
既に 10 本以上ある場合は、次を確認します。
- 先頭列が同じ複合索引と単列索引の重複（
(a, b)があれば(a)は不要） - 使われていない索引（
pg_stat_user_indexes/sys.schema_unused_indexes） - 同じクエリパターンに対する複数の近似索引（整理統合できないか）
詳細な重複検知と未使用削除の手順は インデックスの罠 に。
CREATE INDEX CONCURRENTLY / オンライン DDL
本番稼働中のテーブルに索引を追加するときは、テーブルをロックせずに作る手順が必須です。RDBMS ごとの手段を知っておくと、設計時の「あとから追加」判断が変わります。
| RDBMS | 非ロック手段 | 注意点 |
|---|---|---|
| PostgreSQL | CREATE INDEX CONCURRENTLY | 通常より遅く、失敗すると INVALID 状態で残る → 削除して再実行 |
| MySQL (InnoDB) | 8.0+ のオンライン DDL（ALGORITHM=INPLACE, LOCK=NONE） | UNIQUE の場合は SHARE ロックが入る |
| MySQL (大規模) | pt-online-schema-change / gh-ost | トリガーによるコピーベース。ディスク容量 × 2 が必要 |
| SQL Server | WITH (ONLINE = ON) | Enterprise Edition のみ |
| Oracle | ONLINE オプション | Enterprise Edition 以上 |
いずれも通常の CREATE INDEX より時間とリソースを使います。「後から貼れるから設計時は適当で OK」ではなく、「後から貼るのも高コスト」という前提で初期 DDL を詰める方が結局安上がりです。
未使用インデックスの監視
「使われていないのに維持コストだけ払っている索引」は意外と多く、定期監査の対象です。各 RDBMS で未使用検出の手段があります。
-- PostgreSQL
SELECT schemaname, relname, indexrelname, idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;
-- MySQL 8.0+
SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes;判断の注意点:
- 統計はサーバー再起動でリセットされる → 数週間以上の運用観察が必要
- 月次の集計バッチが使う索引は、計測タイミングでゼロに見える → 業務サイクルを考慮
- UNIQUE 制約用の索引は「使われていなくても」制約のために残す必要がある
本気で削除する前に、「非アクティブ化（RENAME して残す）」してしばらく様子を見るのが安全です。
設計レビュー用チェックリスト
新規テーブル / 既存テーブル拡張の設計レビューで、索引観点を漏らさないためのチェックリスト。
テーブル単位
- [ ] 主キーは時間順に増えるか（BIGINT AUTO_INCREMENT / UUID v7）
- [ ] 想定される典型クエリが 3 件以上書き出してあるか
- [ ] 各クエリを支える索引が明示的に設計されているか
- [ ] 合計索引本数は 5〜7 本以内か（PK 含む）
- [ ] 先頭列が同じ複合と単列の重複がないか（
(a, b)vs(a)）
列単位
- [ ] 全 FK 列に索引があるか（PostgreSQL / SQL Server / Oracle は手動）
- [ ] 業務キーの UNIQUE 制約があるか
- [ ] 論理削除を使う場合、部分インデックス（
WHERE deleted_at IS NULL）を検討したか - [ ] マルチテナントの場合、
tenant_idを全索引の先頭に含めたか
クエリ単位
- [ ]
WHERE句の列が索引の先頭列と一致しているか（左端一致） - [ ]
ORDER BY+LIMITが索引のソート順で完結するか - [ ] 深いページネーションはキーセット方式になっているか
- [ ]
ORDER BYに tie-breaker（PK）が入っているか
このチェックリストを PR / MR テンプレートに入れておくと、レビューの抜け漏れが減ります。
まとめ — 索引は設計の一部
このシリーズで扱った内容は、すべてテーブルを作る前・作る瞬間に決めるべき索引の形です。後から遅くなってから慌てて貼るのではなく、設計レビューの対象として最初に詰めることで、長期の保守コストを桁で減らせます。
- 設計時にインデックスを考える理由 — このページで決めるマインドセット
- 種別の選び方 — B-Tree 以外の選択肢
- 主キー設計 — InnoDB clustered の特殊事情
- 複合列順 — 等値 → 範囲 → ソート
- カバリング戦略 — INCLUDE の使い分け
- FK・リレーション — 子側索引必須
- 部分・式 — 論理削除・テナント・LOWER
- ページング — キーセット方式
- トレードオフと設計レビュー — このページ
既に動いているクエリの最適化や EXPLAIN の読み方は、姉妹シリーズの チューニング編 を併せて参照してください。