Database ‐ Execution Plans 2 ‐ Mastering ANALYZE - thought-corner/backend-roadmap GitHub Wiki

EXPLAIN ANALYZE

EXPLAIN : 쿼리를 실행하지 않는다. 예상 실행 계획을 제공한다.
EXPLAIN ANALYZE : 쿼리를 실제로 실행한다. 예상 + 실제 실행 결과를 보여준다.

EXPLAIN ANALYZE 사용 시 주의 사항

EXPLAIN ANALYZE는 쿼리를 실제로 실행한다.
대용량 테이브렝서 EXPLAIN ANALYZE를 실행하면 실행 시간이 오래 걸릴 수 있다. 일반 EXPLAIN은 쿼리를 실행하지 않으므로 즉시 결과가 나오지만, EXPLAIN ANALYZE는 실제로 실행해야 하므로 느린 쿼리라면 결과도 느리게 나온다.
EXPLAIN ANALYZE는 계획만 보여주는 도구가 아니라 쿼리를 진짜 실행하고 그 결과를 버리는 도구이다. 운영 DB에 실제로 부하가 걸린다.

운영 환경 대안 - EXPLAIN FORMAT=TREE

운영 환경에서 EXPLAIN ANALYZE 가 부담스러울 때는 EXPLAIN FORMAT=TREE를 사용하면, 쿼리를 실제 실행하지 않고도 트리 형태의 실행 계획을 볼 수 있다.

1. 운영 DB에서 느린 쿼리 분석 : 실제 실행이 부담스러울 때, `EXPLAIN FORMAT=TREE`로 실행 게획의 구조와 예상 비용을 먼저 파악한다.
2. 빠른 확안 : 인덱스를 타는지, 조인 순서는 어떤지 등 실행 계획의 큰 그림을 빠르게 확인할 수 있다.
3. EXPLAIN ANALYZE 전 사전 검토 : 트리 구조를 먼저 확인한 뒤, 필요한 경우에만 `EXPLAIN ANALYZE`로 실제 실행 결과를 확인한다.

EXPLAIN과 EXPLAIN ANALYZE를 함께 사용하는 워크플로

1단계: EXPLAIN으로 방향 잡기 (빠르다, 쿼리 미실행)

EXPLAIN SELECT * FROM product WHERE category_id = 11;

2단계: EXPLAIN ANALYZE로 검증하기 (실제 실행)

EXPLAIN ANALYZE SELECT * FROM product WHERE category_id = 11;

예상 rows와 실제 rows를 비교한다. 차이가 크면 통계 정보가 부정확하거나 옵티마이저가 잘못된 판단을 내릴 수 있다.

EXPLAIN ANALYZE - 실행 통계 1

전체 테이블 스캔 과정

1줄: 실행 동작 ⭢ Table scan on o : 말 그대로 테이블을 처음부터 끝까지 스캔한다는 뜻이다.
2줄: 예상 정보 (옵티마이저 추정) ⭢ (cost=485519 rows=4670000) : cost=485519는 옵티마이저가 추정한 비용이다. 단위는 없고, 다른 실행 계획과 비교하는 상대적 수치이다. 옵티마이저가 내부적으로 사용하는 값이다. rows=4670000는 옵티마이저가 추정한 반환 행 수이다.
3줄: 실제 정보 (실행 후 측정) ⭢ (actual time=0.657..969 rows=5000000 loops=1) : actual time=0.657..969는 실제 소요 시간(ms)이다. 첫 번째 값은 첫 행을 반환하기까지의 시간, 두 번째 값은 마지막 행을 반환하기까지의 시간이다. rows=5000000는 실제 반환된 행 수이다. loops=1는 이 단계가 실행된 횟수이다.

드라이빙 / 드리븐 테이블 쉽게 이해하기

Nested Loop Join은 이중 for문이라고 이해하면 된다.

for row in 드라이빙 테이블: // 바깥 루프, 먼저 읽는 쪽
    for match in 드리븐 테이블: // 안쪽 루프, 매번 찾는 쪽
        결과에 추가

