[MySQL] EXPLAIN 실행계획 읽는 법: type, Extra, rows 그리고 EXPLAIN ANALYZE

[MySQL] EXPLAIN 실행계획 읽는 법: type, Extra, rows 그리고 EXPLAIN ANALYZE

쿼리가 느릴 때 인덱스를 "감"으로 더하는 건 도박이에요. MySQL이 그 쿼리를 실제로 어떻게 처리하는지 보여주는 도구가 있는데 안 쓸 이유가 없죠. EXPLAIN이에요. 다만 출력 컬럼이 10개가 넘어서, 어디부터 봐야 하는지 모르면 외계어처럼 보여요.

실전에서 보는 순서 그대로 — type(접근 방식), key(어떤 인덱스), rows(얼마나 읽나), Extra(추가 작업) — 를 따라가고, 추정이 아닌 실측을 주는 EXPLAIN ANALYZE까지 짚어요. 인덱스 원리·설계 편의 검증 도구예요.

01. 읽는 순서: type → key → rows → Extra

EXPLAIN SELECT * FROM orders WHERE user_id = 7 AND status = 'PAID';

출력에서 실전 가치가 큰 건 네 컬럼이에요. type(테이블에 어떻게 접근하나), key(실제로 고른 인덱스), rows(읽을 행 수 추정), Extra(정렬·임시테이블 같은 추가 작업). 이 순서로 보면 5초 안에 "이 쿼리가 왜 느린가"의 후보가 잡혀요.

02. type, 접근 방식의 등급표

• const — PK·유니크 인덱스 동등 조회. 최대 1건이라 최상이에요.
• ref — 일반 인덱스 동등 조회. 양호해요.
• range — 인덱스 범위 스캔(BETWEEN, >, IN 등). 범위가 좁으면 좋아요.
• index — 인덱스 풀 스캔. 이름 때문에 좋아 보이지만, 인덱스를 처음부터 끝까지 읽는 거예요. 주의 신호예요.
• ALL — 테이블 풀 스캔. 데이터가 많은 테이블이면 빨간불이에요.

목표는 const/ref/range예요. index·ALL이 뜨면 앞 편의 체크리스트(왼쪽 접두 위반·컬럼 가공·형변환)부터 의심해요.

다만 ALL이 항상 죄는 아니에요. 수십 건짜리 코드 테이블은 풀 스캔이 인덱스보다 싸고, 옵티마이저도 일부러 ALL을 골라요. "ALL을 무조건 없애기"가 아니라 "rows가 큰 ALL"을 잡는 거예요. type은 rows와 함께 읽어야 판단이 돼요.

03. key와 possible_keys, 무엇을 골랐나

possible_keys는 후보 인덱스들, key는 옵티마이저가 실제 고른 인덱스예요. 두 케이스가 문제예요.

• key가 NULL — 쓸 인덱스가 없는 거예요. 인덱스 추가나 쿼리 수정 대상이에요.
• 기대한 인덱스가 아닌 다른 걸 골랐어요 — 옵티마이저가 통계를 보고 "그 인덱스는 별로"라고 판단한 거예요. 보통은 옵티마이저가 맞아요(카디널리티가 낮다거나). 정말 통계가 낡아 잘못 고르는 경우엔 ANALYZE TABLE로 통계를 갱신하는 게 힌트 강제(FORCE INDEX)보다 먼저예요.

04. rows와 filtered, 얼마나 읽고 얼마나 남나

rows는 "이 단계에서 읽을 행 수"의 추정치예요. 쿼리 비용의 체감 지표라, type이 좋아도 rows가 수백만이면 느려요. filtered는 읽은 것 중 조건을 통과하는 비율(%)이에요. rows가 큰데 filtered가 작으면 "많이 읽고 대부분 버린다"는 뜻 — 인덱스가 조건을 못 걸러주고 있다는 신호예요.

