🔍 Step 17: 実行計画の読み方

クエリがどう実行されるか見える化しよう!

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

実行計画とは何か
EXPLAIN QUERY PLANの使い方
SCAN vs SEARCHの違い
インデックスが使われているか確認する方法
ボトルネックの特定方法

🎯 1. 実行計画とは?

1-1. クエリの「設計図」を見る

実行計画（Query Plan）は、データベースが「どうやってデータを取得するか」を示す計画書です。

SQLを書いたとき、データベースは裏側で「どのインデックスを使うか」「どの順番でテーブルを読むか」を自動的に決めています。この決定プロセスを「見える化」したものが実行計画です。

💡 料理で例えると…

SQL文 = 「カレーを作って」という注文
実行計画 = 「①野菜を切る→②肉を炒める→③煮込む」というレシピ

実行計画を見れば、どの手順で調理されるかが分かります。非効率な手順（ボトルネック）も発見できます!

1-2. 実行計画で分かること

実行計画から読み取れる情報:

インデックスが使われているか（USING INDEXの有無）
どのテーブルから読んでいるか（処理の順序）
全件スキャンか、ピンポイント検索か（SCAN vs SEARCH）
ソート処理が発生しているか（USE TEMP B-TREEの有無）
どの処理が遅いか（ボトルネックの特定）

1-3. EXPLAIN QUERY PLANの基本

SQLiteでは、EXPLAIN QUERY PLANコマンドで実行計画を確認できます。

📌 基本構文

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

EXPLAIN QUERY PLAN
SELECT文;

確認したいSELECT文の前に EXPLAIN QUERY PLAN を付けるだけです。

実際に試してみましょう。

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

— 実行計画を確認
EXPLAIN QUERY PLAN
SELECT * FROM customers WHERE customer_id = 101;

実行結果イメージ:

QUERY PLAN
`--SEARCH customers USING INTEGER PRIMARY KEY (rowid=?)

この出力は「customersテーブルを主キーで検索している」という意味です!

📌 重要なポイント

EXPLAIN QUERY PLAN は実際にデータを取得しません（計画だけ見る）
大量データのクエリでも安全に実行できる
何度実行してもデータは変わらない

📊 2. 実行計画の基本パターン

2-1. パターン1: SEARCH（インデックス使用 = 高速）

SEARCHは、インデックスを使ってピンポイントで検索していることを示します。これは良いサインです!

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

— 主キーで検索（自動的にインデックスが使われる）
EXPLAIN QUERY PLAN
SELECT * FROM customers WHERE customer_id = 101;

実行結果イメージ:

QUERY PLAN
`--SEARCH customers USING INTEGER PRIMARY KEY (rowid=?)

✅ SEARCHの特徴

インデックスを使って直接ジャンプできる
100万件のテーブルでも一瞬で見つかる
高速な検索パターン

2-2. パターン2: SCAN（全件スキャン = 遅い）

SCANは、テーブルの全行を1つずつチェックしていることを示します。これは遅いサインです!

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

— インデックスがない列で検索
EXPLAIN QUERY PLAN
SELECT * FROM customers WHERE prefecture = ‘東京都’;

実行結果イメージ:

QUERY PLAN
`--SCAN customers

⚠️ SCANの特徴

全行を1つずつチェックする
100万件のテーブルなら100万回のチェックが必要
遅い検索パターン → インデックスを検討!

2-3. SCAN と SEARCH の違いを図解

SCAN（全件スキャン）のイメージ:

テーブル: [行1] → チェック
          [行2] → チェック
          [行3] → チェック
          [行4] → チェック
          ...
          [行100万] → チェック
          
→ 全行をチェックするので時間がかかる!

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

インデックス: 101 → 行5の位置

テーブル: [行1]
          [行2]
          [行3]
          [行4]
          [行5] ← 直接ジャンプ!
          ...
          
→ インデックスで位置が分かるので一瞬!

2-4. パターン3: USING INDEX（インデックス名が表示される）

USING INDEX インデックス名 と表示されたら、そのインデックスが使われています!

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

— まずインデックスを作成
CREATE INDEX idx_email ON customers(email);

— 実行計画を確認
EXPLAIN QUERY PLAN
SELECT * FROM customers WHERE email = ‘tanaka@example.com’;

実行結果イメージ:

QUERY PLAN
`--SEARCH customers USING INDEX idx_email (email=?)

