MySQL EXPLAIN ANALYZE – 테오의 저장소

EXPLAIN ANALYZE란? — EXPLAIN과 무엇이 다른가

MySQL 8.0.18에서 추가된 EXPLAIN ANALYZE는 쿼리를 실제로 실행한 뒤, 각 노드별 실행 시간(actual time)·실제 처리 행 수(actual rows)·반복 횟수(loops)를 트리 형태로 보여주는 진단 도구입니다. 기존 EXPLAIN이 옵티마이저의 추정치만 보여주는 것과 달리, EXPLAIN ANALYZE는 실측 데이터를 제공합니다.

핵심 차이를 표로 정리하면 다음과 같습니다.

항목	EXPLAIN	EXPLAIN ANALYZE
쿼리 실행	실행하지 않음 (추정만)	실제 실행 후 결과 반환
rows 값	옵티마이저 추정 행 수	실제 처리된 행 수 (actual rows)
시간 정보	없음	노드별 actual time (ms)
출력 형식	테이블 / JSON / TREE	TREE 형식 고정
부작용	없음	SELECT 외 DML은 실제 변경 발생 (주의!)

주의: EXPLAIN ANALYZE는 쿼리를 실제로 실행하므로, UPDATE/DELETE 문에 사용하면 데이터가 변경됩니다. 운영 DB에서는 반드시 SELECT 쿼리에만 사용하거나 트랜잭션으로 감싸서 ROLLBACK하세요.

EXPLAIN ANALYZE 출력 읽는 법

기본 사용법은 단순합니다.

EXPLAIN ANALYZE
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= '2026-01-01';

출력은 다음과 같은 트리 구조입니다.

-> Nested loop inner join  (cost=1245 rows=3200)
     (actual time=0.089..12.450 rows=3150 loops=1)
    -> Index range scan on o using idx_created_at
         over ['2026-01-01' <= created_at]  (cost=580 rows=3200)
         (actual time=0.045..4.230 rows=3150 loops=1)
    -> Single-row index lookup on c using PRIMARY (id=o.customer_id)
         (cost=0.25 rows=1)
         (actual time=0.002..0.002 rows=1 loops=3150)

각 노드의 괄호 안에 들어있는 숫자를 해석해 봅시다.

cost: 옵티마이저가 추정한 비용 (상대 단위)
rows: 옵티마이저 추정 행 수
actual time=A..B: 첫 번째 행을 반환하기까지 A ms, 마지막 행까지 B ms
actual rows: 해당 노드가 실제로 반환한 행 수
loops: 이 노드가 반복 실행된 횟수 (Nested Loop Join에서 내부 테이블 접근 시 중요)

실제 총 처리 비용을 계산하려면 actual time × loops를 곱해야 합니다. 위 예시에서 customers 테이블 조회의 실제 총 시간은 0.002ms × 3150 = 약 6.3ms입니다.

실무 패턴 ①: 추정 행 수 vs 실제 행 수 괴리 — 통계 갱신이 필요한 신호

EXPLAIN ANALYZE의 가장 강력한 용도는 옵티마이저 추정치와 실측치의 괴리를 발견하는 것입니다.

-> Index range scan on payments using idx_status
     (cost=120 rows=500)
     (actual time=0.030..45.200 rows=48000 loops=1)

추정 rows=500인데 실제 rows=48,000이면, 옵티마이저가 잘못된 판단을 내렸다는 의미입니다. 원인은 대부분 통계 정보가 오래되었기 때문입니다.

ANALYZE TABLE payments;

InnoDB는 기본적으로 innodb_stats_auto_recalc=ON으로 테이블의 10% 이상 변경 시 자동 갱신하지만, 특정 컬럼의 분포가 크게 바뀐 직후에는 수동 ANALYZE TABLE이 필요할 수 있습니다.

실무 패턴 ②: Nested Loop Join의 loops 폭발 진단

Nested Loop Join에서 내부 테이블 접근의 loops 값이 비정상적으로 높으면, 조인 순서나 인덱스를 재검토해야 합니다.

