Real MySQL 8.0 - 10장. 실행 계획

9장이 "옵티마이저가 어떻게 길을 고르는가"였다면, 10장은 **"옵티마이저가 고른 길을 어떻게 들여다보는가"**다. 실행 계획(EXPLAIN)은 내비게이션이 보여주는 경로 미리보기다. 출발하기 전에 "고속도로 탈 건지, 국도로 돌아갈 건지"를 보여준다. 느린 쿼리를 만났을 때 제일 먼저 하는 일이 이걸 보는 것이다.

10장은 사실상 EXPLAIN 출력 칼럼 사전이다. 처음부터 끝까지 외우는 장이 아니라, 실행 계획에서 모르는 값을 만났을 때 찾아보는 장이다. 그래서 이 정리도 "자주 보는 값" 위주로 추렸다.

# 10.1 통계 정보

옵티마이저는 비용 기반이라 통계 정보가 계획의 재료다. 8.0부터는 인덱스 통계에 더해 칼럼 값의 분포를 담은 히스토그램까지 쓴다.

# 테이블·인덱스 통계

5.6부터 InnoDB 통계는 mysql.innodb_table_stats, mysql.innodb_index_stats 테이블에 영구 저장된다(innodb_stats_persistent=ON, 기본값). 옛날엔 메모리에만 있어서 서버를 재시작하면 통계가 바뀌고 실행 계획도 바뀌는 문제가 있었다.

통계는 테이블 전체를 읽지 않고 일부 페이지만 샘플링해서 만든다. 그래서 다음 경우에 자동으로 다시 수집되는데, 운영 중에 갑자기 실행 계획이 바뀌는 원인이 되기도 한다.

테이블이 새로 열릴 때, 레코드가 대량(1/16)으로 바뀌었을 때, ANALYZE TABLE을 실행했을 때 등

innodb_stats_auto_recalc로 자동 재수집을 끌 수 있고, 샘플링할 페이지 수(innodb_stats_persistent_sample_pages)를 늘리면 더 정확해지는 대신 수집이 느려진다.

# 히스토그램 (8.0)

기존 통계는 "인덱스 칼럼의 유니크 값 개수"뿐이었다. 레코드 1,000건에 user_id 유니크 값이 100개면 "user_id=?는 10건쯤 나오겠지" 하고 균등하게 가정했다. 하지만 실제 데이터는 균등하지 않다. 주문을 1,000건 한 사용자도 있고 0건인 사용자도 있다.

히스토그램은 칼럼 값을 구간(버킷)으로 나눠 각 구간에 레코드가 얼마나 있는지 기록한다.

ANALYZE TABLE employees UPDATE HISTOGRAM ON gender, hire_date;
ANALYZE TABLE employees DROP HISTOGRAM ON gender, hire_date;

싱글톤(Singleton): 값마다 버킷 하나. 유니크 값이 적은 칼럼(성별, 상태 코드)용.
높이 균형(Equi-Height): 버킷마다 레코드 수가 비슷하게 구간을 나눈다. 값이 많은 칼럼용.

책의 예시에서 first_name='Zita' AND birth_date BETWEEN '1950...' AND '1960...'의 비율을 히스토그램 없이는 11%로 예측했는데, 히스토그램이 있으니 61%로 예측했다(실제 64%). 이 차이가 조인 순서를 바꾼다.

핵심 포인트 두 가지.

히스토그램은 자동으로 수집되지 않는다. 필요한 칼럼에 대해 직접 ANALYZE TABLE ... UPDATE HISTOGRAM을 해야 한다.
인덱스가 있는 칼럼은 히스토그램을 거의 안 쓴다. 인덱스가 있으면 옵티마이저가 인덱스를 직접 살짝 뒤져서(인덱스 다이브, Index dive) 실제 건수를 세는 게 더 정확하기 때문이다. 그러니 히스토그램은 인덱스가 없는 칼럼의 조건이 조인 순서에 영향을 줄 때 쓸모 있다.