USING INDEX idx_email → idx_emailインデックスが使われている!

2-5. パターン4: COVERING INDEX（超高速!）

COVERING INDEX（カバリングインデックス）は、インデックスだけで必要なデータが全て取得できる状態です。テーブル本体を読まないので超高速です!

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

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

— SELECTで取得する列がインデックスに含まれている
EXPLAIN QUERY PLAN
SELECT prefecture, city FROM customers 
WHERE prefecture = ‘東京都’;

実行結果イメージ:

QUERY PLAN
`--SEARCH customers USING COVERING INDEX idx_location (prefecture=?)

COVERING INDEX → テーブル本体を読まずにインデックスだけで完結!

💡 なぜCOVERING INDEXは速いのか

通常の検索:

インデックスで行の位置を探す
テーブル本体からデータを取得（追加のディスクアクセス）

COVERING INDEX:

インデックスで行の位置を探す
インデックス自体に必要なデータがある（テーブル不要!）

2-6. 実行計画パターンのまとめ

📌 実行計画のパターン一覧

パターン	意味	速度
SCAN	全件スキャン（インデックスなし）	⚠️ 遅い
SEARCH	インデックスを使った検索	✅ 速い
USING INDEX xxx	インデックスxxxを使用	✅ 速い
COVERING INDEX	インデックスだけで完結	🚀 超速い
USE TEMP B-TREE	一時的なソート処理	⚠️ 遅い

🔗 3. JOINの実行計画

3-1. JOINの実行計画を読む

JOINを使ったクエリでは、実行計画が階層構造で表示されます。上から下に実行されます。

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

— 2つのテーブルをJOIN
EXPLAIN QUERY PLAN
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

実行結果イメージ:

QUERY PLAN
|--SCAN orders AS o
`--SEARCH customers AS c USING INTEGER PRIMARY KEY (rowid=?)

3-2. 実行順序の読み方

この実行計画を詳しく解読してみましょう。

📌 実行順序の解説

順番	記号	内容	説明
1	\|–	SCAN orders AS o	ordersテーブルを全件読む（外側のループ）
2	`–	SEARCH customers AS c	各orderに対してcustomersを主キーで検索（内側のループ）

💡 JOINの処理イメージ

ordersテーブル（SCAN = 全件読む）
  ↓
  [order_id=1, customer_id=101] → customersで customer_id=101 を SEARCH
  [order_id=2, customer_id=102] → customersで customer_id=102 を SEARCH
  [order_id=3, customer_id=101] → customersで customer_id=101 を SEARCH
  ...

ordersの各行に対して、customersを検索しています。customersは主キーで検索されるので高速です。

3-3. JOINのボトルネックを発見する

上記の実行計画では、ordersがSCAN（全件スキャン）になっています。これが遅い原因かもしれません。

⚠️ 問題点

SCAN orders → ordersテーブルを全件読んでいる
ordersが100万件あれば、100万回のループが発生!

3-4. JOINの最適化

ordersのcustomer_idにインデックスを追加して、実行計画がどう変わるか見てみましょう。

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

— ordersのcustomer_idにインデックスを追加
CREATE INDEX idx_orders_customer ON orders(customer_id);

— 再度実行計画を確認
EXPLAIN QUERY PLAN
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

実行結果イメージ:

QUERY PLAN
|--SCAN orders AS o USING INDEX idx_orders_customer
`--SEARCH customers AS c USING INTEGER PRIMARY KEY (rowid=?)

USING INDEX idx_orders_customer が追加されました!

✅ 改善ポイント

ordersもインデックスを使うようになりました。JOINの両側にインデックスがあると高速です!

