Ch10-2 실행 계획 확인

한 줄 정의

EXPLAIN 으로 옵티마이저가 수립한 실행 계획을 미리 확인하고, EXPLAIN ANALYZE 로 실제 실행한 뒤 단계별 소요 시간까지 측정합니다. 출력은 테이블·TREE·JSON 세 가지 포맷으로 볼 수 있습니다.

쉽게 말하면

EXPLAIN 은 내비게이션의 예상 경로 안내 입니다. 출발 전에 “이 길로 가면 몇 분 걸릴 것 같다”를 보여줍니다.

EXPLAIN ANALYZE 는 실제로 운전해 보고 적은 주행 기록 입니다. 구간마다 진짜로 몇 초 걸렸는지, 몇 번 돌았는지를 알려줍니다. 그래서 예상(cost)과 실제(actual time)의 차이를 비교할 수 있습니다.

왜 중요한가?

옵티마이저가 틀린 길을 골랐는지 진단하려면 그 결정을 눈으로 봐야 합니다.

• EXPLAIN: 쿼리를 실행하지 않고 계획만 본다 → 느린 쿼리도 즉시 확인
• EXPLAIN ANALYZE: 쿼리를 실제로 실행 하고 측정한다 → 예측이 실제와 얼마나 어긋났는지 확인

이 둘을 구분해 쓰는 것이 튜닝의 출발점입니다.

핵심 내용

10.2.1 실행 계획 출력 포맷

실행 계획은 DESC 또는 EXPLAIN 으로 확인합니다. 8.0부터 기존의 EXPLAIN EXTENDED / EXPLAIN PARTITIONS 옵션은 문법에서 제거 되고 모든 정보가 통합 출력됩니다. 대신 FORMAT 옵션으로 표시 방법을 고릅니다.

테이블 포맷 (기본)

EXPLAIN
SELECT * FROM employees e
  INNER JOIN salaries s ON s.emp_no=e.emp_no
WHERE first_name='ABC';

id	select_type	table	type	key	key_len	ref	rows	filtered
1	SIMPLE	e	ref	ix_firstname	58	const	1	100.00
1	SIMPLE	s	ref	PRIMARY	4	employees.e.emp_no	10	100.00

12개 칼럼(id ~ Extra)의 의미는 Ch10-3 실행 계획 분석에서 다룹니다.

TREE 포맷

EXPLAIN FORMAT=TREE SELECT ... \G
-- -> Nested loop inner join  (cost=2.40 rows=10)
--     -> Index lookup on e using ix_firstname (first_name='ABC')  (cost=0.35 rows=1)
--     -> Index lookup on s using PRIMARY (emp_no=e.emp_no)  (cost=2.05 rows=10)

들여쓰기로 호출 순서와 단계 구조 를 보여주고, 각 단계에 예측 비용(cost)과 예측 행 수(rows)가 붙습니다.

JSON 포맷

EXPLAIN FORMAT=JSON SELECT ... \G

query_block, cost_info(query_cost), nested_loop, access_type, used_key_parts 등 가장 상세한 정보 를 구조화해 보여줍니다.

포맷마다 표시 정보와 선호도 차이는 있지만, 실행 계획의 큰 흐름 을 파악하는 데는 차이가 없습니다. 책은 가독성을 위해 테이블 포맷을 기준으로 설명합니다.

10.2.2 쿼리의 실행 시간 확인 — EXPLAIN ANALYZE

8.0.18 버전부터 추가됐습니다. EXPLAIN 과 달리 쿼리를 실제로 실행 하고, 사용된 실행 계획과 단계별 소요 시간 을 함께 보여줍니다. SHOW PROFILE 도 시간을 보여주지만 실행 계획 단계별로는 보여주지 못합니다. EXPLAIN ANALYZE 는 항상 TREE 포맷 으로만 출력되어 FORMAT 옵션을 쓸 수 없습니다.

EXPLAIN ANALYZE
SELECT e.emp_no, avg(s.salary)
FROM employees e
  INNER JOIN salaries s ON s.emp_no=e.emp_no
    AND s.salary>50000 AND s.from_date<='1990-01-01' AND s.to_date>'1990-01-01'
WHERE e.first_name='Matt'
GROUP BY e.hire_date \G

A) -> Table scan on <temporary>                (actual time=0.001..0.004 rows=48 loops=1)
B)   -> Aggregate using temporary table        (actual time=3.799..3.808 rows=48 loops=1)
C)     -> Nested loop inner join     (cost=685.24 rows=135) (actual time=0.367..3.602 rows=48 loops=1)
D)       -> Index lookup on e using ix_firstname (first_name='Matt')
                                     (cost=215.08 rows=233) (actual time=0.348..1.046 rows=233 loops=1)