드라이빙(driving, outer) = 기준이 되는 바깥쪽 테이블. 조인을 "주도"한다.
드리븐(driven, inner) = 드라이빙의 각 행에 대해 끌려가며 조회되는 안쪽 테이블
핵심 원칙 : 드라이빙은 작을수록, 드리븐은 인덱스가 있을수록 빠르다.

EXPLAIN ANALYZE - 실행 통계 2

-- [실행 결과] (각 라인 앞의 번호는 실행 순서 = 안쪽부터 바깥쪽 순)
-> Limit: 10 row(s)  (actual time=410..410 rows=10 loops=1)                              [⑧]
    -> Sort: o.total_price DESC, limit input to 10 row(s) per chunk                      [⑦]
       (actual time=410..410 rows=10 loops=1)
        -> Stream results  (cost=54468 rows=5573)                                        [⑥]
           (actual time=0.296..407 rows=60033 loops=1)
            -> Nested loop inner join  (cost=54468 rows=5573)                            [⑤]
               (actual time=0.291..402 rows=60033 loops=1)
                -> Filter: (m.member_id between 2940001 and 2950000)                     [②]
                   (cost=3942 rows=19570) (actual time=0.211..3.01 rows=10000 loops=1)
                    -> Index range scan on m using PRIMARY                               [①]
                       over (2940001 <= member_id <= 2950000)
                       (cost=3942 rows=19570) (actual time=0.209..2.42 rows=10000 loops=1)
                -> Filter: (o.order_status = 'DELIVERED')                                [④]
                   (cost=2.3 rows=0.285) (actual time=0.0149..0.0396 rows=6 loops=10000)
                    -> Index lookup on o using idx_orders_member_id                      [③]
                       (member_id=m.member_id)
                       (cost=2.3 rows=2.85) (actual time=0.0143..0.0388 rows=10 loops=10000)

member 테이블의 PRIMARY 키를 범위 스캔하고 있다.
조건 재평가 : Index range scan임에도 불구하고 필터를 사용하고 있다. MySQL은 인덱스가 가져온 행이 조건에 맞다는 등호 조건은 보장되나 범위 조건은 보장되지 않는다. 이것이 Filter로 표시된다. BETWEEN처럼 구간과 조건이 딱 맞는 경우도 예외 없이 검사한다.
Index lookup on o using idx_orders_member_id
- idx_orders_member_id : 1회 조회.
- loops=10000 회원 수만큼 반복.
- rows=10는 1회 평균 10건을 반환. 즉, 1회 0.0388ms x 10,000 = 약 388ms. 전체 쿼리 시간의 대부분을 차지하는 병목 구간이다.
Filter: (o.order_status = 'DELIVERED')
- rows=6 1회 평균 6건(약 60% 통과율).
- loops=10,000이므로 누적 통과 행 수 = 6 x 10,000 = 60,000건이다.
Nested loop inner join : rows=60033 60,033건 조인 결과. 누적 402ms
Stream results : 조인 결과를 버퍼링없이 상위 노드로 스트리밍 전달. 임시 테이블/버퍼에 쌓지 않고, 한 행이 만들어지는 족족 위로 흘려보내는 파이프라인 역할을 한다.
- Streaming : 1행씩 상위로 바로 전달. 메모리 거의 사용 안 한다.
- Materialization : 전부 쌓았다가 전달. 메모리/디스크 사용한다.
Sort: o.total_price DESC, limit input to 10 row(s) per chunk : 60,033건 전체를 정렬하지 않고 10칸짜리 순위표로 상위 10건만 유지한다.
Limit: 10 row(s) : 최종 10건만 클라이언트로 반환.

명확한 드라이빙 테이블을 선택하라.

member를 먼저 PK 범위 스캔하여 드라이빙 테이블로 삼고 orders를 조회하는 계획을 선택했다.
member_id 범위 조건을 통해 VIP 회원 10,000명만 먼저 뽑아내고, 그 회원들의 주문만 orders에서 조회하는 방식이다.
조인 성능을 높이기 위해 필터링 조건이 명확하게 걸린 member 테이블을 먼저 스캔하는 매우 직관적이고 효율적인 선택이다.

