PART 03 — SQL アンチパターン
ロック競合・集計コスト・その他の注意点

with db.begin():
    db.execute("UPDATE accounts SET balance = balance - 1000 WHERE id = 1")
    # ← ここで外部APIを呼ぶ（数百ms〜数秒かかる）
    result = payment_api.charge(amount=1000)
    db.execute("UPDATE accounts SET balance = balance + 1000 WHERE id = 2")
# APIが遅い間ずっとロックが保持される

# 1. 外部処理を先に行う
result = payment_api.charge(amount=1000)

# 2. DBへの書き込みだけをトランザクションで囲む（短時間で完了）
if result.success:
    with db.begin():
        db.execute("UPDATE accounts SET balance = balance - 1000 WHERE id = 1")
        db.execute("UPDATE accounts SET balance = balance + 1000 WHERE id = 2")

-- トランザクション A: accounts → orders の順でロック
BEGIN;
  SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
  SELECT * FROM orders   WHERE id = 101 FOR UPDATE;
COMMIT;

-- トランザクション B: orders → accounts の逆順でロック
BEGIN;
  SELECT * FROM orders   WHERE id = 101 FOR UPDATE;
  SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- A がロック中で待機 → デッドロック
COMMIT;

-- A・B ともに同じ順序（テーブル名アルファベット順など）でロックする
-- 両方: accounts → orders の順
BEGIN;
  SELECT * FROM accounts WHERE id = 1   FOR UPDATE;
  SELECT * FROM orders   WHERE id = 101 FOR UPDATE;
COMMIT;

-- user_id > 1000 は集計「前」に絞れるのに HAVING で書いている
-- 全 user_id を集計してから捨てる
SELECT user_id, COUNT(*)
FROM orders
GROUP BY user_id
HAVING user_id > 1000 AND COUNT(*) >= 3;

-- user_id > 1000 は WHERE に移す（集計対象を最初から絞る）
-- COUNT(*) >= 3 は集計後の条件なので HAVING に残す
SELECT user_id, COUNT(*)
FROM orders
WHERE user_id > 1000
GROUP BY user_id
HAVING COUNT(*) >= 3;

-- orders テーブル: 10行あるが coupon_code が NULL の行が3行
SELECT
  COUNT(*)              AS total_orders,      -- 10
  COUNT(coupon_code)    AS orders_with_coupon, -- 7
  COUNT(DISTINCT coupon_code) AS unique_coupons -- クーポンの種類数
FROM orders;

-- 一時テーブルに入れて JOIN する（プランナーが最適化しやすい）
CREATE TEMPORARY TABLE target_ids (id INT PRIMARY KEY);
INSERT INTO target_ids VALUES (1),(2),...;  -- バルクインサート

SELECT p.* FROM products p
JOIN target_ids t ON t.id = p.id;

-- またはアプリケーション側でチャンク分割する (Python 例)
chunk_size = 500
for i in range(0, len(ids), chunk_size):
    chunk = ids[i:i+chunk_size]
    results += db.execute("SELECT * FROM products WHERE id IN (%s)"
                          % ",".join("?" * len(chunk)), chunk).fetchall()

-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM orders WHERE created_at > '2024-01-01';

-- 見るべきキーワード
-- Seq Scan        → フルスキャン（インデックスが使われていない）
-- Index Scan      → インデックスが使われている ✅
-- Index Only Scan → カバリングインデックス（最速）✅
-- Sort (filesort) → ORDER BY がインデックスを使えていない
-- Hash Join / Nested Loop → 結合の種類

-- MySQL
EXPLAIN FORMAT=JSON
SELECT * FROM orders WHERE created_at > '2024-01-01';