🎨 Step 15: インデックス設計の実践

効果的なインデックスを設計しよう!

📋 このステップで学ぶこと

インデックスの作成・削除・確認方法
単一列インデックスと複合インデックスの使い分け
カーディナリティ（値の種類）を考慮した設計
WHERE句、JOIN、ORDER BYでの効果
実践的なインデックス設計パターン

🎯 1. インデックスの基本操作
🔍 2. インデックスの確認と削除
🔢 3. 単一列インデックス vs 複合インデックス
📊 4. カーディナリティを考慮する
🔍 5. WHERE句での効果
🔗 6. JOINでの効果
📈 7. ORDER BYでの効果
🎨 8. 実践的なインデックス設計
1. 8-1. 設計の流れ
2. 8-2. 実例：ECサイトのインデックス設計
📝 練習問題
📝 Step 15 のまとめ
❓ よくある質問

🎯 1. インデックスの基本操作

1-1. インデックス作成の基本構文

📌 インデックス作成の構文

※横にスクロールできます

— 基本形
CREATE INDEX インデックス名 ON テーブル名(列名);

— ユニークインデックス（重複不可）
CREATE UNIQUE INDEX インデックス名 ON テーブル名(列名);

— 複合インデックス（複数列）
CREATE INDEX インデックス名 ON テーブル名(列1, 列2, 列3);

各部分の意味:

CREATE INDEX → インデックスを作成する命令
UNIQUE → 重複した値を許さない（省略可）
インデックス名 → 自分で決める名前
ON テーブル名(列名) → どのテーブルのどの列にインデックスを作るか

1-2. インデックス名の命名規則

インデックス名は自由に決められますが、後で分かりやすいように命名規則を決めておくと便利です。

💡 おすすめの命名規則

パターン	例	説明
idx_テーブル_列	idx_customers_email	最も一般的
idx_列	idx_email	シンプル
idx_列1_列2	idx_prefecture_city	複合インデックス

idx_ はindexの略です。ix_ や i_ を使う場合もあります。

⚠️ 避けるべき命名

index1, my_index → 何のインデックスか分からない
temp_idx → 一時的なもののように見える
日本語のインデックス名 → 環境によってエラーになる可能性

1-3. 例1：メールアドレスにユニークインデックス

目的：メールアドレスの検索を高速化し、重複登録を防ぐ

※横にスクロールできます

CREATE UNIQUE INDEX idx_customers_email ON customers(email);

クエリの解説:

UNIQUE → 同じメールアドレスは登録できなくなる
idx_customers_email → インデックス名（customersテーブルのemail列）
ON customers(email) → customersテーブルのemail列に作成

このインデックスを作成すると、以下のクエリが高速化されます。

※横にスクロールできます

— このクエリでインデックスが使われる
SELECT * FROM customers WHERE email = ‘tanaka@example.com’;

1-4. 例2：都道府県に通常インデックス

目的：都道府県での検索を高速化する（重複OK）

※横にスクロールできます

CREATE INDEX idx_customers_prefecture ON customers(prefecture);

クエリの解説:

UNIQUE なし → 同じ都道府県の人が何人いてもOK
東京都の人が1万人いても、インデックスは正常に動作する

1-5. 例3：注文日にインデックス（範囲検索用）

目的：日付での範囲検索を高速化する

※横にスクロールできます

CREATE INDEX idx_orders_order_date ON orders(order_date);

このインデックスを作成すると、以下のような範囲検索が高速化されます。

※横にスクロールできます

— 範囲検索でもインデックスが使われる
SELECT * FROM orders 
WHERE order_date >= ‘2024-01-01’ 
  AND order_date < '2024-02-01';

🔍 2. インデックスの確認と削除

2-1. 作成したインデックスを確認する

テーブルにどんなインデックスがあるか確認できます。

※横にスクロールできます

— テーブルのインデックス一覧を表示
PRAGMA index_list(‘customers’);

実行結果イメージ:

seq	name	unique
0	idx_customers_email	1
1	idx_customers_prefecture	0

unique = 1 はユニークインデックス、0 は通常インデックスです。

2-2. インデックスの詳細を確認する

※横にスクロールできます

— インデックスの詳細（どの列が含まれるか）を表示
PRAGMA index_info(‘idx_customers_email’);

2-3. 全インデックスを一覧表示

※横にスクロールできます