Top-N Sort 최적화 덕분에 정렬은 거의 공짜

limit input to 10 row(s) per chunk은 MySQL이 크기 10짜리 우선순위 힙을 사용해 훑으면서 상위 N건만 유지한다. ORDER BY + LIMIT 패턴은 해당 최적화가 자동으로 적용된다.

EXPLAIN ANALYZE - Top-N 최적화

불필요한 계산을 하지 않는다 - Top-N 힙은 들어온 값이 현재 등수보다 낮다면 비교 한 번으로 끝내고 버린다. 순위조차 매기지 않는다.
메모리가 상수다 - 전체를 버퍼에 넣고 sort_buffer_size를 넘어서면 filesort on disk가 발생해 속도가 수십 ~ 수백 배 느려진다. 이렇게 정렬용 메모리 공간이 부족하여 처리하던 데이터를 디스크의 임시 파일로 넘겨 기록하는 현상을 디스크 스필이라고 한다. Top-N 힙은 항상 딱 N행만 들고 있다. 입력이 1만이든 1억이든 힙 크기는 O(N) 고정이라, 디스크 스필이 구조적으로 발생할 수 없다
비교 횟수가 훨씬 적다 - Top-N 힙은 최악의 경우에도 O(N log K) 복잡도로 기존 전체를 정렬할 경우 O(N log N)보다도 훨씬 효율적이다.

Top-N 힙 정의

Top-N 힙(Top-N Heap)은 ORDER BY ... LIMIT N 쿼리에서 MySQL이 자동으로 쓰는 정렬 최적화 기법이다.
MySQL은 ORDER BY ... LIMIT N 패턴이 보이면 자동으로 이 방식을 시도한다. 단, 가장 결정적인 기준은 순위를 모두 메모리에 담을 수 있는가에 있다.
- LIMIT N의 N이 충분히 작아야 한다 : N이 충분히 작아야 한다. N이 작으면 정렬 버퍼(sort_buffer_size)에 여유롭게 들어간다. 만약 N이 수십만 건 이상으로 너무 크다면 MySQL은 이 최적화를 포기하고 일반 정렬을 수행한다.
- OFFSET이 없거나 작아야한다 : LIMIT 10 OFFSET 1,000,000과 같은 쿼리는 사실상 상위 1,000,010개의 원소를 전부 기억해야 하므로 힙 크기가 너무 커져서 최적화가 쓰이지 못한다.
- 정렬할 레코드의 전체 크기가 정렬 버퍼보다 작아야 한다 : 메모리 안에서 힙 정렬을 완결지어야 하므로 (N + OFFSET) × 레코드의 크기가 정렬 버퍼(sort_buffer_size)를 넘지 않아야 한다.
반대로 LIMIT없이 모든 데이터를 정렬하면 MySQL은 결국 모든 데이터를 정렬해야 하므로 최적화가 원천적으로 쓰이지 못하고 정렬 시간과 메모리 비용이 급증하게 된다.

EXPLAIN ANALYZE - 파이프라인 모델 최적화

각 노드가 상위 노드의 호출에 반응해 한 행씩 데이터를 전달하는 구조를 데이터베이스 아키텍처 공식 용어로 반복자 모델이라고 한다.
부모가 자식에게 다음 행을 달라고 요청하면 자식이 한 행만 만들어서 위로 올린다. 이걸 끝날 때까지 반복한다.

패스스루 vs 블로커 차이점

패스스루(Pass-Through) : 자식이 행을 주면 즉시 가공해 부모에게 전달한다. 파이프라인으로 흐른다.
- ex. Stream results, Filter, Nested loop join, Limit, Index scan
블로커(Pipeline Breaker) : 입력을 모두 소비해야 첫 출력을 낼 수 있다.
- ex. Sort, Temporary table, Materialize, GROUP BY

왜 결과를 모두 모아서 전달하지 않고 하나씩 전달할까?

한꺼번에 쌓아서 넘기는 쪽이 직관적으로 보이나 그렇게 하지 않는 다섯 가지 실용적 이유가 있다.

1. 메모리를 아낀다.

