この記事の要点

基本: CREATE INDEX idx_name ON table(col1, col2) 一意性制約: CREATE UNIQUE INDEX で重複を禁止 タイプ指定: USING btree / hash / gin / gist (PostgreSQL) 部分インデックス (PG): WHERE deleted_at IS NULL で条件付き 本番運用: オンライン作成 (MySQL 5.6+) / CONCURRENTLY (PG) でロック回避

CREATE INDEX 基本構文

-- 単一カラム
CREATE INDEX idx_users_email ON users(email);

-- 複合インデックス（左から順に使われる）
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date DESC);

-- 一意インデックス（重複禁止 = UNIQUE 制約と等価）
CREATE UNIQUE INDEX idx_users_email_uq ON users(email);

-- インデックス削除
DROP INDEX idx_users_email;             -- PostgreSQL/Oracle
DROP INDEX idx_users_email ON users;    -- MySQL

-- インデックス確認
SHOW INDEX FROM users;                   -- MySQL
\d users                                 -- PostgreSQL psql
SELECT * FROM pg_indexes WHERE tablename = 'users';

命名規約とベストプラクティス

規約	例
プレフィックス idx_	idx_users_email
UNIQUE は uq_	uq_users_email
テーブル名_カラム名	idx_orders_user_id_status
命名は全DBで 63〜128文字以内	長すぎ注意（PG 63 / MySQL 64）

インデックスの種類

B-tree（既定）

もっとも汎用的。等価検索・範囲検索・ソートに有効。ほぼ全DBの既定。

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_date ON orders USING btree (order_date);   -- PG 明示

Hash

等価検索のみ高速。範囲検索やソートには使えない。PostgreSQL 10+ で WAL 対応。

-- PostgreSQL
CREATE INDEX idx_users_email_hash ON users USING hash (email);

GIN / GiST（PostgreSQL）

-- GIN: 全文検索 / JSONB / 配列
CREATE INDEX idx_articles_tags ON articles USING gin (tags);   -- 配列カラム
CREATE INDEX idx_logs_data ON logs USING gin (data jsonb_path_ops);

-- GiST: 地理空間 / 範囲型
CREATE INDEX idx_locations_geom ON locations USING gist (geom);

部分インデックス（Partial Index）

条件を満たす行だけインデックス化。論理削除カラムを持つテーブルで威力を発揮します。

-- PostgreSQL（MySQL は未対応）
CREATE INDEX idx_users_active_email
ON users(email)
WHERE deleted_at IS NULL;

-- メリット
-- - インデックスサイズが小さい（生存ユーザーのみ）
-- - 書き込み時のメンテナンスコストが下がる
-- - 検索時に条件が同じならプランナが使ってくれる

-- 使う側のクエリは同条件を含める
SELECT * FROM users
WHERE email = 'taro@example.com' AND deleted_at IS NULL;

関数インデックス（Function-based Index / Expression Index）

-- PostgreSQL: LOWER(email) で大小文字無視の検索を高速化
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

SELECT * FROM users WHERE LOWER(email) = LOWER('Taro@Example.com');

-- Oracle
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- MySQL 8.0+
CREATE INDEX idx_users_email_lower ON users((LOWER(email)));  -- 関数は () で囲む

-- 日付の年月だけ
CREATE INDEX idx_orders_yyyymm ON orders(EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date));

カバリングインデックス (INCLUDE)

-- PostgreSQL 11+ / SQL Server: 非キー列をインデックスに含める
CREATE INDEX idx_orders_user_inc
ON orders(user_id)
INCLUDE (order_date, total_amount);

-- インデックスだけで SELECT が完結（Index Only Scan）
SELECT order_date, total_amount FROM orders WHERE user_id = 100;

オンライン作成（本番運用）

大規模テーブルへの CREATE INDEX はテーブルロックを起こし、業務を止めます。本番ではオンライン作成を使います。

MySQL 5.6+ (InnoDB)

-- 既定でオンライン作成（DML を許可）
ALTER TABLE orders ADD INDEX idx_orders_user_id (user_id);

-- 明示的に指定
ALTER TABLE orders ADD INDEX idx_orders_user_id (user_id)
  ALGORITHM=INPLACE, LOCK=NONE;

-- 完全コピー (古い方式)
ALTER TABLE orders ADD INDEX idx_orders_user_id (user_id)
  ALGORITHM=COPY, LOCK=SHARED;

PostgreSQL CONCURRENTLY

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

-- 注意:
-- - トランザクション内で実行不可
-- - 通常より時間がかかる（2 回スキャン）
-- - 失敗時に INVALID なインデックスが残る → DROP して再作成
SELECT indexname, indisvalid FROM pg_index
JOIN pg_class ON pg_class.oid = indexrelid
WHERE NOT indisvalid;

インデックス再構築

-- PostgreSQL: 肥大化したインデックスをスリム化
REINDEX INDEX idx_orders_user_id;
REINDEX TABLE orders;
REINDEX INDEX CONCURRENTLY idx_orders_user_id;  -- PG 12+

-- MySQL: 統計情報の更新（ANALYZE）+ 必要なら OPTIMIZE TABLE
ANALYZE TABLE orders;
OPTIMIZE TABLE orders;          -- テーブル再構築（重い）

-- Oracle
ALTER INDEX idx_orders_user_id REBUILD ONLINE;

インデックス設計の指針

状況	指針
頻繁に WHERE / JOIN で使うカラム	インデックス付与
カーディナリティが低い (例: status = 0/1)	部分インデックスのみ有効
更新が頻繁なカラム	付けすぎ注意（書き込みが遅くなる）
複合インデックス順序	等価検索 → 範囲検索の順
NULL を含むカラム	DB別に挙動差（Oracle は NULL を索引化しない）

FAQ

Q: インデックスは何個まで作って良い？
A: 経験則で 1 テーブル 5〜10 個まで。書き込みコストとプランナのコスト計算が増えます。EXPLAIN で使われていないものは削除。

Q: 複合インデックス (a, b, c) は WHERE b = ? に効く？
A: 効きません。左から順なので WHERE a = ? 必須。WHERE a = ? AND c = ? は a だけ使われます。

Q: CONCURRENTLY が途中で失敗した
A: 残った invalid インデックスを DROP して、原因（重複エラー等）解消後に再実行。