🐘 PostgreSQL 비교 — PG는 히스토그램이 기본이다. ANALYZE(또는 백그라운드의 autovacuum이 알아서 돌리는 ANALYZE)가 모든 칼럼에 대해 "가장 흔한 값 목록 + 그 비율(MCV, Most Common Values)"과 "나머지 값의 히스토그램"을 수집해서 pg_stats 뷰에 저장한다. MySQL처럼 칼럼을 골라서 수동으로 만들 필요가 없다. 대신 자동 수집이 데이터 변화를 못 따라가면 PG도 똑같이 엉뚱한 계획을 세운다. 대량 적재 직후 ANALYZE를 한 번 돌려주는 습관은 둘 다 필요하다.

# 코스트 모델(Cost Model)

옵티마이저가 비용을 계산할 때 쓰는 단위 작업별 가격표다. mysql.server_cost, mysql.engine_cost 테이블에 있다.

작업	기본 비용
디스크에서 데이터 페이지 읽기	1.00
메모리(버퍼 풀)에서 페이지 읽기	0.25
디스크 임시 테이블 생성	20.00
메모리 임시 테이블 생성	1.00
인덱스 키 비교	0.05
레코드 비교(평가)	0.10

바꿀 수는 있지만 바꾸지 않는 게 좋다. 어떤 값을 바꾸면 어떤 계획이 바뀔지 예측하기 어렵다. "디스크 읽기가 메모리 읽기의 4배, 디스크 임시 테이블은 20배 비싸다" 정도의 감만 가져가면 된다.

# 10.2 실행 계획 확인

# 출력 포맷

EXPLAIN SELECT ...;                 -- 테이블 포맷 (기본)
EXPLAIN FORMAT=TREE SELECT ...;     -- 트리 포맷
EXPLAIN FORMAT=JSON SELECT ...;     -- JSON 포맷

보여주는 내용은 거의 같다. 테이블 포맷이 제일 흔하고, 조인 순서가 헷갈릴 땐 TREE 포맷이 명확하다.

# EXPLAIN ANALYZE — 진짜로 실행해 본다 (8.0.18)

EXPLAIN은 예측만 보여준다. EXPLAIN ANALYZE는 쿼리를 실제로 실행하고 단계별 실제 소요 시간과 처리 건수를 TREE 포맷으로 보여준다.

-> Nested loop inner join  (cost=685.24 rows=135) (actual time=0.367..3.602 rows=48 loops=1)
    -> 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)
    -> 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)

읽는 순서: 들여쓰기가 깊은 줄부터, 같은 깊이면 위에서부터 실행된다.
actual time=0.007..0.009: 첫 레코드까지 걸린 평균 시간 .. 마지막 레코드까지 걸린 평균 시간 (ms)
rows=10: 한 번 실행에 평균 몇 건
loops=233: 이 단계가 몇 번 반복됐는지. 위 예시는 e 테이블에서 233건을 읽어서 s 테이블 조회가 233번 반복됐다는 뜻이다.

예측(cost, rows)과 실제(actual, rows)가 크게 다르면 통계 정보가 틀렸다는 신호다.

운영 DB에서 EXPLAIN ANALYZE 조심

진짜로 실행한다. 10분 걸리는 쿼리면 10분 걸리고, 그동안 운영 DB에 부하를 준다. 먼저 EXPLAIN으로 대충 보고, 튜닝 후 확인용으로 쓰자.

🐘 PostgreSQL 비교 — EXPLAIN ANALYZE는 PG가 원조다. 아주 오래전부터 있었고, MySQL 8.0.18이 비슷한 형태를 가져온 것이다. PG에선 EXPLAIN (ANALYZE, BUFFERS)처럼 BUFFERS를 붙이면 각 단계가 **버퍼 캐시에서 몇 페이지 읽었고(hit) 디스크에서 몇 페이지 읽었는지(read)**까지 보여줘서, "메모리에서 끝났나, 디스크까지 갔나"를 바로 알 수 있다. 읽는 법(안쪽부터, loops 곱하기)은 MySQL과 똑같다. 그리고 PG는 EXPLAIN ANALYZE UPDATE도 실제로 수정하니 BEGIN; ... ROLLBACK;으로 감싸야 한다.

# 10.3 실행 계획 분석 — 칼럼별로