3-5. 3テーブルのJOIN

3つ以上のテーブルをJOINした場合も、同様に階層構造で表示されます。

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

EXPLAIN QUERY PLAN
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

実行結果イメージ:

QUERY PLAN
|--SCAN orders AS o
|--SEARCH customers AS c USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH products AS p USING INTEGER PRIMARY KEY (rowid=?)

📌 読み方

SCAN orders：ordersテーブルを全件読む（外側のループ）
SEARCH customers：各orderに対してcustomersを主キーで検索
SEARCH products：各orderに対してproductsを主キーで検索

📈 4. サブクエリの実行計画

4-1. IN句のサブクエリ

WHERE句でサブクエリを使った場合の実行計画を見てみましょう。

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

— 1万円以上の注文をした顧客を検索
EXPLAIN QUERY PLAN
SELECT * FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders WHERE amount > 10000
);

実行結果イメージ:

QUERY PLAN
|--LIST SUBQUERY 1
|  `--SCAN orders
|--BLOOM FILTER ON c (customer_id=?)
`--SEARCH customers AS c USING INTEGER PRIMARY KEY (rowid=?)

📌 サブクエリの実行計画の要素

要素	意味
LIST SUBQUERY 1	サブクエリを実行（結果をリストとして保持）
BLOOM FILTER	効率化のためのフィルター（高速な存在チェック）
SEARCH customers	サブクエリの結果を元にメインクエリを実行

4-2. 相関サブクエリ（CORRELATED SUBQUERY）

相関サブクエリは、外側のクエリの値を参照するサブクエリです。実行計画では特別な表示になります。

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

— 注文がある顧客だけを検索（EXISTSを使用）
EXPLAIN QUERY PLAN
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id
);

実行結果イメージ:

QUERY PLAN
|--SCAN customers AS c
`--CORRELATED SCALAR SUBQUERY 1
   `--SEARCH orders AS o USING INDEX idx_orders_customer (customer_id=?)

⚠️ CORRELATED SUBQUERY に注意

相関サブクエリは、外側のループの各行に対してサブクエリが実行されます。

customersが1万件 → サブクエリが1万回実行される
行数が多いと非常に遅くなる可能性がある
可能であればJOINに書き換えを検討

4-3. 相関サブクエリをJOINに書き換える

上記のEXISTS句は、JOINに書き換えることができます。

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

— JOINに書き換え
EXPLAIN QUERY PLAN
SELECT DISTINCT c.*
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

実行結果イメージ:

QUERY PLAN
|--SCAN customers AS c
`--SEARCH orders AS o USING INDEX idx_orders_customer (customer_id=?)

CORRELATED SUBQUERY が消えました!

🎭 5. 集計とソートの実行計画

5-1. GROUP BYの実行計画

GROUP BY句を使った集計クエリの実行計画を見てみましょう。

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

— 顧客別の注文回数を集計
EXPLAIN QUERY PLAN
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id;

実行結果イメージ:

QUERY PLAN
`--SCAN orders USING INDEX idx_orders_customer

✅ インデックスでGROUP BY

GROUP BY句の列（customer_id）にインデックスがあると、データが既に並んでいるため、ソート処理をスキップできます!

5-2. ORDER BYの実行計画（インデックスなし）

ORDER BY句を使ったソートの実行計画を見てみましょう。まずはインデックスがない場合です。

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

— 日付でソート（インデックスなし）
EXPLAIN QUERY PLAN
SELECT * FROM orders ORDER BY order_date DESC;

実行結果イメージ:

QUERY PLAN
|--SCAN orders
`--USE TEMP B-TREE FOR ORDER BY

⚠️ USE TEMP B-TREE FOR ORDER BY

これは一時的なB-treeを作ってソートしていることを示します。

メモリ/ディスクに一時的なデータ構造を作成
データ量が多いと非常に遅くなる
インデックスを追加すれば解消できる

5-3. ORDER BYの実行計画（インデックスあり）

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

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

— order_dateにインデックスを追加（降順）
CREATE INDEX idx_order_date ON orders(order_date DESC);

— 再度実行計画を確認
EXPLAIN QUERY PLAN
SELECT * FROM orders ORDER BY order_date DESC;

実行結果イメージ:

QUERY PLAN
`--SCAN orders USING INDEX idx_order_date

