【MySQL】インデックスの仕組みと設計方法｜EXPLAIN・複合インデックス・パフォーマンス改善の実践

2026-05-12

MySQL のクエリが遅いとき、とりあえずインデックスを追加していませんか。

インデックスは検索を速くするための重要な仕組みですが、貼り方を間違えると期待したほど効かなかったり、書き込みやストレージの負担が増えたりします。この記事では「なぜ効かないのか」を EXPLAIN と複合インデックスの見方からほどき、追加前に確認すべきポイントを整理します。

はじめに

まず、この記事の前提と扱う範囲を明確にします。MySQL のインデックスを「なんとなく貼る」状態から一歩進めて、実際のクエリを見ながら設計するための考え方を扱います。

InnoDB のクラスタ化インデックスとセカンダリインデックス
EXPLAIN で見るべき列
複合インデックスの左端プレフィックス
インデックス設計の実務的な手順
一人称の実務経験を含む失敗例

検証範囲

検証範囲は、2026-05-12 時点で確認した MySQL 8.4 Reference Manual です。実際の実行計画は、MySQL のバージョン、テーブル定義、データ量、統計情報、WHERE 句や ORDER BY の条件によって変わります。この記事の SQL 例は、考え方を説明するための最小例として読んでください。

インデックスはどこを速くするのか

インデックスは、テーブルの中から目的の行を探すための手がかりです。索引がない状態では、条件に合う行を探すために多くの行を確認することがあります。索引があれば、MySQL は条件に合いそうな範囲を絞り込んでから行を探せます。

InnoDB では、空間インデックスを除き、インデックスは B-tree データ構造として説明されています。B-tree は値の順序を保つため、等価検索だけでなく範囲検索や並び順にも関係します。

ただし、インデックスは無料ではありません。行を追加・更新・削除するときは、テーブル本体だけでなく関連するインデックスも更新されます。読み取りを速くする目的でインデックスを増やしすぎると、書き込みやストレージの負担が増える点に注意が必要です。

InnoDB のクラスタ化インデックスとセカンダリインデックス

InnoDB のインデックスを考えるときは、まずクラスタ化インデックスとセカンダリインデックスを分けると理解しやすくなります。

種類	役割	代表例
クラスタ化インデックス	行データを格納する特別なインデックス	`PRIMARY KEY`
セカンダリインデックス	クラスタ化インデックス以外のインデックス	`INDEX idx_users_email (email)`

MySQL 8.4 の InnoDB ドキュメントでは、PRIMARY KEY を定義すると InnoDB はそれをクラスタ化インデックスとして使う、と説明されています。主キーがない場合は、条件を満たす UNIQUE NOT NULL インデックス、さらにそれもない場合は内部的な隠しクラスタ化インデックスが使われます。

セカンダリインデックスの各レコードには、インデックス対象のカラムに加えて主キーの値が含まれます。取得したい列がセカンダリインデックスだけで足りない場合は、主キーを使ってクラスタ化インデックス側の行データを取りに行く、という流れになります。

この性質から、主キーが非常に長い場合はセカンダリインデックスのサイズにも影響します。実務では、主キーの選び方とセカンダリインデックスの数をセットで考える必要があります。

EXPLAIN で実行計画を確認する

インデックスを設計するときは、クエリの実行計画を見ます。MySQL では EXPLAIN を使うと、オプティマイザがどのようにテーブルを読む予定かを確認できます。オプティマイザは、MySQL が候補の中から実行方法を選ぶ仕組みです。

たとえば、EC サイトの注文テーブルを考えます。

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(20) NOT NULL,
  ordered_at DATETIME NOT NULL,
  total_amount DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (id),
  INDEX idx_orders_user_status_ordered_at (user_id, status, ordered_at)
);

ユーザーごとの注文履歴を、ステータスと注文日時で絞るクエリです。

EXPLAIN
SELECT id, user_id, status, ordered_at, total_amount
FROM orders
WHERE user_id = 1001
  AND status = 'paid'
  AND ordered_at >= '2026-05-01 00:00:00'
ORDER BY ordered_at DESC
LIMIT 20;

出力は環境やデータ量によって変わりますが、簡略化すると次のような行を確認します。

