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
SUBQUERYSELECT 절의 서브쿼리
DERIVEDFROM 절의 서브쿼리 (임시 테이블 생성)
UNIONUNION의 두 번째 이후 SELECT

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

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

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

type설명성능
system테이블에 행이 1개최고
constPK/유니크 인덱스로 1행 조회◎
eq_refJOIN에서 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 bufferJOIN 시 인덱스 없음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 filesortORDER BY와 인덱스 불일치복합 인덱스에 정렬 컬럼 포함
Using temporaryGROUP 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편트랜잭션과 MVCCACID, 격리 수준, Read View, Undo 체인
4편잠금레코드/갭/넥스트키 락, 데드락, Wait-for Graph
5편쿼리 실행 계획EXPLAIN, 옵티마이저, type, Extra

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

MySQLEXPLAIN쿼리 최적화옵티마이저실행 계획데이터베이스면접 준비

관련 글

MySQL JSON 정렬의 Out of sort memory — 버퍼를 늘리면 절벽이 옮겨갈 뿐입니다

MySQL 에서 JSON 컬럼을 ORDER BY 하다 Out of sort memory 가 나면 메시지는 sort_buffer_size 를 늘리라고 합니다. 재현해 보니 8MB 로 올리자 통과는 했지만 버퍼를 꽉 채웠고, 정렬을 없애는 인덱스는 옵티마이저가 고르지 않았습니다.

관련도 93%

CodeRabbit으로 코드 속 예상 SQL을 정리하고 DBA 검토를 요청한 이유

조건문과 쿼리 빌더에 흩어진 코드를 DBA가 검토하려면 어떤 SQL이 만들어지는지부터 파악해야 했습니다. CodeRabbit에 예상 SQL과 검토 지점을 정리하도록 설정한 배경, 가상 코드 예시, 실제 SQL 대조와 사람의 판단이 필요한 구간을 함께 정리했습니다.

관련도 92%

인덱스는 왜 빠른가 — B+Tree부터 커버링 인덱스까지 (2편)

인덱스를 걸면 빨라진다는 건 알지만, 왜 빠른지 설명할 수 있는가? B+Tree의 구조, 클러스터드 인덱스와 세컨더리 인덱스의 차이, 커버링 인덱스가 디스크 접근을 줄이는 원리, 복합 인덱스의 최좌선 규칙까지 정리합니다.

관련도 90%