— データベース内の全インデックスを表示
SELECT name, tbl_name, sql 
FROM sqlite_master 
WHERE type = ‘index’;

2-4. インデックスを削除する

不要になったインデックスは削除できます。インデックスが多すぎると更新処理が遅くなるため、使っていないインデックスは削除しましょう。

※横にスクロールできます

— インデックスを削除
DROP INDEX idx_customers_prefecture;

— インデックスが存在する場合のみ削除（エラー回避）
DROP INDEX IF EXISTS idx_customers_prefecture;

📌 インデックス削除の注意点

削除すると、そのインデックスを使っていたクエリが遅くなる
本番環境で削除する前に、影響を確認しよう
主キーのインデックスは削除できない（PRIMARY KEYと一体）

🔢 3. 単一列インデックス vs 複合インデックス

3-1. 単一列インデックスとは

単一列インデックスは、1つの列だけにインデックスを作成するものです。最もシンプルな形式です。

※横にスクロールできます

— 単一列インデックスの例
CREATE INDEX idx_email ON customers(email);
CREATE INDEX idx_prefecture ON customers(prefecture);
CREATE INDEX idx_order_date ON orders(order_date);

✅ 単一列インデックスが向いている場合

その列単独で検索されることが多い
複数の列を別々に検索する
シンプルで管理しやすい

3-2. 複合インデックスとは

複合インデックスは、複数の列を組み合わせて1つのインデックスにするものです。

※横にスクロールできます

— 複合インデックスの例
CREATE INDEX idx_location ON customers(prefecture, city);
CREATE INDEX idx_category_price ON products(category, price);
CREATE INDEX idx_customer_date ON orders(customer_id, order_date);

✅ 複合インデックスが向いている場合

複数の列を一緒に検索することが多い
WHERE句に複数の条件がある
インデックスの数を減らしたい

3-3. 複合インデックスの重要なルール（左端一致）

複合インデックスには左端一致の原則という重要なルールがあります。インデックスは左側の列から順番に使われます。

📌 左端一致の原則とは

複合インデックス (prefecture, city) がある場合:

クエリ	インデックス使用	理由
WHERE prefecture = ‘東京都’	✅ 使われる	左端の列を使っている
WHERE prefecture = ‘東京都’ AND city = ‘渋谷区’	✅ 使われる	左から順番に使っている
WHERE city = ‘渋谷区’	❌ 使われない	左端のprefectureがない!

これは本の索引で例えると分かりやすいです。

💡 本の索引で例えると…

「都道府県 → 市区町村」の順に整理された住所録があるとします。

「東京都」を探す → 簡単!（東京都のページに直接行ける）
「東京都 → 渋谷区」を探す → 簡単!（東京都のページの中で渋谷区を探す）
「渋谷区」だけを探す → 大変!（全ての都道府県をチェックする必要がある）

3-4. 具体例：インデックスが使われるケース

※横にスクロールできます

— 複合インデックスを作成
CREATE INDEX idx_location ON customers(prefecture, city);

— ✅ 使われる：左端の列を使っている
SELECT * FROM customers WHERE prefecture = ‘東京都’;

— ✅ 使われる：両方の列を順番に使っている
SELECT * FROM customers WHERE prefecture = ‘東京都’ AND city = ‘渋谷区’;

— ❌ 使われない：左端のprefectureがない!
SELECT * FROM customers WHERE city = ‘渋谷区’;

3-5. 単一列インデックス2つ vs 複合インデックス1つ

「2つの列で検索する場合、単一列インデックスを2つ作るのと、複合インデックスを1つ作るのはどちらが良いか?」という疑問がよくあります。

比較：prefecture AND city での検索

パターン	インデックス	効果
単一列 × 2	idx_prefecture, idx_city	どちらか1つしか使われない
複合 × 1	idx_location(prefecture, city)	両方の条件で効率的に検索

※横にスクロールできます

— パターン1：単一列インデックス2つ
CREATE INDEX idx_prefecture ON customers(prefecture);
CREATE INDEX idx_city ON customers(city);

— パターン2：複合インデックス1つ
CREATE INDEX idx_location ON customers(prefecture, city);

— このクエリを実行した場合…
SELECT * FROM customers 
WHERE prefecture = ‘東京都’ AND city = ‘渋谷区’;

— パターン1：prefectureまたはcityのインデックスどちらか1つだけが使われる
— パターン2：両方の条件でインデックスが効く → より高速!

