EXPLAIN으로 읽는 쿼리 실행 계획 — 느린 쿼리 진단법 (5편)

들어가며

"이 쿼리가 느린데 왜 그런지 모르겠어요." 이 질문에 답하는 첫 번째 도구가 EXPLAIN입니다.

1편에서 데이터가 페이지 단위로 저장되는 것, 2편에서 인덱스가 B+Tree로 동작하는 것, 3편에서 MVCC가 읽기 일관성을 보장하는 것, 4편에서 잠금이 동시성을 제어하는 것을 살펴봤습니다. EXPLAIN은 이 모든 것이 실제 쿼리에서 어떻게 조합되는지 보여주는 창입니다.

이 글에서는 EXPLAIN의 각 컬럼이 무엇을 의미하는지, 옵티마이저가 어떤 기준으로 실행 계획을 선택하는지, 그리고 느린 쿼리를 실전에서 어떻게 진단하고 개선하는지 정리합니다.

EXPLAIN 기본 사용법

EXPLAIN SELECT * FROM users WHERE age > 25 ORDER BY created_at DESC LIMIT 10;

결과는 테이블 형태로 출력되며, 각 행은 쿼리에서 접근하는 테이블 하나를 나타냅니다. JOIN이 있으면 여러 행이 출력됩니다.

EXPLAIN FORMAT=JSON

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 25;

JSON 형식은 더 상세한 정보를 제공합니다. 특히 cost_info로 옵티마이저가 계산한 비용을 확인할 수 있습니다.

EXPLAIN ANALYZE (MySQL 8.0.18+)

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 25;

EXPLAIN ANALYZE는 실제로 쿼리를 실행하고, 예측값과 실제값을 비교해줍니다. 옵티마이저의 예측이 맞는지 검증할 때 유용합니다. 단, 실제 실행이므로 프로덕션에서는 주의가 필요합니다.

EXPLAIN 컬럼 해부

id: 쿼리 실행 순서

같은 id는 JOIN으로 함께 실행, 다른 id는 서브쿼리입니다. 큰 id가 먼저 실행됩니다.

-- id=1: 외부 쿼리
-- id=2: 서브쿼리 (먼저 실행)
EXPLAIN SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE total > 10000);

select_type: 쿼리 유형

값	의미
SIMPLE	서브쿼리나 UNION 없는 단순 SELECT
PRIMARY	가장 바깥 SELECT
SUBQUERY	SELECT 절의 서브쿼리
DERIVED	FROM 절의 서브쿼리 (임시 테이블 생성)
UNION	UNION의 두 번째 이후 SELECT

DERIVED가 보이면, 임시 테이블이 생성된다는 의미입니다. 가능하면 JOIN으로 변환하는 것이 좋습니다.

type: 접근 방식 (가장 중요)

옵티마이저가 테이블에 어떻게 접근하는지를 나타냅니다. 성능 순서로:

type	설명	성능
system	테이블에 행이 1개	최고
const	PK/유니크 인덱스로 1행 조회	◎
eq_ref	JOIN에서 PK/유니크로 1행씩 매칭	◎
ref	비유니크 인덱스로 매칭	○
range	인덱스 범위 스캔 (BETWEEN, >, <)	○
index	인덱스 풀 스캔 (커버링 인덱스)	△
ALL	테이블 풀 스캔	✕

const: 상수 시간 조회

EXPLAIN SELECT * FROM users WHERE id = 1;
-- type: const
-- PK로 정확히 1행. B+Tree 높이(3-4) 만큼만 페이지 읽기.

ref: 비유니크 인덱스 매칭

EXPLAIN SELECT * FROM orders WHERE user_id = 42;
-- type: ref
-- user_id 인덱스로 여러 행을 찾을 수 있음

range: 범위 스캔

EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;
-- type: range
-- 인덱스를 타지만, 범위 내 모든 리프를 스캔

ALL: 풀 테이블 스캔

EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- type: ALL
-- 인덱스를 탈 수 없어서 모든 행을 스캔

ALL이 항상 나쁜 것은 아닙니다. 전체 행의 대부분을 읽어야 하는 경우(예: 집계 쿼리), 옵티마이저가 의도적으로 풀 스캔을 선택하기도 합니다. 인덱스를 타면 랜덤 I/O가 많아져서 오히려 느릴 수 있습니다.

possible_keys와 key: 인덱스 선택

• possible_keys: 사용할 수 있는 인덱스 후보
• key: 실제로 선택된 인덱스
• key_len: 사용된 인덱스의 바이트 길이

possible_keys에 인덱스가 있는데 key가 NULL이면? 옵티마이저가 "인덱스를 타느니 풀 스캔이 낫다"고 판단한 것입니다. 보통 조회 결과가 전체 행의 20-30% 이상일 때 이런 결정을 내립니다.

