⚡ Step 14: インデックスの基礎理論

データベースを高速化する魔法の仕組み!

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

インデックスとは何か（本の索引のたとえ）
B-tree（ビーツリー）の仕組み
インデックスの種類（主キー、ユニーク、複合など）
クラスタ化インデックスと非クラスタ化インデックス
いつインデックスを使うべきか

🎯 1. インデックスとは?

1-1. 本の「索引」をイメージしよう

インデックス（索引）は、データベースの検索を高速化するための仕組みです。本の巻末にある「索引」と全く同じ考え方です!

💡 本の例で理解しよう

500ページの本から「データベース」という単語を探したい場合:

方法	やり方	時間
索引なし	1ページ目から順番に全ページを読む	とても遅い（数時間?）
索引あり	巻末の索引で「データベース → 98ページ」と確認	一瞬（数秒）

索引があれば、目的のページに直接ジャンプできます!

1-2. データベースでも同じ

データベースでも、インデックスがあるかないかで検索速度が劇的に変わります。

例：100万件のcustomersテーブルから、customer_id = 12345 を探す

状態	処理	実行時間
インデックスなし	100万行を1行ずつ確認	約3秒
インデックスあり	インデックスで直接ジャンプ	約0.001秒

約3000倍の速度差!

1-3. インデックスがない場合の動き（フルテーブルスキャン）

インデックスがない場合、データベースはフルテーブルスキャン（全件スキャン）を行います。つまり、テーブルの全行を1行ずつ確認します。

フルテーブルスキャンのイメージ:

確認する行	customer_id	判定
1行目	1	❌ 違う
2行目	2	❌ 違う
3行目	3	❌ 違う
…	…	…
12345行目	12345	✅ 見つかった!

目的のデータを見つけるまで、12,345回も確認が必要でした。

1-4. インデックスがある場合の動き

インデックスがあると、目的のデータに直接ジャンプできます。

インデックス検索のイメージ:

インデックスで「customer_id = 12345」を検索
インデックスが「12345行目にある」と教えてくれる
12345行目に直接アクセス

わずか数回のアクセスで完了!

1-5. インデックスの効果まとめ

📌 インデックスで高速化できるもの

検索（WHERE句）：条件に合うデータを素早く見つける
並び替え（ORDER BY）：ソート済みなので再計算不要
結合（JOIN）：結合キーの検索が高速化
重複チェック（UNIQUE制約）：既存データの確認が高速

🚀 2. なぜインデックスは速いのか

2-1. 二分探索という考え方

インデックスが速い理由を理解するために、まず二分探索という考え方を紹介します。

💡 数当てゲームで理解しよう

1〜100の数字から、相手が考えた数字を当てるゲームを考えます。

方法1：順番に聞く（フルスキャン）

「1ですか?」→ No
「2ですか?」→ No
「3ですか?」→ No
… 最悪100回必要

方法2：半分に絞る（二分探索）

「50より大きい?」→ Yes → 51〜100に絞られる
「75より大きい?」→ No → 51〜75に絞られる
「63より大きい?」→ Yes → 64〜75に絞られる
… 最大7回で必ず見つかる!

毎回半分に絞ることで、100個のデータでも7回で見つかります。これがインデックスの基本的な考え方です。

2-2. データ量と検索回数の関係

フルスキャン vs インデックス検索:

データ件数	フルスキャン（平均）	インデックス検索
100件	50回	7回
1,000件	500回	10回
10,000件	5,000回	14回
100,000件	50,000回	17回
1,000,000件	500,000回	20回

100万件でもわずか20回の比較で見つかります!

💡 計算量の違い（専門用語）

フルスキャン：O(N) → データ量に比例して時間がかかる
インデックス検索：O(log N) → データ量が増えてもほとんど変わらない

🌳 3. B-tree（ビーツリー）インデックスの仕組み

3-1. B-treeとは

ほとんどのデータベース（SQLite、MySQL、PostgreSQLなど）は、B-tree（ビーツリー）という構造でインデックスを管理しています。

💡 B-treeとは?

Balanced Tree（バランス木）の略です。データを「木の構造」で整理して、どのデータにも同じ回数でたどり着けるようにしています。

3-2. B-treeの構造

B-treeは、以下のような階層構造になっています。