N건을 전부 쌓아두면 그만큼의 중간 버퍼가 필요하다. 한 행씩 흘려보내면 몇 행짜리 공간만 쓰면 된다. 수백만 건 단위 쿼리에서는 이 차이가 임시 디스크 사용으로 이어질 수도 있다.

2. 첫 결과가 빨리 나온다.

사용자가 LIMIT N을 건 쿼리에서 첫 행을 0.3ms 만에 받고 싶다고 가정할 때, 전체를 모아서 넘기는 방식을 사용하면 전체를 다 만든 뒤에야 첫 행이 나가게 된다. 스트림 방식은 첫 행이 만들어지자마자 위로 올라가므로 체감 응답이 훨씬 빠르다.

3. 조기 종료가 가능하다.

LIMIT N처럼 N건만 받으면 끝인 경우 스트림 모델은 N번째 데이터가 나오는 순간 자식에게 더 이상 Read()를 호출하지 않는다. 그 시점에서 자식 루프도 즉시 멈춘다. 자식이 전체를 먼저 다 만들었다면 결국 버려질 건들까지 헛되이 만들어낸 것이 된다.

4. 메모리 한계에 걸리지 않는다.

'모두 모으기' 전략은 결과가 메모리보다 크면 디스크로 쏟아야 한다(=Using temporary on disk). 스트림 방식은 결과의 크기와 무관하게 몇 행짜리 메모리만 있으면 동작하므로 10만 건이든 1억 건이든 동일한 방식으로 돌릴 수 있다.

5. 노드끼리의 결합이 단순해진다.

모든 노드가 똑같은 인터페이스만 지키면 된다. Scan → Filter → Join → Sort → Limit를 어떤 조합으로 쌓든 버퍼 크기 협상과 같은 복잡한 절차가 필요하지 않다. 이 단순성 덕분에 옵티마이저가 수많은 플랜을 자유롭게 조합할 수 있다.

❗'첫 행이 빨리 보인다'는 어디서 보느냐에 따라 다르다.

MySQL Workbench와 같은 데이터베이스 클라이언트 툴은 서버에서 행이 도착하는 대로 즉시 화면에 보여주기 때문에 체감상 첫 행이 정말 빨리 나타나지만 애플리케이션 서버에서는 상황이 다르다. 데이터베이스 드라이버가 기본적으로 쿼리 결과를 전부 다 메모리에 내려받은 뒤에야 애플리케이션 레이어로 넘겨주고, 프레임워크나 ORM 라이브러리가 이를 리스트 등의 자료구조로 변환하는 경우가 많다.

결국 애플리케이션 코드 입장에서는 쿼리 결과 전체가 메모리에 올라와야 비로소 첫 행을 읽을 수 있다. 즉 서버가 스트리밍으로 데이터를 내보내더라도 그 이득이 애플리케이션 서버까지 그대로 전달되진 않는다.

❗애플리케이션 단의 커서 기반 데이터 스트리밍이 좋은 대안이 될까?

데이터베이스 드라이버나 프레임워크가 제공하는 커서 기반 스트리밍 기능을 사용해서 결과를 한 행씩 스트리밍 처리하면 메모리 문제를 기술적으로 해결을 할 순 있지만 이 방식을 거의 사용하지 않는다.

데이터베이스 커넥션 고갈 위험 : 스트리밍을 통해 데이터를 소비하는 동안 데이터베이스 커넥션과 트랜잭션 세션이 소켓을 연 채 계속 유지되어야 한다. 만약 애플리케이션이 데이터를 한 행씩 처리하는 도중에 외부 API를 호출하거나 무거운 비즈니스 로직을 수행하면 커넥션을 장시간 점유하게 된다. 결국 커넥션 풀이 순식간에 고갈되어 시스템 전체가 마비되는 문제가 발생한다.

까다로운 리소스 해제 책임 : 스트리밍 처리가 끝나면 반드시 커서를 닫고 데이터베이스 세션을 명시적으로 정리해야 한다. 만약 개발자 실수로 커서를 정상적으로 닫지 못하면 데이터베이스 서버와 애플리케이션 메모리 양쪽 모두에 커서와 세션이 남아 리소스 누수가 발생한다.

