データが増えるにつれて、SELECT文の応答が遅くなる場面があります。インデックス（索引）はその問題を根本から解決する仕組みです。MySQLではほとんどのインデックスにB-tree（バランス木）構造を採用しており、数十万〜数百万行のテーブルでも特定のレコードを素早く絞り込めます。この記事では、B-treeの概念、CREATE INDEXの基本構文、インデックスを貼るべき列・貼ってはいけない列の判断基準、よくある落とし穴まで実例で解説します。
このシナリオで考える
ECサイトの受注テーブルordersを例に使います。まず構造とサンプルデータを作成しておきます。
CREATE TABLE orders (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
status ENUM('pending','shipped','completed','cancelled') NOT NULL,
total DECIMAL(10,2) NOT NULL,
ordered_at DATETIME NOT NULL
);
INSERT INTO orders (user_id, status, total, ordered_at) VALUES
(101, 'completed', 3200.00, '2026-01-10 09:15:00'),
(102, 'shipped', 8900.00, '2026-02-14 11:30:00'),
(101, 'completed', 1500.00, '2026-03-01 16:45:00'),
(103, 'pending', 5600.00, '2026-04-20 10:00:00');
このテーブルが100万行まで成長したとき、WHERE user_id = 101の検索をどう速くするかが今回のテーマです。
インデックスとは何か — B-treeの仕組み
インデックスなしでは、MySQLはデータを格納したページを先頭から順に全部読みます（フルテーブルスキャン）。100万行あれば最悪の場合、目的の行を見つけるまでに100万回の比較が必要です。
インデックスを貼ると、MySQLはB-tree（バランス木）と呼ばれる別のデータ構造を参照します。B-treeは値をソートした状態で木構造に格納しており、辞書の索引のように素早く目的の値まで絞り込めます。100万行のテーブルでも、ルートノード→ブランチノード→リーフノードとたどる20ステップ前後で目的のレコードに到達できます。
| 構成要素 | 役割 |
|---|---|
| ルートノード | 検索の起点。テーブルに1つ存在する |
| ブランチノード | 中間の分岐点。「値がAより大きければ右の枝へ」のように振り分ける |
| リーフノード | 実際のキー値と、対応する行へのポインタを保持する |
MySQLのプライマリキーはクラスタードインデックスと呼ばれる特殊なB-treeで、リーフノードに行データ本体を格納します。プライマリキー検索は1回のアクセスで完結します。一方、user_idなどに貼るセカンダリインデックスのリーフにはプライマリキー値だけが入っており、そこから行データを引き直します（二段階アクセス）。
B-treeは=だけでなく、>・<・BETWEEN・INの範囲検索にも有効です。値がソート済みのため、「101以上の値が並ぶリーフ」まで一気にたどれます。
CREATE INDEXの基本構文
インデックスを追加する方法は主に2つです。
テーブル作成時に定義する
CREATE TABLE orders (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
status ENUM('pending','shipped','completed','cancelled') NOT NULL,
ordered_at DATETIME NOT NULL,
INDEX idx_user_id (user_id),
INDEX idx_ordered_at (ordered_at)
);
既存テーブルに追加する
-- シングルカラム
ALTER TABLE orders ADD INDEX idx_user_id (user_id);
-- 複合インデックス（左の列から順に使う）
ALTER TABLE orders ADD INDEX idx_user_ordered (user_id, ordered_at);
-- CREATE INDEX 文でも同じ
CREATE INDEX idx_status ON orders (status);
現在のインデックス一覧はSHOW INDEX FROM orders;で確認できます。
複合インデックスには左端マッチの原則があります。(user_id, ordered_at)というインデックスなら、WHERE user_id = 101やWHERE user_id = 101 AND ordered_at > '2026-01-01'には有効ですが、WHERE ordered_at > '2026-01-01'だけではフルスキャンになります。左端の列を省いた検索では使われません。
インデックスを貼るべき列・貼ってはいけない列
インデックスは検索を速くする一方、INSERT / UPDATE / DELETE のたびに更新コストが発生します。貼りすぎると書き込みが遅くなり、ディスクも圧迫します。
| 列の特徴 | 判断 | 理由 |
|---|---|---|
| WHERE / JOIN ON で頻繁に絞り込む | ◎ 貼る | 検索が劇的に速くなる |
| ORDER BY / GROUP BY に使う | ○ 検討する | ソート処理をスキップできる場合がある |
| カーディナリティが低い列（status・フラグ等） | ✕ 原則不要 | 値の種類が少なく絞り込み効果が薄い |
| 更新頻度が非常に高い列 | △ 慎重に | 書き込みのたびにインデックス更新コストが発生 |
| 行数が数千件以下の小テーブル | △ 不要なことが多い | フルスキャンの方が速いとオプティマイザが判断する |
カーディナリティとは、カラムが持つ値の種類の多さです。user_idはユーザー数だけ値の種類があり高カーディナリティです。statusは4種類しかなく低カーディナリティになります。カーディナリティが低い列にインデックスを貼ると、絞り込んでも大量の行が残るため、MySQLが「インデックスよりフルスキャンの方が速い」と判断してインデックスを使わないことがあります。
よくある落とし穴
インデックスを貼っても、クエリの書き方次第では無効になります。代表的な3パターンを押さえておきましょう。
1. 列を関数で囲む
-- ✕ YEAR() でラップするとインデックスが効かない
SELECT * FROM orders WHERE YEAR(ordered_at) = 2026;
-- ○ 範囲指定に書き直すとインデックスが使われる
SELECT * FROM orders
WHERE ordered_at >= '2026-01-01' AND ordered_at < '2027-01-01';
インデックスは列の生の値に対して作られています。YEAR(ordered_at)という式の結果にはインデックスがないため、全行を評価し直すことになります。
2. LIKEの前方ワイルドカード
-- ✕ 前方ワイルドカードはB-treeを先頭から辿れない
SELECT * FROM products WHERE name LIKE '%テレビ';
-- ○ 後方がワイルドカード（前方一致）ならインデックスが効く
SELECT * FROM products WHERE name LIKE 'テレビ%';
3. 型の暗黙変換
-- ✕ user_id は INT 型。文字列で渡すと暗黙変換が起き、インデックスが使われない場合がある
SELECT * FROM orders WHERE user_id = '101';
-- ○ 型を合わせて渡す
SELECT * FROM orders WHERE user_id = 101;
インデックスが実際に使われているかはEXPLAINで確認できます。
EXPLAIN SELECT * FROM orders WHERE user_id = 101;
出力のkey列にインデックス名が表示されていれば使用中、NULLなら使われていません。チューニングの第一歩はEXPLAINを読む習慣をつけることです。
まとめ
インデックスはB-tree構造で値をソート保持し、フルスキャンを大幅に削減します。CREATE INDEXで後から追加でき、WHERE / JOIN / ORDER BY でよく使う高カーディナリティな列が主な貼り先です。列を関数で囲む・前方ワイルドカード・型の不一致はインデックスを無効化する代表的な落とし穴です。迷ったらEXPLAINで確認する習慣をつけましょう。
参考リンク
アイキャッチ画像: Photo by Team Nocoloco on Unsplash
