🎯 STEP 13: インデックス設計戦略
インデックスを適切に設計し、クエリパフォーマンスを劇的に改善しよう
📋 このステップで学ぶこと
- インデックスとは何か、どのように動作するか
- インデックスを貼るべきカラムの判断基準
- 複合インデックスの設計と順序の決め方
- カバリングインデックスの活用
- クエリパフォーマンス改善の実践テクニック
学習時間の目安： 2.5時間 ｜ 前提知識： SQL基礎、テーブル設計の基本を理解していること
🎯 1. インデックスとは？
STEP 12からの続き：Part 3 実践設計編のスタート
STEP 7〜12では正規化について学びました。このSTEP 13からはPart 3「実践設計編」が始まります。正規化されたテーブルを実際に高速で動作させるための技術を学んでいきましょう。
まず最初に学ぶのはインデックス設計です。どんなに美しく正規化されたテーブルでも、インデックスがなければ検索性能は悲惨なことになります。
インデックスの基本
インデックス（Index）とは、データベースのデータに素早くアクセスするための仕組みです。本の索引（さくいん）と同じイメージです。
📚 例え話：本の索引
500ページある本から「データベース」という単語が書かれているページを探すとき：
索引なし：1ページ目から順番に全ページをめくって探す → 時間がかかる
索引あり：巻末の索引で「データベース」を探し、該当ページ（例：p.123, p.245）に直接飛ぶ → 超高速
データベースのインデックスも、まったく同じ原理です！
インデックスがない場合 vs ある場合
❌ インデックスがない場合（フルテーブルスキャン）
— 100万件のusersテーブルから特定のメールアドレスを検索
SELECT * FROM users WHERE email = ‘yamada@example.com’;
— インデックスなし → 全行スキャン
— 実行時間: 約2.5秒
— 読み取り行数: 1,000,000行
データベースは全ての行を1つずつチェックする必要があります。
✅ インデックスがある場合
— email カラムにインデックスを作成
CREATE INDEX idx_users_email ON users(email);
— 同じクエリを実行
SELECT * FROM users WHERE email = ‘yamada@example.com’;
— インデックスあり → 直接アクセス
— 実行時間: 約0.01秒（250倍高速！）
— 読み取り行数: 1行のみ
インデックスを使って該当データに直接ジャンプできます。
インデックスの仕組み
📊 インデックスの内部構造（B-Treeインデックス）
ほとんどのデータベースはB-Tree（バランスツリー）というデータ構造を使っています：
- データがソートされた状態で木構造に保存される
- 二分探索のように、効率的に目的のデータを見つけられる
- 100万件のデータでも、約20回程度の比較で目的のデータに到達
- 挿入・削除時も、木構造を自動的にバランス調整する
🔍 2. インデックスを貼るべきカラムの判断基準
すべてのカラムにインデックスを貼れば良いわけではありません。適切なカラムにだけインデックスを貼ることが重要です。
インデックスを貼るべきカラム
✅ インデックスを貼ると効果的なカラム
- 主キー（Primary Key）
→ 自動的にインデックスが作成される - 外部キー（Foreign Key）
→ JOIN処理で頻繁に使われるため - WHERE句で頻繁に使うカラム
→ 検索条件として使われる - ORDER BY句で使うカラム
→ ソート処理が高速化される - GROUP BY句で使うカラム
→ 集計処理が高速化される - カーディナリティが高いカラム
→ 重複が少なく、値の種類が多い（例：メールアドレス、電話番号）
インデックスを貼るべきでないカラム
❌ インデックスを貼っても効果が薄い（または逆効果の）カラム
- カーディナリティが低いカラム
→ 値の種類が少ない（例：性別、ステータス（true/false）、都道府県） - ほとんど検索に使わないカラム
→ インデックス維持のコストが無駄 - 頻繁に更新されるカラム
→ 更新のたびにインデックスも更新が必要で、パフォーマンス低下 - 小さいテーブル（数千行以下）
→ フルスキャンでも十分高速
実例で理解する
📝 usersテーブルの例
CREATE TABLE users (
user_id INT PRIMARY KEY, — 自動的にインデックス作成
email VARCHAR(255) UNIQUE, — 自動的にインデックス作成
username VARCHAR(50),
password_hash VARCHAR(255),
gender VARCHAR(10), — 性別（M/F/Other）
is_active BOOLEAN, — アクティブかどうか
created_at TIMESTAMP,
last_login_at TIMESTAMP
);
インデックス設計の判断：
| カラム | インデックス | 理由 |
|---|---|---|
| user_id | ✅ 自動 | PRIMARY KEYは自動的にインデックスが作成される |
| ✅ 自動 | UNIQUE制約により自動的にインデックスが作成される | |
| username | ✅ 推奨 | ログイン時にusernameで検索することが多い |
| password_hash | ❌ 不要 | 検索に使わない |
| gender | ❌ 不要 | カーディナリティが低い（値が3種類程度） |
| is_active | ⚠️ 状況次第 | 頻繁に「アクティブユーザーのみ」検索するなら有効 |
| created_at | ✅ 推奨 | 「特定期間のユーザー」を検索することが多い |
| last_login_at | ✅ 推奨 | 「最近ログインしたユーザー」の検索に使う |
推奨インデックス設計：
— 主キーとUNIQUEは自動作成されるので不要
— username での検索用
CREATE INDEX idx_users_username ON users(username);
— 登録日での範囲検索用
CREATE INDEX idx_users_created_at ON users(created_at);
— 最終ログイン日での範囲検索用
CREATE INDEX idx_users_last_login ON users(last_login_at);
🔧 3. 複合インデックスの設計
複合インデックス（Composite Index）とは、複数のカラムを組み合わせたインデックスです。
複合インデックスが必要なケース
📝 例：商品検索システム
products テーブル
—————————————
product_id | product_name | category_id | price | stock | created_at
— よくあるクエリ
SELECT * FROM products
WHERE category_id = 10 AND price BETWEEN 1000 AND 5000
ORDER BY created_at DESC;
インデックス設計の選択肢：
- 単一インデックス：category_id と price にそれぞれ別々のインデックス
- 複合インデックス：(category_id, price, created_at) の複合インデックス
複合インデックスの順序が重要
💡 複合インデックスの順序ルール
複合インデックス (A, B, C) は、以下のクエリで有効です：
- ✅ WHERE A = …
- ✅ WHERE A = … AND B = …
- ✅ WHERE A = … AND B = … AND C = …
- ❌ WHERE B = …
- ❌ WHERE C = …
- ❌ WHERE B = … AND C = …
つまり、インデックスは「左から順番に」使われます！
複合インデックスの順序の決め方
📋 順序を決める3つの原則
原則1：等価条件（=）を先に
— クエリ例
WHERE category_id = 10 AND price BETWEEN 1000 AND 5000
— インデックス順序
CREATE INDEX idx_products ON products(category_id, price);
— category_id（=）を先に、price（範囲）を後に
原則2：カーディナリティが高いものを先に（等価条件が複数ある場合）
— クエリ例
WHERE prefecture = ‘東京都’ AND gender = ‘M’
— prefecture: 47種類
— gender: 3種類
— インデックス順序
CREATE INDEX idx_users ON users(prefecture, gender);
— カーディナリティが高い prefecture を先に
原則3：よく使う条件を先に
— クエリ1（頻度: 高）
WHERE status = ‘active’
— クエリ2（頻度: 低）
WHERE status = ‘active’ AND created_at > ‘2025-01-01’
— インデックス順序
CREATE INDEX idx_users ON users(status, created_at);
— よく使う status を先に、created_at は後に
実例：複合インデックスの効果
📊 パフォーマンス比較
シナリオ：100万件の products テーブルから検索
SELECT * FROM products
WHERE category_id = 10 AND price BETWEEN 1000 AND 5000
ORDER BY created_at DESC
LIMIT 20;
| インデックス設計 | 実行時間 | 読み取り行数 |
|---|---|---|
| インデックスなし | 3.2秒 | 1,000,000行 |
| category_id のみ | 0.8秒 | 50,000行 |
| price のみ | 1.5秒 | 200,000行 |
| (category_id, price) | 0.05秒 | 2,000行 |
| (category_id, price, created_at) | 0.01秒 | 20行 |
結論：適切な複合インデックスで320倍の高速化を実現！
⚡ 4. カバリングインデックス
カバリングインデックス（Covering Index）とは、クエリが必要とする全てのカラムを含むインデックスのことです。
カバリングインデックスの仕組み
📝 通常のインデックス vs カバリングインデックス
通常のインデックス：
— インデックス: (category_id)
SELECT product_id, product_name, price
FROM products
WHERE category_id = 10;
— 処理フロー:
— 1. インデックスで category_id = 10 の行を特定
— 2. 実テーブルにアクセスして product_name, price を取得 ← 追加のアクセスが必要
カバリングインデックス：
— カバリングインデックス: (category_id, product_name, price)
SELECT product_id, product_name, price
FROM products
WHERE category_id = 10;
— 処理フロー:
— 1. インデックスで category_id = 10 の行を特定
— 2. インデックス内に必要な全カラムがあるので、実テーブルへのアクセス不要 ← 超高速！
ポイント：カバリングインデックスを使うと、テーブル本体を読む必要がなくなるため、さらに高速化されます。
カバリングインデックスの設計例
✅ カバリングインデックスの活用例
— よくあるクエリ
SELECT user_id, username, email
FROM users
WHERE is_active = true
ORDER BY created_at DESC
LIMIT 100;
— カバリングインデックス
CREATE INDEX idx_users_covering
ON users(is_active, created_at, user_id, username, email);
— このインデックスには:
— – WHERE条件: is_active
— – ORDER BY: created_at
— – SELECT対象: user_id, username, email
— が全て含まれているので、テーブル本体へのアクセスが不要！
⚠️ カバリングインデックスの注意点
- インデックスサイズが大きくなる（多くのカラムを含むため）
- 更新時のオーバーヘッドが増える
- 全てのクエリに対してカバリングインデックスを作るのは現実的ではない
- 頻繁に実行される重要なクエリにのみ適用する
🎯 5. インデックス設計のベストプラクティス
インデックス数の適正化
💡 インデックスは「多ければ良い」わけではない
インデックスのデメリット：
- ストレージ使用量が増える：インデックスもディスク容量を消費する
- 書き込み（INSERT/UPDATE/DELETE）が遅くなる：データ変更のたびにインデックスも更新が必要
- メモリ使用量が増える：インデックスもメモリにキャッシュされる
目安：1つのテーブルに5〜10個以内のインデックスが適切。それ以上は慎重に検討。
インデックス設計チェックリスト
📋 インデックスを作成する前のチェックリスト
- ✅ このカラムはWHERE句で頻繁に使われるか？
- ✅ カーディナリティが十分高いか？（値の種類が多いか）
- ✅ テーブルのサイズは十分大きいか？（数万行以上）
- ✅ このインデックスでパフォーマンスが本当に改善されるか？（実測する）
- ✅ 既存のインデックスと重複していないか？
- ✅ 書き込み性能への影響は許容範囲か？
実践的なインデックス設計フロー
🔄 インデックス設計の実践フロー
1
主要なクエリを特定する
最も頻繁に実行されるクエリ、パフォーマンスが重要なクエリをリストアップ
2
EXPLAIN で実行計画を確認
インデックスが使われているか、フルテーブルスキャンになっていないかを確認
3
インデックスを設計・作成
WHERE句、JOIN、ORDER BYで使うカラムに対してインデックスを作成
4
パフォーマンステスト
インデックス作成前後で実行時間を測定し、効果を検証
5
継続的な監視と最適化
スロークエリログを監視し、必要に応じてインデックスを追加・削除
🛠️ 6. EXPLAIN でクエリパフォーマンスを確認
EXPLAINは、SQLの実行計画を確認するコマンドです。インデックスが使われているかを確認できます。
EXPLAIN の使い方
— EXPLAIN をクエリの前につけるだけ
EXPLAIN SELECT * FROM users WHERE email = ‘yamada@example.com’;
— 実行結果（簡略版）
+—-+——-+——-+——+——+————-+
| id | type | key | rows | Extra |
+—-+——-+——-+——+—————–+
| 1 | ref | idx_email | 1 | Using index |
+—-+——-+——-+——+—————–+
EXPLAIN の読み方
| 項目 | 説明 |
|---|---|
| type |
アクセス方法 ✅ const, eq_ref, ref = インデックス使用（高速） ❌ ALL = フルテーブルスキャン（遅い） |
| key |
使用されたインデックス名 NULL = インデックス未使用 |
| rows |
検査される推定行数 少ないほど良い |
| Extra |
追加情報 ✅ Using index = カバリングインデックス使用 ⚠️ Using filesort = ソート処理が必要 ⚠️ Using temporary = 一時テーブル使用 |
実例：インデックスあり vs なし
❌ インデックスがない場合
EXPLAIN SELECT * FROM users WHERE email = ‘yamada@example.com’;
+—-+——+——+———+————-+
| id | type | key | rows | Extra |
+—-+——+——+———+————-+
| 1 | ALL | NULL | 1000000 | Using where |
+—-+——+——+———+————-+
— type = ALL → フルテーブルスキャン
— key = NULL → インデックス未使用
— rows = 1000000 → 100万行すべてをチェック
✅ インデックスがある場合
CREATE INDEX idx_users_email ON users(email);
EXPLAIN SELECT * FROM users WHERE email = ‘yamada@example.com’;
+—-+——+—————–+——+————-+
| id | type | key | rows | Extra |
+—-+——+—————–+——+————-+
| 1 | ref | idx_users_email | 1 | Using index |
+—-+——+—————–+——+————-+
— type = ref → インデックス使用
— key = idx_users_email → インデックス使用
— rows = 1 → 1行だけチェック（100万倍効率的！）
📝 7. インデックス設計のまとめ
✅ インデックス設計の重要ポイント
- 主キー、外部キー、WHERE句で使うカラムにインデックスを貼る
- カーディナリティが高いカラムを優先する
- 複合インデックスの順序は重要（等価条件 → 範囲条件 → ソート条件）
- カバリングインデックスで更なる高速化
- EXPLAIN で実行計画を確認し、インデックスが使われているか検証
- インデックスは多すぎず、少なすぎず、適切な数を維持
- 継続的な監視と最適化が重要
💡 実務での心構え
インデックス設計は「推測ではなく測定」が鉄則です。
✅ EXPLAINで実行計画を確認
✅ 実行時間を測定
✅ インデックス作成前後で比較
✅ 本番環境に近いデータ量でテスト
「このカラムにインデックスを貼れば速くなるだろう」という推測だけで判断せず、必ず実測して効果を確認しましょう！
📝 練習問題
問題 1
基礎
インデックスとは何か、自分の言葉で説明してください。
【解答例】
インデックスとは、データベースのデータに素早くアクセスするための仕組みです。本の索引（さくいん）のように、特定のデータを探すときに全体を順番に見ていく必要がなく、直接目的のデータにジャンプできます。
例えば、100万件のユーザーテーブルから特定のメールアドレスを探す場合、インデックスがなければ全100万件をチェックする必要がありますが、インデックスがあれば数十回の比較で目的のデータを見つけられます。
問題 2
基礎
以下のusersテーブルで、インデックスを貼るべきカラムを選び、その理由を説明してください。
CREATE TABLE users (
user_id INT PRIMARY KEY,
email VARCHAR(255),
username VARCHAR(50),
password_hash VARCHAR(255),
age INT,
gender VARCHAR(10),
prefecture VARCHAR(20),
is_premium BOOLEAN,
created_at TIMESTAMP,
last_login_at TIMESTAMP
);
— よくあるクエリ
— 1. WHERE email = ‘…’
— 2. WHERE username = ‘…’
— 3. WHERE prefecture = ‘…’ AND age > 18
— 4. WHERE is_premium = true ORDER BY last_login_at DESC
【解答】
推奨インデックス：
— 1. email（ログイン、パスワードリセットなどで頻繁に使用）
CREATE INDEX idx_users_email ON users(email);
— 2. username（ログイン、検索で頻繁に使用）
CREATE INDEX idx_users_username ON users(username);
— 3. 都道府県と年齢での検索用
CREATE INDEX idx_users_prefecture_age ON users(prefecture, age);
— 4. プレミアム会員の最終ログイン順表示用
CREATE INDEX idx_users_premium_login ON users(is_premium, last_login_at);
— 5. 登録日での範囲検索用
CREATE INDEX idx_users_created_at ON users(created_at);
理由：
- user_id: PRIMARY KEYなので自動的にインデックス作成済み
- email, username: ログインや検索で頻繁に使われる、カーディナリティが高い
- (prefecture, age): 複合インデックス。等価条件（prefecture）を先に、範囲条件（age）を後に
- (is_premium, last_login_at): WHERE条件とORDER BYの両方に対応
- password_hash: 検索に使わないのでインデックス不要
- gender: カーディナリティが低い（3種類程度）ので優先度低い
問題 3
標準
複合インデックス (A, B, C) は、どのクエリで有効ですか？全て答えてください。
CREATE INDEX idx_abc ON table(A, B, C);
— クエリ1
SELECT * FROM table WHERE A = 1;
— クエリ2
SELECT * FROM table WHERE B = 2;
— クエリ3
SELECT * FROM table WHERE A = 1 AND B = 2;
— クエリ4
SELECT * FROM table WHERE A = 1 AND B = 2 AND C = 3;
— クエリ5
SELECT * FROM table WHERE A = 1 AND C = 3;
— クエリ6
SELECT * FROM table WHERE B = 2 AND C = 3;
【解答】
インデックスが有効なクエリ：
- ✅ クエリ1: WHERE A = 1
→ インデックスの先頭カラムAを使用するので有効 - ❌ クエリ2: WHERE B = 2
→ 先頭カラムAがないため、インデックスは使われない - ✅ クエリ3: WHERE A = 1 AND B = 2
→ AとBの両方を使用するので有効 - ✅ クエリ4: WHERE A = 1 AND B = 2 AND C = 3
→ A、B、Cの全てを使用するので最も効率的 - ⚠️ クエリ5: WHERE A = 1 AND C = 3
→ Aは使われるが、Cは使われない（Bがスキップされるため） - ❌ クエリ6: WHERE B = 2 AND C = 3
→ 先頭カラムAがないため、インデックスは使われない
重要ポイント：
複合インデックスは「左から順番に」使われます。途中のカラムをスキップすると、それ以降のカラムはインデックスとして機能しません。
問題 4
標準
以下のクエリに最適な複合インデックスを設計してください。インデックスのカラム順序とその理由も説明してください。
SELECT product_id, product_name, price
FROM products
WHERE category_id = 10
AND price BETWEEN 1000 AND 5000
AND stock > 0
ORDER BY created_at DESC
LIMIT 20;
【解答】
CREATE INDEX idx_products_optimal
ON products(category_id, stock, price, created_at);
カラム順序の理由：
- category_id（最初）：等価条件（=）なので最優先
- stock（2番目）：等価条件ではないが、範囲条件より絞り込み効果が高い
- price（3番目）：範囲条件（BETWEEN）
- created_at（最後）：ORDER BY用。インデックスがソート済みなので、ソート処理が不要になる
さらに効果を高めるには（カバリングインデックス）：
CREATE INDEX idx_products_covering
ON products(category_id, stock, price, created_at, product_id, product_name);
SELECT対象のカラム（product_id, product_name）もインデックスに含めることで、テーブル本体へのアクセスが不要になり、さらに高速化されます。
問題 5
応用
以下の3つのクエリがあります。最小限のインデックス数で全てのクエリを最適化するには、どのようなインデックスを作成すべきですか？
— クエリ1（頻度: 高）
SELECT * FROM orders
WHERE customer_id = 100
ORDER BY order_date DESC;
— クエリ2（頻度: 中）
SELECT * FROM orders
WHERE customer_id = 100
AND status = ‘completed’;
— クエリ3（頻度: 低）
SELECT * FROM orders
WHERE order_date BETWEEN ‘2025-01-01’ AND ‘2025-01-31’;
【解答】
推奨インデックス（2つ）：
— インデックス1: クエリ1とクエリ2の両方に対応
CREATE INDEX idx_orders_customer_status_date
ON orders(customer_id, status, order_date);
— インデックス2: クエリ3専用
CREATE INDEX idx_orders_order_date
ON orders(order_date);
各インデックスの役割：
| クエリ | 使用インデックス | 理由 |
|---|---|---|
| クエリ1 | idx_orders_customer_status_date | customer_id で絞り込み、order_date でソート |
| クエリ2 | idx_orders_customer_status_date | customer_id と status の両方で絞り込み |
| クエリ3 | idx_orders_order_date | order_date の範囲検索 |
設計のポイント：
- 1つの複合インデックスで複数のクエリに対応：(customer_id, status, order_date) は、クエリ1（customer_id のみ）とクエリ2（customer_id + status）の両方で有効
- 頻度の低いクエリは別インデックス：クエリ3は customer_id を使わないため、別インデックスが必要
- インデックス数の最小化：3つのクエリに対して、2つのインデックスで対応
問題 6
応用
以下のEXPLAIN結果を見て、問題点を指摘し、改善策を提案してください。
— クエリ
SELECT u.user_id, u.username, COUNT(o.order_id) as order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE u.created_at >= ‘2024-01-01’
AND o.status = ‘completed’
GROUP BY u.user_id, u.username
ORDER BY order_count DESC
LIMIT 10;
— EXPLAIN結果
+—-+——-+——-+——+——-+—————————+
| id | type | table | key | rows | Extra |
+—-+——-+——-+——+——-+—————————+
| 1 | ALL | u | NULL | 50000 | Using where; Using filesort |
| 1 | ALL | o | NULL | 200000| Using where |
+—-+——-+——-+——+——-+—————————+
【解答】
問題点の指摘：
- 両テーブルでフルテーブルスキャン（type = ALL）：インデックスが使われていない
- users テーブルで5万行、orders テーブルで20万行をスキャン：非常に非効率
- Using filesort：ソート処理が必要（ORDER BY用のインデックスがない）
- key = NULL：どちらのテーブルもインデックスが使われていない
改善策：
— 1. users テーブルに created_at のインデックス
CREATE INDEX idx_users_created_at ON users(created_at);
— 2. orders テーブルに user_id と status の複合インデックス
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
— 改善後のEXPLAIN結果（予測）
+—-+——-+——-+———————-+——-+——————-+
| id | type | table | key | rows | Extra |
+—-+——-+——-+———————-+——-+——————-+
| 1 | range | u | idx_users_created_at | 10000 | Using where; Using temporary |
| 1 | ref | o | idx_orders_user_status | 50 | Using where |
+—-+——-+——-+———————-+——-+——————-+
改善のポイント：
- ✅ users.created_at にインデックス：WHERE条件での絞り込みが高速化（50000行 → 10000行）
- ✅ (orders.user_id, orders.status) の複合インデックス：JOINとWHERE条件の両方で有効（200000行 → 50行程度）
- ✅ type が range と ref に改善：インデックスが使われるようになった
- ⚠️ filesort は残る：COUNT結果でのソートなので、インデックスでは解決できない（集計後のソート）
問題 7
応用
【総合問題】以下のテーブルとクエリに対して、最適なインデックス設計を行ってください。CREATE INDEX文を全て書いてください。
— テーブル定義
CREATE TABLE posts (
post_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
category_id INT NOT NULL,
title VARCHAR(200) NOT NULL,
content TEXT NOT NULL,
view_count INT DEFAULT 0,
is_published BOOLEAN DEFAULT true,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (category_id) REFERENCES categories(category_id)
);
— よくあるクエリ（頻度順）
— クエリ1（頻度: 最高）
SELECT post_id, title, created_at
FROM posts
WHERE is_published = true
ORDER BY created_at DESC
LIMIT 20;
— クエリ2（頻度: 高）
SELECT * FROM posts
WHERE user_id = 100
ORDER BY created_at DESC;
— クエリ3（頻度: 高）
SELECT * FROM posts
WHERE category_id = 5
AND is_published = true
ORDER BY view_count DESC
LIMIT 10;
— クエリ4（頻度: 中）
SELECT post_id, title
FROM posts
WHERE category_id = 5
AND created_at >= ‘2025-01-01’;
— クエリ5（頻度: 低）
SELECT COUNT(*) FROM posts
WHERE user_id = 100
AND is_published = true;
【解答】
推奨インデックス設計：
— インデックス1: クエリ1用（最も頻繁に実行されるクエリ）
— カバリングインデックスで最大限の高速化
CREATE INDEX idx_posts_published_created
ON posts(is_published, created_at, post_id, title);
— インデックス2: クエリ2とクエリ5用
CREATE INDEX idx_posts_user_created
ON posts(user_id, created_at);
— インデックス3: クエリ3用
CREATE INDEX idx_posts_category_published_views
ON posts(category_id, is_published, view_count);
— インデックス4: クエリ4用
CREATE INDEX idx_posts_category_created
ON posts(category_id, created_at, post_id, title);
— 外部キーには自動的にインデックスが作成される場合がありますが、
— 念のため確認し、なければ以下を追加
— CREATE INDEX idx_posts_user_id ON posts(user_id);
— CREATE INDEX idx_posts_category_id ON posts(category_id);
各インデックスの役割：
| インデックス | 対象クエリ | 設計理由 |
|---|---|---|
| (is_published, created_at, post_id, title) | クエリ1 | カバリングインデックス。SELECT対象の全カラムを含むため、テーブル本体へのアクセス不要 |
| (user_id, created_at) | クエリ2, 5 | user_id で絞り込み、created_at でソート。クエリ5のCOUNTにも有効 |
| (category_id, is_published, view_count) | クエリ3 | category_id と is_published で絞り込み、view_count でソート |
| (category_id, created_at, post_id, title) | クエリ4 | category_id と created_at で絞り込み。post_id, title を含めてカバリングインデックス化 |
最適化のポイント：
- ✅ 頻度の高いクエリはカバリングインデックス：クエリ1とクエリ4
- ✅ 複数のクエリで共用できるインデックス：(user_id, created_at) はクエリ2と5で共用
- ✅ インデックスの順序を最適化：等価条件 → 範囲条件 → ソート条件の順
- ✅ 外部キーのインデックス：JOIN性能を考慮
- ⚠️ インデックス数は4〜5個：適切な範囲内で、書き込み性能への影響も考慮
❓ よくある質問
Q1: すべてのカラムにインデックスを貼れば、すべてのクエリが速くなりますか？
いいえ、逆効果になる可能性があります。インデックスにはデメリットもあります。書き込み（INSERT/UPDATE/DELETE）が遅くなる、ストレージ使用量が増える、メモリ使用量が増える、などです。適切なバランスが重要で、目安として1つのテーブルに5〜10個程度のインデックスが適切です。
Q2: カーディナリティとは何ですか？どのくらいあれば「高い」と言えますか？
カーディナリティとは、「値の種類の多さ」です。カーディナリティが高い例としては、メールアドレス（ほぼユニーク）、電話番号、注文IDなどがあります。低い例としては、性別（2〜3種類）、都道府県（47種類）、ステータス（true/false）などです。目安として、全体の10%以上が同じ値の場合、カーディナリティが低いと判断できます。
Q3: 複合インデックス (A, B) と、単一インデックス A, B を別々に作るのは、どう違いますか？
大きく違います。複合インデックス (A, B) は WHERE A = … AND B = … のクエリで非常に効率的で、WHERE A = … のクエリでも使えますが、WHERE B = … のクエリでは使えません。一方、単一インデックスA と B を別々に作った場合、それぞれのカラムでの検索には使えますが、WHERE A = … AND B = … のクエリでは効率が複合インデックスより劣ります。頻繁に一緒に使われるカラムは、複合インデックスにすることをおすすめします。
Q4: EXPLAINの結果で、type が ALL でも問題ない場合はありますか？
はい、以下の場合は問題ありません。テーブルのサイズが小さい（数千行以下）場合、テーブルの大部分（20%以上）を取得する場合、頻繁に実行されないクエリの場合です。重要なのは、頻繁に実行される重要なクエリでフルスキャンが発生していないかを確認することです。
Q5: インデックスを作成したのに、使われていません。なぜですか？
いくつかの理由が考えられます。カラムに関数を適用している場合（WHERE YEAR(created_at) = 2025）、暗黙的な型変換がある場合、LIKE ‘%keyword%’ のような前方一致以外の検索の場合、OR条件で複数のカラムを指定している場合、統計情報が古い場合などです。EXPLAINで確認し、どのインデックスが使われているか（または使われていないか）を確認しましょう。
Q6: 本番環境でインデックスを追加するとき、注意点はありますか？
はい、重要な注意点があります。インデックス作成中はテーブルがロックされる可能性があるため、オフピーク時間に実行すること、MySQL 5.6以降ではONLINE DDLを使用すること、ステージング環境でテストすること、ロールバック計画を準備しておくことが重要です。推奨コマンドは CREATE INDEX idx_name ON table(column) ALGORITHM=INPLACE, LOCK=NONE; です。
学習メモ
データベース設計・データモデリング - Step 13
📋 過去のメモ一覧
▼