ORM 및 프레임워크 메모리 누수 부하 : 애플리케이션 레이어(객체 - 관계 매핑을 지원하는 ORM 프레임워크 포함)에서 대량의 데이터를 스트리밍으로 한 행씩 읽어 들이더라도 읽어 들인 레코드를 내부 캐시나 메모리에 그대로 적재하는 경우가 많다. 결국 읽어 들이는 데이터 양이 많을수록 메모리 사용량이 점진적으로 증가하며, 서버 메모리가 고갈되는 현상을 마주하게 된다. 이를 피하려면 자동 캐싱 기능을 비활성화하거나 수동으로 메모리를 주기적으로 비워주는 복잡한 처리가 필수적이다.

EXPLAIN ANALYZE - 예상 vs 실제의 차이

인덱스 다이브(Index Dive) 한계(근사치 추정 문제)

MySQL 옵티마이저는 인덱스가 있는 컬럼에 대해 범위 조건이 주어지면 해당 인덱스의 B+Tree를 직접 타고 들어가 해당 범위 안에 대략 몇 건의 데이터가 존재할지를 추정하는 기법을 사용하는데 이걸 인덱스 다이브라고 한다.
옵티마이저는 범위 안의 모든 데이터를 하나하나 전수조사하지 않고 범위의 시작점과 끝점을 빠르게 짚어내 인덱스 페이지의 밀도와 평균 레코드 크기를 활용해 대략적인 건수를 빠르게 추정한다.

통계 정보(Table Statistics)가 오래된 경우

예상치와 실측치 격차가 2~3배 수준을 넘어 수십, 수백 배 이상 차이나면 이는 단순한 근사치 오차로 보기 어렵다. 테이블의 통계 정보가 심하게 왜곡되었거나 너무 오래되었을 가능성이 높다.
MySQL 옵티마이저는 테이블의 전체 레코드 수나 인덱스 카디널리티와 같은 다양한 통계 정보를 나침반 삼아 저렴한 쿼리 실행 경로를 계산한다. 이 통계 정보는 기본적으로 테이블 데이터가 일정 비율(약 10%) 이상 누적 변경되면 백그라운드에서 자동으로 갱신된다.
하지만 대규모 배치 작업으로 수천만 건의 데이터를 일시적으로 밀어 넣거나 삭제한 직후라면 이야기가 달라진다. 자동 갱신이 즉각 일어나지 않아 왜곡된 과거 통계 정보를 보고 옵티마이저가 엉뚱한 실행 계획을 세울 수 있다.

데이터 분포의 편향(히스토그램)

인덱스가 없는 컬럼이거나 설령 인덱스가 존재하더라도 데이터의 특정 값 분포가 한쪽으로 극심하게 쏠리는 경우가 있다.
문제는 옵티마이저의 기본 모델링에 있다. 옵티마이저는 통계 정보의 단순 샘플링에만 의존하기 때문에 기본적으로 모든 데이터는 균등하게 흩어져 분포하고 있다고 가정한다.

의사결정 가이드

오차가 2~3배 이내인 경우

진단 : 통계 정보의 샘플링 추정 또는 인덱스 다이브 보정이 정상 작동해 매우 건강한 상태
대응 : 지극히 정상적이므로 쿼리 튜닝이나 시스템 설정 변경 필요 X

오차가 수십, 수백 배 이상 벌어지고 실행 계획이 꼬인 경우

진단 : 테이블 통계 정보가 너무 오래되어 옵티마이저가 눈이 먼 상태
대응 : ANALYZE TABLE 테이블명을 실행해 통계 정보 강제 갱신

특정 조건의 값에 대해서만 유독 예측 오차가 심한 경우

진단 : 인덱스가 없는 컬럼의 극단적인 데이터 분포 편향으로 인해 옵티마이저의 균등 분포 가정이 무너져 발생한 오류
대응 : 해당 비인덱스 컬럼에 히스토그램을 수동으로 생성해 옵티마이저에게 데이터 밀도 지도를 제공