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

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

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

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

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

---

## 主キーインデックス

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

### 用途

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

```sql
SELECT * FROM users WHERE id = 1;

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

### 補足

* 多くのRDBでは、主キーを設定すると**暗黙的にインデックスが作成**される

  * 例: MySQL InnoDBなど
  * 参考: [主キー制約とインデックスの話](https://qiita.com/suzukito/items/ca6a19fc7c6dec35b302)

---

## セカンダリインデックス

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

### 用途

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

```sql
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種類のアルゴリズムについて](https://zenn.dev/captain_blue/articles/three-types-of-join-algorithms)
  * 結合先のテーブル探索でインデックスによりアクセス数を削減できる
* フィルタ条件が特定のカラムに集中している場合に有効

---

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

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

### 用途

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

```sql
-- 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などの構造を用いた、空間情報（位置・領域）に特化したインデックス

### 用途

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

```sql
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 の高速化

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

### 特徴

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

---

## カバリングインデックス

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

### 用途

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

```sql
-- 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削減 |