E)       -> Filter: ((s.salary>50000) and ...)  (cost=0.98 rows=1) (actual time=0.009..0.011 rows=0 loops=233)
F)         -> Index lookup on s using PRIMARY (emp_no=e.emp_no)
                                     (cost=0.98 rows=10) (actual time=0.007..0.009 rows=10 loops=233)

TREE 포맷의 실행 순서 읽는 법

들여쓰기는 호출 순서 를 의미하며, 실제 실행 순서 는 두 규칙으로 읽습니다.

• 들여쓰기가 같은 레벨 에서는 위쪽 라인이 먼저 실행
• 들여쓰기가 다른 레벨 에서는 가장 안쪽 라인이 먼저 실행

위 예제의 실제 실행 순서는 D → F → E → C → B → A 입니다.

1. D) employees의 ix_firstname 으로 first_name='Matt' 레코드를 찾고
2. F) salaries의 PRIMARY로 그 emp_no 에 맞는 레코드를 찾아
3. E) salary·from_date·to_date 조건에 맞는 것만 걸러
4. C) 1·3 결과를 조인하고
5. B) 임시 테이블에 담으며 GROUP BY 집계하고
6. A) 임시 테이블을 읽어 결과 반환

actual time / rows / loops

필드	의미
actual time=0.007..0.009	첫 숫자 = 첫 레코드 를 가져오는 평균 시간(ms), 둘째 숫자 = 마지막 레코드 까지의 평균 시간(ms)
rows=10	한 번 반복에서 처리한 평균 레코드 건수
loops=233	이 단계가 반복된 횟수 (바깥 테이블이 233건이라 233번 반복)

actual time 과 rows 가 “평균”인 이유는 loops 가 1보다 크기 때문입니다. salaries 검색을 233번 반복했고, 매번 첫 레코드까지 평균 0.007ms, 10건을 모두 가져오는 데 평균 0.009ms가 걸렸다는 뜻입니다.

EXPLAIN ANALYZE 는 쿼리를 끝까지 실제로 실행 하므로, 아주 느린 쿼리는 완료돼야 결과가 나옵니다. 실행 계획이 나쁜 쿼리는 먼저 EXPLAIN 으로 계획만 확인해 어느 정도 튜닝한 뒤 EXPLAIN ANALYZE 를 돌리는 것이 좋습니다.

정리

EXPLAIN vs EXPLAIN ANALYZE

구분	EXPLAIN	EXPLAIN ANALYZE
쿼리 실행	안 함 (계획만)	실제 실행
보여주는 값	예측 (cost, rows)	예측 + 실측 (actual time, loops)
출력 포맷	테이블 / TREE / JSON	TREE 고정
느린 쿼리	즉시 확인 가능	완료돼야 확인
도입	기존	8.0.18+

세 가지 출력 포맷

포맷	특징
테이블 (기본)	12개 칼럼 한눈에, 가독성 높음
TREE	단계 구조·호출 순서·예측 비용
JSON	가장 상세, 구조화

내 생각

• 예측과 실측의 갭이 곧 튜닝 포인트입니다. EXPLAIN 의 rows 와 EXPLAIN ANALYZE 의 actual rows 가 크게 어긋난다면, 그건 통계가 거짓말을 하고 있다는 신호입니다. 옵티마이저가 “1건”이라 예측한 곳에서 실제로 수만 건이 나왔다면 조인 순서가 통째로 잘못 짜였을 수 있습니다.

• loops 를 보는 습관이 N+1 같은 반복 비용을 드러냅니다. actual time 자체는 작아 보여도 loops 가 수천이면 누적 시간은 폭발합니다. 안쪽 단계의 actual time × loops 가 실제 체감 비용이라는 감각으로 읽어야 합니다.

• 느린 쿼리에 바로 EXPLAIN ANALYZE 를 거는 건 함정입니다. 그건 그 느린 쿼리를 한 번 더 끝까지 돌리는 일입니다. 계획이 의심스러우면 EXPLAIN 으로 먼저 구조를 보고, 가설을 세워 고친 뒤에야 실측으로 검증하는 순서가 안전합니다.

관련 개념

• Ch10-3 실행 계획 분석 — 테이블 포맷 12개 칼럼의 상세 의미
• Ch10-1 통계 정보 — cost·rows 예측값의 출처
• Ch09-1 옵티마이저의 역할 — 이 실행 계획을 만들어내는 주체

출처

• Real MySQL 8.0 (1권), 10.2 실행 계획 확인