MySQL クエリと EXPLAIN 実行計画の概要
インデックスの高度な機能を理解してこそ、真に高性能なクエリを作成することができます。
このレッスンでは、インデックスの種類とEXPLAIN解析について詳しく解説します。
1. 学習内容
- クラスタ化インデックスと非クラスタ化インデックスの比較
- カバレッジ指数
- 複合インデックスと最左前置子
- インデックスが非アクティブになる状況
- EXPLAINの実行計画の解釈
2. クラスタ化インデックスと非クラスタ化インデックス
| ディメンション | クラスタ化インデックス | 非クラスタ化インデックス |
|---|---|---|
| 保存方法 | データとインデックスは一緒に保存される | インデックスはデータのアドレスを指し示す |
| 数量 | 1つまで | 複数可 |
| 主キー | InnoDB は自動的に主キーを使用します | セカンダリインデックス |
| クエリ | データを直接取得 | テーブルの参照が必要 |
(1) テーブルでの検索
graph LR
A[Secondary Index<br/>idx_email] -->|Primary Key Value| B[Clustered Index<br/>PRIMARY]
B -->|Complete Data| C[Data Row]
SQL
-- The process of table lookup
SELECT * FROM users WHERE email = 'alice@email.com';
-- 1. Find id=1 in idx_email
-- 2. Find complete data for id=1 in PRIMARY
3. カバレッジ・インデックス
クエリに含まれるすべてのフィールドがインデックスに含まれているため、テーブルを参照する必要はありません。
▶ 例：オーバーレイインデックス
SQL
-- Create a composite index
CREATE INDEX idx_name_email ON users(username, email);
-- Covering Index（No need to return to the table）
SELECT username, email FROM users WHERE username = 'alice';
-- Non-covering index（Need to return to the table）
SELECT * FROM users WHERE username = 'alice';
| タイプ | テーブルに戻る | パフォーマンス |
|---|---|---|
| 索引 | 番号 | 高速 |
| 非カバレッジインデックス | はい | 遅い |
4. 複合インデックスと最左プレフィックス
(1) 最左前置詞の原則
複合インデックス (a, b, c) は、以下のクエリで使用できます：
SQL
WHERE a = 1 -- ✅ Using index
WHERE a = 1 AND b = 2 -- ✅ Using index
WHERE a = 1 AND b = 2 AND c = 3 -- ✅ Using index
WHERE b = 2 -- ❌ Index not used
WHERE b = 2 AND c = 3 -- ❌ Index not used
▶ 例：インデックスの使用状況の確認
SQL
CREATE INDEX idx_abc ON orders(customer_id, status, order_date);
-- ✅ Using index
EXPLAIN SELECT * FROM orders WHERE customer_id = 1;
EXPLAIN SELECT * FROM orders WHERE customer_id = 1 AND status = 'paid';
EXPLAIN SELECT * FROM orders WHERE customer_id = 1 AND status = 'paid' AND order_date > '2026-01-01';
-- ❌ Index not used (Violates the Leftmost Prefix Rule)
EXPLAIN SELECT * FROM orders WHERE status = 'paid';
5. インデックスが効果を発揮しなくなるケース
| シナリオ | 例 | 理由 |
|---|---|---|
| 関数の演算 | WHERE YEAR(date) = 2026 |
関数で囲まれたインデックス |
| 暗黙の型変換 | WHERE phone = 13800001111 |
数値を用いた文字列配列の検索 |
| LIKE Left Fuzzy | WHERE name LIKE '%john' |
接頭辞不明 |
| OR 条件 | WHERE a = 1 OR b = 2 |
一部のフィールドにインデックスが設定されていない |
| NOT IN/NOT EXISTS | WHERE id NOT IN (1,2) |
オプティマイザがフルテーブルスキャンを選択 |
| IS NULL/IS NOT NULL | WHERE col IS NULL |
NULLの割合による |
▶ 例：インデックスの障害と復旧
SQL
-- ❌ Index Invalidation
SELECT * FROM users WHERE YEAR(created_at) = 2026;
-- ✅ Fix: Change to a range query
SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
-- ❌ Index Invalidation
SELECT * FROM users WHERE phone = 13800001111;
-- ✅ Fix: Use quotes
SELECT * FROM users WHERE phone = '13800001111';
6. 実行計画のEXPLAIN
▶ 例：EXPLAIN の使用
SQL
EXPLAIN SELECT * FROM users WHERE email = 'alice@email.com';
出力：
TEXT
+----+-------+------+---------+------+----------+-------+
| id | type | key | key_len | ref | rows | Extra |
+----+-------+------+---------+------+----------+-------+
| 1 | const | idx_email | 402 | const | 1 | |
+----+-------+------+---------+------+----------+-------+
(1) 主要フィールドの説明
| フィールド | 説明 | 有効な値 |
|---|---|---|
| 型 | アクセス種別 | const > eq_ref > ref > range > index > ALL |
| キー | 使用されるインデックス | NULL不可 |
| 行数 | スキャン対象となる行数の推定値 | 数値が低いほど良い |
| 補足 | 追加情報 | インデックスの使用（カバリング・インデックス） |
(2) アクセス型
| 種類 | 説明 | メリット・デメリット |
|---|---|---|
| const | 主キー／一意インデックスによる等値クエリ | ⭐⭐⭐ |
| eq_ref | 結合には主キーまたは一意インデックスを使用する | ⭐⭐⭐ |
| ref | 一意でないインデックスに対する等値クエリ | ⭐⭐ |
| 範囲 | インデックス範囲クエリ | ⭐⭐ |
| インデックス | インデックスの完全スキャン | ⭐ |
| すべて | テーブル全体のスキャン | ❌ |
❓ よくある質問
Q 複合インデックスのフィールド順序はどのように決めるべきですか？
A 選択性の高いフィールドを先に配置し、クエリの頻度が高いフィールドを先に配置してください。
Q EXPLAIN の「rows」の値は正確ですか？
A これらは推定値であり、正確な値ではありません。ただし、クエリの効率を評価する目安としては利用できます。
Q インデックスが効果を発揮しなくなった場合はどうすればよいですか？
A インデックスが効果を発揮しなくなる状況を避けるようSQLを書き直すか、関数インデックスを作成してください（MySQL 8.0以降）。
Q EXPLAIN の「rows」の値は正確ですか？
A これらは正確な値ではなく推定値ですが、相対的な変化を比較することで、最適化の効果を評価することができます。ANALYZE TABLE を使用して統計情報を更新してください。
Q プレフィックスインデックスはどのような場合に使用すべきですか？
A VARCHARフィールドが非常に長く、かつプレフィックスが十分に識別力がある場合です。例えば、
INDEX(email(10))のように、最初の10文字の識別力が95%を超える場合です。📖 まとめ
- クラスタ化インデックスはデータとインデックスを一緒に格納するのに対し、非クラスタ化インデックスではテーブルの参照が必要となります。
- カバレッジインデックス：すべてのクエリフィールドがインデックスに含まれているため、テーブルを参照する必要がありません。
- 最左側のプレフィックス：複合インデックスは左から右へと照合されます
- インデックスの無効化が発生するシナリオ：関数、暗黙の型変換、左ファジー、OR
- EXPLAIN タイプ、キー、行数に焦点を当てて、クエリプランを分析する
📝 練習問題
-
基本問題（難易度：⭐）：
EXPLAINを使用してクエリを分析し、インデックスが使用されているかどうかを判断してください。 -
応用問題（難易度 ⭐⭐）：左端プレフィックス原則を検証するための複合インデックスを作成してください。
-
課題問題（難易度：⭐⭐⭐）：インデックスが効果を発揮しない3つのシナリオを挙げ、その解決策を提案してください。
