インデックス

B-Tree、実行計画、クエリ最適化

B-Tree インデックス

最も一般的なインデックス構造。バランス木で、検索・挿入・削除がO(log n)。

得意

• 等価検索（=）
• 範囲検索（<, >, BETWEEN）
• ORDER BY
• 前方一致（LIKE 'abc%'）

苦手

• 後方一致（LIKE '%abc'）
• 関数適用（WHERE LOWER(name) = ...）
• NULLの検索（IS NULL）
• 否定条件（!=, NOT IN）

B-Tree構造イメージ

Root: [30, 60]

[10, 20]

[40, 50]

[70, 80]

リーフノードにデータへのポインタ

インデックスの種類

タイプ	説明	ユースケース
単一カラム	1つのカラムに作成	頻繁に検索されるカラム
複合（Composite）	複数カラムの組み合わせ	複数条件での検索
ユニーク	重複を許さない	メールアドレス、ID
カバリング	必要なカラムを全て含む	テーブルアクセス回避
Full-text	全文検索用	テキスト検索
Hash	等価検索専用（PostgreSQL）	等価検索のみ、範囲不可
GIN/GiST	配列、JSONB、地理空間	複雑なデータ型

複合インデックスの順序

複合インデックスは左端から順に使用される（Leftmost Prefix Rule）

1 -- インデックス: (a, b, c)
2 
3 -- ✅ 使用される
4 WHERE a = 1
5 WHERE a = 1 AND b = 2
6 WHERE a = 1 AND b = 2 AND c = 3
7 WHERE a = 1 ORDER BY b
8 
9 -- ❌ 使用されない（aがない）
10 WHERE b = 2
11 WHERE b = 2 AND c = 3
12 WHERE c = 3
13 
14 -- ⚠️ 部分的に使用
15 WHERE a = 1 AND c = 3  -- aのみ使用
16 WHERE a > 1 AND b = 2  -- aの範囲検索後、bは使用されない

複合インデックスの設計指針

• 等価条件（=）のカラムを先に
• 範囲条件（<, >, BETWEEN）のカラムは後に
• カーディナリティが高いカラムを優先
• ORDER BYで使うカラムも考慮

実行計画（EXPLAIN）

1 -- PostgreSQL
2 EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
3 
4 -- 結果の見方
5 -- Seq Scan        : テーブルフルスキャン（遅い）
6 -- Index Scan      : インデックス使用、テーブルアクセスあり
7 -- Index Only Scan : カバリングインデックス（最速）
8 -- Bitmap Scan     : 複数条件のインデックス結合
9 
10 -- MySQL
11 EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
12 
13 -- type の見方（良い順）
14 -- const   : 主キー/ユニークで1行
15 -- eq_ref  : JOINで1行
16 -- ref     : インデックスで複数行
17 -- range   : インデックスで範囲
18 -- index   : インデックスフルスキャン
19 -- ALL     : テーブルフルスキャン（最悪）

インデックス作成

1 -- 基本
2 CREATE INDEX idx_users_email ON users(email);
3 
4 -- 複合インデックス
5 CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
6 
7 -- ユニークインデックス
8 CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
9 
10 -- 部分インデックス（PostgreSQL）
11 CREATE INDEX idx_orders_active ON orders(created_at)
12   WHERE status = 'active';
13 
14 -- 式インデックス（PostgreSQL）
15 CREATE INDEX idx_users_lower_email ON users(LOWER(email));
16 
17 -- 同時作成（ロックを最小化）
18 CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
19 
20 -- インデックス削除
21 DROP INDEX idx_users_email;
22 
23 -- インデックス確認
24 \d users                          -- PostgreSQL
25 SHOW INDEX FROM users;           -- MySQL

インデックス設計の原則

WHERE句で使うカラム

検索条件に頻繁に使われるカラムにインデックス

カーディナリティが高いカラム

ユニークな値が多いカラムほど効果的（email > status）

JOIN条件のカラム

外部キーにはインデックスを作成

ORDER BY / GROUP BYのカラム

ソート処理を回避できる

過剰なインデックスを避ける

INSERT/UPDATE/DELETEが遅くなる。ストレージも消費。

SRE/インフラ観点

本番でのインデックス追加

• PostgreSQL: CREATE INDEX CONCURRENTLY を使用
• MySQL: pt-online-schema-change または gh-ost を検討
• 大きなテーブルは事前にテスト環境で所要時間を確認

監視・メンテナンス

• スロークエリログの分析
• 未使用インデックスの特定と削除
• インデックスの断片化確認（REINDEX）
• 統計情報の更新（ANALYZE）

1	-- インデックス: (a, b, c)
2
3	-- ✅ 使用される
4	WHERE a = 1
5	WHERE a = 1 AND b = 2
6	WHERE a = 1 AND b = 2 AND c = 3
7	WHERE a = 1 ORDER BY b
8
9	-- ❌ 使用されない（aがない）
10	WHERE b = 2
11	WHERE b = 2 AND c = 3
12	WHERE c = 3
13
14	-- ⚠️ 部分的に使用
15	WHERE a = 1 AND c = 3 -- aのみ使用
16	WHERE a > 1 AND b = 2 -- aの範囲検索後、bは使用されない

1	-- PostgreSQL
2	EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
3
4	-- 結果の見方
5	-- Seq Scan : テーブルフルスキャン（遅い）
6	-- Index Scan : インデックス使用、テーブルアクセスあり
7	-- Index Only Scan : カバリングインデックス（最速）
8	-- Bitmap Scan : 複数条件のインデックス結合
9
10	-- MySQL
11	EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
12
13	-- type の見方（良い順）
14	-- const : 主キー/ユニークで1行
15	-- eq_ref : JOINで1行
16	-- ref : インデックスで複数行
17	-- range : インデックスで範囲
18	-- index : インデックスフルスキャン
19	-- ALL : テーブルフルスキャン（最悪）

1	-- 基本
2	CREATE INDEX idx_users_email ON users(email);
3
4	-- 複合インデックス
5	CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
6
7	-- ユニークインデックス
8	CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
9
10	-- 部分インデックス（PostgreSQL）
11	CREATE INDEX idx_orders_active ON orders(created_at)
12	WHERE status = 'active';
13
14	-- 式インデックス（PostgreSQL）
15	CREATE INDEX idx_users_lower_email ON users(LOWER(email));
16
17	-- 同時作成（ロックを最小化）
18	CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
19
20	-- インデックス削除
21	DROP INDEX idx_users_email;
22
23	-- インデックス確認
24	\d users -- PostgreSQL
25	SHOW INDEX FROM users; -- MySQL