key_len으로 복합 인덱스 활용도 확인

-- 인덱스: (age INT, name VARCHAR(50), city VARCHAR(50))
-- key_len = 4                → age만 사용
-- key_len = 4 + 52 = 56     → age + name까지 사용
-- key_len = 4 + 52 + 52 = 108 → 3개 컬럼 모두 사용
-- (VARCHAR는 길이 + 2바이트 + NULL 1바이트)

key_len이 예상보다 짧다면, 복합 인덱스의 일부만 활용되고 있다는 신호입니다.

rows: 예상 스캔 행 수

옵티마이저가 예측한 스캔 행 수입니다. 정확한 값이 아니라 통계 기반 추정치입니다.

이 값이 실제와 크게 다르면, 통계가 오래되었을 수 있습니다:

-- 통계 갱신
ANALYZE TABLE users;

filtered: 필터링 비율

rows 중 실제로 조건을 만족하는 행의 비율(%)입니다. rows × filtered / 100이 실제 결과 행 수의 추정치입니다.

-- rows: 1000, filtered: 10.00
-- 예상 결과: 1000 × 10% = 100행

filtered가 낮다면(1-5%), 인덱스로 가져온 행 중 대부분을 버린다는 의미입니다. 더 선택적인 인덱스가 필요합니다.

Extra: 추가 정보 (놓치기 쉬운 핵심)

Extra 컬럼에는 성능에 중요한 정보가 담겨 있습니다.

좋은 신호

값	의미
Using index	커버링 인덱스. 클러스터드 인덱스 접근 불필요.
Using index condition	인덱스 컨디션 푸시다운(ICP). 스토리지 엔진에서 필터링.
Using where	서버 계층에서 추가 필터링. 그 자체로는 중립적.

주의가 필요한 신호

값	의미	개선 방향
Using filesort	정렬을 위해 추가 작업 필요	ORDER BY에 맞는 인덱스 추가
Using temporary	임시 테이블 생성	GROUP BY/ORDER BY 최적화
Using join buffer	JOIN 시 인덱스 없음	JOIN 조건에 인덱스 추가

Using filesort의 이해

"filesort"라는 이름과 달리, 반드시 디스크를 사용하는 건 아닙니다. 메모리(sort_buffer_size)에서 정렬되면 빠르지만, 데이터가 크면 디스크 임시 파일을 사용합니다.

-- ❌ Using filesort 발생
SELECT * FROM users WHERE age > 20 ORDER BY name;
-- age 인덱스를 타지만, name으로 정렬하려면 별도 정렬 필요

-- ✅ 인덱스로 정렬까지 해결
-- 인덱스: (age, name)
SELECT * FROM users WHERE age = 25 ORDER BY name;
-- age=25로 인덱스 탐색 + name 순서가 이미 정렬되어 있음

옵티마이저의 판단 기준

MySQL 옵티마이저는 비용 기반(Cost-Based)입니다. 여러 실행 계획의 비용을 계산하고, 가장 낮은 비용을 선택합니다.

비용 모델의 구성 요소

• I/O 비용: 디스크에서 페이지를 읽는 비용. Buffer Pool 히트율에 따라 달라짐.
• CPU 비용: 행을 비교, 정렬하는 비용.

-- 옵티마이저가 계산한 비용 확인
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 25;
-- "cost_info": {
--   "read_cost": "10.25",
--   "eval_cost": "2.50",
--   "prefix_cost": "12.75",    ← 누적 비용
--   "data_read_per_join": "1M"
-- }

옵티마이저가 인덱스를 안 타는 이유

1. 선택도(Selectivity)가 낮을 때: 조건에 해당하는 행이 전체의 20-30% 이상이면, 인덱스 랜덤 I/O보다 풀 스캔 순차 I/O가 빠릅니다.
2. 통계가 부정확할 때: ANALYZE TABLE로 갱신이 필요합니다.
3. 함수/형변환이 적용될 때: 인덱스 키와 비교 대상의 형이 다르면 인덱스를 탈 수 없습니다.

옵티마이저 힌트

옵티마이저가 잘못된 판단을 할 때 힌트로 유도할 수 있습니다:

-- 특정 인덱스 사용 유도
SELECT * FROM users USE INDEX (idx_age) WHERE age > 25;

-- MySQL 8.0+ 옵티마이저 힌트
SELECT /*+ INDEX(users idx_age) */ * FROM users WHERE age > 25;
SELECT /*+ NO_INDEX(users idx_age) */ * FROM users WHERE age > 25;

하지만 힌트는 최후의 수단입니다. 데이터 분포가 바뀌면 힌트가 오히려 성능을 악화시킬 수 있습니다. 인덱스 설계나 쿼리 구조를 개선하는 것이 우선입니다.