💡 選び方のポイント

一緒に検索する → 複合インデックス
別々に検索する → 単一列インデックス
迷ったら実際のクエリパターンを分析

📊 4. カーディナリティを考慮する

4-1. カーディナリティとは

カーディナリティとは、列に含まれる「値の種類の数」のことです。カーディナリティが高いほど、インデックスの効果が高くなります。

カーディナリティの例:

列	値の例	カーディナリティ	インデックス効果
customer_id	1, 2, 3, … (全員違う)	超高（10万種類）	◎ 効果大
email	全員違うメールアドレス	超高	◎ 効果大
birth_date	1980-01-01〜2005-12-31	中（数千種類）	○ 効果あり
prefecture	東京都、大阪府、…	低（47種類）	△ 効果薄い
gender	男、女	超低（2種類）	× ほぼ効果なし
is_active	0, 1	超低（2種類）	× ほぼ効果なし

4-2. なぜカーディナリティが重要なのか

カーディナリティが低い列（例：gender）にインデックスを作っても、検索の絞り込み効果が薄いためです。

💡 例：10万人の顧客から検索

email で検索（カーディナリティ高）:

10万人 → 1人に絞り込める（99.999%削減）
インデックスの効果: 非常に大きい

gender で検索（カーディナリティ低）:

10万人 → 5万人に絞り込める（50%削減）
インデックスの効果: ほとんどない（結局5万行を読む）

4-3. カーディナリティの確認方法

実際のデータでカーディナリティを確認してみましょう。

※横にスクロールできます

— カーディナリティを確認するクエリ
SELECT 
    COUNT(DISTINCT prefecture) AS unique_values,   — 値の種類の数
    COUNT(*) AS total_rows,                        — 総行数
    ROUND(
        CAST(COUNT(DISTINCT prefecture) AS REAL) / COUNT(*) * 100, 
        3
    ) AS cardinality_percent                       — カーディナリティ（%）
FROM customers;

実行結果イメージ（prefecture列）:

unique_values	total_rows	cardinality_percent
47	100,000	0.047%

47種類 / 10万行 = 0.047%（カーディナリティ低い）

※横にスクロールできます

— email列のカーディナリティを確認
SELECT 
    COUNT(DISTINCT email) AS unique_values,
    COUNT(*) AS total_rows,
    ROUND(
        CAST(COUNT(DISTINCT email) AS REAL) / COUNT(*) * 100, 
        1
    ) AS cardinality_percent
FROM customers;

実行結果イメージ（email列）:

unique_values	total_rows	cardinality_percent
99,800	100,000	99.8%

99,800種類 / 10万行 = 99.8%（カーディナリティ高い）

4-4. カーディナリティ別の判断基準

📌 インデックス作成の目安

カーディナリティ	目安	判断
10%以上	高	✅ インデックス推奨
1〜10%	中	△ 検索頻度で判断
1%未満	低	❌ 通常は不要

ただし、検索頻度が非常に高い場合や、部分インデックスとして使う場合は、カーディナリティが低くても効果がある場合があります。

4-5. 複合インデックスの列の順序

複合インデックスを作る際は、カーディナリティの高い列を先に配置すると効率的です。

※横にスクロールできます

— ❌ 悪い例：カーディナリティの低い列が先
CREATE INDEX idx_bad ON customers(gender, email);
— genderは2種類しかないので、最初の絞り込み効果が薄い

— ✅ 良い例：カーディナリティの高い列が先
CREATE INDEX idx_good ON customers(email, gender);
— emailで大きく絞り込んでから、genderで絞る

💡 複合インデックスの列順決定ルール

カーディナリティの高い列を先に
=条件の列を範囲条件（>, <）より先に
よく使う列を先に

🔍 5. WHERE句での効果

5-1. 等価条件（=）での使用

最もインデックスの効果が高いのは等価条件（=）での検索です。

※横にスクロールできます

— インデックスを作成
CREATE INDEX idx_customer_id ON orders(customer_id);

— ✅ インデックスが使われる（等価条件）
SELECT * FROM orders WHERE customer_id = 101;

— ✅ INでも使われる
SELECT * FROM orders WHERE customer_id IN (101, 102, 103);

ポイント:

IN (101, 102, 103) は内部的に = 101 OR = 102 OR = 103 と同じ扱いになるため、インデックスが効きます。

5-2. 範囲条件（>, <, BETWEEN）での使用

