9장. EXPLAIN 읽는 법 — type·key·rows·filtered

{/* 삽화 자리 — 계기판 여러 개가 붙은 패널. 바늘 하나만 보고 판단하려다 다른 바늘을 놓치는 장면 */}

type=range가 type=ALL보다 51배 느렸습니다. 사다리에서 두 칸이나 위인데요. 바늘 하나만 보면 이런 걸 못 봅니다.

면접 실전 질문: ① EXPLAIN에서 제일 먼저 보는 건 뭔가요? ② type=index는 좋은 건가요? ③ rows는 실제 행 수인가요?

배경 — 손을 놓을 시간

7장과 8장 내내 우리는 FORCE INDEX로 옵티마이저를 묶어놨습니다. 재려는 게 인덱스의 값이었으니 옵티마이저가 중간에 마음을 바꾸면 곤란했거든요.

이제 손을 놓습니다. 그러면 옵티마이저가 무엇을 골랐는지, 왜 골랐는지를 우리가 읽어야 해요. 그 창구가 EXPLAIN입니다.

4장에서 이미 한 번 열어봤습니다. 그때는 비용 숫자를 꺼내려고 optimizer_trace까지 갔죠. 4장이 “옵티마이저가 어떻게 고르는가”였다면, 이 장은 “그 결과를 어떻게 읽는가”입니다. 읽을 줄 알아야 “왜 안 타는지”를 물을 수 있으니까요.

스토리 — 다섯 개의 계기판

한 줄짜리 EXPLAIN은 세로로 보는 게 편합니다.

mysql> EXPLAIN SELECT * FROM scan_demo WHERE transaction_no = '...' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: scan_demo
   partitions: NULL
         type: ref                        ← 어떻게 접근하나
possible_keys: idx_txno                   ← 쓸 수 있었던 후보
          key: idx_txno                   ← 실제로 고른 것
      key_len: 80
          ref: const
         rows: 1                          ← 몇 행일 것 같나 (추정)
     filtered: 100.00                     ← 그중 몇 %가 통과할 것 같나 (추정)
        Extra: Using index condition      ← 나머지 사정

열두 컬럼이 나오지만 실무에서 손이 가는 건 다섯입니다 — type, key, rows, filtered, Extra.

possible_keys와 key의 관계부터 짚고 갑시다. possible_keys는 후보 목록이고 key가 결정입니다. 그리고 둘이 어긋날 수 있어요 — 뒤에 나올 표의 인덱스 스캔 행은 WHERE가 없어서 possible_keys가 비어 있는데 key에는 인덱스가 찍힙니다.

type은 보통 이런 사다리로 소개됩니다.

const  >  eq_ref  >  ref  >  range  >  index  >  ALL
좋음                                            나쁨

• const — PK나 NOT NULL 유니크 인덱스에 등호. 복합 인덱스면 모든 컬럼이 상수와 비교돼야 합니다. 많아야 한 행이라 옵티마이저가 상수 취급해요.
• eq_ref — 조인에서 상대 행마다 정확히 한 행을 찾는 경우. 유니크 인덱스가 있어야 합니다.
• ref — 인덱스에 등호인데 한 행을 보장받지 못하는 경우.
• range — BETWEEN, >, IN 같은 범위.
• index — 인덱스를 처음부터 끝까지 훑음.
• ALL — 테이블을 처음부터 끝까지 훑음. 1장의 그 풀스캔입니다.

eq_ref와 ref의 차이는 표에서 바로 볼 수 있습니다. 우리 transaction_no는 200만 행에 값이 전부 다른데도 ref로 나와요. 인덱스에 UNIQUE가 안 붙어 있으면 옵티마이저는 “한 행”을 보장받지 못하기 때문입니다.

사다리 자체는 유용합니다. 다만 이게 비용 순서를 보장하지는 않아요. 아래 표의 마지막 네 줄이 그걸 보여줍니다.

핵심 — 계기판에 실제 값을 붙여봤습니다

200만 행 테이블에서 일곱 가지 질의를 던지고, EXPLAIN이 뭐라고 하는지와 실제로 몇 페이지를 만졌는지를 나란히 놨습니다.

