쿼리 최적화 — 옵티마이저·실행계획·EXPLAIN (PostgreSQL·MySQL)

같은 SQL, 수십 가지 실행 방법

SELECT * FROM orders o JOIN member m ON ... WHERE ... 한 줄을 실행하는 방법은 수십 가지다 — 어느 테이블을 먼저 읽을지, 인덱스를 탈지 풀스캔할지, 조인을 어떤 알고리즘으로 엮을지. 그리고 그 방법들 사이의 속도 차이는 수천 배가 날 수 있다. 누가 이 선택을 할까? — 우리가 "어떻게"를 안 적기 때문에(SQL은 "무엇"만 선언한다), DB의 **옵티마이저(optimizer)**가 대신 고른다. 그 선택의 결과물이 **실행계획(execution plan)**이다.

현대 DB의 옵티마이저는 **비용 기반(cost-based)**이다 — 각 후보 계획의 비용(읽을 디스크 페이지·CPU)을 통계로 추정해 가장 싼 것을 고른다. 그래서 느린 쿼리를 고치는 일은 "이렇게 하면 빠를 거야"라는 추측이 아니라, **EXPLAIN으로 옵티마이저가 실제 어떤 계획을 골랐는지 보고 시작*해야 한다. (출처: PostgreSQL — Using EXPLAIN / MySQL 8.4 — Optimizing Queries with EXPLAIN.)

옵티마이저는 통계로 추정한다 — 통계가 낡으면 헛다리

옵티마이저는 미래를 모른다 — 대신 테이블 통계(행 수, 칼럼별 값 분포·카디널리티, 히스토그램, NULL 비율)를 보고 *"이 조건이면 대략 몇 행이 나올까"*를 추정한다. 이 추정이 모든 결정의 출발점이다. 예컨대 *"이 조건에 맞는 행이 전체의 0.1%"*라고 추정하면 인덱스로 그 몇 행만 집는 게 싸다고 보고, *"50%가 나온다"*면 어차피 절반을 읽을 거 풀스캔이 낫다고 본다.

여기서 결정적인 함정 — 통계가 낡으면 추정이 틀리고, 추정이 틀리면 계획이 망한다. 대량 INSERT/DELETE 직후엔 통계가 옛 데이터를 반영해, 실제론 100만 행인데 100행으로 오추정하면 — 인덱스를 100번 타면 될 줄 알고 100만 번 타는 재앙이 벌어진다. 그래서 대량 변경 후엔 통계를 갱신해야 한다.

PostgreSQL — autovacuum의 auto-analyze가 자동 갱신, 수동은 ANALYZE table;
MySQL/InnoDB — ANALYZE TABLE table; (InnoDB는 통계를 샘플링)

EXPLAIN 읽는 법 — 추정과 실제를 비교한다

EXPLAIN은 계획만(추정치), EXPLAIN ANALYZE는 실제로 실행해 진짜 시간·행 수까지 보여준다 — 진단할 땐 ANALYZE를 본다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE member_id = 42 ORDER BY created_at DESC LIMIT 20;

Limit  (actual time=0.05..0.07 rows=20)
  ->  Index Scan using idx_orders_member_created on orders   ← 인덱스로 바로 진입(좋음)
        Index Cond: (member_id = 42)
        (estimated rows=18  actual rows=20)                  ← 추정≈실제(통계 건강)

읽는 세 가지 요령 — (1) 가장 안쪽(들여쓰기 깊은) 노드부터 위로 데이터가 흐른다(안쪽이 먼저 실행). (2) estimated rows vs actual rows의 차이를 본다 — 추정 100인데 실제 100만이면 통계가 거짓말을 한 것이라 나쁜 계획의 원흉이다. (3) 큰 테이블에 Seq Scan(MySQL은 type: ALL)이 보이면 인덱스 누락이나 인덱스 무력화([06])를 의심한다.

MySQL은 EXPLAIN의 type 칼럼이 핵심 신호다(좋은 순) — const(PK로 1행) → eq_ref → ref(인덱스 동치) → range(범위) → index(인덱스 풀스캔) → ALL(풀 테이블 스캔, 보통 빨간불). Extra의 Using filesort·Using temporary(정렬·임시 테이블을 만드는 비싼 작업)도 경고 신호다.

조인 알고리즘 — 왜 이걸 골랐나

조인은 세 알고리즘 중에서 비용으로 선택된다. 각각이 언제 유리한지를 알면 EXPLAIN의 선택이 이해된다.

