この記事の要点

Index は SELECT を速くする一方、INSERT/UPDATE/DELETE を遅くする トレードオフがある B-Tree がデフォルト。範囲検索・ソート・前方一致 LIKE に強い Composite Index は左端から使われる (Leftmost Prefix Rule) Covering Index はクエリで必要な列を全部含むインデックス → Index-Only Scan で高速 カーディナリティ低い列 (性別、フラグ等) には Bitmap Index またはインデックス不要

Index (索引) とは

DB の Index は、テーブルから特定の行を高速に見つけるためのデータ構造です。本の巻末索引と同じで、「email = ? で 1 件取りたい」というクエリに対し、全行 (Full Table Scan) ではなく Index 経由で対数時間 O(log N) で到達できます。

ただし万能ではなく、INSERT / UPDATE / DELETE 時に Index 自身も更新されるため、書き込みヘビーなシステムでは逆効果になる場合もあります。「むやみに全列に Index 張る」は典型的なアンチパターンです。

主要な Index 種別

種類	得意	代表 DB
B-Tree	等価 / 範囲 / ソート / 前方一致 LIKE	★ 全 DB のデフォルト
Hash	等価のみ (高速)	MySQL Memory, PostgreSQL
Bitmap	カーディナリティ低い列、OLAP	Oracle, PostgreSQL (実装は GIN/BRIN)
Full Text	自然言語の全文検索	MySQL, PostgreSQL (tsvector)
GiST / GIN	配列 / JSON / 地理空間	PostgreSQL
R-Tree / SP-GiST	地理空間 / 多次元	PostgreSQL, SQLite
Cluster (Index Organized Table)	主キー順に物理配置	InnoDB の Primary Key, Oracle IOT

B-Tree Index の仕組み

                  [50]
                 /     \
            [20|35]    [70|85]
           /   |   \   /  |  \
      [10] [25,30] [40] [60] [75,80] [90,95]

特徴:
- 内部ノードは範囲、葉ノードは実データ (or 行 ID) へのポインタ
- 深さ 3-4 で数億行をカバー (平衡木)
- 範囲検索: 葉ノードを横にリンクで辿る (Linked List)
- 前方一致 LIKE 'abc%' は使えるが LIKE '%abc' は使えない

Index の作成・確認

-- 単一列
CREATE INDEX idx_users_email ON users(email);

-- 複合 (Composite) Index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- ユニーク
CREATE UNIQUE INDEX uq_users_login_id ON users(login_id);

-- 部分 Index (PostgreSQL / SQL Server)
CREATE INDEX idx_orders_active ON orders(user_id)
WHERE status = 'active';

-- 関数 Index (式)
CREATE INDEX idx_lower_email ON users(LOWER(email));

-- 降順
CREATE INDEX idx_logs_date_desc ON logs(created_at DESC);

-- 削除
DROP INDEX idx_users_email;       -- PostgreSQL / Oracle
ALTER TABLE users DROP INDEX idx_users_email;  -- MySQL

Composite Index と Leftmost Prefix Rule

複数列 Index は左端から順にのみ使われます。これは B-Tree が複合キーをタプル順に並べるためです:

-- インデックス: (a, b, c)
CREATE INDEX idx_abc ON tbl(a, b, c);

-- ✅ 使える
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND c = 3                -- 部分的 (a だけ使用、c はフィルタ)

-- ❌ 使えない (a が条件に無い)
WHERE b = 2
WHERE b = 2 AND c = 3
WHERE c = 3

列順はカーディナリティ高い → 低い順 + 検索パターンで決定。「絞り込み量の多い列」を先頭にするのが基本。

Covering Index と Index-Only Scan

クエリで使う列 (SELECT / WHERE / ORDER BY) が全て Index に含まれていれば、テーブル本体を読まずに Index だけで結果を返せます (Index-Only Scan):

-- テーブル
CREATE TABLE orders (id, user_id, status, total, created_at, ...);

-- ❌ Covering でない
CREATE INDEX idx_user ON orders(user_id);
SELECT total FROM orders WHERE user_id = 100;  -- Index → 行 → total 取得

-- ✅ Covering (PostgreSQL の INCLUDE)
CREATE INDEX idx_user_cov ON orders(user_id) INCLUDE (total);
SELECT total FROM orders WHERE user_id = 100;  -- Index だけで完結

-- ✅ MySQL では INCLUDE が無いので順序を工夫
CREATE INDEX idx_user_cov ON orders(user_id, total);
SELECT total FROM orders WHERE user_id = 100;

EXPLAIN で Index 利用を確認

-- MySQL / PostgreSQL
EXPLAIN SELECT * FROM users WHERE email = 'foo@example.com';

