データ特性・検索ユースケースに合わせたインデックス選定
- インデックス選定の際、データ特性・ユースケースから、逆引きで、最適なものを選定できるよう体系的に整理した
ユースケース別インデックス選定表
| 検索ユースケース | データ特性 | 推奨インデックス |
|---|---|---|
| 一意なキーによる検索 | 主キーでの検索 | 主キーインデックス |
| 非ユニークなキーの検索 | 主キー以外で検索を行う機会のあるデータ | セカンダリインデックス |
| 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削減 |