+----+-------------+-------+------+---------------+-----+---------+-----+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+-----+---------+-----+------+----------+-------+

위에서 아래 순서가 대체로 실행 순서(먼저 접근하는 테이블이 위)다. 아래에 칼럼별로 자주 보는 값만 정리했다. 실무에서 진짜 보는 건 type, key, rows, Extra 네 개다.

# id

SELECT 단위(서브쿼리 포함)마다 붙는 번호다. 조인하는 테이블들은 같은 id를 가진다. 주의할 건 id 숫자가 접근 순서를 의미하지는 않는다는 것. 순서가 헷갈리면 FORMAT=TREE로 본다.

# select_type — 이 SELECT가 어떤 종류인가

값	의미
`SIMPLE`	UNION·서브쿼리 없는 단순 SELECT (조인 포함). 제일 흔하다
`PRIMARY`	UNION이나 서브쿼리가 있을 때 가장 바깥 쿼리
`UNION`	UNION의 두 번째 이후 SELECT
`SUBQUERY`	FROM절 이외에서 쓰인 서브쿼리
`DERIVED`	FROM절 서브쿼리. 임시 테이블(파생 테이블)을 만든다 — 가능하면 조인으로 풀어 쓰자
`DEPENDENT SUBQUERY`	바깥 쿼리 값을 참조하는 서브쿼리. 바깥 레코드마다 반복 실행될 수 있어서 느린 경우가 많다
`MATERIALIZED`	서브쿼리를 임시 테이블로 구체화 (9장의 세미 조인 Materialization)
`LATERAL DERIVED`	8.0.14의 LATERAL JOIN — FROM절 서브쿼리가 바깥 칼럼을 참조

DEPENDENT가 붙은 줄이나 DERIVED가 보이면 한 번 더 들여다본다.

# table

접근하는 테이블(별칭)이다. <derived2>, <union1,2>, <subquery2>처럼 꺾쇠가 있으면 임시 테이블이고, 숫자는 그걸 만든 id다.

# type — 테이블을 어떻게 읽는가 ⭐

실행 계획에서 가장 중요한 칼럼이다. 아래로 갈수록 느리다.

type	의미	판단
`system`	레코드가 0~1건인 테이블 (MyISAM/MEMORY)
`const`	PK나 유니크 키로 딱 1건 조회. 옵티마이저가 상수로 바꿔버림	최고
`eq_ref`	조인에서 드리븐 테이블을 PK/유니크 키로 1건씩 조회	매우 좋음
`ref`	인덱스 동등 비교(`=`). 여러 건 나올 수 있음	좋음
`fulltext`	전문 검색 인덱스
`ref_or_null`	`ref` + `IS NULL` 비교
`unique_subquery` / `index_subquery`	`IN (서브쿼리)`를 인덱스로 처리
`range`	인덱스 범위 검색 (`<`, `>`, `BETWEEN`, `IN`, `LIKE 'abc%'`)	보통 OK
`index_merge`	인덱스 여러 개를 합쳐 씀 (9장 인덱스 머지). 생각보다 효율이 좋지 않은 경우가 많다	애매
`index`	인덱스 풀 스캔. 이름 때문에 좋아 보이지만 인덱스를 처음부터 끝까지 다 읽는다	주의
`ALL`	테이블 풀 스캔	경고

index는 "인덱스를 잘 탔다"는 뜻이 아니다. 흔한 오해다. 테이블 대신 크기가 작은 인덱스를 풀 스캔한 것일 뿐이다. range/const/ref가 진짜로 인덱스를 효율적으로 쓴 것이다.
ALL이 무조건 나쁜 것도 아니다. 작은 테이블이거나, 어차피 대부분을 읽어야 하는 쿼리면 풀 스캔이 맞다(9장의 리드 어헤드 덕분에 생각보다 빠르다). 문제는 **큰 테이블에서 몇 건만 필요한데 ALL**인 경우다.

