この記事の要点

CREATE INDEX idx ON t(col) で B-Tree インデックスを作成（デフォルト） UNIQUE INDEX は重複禁止。NULL は許容 (DB により挙動差) PostgreSQL は USING btree / hash / gist / gin / brin でアルゴリズム指定可 複合インデックス (Composite) はカラム順が WHERE / ORDER BY と一致したときに最も効く Partial Index (PG/SQL Server): WHERE 句付きで一部行だけ索引化 → 軽量 Function-based Index: CREATE INDEX ON t(LOWER(email)) で関数結果に索引 ALTER INDEX ... REBUILD で再構築。断片化解消・統計再計算

基本: CREATE INDEX

-- 1 カラム索引（B-Tree がデフォルト）
CREATE INDEX idx_users_email ON users(email);

-- 一意制約付き
CREATE UNIQUE INDEX uq_users_email ON users(email);

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

-- 複合インデックス
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- スキーマ指定
CREATE INDEX shop.idx_p_name ON shop.products(name);

-- コメント付き
CREATE INDEX idx_t_x ON t(x) COMMENT 'Hot path for /search';

UNIQUE INDEX と UNIQUE 制約の違い

項目	UNIQUE INDEX	UNIQUE 制約
記述	CREATE UNIQUE INDEX	ALTER TABLE ADD CONSTRAINT
NULL の扱い	DB による（Oracle: 複数 NULL OK / SQL Server 2008+: WHERE col IS NOT NULL で部分索引化）	DB による（基本は複数 NULL OK）
外部キー参照	UNIQUE 制約のみ参照可能（MySQL/PG）	可能
論理意味	性能 + 一意保証	業務ルール表明

各 DB のインデックスタイプ

-- PostgreSQL の豊富なタイプ
CREATE INDEX idx_btree ON t USING btree(col);     -- デフォルト
CREATE INDEX idx_hash  ON t USING hash(col);      -- 等価比較専用
CREATE INDEX idx_gist  ON t USING gist(geom);     -- 地理情報 / 全文
CREATE INDEX idx_gin   ON t USING gin(tags);      -- JSONB / 配列
CREATE INDEX idx_brin  ON t USING brin(ts);       -- 巨大時系列向け軽量

-- MySQL / MariaDB
CREATE INDEX idx_b   ON t(col);                                -- B-Tree
CREATE FULLTEXT INDEX idx_f ON t(body);                        -- 全文検索
CREATE SPATIAL INDEX idx_s ON t(geom);                         -- 空間
ALTER TABLE t ADD INDEX idx(col) USING HASH;                   -- MEMORY エンジン

-- Oracle
CREATE INDEX idx ON t(col);                                    -- B-Tree
CREATE BITMAP INDEX idx_b ON t(col);                           -- 低カーディナリティ向け
CREATE INDEX idx_fn ON t(LOWER(name));                         -- 関数索引
CREATE INDEX idx_ctx ON t(body) INDEXTYPE IS CTXSYS.CONTEXT;   -- 全文 (Text)

-- SQL Server
CREATE NONCLUSTERED INDEX idx ON t(col);
CREATE CLUSTERED INDEX idx_pk ON t(id);                        -- 表自体の物理順序
CREATE COLUMNSTORE INDEX cs_idx ON facts(...);                 -- 列指向（OLAP）

複合インデックス（カラム順序が命）

複合インデックス (A, B, C) は左端から連続して使えた場合のみ最大限機能します。

CREATE INDEX idx_o ON orders(customer_id, status, created_at);

-- ✅ 使える
WHERE customer_id = 1;
WHERE customer_id = 1 AND status = 'paid';
WHERE customer_id = 1 AND status = 'paid' AND created_at >= '2024-01-01';

-- ❌ 左端を飛ばすと使えない（MySQL の場合）
WHERE status = 'paid';                          -- customer_id を指定していない
WHERE created_at >= '2024-01-01';                -- 同上

-- △ 範囲のあとは止まる
WHERE customer_id = 1 AND created_at >= '...';   -- status 指定なし → created_at は使えるが効果限定
-- → 検索パターンに合わせて (customer_id, created_at) も検討

Partial Index (部分索引)

「特定の値だけ索引化」できる機能。索引サイズが激減し更新も軽くなります。

-- PostgreSQL
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';
-- → status = 'active' の行だけ索引化
-- WHERE status = 'active' AND email = '...' の SELECT で利用

-- SQL Server: Filtered Index
CREATE INDEX idx_pending ON orders(id) WHERE status = 'pending';

-- MySQL は Partial Index 非対応（≠ 「索引の prefix だけ」の意味の partial）
-- MySQL の prefix index は別概念
CREATE INDEX idx_email_prefix ON users(email(20));   -- email の先頭 20 文字

