MySQL インデックスの基礎：作成と管理
インデックスはデータベースのパフォーマンスを最適化する上で重要な役割を果たします。インデックスのないクエリは、目次を見ずに辞書で単語を調べるようなものです。
このレッスンでは、インデックスの仕組みについて解説するとともに、その作成方法や管理方法についても説明します。
1. 学習内容
- インデックスとは何ですか？また、なぜインデックスが必要なのでしょうか？
- B+木インデックス構造の原理
- CREATE INDEX / ALTER TABLE: インデックスを作成する
- インデックスの表示と削除
- インデックスの長所と短所
2. 実際の事例
(1) 課題：クエリの実行速度が遅すぎる
100万件のユーザーレコード、メールアドレスで検索：
SQL
SELECT * FROM users WHERE email = 'alice@email.com';
-- Execution time: 2.5 seconds (Full Table Scan)
(2) インデックスに関する解決策
SQL
CREATE INDEX idx_email ON users(email);
-- Search again: 0.003 seconds (Index Lookup)
| 次元 | インデックスなし | インデックスあり |
|---|---|---|
| クエリ方式 | フルテーブルスキャン | B+ツリー検索 |
| 100万行のクエリ | 2～5秒 | 0.01秒未満 |
| 書き込みパフォーマンス | 追加のオーバーヘッドなし | 若干遅くなる（インデックスのメンテナンスが必要） |
3. インデックスの仕組み
(1) B+木構造
graph TB
R[Root Node<br/>10 | 30 | 50] --> L1[1-10]
R --> L2[11-30]
R --> L3[31-50]
R --> L4[51+]
L1 --> D1[Data: 1,3,5,7,9]
L1 --> D2[Data: 2,4,6,8,10]
L2 --> D3[Data: 11,15,20]
L2 --> D4[Data: 25,28,30]
L3 --> D5[Data: 31,35,40]
L3 --> D6[Data: 45,48,50]
(2) インデックスの種類
| タイプ | 説明 | 利用例 |
|---|---|---|
| B+木インデックス | デフォルトのインデックスタイプ | 等値/範囲/ソート済み |
| ハッシュインデックス | ハッシュテーブル | 等価クエリ（Memory Engine） |
| 全文インデックス | テキスト検索 | 記事内容検索 |
| 空間インデックス | GISデータ | 地理位置情報クエリ |
4. インデックスの作成
▶ 例：インデックスの作成
SQL
-- Methods1：CREATE INDEX
CREATE INDEX idx_email ON users(email);
-- Unique Index
CREATE UNIQUE INDEX idx_email ON users(email);
-- Methods2：ALTER TABLE
ALTER TABLE users ADD INDEX idx_username (username);
-- Composite Index（Composite Index）
CREATE INDEX idx_name_email ON users(username, email);
-- Prefix Index
CREATE INDEX idx_email_prefix ON users(email(10));
5. 索引を表示する
▶ 例：インデックス情報の表示
SQL
-- View all indexes on the table
SHOW INDEX FROM users;
-- View Index Information
SHOW INDEX FROM users\G
出力：
TEXT
+-------+------------+----------+--------------+-------------+
| Table | Key_name | Seq_in_index | Column_name | Index_type |
+-------+------------+--------------+-------------+------------+
| users | PRIMARY | 1 | id | BTREE |
| users | idx_email | 1 | email | BTREE |
+-------+------------+--------------+-------------+------------+
6. インデックスの削除
▶ 例：インデックスの削除
SQL
-- Methods1：DROP INDEX
DROP INDEX idx_email ON users;
-- Methods2：ALTER TABLE
ALTER TABLE users DROP INDEX idx_email;
-- Delete Primary Key
ALTER TABLE users DROP PRIMARY KEY;
7. インデックスのユースケース
| シナリオ | インデックスを作成すべきか？ | 理由 |
|---|---|---|
| WHERE 条件フィールド | ✅ | クエリの高速化 |
| JOIN フィールド | ✅ | 高速結合 |
| 並べ替え対象フィールド | ✅ | 並べ替えを避ける |
| GROUP BY フィールド | ✅ | 高速グループ化 |
| 選択性の高い分野 | ✅ | 高い識別力 |
| 頻繁な現場での更新 | ❌ | 高い維持管理コスト |
| 選択性の低い項目 | ❌ | 性別（男／女）など |
| データ量の少ないテーブル | ❌ | フルテーブルスキャンの方が高速 |
❓ よくある質問
Q インデックスは多ければ多いほど良いのでしょうか？
A いいえ。インデックスはディスク容量を消費し、書き込みパフォーマンスを低下させます。頻繁にクエリされるフィールドにのみインデックスを作成してください。
Q 主キーには自動的にインデックスが作成されますか？
A はい。PRIMARY KEY には自動的にクラスタ化インデックスが作成され、UNIQUE キーには自動的に一意インデックスが作成されます。
Q インデックスが必要かどうかはどうやって判断すればよいですか？
A
EXPLAIN を使用してクエリプランを分析し、インデックスが使用されているかどうかを確認してください。Q インデックスは多ければ多いほど良いのでしょうか？
A いいえ。インデックスは書き込みパフォーマンスを低下させるため、1つのテーブルに設定するインデックスは5～6個以内に抑えることをお勧めします。
Q 主キーは常にクラスタ化インデックスですか？
A InnoDB ではそうです。主キーはクラスタ化インデックスです。MyISAM では、主キーは非クラスタ化インデックスです。
📖 まとめ
- インデックスはクエリの実行を高速化しますが、書き込みパフォーマンスを低下させます
- B+木はデフォルトのインデックス構造であり、完全一致、範囲クエリ、およびソート済みクエリに対応しています。
- CREATE INDEX で作成、DROP INDEX で削除
- 適用対象：WHERE／JOIN／ORDER BY／GROUP BY の各フィールド
- 該当なし：頻繁な更新、選択性の低さ、小規模なテーブル
📝 練習問題
-
基本問題（難易度 ⭐）：
usersテーブルのemailフィールドに一意インデックスを作成してください。 -
応用問題（難易度 ⭐⭐）：複合インデックス
(department, salary)を作成し、最左前置子原則が成り立つことを確認してください。 -
チャレンジ問題（難易度：⭐⭐⭐）：
EXPLAINを使用して、インデックスありとなしのクエリのパフォーマンスの違いを比較してください。