-> Nested loop inner join  (actual time=0.1..850.0 rows=200 loops=1)
    -> Table scan on A  (actual time=0.05..2.0 rows=50000 loops=1)
    -> Single-row index lookup on B using idx_fk
         (actual time=0.015..0.015 rows=0.004 loops=50000)

B 테이블을 50,000번 조회하지만 actual rows=0.004(평균)이므로, A에서 대부분의 행이 조인 매칭에 실패하고 있습니다. 이 경우:

A 테이블에 WHERE 필터를 먼저 걸어 driving table 행 수를 줄이거나
STRAIGHT_JOIN 힌트로 조인 순서를 강제하여 더 작은 테이블을 driving으로 만드는 것을 검토합니다

실무 패턴 ③: actual time으로 병목 노드 특정하기

복잡한 쿼리에서 어떤 노드가 가장 오래 걸리는지 찾는 것이 핵심입니다. 트리의 각 노드에서 actual time의 두 번째 값(마지막 행 반환 시점)을 비교하면 됩니다.

-> Sort: o.created_at DESC  (actual time=120.5..120.8 rows=100 loops=1)
    -> Hash join  (actual time=85.2..110.3 rows=9500 loops=1)
        -> Table scan on temp_result  (actual time=0.01..5.2 rows=9500 loops=1)
        -> Hash
            -> Index range scan on products ...
                 (actual time=0.5..78.0 rows=40000 loops=1)

이 예시에서 products 테이블의 Index range scan이 78ms로 전체 120ms 중 대부분을 차지합니다. 이 노드의 인덱스 최적화가 가장 효과적입니다.

실무 패턴 ④: filesort와 temporary table 비용 확인

MySQL 8.0.18+의 EXPLAIN ANALYZE는 filesort, temporary table 사용 여부도 트리에 명시합니다.

-> Sort: created_at DESC  (actual time=250.0..250.5 rows=1000 loops=1)
    -> Stream results  (actual time=0.1..180.0 rows=150000 loops=1)
        -> ...

150,000행을 스트리밍한 뒤 정렬하는 데 70ms(250-180)가 추가로 소요되었습니다. 이 정도면 인덱스로 정렬을 대체할 수 있는지 검토할 가치가 있습니다. ORDER BY 컬럼이 이미 인덱스에 포함되어 있다면 filesort를 제거할 수 있습니다.

운영 환경에서 안전하게 사용하는 팁

SELECT만 사용: DML에 EXPLAIN ANALYZE를 걸면 실제로 실행됩니다. INSERT/UPDATE/DELETE는 반드시 BEGIN ... ROLLBACK 안에서 실행하세요.
타임아웃 설정: 느린 쿼리를 진단하려다 더 느려질 수 있습니다. SET SESSION max_execution_time = 30000; (30초)로 안전장치를 두세요.
읽기 복제본 활용: 운영 마스터 대신 읽기 복제본에서 실행하면 부하 영향을 최소화할 수 있습니다.
결과 저장: 튜닝 전후 EXPLAIN ANALYZE 결과를 저장해서 비교하면, 어떤 변경이 실제로 효과가 있었는지 정량적으로 판단할 수 있습니다.

EXPLAIN FORMAT=TREE와의 관계

EXPLAIN ANALYZE의 출력은 EXPLAIN FORMAT=TREE의 상위 호환입니다. FORMAT=TREE는 옵티마이저 추정치만 트리로 보여주고, ANALYZE는 여기에 실측 데이터를 추가합니다. 쿼리를 실행하지 않고 실행 계획만 빠르게 보고 싶다면 EXPLAIN FORMAT=TREE를, 실측 기반 진단이 필요하면 EXPLAIN ANALYZE를 사용하세요.

정리: EXPLAIN ANALYZE를 쓰는 판단 기준

쓰세요: 슬로우 쿼리 원인 특정, 인덱스 추가/변경 전후 효과 측정, 옵티마이저 추정치가 의심될 때
쓰지 마세요: 단순 실행 계획 확인(EXPLAIN이면 충분), DML 문(롤백 없이), 운영 마스터에서 부하가 우려될 때

MySQL 8.0.18 이상이라면 EXPLAIN ANALYZE는 느린 쿼리 진단의 첫 번째 도구가 되어야 합니다. 추정이 아닌 실측으로 병목을 찾으세요.