複数のカラムをひとつのインデックスにまとめた「複合インデックス（コンポジットインデックス）」は、MySQLチューニングの要です。しかし、カラムを並べる順序を間違えると、インデックスを定義したのに検索に使われないという状況が生じます。この記事では、左端マッチの原則から選択性の考え方まで、複合インデックス設計の核心を実例とEXPLAIN出力を交えて解説します。
このシナリオで考える
ECサイトの注文管理テーブルを使います。
CREATE TABLE orders (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL,
status VARCHAR(20) NOT NULL,
created_at DATETIME NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
INSERT INTO orders (user_id, status, created_at, amount) VALUES
(1001, 'completed', '2024-01-10 09:00:00', 5000.00),
(1001, 'cancelled', '2024-01-12 14:30:00', 3000.00),
(1002, 'completed', '2024-01-13 10:00:00', 8000.00),
(1003, 'pending', '2024-01-14 11:00:00', 1500.00),
(1002, 'completed', '2024-01-15 16:00:00', 12000.00);「特定ユーザーの注文を絞り込む」「日付範囲とステータスで集計する」といったクエリを想定しながら読み進めてください。
複合インデックスの基本構文と左端マッチの原則
複合インデックスは、カラムをカンマ区切りで並べて定義します。
CREATE INDEX idx_user_status ON orders (user_id, status);このインデックスが検索に使われるかどうかは、WHERE句に「先頭カラムから連続して」指定されているかどうかで決まります。これを「左端マッチ（leftmost prefix）の原則」と呼びます。
| WHERE句の条件 | インデックス使用 | 備考 |
|---|---|---|
| WHERE user_id = 1001 | ✅ 使える | 先頭カラムのみ |
| WHERE user_id = 1001 AND status = ‘completed’ | ✅ 使える | 両方を指定 |
| WHERE status = ‘completed’ | ❌ 使えない | 先頭（user_id）をスキップ |
| WHERE status = ‘completed’ AND user_id = 1001 | ✅ 使える | 記述順は問わない※ |
※WHERE句の記述順序は関係ありません。MySQLのオプティマイザが自動的に最適な順に並べ替えます。先頭カラムの user_id が条件に含まれていれば、インデックスが使われます。
EXPLAINで確認すると、key 列にインデックス名が表示されます。
EXPLAIN SELECT * FROM orders
WHERE user_id = 1001 AND status = 'completed'\G select_type: SIMPLE
table: orders
type: ref
possible_keys: idx_user_status
key: idx_user_status
rows: 1
Extra: NULLtype: ref と key: idx_user_status が確認できれば、インデックスが有効に使われています。
カラム順序を逆にすると何が変わるか
先ほどとは逆順で定義した場合を見てみましょう。
CREATE INDEX idx_status_user ON orders (status, user_id);このインデックスは status が先頭にあるため、WHERE user_id = 1001 だけのクエリにはインデックスが使われません。左端の status が条件に含まれていないからです。
-- idx_status_user は使われない（statusが条件にない）
SELECT * FROM orders WHERE user_id = 1001;
-- idx_status_user が使われる
SELECT * FROM orders WHERE status = 'completed' AND user_id = 1001;アプリが「特定ユーザーの全注文を取得する」クエリを最も頻繁に発行するなら、(user_id, status) の順が正解です。先頭カラムの選択は、最もよく使われるクエリパターンから逆算して決めるのが基本方針です。
選択性（カーディナリティ）の高いカラムを先頭に置く理由
選択性（selectivity）とは「そのカラムの値がどれだけバラけているか」を表す指標です。ユニーク値の総数をカーディナリティと呼び、カーディナリティが高いほど選択性が高くなります。
-- 各カラムのカーディナリティを確認
SELECT
COUNT(DISTINCT user_id) AS card_user,
COUNT(DISTINCT status) AS card_status,
COUNT(DISTINCT created_at) AS card_created
FROM orders;100万行のテーブルで card_user = 800,000、card_status = 5 だったとします。status を先頭にすると、インデックス検索後にまだ20万行を読むことになります。user_id を先頭にすれば、ヒット行数を数件に絞り込めます。
| インデックス順 | 1段階目のカーディナリティ | 絞り込み後のイメージ |
|---|---|---|
| (status, user_id) | 5種類 | 20万行が残る |
| (user_id, status) | 80万種類 | 数行に絞れる |
等値条件（=）で使うカラムが複数ある場合は、カーディナリティの高い順に並べるのが目安です。
レンジ条件の後ろにあるカラムはインデックス検索に使われない
複合インデックスで最も見落としやすい落とし穴です。BETWEEN、>、<、LIKE 'prefix%' といったレンジ条件が登場すると、そのカラム以降のカラムはインデックス検索の対象外になります。
CREATE INDEX idx_status_created_user ON orders (status, created_at, user_id);
-- ①等値 → ②レンジ → ③等値
SELECT * FROM orders
WHERE status = 'completed'
AND created_at BETWEEN '2024-01-01' AND '2024-01-31'
AND user_id = 1001;このクエリでは status（等値）と created_at（レンジ）までインデックスが利きますが、その後の user_id はインデックス検索の対象から外れます。フィルタ処理として行ごとにチェックされます。
カラム順を変えるだけで、3カラムすべてにインデックスを使えます。
| インデックスの定義順 | status（等値） | user_id（等値） | created_at（レンジ） |
|---|---|---|---|
| (status, created_at, user_id) | ✅ | ❌ フィルタ | ✅ |
| (status, user_id, created_at) | ✅ | ✅ | ✅ |
等値条件のカラムをレンジ条件のカラムより前に置くのが鉄則です。
よくある落とし穴・注意点
単独インデックスとの重複に注意する
(user_id) の単独インデックスと (user_id, status) の複合インデックスが共存すると、単独インデックスは冗長です。複合インデックスの先頭カラムが同じなら、単独インデックスの代わりに使えます。不要なインデックスはINSERT/UPDATE/DELETEのオーバーヘッドになるため整理しましょう。
前方一致以外のLIKEはインデックスに使われない
LIKE '%completed' のように先頭がワイルドカードのパターンは、インデックス先頭カラムに置いても全表スキャンになります。LIKE 'comp%'（前方一致）ならインデックスが効きます。
ORDER BYとカラム順を合わせるとソートコストが下がる
(user_id, created_at) のインデックスがある場合、ORDER BY user_id, created_at はインデックスの並び順をそのまま利用できるためファイルソートを回避できます。WHERE句だけでなくORDER BY句も考慮してカラム順を決めましょう。
まとめ
複合インデックスの設計で押さえるべき点は3つです。先頭カラムは最頻クエリのWHERE句から逆算して選ぶ。等値条件のカラムをレンジ条件より前に置く。複数の等値カラムが並ぶ場合はカーディナリティの高い順にする。EXPLAINで key 列と rows 列を確認しながら、実クエリのパターンに合わせて調整しましょう。
参考リンク
アイキャッチ画像: Photo by Team Nocoloco on Unsplash