Function-based Index (関数索引)

-- 大文字小文字を無視する検索の高速化
-- PostgreSQL
CREATE INDEX idx_users_lower_email ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- ★ WHERE 側も同じ式を使うこと

-- Oracle
CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));

-- MySQL 8.0+ : Functional Index
CREATE INDEX idx ON users ((LOWER(email)));        -- ★ 括弧 2 重

-- 計算列に索引 (生成列 + 通常索引)
ALTER TABLE users ADD email_lc VARCHAR(255)
       GENERATED ALWAYS AS (LOWER(email)) STORED;
CREATE INDEX idx_email_lc ON users(email_lc);

DROP INDEX

-- 標準
DROP INDEX idx_users_email;                       -- PostgreSQL / Oracle

-- MySQL は ON が必要
DROP INDEX idx_users_email ON users;
-- または
ALTER TABLE users DROP INDEX idx_users_email;

-- SQL Server
DROP INDEX users.idx_users_email;
DROP INDEX idx_users_email ON users;

-- 存在しない場合のエラー回避
DROP INDEX IF EXISTS idx_users_email;             -- PG / SQL Server 2016+

ALTER INDEX REBUILD

断片化したインデックスを再構築し、サイズと検索速度を改善します。

-- Oracle
ALTER INDEX idx REBUILD;                          -- 別表領域に作り直して切替（高速）
ALTER INDEX idx REBUILD ONLINE;                   -- DML を止めない
ALTER INDEX idx COALESCE;                         -- 同一表領域内で詰める（軽量）

-- PostgreSQL
REINDEX INDEX idx;
REINDEX INDEX CONCURRENTLY idx;                   -- 11+ ONLINE

-- SQL Server
ALTER INDEX idx ON users REBUILD;
ALTER INDEX idx ON users REORGANIZE;              -- 軽量版（断片化 < 30% 推奨）

-- MySQL は REBUILD コマンド無し → ALTER TABLE で再構築
ALTER TABLE users ENGINE = InnoDB;                -- 全索引と表の再構築
OPTIMIZE TABLE users;                              -- 同様

統計情報 (Index Stats)

-- Oracle
EXEC DBMS_STATS.GATHER_INDEX_STATS('HR', 'IDX_EMP_EMAIL');
SELECT index_name, distinct_keys, leaf_blocks, blevel, clustering_factor
FROM   user_indexes;

-- PostgreSQL
ANALYZE users;
SELECT * FROM pg_stat_user_indexes WHERE relname = 'users';

-- MySQL
ANALYZE TABLE users;
SELECT * FROM information_schema.statistics WHERE table_name = 'users';

-- SQL Server
UPDATE STATISTICS users idx_users_email WITH FULLSCAN;
DBCC SHOW_STATISTICS('users', 'idx_users_email');

使われない索引の検出

-- PostgreSQL: 一度も使われていない索引
SELECT s.schemaname, s.relname AS table, s.indexrelname AS index,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM   pg_stat_user_indexes s
WHERE  s.idx_scan = 0
  AND  s.indexrelid NOT IN (SELECT indexrelid FROM pg_index WHERE indisunique);

-- MySQL 8.0+ (Performance Schema)
SELECT object_schema, object_name, index_name
FROM   sys.schema_unused_indexes;

-- Oracle: V$OBJECT_USAGE
ALTER INDEX idx MONITORING USAGE;
-- ... 一定期間運用後 ...
SELECT * FROM v$object_usage;

インデックスの注意点

• 索引は更新の足を引っ張る → 更新系テーブルに無闇に張らない
• カーディナリティが低い列 (フラグ 0/1 等) には B-Tree は効きにくい → 部分索引や BITMAP を検討
• WHERE 句に関数を使うと索引が効かない (Sargable でない) → 関数索引で対処
• 暗黙キャスト (varchar 列に数値で WHERE) も索引が効かない
• 定期的に ANALYZE で統計を更新しないとオプティマイザが索引を使わない

FAQ

Q: 索引を張るほど速くなる？
A: SELECT は速くなるが INSERT/UPDATE/DELETE は遅くなります。ストレージも食う。EXPLAIN で本当に使われているか必ず確認。

Q: 主キーは自動でインデックスがつく？
A: はい、PRIMARY KEY は暗黙的に UNIQUE インデックスを作ります（クラスタード or 非クラスタード）。

Q: ONLINE REBUILD は本当に DML を止めない？
A: ほぼ止めませんが、一瞬の Metadata Lock が必要。PostgreSQL は CONCURRENTLY でほぼ無影響。