MySQL のインデックスとは？仕組みと効果的な使い方を解説
テーブルの行数が増えてきたタイミングで、これまで一瞬で返っていた SELECT 文が急に遅くなったという経験がある人は少なくないと思います。多くの場合、原因は検索条件に使っている列にインデックスが設定されていないことにあります。インデックスは、書籍の索引のように、特定の値がどこにあるかをあらかじめ整理しておくことで、テーブル全体を走査せずに目的の行へたどり着けるようにする仕組みです。この記事では、MySQL のインデックスがどのような構造で動作しているのかを解説したうえで、作成方法、複合インデックスにおける列の順序、EXPLAIN 文を使った効果の確認方法、インデックスが効かなくなるケースまで、実務でつまずきやすいポイントを中心に紹介します。
インデックスとは何か
インデックスは、テーブルの特定の列（複数列の組み合わせも可能）の値と、その値を持つ行の場所を対応づけて保持しておく、検索を高速化するためのデータ構造です。インデックスが存在しない状態で特定の条件に一致する行を探す場合、データベースはテーブルの先頭から末尾まで全ての行を順番に確認する必要があります。これをフルテーブルスキャンと呼び、テーブルの行数に比例して処理時間が増えていきます。
インデックスを設定しておくと、データベースは索引の中から条件に一致する値を効率的に絞り込み、該当する行だけに直接アクセスできるようになります。検索対象の行数が数百万件を超えるようなテーブルでは、インデックスの有無によって応答時間が数百倍から数千倍変わることも珍しくないです。一方で、インデックスは万能ではなく、テーブルへの書き込み負荷やディスク使用量の増加といった代償も伴うため、どの列に設定するかを見極めることが重要になります。
MySQL で使われる B-Tree インデックスの仕組み
MySQL でよく利用されるストレージエンジンである InnoDB では、インデックスの実装に B-Tree（Balanced Tree、平衡木）と呼ばれるデータ構造が使われています。B-Tree は、根（ルート）から葉（リーフ）に向かって枝分かれしていく木構造で、どの葉ノードまでの深さもほぼ均一に保たれるように設計されています。この特性によって、テーブルの行数が増えても、目的の値にたどり着くまでの探索回数が緩やかにしか増加しないという利点があります。
B-Tree の各ノードには、値の範囲とその範囲に対応する子ノードへのポインタが格納されています。検索を行う際は、ルートノードから条件に合致する範囲を選び、該当する子ノードへと降りていく処理を繰り返し、最終的に目的のデータが格納されている葉ノードに到達します。この構造は等号（=）による検索だけでなく、範囲検索（BETWEEN、<、> など）や並び替え（ORDER BY）にも効率的に対応できる点が特徴です。
InnoDB のテーブルには、主キーの値をもとに行データそのものが B-Tree の形で格納されるクラスタインデックスという仕組みがあります。主キー以外の列に作成したインデックス（セカンダリインデックスと呼びます）は、その列の値と主キーの値だけを保持する別の B-Tree として管理され、実際の行データを取得する際には主キーを使ってクラスタインデックスを再度検索する形になります。この二段階の検索が発生する点は、後述するカバリングインデックスを理解するうえで重要な前提です。
https://dev.mysql.com/doc/refman/8.4/en/mysql-indexes.html
インデックスの作成方法
インデックスは、テーブル作成時に指定する方法と、既存のテーブルに後から追加する方法の 2 通りがあります。既存のテーブルに追加する場合は CREATE INDEX 文を使います。
CREATE INDEX idx_users_email ON users (email);
このように 1 つの列に対してインデックスを作成することを単一列インデックスと呼びます。命名規則に厳密な決まりはないですが、idx_テーブル名_列名 のように、どのテーブルのどの列に対するインデックスかがひと目でわかる名前を付けておくと、後から見直す際に管理がしやすいです。
作成済みのインデックスが不要になった場合は、DROP INDEX 文で削除できます。例えば、試しに prefecture 列に作成したインデックスを削除する場合は次のように実行します。
CREATE INDEX idx_users_prefecture ON users (prefecture);
DROP INDEX idx_users_prefecture ON users;
インデックスの追加や削除は、テーブルの行数が多い場合には時間のかかる操作になることがあります。InnoDB では多くの場合オンラインでの追加に対応していますが、本番環境で実行する際は事前にテーブルのサイズや実行時間の見積もりを確認しておくと安心です。
https://dev.mysql.com/doc/refman/8.4/en/create-index.html
複合インデックスと列の順序
複数の列を組み合わせて 1 つのインデックスを作成することもでき、これを複合インデックス（あるいは複合列インデックス）と呼びます。例えば、ユーザーの都道府県と登録日で絞り込む検索が頻繁に行われる場合、以下のように 2 つの列をまとめてインデックス化します。
CREATE INDEX idx_users_prefecture_created_at ON users (prefecture, created_at);
複合インデックスを作成する際にもっとも重要なのが列の並び順です。B-Tree 構造の複合インデックスは、先頭の列を基準に値がソートされ、その中で次の列がソートされるという入れ子の構造になっています。そのため、このインデックスは先頭の prefecture 列だけを条件にした検索や、prefecture と created_at の両方を条件にした検索には効果を発揮しますが、created_at 列だけを条件にした検索には利用されないです。この性質は左端一致（leftmost prefix）の原則と呼ばれています。
検索条件でよく使われる列の組み合わせと、その頻度をもとに列の順序を決めることが、複合インデックスを設計するうえでの基本方針になります。以下の表は、列の並び順によって検索条件がインデックスの恩恵を受けられるかどうかをまとめたものです。
| 検索条件 | (prefecture, created_at) の場合 |
|---|---|
WHERE prefecture = ? | 利用できる |
WHERE prefecture = ? AND created_at = ? | 利用できる |
WHERE created_at = ? のみ | 利用できない |
なお、カーディナリティ（列に含まれる値の種類の多さ）が高い列を先頭に置いたほうが良いという説明を見かけることもありますが、実際には検索条件としてどの列の組み合わせが頻繁に使われるかを優先して決めるほうが実務上は有効なことが多いです。
https://dev.mysql.com/doc/refman/8.4/en/multiple-column-indexes.html
EXPLAIN でインデックスの効果を確認する
インデックスを作成したあと、そのインデックスが実際にクエリで使われているかどうかを確認する方法として、EXPLAIN 文があります。SELECT 文の先頭に EXPLAIN を付けて実行すると、MySQL がそのクエリをどのように実行する計画を立てているかを確認できます。
EXPLAIN SELECT * FROM users WHERE email = 'example@example.com';
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | users | NULL | ref | idx_users_email | idx_users_email | 1022 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
実行結果にはいくつかの列が表示されますが、特に注目したいのは以下の項目です。
| 列名 | 意味 |
|---|---|
type | アクセス方法の種類。ALL はフルテーブルスキャンを示し、ref や range はインデックスが使われていることを示す |
possible_keys | 使用できる可能性のあるインデックスの一覧 |
key | 実際に選択されたインデックス |
rows | 実行計画上で走査される見込みの行数 |
Extra | 追加情報。Using index と表示されれば、テーブル本体にアクセスせずインデックスだけでクエリが完結している |
type の列が ALL になっている場合はインデックスが使われずフルテーブルスキャンが行われていることを意味するので、想定していたインデックスが利用されているかを確認する際にまず見るべき項目です。また rows の値が想定より大きい場合は、インデックスの選択自体は行われていても、絞り込みの効果が薄いことを示しているケースもあります。
MySQL 8.0 以降では、EXPLAIN の出力を人が読みやすいツリー形式で表示する EXPLAIN FORMAT=TREE も利用できます。複数テーブルを結合するクエリで実行すると、どの順番でテーブルにアクセスしているかが階層的に表示され、実行計画の全体像をつかみやすくなります。
EXPLAIN FORMAT=TREE
SELECT u.email, o.status
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.prefecture = '東京都';
-> Nested loop inner join (cost=450 rows=100)
-> Table scan on o (cost=100 rows=1000)
-> Filter: (u.prefecture = '東京都') (cost=0.25 rows=0.1)
-> Single-row index lookup on u using PRIMARY (id=o.user_id) (cost=0.25 rows=1)
インデントが深い行ほど先に実行される処理を表しています。この例では orders テーブルの全件スキャンから始まり、そこで見つかった行 1 件ごとに users テーブルを主キーで検索していることがわかります。orders テーブルの user_id 列にはまだインデックスを作成していないため、結合の起点として全件スキャンが選ばれています。
https://dev.mysql.com/doc/refman/8.4/en/explain.html
カバリングインデックス
先ほど触れたとおり、InnoDB のセカンダリインデックスは、条件に一致する行の主キーの値をまず見つけたあとに、その主キーを使って実際の行データを再検索するという二段階の処理を行います。この二段階目の検索をテーブルアクセスと呼び、インデックスの検索と比べて相対的にコストの高い処理になります。
もし SELECT 文で取得したい列がすべてインデックスの中に含まれている場合、この二段階目のテーブルアクセスを省略でき、インデックスの検索結果だけでクエリが完結します。このような状態になるインデックスをカバリングインデックスと呼びます。
CREATE INDEX idx_orders_user_status ON orders (user_id, status);
SELECT user_id, status FROM orders WHERE user_id = 123;
上記の例では、SELECT 句で指定している user_id と status の両方がインデックスに含まれているため、テーブル本体へのアクセスが発生しないカバリングインデックスとして機能します。取得する列数が多いテーブルや、行のサイズが大きいテーブルほど、カバリングインデックスによる効果は大きくなる傾向があります。ただし、インデックスに含める列を増やしすぎるとインデックス自体のサイズが大きくなり、書き込み時のコストが増えるため、頻度の高いクエリに絞って適用を検討するのがよいでしょう。
実際に EXPLAIN を付けて実行すると、Extra 列に Using index と表示され、テーブル本体へのアクセスが発生していないことを確認できます。
EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 123;
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | orders | NULL | ref | idx_orders_user_status | idx_orders_user_status | 4 | const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+------+------------------------+------------------------+---------+-------+------+----------+-------------+
インデックスが効かないケース
インデックスを作成していても、クエリの書き方によっては期待どおりに利用されないことがあります。代表的なパターンをいくつか紹介します。
1 つ目は、インデックスが設定されている列に対して関数や演算を適用している場合です。以下のように列側を加工してしまうと、インデックスに保存されている値と一致しなくなるため、フルテーブルスキャンになりやすいです。ここでは created_at 列単体にインデックスを作成した状態で、EXPLAIN を使って挙動を確認します。
CREATE INDEX idx_users_created_at ON users (created_at);
EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2026;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 1000 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
created_at にインデックスを作成しているにもかかわらず type が ALL となっており、フルテーブルスキャンが発生していることがわかります。この場合は、比較する側の値を加工する形に書き換えることで、インデックスを利用できるようになります。
EXPLAIN SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
+----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | users | NULL | range | idx_users_created_at | idx_users_created_at | 5 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+----------------------+----------------------+---------+------+------+----------+-----------------------+
書き換え後は type が range に変わり、key に idx_users_created_at が選択されていることが確認できます。
2 つ目は、文字列の前方一致ではなく部分一致で検索している場合です。LIKE '%keyword%' のようにワイルドカードを先頭に付けた検索は、B-Tree インデックスのソート順を活用できないため、インデックスが効かないです。前方一致の LIKE 'keyword%' であれば、通常のインデックスでも効果があります。
3 つ目は、複合インデックスの左端一致の原則から外れる検索を行っている場合です。前述のとおり、複合インデックスの先頭の列を条件に含めていないと、そのインデックスは利用されないことが多いです。
4 つ目は、テーブルの統計情報が古く、オプティマイザがインデックスを使わない実行計画のほうが速いと誤って判断してしまう場合です。この場合は ANALYZE TABLE 文で統計情報を更新すると改善することがあります。
ANALYZE TABLE users;
インデックスを使う際の注意点
インデックスは検索を高速化する一方で、いくつかの代償も伴うため、闇雲に増やせばよいというものではないです。
書き込み性能への影響がもっとも大きな代償です。INSERT や UPDATE、DELETE を実行するたびに、対象の行だけでなく、その行に関連するすべてのインデックスも更新する必要があります。インデックスの数が多いテーブルほど、書き込み時の処理コストが増加する傾向があります。
ディスク使用量が増える点も見落としやすいポイントです。インデックスはテーブル本体とは別に専用の領域を必要とするため、特に複合インデックスを多数作成していると、テーブル本体よりもインデックス全体のサイズのほうが大きくなることもあります。
使われていないインデックスが放置されているケースも実務ではよく見られます。過去に追加したものの、その後クエリの条件が変わってしまい、実質的に利用されなくなったインデックスは、書き込み負荷だけを増やす無駄なコストになります。sys.schema_unused_indexes のようなビューを定期的に確認し、使われていないインデックスを洗い出す運用を取り入れると、テーブルを健全な状態に保ちやすいです。
まとめ
MySQL のインデックスについて、B-Tree による仕組みから作成方法、複合インデックスの列順序、EXPLAIN を使った効果の確認方法、そしてインデックスが効かなくなるケースまでを解説しました。実際にテーブルを設計・運用する際は、以下の点を意識しておくとよいでしょう。
- インデックスは検索対象の行数を絞り込むための仕組みで、B-Tree 構造によって等号検索だけでなく範囲検索や並び替えにも効率的に対応できる
- 複合インデックスを作成する際は、検索条件でよく使われる列の組み合わせをもとに、左端一致の原則を踏まえた列の順序を選ぶ
- 取得したい列がすべてインデックスに含まれるカバリングインデックスを活用すると、テーブル本体への追加アクセスを省略できる
- インデックスが実際に使われているかどうかは EXPLAIN 文で確認でき、
typeやExtraの列から実行計画の詳細を読み取れる - インデックスは書き込み性能やディスク使用量の増加という代償も伴うため、使われていないインデックスは定期的に見直す