範囲条件でもインデックスは使われます。

※横にスクロールできます

— インデックスを作成
CREATE INDEX idx_order_date ON orders(order_date);

— ✅ 範囲検索でもインデックスが使われる
SELECT * FROM orders WHERE order_date >= ‘2024-01-01’;

— ✅ BETWEENでも使われる
SELECT * FROM orders WHERE order_date BETWEEN ‘2024-01-01’ AND ‘2024-12-31’;

— ✅ 金額の範囲検索
SELECT * FROM orders WHERE total_amount > 10000;

5-3. LIKE検索での使用

LIKE検索では、前方一致の場合のみインデックスが使われます。

※横にスクロールできます

— インデックスを作成
CREATE INDEX idx_customer_name ON customers(customer_name);

— ✅ 前方一致は使われる
SELECT * FROM customers WHERE customer_name LIKE ‘田中%’;

— ❌ 中間一致は使われない
SELECT * FROM customers WHERE customer_name LIKE ‘%太郎%’;

— ❌ 後方一致は使われない
SELECT * FROM customers WHERE customer_name LIKE ‘%太郎’;

📌 なぜ前方一致だけインデックスが効くのか

インデックスは「あいうえお順」のように整列されています。
「田中〜」で始まる名前は連続して並んでいるので見つけやすいですが、「〜太郎」で終わる名前はバラバラに散らばっているため、全件スキャンが必要になります。

5-4. インデックスが使われないパターン

以下のような場合、インデックスが使われないので注意しましょう。

⚠️ インデックスが使われないパターン

パターン	例	理由
列に関数を使う	WHERE UPPER(name) = ‘TANAKA’	関数の結果にインデックスがない
列を演算する	WHERE age + 1 > 30	演算結果にインデックスがない
型変換する	WHERE CAST(id AS TEXT) = ‘101’	変換後の値にインデックスがない
NOT条件	WHERE customer_id != 101	「以外」はほぼ全件になる
中間・後方一致	WHERE name LIKE ‘%田中%’	先頭が不明だと検索できない

回避策として、以下のように列を加工せずに書き換えます。

※横にスクロールできます

— ❌ インデックスが使われない
SELECT * FROM customers WHERE age + 1 > 30;

— ✅ インデックスが使われる（条件を書き換え）
SELECT * FROM customers WHERE age > 29;

5-5. 複合インデックスでの複数条件

※横にスクロールできます

— 複合インデックスを作成
CREATE INDEX idx_multi ON customers(prefecture, city, age);

— ✅ 全て使われる（左から順番に条件がある）
SELECT * FROM customers 
WHERE prefecture = ‘東京都’ AND city = ‘渋谷区’ AND age = 30;

— ✅ prefectureとcityが使われる
SELECT * FROM customers 
WHERE prefecture = ‘東京都’ AND city = ‘渋谷区’;

— ✅ prefectureだけ使われる
SELECT * FROM customers WHERE prefecture = ‘東京都’;

— ❌ 使われない（左端のprefectureがない）
SELECT * FROM customers WHERE city = ‘渋谷区’ AND age = 30;

🔗 6. JOINでの効果

6-1. 結合キーにインデックスが必要な理由

JOINのパフォーマンスを上げるには、結合キーにインデックスが必須です。

インデックスなしの場合の処理:

ordersテーブル（10万件）とcustomersテーブル（1万件）をJOINする場合:

ordersの1行目 → customersを全件スキャンして該当顧客を探す
ordersの2行目 → customersを全件スキャンして該当顧客を探す
…
合計: 10万 × 1万 = 10億回の比較!

インデックスありの場合の処理:

customer_idにインデックスがあれば:

ordersの1行目 → インデックスで直接該当顧客を取得
ordersの2行目 → インデックスで直接該当顧客を取得
…
合計: 10万 × 約20回 = 200万回の比較（5000倍速い!）

6-2. JOINの最適化

※横にスクロールできます

— インデックスなし（遅い）
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
— ordersの各行について、customersを全件スキャン!

※横にスクロールできます

— インデックスを作成（orders側）
CREATE INDEX idx_orders_customer ON orders(customer_id);

— インデックスあり（速い）
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
— インデックスで直接該当顧客を取得!

ポイント:

customersのcustomer_idはPRIMARY KEYなので、自動的にインデックスがあります。
ordersのcustomer_idは外部キーなので、手動でインデックスを作る必要があります。