B-treeのイメージ:

                    [50]              ← ルート（根）
                   /    \
                [25]    [75]          ← 中間ノード
               /   \    /   \
            [10] [30] [60] [90]       ← リーフ（葉）
             |    |    |    |
           データ データ データ データ

各部分の名前:

ルート：木の一番上（最初に確認する場所）
中間ノード：ルートとリーフの間
リーフ：木の一番下（実際のデータへのポインタがある）

3-3. B-treeで検索する流れ

例えば、値 42 を探す場合の流れを見てみましょう。

検索の流れ（値 42 を探す）:

ステップ	確認するノード	判断	次の行動
1	ルート [50]	42 < 50	左の枝へ進む
2	中間ノード [25]	42 > 25	右の枝へ進む
3	リーフ [30]	42 > 30	この範囲にある! → データ取得

わずか3回の比較で見つかりました!

3-4. なぜB-treeが使われるのか

B-treeの優れた点:

バランスが保たれる：どのデータも同じ回数でアクセスできる
範囲検索に強い：「10〜50の値」のような検索も高速
ソート済み：ORDER BYの処理が不要になることも
ディスクアクセスに最適化：1回の読み込みで多くのデータを確認

📚 4. インデックスの種類

4-1. 主キーインデックス（Primary Key Index）

PRIMARY KEY を設定すると、自動的にインデックスが作成されます。手動で作る必要はありません。

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

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,  — 自動的にインデックスが作成される
    customer_name TEXT,
    email TEXT
);

主キーインデックスの特徴:

自動作成：PRIMARY KEYを設定するだけでOK
ユニーク：重複した値は入らない
NULL不可：必ず値が必要
最も高速：クラスタ化インデックスになる（後述）

4-2. ユニークインデックス（Unique Index）

重複を許さないインデックスです。メールアドレスなど、一意である必要がある列に使います。

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

— メールアドレスにユニークインデックスを作成
CREATE UNIQUE INDEX idx_email ON customers(email);

クエリの解説:

CREATE UNIQUE INDEX → ユニークインデックスを作成
idx_email → インデックスの名前（自由に決められる）
ON customers(email) → customersテーブルのemail列に作成

ユニークインデックスを作成すると、重複したメールアドレスは登録できなくなります。

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

— 1回目：OK
INSERT INTO customers (customer_name, email) 
VALUES (‘山田太郎’, ‘yamada@example.com’);

— 2回目：エラー! 同じメールアドレスは登録できない
INSERT INTO customers (customer_name, email) 
VALUES (‘田中次郎’, ‘yamada@example.com’);
— エラー: UNIQUE constraint failed: customers.email

4-3. 通常インデックス（Non-Unique Index）

重複を許すインデックスです。検索を速くしたい列に使います。

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

— 都道府県にインデックスを作成
CREATE INDEX idx_prefecture ON customers(prefecture);

クエリの解説:

CREATE INDEX → 通常のインデックスを作成（UNIQUE なし）
同じ都道府県の顧客は何人いてもOK

4-4. 複合インデックス（Composite Index）

複数の列を組み合わせたインデックスです。複数の条件で検索することが多い場合に有効です。

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

— 都道府県と市区町村の組み合わせでインデックスを作成
CREATE INDEX idx_location ON customers(prefecture, city);

📌 複合インデックスの重要なルール

複合インデックスは左側の列から順番に使われます。これを「左端一致の原則」と呼びます。

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

4-5. 部分インデックス（Partial Index）

条件に合うデータだけをインデックス化します。特定の条件でよく検索する場合に有効です。

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

— アクティブな顧客だけをインデックス化
CREATE INDEX idx_active_customers 
ON customers(customer_id) 
WHERE is_active = 1;

クエリの解説:

WHERE is_active = 1 → この条件に合うデータだけインデックス化
非アクティブな顧客はインデックスに含まれない

部分インデックスのメリット:

容量削減：必要な部分だけなのでインデックスが小さい
更新が速い：更新対象のデータが少ない
メンテナンスが楽：再構築が速く終わる

4-6. インデックスの種類まとめ

インデックスの種類と使い分け:

種類	特徴	使う場面
主キー	自動作成、ユニーク、NULL不可	テーブルのID列
ユニーク	重複不可	メールアドレス、電話番号
通常	重複OK	検索をよく行う列
複合	複数列の組み合わせ	複数条件での検索
部分	条件に合うデータだけ	特定条件での検索が多い