🐘 PostgreSQL 비교 — PG는 type 칼럼 대신 노드 이름으로 접근 방식을 보여준다. 대응시키면 이렇다: ALL ≈ Seq Scan, ref/range/const ≈ Index Scan, 커버링 인덱스 ≈ Index Only Scan. 그리고 PG에만 있는 **Bitmap Heap Scan**이 있는데, 인덱스로 찾은 레코드 위치를 먼저 전부 모아 디스크 순서대로 정렬한 뒤 테이블을 읽는 방식이다. 9장의 MRR과 같은 아이디어(랜덤 I/O를 순차 I/O에 가깝게)고, 인덱스 여러 개를 AND/OR로 합치는 것도 이걸로 한다(MySQL의 index_merge와 비슷하다).

# possible_keys / key / key_len

possible_keys: 후보 인덱스 목록. 무시해도 된다. 그냥 후보일 뿐이다.
key: 실제로 사용한 인덱스. NULL이면 인덱스를 안 썼다. 의도한 인덱스가 여기 찍혀 있는지 확인한다.
key_len: 다중 칼럼 인덱스에서 앞에서부터 몇 바이트(몇 칼럼)까지 썼는지. 예를 들어 (dept_no CHAR(4), emp_no INT) 인덱스에서 key_len=16이면 dept_no(utf8mb4라 4글자×4바이트=16)까지만 썼다는 뜻이고, 20이면 emp_no(INT 4바이트)까지 다 쓴 것이다. 다중 칼럼 인덱스의 뒤 칼럼까지 제대로 타는지 확인할 때 쓴다. NULL 허용 칼럼이면 1바이트가 더 붙는다.

# ref

type이 ref/eq_ref일 때 무엇과 비교했는지. const면 상수, employees.e.emp_no면 다른 테이블 칼럼이다. func가 찍히면 값에 변환(형 변환·함수)이 들어간 것이니, 조인 칼럼 타입이 서로 다른지 한 번 확인해 보자.

# rows / filtered

rows: 이 단계에서 읽어야 할 것으로 예측한 레코드 수. 반환 건수가 아니라 읽을 건수다. 실제와 다를 수 있다(통계 기반 예측이니까).
filtered: 읽은 rows 중 WHERE 조건을 통과해 남을 것으로 예측한 비율(%). rows=233, filtered=23.2면 약 54건이 다음 단계로 넘어간다고 본 것이다. 조인할 때 이 값이 다음 테이블을 몇 번 조회할지 결정하니, 조인 순서 결정에 중요하다. 9장의 컨디션 팬아웃과 히스토그램이 이 값을 더 정확하게 만든다.

# Extra — 결정적인 힌트는 여기 있다 ⭐

이름은 "기타"지만 성능에 결정적인 정보가 다 여기 나온다. 수십 가지가 있지만 이것만 알면 된다.

🟢 좋은 신호

Extra	의미
`Using index`	커버링 인덱스. 필요한 칼럼이 인덱스에 다 있어서 테이블을 아예 안 읽었다. 최고의 신호
`Using index condition`	인덱스 컨디션 푸시다운(ICP)으로 인덱스 단계에서 걸러냈다
`Using index for group-by`	루스 인덱스 스캔으로 GROUP BY 처리
`Using index for skip scan`	인덱스 스킵 스캔

🔴 경고 신호

Extra	의미
`Using filesort`	인덱스로 정렬 못 하고 소트 버퍼에서 별도 정렬. 레코드가 많으면 느리다
`Using temporary`	임시 테이블 사용 (GROUP BY, DISTINCT, 정렬 등). 디스크로 넘어가면 더 느리다
`Using join buffer (hash join)` 등	드리븐 테이블에 조인에 쓸 인덱스가 없다. 조인 칼럼 인덱스를 확인하자
`Range checked for each record`	레코드마다 인덱스를 쓸지 말지 다시 판단한다. 조인 조건이 변수라 미리 못 정한 것 — 보통 느리다

🟡 알아두면 좋은 것

