複合インデックスってカーディナリティの高い順に貼っときゃいいわけではないんですか？！

こんにちは！

開発本部 SINIS for X 開発チームの西野（@fingerEase24）です。

今回は、タイトルの通りMySQLにおける複合インデックスの貼り方について、そうだったのか！となった学びについてお話しようと思います。

なお、MySQLバージョンは8系となっております。

複合インデックスについて

まず前提として、複合インデックスについて簡単におさらいしておきます。

複合インデックスとは、複数のカラムを組み合わせて定義するインデックスのことです。

ALTER TABLE `users` ADD KEY `users_idx_1` (`last_name`, `first_name`);

上記のように定義した場合、MySQLは last_name → first_name の順にデータをソートしたB-Treeを構築します。

これは電話帳のようなもので、まず姓で並べ、同じ姓の中では名で並べる、というイメージです。

この性質上、複合インデックスにはカラムの定義順が重要であるという特徴があります。

先頭のカラムから順にインデックスが効くため、途中のカラムだけを指定したクエリではインデックスが有効に使われません。

また、複合インデックスのカラム順を決める際には「カーディナリティ（値の種類の多さ）が高いカラムを先にすると良い」という考え方が広く知られています。

これは、より多くのデータを絞り込めるカラムを先に評価した方が効率的になるケースが多いためです。

しかし、必ずしもこの原則に従うことが最適ではない場合があります。

今回は、実務で遭遇したケースを簡略化してお伝えします。

実際に遭遇したケース

SNS上でのアクティビティ履歴を保存する以下のようなテーブルがありました（関係のないカラムやインデックスは省略しています）。

CREATE TABLE `account_activity_histories` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `event_type` enum('quote post','like','repost','reply','mention') NOT NULL COMMENT 'イベントの種類',
  `performed_at` datetime NOT NULL COMMENT 'アクションされた日時',
  PRIMARY KEY (`id`)
)

このテーブルに対して以下のようなクエリを発行する必要がありました。

SELECT
  *
FROM
  account_activity_histories AS a
WHERE
  a.event_type = ?
  AND a.performed_at BETWEEN ? AND ?;

そこで、以下のようにインデックスを追加しようとしました。

ALTER TABLE `account_activity_histories` ADD KEY `account_activity_histories_idx_1` (`performed_at`, `event_type`);

この際の思考プロセスとしては、

• event_type と performed_at を比較した際、 performed_at の方がカーディナリティが高い
• 複合インデックスの順番はカーディナリティの高い順にするべきなので performed_at を先にしよう

というものでした。

しかし、コードレビューにてこの順番を逆にした方が効率が良いという指摘をいただきました。

これはB-Treeインデックスの性質によるもので、複合インデックスの場合は定義順にツリーを走査していきますが、範囲検索（BETWEEN, >, < など）のカラムが来ると、それ以降のカラムはツリーの走査（seek）には利用されません。

ただし、MySQL 5.6以降では Index Condition Pushdown（ICP） により、ツリー走査には使われなくても、ストレージエンジンレベルでのフィルタリングには活用されます。

そのため performed_at 単独のインデックスよりは多少効率的ですが、両カラムをツリー走査に活用できる (event_type, performed_at) の順とは大きな差が生まれます。

検証

実際にそれぞれの順番でインデックスを貼り、以下の EXPLAIN ANALYZE で実行計画を確認してみました。

EXPLAIN ANALYZE SELECT
  *
FROM
  account_activity_histories AS a
WHERE
  a.event_type = 'reply'
  AND a.performed_at BETWEEN '2026-01-01 0:00:00'
  AND '2026-01-01 23:59:59';

なお、データ数は約10万行としています。

複合インデックス順が performed_at,event_type の場合

-> Index range scan on a using account_activity_histories_idx_1 over ('2026-01-01 00:00:00' <= performed_at <= '2026-01-01 23:59:59' AND event_type = 'reply'), with index condition: ((a.event_type = 'reply') and (a.performed_at between '2026-01-01 00:00:00' and '2026-01-01 23:59:59'))  (cost=12166 rows=27036) (actual time=0.0578..13.6 rows=2856 loops=1)

