⚡ Step 18: クエリ最適化テクニック

遅いクエリを高速化するテクニック集!

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

遅いクエリの診断手順（5ステップ）
N+1問題とその解決方法
不要なDISTINCTの削除
EXISTS vs INの使い分け
サブクエリ vs JOINの選択
大量データ処理の工夫

🔍 1. 遅いクエリの診断手順

1-1. 体系的なアプローチが大切

クエリが遅いとき、闇雲にインデックスを作るのは良くありません。体系的に診断して、本当のボトルネックを見つけましょう!

💡 医者で例えると…

「お腹が痛い」という症状に対して、いきなり手術はしませんよね?
まず検査をして、原因を特定してから、適切な治療をします。
クエリの最適化も同じです!

1-2. 診断の5ステップ

📌 診断の5ステップ

実行時間を計測する（現状把握）
実行計画を確認する（原因分析）
ボトルネックを特定する（問題箇所の特定）
改善策を実施する（治療）
効果を検証する（再検査）

1-3. ステップ1：実行時間を計測する

まず、現在のクエリがどのくらい時間がかかっているか計測します。

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

— SQLiteで実行時間を計測する設定
.timer on

— クエリを実行
SELECT * FROM customers 
WHERE prefecture = ‘東京都’;

実行結果イメージ:

customer_id | customer_name | prefecture | ...
------------+---------------+------------+----
101         | 田中太郎     | 東京都     | ...
102         | 佐藤花子     | 東京都     | ...
...

Run Time: real 2.345 user 2.100 sys 0.234

real 2.345 → 2.345秒かかっている（遅い!）

1-4. ステップ2：実行計画を確認する

次に、EXPLAIN QUERY PLANでクエリがどのように実行されているか確認します。

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

EXPLAIN QUERY PLAN
SELECT * FROM customers 
WHERE prefecture = ‘東京都’;

実行結果イメージ:

QUERY PLAN
`--SCAN customers

SCAN customers → 全件スキャンが原因!

1-5. ステップ3：ボトルネックを特定する

実行計画から、遅い原因を特定します。

💡 よくあるボトルネック

症状	原因	対策
SCAN テーブル名	インデックスがない	インデックスを作成
USE TEMP B-TREE	ソート処理が発生	ORDER BY列にインデックス
CORRELATED SUBQUERY	相関サブクエリが重い	JOINに書き換え
多数のJOIN	結合が多すぎる	結合キーにインデックス

1-6. ステップ4：改善策を実施する

ボトルネックが分かったら、改善策を実施します。今回は「SCAN」が原因なので、インデックスを作成します。

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

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

1-7. ステップ5：効果を検証する

改善後、再度計測して効果を確認します。

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

— 再度実行時間を計測
.timer on
SELECT * FROM customers 
WHERE prefecture = ‘東京都’;

実行結果イメージ:

Run Time: real 0.023 user 0.020 sys 0.003

2.345秒 → 0.023秒（約100倍高速化!）

実行計画も確認します。

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

EXPLAIN QUERY PLAN
SELECT * FROM customers 
WHERE prefecture = ‘東京都’;

実行結果イメージ:

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

SCAN → SEARCH USING INDEX に変わりました!

💡 診断のコツ

測定してから最適化する（感覚ではなく数値で判断）
1つずつ改善を試す（複数同時だと効果が分からない）
効果を数値で確認する（○秒→○秒と記録）
本番と同じデータ量でテストする

🔄 2. N+1問題とその解決

2-1. N+1問題とは?

N+1問題は、パフォーマンス問題の中で最も多い原因の1つです。ループの中でクエリを実行することで、大量のクエリが発生してしまう問題です。

💡 名前の由来

1回目：リストを取得するクエリ
N回：リストの各要素に対するクエリ
合計：1 + N = N+1回のクエリが実行される → N+1問題

2-2. N+1問題の具体例

「全顧客とその注文件数を表示したい」という要件を考えてみましょう。

⚠️ N+1問題が発生するコード

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

— 1. 全顧客を取得（1回目のクエリ）
SELECT * FROM customers;
— 結果: 1000人の顧客が取得される

— 2. 各顧客の注文を取得（N回のクエリ）
— 顧客が1000人いたら、以下が1000回実行される!
SELECT * FROM orders WHERE customer_id = 101;
SELECT * FROM orders WHERE customer_id = 102;
SELECT * FROM orders WHERE customer_id = 103;
…
SELECT * FROM orders WHERE customer_id = 1100;

— 合計: 1 + 1000 = 1001回のクエリ!

2-3. なぜN+1問題は遅いのか

N+1問題が遅い理由:

ネットワーク往復：1001回のクエリ = 1001回のネットワーク通信
クエリ解析：1001回のSQLパース処理
オーバーヘッド：各クエリの接続・切断処理

1回のクエリが0.005秒でも、1001回なら5秒以上かかります!

2-4. アプリケーションコードでのN+1問題

N+1問題は、アプリケーションのコードで発生することが多いです。

⚠️ 問題のあるPythonコード例

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

# Pythonでの例（問題あり）
# 1回目: 全顧客を取得
customers = db.execute(“SELECT * FROM customers”)

# N回: 各顧客の注文を個別に取得（N+1問題!）
for customer in customers:
    orders = db.execute(
        “SELECT * FROM orders WHERE customer_id = ?”,
        customer[‘id’]
    )
    print(f”{customer[‘name’]}: {len(orders)}件の注文”)

# 顧客が1000人なら、合計1001回のクエリ!

2-5. 解決策1：JOINで1回で取得

最も効果的な解決策は、JOINを使って1回のクエリで全てのデータを取得することです。

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

— ✅ 1回のクエリで全て取得
SELECT 
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) as order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

クエリの解説:

LEFT JOIN → 注文がない顧客も含める
COUNT(o.order_id) → 注文件数をカウント
GROUP BY → 顧客ごとに集計

アプリケーションコードも改善されます。

✅ 改善後のPythonコード

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

# Pythonでの例（改善後）
# 1回のクエリで全て取得
result = db.execute(“””
    SELECT 
        c.customer_id,
        c.customer_name,
        COUNT(o.order_id) as order_count
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.customer_name
“””)

for row in result:
    print(f”{row[‘customer_name’]}: {row[‘order_count’]}件の注文”)

# 1回のクエリで完了!

2-6. 解決策2：IN句で一括取得

JOINが使いにくい場合は、IN句で一括取得する方法もあります。

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

— 1. 顧客を取得（1回目）
SELECT * FROM customers;

— 2. 全顧客の注文を一括取得（2回目）
SELECT * FROM orders 
WHERE customer_id IN (101, 102, 103, 104, …);

— 合計: 2回のクエリで完了!（1001回→2回）

2-7. N+1問題解決の効果

✅ N+1問題解決の効果

項目	改善前	改善後
クエリ回数	1001回	1回
実行時間	5秒	0.05秒
速度向上	–	100倍!

2-8. N+1問題を防ぐコツ

📌 N+1問題を防ぐコツ

ループの中でクエリを実行しない
JOINで一度に取得する
IN句で一括取得する
ORMを使っている場合はEager Loadingを使う
SQLログを確認して、クエリ数を監視する

N+1問題の見つけ方:

SQLログを有効にして、クエリ数を確認する
1つの処理で数百〜数千のクエリが実行されていたら、N+1問題の可能性大
同じような形のクエリが大量に並んでいたら要注意

🔍 3. 不要なDISTINCTを削除

3-1. DISTINCTは重い処理

DISTINCTは重複を削除するために、全データをソートする必要があります。何も考えずに「とりあえずDISTINCT」と書くと、パフォーマンスが大きく低下します。

⚠️ DISTINCTのコスト

全データをメモリにロードする必要がある
重複チェックのためにソートが必要
データ量が多いと劇的に遅くなる
実行計画にUSE TEMP B-TREEが出ることも

3-2. 不要なDISTINCTの例1：主キーで検索

主キーは元々重複しないので、DISTINCTは不要です。

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

— ❌ 不要なDISTINCT（customer_idは主キー）
SELECT DISTINCT customer_id, customer_name
FROM customers;

— ✅ DISTINCTは不要（主キーは重複しない）
SELECT customer_id, customer_name
FROM customers;

ポイント:

主キー（PRIMARY KEY）やユニーク制約（UNIQUE）がある列を含むSELECTでは、DISTINCTは不要です。

3-3. 不要なDISTINCTの例2：JOINで重複が発生

JOINで重複が発生する場合、DISTINCTで解決するのは効率が悪いです。

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

— ❌ 効率が悪い（DISTINCTで重複削除）
SELECT DISTINCT c.customer_id, c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

このクエリは「注文がある顧客を取得したい」という意図ですが、1人の顧客が複数回注文していると、JOINで重複が発生します。DISTINCTで削除するのは非効率です。

3-4. DISTINCTの代替案1：GROUP BY

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

— ✅ GROUP BYで代用（より明示的）
SELECT c.customer_id, c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

GROUP BYのメリット:

意図が明確（「グループ化する」という意味）
集計関数を同時に使える（COUNT、SUM等）
インデックスを使える場合がある

3-5. DISTINCTの代替案2：EXISTS

「存在チェックだけ」なら、EXISTSが最も効率的です。

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

— ✅ EXISTSで存在チェック（最も効率的）
SELECT c.customer_id, c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id
);

EXISTSのメリット:

JOINしないので重複が発生しない
短絡評価：1件見つかれば即終了
DISTINCTやGROUP BYのソート処理が不要

3-6. DISTINCTが必要な場合

もちろん、DISTINCTが必要な場合もあります。

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

— ✅ 本当に重複を削除する必要がある場合
— 例：都道府県の一覧を取得
SELECT DISTINCT prefecture
FROM customers;

— ✅ 複数テーブルの結合で重複が発生し、
— かつ関連データも必要な場合
SELECT DISTINCT p.product_id, p.product_name
FROM products p
JOIN order_details od ON p.product_id = od.product_id
JOIN orders o ON od.order_id = o.order_id
WHERE o.order_date >= ‘2024-01-01’;

3-7. DISTINCTの代替手段まとめ

📌 DISTINCTの代替手段

目的	推奨方法
存在チェックのみ	EXISTS（最速）
集計も同時に	GROUP BY + 集計関数
主キーを含む	DISTINCTを削除
純粋な重複削除	DISTINCT（必要な場合のみ）

⚖️ 4. EXISTS vs INの使い分け

4-1. EXISTSとINの基本的な違い

どちらも「条件に合うデータがあるか」をチェックしますが、動作の仕組みが異なります。

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

— EXISTS: 「存在するか」だけをチェック
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id
);

— IN: 値のリストから検索
SELECT * FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders
);

4-2. 動作の仕組みの違い

EXISTSの動作:

外側のテーブル（customers）の行を1つ読む
内側のサブクエリを実行（条件に合う行を探す）
1件でも見つかれば即終了（短絡評価）
次の行へ進む

INの動作:

サブクエリを実行し、全ての値をリストに格納
外側のテーブル（customers）の行を読む
リストの中に値があるかチェック
次の行へ進む

4-3. EXISTSが速い場合

外側が小さく、内側が大きい場合は、EXISTSが有利です。

例：顧客100人、注文100万件

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

— EXISTS: 各顧客で1件見つかれば終了
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id
);
— 最大100回のサブクエリ実行
— 各サブクエリは1件見つければ終了（短絡評価）

— IN: 100万件の注文IDを全て取得
SELECT * FROM customers
WHERE customer_id IN (
    SELECT customer_id FROM orders
);
— まず100万件からcustomer_idを取得
— 重複削除して比較リストを作成

4-4. INが速い場合

内側が小さく、固定リストの場合は、INが有利です。

例：特定の商品（10個）を注文した顧客を検索

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

— IN: 小さなリストなら効率的
SELECT * FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id 
    FROM orders 
    WHERE product_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
);
— 10個の商品の注文を先に絞り込める
— 結果は小さなリスト

4-5. NOT EXISTS vs NOT IN（重要!）

「〜に含まれない」を検索する場合、NOT INには罠があります。

⚠️ NOT INの罠：NULLに注意!

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

— NOT IN: NULLがあると結果が0件になる!
SELECT * FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders
    — もしcustomer_idにNULLが1つでもあると…
    — 結果は0件になる!
);

サブクエリの結果にNULLが1つでも含まれると、NOT INは何も返しません!

NOT EXISTSを使えば、NULLの問題を回避できます。

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

— ✅ NOT EXISTS: NULL安全
SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id
);

— ✅ NOT INを使う場合はNULLを除外
SELECT * FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders 
    WHERE customer_id IS NOT NULL  — NULLを除外!
);

4-6. 使い分けの判断基準

📌 EXISTS vs INの使い分け

状況	推奨	理由
存在チェックのみ	EXISTS	短絡評価で速い
小さな固定リスト	IN	シンプルで効率的
NOT条件	NOT EXISTS	NULL安全
迷ったら	EXISTS	安全で効率的

💡 実務でのアドバイス

NOT条件では必ずNOT EXISTSを使う
EXISTSとINで迷ったら、両方試して実行計画を比較
INの中にNULLが入る可能性がないか確認する

🔀 5. サブクエリ vs JOINの選択

5-1. 基本的な考え方

サブクエリとJOINは、多くの場合で同じ結果を得られますが、パフォーマンスが異なることがあります。どちらを使うべきか、パターン別に見ていきましょう。

5-2. パターン1：存在チェックのみ

「注文がある顧客だけを取得したい」という場合です。

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

— サブクエリ（EXISTS）
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id
);

— JOIN
SELECT DISTINCT c.*
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

✅ 推奨: EXISTS

DISTINCTが不要（JOINは重複が発生するため必要）
短絡評価で速い（1件見つかれば終了）
意図が明確（「存在する」という意味）

5-3. パターン2：関連データも取得

「顧客情報と一緒に、注文件数も取得したい」という場合です。

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

— サブクエリ（各顧客ごとにサブクエリを実行）
SELECT 
    c.*,
    (SELECT COUNT(*) FROM orders o 
     WHERE o.customer_id = c.customer_id) as order_count
FROM customers c;

— JOIN（1回のスキャンで完了）
SELECT 
    c.*,
    COUNT(o.order_id) as order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

✅ 推奨: JOIN

ordersテーブルを1回だけスキャン
サブクエリは顧客数×回実行される（N+1問題と同じ）
LEFT JOINで注文がない顧客も含められる

5-4. パターン3：複数の集計を同時に

「顧客の注文件数、合計金額、最終注文日を全て取得したい」という場合です。

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

— サブクエリ（ordersを3回スキャン = 非効率）
SELECT 
    c.customer_name,
    (SELECT COUNT(*) FROM orders o 
     WHERE o.customer_id = c.customer_id) as order_count,
    (SELECT SUM(amount) FROM orders o 
     WHERE o.customer_id = c.customer_id) as total_amount,
    (SELECT MAX(order_date) FROM orders o 
     WHERE o.customer_id = c.customer_id) as last_order_date
FROM customers c;

このクエリは、顧客1人に対して3回のサブクエリを実行します。顧客が1万人なら、3万回のサブクエリが実行されます!

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

— ✅ JOIN（ordersを1回だけスキャン）
SELECT 
    c.customer_name,
    COUNT(o.order_id) as order_count,
    COALESCE(SUM(o.amount), 0) as total_amount,
    MAX(o.order_date) as last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

クエリの解説:

LEFT JOIN → 注文がない顧客も含める
COUNT(o.order_id) → 注文件数（NULLはカウントしない）
COALESCE(SUM(o.amount), 0) → 注文がない場合は0
MAX(o.order_date) → 最終注文日

✅ 推奨: JOIN

改善効果:
サブクエリ: 顧客数 × 3回 = 3万回のスキャン
JOIN: 1回のスキャン
→ 3万倍の改善!

5-5. パターン4：フィルタリングのみ

「2024年以降に注文された商品だけを取得したい」という場合です。

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

— サブクエリ（IN）
SELECT * FROM products
WHERE product_id IN (
    SELECT product_id FROM orders 
    WHERE order_date >= ‘2024-01-01’
);

— JOIN
SELECT DISTINCT p.*
FROM products p
JOIN orders o ON p.product_id = o.product_id
WHERE o.order_date >= ‘2024-01-01’;

— ✅ 推奨: サブクエリ（EXISTS）
SELECT * FROM products p
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.product_id = p.product_id 
      AND o.order_date >= ‘2024-01-01’
);

✅ 推奨: EXISTS

DISTINCTが不要（JOINは重複が発生）
意図が明確（「存在する商品」という意味）
短絡評価で効率的

5-6. 選択の基準まとめ

📌 サブクエリ vs JOINの選択基準

目的	推奨	理由
存在チェックのみ	EXISTS	DISTINCTが不要、短絡評価
関連データも取得	JOIN	1回のスキャンで完了
複数の集計	JOIN	1回のスキャンで全て計算
フィルタリング	EXISTS	DISTINCTが不要、明確
迷ったら	両方試す	実行計画を比較

💾 6. 大量データ処理の工夫

6-1. 一度に全件処理する危険性

100万件のデータを一度に処理すると、以下の問題が発生します。

⚠️ 一度に全件処理する問題点

メモリ不足：100万件をメモリに展開できない
タイムアウト：処理時間が長すぎて接続が切れる
ロック時間：長時間テーブルがロックされる
ログ肥大化：大量のトランザクションログが発生
リカバリ困難：途中で失敗すると最初からやり直し

6-2. 解決策1：バッチ処理で分割

大量データは小さな単位に分割して処理します。

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

— ❌ 一度に全件更新（危険）
UPDATE customers SET is_active = 1;

— ✅ バッチで分割（1万件ずつ）
UPDATE customers SET is_active = 1 
WHERE customer_id BETWEEN 1 AND 10000;

UPDATE customers SET is_active = 1 
WHERE customer_id BETWEEN 10001 AND 20000;

UPDATE customers SET is_active = 1 
WHERE customer_id BETWEEN 20001 AND 30000;
— 以下続く…

バッチ処理のメリット:

ロック時間が短い（他の処理が待たされない）
進捗が分かる（何%完了したか確認できる）
途中再開が可能（失敗しても続きから再開）
メモリ効率が良い（少量ずつ処理）

6-3. 解決策2：LIMITとOFFSETで分割取得

大量データを取得する場合、ページング処理で分割します。

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

— ページング処理
— 1ページ目（1〜1000件）
SELECT * FROM customers 
ORDER BY customer_id 
LIMIT 1000 OFFSET 0;

— 2ページ目（1001〜2000件）
SELECT * FROM customers 
ORDER BY customer_id 
LIMIT 1000 OFFSET 1000;

— 3ページ目（2001〜3000件）
SELECT * FROM customers 
ORDER BY customer_id 
LIMIT 1000 OFFSET 2000;

⚠️ OFFSETの問題点

OFFSETが大きくなると遅くなります。
OFFSET 100000 の場合、データベースは100,000行を読み飛ばす必要があります。
100ページ目でも1ページ目でも同じデータを読むので、非効率です。

6-4. 解決策3：Keyset Pagination（推奨）

Keyset Paginationは、前回の最後のIDを使って続きを取得する方法です。OFFSETより圧倒的に速いです!

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

— ✅ Keyset Pagination（推奨）
— 1回目（IDが0より大きいものを1000件）
SELECT * FROM customers 
WHERE customer_id > 0
ORDER BY customer_id 
LIMIT 1000;
— 最後のcustomer_id: 1000

— 2回目（IDが1000より大きいものを1000件）
SELECT * FROM customers 
WHERE customer_id > 1000
ORDER BY customer_id 
LIMIT 1000;
— 最後のcustomer_id: 2000

— 3回目（IDが2000より大きいものを1000件）
SELECT * FROM customers 
WHERE customer_id > 2000
ORDER BY customer_id 
LIMIT 1000;
— 最後のcustomer_id: 3000

✅ Keyset Paginationのメリット

OFFSETより圧倒的に速い
ページ数に関係なく一定速度（100ページ目でも1ページ目と同じ速度）
インデックスを効率的に使える
無限スクロールに最適

OFFSETとKeyset Paginationの比較:

方式	100ページ目の処理	速度
OFFSET	99,000行を読み飛ばしてから取得	⚠️ 遅い
Keyset	WHERE id > 99000 で直接取得	✅ 速い（1ページ目と同じ）

6-5. 解決策4：一時テーブルで中間結果を保存

複雑な集計結果を何度も使う場合、一時テーブルに保存すると効率的です。

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

— 複雑な集計を一時テーブルに保存
CREATE TEMP TABLE customer_summary AS
SELECT 
    customer_id,
    COUNT(*) as order_count,
    SUM(amount) as total_amount
FROM orders
GROUP BY customer_id;

クエリの解説:

CREATE TEMP TABLE → 一時テーブルを作成
AS SELECT ... → SELECTの結果をテーブルに保存
一時テーブルはセッション終了時に自動削除される

一時テーブルにインデックスを追加して、高速に検索できます。

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

— 一時テーブルにインデックスを追加
CREATE INDEX tmp_idx ON customer_summary(customer_id);

— 一時テーブルを使って高速に処理
SELECT c.customer_name, s.order_count, s.total_amount
FROM customers c
JOIN customer_summary s ON c.customer_id = s.customer_id
WHERE s.total_amount > 100000;

一時テーブルの活用シーン:

複雑な集計結果を複数回使う場合
中間結果を保存してデバッグしたい場合
段階的な処理を行いたい場合
レポート作成など、同じ計算を何度も使う場合

6-6. 大量データ処理のまとめ

💡 大量データ処理のコツ

バッチで分割する（1万〜10万件ずつ）
Keyset Paginationを使う（OFFSETは避ける）
一時テーブルで中間結果を保存
進捗を表示する（何%完了したか）
再開可能な設計にする（途中失敗に備える）

📝 練習問題

クエリ最適化の実践をしましょう!

問題 1基本

N+1問題の特定

以下のコードにN+1問題はありますか?
customers = SELECT * FROM customers; FOR EACH customer: SELECT * FROM orders WHERE customer_id = customer.id;

解答: はい、N+1問題です

理由:

1回目: 全顧客を取得
N回: 各顧客の注文を個別に取得
顧客が1000人なら、合計1001回のクエリ!

解決策:

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

— JOINで1回のクエリにまとめる
SELECT c.*, COUNT(o.order_id) as order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

問題 2基本

不要なDISTINCTの削除

以下のクエリでDISTINCTは必要ですか?
SELECT DISTINCT customer_id, customer_name FROM customers;

解答: 不要です

理由:

customer_idは主キーなので重複しない
DISTINCTは不要なソート処理を発生させる

改善:

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

— DISTINCTを削除
SELECT customer_id, customer_name FROM customers;

問題 3基本

診断の5ステップ

遅いクエリを最適化する際の5ステップを順番に答えてください。

解答:

実行時間を計測する（現状把握）
実行計画を確認する（原因分析）
ボトルネックを特定する（問題箇所の特定）
改善策を実施する（治療）
効果を検証する（再検査）

感覚ではなく、数値で効果を確認することが重要です。

問題 4応用

EXISTS vs INの選択

以下の2つのクエリで、どちらが速い可能性が高いですか?
顧客: 10万人、注文: 1000万件
A) WHERE EXISTS (SELECT 1 FROM orders WHERE ...)
B) WHERE customer_id IN (SELECT customer_id FROM orders WHERE ...)

解答: A（EXISTS）が速い

理由:

EXISTS：各顧客で1件見つかれば即終了（短絡評価）
IN：1000万件の注文から全customer_idを取得してから比較
外側（顧客）が小さく、内側（注文）が大きい場合はEXISTSが有利

問題 5応用

サブクエリの最適化

以下のクエリを最適化してください。
SELECT c.*, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id), (SELECT SUM(amount) FROM orders o WHERE o.customer_id = c.customer_id) FROM customers c;

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

— ✅ JOINで1回のスキャンに統合
SELECT 
    c.*,
    COUNT(o.order_id) as order_count,
    COALESCE(SUM(o.amount), 0) as total_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

解説:

元のクエリ: ordersを顧客数×2回スキャン
最適化後: ordersを1回だけスキャン
顧客が10万人なら、20万回→1回に削減!

問題 6応用

大量データの削除

100万件のデータから、古いデータ50万件を削除したい。
一度に削除するのは危険です。どう改善しますか?

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

— ✅ バッチで分割削除（1万件ずつ）
DELETE FROM orders 
WHERE order_id IN (
    SELECT order_id 
    FROM orders 
    WHERE order_date < '2020-01-01'
    ORDER BY order_id
    LIMIT 10000
);

-- または、IDの範囲で分割
DELETE FROM orders 
WHERE order_id BETWEEN 1 AND 10000
  AND order_date < '2020-01-01';

DELETE FROM orders 
WHERE order_id BETWEEN 10001 AND 20000
  AND order_date < '2020-01-01';
-- 以下、50回繰り返す...

解説:

バッチで分割することで、ロック時間を短縮し、メモリ不足を防げます。途中で失敗しても、続きから再開できます。

問題 7チャレンジ

N+1問題の解決

以下の処理を1回のクエリで実現してください。
「各顧客の最新注文日と総注文金額を取得する」
元のコード:
FOR EACH customer: last_order = SELECT MAX(order_date) FROM orders WHERE ... total = SELECT SUM(amount) FROM orders WHERE ...

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

— ✅ 1回のクエリで全て取得
SELECT 
    c.customer_id,
    c.customer_name,
    MAX(o.order_date) as last_order_date,
    COALESCE(SUM(o.amount), 0) as total_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

解説:

元: 顧客数 × 2回のクエリ
改善後: 1回のクエリ
10万人なら、20万回→1回に削減!

問題 8チャレンジ

Keyset Paginationの実装

OFFSET方式のページングをKeyset方式に書き換えてください。
SELECT * FROM orders ORDER BY order_id LIMIT 100 OFFSET 10000;

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

— ✅ Keyset Pagination
— 前回の最後のorder_idが10000だったとする

— 次のページを取得
SELECT * FROM orders 
WHERE order_id > 10000
ORDER BY order_id 
LIMIT 100;

— OFFSETと違い、常に高速!
— 100ページ目でも1ページ目と同じ速度

解説:

OFFSETは前のページも読む必要がある（10000行を読み飛ばす）
Keysetは前回の最後のIDから続きを読むだけ
100ページ目でも1ページ目と同じ速度!

問題 9チャレンジ

総合最適化

以下のクエリを可能な限り最適化してください。
SELECT DISTINCT c.customer_name FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_date >= '2024-01-01' ORDER BY c.customer_name;

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

— ステップ1: DISTINCTをEXISTSに置き換え
SELECT c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id 
      AND o.order_date >= ‘2024-01-01’
)
ORDER BY c.customer_name;

— ステップ2: インデックス追加
CREATE INDEX idx_orders_customer_date 
ON orders(customer_id, order_date);

CREATE INDEX idx_customers_name 
ON customers(customer_name);

— ステップ3: 実行計画で確認
EXPLAIN QUERY PLAN
SELECT c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id 
      AND o.order_date >= ‘2024-01-01’
)
ORDER BY c.customer_name;

最適化のポイント:

DISTINCTをEXISTSに置き換え（ソート削減）
JOINキーと検索条件にインデックス
ORDER BY用のインデックス

📝 Step 18 のまとめ

✅ 学んだテクニック

診断手順：測定→分析→改善→検証
N+1問題：JOINで一度に取得
DISTINCT削除：不要なソートを回避
EXISTS vs IN：適切な選択
サブクエリ vs JOIN：用途に応じて使い分け
大量データ：バッチ処理とKeyset Pagination

📌 最適化の黄金ルール

測定してから最適化する
ボトルネックを正確に特定する
1つずつ改善を試す
効果を検証する
過度な最適化はしない

💡 優先順位

N+1問題を解消（最重要!）
インデックスを適切に作成
不要なDISTINCTを削除
EXISTS/INを適切に選択
バッチ処理で大量データを扱う

❓ よくある質問

Q1: N+1問題はどう見つけますか?

ログでクエリ数を確認してください。1つの処理で数百〜数千のクエリが実行されていたら、N+1問題の可能性が高いです。ORMを使っている場合は、SQLログを有効にしましょう。

Q2: DISTINCTとGROUP BYどちらが速い?

GROUP BYの方が速いことが多いです。GROUP BYはインデックスを使える場合があり、集計も同時にできます。ただし、単に重複削除だけなら大差ありません。迷ったら実行計画を比較しましょう。

Q3: EXISTSとINで結果が違うことはある?

NOT INでNULLがある場合だけ違います。NOT INはサブクエリにNULLがあると、何も返しません。NOT EXISTSはNULL安全です。通常のEXISTS/INでは結果は同じです。

Q4: Keyset PaginationとOFFSETの使い分けは?

Keyset Paginationを推奨します。ただし、「Nページ目にジャンプ」が必要な場合はOFFSETを使います。Keysetは「次へ」「前へ」のみの無限スクロールに最適です。

Q5: 一時テーブルはいつ使うべき?

以下の場合に有効です:

複雑な集計結果を複数回使う
中間結果を保存してデバッグしたい
段階的な処理を行いたい

ただし、作成にもコストがかかるので、1回しか使わないなら不要です。

Q6: 最適化の効果をどう測定する?

実行時間を計測してください。SQLiteなら.timer on、アプリケーション側なら時間計測ライブラリを使います。改善前後で数値で比較することが重要です。「なんとなく速くなった」ではなく、「10秒→0.5秒（20倍改善）」のように定量的に評価しましょう。

🎓 次のステップでは

Step 19: パフォーマンスチューニング実践では、実際のシナリオを使って、これまで学んだテクニックを総合的に活用する実践演習を行います!

📝

学習メモ

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

📋 過去のメモ一覧 ▼