🔍 5. クラスタ化インデックスと非クラスタ化インデックス

5-1. 2つのタイプがある

インデックスには、クラスタ化と非クラスタ化の2種類があります。この違いを理解すると、なぜ主キーが速いのかがわかります。

5-2. クラスタ化インデックス（Clustered Index）

💡 イメージ：辞書

辞書は「あいうえお順」に単語と説明がセットで並んでいます。索引を引く必要がなく、その場で内容を読めます。

クラスタ化インデックスでは、データ自体がインデックスの順序で物理的に並んでいます。

クラスタ化インデックスのイメージ:

[インデックス]        [データ]（同じ場所にある）
    1 ───────────→ 田中太郎, tanaka@..., 東京都
    2 ───────────→ 佐藤花子, satou@..., 大阪府
    3 ───────────→ 鈴木一郎, suzuki@..., 北海道

インデックスとデータが一体化しているため、一度のアクセスでデータを取得できます。

クラスタ化インデックスの特徴:

テーブルに1つだけ：データの物理的な並び順は1種類しかない
通常は主キー：PRIMARY KEY がクラスタ化インデックスになる
高速：データに直接アクセスできる
範囲検索に強い：連続したデータがまとまっている

5-3. 非クラスタ化インデックス（Non-Clustered Index）

💡 イメージ：本の巻末索引

巻末の索引には「データベース → 98ページ」のようにページ番号だけが書いてあります。実際の内容を読むには、そのページに移動する必要があります。

非クラスタ化インデックスには、データの場所（ポインタ）が記録されています。実際のデータは別の場所にあります。

非クラスタ化インデックスのイメージ:

[インデックス]        [ポインタ]    [実際のデータ]
tanaka@example.com ──→ 行1 ────→ 田中太郎, tanaka@..., 東京都
satou@example.com  ──→ 行2 ────→ 佐藤花子, satou@..., 大阪府
suzuki@example.com ──→ 行3 ────→ 鈴木一郎, suzuki@..., 北海道

インデックス → ポインタ → データの2段階でアクセスします。

非クラスタ化インデックスの特徴:

複数作成可能：いくつでも作れる
2段階アクセス：インデックス → データ
追加容量が必要：インデックスの分だけディスクを使う
CREATE INDEXで作成：手動で作る必要がある

5-4. 両者の比較

クラスタ化 vs 非クラスタ化:

項目	クラスタ化	非クラスタ化
作成数	1つだけ	複数OK
データの場所	インデックスと一体	別の場所（ポインタで参照）
検索速度	最速（1段階）	速い（2段階）
範囲検索	非常に得意	まあまあ
通常の使い方	主キー（自動）	その他の検索列

⏰ 6. いつインデックスを使うべきか

6-1. インデックスを作るべき列

✅ インデックスを作ると効果的な場合:

WHERE句でよく使う列：検索条件になる列
JOINで結合キーになる列：外部キーなど
ORDER BYでソートする列：並び替えの対象
GROUP BYでグループ化する列：集計の基準
外部キーの列：関連テーブルとの結合
カーディナリティが高い列：値の種類が多い列

6-2. 具体的な例

例1: WHERE句でよく使う列

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

— このクエリを頻繁に実行する場合
SELECT * FROM customers WHERE email = ‘tanaka@example.com’;

— emailにインデックスを作成すると高速化
CREATE INDEX idx_email ON customers(email);

例2: JOINで使う列

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

— この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);

ポイント:

JOINでは、結合される側（多い側）にインデックスがあると効果的です。customers（1側）の主キーは自動でインデックスがあるので、orders（多側）に作成します。

例3: ORDER BYで使う列

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

— このクエリを頻繁に実行する場合
SELECT * FROM orders ORDER BY order_date DESC LIMIT 100;

— order_dateにインデックスを作成すると、ソートが不要になる
CREATE INDEX idx_order_date ON orders(order_date DESC);

6-3. インデックスを作らない方が良い場合

❌ インデックスを作らない方が良い場合:

小さいテーブル：数百行以下ならフルスキャンでも十分速い
頻繁に更新される列：INSERT/UPDATE/DELETEのたびにインデックス更新が必要
カーディナリティが低い列：性別（男/女）など値の種類が少ない
全件取得が多い列：全データを取得するクエリには効果がない

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

