MySQL 쿼리 실행 계획(EXPLAIN) 완벽 이해하기
데이터베이스 성능 튜닝에서 가장 기본적이면서도 강력한 도구가 바로 EXPLAIN입니다. 쿼리를 실제로 실행하지 않고도 MySQL 옵티마이저가 데이터를 어떻게 검색할 계획인지를 보여주며, 인덱스 사용 여부, 조인 방식, 예상 검사 행 수까지 파악할 수 있습니다. 특히 대용량 데이터를 다루는 서비스에서는 쿼리 하나가 전체 시스템 성능을 좌우할 수 있기 때문에, EXPLAIN을 통한 실행 계획 분석은 백엔드 개발자에게 필수적인 역량입니다. 이번 글에서는 EXPLAIN의 기본 사용법부터 출력 컬럼 해석, 실전 분석 사례까지 상세히 정리해 드리겠습니다.
EXPLAIN이란 무엇인가
EXPLAIN은 MySQL에서 쿼리의 실행 계획을 분석하는 진단 도구입니다. SELECT, DELETE, INSERT, REPLACE, UPDATE 문 앞에 EXPLAIN 키워드를 붙이면, MySQL이 해당 쿼리를 어떻게 실행할 것인지에 대한 상세 정보를 반환합니다. 이 정보를 통해 쿼리가 효율적으로 실행되는지, 불필요한 풀 테이블 스캔이 발생하는지, 적절한 인덱스를 사용하고 있는지를 판단할 수 있습니다.
EXPLAIN은 쿼리를 실제로 실행하지 않고 실행 계획만 보여주기 때문에, 프로덕션 환경에서도 안전하게 사용할 수 있습니다. 다만 EXPLAIN ANALYZE는 실제 실행까지 포함하므로 주의가 필요합니다.
EXPLAIN 기본 사용법
EXPLAIN은 조회하려는 쿼리 앞에 키워드를 붙이기만 하면 됩니다. 사용 방식에 따라 제공되는 정보의 깊이가 달라집니다.
📋 기본 EXPLAIN 실행
가장 기본적인 형태로, 쿼리 앞에 EXPLAIN을 붙여 실행 계획을 확인합니다. 실행 계획만 보여주고 실제 데이터는 반환하지 않습니다.
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';📋 EXPLAIN ANALYZE 활용
MySQL 8.0.18부터 도입된 EXPLAIN ANALYZE는 실행 계획뿐만 아니라 실제 실행 시간까지 함께 보여줍니다. 예상 비용(cost)과 실제 실행 시간을 비교할 수 있어 더 정확한 성능 분석이 가능합니다.
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'completed';📋 JSON 형식 출력
복잡한 쿼리의 실행 계획을 구조화된 형태로 확인하고 싶다면 FORMAT=JSON 옵션을 사용합니다. 자동화된 성능 모니터링 도구와 연동할 때 유용합니다.
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'test@example.com';EXPLAIN 출력 컬럼 완벽 해석
EXPLAIN을 실행하면 여러 컬럼이 출력됩니다. 각 컬럼이 무엇을 의미하는지 정확히 이해하는 것이 실행 계획 분석의 출발점입니다.
| 컬럼명 | 의미 | 중요도 |
|---|---|---|
| id | SELECT 문 식별 번호 | ⭐⭐⭐ |
| select_type | SELECT 유형 (SIMPLE, PRIMARY, SUBQUERY 등) | ⭐⭐⭐ |
| table | 참조하는 테이블명 | ⭐⭐ |
| type | 데이터 접근 방식 (ALL, index, range, ref 등) | ⭐⭐⭐⭐⭐ |
| possible_keys | 사용 가능한 인덱스 목록 | ⭐⭐⭐ |
| key | 실제 사용된 인덱스 | ⭐⭐⭐⭐⭐ |
| key_len | 사용된 인덱스의 바이트 길이 | ⭐⭐⭐ |
| ref | 인덱스와 비교되는 대상 | ⭐⭐ |
| rows | 예상 검사 행 수 | ⭐⭐⭐⭐ |
| filtered | 필터링 후 남은 행 비율(%) | ⭐⭐⭐ |
| Extra | 추가 실행 정보 | ⭐⭐⭐⭐⭐ |
🔍 id 컬럼
SELECT 문의 식별 번호입니다. 서브쿼리나 UNION이 포함된 복잡한 쿼리에서 각 SELECT 블록을 구분하는 역할을 합니다. 숫자가 클수록 먼저 실행되며, 같은 id 값을 가지면 위에서 아래로 순차적으로 실행됩니다.
🔍 select_type 컬럼
- SIMPLE: 단순 SELECT, 서브쿼리나 UNION 없음
- PRIMARY: 가장 바깥쪽 SELECT
- SUBQUERY: 독립적인 서브쿼리
- DERIVED: FROM 절의 서브쿼리 (임시 테이블 생성)
- DEPENDENT SUBQUERY: 외부 쿼리 결과에 의존하는 서브쿼리 (성능 주의)
- UNION: UNION 구문의 두 번째 이후 SELECT
🔍 type 컬럼 — 성능의 척도
type은 가장 중요한 컬럼 중 하나로, MySQL이 테이블 데이터에 어떤 방식으로 접근하는지를 보여줍니다.
성능 좋음 ▶ system > const > eq_ref > ref > range > index > ALL ◀ 성능 나쁨| 타입 | 설명 | 사용 예시 |
|---|---|---|
| system | 테이블이 1행만 존재 | 설정 테이블 |
| const | PK/UK로 단 1행 조회 | WHERE id = 1 |
| eq_ref | 조인에서 PK/UK로 1행 매칭 | A JOIN B ON A.id = B.pk |
| ref | 인덱스로 여러 행 조회 | WHERE status = ‘active’ |
| range | 인덱스 범위 스캔 | WHERE age BETWEEN 20 AND 30 |
| index | 인덱스 풀 스캔 | 인덱스만으로 커버 가능할 때 |
| ALL | 테이블 풀 스캔 | ⚠️ 인덱스 미사용, 반드시 개선 |
실무 기준: range 이상이어야 양호하며, ALL은 반드시 개선해야 합니다.
🔍 key 컬럼
실제로 사용된 인덱스 이름입니다. possible_keys에 있지만 key가 NULL이라면 인덱스를 사용하지 않은 것이며, 이 경우 풀 테이블 스캔이 발생했을 가능성이 높습니다.
🔍 key_len 컬럼
사용된 인덱스의 바이트 길이입니다. 복합 인덱스를 사용할 때 얼마나 많은 컬럼을 활용했는지 알 수 있습니다. 예를 들어 INT 타입 컬럼 하나만 사용했다면 4바이트, 두 개를 사용했다면 8바이트가 나타납니다.
🔍 rows 컬럼
MySQL이 쿼리를 실행하기 위해 검사해야 할 것으로 예상하는 행의 수입니다. 이 숫자가 실제 테이블의 전체 행 수와 비슷하다면 인덱스를 제대로 활용하지 못하고 있다는 신호입니다.
🔍 Extra 컬럼 — 숨겨진 성능 힌트
| 값 | 의미 | 조치 |
|---|---|---|
| Using index | 커버링 인덱스 사용 | ✅ 최적 상태 |
| Using where | 스토리지 엔진 이후 필터링 | 보통 양호 |
| Using filesort | 정렬을 메모리/디스크에서 수행 | ⚠️ 인덱스 정렬 고려 |
| Using temporary | 임시 테이블 생성 | ⚠️ GROUP BY/ORDER BY 최적화 |
| Using join buffer | 조인 버퍼 사용 (인덱스 없음) | ❌ 인덱스 추가 검토 |
| Using index condition | ICP (Index Condition Pushdown) | ✅ 5.6+ 최적화 활용 |
실전 실행 계획 분석 예시
이론만으로는 부족합니다. 실제 쿼리의 EXPLAIN 결과를 분석하고 문제를 찾아 개선하는 과정을 살펴보겠습니다.
🚨 예시 1: 풀 테이블 스캔 문제 진단
다음 쿼리의 실행 계획을 살펴보겠습니다.
EXPLAIN SELECT * FROM orders
WHERE created_at > '2024-01-01' AND status = 'completed';문제점:
- type: ALL → 풀 테이블 스캔 발생
- possible_keys: NULL → 사용 가능한 인덱스 없음
- rows: 500,000 → 50만 행 전체 검사
해결:
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);인덱스 추가 후 다시 EXPLAIN을 실행하면 type이 range로 변경되고, rows가 크게 줄어드는 것을 확인할 수 있습니다.
✅ 예시 2: 커버링 인덱스 최적화
다음 쿼리를 보겠습니다.
EXPLAIN SELECT user_id, email FROM users WHERE status = 'active';최적 상태:
- type: ref → 인덱스 효율적 사용
- Extra: Using index → 테이블 접근 없이 인덱스만으로 해결 (커버링 인덱스)
이 상태는 디스크 I/O를 최소화하는 이상적인 상태입니다.
🚨 예시 3: 잘못된 인덱스 사용
last_name과 first_name으로 구성된 복합 인덱스가 있을 때, 다음 쿼리를 실행해봅니다.
EXPLAIN SELECT * FROM users WHERE first_name = 'John';문제점:
- possible_keys에 인덱스가 있지만 key: NULL
- 복합 인덱스의 선두 컬럼(last_name)이 조건에 없어 인덱스 활용 불가
해결:
-- 별도 인덱스 추가
ALTER TABLE users ADD INDEX idx_first_name (first_name);EXPLAIN ANALYZE 심화 분석
MySQL 8.0.18부터 제공되는 EXPLAIN ANALYZE는 예상 실행 계획에 실제 실행 시간까지 더해주어 더 정밀한 분석이 가능합니다.
EXPLAIN ANALYZE
SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'completed'
LIMIT 100;핵심 포인트:
- actual time: 각 연산자의 실제 실행 시간
- loops: 반복 실행 횟수 (조인 버퍼/서브쿼리 주의)
- rows 예측 vs 실제 차이 → 통계 정보 갱신 필요 신호
예상 rows와 actual rows가 크게 차이나면 ANALYZE TABLE을 실행하여 통계를 갱신해야 합니다.
실무 체크리스트
EXPLAIN 결과를 볼 때마다 다음 항목들을 순서대로 확인하는 습관을 들이면 체계적인 분석이 가능합니다.
- ✅ type이 ALL이나 index인지 확인 → 인덱스 추가 또는 쿼리 수정
- ✅ Extra에 Using filesort/temporary 있는지 확인 → 정렬/그룹핑 최적화
- ✅ key_len 확인 → 복합 인덱스 활용도 체크
- ✅ rows × filtered ≈ 실제 결과 행 수와 비교
- ✅ possible_keys에 있지만 key가 NULL → 인덱스 사용 불가 원인 분석
- ✅ 다중 테이블 조인 시 각 테이블의 type 모두 확인
- ✅ EXPLAIN ANALYZE로 예상 시간과 실제 실행 시간 비교
흔한 실수와 해결책
EXPLAIN으로 분석하다 보면 반복해서 마주치는 패턴들이 있습니다.
❌ 인덱스 컬럼에 함수 적용
-- ❌ 인덱스 불가: 컬럼에 함수 적용
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- ✅ 해결: 범위 조건으로 변경
SELECT * FROM users WHERE created_at >= '2024-01-01'
AND created_at < '2025-01-01';❌ 암시적 형변환
-- ❌ 인덱스 불가: 문자열 컬럼에 숫자 비교
SELECT * FROM users WHERE code = 123; -- code는 VARCHAR
-- ✅ 해결: 올바른 타입 사용
SELECT * FROM users WHERE code = '123';❌ LIKE 패턴의 위치
-- ❌ 인덱스 불가: 앞부분 와일드카드
SELECT * FROM users WHERE email LIKE '%gmail.com';
-- ✅ 해결: 뒷부분 와일드카드만 사용
SELECT * FROM users WHERE email LIKE 'user@%';❌ OR 조건의 함정
-- ❌ 인덱스 불가: OR로 하나라도 인덱스 미사용 시 풀 스캔
SELECT * FROM users WHERE status = 'active' OR age > 50;
-- ✅ 해결: UNION으로 분리
SELECT * FROM users WHERE status = 'active'
UNION
SELECT * FROM users WHERE age > 50;자주 묻는 질문
EXPLAIN은 프로덕션에서 안전한가요?
기본 EXPLAIN은 쿼리를 실제로 실행하지 않으므로 안전합니다. 하지만 EXPLAIN ANALYZE는 실제 실행까지 포함하므로, 대용량 테이블이나 복잡한 조인이 있는 쿼리에서는 주의가 필요합니다. 가능하면 개발/스테이징 환경에서 먼저 테스트하세요.
EXPLAIN 결과가 실제와 다를 때는?
EXPLAIN은 테이블 통계 정보를 기반으로 예상을 보여줍니다. 통계 정보가 오래되었거나 데이터 분포가 급변한 경우 예측과 실제가 달라질 수 있습니다. 이때는 ANALYZE TABLE 명령으로 통계를 갱신한 뒤 다시 확인하세요.
ANALYZE TABLE users;복합 인덱스 순서가 EXPLAIN에 어떻게 나타나나요?
key_len 컬럼을 확인하면 됩니다. 예를 들어 (name VARCHAR(50), age INT) 복합 인덱스에서 key_len이 4라면 age 컬럼만 사용한 것이고, 54라면 name(50+2바이트 오버헤드)과 age(4바이트)를 모두 사용한 것입니다.
마무리
이번 글에서는 MySQL 쿼리 실행 계획(EXPLAIN) 완벽 이해하기를 자세히 살펴보았습니다. EXPLAIN은 데이터베이스 성능 튜닝의 핵심 도구이지만, 단순히 실행 결과만 보는 것이 아니라 각 컬럼의 의미를 정확히 해석하고 개선까지 이어가는 연속적인 과정입니다.
핵심을 정리하면 다음과 같습니다: type 컬럼을 먼저 확인하여 ALL을 피하고, Extra 컬럼에서 Using filesort와 Using temporary를 제거하며, key와 key_len으로 인덱스 활용도를 점검하고, EXPLAIN ANALYZE로 예상과 실제의 괴리를 줄이세요.
MySQL 쿼리 실행 계획(EXPLAIN) 관련 궁금한 점이나 문제가 있으시면 댓글로 남겨 주세요. 도움이 되셨다면 이 글을 주변에 공유해 주시는 것도 잊지 마세요!
댓글 0
첫 댓글을 남겨보세요.