データ特性・検索ユースケースに合わせたインデックス選定

インデックス選定の際、データ特性・ユースケースから、逆引きで、最適なものを選定できるよう体系的に整理した

ユースケース別インデックス選定表

検索ユースケース	データ特性	推奨インデックス
一意なキーによる検索	主キーでの検索	主キーインデックス
非ユニークなキーの検索	主キー以外で検索を行う機会のあるデータ	セカンダリインデックス
JOIN（多対一、多対多）	使用上、関連テーブルとの結合が必要なデータ	セカンダリインデックス
複数カラムを使った検索やユニーク制約	使用上、複数カラムの組合せを行う場合がある（性・名など）	連結インデックス
範囲検索やソート	ソート順、連続性のある値（履歴・時系列データ）	クラスタインデックス
多次元検索	多次元な要素でかつ、範囲検索をするデータ（緯度 x 経度・時間 x 金額など）	多次元インデックス
クエリの全カラムがインデックスに含まれる	列数の少ないデータの閲覧・書き込みがあまりないデータ（インデックスの更新に負荷がかからないもの）	カバリングインデックス

主キーインデックス

主キーに張られるインデックス
一意性を保証し、最も高速な検索ができる

用途

主キーによる検索
主キー制約の一意性検証
結合先（1側）のレコード参照

SELECT * FROM users WHERE id = 1;

-- orders.user_idそれぞれに対し、一回のアクセスで、結合用のユーザーレコードを取得できる
SELECT * FROM orders
JOIN users ON orders.user_id = users.id;

補足

多くのRDBでは、主キーを設定すると暗黙的にインデックスが作成される
- 例: MySQL InnoDBなど
- 参考: 主キー制約とインデックスの話

セカンダリインデックス

主キー以外のキーに手動で追加できるインデックス
WHERE句やJOIN、ソート、グルーピングなど、さまざまな用途に効果がある

用途

非ユニークなカラムの検索
JOIN のパフォーマンス向上
WHERE句、GROUP BY、ORDER BY の高速化

SELECT * FROM users WHERE name = '太郎';

SELECT users.name, orders.order_date
FROM users
JOIN orders ON users.id = orders.user_id
WHERE orders.order_date >= '2025-01-01';

特に効果的なケース

多対一・多対多のリレーション
- 参考: JOINの3種類のアルゴリズムについて
- 結合先のテーブル探索でインデックスによりアクセス数を削減できる
フィルタ条件が特定のカラムに集中している場合に有効

連結インデックス（複合インデックス）

複数のカラムを1つのインデックスとして結合し、検索や制約に利用する

用途

複数カラムの組み合わせによる検索
複数カラムによるユニーク制約の検証

-- email + passwordの組み合わせにユニーク制約をかける
CREATE UNIQUE INDEX idx_user_email_password
  ON users(email, encrypted_password);

INSERT INTO users (email, encrypted_password)
VALUES ('a@example.com', 'xxx');

注意点

左端一致の原則に注意：インデックスの先頭カラムから順に使わないと効果が薄れる

多次元インデックス

R-treeなどの構造を用いた、空間情報（位置・領域）に特化したインデックス

用途

複数カラムの範囲にまたがる検索（例：緯度・経度）

SELECT * FROM restaurants
WHERE latitude BETWEEN 35.0 AND 36.0
  AND longitude BETWEEN 139.0 AND 140.0;

補足

空間検索やベクトル検索に強い
GISエンジン、MongoDB、Elasticsearchなどでも利用される

クラスタインデックスとは

インデックス順に物理データが並んで格納される構造
- インデックスがそのままデータの実態を持っている
MySQL InnoDBでは主キーインデックスがクラスタインデックスになる

用途

範囲検索（日時、連番IDなど）
ORDER BY の高速化

SELECT * FROM logs
WHERE created_at BETWEEN '2025-01-01' AND '2025-01-31';

特徴

テーブルに1つしか持てない
インデックスの順番でデータが格納されるため、範囲取得が非常に速い

カバリングインデックス

クエリに必要なすべてのカラムをインデックスがカバー状態
- インデックスとしては、本質的にセカンダリインデックスと同じ
実データを読み込む必要がないため、非常に高速

用途

クエリで使用するカラムをすべてインデックスに含める

-- emailとcreated_atの両方に対するクエリ
CREATE INDEX idx_user_email_created
  ON users(email, created_at);

SELECT email, created_at
FROM users
WHERE email = 'user@example.com';

効果

ディスクI/O削減
テーブルアクセスなしでクエリを完結できる（Covering）

まとめ

インデックス種別	説明
主キーインデックス	主キーに自動的に張られ、一意性と高速アクセスを保証する
セカンダリインデックス	任意のカラムに追加でき、検索・JOIN・ソートを効率化する
連結インデックス	複数カラムをまとめたインデックス。順序に注意しながら検索・制約に使える
多次元インデックス	緯度経度など多軸の範囲検索に適した構造。空間検索に強い
クラスタインデックス	インデックス順に物理データを格納。範囲検索やORDER BYに最適
カバリングインデックス	クエリが必要とする全カラムがインデックスに含まれている状態。高速でI/O削減