複合・ユニークインデックス ― 索引を使いこなす

導入

前のレッスンで、1つの列に索引を張る基本を学びました。実務では「2つの列の組み合わせで検索する」「この列に重複を許さない」といった、もう一歩進んだ索引が欲しくなります。複合インデックスとユニークインデックスを押さえましょう。

説明

ユニークインデックス ― 重複を禁止しつつ速くする。CREATE UNIQUE INDEX は、索引の速さに加えて「その列に同じ値は2つ入れない」という制約も兼ねます。「メールアドレスは1人1つ」のような一意性を守るのに使います。

CREATE UNIQUE INDEX idx_users_name ON users(name);

この索引を張った後で同じ name を追加しようとすると、UNIQUE constraint failed で弾かれます。速さと一意性を同時に得られるわけです。

複合インデックス ― 複数列をまとめて索引する。ON テーブル(列A, 列B) と複数列を並べると、「A と B の組み合わせ」で引ける索引になります。

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

flowchart TB
    C["複合インデックス (user_id, status)"]
    C --> A["WHERE user_id = ? AND status = ?　→ よく効く"]
    C --> B["WHERE user_id = ? だけ　→ 効く（左端は使える）"]
    C --> D["WHERE status = ? だけ　→ 効きにくい（左端を飛ばすと×）"]

複合インデックスには列の順番が重要という性質があります。「左端の列から順に使う」ため、(user_id, status) の索引は「user_id だけ」の検索にも効きますが、「status だけ」の検索には効きにくい。よく一緒に絞り込む列を、選択性の高い順に並べるのが定石です。索引は「検索・結合・並べ替えでよく使う列」に絞って張る――この原則は複合でも同じです。

やってみよう

初期表示のコードを実行し、name にユニークインデックスを張った後の実行計画が USING INDEX になることを確認しましょう。次に INSERT INTO users (id, name, age, city) VALUES (99, '佐藤', 20, '沖縄'); を試すと、name が重複するため UNIQUE constraint failed で弾かれます。ユニークインデックスが一意性も守っている証拠です。

演習

orders テーブルに、user_id と ordered_at の複合インデックスを idx_orders_user_date という名前で作ってください。