カーディナリティとは、「列に含まれる値の種類の数」のことです。インデックスの効果に大きく影響します。

カーディナリティの例:

列	値の例	カーディナリティ	インデックス効果
customer_id	1, 2, 3, … (全員違う)	非常に高い	◎ 効果大
email	全員違うメールアドレス	非常に高い	◎ 効果大
prefecture	47都道府県	中程度	○ 効果あり
gender	男, 女 (2種類)	低い	△ 効果薄い
is_active	0, 1 (2種類)	低い	△ 効果薄い

💡 カーディナリティの目安

一般的に、全体の10%未満の行を取得する場合に、インデックスが効果的です。例えば、1万件のテーブルで1000件以上ヒットするような列には、インデックスの効果が薄くなります。

6-5. インデックスのデメリット

インデックスは検索を高速化しますが、デメリットもあります。

⚠️ インデックスの代償:

デメリット	詳細
ディスク容量が増える	インデックスの分だけストレージを使用する
INSERT/UPDATE/DELETEが遅くなる	データ更新時にインデックスも更新が必要
メンテナンスが必要	長期間使用すると断片化が進み、再構築が必要

6-6. バランスが大切

💡 インデックス設計のポイント

必要最小限のインデックスを作る（作りすぎない）
検索速度と更新速度のバランスを考える
実際のクエリパターンを分析してから作る
実測してから最適化する（推測より計測）

🛠️ 7. インデックスの作成と確認

7-1. インデックスの作成

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

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

— 通常のインデックス
CREATE INDEX インデックス名 ON テーブル名(列名);

— ユニークインデックス
CREATE UNIQUE INDEX インデックス名 ON テーブル名(列名);

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

— 部分インデックス
CREATE INDEX インデックス名 ON テーブル名(列名) WHERE 条件;

7-2. インデックスの確認

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

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

— インデックスの詳細を表示
PRAGMA index_info(‘idx_email’);

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

7-3. インデックスの削除

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

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

— インデックスが存在する場合のみ削除
DROP INDEX IF EXISTS idx_email;

7-4. インデックスの再構築

長期間使用していると、インデックスが断片化して効率が悪くなることがあります。定期的に再構築すると良いでしょう。

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

— インデックスを再構築（SQLite）
REINDEX customers;

— 全インデックスを再構築
REINDEX;

— データベース全体を最適化
VACUUM;

📝 練習問題

インデックスの理解を深めましょう!

問題 1基本

インデックスの効果

100万件のデータから1件を検索する場合、インデックスがある場合とない場合で、どのくらいの差がありますか?

解答:

インデックスなし：平均50万回の比較（全件の半分）
インデックスあり：約20回の比較（B-tree）
速度差：約25,000倍!

解説:

B-treeインデックスは対数時間 O(log N) で検索できるため、データ量が増えても検索時間はほとんど増えません。

問題 2基本

主キーインデックス

PRIMARY KEYを設定すると、自動的に何が作成されますか?

解答：インデックスが自動的に作成されます

解説:

PRIMARY KEYを設定すると、その列に対してクラスタ化インデックスが自動的に作成されます。手動でインデックスを作る必要はありません。

問題 3基本

インデックスの作成

customersテーブルのemail列にユニークインデックスを作成するSQLを書いてください。

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

CREATE UNIQUE INDEX idx_email ON customers(email);

解説:

CREATE UNIQUE INDEX → ユニーク（重複不可）インデックスを作成
idx_email → インデックスの名前
ON customers(email) → customersテーブルのemail列に作成

問題 4応用

インデックスの判断

以下の列に、インデックスを作るべきか判断してください:
1. email（全員違う値）
2. gender（男/女の2種類）
3. customer_id（PRIMARY KEY）
4. prefecture（47種類）

解答:

1. email：○ 作るべき（カーディナリティ高い、ユニーク制約にも使える）
2. gender：× 作らない（カーディナリティ低い、効果が薄い）
3. customer_id：○ 自動作成される（PRIMARY KEYなので不要）
4. prefecture：△ 用途次第（検索頻度が高ければ作る）

解説:

カーディナリティが高く、WHERE句でよく使う列にインデックスを作成します。

問題 5応用

クラスタ化インデックス

クラスタ化インデックスは、1つのテーブルにいくつ作成できますか? また、その理由は?