6-3. 複数テーブルのJOIN

※横にスクロールできます

— 3テーブルのJOIN
SELECT c.customer_name, o.order_date, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

— 必要なインデックス
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_product ON orders(product_id);
— customersとproductsの主キーは自動的にインデックスがある

6-4. LEFT JOINでの効果

LEFT JOINでも同様にインデックスが効果的です。

※横にスクロールできます

— LEFT JOINでもインデックスは効果的
SELECT c.customer_name, o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

— ordersのcustomer_idにインデックスがあると高速化
CREATE INDEX idx_orders_customer ON orders(customer_id);

📌 JOIN最適化のコツ

結合キー（ON句で使う列）に必ずインデックスを作る
外部キーには自動でインデックスが作られないことが多い
複数列での結合は複合インデックスを検討
JOINの順序も影響する（小さいテーブルを外側に）

📈 7. ORDER BYでの効果

7-1. ソートの高速化

ORDER BYで使う列にインデックスがあると、ソート処理をスキップできます。

インデックスなしの場合:

テーブルから全データを読み込む
メモリ上でソートする（時間がかかる）
ソート結果を返す

インデックスありの場合:

インデックスは既にソート済み
インデックスの順序でデータを読むだけ
ソート処理が不要!

※横にスクロールできます

— インデックスなし（遅い）
SELECT * FROM orders ORDER BY order_date DESC;
— 全データを読み込んでからソート

— インデックスを作成
CREATE INDEX idx_order_date_desc ON orders(order_date DESC);

— インデックスあり（速い）
SELECT * FROM orders ORDER BY order_date DESC;
— インデックスの順序で読むだけ（ソート不要!）

7-2. 昇順・降順の指定

インデックスを作成する際に、昇順（ASC）か降順（DESC）かを指定できます。クエリで使う順序に合わせると最も効果的です。

※横にスクロールできます

— 降順でよく使う場合（最新のデータから表示）
CREATE INDEX idx_order_date_desc ON orders(order_date DESC);
SELECT * FROM orders ORDER BY order_date DESC;  — ✅ 最速

— 昇順でよく使う場合（古いデータから表示）
CREATE INDEX idx_order_date_asc ON orders(order_date ASC);
SELECT * FROM orders ORDER BY order_date ASC;   — ✅ 最速

7-3. WHERE + ORDER BYの組み合わせ

WHERE句とORDER BY句を両方使う場合は、複合インデックスが効果的です。

※横にスクロールできます

— このクエリを高速化したい
SELECT * FROM orders
WHERE customer_id = 101
ORDER BY order_date DESC;

— 複合インデックスを作成
CREATE INDEX idx_customer_date ON orders(customer_id, order_date DESC);

— 効果:
— 1. customer_id = 101 で絞り込み
— 2. その中で order_date DESC の順に並んでいる（ソート不要!）

📌 複合インデックスの列順のルール

WHERE句の列 → ORDER BY句の列の順に配置すると効果的です。

7-4. LIMIT句との組み合わせ（非常に効果的!）

ORDER BY + LIMIT の組み合わせでは、インデックスの効果が劇的に現れます。

※横にスクロールできます

— 最新の10件を取得
SELECT * FROM orders 
ORDER BY order_date DESC 
LIMIT 10;

— インデックスがあれば超高速!
CREATE INDEX idx_order_date_desc ON orders(order_date DESC);
— 最初の10件だけ読めばいいので、一瞬で終わる!

💡 LIMIT使用時の劇的な効果

インデックス + LIMITの組み合わせは、100万件のテーブルでも数ミリ秒で結果を返せます!
なぜなら、インデックスの最初の10件だけを読めばいいからです。

活用シーン:

最新のお知らせ10件を表示（ニュースサイト、SNS）
売上トップ10を表示（ランキング）
最近の注文5件を表示（マイページ）
ページネーション（一覧の1ページ目を表示）

🎨 8. 実践的なインデックス設計

8-1. 設計の流れ

💡 インデックス設計の6ステップ

遅いクエリを特定する
WHERE句の条件を分析する
カーディナリティを確認する
最適なインデックスを設計する
実測して効果を確認する
不要なインデックスを削除する

8-2. 実例：ECサイトのインデックス設計

ECサイトのordersテーブルに対して、よくあるクエリパターンを分析してインデックスを設計してみましょう。

よく実行されるクエリパターン:

顧客別の注文履歴: WHERE customer_id = ?
日付範囲での検索: WHERE order_date BETWEEN ? AND ?
顧客の最新注文: WHERE customer_id = ? ORDER BY order_date DESC LIMIT 10
ステータス別集計: WHERE status = ? GROUP BY customer_id

最適なインデックス設計:

※横にスクロールできます

— インデックス1: 顧客別注文（クエリ1と3をカバー）
CREATE INDEX idx_orders_customer_date 
ON orders(customer_id, order_date DESC);
— customer_idでの検索と、日付ソートの両方に対応

— インデックス2: 日付範囲検索（クエリ2）
CREATE INDEX idx_orders_order_date 
ON orders(order_date);
— 日付だけでの検索用

— インデックス3: ステータス別（クエリ4）
CREATE INDEX idx_orders_status_customer 
ON orders(status, customer_id);
— ステータスで絞り込んでから顧客別に集計

設計のポイント:

3つのインデックスで全てのクエリパターンをカバー
複合インデックスを活用して効率化
検索頻度の高いパターンを優先

📝 練習問題

実践的なインデックス設計を学びましょう!

問題 1基本

単一列インデックスの作成

customersテーブルのemail列に、ユニークインデックスを作成してください。
インデックス名は「idx_customers_email」としてください。

※横にスクロールできます

— ユニークインデックスを作成
CREATE UNIQUE INDEX idx_customers_email ON customers(email);

— 確認
PRAGMA index_list(‘customers’);

— テスト
SELECT * FROM customers WHERE email = ‘test@example.com’;

解説:

UNIQUE INDEX で重複したメールアドレスの登録を防げる
検索も高速化される

問題 2基本

複合インデックスの作成

customersテーブルに、prefectureとcityの複合インデックスを作成してください。

※横にスクロールできます

— 複合インデックスを作成
CREATE INDEX idx_customers_prefecture_city 
ON customers(prefecture, city);

— テスト1: 両方の条件（✅ 使われる）
SELECT * FROM customers 
WHERE prefecture = ‘東京都’ AND city = ‘渋谷区’;

— テスト2: prefectureだけ（✅ 使われる）
SELECT * FROM customers WHERE prefecture = ‘東京都’;

— テスト3: cityだけ（❌ 使われない）
SELECT * FROM customers WHERE city = ‘渋谷区’;

解説:

複合インデックスは左端一致の原則により、左側の列から順番に使われます。

問題 3応用

カーディナリティの確認

customersテーブルのprefecture列のカーディナリティを確認するSQLを書いてください。

※横にスクロールできます

— カーディナリティを確認
SELECT 
    COUNT(DISTINCT prefecture) AS unique_values,
    COUNT(*) AS total_rows,
    ROUND(
        CAST(COUNT(DISTINCT prefecture) AS REAL) / COUNT(*) * 100, 
        3
    ) AS cardinality_percent
FROM customers;

解説:

COUNT(DISTINCT prefecture) → 値の種類の数
COUNT(*) → 総行数
カーディナリティ% = 種類数 / 総行数 × 100

問題 4応用

JOIN用のインデックス

以下のクエリを高速化するために、適切なインデックスを作成してください。
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id;

※横にスクロールできます

— ordersテーブルのcustomer_idにインデックスを作成
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

— customersテーブルのcustomer_idは主キーなので、
— 自動的にインデックスがある

解説:

JOIN句の結合キーにインデックスがあると、劇的に高速化します。外部キー側（orders）にインデックスを作成します。

問題 5応用

ORDER BY用のインデックス

以下のクエリを高速化するインデックスを作成してください。
SELECT * FROM orders ORDER BY order_date DESC LIMIT 100;

※横にスクロールできます

— 降順でインデックスを作成
CREATE INDEX idx_orders_date_desc ON orders(order_date DESC);

解説:

ORDER BY + LIMITの組み合わせでは、インデックスがあると最初のN件だけ読めばいいので非常に高速です。

問題 6チャレンジ

複雑なクエリの最適化

以下のクエリを最適化するために、最適な複合インデックスを設計してください。
SELECT * FROM orders WHERE customer_id = 101 AND status = '完了' ORDER BY order_date DESC;

※横にスクロールできます

— 最適な複合インデックス
CREATE INDEX idx_orders_customer_status_date 
ON orders(customer_id, status, order_date DESC);

このインデックスが最適な理由:

customer_id で大きく絞り込む（=条件）
status でさらに絞り込む（=条件）
order_date でソート

全ての条件とソートを1つのインデックスでカバーできます。

問題 7チャレンジ

インデックスが使われない例

以下のクエリでインデックスが使われない理由を説明し、使われるように書き換えてください。
SELECT * FROM customers WHERE UPPER(customer_name) = 'TANAKA';

理由:

列に関数（UPPER）を適用しているため、インデックスが使われません。関数の結果にはインデックスがないためです。

書き換え例:

※横にスクロールできます

— 方法1: 大文字小文字を区別しない比較（SQLiteの場合）
SELECT * FROM customers 
WHERE customer_name = ‘tanaka’ COLLATE NOCASE;

— 方法2: データを保存する際に正規化しておく
— 登録時に customer_name_upper 列に大文字で保存
SELECT * FROM customers WHERE customer_name_upper = ‘TANAKA’;

問題 8チャレンジ

不要なインデックスの特定

以下のインデックスがあります。不要なものを特定して理由を説明してください。
1. idx_email (email)
2. idx_prefecture (prefecture)
3. idx_location (prefecture, city)
4. idx_city (city)

不要なインデックス: idx_prefecture

理由:

idx_location (prefecture, city) があれば、prefecture単独の検索もカバーできます（左端一致の原則）。同じ役割のインデックスが2つあるのは無駄です。

※横にスクロールできます

— 削除すべきインデックス
DROP INDEX idx_prefecture;

— idx_cityは使用状況による
— WHERE city = ‘渋谷区’ というクエリがあるなら残す
— なければ削除を検討

📝 Step 15 のまとめ

✅ 学んだこと

インデックスの作成・削除・確認方法
単一列インデックスと複合インデックスの使い分け
左端一致の原則（複合インデックスは左から使われる）
カーディナリティを考慮した設計
WHERE、JOIN、ORDER BYでの効果
ORDER BY + LIMITの劇的な効果

📌 インデックス作成の構文

※横にスクロールできます

— 単一列インデックス
CREATE INDEX idx_name ON table_name(column);

— ユニークインデックス
CREATE UNIQUE INDEX idx_name ON table_name(column);

— 複合インデックス
CREATE INDEX idx_name ON table_name(col1, col2, col3);

— 削除
DROP INDEX idx_name;

— 確認
PRAGMA index_list(‘table_name’);

📌 インデックス設計の黄金ルール

実際のクエリパターンを分析する
カーディナリティの高い列を優先
複合インデックスは左から順番に使われる
=条件を範囲条件より先に
必要最小限のインデックスを作る
定期的に使用状況を確認する

❓ よくある質問

Q1: 複合インデックスと単一列インデックスどちらが良い?

一緒に検索する列は複合インデックスが効率的です。例えば、WHERE prefecture = ? AND city = ? というクエリが多いなら、(prefecture, city) の複合インデックスを作りましょう。別々に検索することが多いなら、それぞれに単一列インデックスを作ります。

Q2: カーディナリティが低い列にもインデックスは必要?

基本的には不要です。ただし、検索頻度が非常に高い場合や、部分インデックスとして使う場合は効果がある可能性があります。

Q3: インデックスはいくつまで作れますか?

技術的な制限はありませんが、5〜10個程度が現実的です。インデックスが多すぎると、INSERT/UPDATE/DELETEが遅くなり、ディスク容量も増えます。

Q4: インデックスを作ったのに遅いままです

以下を確認してください:

実行計画でインデックスが使われているか確認
WHERE句で列に関数を使っていないか
複合インデックスの列の順序は適切か
ANALYZEコマンドで統計情報を更新

Q5: 既存のインデックスを確認する方法は?

※横にスクロールできます

— テーブルのインデックス一覧
PRAGMA index_list(‘table_name’);

— インデックスの詳細
PRAGMA index_info(‘index_name’);

— 全インデックスを表示
SELECT * FROM sqlite_master WHERE type = ‘index’;

Q6: ORDER BY用のインデックスは必ず作るべき?

頻繁にソートする列には作るべきです。特にLIMIT句と組み合わせる場合は効果絶大です。

🎓 次のステップでは

Step 16: インデックスの落とし穴では、インデックスが効かないパターンや、よくある間違いについて学びます!

📝

学習メモ

SQL応用・パフォーマンス最適化 - Step 15

📋 過去のメモ一覧 ▼