USE TEMP B-TREE が消えました!

✅ 改善効果

インデックスは既にソート済みなので、その順序で読むだけで完了します。ソート処理が不要になり、超高速です!

5-4. WHERE + ORDER BY の組み合わせ

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

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

— WHERE + ORDER BY
EXPLAIN QUERY PLAN
SELECT * FROM orders 
WHERE customer_id = 101
ORDER BY order_date DESC;

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

QUERY PLAN
|--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
`--USE TEMP B-TREE FOR ORDER BY

WHEREはインデックスを使っているが、ソート処理が発生している…

複合インデックスを作成して解消しましょう。

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

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

— 再度確認
EXPLAIN QUERY PLAN
SELECT * FROM orders 
WHERE customer_id = 101
ORDER BY order_date DESC;

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

QUERY PLAN
`--SEARCH orders USING INDEX idx_customer_date (customer_id=?)

USE TEMP B-TREE が消えました! WHEREとORDER BYの両方がインデックスでカバーされています。

🔍 6. ボトルネックの特定方法

6-1. 実行計画でチェックすべきポイント

実行計画を見るときは、以下のポイントをチェックしましょう。

📌 チェックリスト

確認ポイント	良いパターン	悪いパターン
検索方法	✅ SEARCH	⚠️ SCAN
インデックス使用	✅ USING INDEX	⚠️ 表示なし
ソート処理	✅ 表示なし	⚠️ USE TEMP B-TREE
サブクエリ	✅ LIST SUBQUERY	⚠️ CORRELATED SUBQUERY

6-2. 問題例1：全件スキャン

まず、問題のあるクエリを確認してみましょう。

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

— 問題のあるクエリ
EXPLAIN QUERY PLAN
SELECT * FROM customers WHERE email = ‘test@example.com’;

実行結果イメージ:

QUERY PLAN
`--SCAN customers

⚠️ 問題: 全件スキャン

SCAN customers = インデックスが使われていない!
10万件のcustomersテーブルなら、10万行を全てチェックしています。

6-3. 問題例1の解決策

email列にインデックスを追加して、再度実行計画を確認します。

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

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

— 再度実行計画を確認
EXPLAIN QUERY PLAN
SELECT * FROM customers WHERE email = ‘test@example.com’;

改善後の実行計画:

QUERY PLAN
`--SEARCH customers USING INDEX idx_email (email=?)

✅ 改善!

SCAN → SEARCH USING INDEX に変わりました。これで高速になります!

6-4. 問題例2：不要なソート処理

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

— 問題のあるクエリ
EXPLAIN QUERY PLAN
SELECT * FROM orders 
WHERE customer_id = 101
ORDER BY order_date DESC;

実行結果イメージ:

QUERY PLAN
|--SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
`--USE TEMP B-TREE FOR ORDER BY

⚠️ 問題: ソート処理が発生

WHERE句はインデックスを使っているが、ORDER BYでUSE TEMP B-TREEが発生しています。

6-5. 問題例2の解決策

WHEREとORDER BYの両方をカバーする複合インデックスを作成します。

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

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

— 再度実行計画を確認
EXPLAIN QUERY PLAN
SELECT * FROM orders 
WHERE customer_id = 101
ORDER BY order_date DESC;

改善後の実行計画:

QUERY PLAN
`--SEARCH orders USING INDEX idx_customer_date (customer_id=?)

✅ 改善!

USE TEMP B-TREE が消えました。ソート処理が不要になり、超高速です!

6-6. 改善前後の比較まとめ

📌 改善パターンのまとめ

