Query Optimization Catalog

Query Optimization Catalog — SQL 쿼리 최적화 카탈로그

performance-analyst 에이전트가 성능 최적화 시 활용하는 인덱스 전략, 실행 계획 분석, 쿼리 안티패턴 해결 레퍼런스.

대상 에이전트

performance-analyst — 이 스킬의 최적화 기법을 성능 분석과 인덱스 설계에 직접 적용한다.

인덱스 전략

인덱스 유형별 선택 가이드

복합 인덱스 설계 원칙

왼쪽 접두사 규칙 (Leftmost Prefix)

INDEX idx_abc ON table(a, b, c)

-- 활용 가능:
WHERE a = 1                          -- O (a만)
WHERE a = 1 AND b = 2               -- O (a, b)
WHERE a = 1 AND b = 2 AND c = 3     -- O (전체)
WHERE a = 1 AND c = 3               -- 부분 (a만, c 건너뜀)

-- 활용 불가:
WHERE b = 2                          -- X (a 누락)
WHERE c = 3                          -- X (a, b 누락)

컬럼 순서 결정 규칙

1. WHERE 등호(=) 조건 컬럼 먼저
2. 정렬(ORDER BY) 컬럼 다음
3. 범위(<, >, BETWEEN) 컬럼 마지막
4. 카디널리티 높은 것 먼저 (단, 규칙 1~3 우선)

커버링 인덱스

인덱스만으로 쿼리 결과를 반환 (테이블 접근 불필요).

-- 커버링 인덱스
CREATE INDEX idx_covering ON orders(user_id, status, created_at);

-- 이 쿼리는 인덱스만으로 응답 (Index Only Scan)
SELECT status, created_at FROM orders WHERE user_id = 123;

인덱스 생성/삭제 판단

슬로우 쿼리 안티패턴 & 해결

1. N+1 문제

-- 안티패턴: 루프에서 개별 쿼리
SELECT * FROM users;
-- 각 user마다:
SELECT * FROM orders WHERE user_id = ?;  -- N번 반복!

-- 해결: JOIN 또는 IN
SELECT u.*, o.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 또는 배치 로딩
SELECT * FROM orders WHERE user_id IN (1, 2, 3, ...);

2. SELECT *

-- 안티패턴
SELECT * FROM products WHERE category = 'electronics';

-- 해결: 필요한 컬럼만
SELECT id, name, price FROM products WHERE category = 'electronics';
-- 커버링 인덱스 활용 가능

3. 함수 사용으로 인덱스 무효화

-- 안티패턴: 인덱스 컬럼에 함수 적용
WHERE YEAR(created_at) = 2025

-- 해결: 범위 조건으로 변환
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'

4. OR 조건의 인덱스 무효화

-- 안티패턴
WHERE status = 'active' OR category = 'books'

-- 해결: UNION ALL
SELECT * FROM products WHERE status = 'active'
UNION ALL
SELECT * FROM products WHERE category = 'books' AND status != 'active'

5. 서브쿼리 vs JOIN

-- 안티패턴: 상관 서브쿼리
SELECT * FROM orders o
WHERE o.total > (SELECT AVG(total) FROM orders WHERE user_id = o.user_id);

-- 해결: JOIN + 집계
SELECT o.* FROM orders o
JOIN (SELECT user_id, AVG(total) as avg_total FROM orders GROUP BY user_id) a
ON o.user_id = a.user_id
WHERE o.total > a.avg_total;

6. OFFSET 페이지네이션

-- 안티패턴: 깊은 OFFSET
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 100000;

-- 해결: 커서 기반 (Keyset)
SELECT * FROM products WHERE id > 100000 ORDER BY id LIMIT 20;

7. 대량 IN 절

-- 안티패턴: 수천 개 ID
WHERE id IN (1, 2, 3, ..., 10000)

-- 해결: 임시 테이블 또는 JOIN
-- PostgreSQL: VALUES 또는 ANY(ARRAY[...])
-- 범용: 배치 처리 (500개씩)

EXPLAIN 분석 가이드 (PostgreSQL)

실행 계획 읽기

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 123 AND status = 'completed';

주요 노드 유형

경고 신호

• Seq Scan on 대형 테이블 → 인덱스 필요
• Sort with external merge Disk → work_mem 부족
• actual rows >> estimated rows → 통계 오래됨 (ANALYZE 필요)
• Nested Loop with 대형 테이블 → Hash Join 유도

파티셔닝 전략

파티셔닝 필요 신호

• 테이블 크기 > 수억 행
• 시계열 데이터 (로그, 이벤트, 메트릭)
• 오래된 데이터 주기적 삭제/아카이빙
• 특정 기간 쿼리가 대부분

파티셔닝 유형

Range 파티셔닝 예시 (PostgreSQL)

CREATE TABLE events (
  id BIGINT,
  event_time TIMESTAMPTZ,
  data JSONB
) PARTITION BY RANGE (event_time);

CREATE TABLE events_2025_q1 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

캐싱 전략

캐시 무효화 전략

• TTL 기반: 만료 시간 후 자동 갱신
• 이벤트 기반: 데이터 변경 시 즉시 삭제
• Write-Through: 쓰기 시 캐시도 함께 갱신
• Cache-Aside: 읽기 시 캐시 없으면 DB 조회 후 캐시 저장