id | select_type | table  | type  | possible_keys                         | key                                      | key_len | rows | filtered | Extra
1  | SIMPLE      | orders | range | idx_orders_user_status_ordered_at     | idx_orders_user_status_ordered_at       | 98      | 20   | 100.00   | Using where

まず見る列は次のあたりです。

列	見ること
`type`	テーブルへのアクセス方法。`ref` や `range` など、どの程度絞り込めているかを見る
`possible_keys`	候補になったインデックス
`key`	実際に選ばれたインデックス
`key_len`	複合インデックスのどこまで使われたかを推測する手がかり
`rows`	MySQL が調べると見積もった行数
`filtered`	テーブル条件で絞り込まれる割合の見積もり
`Extra`	`Using index`、`Using where`、`Using filesort` などの補足情報

key_len は、選ばれたキーの長さです。複合インデックスのどの部分まで使われたかを推測する手がかりになります。filtered は、テーブル条件で絞り込まれる行の割合の見積もりです。Extra の Using filesort は、MySQL が追加の並び替え処理を使うことを示します。

公式ドキュメントでは、rows は MySQL が実行のために調べる必要があると考える行数で、InnoDB では推定値であり常に正確とは限らないと説明されています。rows が大きい場合は、条件に対してインデックスが効いていない可能性を疑います。

key が NULL の場合は、実行に使えるインデックスを MySQL が見つけられていない状態です。ただし、key に何か出ているだけで十分とは限りません。複合インデックスの一部だけしか使われていない場合や、絞り込み後の行数がまだ多い場合もあります。

複合インデックスは左から効く

MySQL の複合インデックスは、複数カラムを並べたインデックスです。MySQL 8.4 の公式ドキュメントでは、複合インデックスは最大 16 カラムで構成でき、左から連続したカラムの組み合わせに対して使えると説明されています。

たとえば、次のインデックスがあるとします。

INDEX idx_orders_user_status_ordered_at (user_id, status, ordered_at)

この場合、検索に使いやすい組み合わせは次のようになります。

条件	インデックスを使った検索のしやすさ
`user_id = ?`	使いやすい
`user_id = ? AND status = ?`	使いやすい
`user_id = ? AND status = ? AND ordered_at >= ?`	使いやすい
`status = ?`	左端の `user_id` がないため、検索には使いにくい
`status = ? AND ordered_at >= ?`	左端から連続していないため、検索には使いにくい

これが、よく「左端プレフィックス」と呼ばれる考え方です。インデックス (user_id, status, ordered_at) は、左から連続する条件に使いやすい構造です。具体的には、(user_id)、(user_id, status)、(user_id, status, ordered_at) の検索が対象です。一方で、(status) だけの検索を主目的にはできません。

複合インデックスの順序は、次のようにクエリから逆算します。

よく使う WHERE 条件を並べる。
等価条件で強く絞れるカラムを前に置く。
範囲条件や並び替えに使うカラムを後ろに置く。
EXPLAIN で key、key_len、rows、Extra を確認する。
実データに近い環境で、追加前後の計画を比較する。

「カーディナリティが高いカラムを必ず先頭にする」と単純化されることがあります。カーディナリティは、カラム内の値の種類の多さを指します。実務では値の種類だけでなく、クエリの条件、結合順、ORDER BY、LIMIT、データ分布を合わせて確認します。

インデックス設計の手順

インデックスはテーブルからではなく、遅いクエリから設計します。テーブル定義だけを見て「このカラムは検索されそう」と考えるより、実際に実行される SQL と頻度を見たほうが判断しやすくなります。

1. 遅いクエリを特定する

まず、アプリケーションログやスロークエリログなどから、改善したい SQL を特定します。似たクエリが複数ある場合は、頻度が高いもの、ユーザー影響が大きいもの、実行時間が長いものから見ます。

2. WHERE・JOIN・ORDER BY を分解する

次に、クエリのどこで行を絞っているかを見ます。

SELECT id, user_id, status, ordered_at
FROM orders
WHERE user_id = 1001
  AND status = 'paid'
  AND ordered_at >= '2026-05-01 00:00:00'
ORDER BY ordered_at DESC
LIMIT 20;

この例では、user_id と status が等価条件、ordered_at が範囲条件かつ並び替え条件です。候補としては、(user_id, status, ordered_at) のような複合インデックスを検討できます。