실전: 느린 쿼리 진단 흐름

느린 쿼리를 만났을 때의 접근 순서:

1단계: 슬로우 쿼리 로그에서 대상 식별

-- 슬로우 쿼리 로그 설정
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 1초 이상
SET GLOBAL log_queries_not_using_indexes = ON;

2단계: EXPLAIN으로 실행 계획 확인

EXPLAIN SELECT u.name, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 10;

체크 포인트:

• type이 ALL인 테이블이 있는가?
• key가 NULL인 테이블이 있는가?
• rows가 비정상적으로 큰가?
• Extra에 Using filesort, Using temporary가 있는가?

3단계: EXPLAIN ANALYZE로 실제 실행 검증

EXPLAIN ANALYZE SELECT ...;
-- (actual time=0.015..0.018 rows=1 loops=1)
-- 예측 rows와 실제 rows가 크게 다르면 통계 문제

4단계: 개선 적용

일반적인 개선 패턴:

증상	원인	해결
type: ALL	인덱스 없음 또는 함수 적용	인덱스 추가 / 쿼리 변환
Using filesort	ORDER BY와 인덱스 불일치	복합 인덱스에 정렬 컬럼 포함
Using temporary	GROUP BY와 인덱스 불일치	GROUP BY 컬럼을 인덱스에 포함
rows 과다	선택도 낮은 인덱스	더 선택적인 복합 인덱스
key_len이 짧음	복합 인덱스 부분 활용	쿼리 조건 순서 확인

실전 사례: 개선 전후 비교

사례 1: 정렬이 있는 페이지네이션

-- 느린 쿼리
SELECT * FROM posts 
WHERE is_published = 1 
ORDER BY created_at DESC 
LIMIT 10 OFFSET 10000;

-- EXPLAIN: type=ref, Extra=Using filesort
-- is_published 인덱스는 타지만, 10010행을 읽고 정렬 후 10건만 반환

-- 개선: 복합 인덱스 + 커서 기반 페이지네이션
CREATE INDEX idx_published_created ON posts(is_published, created_at DESC);

SELECT * FROM posts 
WHERE is_published = 1 AND created_at < '2024-03-15 10:30:00'
ORDER BY created_at DESC 
LIMIT 10;

-- EXPLAIN: type=range, Extra=Using index condition
-- 인덱스 순서로 정렬되어 있으므로 filesort 없음
-- OFFSET 대신 커서로 시작점 지정 → 깊은 페이지도 빠름

사례 2: JOIN + GROUP BY

-- 느린 쿼리
SELECT u.name, COUNT(*) as cnt
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.id
HAVING cnt > 5;

-- EXPLAIN:
-- orders: type=ALL (인덱스 없음), Using temporary, Using filesort

-- 개선: 필요한 인덱스 추가
CREATE INDEX idx_status_userid ON orders(status, user_id);

-- EXPLAIN:
-- orders: type=ref, key=idx_status_userid
-- Using index (커버링 인덱스: status와 user_id만 필요)

주의: EXPLAIN이 보여주지 않는 것들

• Buffer Pool 히트율: 같은 쿼리도 캐시 상태에 따라 성능이 다릅니다
• 잠금 대기: 다른 트랜잭션의 잠금에 막혀 느린 경우
• 네트워크 지연: 대량의 결과를 전송하는 비용
• 클라이언트 측 처리: ORM의 N+1 문제 등

EXPLAIN은 "쿼리 실행 계획"만 보여줍니다. 전체 성능 문제를 진단하려면 슬로우 쿼리 로그, SHOW PROCESSLIST, Performance Schema 등을 함께 활용해야 합니다.

시리즈 정리

5편에 걸쳐 RDBMS(MySQL InnoDB)의 내부 동작을 추적했습니다:

편	주제	핵심 키워드
1편	데이터 저장 구조	페이지, Buffer Pool, Redo/Undo Log, WAL
2편	인덱스	B+Tree, 클러스터드/세컨더리, 커버링, 복합 인덱스
3편	트랜잭션과 MVCC	ACID, 격리 수준, Read View, Undo 체인
4편	잠금	레코드/갭/넥스트키 락, 데드락, Wait-for Graph
5편	쿼리 실행 계획	EXPLAIN, 옵티마이저, type, Extra

이 구조들은 독립적이지 않습니다. 페이지 위에 인덱스가 있고, 인덱스 위에 잠금이 걸리고, MVCC가 잠금 없이 읽기를 가능하게 하고, EXPLAIN이 이 모든 것의 조합을 보여줍니다. 한 가지를 깊이 이해하면 나머지가 연결되는 구조입니다.