問題	症状	解決策
全件スキャン	SCAN テーブル名	WHERE句の列にインデックスを追加
ソート処理	USE TEMP B-TREE FOR ORDER BY	ORDER BY列にインデックスを追加
WHERE+ORDER BY	SEARCHとUSE TEMP B-TREEが両方出る	複合インデックスを作成
相関サブクエリ	CORRELATED SUBQUERY	可能ならJOINに書き換え

🌍 7. データベースごとの違い

7-1. SQLiteの実行計画

このコースで使っているSQLiteの実行計画は、シンプルで読みやすいのが特徴です。

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

— SQLiteの実行計画
EXPLAIN QUERY PLAN
SELECT * FROM customers WHERE customer_id = 101;

SQLiteの実行結果:

QUERY PLAN
`--SEARCH customers USING INTEGER PRIMARY KEY (rowid=?)

SQLiteの特徴

シンプルで読みやすい
SCAN / SEARCH がメインの表示
詳細な統計情報は少ない
小〜中規模のデータに適している

7-2. MySQLの実行計画

MySQLではEXPLAINコマンドで実行計画を確認します（QUERY PLANは不要）。

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

— MySQLの実行計画（参考）
EXPLAIN SELECT * FROM customers WHERE customer_id = 101;

MySQLの実行結果イメージ:

id	select_type	table	type	key	rows	Extra
1	SIMPLE	customers	const	PRIMARY	1	NULL

📌 MySQLの重要な列

列名	説明	良い値
type	アクセス方法	const > ref > range > ALL
key	使用されるインデックス	インデックス名が表示される
rows	処理する推定行数	小さいほど良い
Extra	追加情報	Using index（カバリング）

📌 MySQLのtypeの意味（速い順）

const：主キーで1行取得（最速）
eq_ref：JOINで主キー使用
ref：インデックス使用
range：範囲検索でインデックス使用
index：インデックス全スキャン
ALL：全件スキャン（最遅）⚠️

7-3. PostgreSQLの実行計画

PostgreSQLでは、より詳細なコスト情報が表示されます。

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

— PostgreSQLの実行計画（参考）
EXPLAIN SELECT * FROM customers WHERE customer_id = 101;

PostgreSQLの実行結果イメージ:

QUERY PLAN
----------------------------------------------------------------
Index Scan using customers_pkey on customers  
  (cost=0.29..8.30 rows=1 width=100)
  Index Cond: (customer_id = 101)

📌 PostgreSQLの重要な情報

項目	説明
cost=0.29..8.30	推定コスト（開始..終了）。低いほど良い
rows=1	推定行数
width=100	1行あたりのバイト数
Index Scan	インデックスを使った検索

7-4. EXPLAIN ANALYZE（実測値を取得）

PostgreSQLとMySQLでは、EXPLAIN ANALYZEを使うと、実際に実行して計測した値を確認できます。

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

— PostgreSQL/MySQLで実際に実行して計測（参考）
EXPLAIN ANALYZE
SELECT * FROM customers WHERE customer_id = 101;

PostgreSQLの実行結果イメージ:

Index Scan using customers_pkey on customers  
  (cost=0.29..8.30 rows=1 width=100) 
  (actual time=0.015..0.016 rows=1 loops=1)
Planning Time: 0.050 ms
Execution Time: 0.025 ms

✅ EXPLAIN ANALYZEのメリット

actual time：実際にかかった時間
rows：実際に処理された行数
loops：ループ回数
推定値と実測値の差異を確認できる

⚠️ EXPLAIN ANALYZEの注意点

EXPLAIN ANALYZEは実際にクエリを実行します。
UPDATE/DELETEなどの更新クエリには使わないでください!（データが変更されます）

7-5. データベース別のコマンドまとめ

📌 データベース別の実行計画コマンド

データベース	計画のみ	実測あり
SQLite	EXPLAIN QUERY PLAN SELECT…	（なし）
MySQL	EXPLAIN SELECT…	EXPLAIN ANALYZE SELECT…
PostgreSQL	EXPLAIN SELECT…	EXPLAIN ANALYZE SELECT…

📝 練習問題

実行計画を読み解く練習をしましょう!

問題 1基本

SCAN vs SEARCH

以下の実行計画のうち、速いのはどちらですか?
A) `--SCAN customers
B) `--SEARCH customers USING INDEX idx_email (email=?)

解答: B が速い

A（SCAN）：全件スキャン（遅い）
B（SEARCH + INDEX）：インデックス使用（速い）

解説:

SCANは全行をチェックしますが、SEARCHはインデックスで直接ジャンプできるため圧倒的に速いです。

問題 2基本

インデックスの確認

以下の実行計画で、インデックスは使われていますか?
QUERY PLAN `--SCAN customers