다이어그램 로딩 중…

Nested Loop — 바깥 결과가 작고(예: WHERE로 20행) 안쪽 테이블에 인덱스가 있으면 최고다(20번만 인덱스 탐색). 반대로 둘 다 크면 O(n·m)으로 최악이다.
Hash Join — 큰 두 테이블을 등치(=)로 조인할 때의 일꾼이다. 작은 쪽으로 메모리에 해시 테이블을 만들고 큰 쪽을 훑어 맞춘다(메모리가 필요).
Merge Join — 양쪽이 이미 조인 키로 정렬돼 있으면(인덱스 순서) 정렬된 두 줄을 지퍼처럼 병합해 효율적이다.

MySQL은 오래 Nested Loop 계열만 썼지만 8.0.18+부터 Hash Join을 지원한다. PostgreSQL은 셋 다 오래전부터 쓴다 — 그래서 큰 테이블 조인이 느리면 MySQL 버전과 Hash Join 사용 여부를 본다.

느린 쿼리는 추측 말고 측정한다

*"이 쿼리가 느릴 것 같다"*는 감은 자주 틀린다. 측정이 먼저다.

PostgreSQL — pg_stat_statements 확장으로 총시간·호출수·평균이 큰 쿼리를 집계한다.
MySQL — slow query log + performance_schema/sys 스키마로 상위 쿼리를 본다.

여기서 중요한 통찰 — *"가장 느린 한 방"보다 "자주 불려 총합이 큰 쿼리"*가 흔히 더 아프다. 1ms짜리가 100만 번 불리면(총 1000초) *1초짜리 10번(총 10초)*보다 훨씬 큰 부담이다. 그래서 평균이 아니라 "호출수 × 평균"의 총합으로 우선순위를 잡는다.

흔한 안티패턴과 처방

N+1 쿼리 — 목록을 1번 가져오고 각 항목의 연관을 1번씩 N번 더 조회(1+N). 조인이나 IN 한 방으로 합친다(ORM의 페치 조인). 목록 페이지에서 쿼리가 수백 개면 거의 N+1이다.
SELECT * — 안 쓰는 칼럼까지 읽어 커버링 인덱스를 깨고([06]) 네트워크·메모리를 낭비한다. 필요한 칼럼만 적는다.
인덱스 무력화 — WHERE DATE(created_at)=...·앞 와일드카드·형변환은 인덱스를 못 타게 한다([06]).
거대 OFFSET 페이지네이션 — OFFSET 1000000은 앞 100만 행을 읽고 버려 뒤로 갈수록 느려진다.

-- ❌ 뒤 페이지일수록 느려짐 (앞 100만 행을 읽고 버림)
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 1000000;
-- ✅ 키셋 페이지네이션 — 마지막 본 id 이후로, 항상 일정하게 빠름
SELECT * FROM orders WHERE id > :last_id ORDER BY id LIMIT 20;

정리 — 계획을 보고, 통계를 믿게 하고, 인덱스로 돕는다

쿼리 최적화의 절차는 한결같다 — (1) EXPLAIN ANALYZE로 옵티마이저가 고른 실제 계획을 본다 → (2) 추정 vs 실제 행 수로 통계 건강을 확인 → (3) 큰 테이블 풀스캔·filesort·N+1·큰 OFFSET 같은 신호를 찾는다 → (4) 인덱스·쿼리 재작성으로 고치고 다시 측정한다. 옵티마이저는 통계를 믿고 움직이니, 통계를 신선하게 유지하는 것이 모든 튜닝의 토대다.

한 줄 요약 — 비용 기반 옵티마이저는 통계로 행 수를 추정해 가장 싼 계획을 고름(통계 낡으면 헛다리). EXPLAIN ANALYZE로 추정≈실제인지 보고 큰 테이블 Seq Scan/type: ALL·filesort·N+1·큰 OFFSET을 잡는다. 조인: Nested Loop(작은쪽+인덱스)·Hash(큰 등치조인)·Merge(정렬됨)를 비용으로 선택. 느린 쿼리는 호출수×평균 총합으로 측정(pg_stat_statements/slow log). 큰 OFFSET은 키셋 페이지네이션.

(출처: PostgreSQL 17 — Using EXPLAIN / Planner / MySQL 8.4 — EXPLAIN Output / Optimizer / Silberschatz DBSC 7e Ch 15·16.)