SQL パフォーマンス最適化 — EXPLAIN・Index Scanの基礎

SELECT order_id, created_at, amount
FROM   orders
WHERE  created_at >= '2024-01-01'    -- 列を裸のまま使い、定数と比較（Sargable）
  AND  created_at <  '2025-01-01'    -- 「翌年元日未満」で境界を表現（半開区間が定石）
ORDER BY order_id;

/*
  実行順序:
  1. インデックスから range の開始位置を二分探索
  2. リーフを右に辿りながら、終了境界まで連続スキャン
  3. テーブル参照（または Index Only Scan）で SELECT 列を取得
  4. ORDER BY order_id で並べ替え
  */

SELECT order_id, amount  -- 【列指定】必要な2列だけ。I/O・ネット転送・Index Only Scanの余地を確保
FROM   orders
ORDER BY amount DESC     -- 金額の降順で並べ替え
LIMIT    3;              -- 【Top-N】上位3件だけを保持。全件分を保持せずヒープソートになる

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM orders              → 6行をスキャン
  2. ORDER BY amount DESC     → Top-N ソート（上位のみ保持）
  3. LIMIT 3                  → 保持した上位3件を確定（順序付きインデックスがあれば早期終了可能）
  4. SELECT order_id, amount  → 必要な2列だけ返却

  プラン比較（概念）:
  ✗ SELECT *  ORDER BY amount DESC;
       → 全6列を読込 → 6行を全件ソート → 全6行返却
  ✓ SELECT order_id, amount ORDER BY amount DESC LIMIT 3;
       → 2列だけ読込 → ヒープサイズ3でTop-Nソート → 3行返却
*/

SELECT   c.customer_id, c.name
FROM     customers c
WHERE    EXISTS (                       -- 【存在チェック】対応する orders 行が1件でもあれば TRUE
  SELECT 1                              -- 中身は何でもよい。慣習的に「1」を使う（最小限）
  FROM   orders o
  WHERE  o.customer_id = c.customer_id  -- 【相関条件】外側 c の customer_id と結合（NULL は = で一致しない）
)
ORDER BY c.customer_id;                 -- customer_id 昇順で並べ替え

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM customers c      → 4行スキャン（外側）
  2. 各 c に対して EXISTS を評価   → HIT した時点で打ち切り
  3. ORDER BY customer_id  → 昇順に整列

  なぜ NOT IN ではダメか:
    WHERE customer_id NOT IN (SELECT customer_id FROM orders)
    → サブクエリ結果が {101,101,103,NULL,103}
    → 「NOT IN にNULLが含まれる」と全行 UNKNOWN（≠TRUE）になり結果0件。
    → NOT EXISTS なら NULL は相関条件で一致せず、安全。
*/

SELECT   category,
         COUNT(*)    AS order_count,  -- グループ内の全行数（NULLも含む）
         SUM(amount) AS total_amount  -- グループ内の合計金額
FROM     orders
GROUP BY category                     -- カテゴリ単位で集約
ORDER BY total_amount DESC, category  -- 合計降順、同数なら category 昇順で安定化
LIMIT    3;                           -- 【Top-N】上位3件だけを保持（全件の完全なソートは不要）

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM orders          → 8行読込
  2. GROUP BY category    → カテゴリで集約
  3. ORDER BY total_amount DESC, category  → Top-N ソート（上位のみ保持）
  4. LIMIT 3              → 保持した上位3件を確定

  COUNT の使い分け（参考）:
    COUNT(*)                       … 全行数（NULL含む、最も速いことが多い）
    COUNT(amount)                  … amount が NULL でない行数
    COUNT(DISTINCT customer_id)    … ユニーク顧客数（ハッシュ重複排除が要る）
*/