解答: いいえ、使われていません

解説:

単なるSCANは全件スキャンを意味します。USING INDEXと表示されないため、インデックスは使われていません。インデックスが使われている場合は「SEARCH … USING INDEX インデックス名」と表示されます。

問題 3基本

実行計画の基本

EXPLAIN QUERY PLANは実際にデータを取得しますか?

解答: いいえ、取得しません

解説:

EXPLAIN QUERY PLANは計画だけを表示して、実際のデータ取得は行いません。そのため、大量データのクエリでも安全に実行できます。何度実行してもデータは変わりません。

問題 4応用

ソート処理の発生

以下の実行計画で、問題となる部分はどこですか?
|--SEARCH orders USING INDEX idx_customer_id `--USE TEMP B-TREE FOR ORDER BY

解答: USE TEMP B-TREE FOR ORDER BY

解説:

一時的なB-treeを作ってソートしています。データ量が多いと遅くなります。ORDER BY用の列を含む複合インデックスを作れば改善できます。

例えば、WHERE customer_id = ? ORDER BY order_date DESCのクエリなら、(customer_id, order_date DESC)の複合インデックスを作成します。

問題 5応用

カバリングインデックス

COVERING INDEXとは何ですか? なぜ速いのですか?

解答:

カバリングインデックスは、インデックスだけで必要な全てのデータが取得できる状態です。

なぜ速いのか:

テーブル本体を読む必要がない
ディスクI/Oが減る
インデックスは通常、テーブルより小さい

例えば、SELECT prefecture, city FROM customers WHERE prefecture = '東京都'で、(prefecture, city)のインデックスがあれば、テーブル本体を読まずにインデックスだけで結果を返せます。

問題 6応用

実行計画の改善

以下のクエリの実行計画が`--SCAN customersになっています。
どう改善しますか?
SELECT * FROM customers WHERE email = 'test@example.com';

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

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

— 再度実行計画を確認
EXPLAIN QUERY PLAN
SELECT * FROM customers WHERE email = ‘test@example.com’;

— 期待される結果:
— `–SEARCH customers USING INDEX idx_email (email=?)

解説:

WHERE句で使う列（email）にインデックスを作成すれば、SCANからSEARCHに改善されます。

問題 7チャレンジ

JOINの実行順序

以下の実行計画を読み解いてください。どの順序で処理されますか?
|--SCAN orders AS o `--SEARCH customers AS c USING INTEGER PRIMARY KEY

処理順序:

SCAN orders：ordersテーブルを全件読む（外側のループ）
SEARCH customers：各orderに対して、customersを主キーで検索（内側のループ）

詳細:

ordersが外側のループ（先に全件読まれる）
customersが内側のループ（各orderに対して検索）
ordersの各行に対して、customersを検索する処理が繰り返される

改善案:

ordersが全件スキャンになっているので、ordersのcustomer_idにもインデックスを作成すると改善できます。

問題 8チャレンジ

複雑な実行計画の分析