✅ 실측 (MySQL 8.0.46 / Docker / Apple M1 / macOS, 2026-08. 200만 행, 버퍼풀 1GB로 전부 상주, 적응형 해시 인덱스 켬(기본값). 페이지 요청은 Innodb_buffer_pool_read_requests 델타이고, 상태 변수를 읽는 쿼리 자체도 요청을 만들어 해상도가 ±1회쯤 됩니다 — const의 2회는 그 바로 위 값이에요. const는 최적화 단계에서 읽혀 EXPLAIN ANALYZE가 시간을 안 내줍니다. 가운데 두 인덱스 스캔은 같은 SELECT COUNT(*)를 FORCE INDEX만 바꿔 돌린 겁니다. 재현: ./ch09/typeladder.sh. 전체 소스: docs/book/code/db-index/ch09/typeladder.sh)

위쪽 셋은 사다리대로입니다

const 2회, ref 13회, range(100건) 313회. 사다리 순서와 비용 순서가 맞습니다.

ref의 13회는 앞 장들의 숫자로 그대로 분해됩니다.

조회 문장 하나가 쓰는 값        10회   (3장)
그중 클러스터드로 왕복하는 몫     3회   (7장, 적응형 해시 인덱스 켠 값 3.02)
                              ────
                               13회

3장에서 “10회 중 7회가 뭔지 못 갈랐다”고 남긴 그 값과, 7장에서 잰 왕복이 여기서 만납니다.

그런데 아래쪽에서 사다리가 뒤집힙니다

네 줄만 다시 보세요.

사다리에서 두 칸 위인 range가 ALL보다 요청은 164배, 시간은 51배 나쁩니다.

원인은 이 책이 이미 다 설명해뒀습니다. 200만 행을 세컨더리로 훑으면 행마다 클러스터드로 왕복해야 하고(7장), 그 왕복이 행당 3회니까요. 실제로 6,067,769 ÷ 200만 = 행당 3.03입니다. 7장에서 잰 3.02와 소수 둘째 자리까지 같아요.

인덱스를 탄다는 게 항상 이득은 아닙니다. 좁힐 게 없는데 타면, 좁히는 값만 내고 이득은 못 얻습니다.

같은 type인데도 6배 갈립니다

가운데 두 줄은 같은 SELECT COUNT(*)를 FORCE INDEX만 바꿔 돌린 겁니다. 둘 다 type=index고요.

idx_txno  훑기 : 226,476 회   159 ms     ← 리프 4,247 페이지
PRIMARY   훑기 :  36,885 회    35.6 ms   ← 리프 31,747 페이지

더 작은 구조를 훑는 쪽이 6배 더 비쌉니다. 리프 페이지당 요청 수로 보면 53.3회 대 1.16회예요.

원인은 다 밝히지 못했습니다. 실마리는 하나 있습니다 — 200만 행을 226,476회로 나누면 요청 하나당 8.8행입니다. InnoDB가 행을 묶음으로 가져오는 단위와 비슷한 값이에요.

그런데 같은 논리를 클러스터드에 대보면 안 맞습니다. 페이지당 63행인데 요청은 페이지당 1.16회뿐이거든요. 반쪽만 설명되는 실마리입니다. 소스에서 더 파보려면 storage/innobase/row/row0sel.cc의 행 페치 캐시가 출발점입니다.

진짜 구분자는 Extra입니다

표에서 type이 같은데 값이 갈린 자리마다, 실제로 다른 건 Extra였습니다.

Using index가 붙어 있으면 7장의 왕복이 없습니다. 그게 표에서 range 6,067,769와 index 226,476을 가른 결정적 차이예요. type보다 Extra를 먼저 보는 게 나은 경우가 많습니다.

rows는 추정이고, 자주 틀립니다

풀스캔 줄마다 rows가 1,917,639로 찍혀 있습니다. 실제는 2,000,000이에요.