3. インデックスを追加して EXPLAIN を比較する

検証環境でインデックスを追加し、追加前後の EXPLAIN を比較します。

ALTER TABLE orders
  ADD INDEX idx_orders_user_status_ordered_at (user_id, status, ordered_at);

比較するときは、key に期待したインデックスが出ているかだけでなく、rows の見積もりが下がっているか、Extra に想定外の Using filesort などが出ていないかを確認します。

読み替えの目安は、追加前は key = NULL で、rows の見積もりが大きい状態です。追加後に key へ候補インデックスが入り、rows が小さくなるなら、少なくとも実行計画上は改善の方向にあります。

4. 既存インデックスとの重複を確認する

新しいインデックスを追加する前に、既存のインデックスと役割が重複していないかを確認します。

たとえば、次の 2 つがある場合を考えます。

INDEX idx_orders_user_id (user_id),
INDEX idx_orders_user_status_ordered_at (user_id, status, ordered_at)

(user_id, status, ordered_at) は左端プレフィックスとして (user_id) の検索にも使えます。そのため、idx_orders_user_id が本当に必要かは確認対象になります。ただし、削除してよいとは限りません。インデックスサイズ、更新頻度、別クエリでの使われ方、制約の有無を見て判断します。

よくある失敗例

失敗例 1: 単一カラムのインデックスを並べるだけ

私も以前、user_id、status、ordered_at それぞれに単一カラムのインデックスを貼り、EXPLAIN の possible_keys に候補が出たことで安心していたことがありました。ところが、本番に近い負荷で見ると rows の見積もりが思ったほど下がらず、組み合わせ条件に対する効き方が弱いことに気づきました。

次のように単一カラムのインデックスを複数作るだけでは、狙ったクエリに対して最適とは限りません。

INDEX idx_orders_user_id (user_id),
INDEX idx_orders_status (status),
INDEX idx_orders_ordered_at (ordered_at)

クエリが user_id、status、ordered_at を組み合わせて使うなら、複合インデックスを検討する価値があります。MySQL には、複数のインデックスを組み合わせて使う Index Merge 最適化もあります。ただし、複数の単一カラムインデックスを作れば常に最適になる、とは考えないほうが安全です。この場合は EXPLAIN の key、rows、Extra を見て、実際にどのインデックスが使われているか確認します。

失敗例 2: 複合インデックスの途中から使おうとする

(user_id, status, ordered_at) があるからといって、status だけの検索に効くとは限りません。

SELECT id, user_id, status, ordered_at
FROM orders
WHERE status = 'paid'
ORDER BY ordered_at DESC
LIMIT 20;

このクエリでは左端の user_id 条件がありません。status を起点に検索したいなら、別のインデックス、たとえば (status, ordered_at) のような設計を検討します。ただし、似たインデックスを増やすほど更新コストも増えるため、クエリ頻度と影響度を見て判断します。

確認するときは、SHOW INDEX FROM orders; で既存インデックスを見てから、候補インデックスを検証環境で追加します。そのうえで、EXPLAIN の key と rows を比較します。

失敗例 3: SELECT する列を見ていない

Extra に Using index が出る場合、必要な列をインデックスだけで取得できている可能性があります。いわゆるカバリングインデックスです。

カバリングインデックスは、必要な列をインデックスだけで返せる状態です。ただし、カバリングを狙って多くの列をインデックスに含めると、インデックスが大きくなります。読み取りの改善と、書き込み・ストレージの負担を比較します。

まずは SELECT する列を必要最小限にした場合と、列を増やした場合の EXPLAIN を比べると、どこから追加の読み取りが発生しそうかを確認しやすくなります。

実務でのチェックリスト

インデックスを追加する前に、次の順番で確認すると整理しやすいです。

観点	確認すること
クエリ	改善したい SQL は特定できているか
条件	WHERE、JOIN、ORDER BY、LIMIT の使われ方を分解したか
実行計画	`EXPLAIN` で `key`、`rows`、`filtered`、`Extra` を見たか
複合順序	左端プレフィックスに沿った順序になっているか
重複	既存インデックスと役割が重なっていないか
更新コスト	INSERT、UPDATE、DELETE が増えすぎないか
本番差分	検証環境のデータ量・分布が本番に近いか