🦁

複合インデックスとカバリングインデックスの設計と使い分け

に公開
MySQL
index
tech

はじめに

データベースのパフォーマンスチューニングにおいて、「どのカラムにどのようなインデックスを貼るか」は非常に重要です。特に、複数テーブルをJOINしたり、サブクエリを含むクエリでは、インデックス設計がクエリ速度に大きな影響を及ぼします。

本記事では、実際の業務でよくある「通知関連のテーブル」を例に、インデックス設計の考え方や、複合インデックス・カバリングインデックスの使い分けを解説します。

想定シナリオ

例えば、次のような「通知」と「通知確認履歴」のような2つのテーブルがあるとします。

notifications（通知本体）

  • id
  • status

notification_histories（通知確認履歴）

  • id
  • notification_id
  • sent_at
  • confirmed_at

「未確認かつ送信から24時間経過した通知」を抽出したい場合、クエリは次のようになります：

SELECT n.id
FROM notifications n
JOIN (
    SELECT notification_id, MAX(id) as latest_history_id
    FROM notification_histories
    GROUP BY notification_id
) latest_hist ON n.id = latest_hist.notification_id
JOIN notification_histories nh ON latest_hist.latest_history_id = nh.id
WHERE n.status = 'pending'
AND nh.sent_at < NOW() - INTERVAL 24 HOUR
AND nh.confirmed_at IS NULL

インデックス設計のポイント

1. サブクエリ最適化用の複合インデックス

サブクエリ部分（notification_idごとの最新id取得）には (notification_id, id) の複合インデックスがあると、GROUP BYとMAX集計が高速化されます。

CREATE INDEX idx_notification_histories_notification_id_id 
ON notification_histories(notification_id, id);

2. WHERE句用の部分インデックス

「送信日時が24時間前より前」かつ「未確認（NULL）」の絞り込みには、(sent_at, confirmed_at) の複合インデックスが有効です。

CREATE INDEX idx_notification_histories_sent_confirmed 
ON notification_histories(sent_at, confirmed_at);

3. カバリングインデックス

JOIN・サブクエリ・WHERE句で使う全カラムを含む (notification_id, id, sent_at, confirmed_at) のインデックスを作ると、インデックスだけでクエリが完結し、I/Oコストの削減につながります。

CREATE INDEX idx_notification_histories_covering 
ON notification_histories(notification_id, id, sent_at, confirmed_at);

カバリングインデックスだけで十分？

「カバリングインデックスがあれば部分インデックスは不要か？」という疑問もあるでしょう。

カバリングインデックスの利点

  • カバリングインデックスだけでも多くのケースで十分高速です
  • インデックスだけでクエリが完結するため、I/Oコストが大幅に削減されます

部分インデックスの利点

  • (sent_at, confirmed_at) のような部分インデックスはサイズが小さいため、シンプルな検索条件のクエリでより速くなる場合があります
  • メモリ使用量が少なく、キャッシュ効率が良い

両立のメリット

  • 複雑なクエリにはカバリングインデックスが効く
  • シンプルな条件検索には部分インデックスが効く
  • どちらも持つことで幅広いパターンに最適化される

ただし、インデックスが多すぎると書き込みコストやストレージ使用量が増えるので、実際のアクセス頻度やパフォーマンス計測（EXPLAIN等）を見てバランスを取りましょう。

まとめ

  1. 複合インデックスやカバリングインデックスはクエリ内容に合わせて設計する
  2. カバリングインデックスだけでも十分なことが多いが、シンプルな部分インデックスも併用するとさらに最適化されることがある
  3. 実際にパフォーマンスを計測して最適なインデックスを選ぼう

インデックス設計はアプリの成長やクエリの変化に合わせて見直すのがポイントです！

Discussion