解答：1つだけ

理由:

クラスタ化インデックスは、データの物理的な並び順を決めます。データを2つの順序で同時に並べることはできないため、1つのテーブルに1つしか作成できません。

問題 6応用

複合インデックス

(prefecture, city) の複合インデックスがある場合、以下のクエリでインデックスが使われるのはどれですか?
A) WHERE prefecture = ‘東京都’
B) WHERE city = ‘渋谷区’
C) WHERE prefecture = ‘東京都’ AND city = ‘渋谷区’

解答：AとCで使われる（Bは使われない）

解説:

複合インデックスは左側の列から順番に使われます（左端一致の原則）。prefectureだけ、またはprefecture + cityの組み合わせでは使われますが、cityだけでは使われません。

問題 7チャレンジ

インデックスのデメリット

インデックスを作りすぎると、どのような問題が起きますか? 3つ答えてください。

解答:

ディスク容量が増える：インデックスの分だけ容量が必要
INSERT/UPDATE/DELETEが遅くなる：インデックスの更新が必要
メンテナンスコストが増える：定期的な再構築や最適化が必要

解説:

インデックスは検索を速くしますが、データ更新のコストが増えます。必要最小限のインデックスを作ることが重要です。

問題 8チャレンジ

最適なインデックス設計

以下のクエリをよく実行します。最適なインデックスを提案してください:
SELECT * FROM orders WHERE customer_id = 101 AND order_date >= '2024-01-01' ORDER BY order_date DESC;

推奨インデックス:

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

CREATE INDEX idx_customer_date ON orders(customer_id, order_date DESC);

理由:

customer_id：WHERE句で = を使って絞り込み
order_date DESC：範囲検索とソートの両方に対応

この順序で複合インデックスを作れば、1つのインデックスで全ての条件をカバーできます。

📝 Step 14 のまとめ

✅ 学んだこと

インデックス：本の索引のような仕組みで検索を高速化
B-tree：バランス木構造で効率的に検索（O(log N)）
クラスタ化インデックス：データが順序通りに並ぶ（1つだけ）
非クラスタ化インデックス：ポインタでデータを参照（複数OK）
カーディナリティ：値の種類が多いほどインデックス効果大
複合インデックス：左端一致の原則が重要

📌 インデックス作成の判断基準

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

✅ インデックスを作る
– WHERE句でよく使う列
– JOINキーになる列
– ORDER BYで使う列
– カーディナリティが高い列
– 外部キーの列

❌ インデックスを作らない
– 小さいテーブル（数百行以下）
– 頻繁に更新される列
– カーディナリティが低い列（性別など）
– 全件取得が多い列

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

必要最小限のインデックスを作る
実測してから最適化する（推測より計測）
更新頻度を考慮する
複合インデックスを活用する（左端一致に注意）

❓ よくある質問

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

技術的には制限はありませんが、実務では5〜10個程度が目安です。インデックスが多すぎると、更新処理が遅くなります。

Q2: 小さいテーブルにインデックスは必要ですか?

数百行程度なら不要です。全件スキャンでも十分速いため、インデックスのメンテナンスコストの方が大きくなります。

Q3: インデックスがあるのに遅い場合は?

以下の可能性があります:

インデックスが使われていない：EXPLAIN QUERYで確認
複合インデックスの順序が悪い：左端一致を確認
関数やCASTで列を加工：インデックスが効かなくなる
インデックスの断片化：REINDEXで再構築

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

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

— SQLiteの場合
PRAGMA index_list(‘テーブル名’);
SELECT * FROM sqlite_master WHERE type = ‘index’;

Q5: インデックスは定期的にメンテナンスが必要ですか?

はい。長期間使用すると断片化が進みます。定期的にREINDEXやVACUUMを実行することをおすすめします。

Q6: 複合インデックスの列の順序はどう決めますか?

以下の優先順位で決めます:

WHERE句で = を使う列を先に
範囲検索（>, <）の列を次に
ORDER BYの列を最後に

また、カーディナリティの高い列を先にすると効率的です。

🎓 次のステップでは

Step 15: インデックス設計の実践では、実際にインデックスを設計・作成し、EXPLAIN QUERYで効果を確認する実践的なスキルを学びます!

📝

学習メモ

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

📋 過去のメモ一覧 ▼