4장에서 본 그 숫자입니다 — 통계가 페이지 샘플링으로 추정한 값이라 실제보다 4.1% 낮습니다. 그리고 “넓은 범위”는 실제 200,001행인데 423,506으로, 역시 4장에서 본 대로 약 두 배로 잡혔고요.

rows는 옵티마이저가 믿은 값이지 사실이 아닙니다. 실제는 EXPLAIN ANALYZE가 나란히 보여줍니다.

-> Index lookup on scan_demo using idx_txno (transaction_no='...')
   (cost=0.35 rows=1) (actual time=0.0205..0.0217 rows=1 loops=1)
        ↑ 추정            ↑ 첫 행까지  ↑ 마지막 행까지  ↑ 실제  ↑ 반복 횟수

actual time의 두 숫자는 실행 시간의 범위가 아닙니다. 첫 행이 나오기까지와 마지막 행이 나오기까지를 밀리초로 적은 거예요. rows는 반복 1회당 평균 실제 행 수고, loops는 이 노드가 몇 번 돌았는지입니다.

인덱스를 걸었는데 안 탄다면 여기부터 봐야 합니다.

filtered가 100이 아닐 때

표에서는 filtered가 전부 100입니다. 인덱스로 좁혀 읽었으니 읽은 게 곧 통과할 것이라서요.

filtered를 가르는 건 “풀스캔이냐”가 아니라 접근 방식이 미처 못 걸러낸 조건이 남아 있느냐입니다. 그래서 rows × filtered가 옵티마이저가 예상한 결과 건수예요.

인덱스가 없는 컬럼으로 조건을 걸면 이렇게 나옵니다.

EXPLAIN SELECT * FROM scan_demo WHERE payload = 'nope';
-- type=ALL  key=NULL  rows=1917639  filtered=10.00
 
EXPLAIN SELECT * FROM scan_demo WHERE status = 'DONE';
-- type=ALL  key=NULL  rows=1917639  filtered=10.00

payload는 값이 200만 행 모두 같고, status도 값이 몇 종류뿐입니다. 분포가 정반대인데 filtered가 둘 다 정확히 10.00입니다.

우연일 리가 없죠. 이 10이 어디서 왔고, 그것 때문에 무슨 일이 벌어지는지가 10장입니다.

정리

• EXPLAIN에서 손이 가는 계기판은 다섯입니다 — type, key, rows, filtered, Extra. possible_keys는 후보 목록이고 key가 결정이에요.
• type 사다리(const > eq_ref > ref > range > index > ALL)는 유용하지만 비용 순서를 보장하지 않습니다.
• 실측: 위쪽 셋은 사다리대로였습니다(2 → 13 → 313회). ref의 13회는 3장의 문장당 10회 + 7장의 왕복 3회로 그대로 분해됩니다.
• 그런데 range가 ALL보다 요청 164배, 시간 51배 나빴습니다. 좁힐 게 없는데 인덱스를 타면 좁히는 값만 내고 이득은 못 얻습니다.
• 같은 type=index인데 요청이 226,476과 36,885로 6배 갈렸습니다. 원인은 반쪽만 설명했어요.
• type이 같을 때 실제로 갈라놓는 건 Extra입니다. Using index가 있으면 왕복이 없습니다.
• rows와 filtered는 추정입니다. 실제는 EXPLAIN ANALYZE가 나란히 보여줍니다.

생각해볼 질문

1. ref의 13회를 3장의 10회 + 7장의 왕복 3회로 갈랐습니다. 그런데 3장은 그 10회 중 7회가 뭔지 못 밝혔죠. const가 2회뿐인 건 그 7회를 안 내기 때문일까요, 아니면 다른 이유일까요?
2. type=index가 ALL보다 나은 경우와 나쁜 경우를 각각 하나씩 들어본다면요? (표에 둘 다 있습니다)
3. 분포가 정반대인 두 컬럼의 filtered가 똑같이 10.00이었습니다. 이 값은 무엇을 보고 정해졌을까요?

이 장은 계기판을 읽었습니다. 다음 장은 그 계기판 하나가 아무것도 안 보고 대답하는 경우입니다.

▶ 10장. filtered=10이라는 상수 — 통계가 없을 때