インデックスを作成 - CREATE INDEX

インデックス作成の DDL。基本・UNIQUE・部分インデックス・式インデックス・CONCURRENTLY / INPLACE の使い分け

概念図

構文

sql

CREATE [UNIQUE] INDEX [CONCURRENTLY] name ON table (col [, ...]) [WHERE ...];

サンプル

単一列・複合・UNIQUE の基本パターン。複合インデックスは列の順序が検索性能を決める

sql

-- 基本
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- 複合インデックス（並び順が重要）
CREATE INDEX idx_orders_user_placed ON orders (user_id, placed_at DESC);

-- UNIQUE インデックス
CREATE UNIQUE INDEX uq_users_email ON users (email);

インデックスの基本

インデックスは検索を速くするためのデータ構造（多くは B-Tree）で、テーブルに追加的に作られます。読み取りを速くする代償に書き込みコストと容量が増えるため、必要な列にだけ作ります。

PRIMARY KEY と UNIQUE 制約は内部的にインデックスを作成するため、同じ列に重ねてインデックスを作る必要はありません。

UNIQUE INDEX

UNIQUE INDEX は検索高速化に加えて値の一意性を強制します。UNIQUE 制約との違いは、UNIQUE 制約は SQL 標準の概念、UNIQUE INDEX は実装詳細寄りですが、ほとんどの RDBMS で相互変換可能です。

PostgreSQL の部分インデックスと組み合わせると、「status='active' の行だけ UNIQUE」といった条件付き一意制約が書けます。

sql

-- PostgreSQL: アクティブな招待だけメールを一意に
CREATE UNIQUE INDEX uq_invitations_active_email
ON invitations (email)
WHERE status = 'active';

部分インデックスと式インデックス (PostgreSQL)

PostgreSQL は部分インデックス（WHERE で対象行を絞ったインデックス）と式インデックス（関数・式に対するインデックス）をサポートします。

たとえば「未処理のジョブだけを高速に検索したい」「大文字小文字を無視した email を一意にしたい」ケースでサイズを小さく保ちつつ性能を出せます。MySQL は 8.0 で関数インデックス、部分インデックスは未対応です。

sql

-- 部分インデックス: 未処理ジョブだけ
CREATE INDEX idx_jobs_pending ON jobs (created_at)
WHERE status = 'pending';

-- 式インデックス: 大小文字無視の email
CREATE UNIQUE INDEX uq_users_lower_email
ON users (LOWER(email));

オンラインでのインデックス作成

巨大テーブルでの CREATE INDEX は長時間の書き込みロックを招くため、本番ではオンライン方式を使います。

PostgreSQL: CREATE INDEX CONCURRENTLY。書き込みをブロックしない代わりに、トランザクション内で実行できず、失敗時に INVALID インデックスが残ることがある
MySQL (InnoDB): ALGORITHM=INPLACE, LOCK=NONE を指定することで多くの場合オンラインで作成可能
SQL Server: Enterprise Edition で WITH (ONLINE = ON)

sql

-- PostgreSQL: 書き込みをブロックしない
CREATE INDEX CONCURRENTLY idx_events_occurred_at
ON events (occurred_at);

-- MySQL 8.0: オンラインで作成、不可ならエラー
CREATE INDEX idx_events_occurred_at
ON events (occurred_at)
ALGORITHM=INPLACE, LOCK=NONE;

落とし穴

作りすぎ: INSERT/UPDATE/DELETE のたびに全インデックスを更新するため、冗長なインデックスは書き込みを遅くする
複合インデックスの列順ミス: (a, b) のインデックスは WHERE a=? や WHERE a=? AND b=? には使われるが、WHERE b=? だけでは使われない
低選択率のカラムに単独でインデックス: boolean 1 列などはフルスキャンのほうが速いことが多い
CONCURRENTLY の INVALID 残留: 失敗時は DROP INDEX CONCURRENTLY で消して再作成する