-- PostgreSQL の実測値
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'foo@example.com';

-- 注目ポイント
-- Seq Scan (Postgres) / type=ALL (MySQL) → Full Scan、Index 効いてない
-- Index Scan / type=ref or range          → Index 使用
-- Index Only Scan                         → Covering Index 効いてる
-- rows=10  cost=...                       → 推定行数とコスト

カーディナリティと Index Selectivity

カーディナリティ (Cardinality) = 列に含まれるユニーク値の数。Selectivity = カーディナリティ / 全行数。

列の種類	カーディナリティ	Selectivity	B-Tree Index 効果
email, uuid	非常に高い	1.0 近い	★ 抜群
user_id	高い	0.1 以上	★ 効く
status (5 値)	低い	0.0005	状況次第 (Partial / Bitmap 検討)
gender, flag	2 値	0.5	無効 (Full Scan が速い)

Partial Index / 部分インデックス

「全データの 1% にしかヒットしない」場合、その 1% だけを Index に含めると容量も保守コストも激減:

-- PostgreSQL
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';   -- pending 行だけ

-- 効果
-- ・Index サイズ激減 (全 1000 万 → 1 万)
-- ・INSERT/UPDATE 時の Index 更新コスト減
-- ・status = 'pending' を含むクエリでのみ自動利用

Bitmap Index (Oracle)

カーディナリティの低い列に対し、各値ごとに「該当行=1, それ以外=0」のビットマップで持つ。AND/OR をビット演算で実行でき DWH の集計に強い:

-- Oracle のみ
CREATE BITMAP INDEX idx_status_bm ON orders(status);

-- 適用条件
-- ・カーディナリティ < 全行数の 1% 程度
-- ・読み取り中心 (DWH / 分析)
-- ・OLTP には不向き (同時更新でロック広範囲)

Full Text Index

-- MySQL
CREATE FULLTEXT INDEX idx_ft_body ON articles(body) WITH PARSER ngram;
SELECT * FROM articles
WHERE MATCH(body) AGAINST('PostgreSQL 速い' IN NATURAL LANGUAGE MODE);

-- PostgreSQL (tsvector)
CREATE INDEX idx_ft_body ON articles USING GIN (to_tsvector('english', body));
SELECT * FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('postgres & fast');

Cluster Index (Index Organized Table)

テーブル本体が Index の葉ノードに直接格納される形式。InnoDB は主キーが必ず Cluster Index になります:

• 主キー検索が高速 (1 回の Index 探索でデータ到達)
• 主キー以外の Index (Secondary Index) は「主キー値」を持ち、2 段階探索になる
• 主キーは狭く・連番が望ましい (UUID v4 を主キーにすると断片化激しい)

Index 維持コスト

操作	Index 無し	Index N 個
SELECT (該当列)	遅い (Full Scan)	★ 速い
INSERT	速い	遅い (N 個 B-Tree 更新)
UPDATE (Index 列以外)	普通	普通
UPDATE (Index 列)	普通	遅い (古エントリ削除 + 新規)
DELETE	普通	遅い (N 個更新)
ディスク容量	少	2-3 倍に膨らむ事も

Index 設計のチェックリスト

1. 主キー / 外部キーには必ず Index
2. WHERE / JOIN / ORDER BY の主要列を Index 化
3. Composite Index は左端順に注意
4. カーディナリティが低い列の単独 Index は避ける
5. Covering Index で Index-Only Scan を狙う
6. 不要 Index は pg_stat_user_indexes / sys.dm_db_index_usage_stats で発見して削除
7. 本番では CREATE INDEX CONCURRENTLY (PostgreSQL) で無停止作成

FAQ

Q: Index を貼ったのに使われない
A: 統計情報が古い (ANALYZE 実行)、列の型が条件と違う (暗黙変換)、関数を当てている (WHERE YEAR(date) = 2024 は使えない)、選択率が悪い (全行の 30% ヒットなら Full Scan の方が速い) など。

Q: 主キーと Unique Index の違い
A: 主キーは「テーブルに 1 つ、NULL 不可」、Unique Index は「複数可、NULL は 1 行のみ許容 (DB により挙動差)」。両方ともユニーク制約 + Index の機能を持ちます。

Q: 何個まで Index を貼って良い？
A: 目安はテーブル当たり 5-7 個。書込頻度が高いなら 3 個程度。多すぎる場合はクエリパターン整理を先に。

Q: Index Rebuild は必要？
A: PostgreSQL では REINDEX、MySQL InnoDB では OPTIMIZE TABLE で断片化解消。AutoVacuum / ALTER TABLE Online がある現代では年単位の保守で十分。