조인 쿼리면 행마다 rows가 나오는데, 대략 곱한 만큼이 전체 작업량이에요. 첫 테이블에서 rows를 최대한 줄이는 게 조인 튜닝의 출발인 이유예요.

05. Extra, 좋은 신호 하나와 나쁜 신호 둘

• Using index ✅ — 커버링 인덱스예요. 테이블 접근 없이 인덱스만으로 끝났다는 뜻이에요.
• Using filesort ⚠️ — 인덱스 정렬을 못 써서 읽은 결과를 별도로 정렬했어요. ORDER BY가 인덱스와 안 맞는 거예요. 정렬 대상이 크면 디스크 정렬까지 가요.
• Using temporary ⚠️ — GROUP BY·DISTINCT 처리를 위해 임시 테이블을 만들었어요. 역시 데이터가 크면 부담이에요.

filesort·temporary도 소량 데이터면 문제없어요. "rows가 큰데 filesort"가 잡아야 할 조합이에요. 해법은 보통 정렬·그룹 컬럼을 인덱스 정렬과 맞추는 것((user_id, created_at)처럼)이고요.

06. EXPLAIN ANALYZE, 추정에서 실측으로

EXPLAIN의 rows는 통계 기반 추정이에요. 통계가 낡으면 계획과 실제가 어긋나요. "계획상 ref인데 왜 느리지?"가 그 증상이에요. MySQL 8.0.18부터는 실측 도구가 있어요.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 7 ORDER BY created_at DESC LIMIT 20;
-- -> Limit: 20 row(s)  (actual time=0.04..0.21 rows=20)
--   -> Index lookup on orders using idx_user_created (user_id=7)
--      (actual time=0.03..0.18 rows=20)

실제로 실행한 뒤 단계별 actual time과 actual rows를 보여줘요. 추정 rows와 실제 rows의 차이가 크면 통계 갱신이나 설계 재검토의 증거가 돼요. 어느 단계에서 시간이 새는지도 바로 보이고요. 단 진짜 실행이니, 운영에서 무거운 쿼리에 함부로 돌리면 안 돼요.

07. 자주 만나는 문제

type=ALL인데 어디부터 봐야 하나요

① WHERE 조건 컬럼에 인덱스가 있는가 ② 있다면 왼쪽 접두·가공·형변환 위반이 없는가 ③ rows가 작으면(소형 테이블) 그냥 둬도 되는가 순서예요.

같은 쿼리가 어떤 날은 빠르고 어떤 날은 느려요

파라미터에 따라 옵티마이저가 다른 계획을 고르는 경우예요. 값별 분포가 극단적으로 다르면(특정 user가 전체의 절반) 생겨요. EXPLAIN을 느린 파라미터로 떠보는 게 핵심이에요 — 빠른 값으로 떠보면 문제가 안 보여요.

LIMIT이 있는데도 느려요

filesort + LIMIT 조합을 의심해요. 정렬을 인덱스로 못 풀면 전체를 정렬한 뒤에야 20건을 자르거든요. 정렬 컬럼을 인덱스에 맞추면 "앞에서 20건만 읽고 끝"이 돼요.

정리

EXPLAIN은 type → key → rows → Extra 순서로 읽어요. type은 const/ref/range가 목표이고, key가 NULL이면 인덱스 부재, rows는 작업량의 체감치, Extra의 filesort·temporary는 rows가 클 때 잡아야 할 신호예요. 추정이 미덥지 않으면 EXPLAIN ANALYZE로 실제 시간과 행 수까지 실측하고요. 인덱스 설계와 이 검증 도구는 한 세트로 굴려야 제값을 해요.

여기까지가 인덱스 삼부작이에요. 이제 결이 달라져요. 다음 편부터는 트랜잭션의 세계 — 격리수준과 MVCC로 들어갑니다. 검증 도구는 인덱스 원리·설계 편과 늘 같이 봐요.

출처: MySQL — EXPLAIN Output Format · MySQL — EXPLAIN ANALYZE