Using where: 스토리지 엔진에서 가져온 레코드를 MySQL 엔진이 한 번 더 걸렀다. 가장 흔하게 보이는 값이라 그 자체로는 문제가 아니다. 다만 rows는 큰데 최종 결과는 적다면, 필터링을 인덱스 단계에서 못 하고 많이 읽어서 버리고 있다는 뜻이다.
Impossible WHERE / No matching min/max row / Impossible WHERE noticed after reading const tables: WHERE 조건이 항상 거짓이라 실행도 안 했다. 결과가 0건이면 이것 때문일 수 있다.
Select tables optimized away: MIN()/MAX()를 인덱스 끝 레코드 하나 읽는 걸로 끝냈다. 매우 빠르다.
Using MRR, FirstMatch(e), LooseScan, Start temporary/End temporary: 9장의 최적화들이 적용됐다는 표시.
Not exists: LEFT JOIN ... WHERE b.id IS NULL(안티 조인)을 효율적으로 처리했다.
Distinct, Using union(...)/Using intersect(...)/Using sort_union(...): 각각 DISTINCT 최적화, 인덱스 머지.

커버링 인덱스가 왜 그렇게 좋나

8장에서 봤듯 세컨더리 인덱스로 레코드를 찾으면, 인덱스 → PK → 실제 레코드로 한 번 더 찾아가야 한다(랜덤 I/O). 레코드가 1,000건이면 1,000번의 랜덤 I/O다. Using index는 이 과정을 통째로 건너뛰었다는 뜻이다. 그래서 SELECT * 대신 필요한 칼럼만 SELECT하고, 자주 쓰는 쿼리의 칼럼을 인덱스에 포함시키면 극적으로 빨라질 때가 있다.

🐘 PostgreSQL 비교 — PG에는 Extra 칼럼이 없고, 같은 정보가 실행 계획 트리의 노드로 나온다. Using filesort ≈ Sort 노드(EXPLAIN ANALYZE면 메모리에서 끝났는지 Sort Method로 바로 보인다), Using temporary로 하던 GROUP BY ≈ HashAggregate 노드, Using where ≈ 노드 아래의 Filter: 줄(ANALYZE에선 Rows Removed by Filter: 9800처럼 몇 건을 읽고 버렸는지 숫자로 나와서 더 친절하다). 커버링 인덱스는 Index Only Scan인데 PG에는 한 가지 함정이 있다. PG는 인덱스에 "이 레코드가 지금 트랜잭션에서 보이는지"(MVCC 가시성) 정보가 없어서, 테이블 페이지가 최근 VACUUM으로 "전부 보임" 표시가 돼 있어야만 진짜로 테이블을 안 읽는다. Heap Fetches: 0이면 제대로 된 것이고, 숫자가 크면 VACUUM이 밀려 있다는 뜻이다.

# 정리

10장에서 실무로 들고 갈 것.

느린 쿼리는 EXPLAIN부터. 추측하지 말고 실행 계획을 본다.
네 칼럼만 먼저 본다: type → key → rows → Extra.
- type: const/eq_ref/ref/range면 대체로 OK, index와 ALL은 의심 (단, 작은 테이블의 ALL은 괜찮다)
- key: 의도한 인덱스를 탔나, NULL은 아닌가
- rows: 예상 읽기 건수가 결과 건수보다 터무니없이 크지 않나
- Extra: Using filesort, Using temporary, Using join buffer는 경고, Using index는 좋은 신호
type=index는 "인덱스 잘 탔음"이 아니라 "인덱스 풀 스캔"이다.
select_type에 DEPENDENT나 DERIVED가 보이면 서브쿼리를 조인으로 풀 수 있는지 본다.
예측이 이상하면 EXPLAIN ANALYZE로 실제와 비교한다. 예측 rows와 실제 rows가 크게 다르면 통계 문제 → ANALYZE TABLE, 필요하면 인덱스 없는 칼럼에 히스토그램.
순서가 헷갈리면 EXPLAIN FORMAT=TREE. 안쪽(깊은 들여쓰기)부터 실행된다.

반대로 지금 깊이 안 파도 되는 것 — 코스트 모델 값 조정, 통계 수집 관련 변수들, select_type/type/Extra의 드문 값들(UNCACHEABLE, fulltext, Range checked..., Full scan on NULL key 등), JSON 포맷 세부 구조. 실행 계획에서 처음 보는 값이 나오면 그때 이 장을 찾아보면 된다.

← Real MySQL 8.0 - 9장. 옵티마이저와 힌트 Swift - 기초 문법 정리 →