以下の実行計画で、どこがボトルネックになりそうですか?
|--SCAN orders |--SEARCH customers USING INDEX idx_customer_id `--USE TEMP B-TREE FOR ORDER BY

ボトルネック:

SCAN orders：ordersの全件スキャン（遅い）
USE TEMP B-TREE：ソート処理の発生（遅い）

改善策:

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

— 1. ordersのJOINキーにインデックス
CREATE INDEX idx_orders_customer ON orders(customer_id);

— 2. ORDER BYの列を含む複合インデックス
— (元のクエリによる)
CREATE INDEX idx_orders_date ON orders(order_date DESC);

問題 9チャレンジ

最適化の実践

以下のクエリを最適化してください。実行計画で確認しながら改善してください。
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date >= '2024-01-01' ORDER BY o.order_date DESC;

ステップ1: 現状確認

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

EXPLAIN QUERY PLAN
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= ‘2024-01-01’
ORDER BY o.order_date DESC;

— 想定される結果（最適化前）:
— |–SCAN orders AS o
— |–SEARCH customers AS c USING INTEGER PRIMARY KEY
— `–USE TEMP B-TREE FOR ORDER BY

ステップ2: インデックス作成

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

— order_dateでのWHEREとORDER BY用
CREATE INDEX idx_orders_date ON orders(order_date DESC);

ステップ3: 再確認

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

EXPLAIN QUERY PLAN
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= ‘2024-01-01’
ORDER BY o.order_date DESC;

— 期待される結果（最適化後）:
— |–SEARCH orders AS o USING INDEX idx_orders_date
— `–SEARCH customers AS c USING INTEGER PRIMARY KEY
— (USE TEMP B-TREEが消える)

解説:

order_dateにインデックスを作成することで、WHEREの範囲検索とORDER BYのソートの両方が最適化されます。

📝 Step 17 のまとめ

✅ 学んだこと

実行計画とはクエリの「設計図」
EXPLAIN QUERY PLANで実行計画を確認できる
SCAN = 全件スキャン（遅い）
SEARCH = インデックス使用（速い）
USING INDEX = どのインデックスが使われているか
USE TEMP B-TREE = ソート処理が発生（遅い）
COVERING INDEX = インデックスだけで完結（超速い）

📌 実行計画のチェックポイント

✅ SEARCH になっているか?
✅ USING INDEX が表示されているか?
❌ SCAN になっていないか?
❌ USE TEMP B-TREE が出ていないか?
❌ CORRELATED SUBQUERY がないか?

💡 実行計画活用のコツ

遅いクエリをまず実行計画で確認
インデックスが使われているかチェック
改善策を実施後、再度実行計画で確認
本番環境と同じデータ量でテスト

❓ よくある質問

Q1: EXPLAIN QUERY PLANは実際にデータを取得しますか?

いいえ。計画だけを表示して、実際のデータ取得は行いません。そのため、大量データのクエリでも安全に実行できます。

Q2: SCANは必ず遅いですか?

データ量次第です。数百行程度の小さなテーブルなら、SCANでも十分速いです。しかし、数万行以上のテーブルでは、インデックスがないと非常に遅くなります。

Q3: 実行計画が予想と違う結果になります

統計情報が古い可能性があります。ANALYZE;を実行して統計情報を更新してください。データベースは統計情報を元に最適な実行計画を選びます。

Q4: インデックスがあるのにSCANになります

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

WHERE句の列に関数をかけている（UPPER、LOWERなど）
型変換が発生している（数値列に文字列で比較）
データ量が少なく、SCANの方が速いと判断された
インデックスの統計情報が古い（ANALYZEを実行）

Q5: 複数のインデックスがある場合、どれが使われますか?

データベースのオプティマイザが、統計情報を元に最も効率的なインデックスを自動的に選びます。通常は、最も絞り込めるインデックスが選ばれます。

Q6: COVERING INDEXはいつ使われますか?

SELECT句で取得する列が全てインデックスに含まれている場合に使われます。例えば、SELECT prefecture, city FROM customers WHERE prefecture='東京都'で、(prefecture, city)のインデックスがあれば、テーブル本体を読まずに済みます。

🎓 次のステップでは

Step 18: クエリ最適化テクニックでは、実行計画を活用して、具体的にクエリを最適化する方法を学びます!

📝

学習メモ

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

📋 過去のメモ一覧 ▼