複合インデックス順が event_type,performed_at の場合

-> Index range scan on a using account_activity_histories_idx_1 over (event_type = 'reply' AND '2026-01-01 00:00:00' <= performed_at <= '2026-01-01 23:59:59'), with index condition: ((a.event_type = 'reply') and (a.performed_at between '2026-01-01 00:00:00' and '2026-01-01 23:59:59'))  (cost=1285 rows=2856) (actual time=0.0971..11.9 rows=2856 loops=1)

結果から主要な指標を比較すると以下のようになります。

インデックス順	cost	rows (estimated)	rows (actual)	actual time
performed_at, event_type	12,166	27,036	2,856	0.058..13.6ms
event_type, performed_at	1,285	2,856	2,856	0.097..11.9ms

前者は performed_at の範囲で27,036行を走査した上でICPにより event_type をフィルタしているため、estimated rowsとactual rowsに大きな乖離があります。

一方、後者は event_type の等値検索で先に絞り込んでから performed_at の範囲検索を行うため、estimated rowsとactual rowsが一致しており、costも約1/10に抑えられています。

この挙動はMySQL公式ドキュメントで次のように説明されています。「range の最適化」には、範囲スキャンで緩い条件が使われることと、その後のフィルタ処理について次の記述があります。

一般に (前の例で示したように)、範囲スキャンに使用される条件は、WHERE 句より制限がゆるくなります。 MySQL は、範囲条件を満たすが、完全なWHERE 句でない行をフィルタ処理する追加のチェックを実行します。

また、「インデックスコンディションプッシュダウンの最適化」では、インデックス走査で絞り込めない条件をストレージエンジン側で評価するICPの仕組みが説明されています。performed_at を先にした場合、event_type はツリー走査には使われませんが、with index condition（EXPLAIN の Using index condition に相当）により、ストレージエンジンレベルで event_type = 'reply' のフィルタが行われています。

2 番目の部分 (lastname LIKE '%etrunia%') を使用してスキャンする必要がある行数を制限することはできないため、インデックス条件プッシュダウンを使用しない場合、このクエリーでは、zipcode='95054' を持つすべてのユーザーの完全なテーブルの行を取得する必要があります。

インデックス条件プッシュダウンでは、MySQL はテーブルの行全体を読み取る前にlastname LIKE '%etrunia%' 部分をチェックします。 これにより、zipcode 条件に一致するがlastname 条件に一致しないインデックスタプルに対応する完全な行の読取りが回避されます。

この差はさらにデータ量が増加したり、さらに広い範囲での検索を行なったりするとより顕著になるはずです。

結論

以上から、このケースにおいて適切なインデックスは以下になります。

ALTER TABLE `account_activity_histories` ADD KEY `account_activity_histories_idx_1` (`event_type`, `performed_at`);

ポイントとなるのは、等値検索（=）で使うカラムを先に、範囲検索（BETWEEN, >, < など）で使うカラムを後に配置するという原則です。等値検索のカラムを先にすることで、B-Treeのツリー走査で対象を絞り込んだ上で範囲検索に進むことができ、両カラムがインデックスの走査に有効活用されます。

なお、稀なケースではありますがデータの分布の偏りによってはこの限りではない場合もあるので注意が必要です。例えば、 event_type の特定の値がデータの99%を占めるような極端な偏りがある場合、等値検索で絞り込んでもほとんどデータが減らないため、期待した効果が得られないことがあります。

まとめ

複合インデックスの順番を決めるにあたってはカーディナリティの高い順にしておけば良いだろうという認識でしたが、B-Treeインデックスの特性を踏まえると「等値検索を先、範囲検索を後」という観点も重要であり、必ずしもカーディナリティ順が最適とは限らないという点は学びでした。

この記事が同じように誰かの学びになってもらえると幸いです。

---

テテマーチでは、一緒にサービスを作り上げてくれる仲間を募集中です。ご興味のある方は、以下のリンクからぜひご応募ください！ herp.careers

エンジニアチームガイドはこちら！ tetemarche01.notion.site