데이터베이스 쿼리 최적화

Python code

import sqlite3
import time

# 테스트 DB 생성
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
conn.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, "
             "user_id INTEGER, amount REAL)")

# 대량 데이터 삽입
conn.executemany("INSERT INTO users VALUES (?, ?)",
    [(i, f"User-{i}") for i in range(1000)])
conn.executemany("INSERT INTO orders VALUES (?, ?, ?)",
    [(i, i % 1000, i * 100) for i in range(10000)])
conn.commit()

# 안티패턴: N+1 쿼리
start = time.perf_counter()
users = conn.execute("SELECT id FROM users LIMIT 100").fetchall()
for (uid,) in users:
    conn.execute("SELECT SUM(amount) FROM orders WHERE user_id=?",
                 (uid,)).fetchone()
t1 = time.perf_counter() - start
print(f"N+1 쿼리: {t1:.4f}초 (101회 쿼리)")

# 최적화: JOIN으로 1회 쿼리
start = time.perf_counter()
conn.execute("""
    SELECT u.id, u.name, COALESCE(SUM(o.amount), 0)
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id
    LIMIT 100
""").fetchall()
t2 = time.perf_counter() - start
print(f"JOIN 쿼리: {t2:.4f}초 (1회 쿼리)")
print(f"속도 향상: {t1/t2:.1f}x")

# 인덱스 추가
conn.execute("CREATE INDEX idx_orders_user ON orders(user_id)")
start = time.perf_counter()
conn.execute("SELECT * FROM orders WHERE user_id = 500").fetchall()
t3 = time.perf_counter() - start
print(f"인덱스 조회: {t3:.6f}초")

conn.close()