한 줄 정의
EXPLAIN테이블 포맷의 12개 칼럼(id~Extra)을 읽어, 옵티마이저가 어떤 순서로(id·table) 어떤 타입의 쿼리를(select_type) 어떤 접근 방법으로(type) 어떤 인덱스를 얼마나(key·key_len) 사용해 몇 건을 읽고 거르는지(rows·filtered), 그리고 어떤 내부 최적화가 일어나는지(Extra) 해석하는 작업입니다.
쉽게 말하면
실행 계획 분석은 건강검진 결과지를 읽는 일 과 같습니다.
type은 혈압 같은 핵심 지표 — 인덱스를 제대로 타는지 한눈에 보여줍니다key/key_len은 어떤 약을 얼마나 썼는지 — 의도한 인덱스를 의도한 만큼 쓰는지rows/filtered는 검사 추정치 — 통계 기반 예측이라 실제와 다를 수 있습니다Extra는 의사 소견란 — 칼럼 이름과 달리 성능의 진짜 단서가 여기 있습니다
왜 중요한가?
칼럼을 오해하면 엉뚱한 곳을 튜닝합니다. 자주 헷갈리는 함정만 미리 못박아 둡니다.
possible_keys에 인덱스가 나열돼도 그 인덱스를 쓰는 건 아닙니다 (후보일 뿐)type=index는 “인덱스를 잘 쓴다”가 아니라 인덱스 풀 스캔 입니다 (느림)Extra=Using index는 커버링 인덱스 (빠름) —type=index와 정반대 개념rows는 결과 건수가 아니라 읽고 비교해야 할 건수 의 예측치
핵심 내용
실행 계획 읽는 큰 그림
EXPLAIN 은 쿼리에 사용된 테이블(임시 테이블 포함) 수만큼 라인을 출력합니다. 위에서 아래로 읽되, id가 작을수록 바깥(Outer)·먼저 접근, 클수록 안쪽(Inner)·나중 접근입니다. 단 순서가 헷갈릴 때는 EXPLAIN FORMAT=TREE 로 확인하는 게 정확합니다.
10.3.1 id 칼럼
단위 SELECT 쿼리 마다 부여되는 식별자입니다.
- 한 SELECT 안에서 여러 테이블을 조인 하면 → 같은 id (id가 증가하지 않음)
- SELECT가 여러 개(서브쿼리·UNION 등)면 → 각기 다른 id
- 주의: id는 테이블 접근 순서를 의미하지 않습니다. 순서는 TREE 포맷으로 확인
10.3.2 select_type 칼럼
각 단위 쿼리의 타입입니다.
| 값 | 의미 |
|---|---|
SIMPLE | UNION·서브쿼리 없는 단순 쿼리(조인 포함). 실행 계획에 단 하나, 보통 가장 바깥 |
PRIMARY | UNION·서브쿼리를 가진 쿼리의 가장 바깥 단위 쿼리. 단 하나 |
UNION | UNION으로 묶인 단위 쿼리 중 두 번째 이후 (첫 번째는 DERIVED) |
DEPENDENT UNION | UNION인데 외부 쿼리 값에 의존 |
UNION RESULT | UNION 결과를 버퍼링하는 임시 테이블. id 없음 (UNION ALL은 8.0부터 임시 테이블 안 씀 → 이 라인 사라짐) |
SUBQUERY | FROM 절 이외 에서 쓰인 서브쿼리 |
DEPENDENT SUBQUERY | 서브쿼리가 외부 칼럼에 의존 → 외부 먼저 실행돼야 해 느림 |
DERIVED | FROM 절 서브쿼리(파생 테이블). 임시 테이블 생성 |
DEPENDENT DERIVED | 래터럴 조인(LATERAL) — FROM 서브쿼리가 외부 칼럼 참조 (8.0+) |
UNCACHEABLE SUBQUERY | 서브쿼리 결과를 캐시 못 함 (사용자 변수, NOT DETERMINISTIC 루틴, UUID()/RAND() 등) |
UNCACHEABLE UNION | UNION + UNCACHEABLE |
MATERIALIZED | IN(subquery)·FROM 서브쿼리를 임시 테이블로 구체화(5.6+). table에 <subqueryN> 표시 |
튜닝 시
DERIVED부터 확인 하는 습관을 권장합니다. 조인으로 풀 수 있는 서브쿼리는 조인으로 바꾸는 것이 정석입니다.
DEPENDENT의 두 종류 (캐시 차이)
SUBQUERY: 외부에 의존하지 않아 처음 한 번만 실행·캐시DEPENDENT SUBQUERY: 외부 칼럼 값 단위로 캐시 → 반복 실행, 느림
10.3.3 table 칼럼
테이블 기준으로 출력되며, 별칭이 있으면 별칭이 표시됩니다.
- FROM 없는 쿼리 →
NULL <derivedN>: id N 단위 쿼리로 만든 파생(임시) 테이블<unionM,N>: id M·N 결과를 UNION한 임시 테이블<subqueryN>: MATERIALIZED 임시 테이블
id·select_type·table 세 칼럼이 테이블 실행 순서를 판단하는 근거 입니다. (<derived2> 가 위 라인이면 그것이 드라이빙 테이블)
10.3.4 partitions 칼럼
쿼리가 실제로 접근하는 파티션 목록입니다. 옵티마이저가 불필요한 파티션을 제외하는 것을 파티션 프루닝(Partition Pruning) 이라고 합니다. (8.0부터 EXPLAIN PARTITIONS 없이 기본 표시)
흥미로운 점: 파티션 일부만 읽어도
type은ALL로 표시될 수 있습니다. 파티션은 물리적으로 별도 저장 공간이라, 선택된 파티션만 풀 스캔 하기 때문입니다.
10.3.5 type 칼럼 (접근 방법)
각 테이블의 접근 방법(Access type) 입니다. “조인 타입”이라 불리지만 조인과 직접 연결하지 말고 접근 방법으로 해석합니다. 반드시 체크해야 할 가장 중요한 칼럼. 아래는 빠른 순서 이며, ALL 만 인덱스를 안 씁니다. index_merge 를 빼면 한 라인은 인덱스 하나만 씁니다.
| type | 의미 | 비고 |
|---|---|---|
system | 레코드 0·1건 테이블 | MyISAM·MEMORY만 (InnoDB엔 없음) |
const | PK/유니크 키 전체 동등 비교, 반드시 1건 | 유니크 인덱스 스캔 |
eq_ref | 조인에서 앞 테이블 값으로 뒤 테이블을 PK/유니크로 검색, 1건 보장 | 다중 칼럼이면 전부 비교돼야 함 |
ref | 동등 조건 검색, 인덱스 종류·1건 보장 무관 | 매우 빠름 |
fulltext | 전문 검색 인덱스(MATCH ... AGAINST) | const/eq_ref/ref가 아니면 우선 선택. 단 실측상 range가 더 빠를 때 많음 |
ref_or_null | ref + IS NULL 비교 | |
unique_subquery | IN(subquery) 에서 서브쿼리가 중복 없는 유니크 값 반환 | |
index_subquery | IN(subquery) 에서 중복을 인덱스로 제거 | |
range | 인덱스 레인지 스캔 (<,>,BETWEEN,IN,LIKE 등) | 가장 흔하고 충분히 빠름 |
index_merge | 2개 이상 인덱스 결과를 병합 | 책은 효율 문제로 range 아래로 순위 내림 |
index | 인덱스 풀 스캔 (≠ 효율적!) | 풀 테이블 스캔보다는 빠름. 정렬·커버링에 유리 |
ALL | 풀 테이블 스캔 | 최후의 수단. 리드 어헤드로 보완 |
꼭 기억할 점
const·eq_ref·ref는 모두 동등 비교(=,<=>) 기반의 좋은 접근 방법 → 튜닝 시 넘겨도 무방- “인덱스 레인지 스캔”이라 하면 보통
const·ref·range세 가지를 묶어 지칭 index와ALL은 작업 범위를 좁히지 못해 OLTP 웹 서비스엔 부적합ALL이라고 무조건 나쁜 건 아닙니다. DW·배치처럼 대량 처리에선 리드 어헤드(인접 페이지를 한 번에 최대 64개씩 미리 읽기)로 억지 인덱스보다 나을 수 있음
10.3.6 possible_keys 칼럼
옵티마이저가 후보로 고려한 인덱스 목록일 뿐입니다. 테이블의 모든 인덱스가 나오는 경우가 흔하므로 튜닝에 거의 도움 안 됨 → 무시해도 됩니다. 여기 나왔다고 그 인덱스를 쓰는 게 아닙니다.
10.3.7 key 칼럼
실제 선택된 인덱스입니다. 튜닝 시 의도한 인덱스가 여기 떴는지 확인하는 게 핵심. PK는 항상 PRIMARY. index_merge 면 여러 인덱스가 , 로, 인덱스를 못 쓰면 NULL.
10.3.8 key_len 칼럼
다중 칼럼 인덱스에서 몇 개 칼럼(정확히는 몇 바이트)까지 사용했는지 알려주는, 의외로 매우 중요한 칼럼입니다.
- 예: PK가
(dept_no CHAR(4) utf8mb4, emp_no INT)일 때dept_no만 조건 →key_len=16(4문자 × 4바이트, utf8mb4는 문자당 4바이트 고정 계산)dept_no+emp_no조건 →key_len=20(16 + INT 4바이트)
- NULL 허용 칼럼은 +1바이트:
DATE(3바이트) NULLABLE →key_len=4
10.3.9 ref 칼럼
ref 접근에서 비교에 쓴 값입니다. 상수면 const, 다른 테이블 칼럼이면 테이블.칼럼. 신경 안 써도 되지만 func 는 주의 — 값을 그대로 안 쓰고 콜레이션 변환·연산을 거쳐 참조했다는 뜻입니다. 문자집합 불일치, 숫자↔문자 조인 등에서 발생하므로 조인 칼럼 타입을 일치 시키는 게 좋습니다.
10.3.10 rows 칼럼
옵티마이저가 통계 기반으로 예측한, 읽고 비교해야 할 레코드 건수(반환 건수 아님)입니다. 어디까지나 예측이라 실제와 다를 수 있습니다.
- 예:
from_date>='1985-01-01'→ rows=331,143(전체) → 풀 스캔 선택 - 예:
from_date>='2002-07-01'→ rows=292(8.8%) →range선택
인덱스 안 된 칼럼·비대칭 분포에서 예측이 빗나가므로 8.0에서 히스토그램 이 도입됐습니다.
10.3.11 filtered 칼럼
인덱스를 못 쓰는 조건까지 적용한 뒤 남는 레코드의 비율(%) 입니다. (버려지는 비율이 아니라 남는 비율)
조인에서 결정적입니다. rows × filtered 가 실제 조인에 넘어가는 건수이고, 옵티마이저는 이 값이 작은 테이블을 드라이빙(선행) 테이블 로 고릅니다.
- 예: employees
rows=233, filtered=16.03%→ 조인 넘김 ≈ 37건 (233 × 0.1603) - 순서를 뒤집어 salaries 선행 시
rows=3314, filtered=11.11%→ ≈ 368건
이 예측 정확도가 곧 조인 성능이므로 8.0의 히스토그램 이 여기서도 핵심입니다.
10.3.12 Extra 칼럼
이름과 달리 성능의 진짜 단서 가 여기 있습니다. 보통 2~3개가 함께 표시되며, 순서 자체엔 의미가 없습니다. 31개 항목을 성격별로 묶어 정리합니다.
(A) 성능 핵심 — 반드시 해석할 메시지
| 메시지 | 의미 | 좋고 나쁨 |
|---|---|---|
Using index | 커버링 인덱스 — 데이터 파일 안 읽고 인덱스만으로 처리 | 매우 좋음 (type=index와 정반대!) |
Using where | MySQL 엔진 레이어에서 체크 조건 필터링 수행 | 가장 흔함. filtered로 영향 판단 |
Using filesort | ORDER BY를 인덱스로 못 해 별도 정렬(소트 버퍼) | 부하 큼 → 튜닝 대상 |
Using temporary | 중간 결과용 임시 테이블 사용 | 부하 큼. 메모리/디스크는 실행 계획만으론 모름 |
Using index condition | 인덱스 컨디션 푸시다운(ICP) 최적화 | 좋음 |
Using index for group-by | GROUP BY를 루스 인덱스 스캔 으로 처리 | 좋음 |
Using index for skip scan | 인덱스 스킵 스캔(8.0+) | 좋음 |
Using join buffer (...) | 드리븐 테이블에 인덱스가 없어 조인 버퍼 사용 | 주의 — Block Nested Loop/Batched Key Access/hash join 표기 |
Using MRR | Multi Range Read — 키를 모아 정렬해 디스크 접근 최소화 | 좋음 |
Using union/sort_union/intersect (...) | index_merge 의 병합 방식 (OR=union, AND=intersect, 대량 range OR=sort_union) |
Using index(커버링 인덱스)의 핵심
데이터 파일을 안 읽으므로 수십~수백 배 빠를 수 있습니다. InnoDB는 세컨더리 인덱스에 PK가 함께 저장되므로, SELECT pk, indexed_col 형태가 자연스럽게 커버링되는 경우가 많습니다. 단 욕심내 인덱스에 칼럼을 과하게 넣으면 인덱스가 커져 쓰기·메모리에 독이 됩니다. type 과 무관하게(index 풀 스캔이어도) 적용될 수 있습니다.
Using index for group-by의 두 갈래
- 타이트 인덱스 스캔:
AVG/SUM/COUNT처럼 인덱스를 다 읽어야 하면 듬성듬성 못 읽음 → 이 메시지 안 나옴 - 루스 인덱스 스캔: 그루핑 칼럼만 보거나
MIN/MAX처럼 그룹별 양 끝만 읽으면 됨 → 듬성듬성 읽음 → 메시지 표시 - WHERE 인덱스와 GROUP BY 인덱스가 같아야 루스 스캔 가능. 보통 옵티마이저는 WHERE 인덱스를 우선. 검색 건수가 적으면 루스 스캔을 안 써도 빠르므로 손익을 따져 결정
Using join buffer의 알고리즘 표기
드리븐 테이블에 인덱스가 없을 때 블록 네스티드 루프/해시 조인이 조인 버퍼를 씁니다. join_buffer_size 로 크기 조절(웹 서비스는 1MB로 충분, DW는 크게). 8.0의 해시 조인 도 조인 버퍼를 사용하며, 카테시안 조인은 항상 조인 버퍼를 씁니다.
(B) 세미 조인 최적화 전략 표기
| 메시지 | 전략 |
|---|---|
FirstMatch(table) | 기준 테이블 기준 첫 일치 1건 만 검색 |
LooseScan | 루스 스캔 세미 조인 |
Start temporary, End temporary | Duplicate Weed-out — 임시 테이블로 중복 제거 (시작~끝 테이블 표시) |
(C) 진단성 메시지 — “데이터/대상이 없다”
이들은 쿼리 문법 오류가 아니라 실행 계획 기초 자료가 없다 는 뜻입니다. 다만 비즈니스적으로 의도한 쿼리인지 점검할 신호입니다.
| 메시지 | 상황 |
|---|---|
Impossible WHERE | WHERE가 항상 FALSE (예: NOT NULL 칼럼에 IS NULL) |
Impossible HAVING | HAVING 만족 레코드 없음 |
No matching min/max row | MIN()/MAX() 조건 만족 레코드 0건 → NULL 반환 |
const row not found | const 접근인데 테이블에 레코드 0건 |
no matching row in const table | const 조인인데 일치 레코드 없음 |
No matching rows after partition pruning | UPDATE/DELETE 대상 파티션 자체가 없음 (레코드 없음과 다름) |
unique row not found | 유니크 칼럼 아우터 조인인데 일치 없음 |
(D) 기타 동작 표기
| 메시지 | 의미 |
|---|---|
Distinct | DISTINCT 처리 위해 필요한 것만 조인·읽음 |
Full scan on NULL key | col IN (subquery) 에서 col이 NULL이면 차선책으로 서브쿼리 테이블 풀 스캔. col IS NOT NULL 추가로 회피 |
No tables used | FROM 없거나 FROM DUAL |
Not exists | 아우터 조인으로 안티 조인 구현 시, 일치 1건만 확인하고 종료 |
Plan isn't ready yet | EXPLAIN FOR CONNECTION 시 대상이 아직 실행 계획 미수립 |
Range checked for each record (index map: N) | 조인 조건이 변수라 레코드마다 range/ALL을 매번 결정. index map은 후보 인덱스 비트맵(16진수) |
Recursive | CTE 재귀 쿼리(WITH RECURSIVE). 재귀일 때만 표시 |
Rematerialize | 래터럴 조인에서 선행 레코드마다 임시 테이블 재생성 |
Select tables optimized away | WHERE 없는 MIN()/MAX() 를 인덱스 양 끝 1건만 읽어 처리 / MyISAM의 COUNT(*) |
Deleting all rows | 핸들러 차원 전체 삭제(MyISAM 등). 8.0에선 표시 안 됨, TRUNCATE TABLE 권장 |
Zero limit | LIMIT 0 — 레코드 안 읽고 메타데이터만 반환 |
정리
튜닝 시 보는 순서 (체크리스트)
flowchart TD A["1. type 확인<br/>ALL/index면 인덱스 점검"] --> B["2. key 확인<br/>의도한 인덱스인가"] B --> C["3. key_len 확인<br/>인덱스 칼럼 충분히 쓰나"] C --> D["4. rows·filtered 확인<br/>조인 선행 테이블 적절한가"] D --> E["5. Extra 확인<br/>filesort/temporary 있나"] E --> F["6. select_type 확인<br/>DERIVED 조인으로 풀 수 있나"]
가장 자주 헷갈리는 3쌍
| 비교 | A | B |
|---|---|---|
type=index vs Extra=Using index | 인덱스 풀 스캔 (느림) | 커버링 인덱스 (빠름) |
possible_keys vs key | 후보 목록 (무시 가능) | 실제 선택 (중요) |
rows vs 결과 건수 | 읽고 비교할 예측치 | 실제 반환 건수 (다를 수 있음) |
내 생각
-
EXPLAIN을 볼 때 시선이
type → key → Extra로 흐르도록 몸에 익혀야 합니다. 칼럼이 12개라 압도되기 쉽지만, 90%의 진단은 이 셋에서 끝납니다.type이ALL/index인데 큰 테이블이면 경보,Extra에Using filesort; Using temporary가 같이 뜨면 즉시 의심합니다. -
filtered와 히스토그램은 한 몸으로 기억합니다. 10.1에서 본 “0.13초 vs 1.29초” 조인 사고의 정체가 바로 이filtered예측 실패였습니다. 인덱스 없는 칼럼이 조인·필터에 끼면filtered가 막연한 값이 되고, 그게 곧 드라이빙 테이블 오선택으로 이어집니다. -
진단성 메시지(C 그룹)는 “오류”가 아니라 “데이터가 없다”는 신호 임을 분명히 합니다.
Impossible WHERE,No matching min/max row를 보고 쿼리가 틀렸다고 착각하기 쉽지만, 문법은 멀쩡하고 단지 조건에 맞는 데이터가 없을 뿐입니다. 다만 “왜 없지?”는 비즈니스 관점에서 한 번 의심해 볼 가치가 있습니다. -
type=index가 좋아 보이는 착시를 평생 조심해야 합니다. 이름이index라 인덱스를 잘 쓰는 것처럼 보이지만 실제론 인덱스 풀 스캔입니다. 반대로Extra의Using index가 진짜 칭찬입니다. 이 둘이 정반대라는 걸 책이 굵게 강조한 이유를 새깁니다.
관련 개념
- Ch10-1 통계 정보 —
rows·filtered예측의 근거, 히스토그램 - Ch10-2 실행 계획 확인 — EXPLAIN 출력 포맷과 EXPLAIN ANALYZE
- Ch08-8 클러스터링 인덱스 — 커버링 인덱스가 가능한 이유(세컨더리 인덱스에 PK 저장)
- Ch09-3 고급 최적화 — ICP·MRR·해시 조인·세미 조인 전략의 상세
- Ch08-3 B-Tree 인덱스 — 인덱스 접근 방법(type)의 토대
출처
- Real MySQL 8.0 (1권), 10.3 실행 계획 분석