들어가며: 쿼리가 느린 이유를 어떻게 찾는가
SQL을 작성하다 보면 분명히 맞게 짰는데 유독 느린 쿼리를 만나게 된다. 데이터가 적을 때는 문제없이 작동하다가, 데이터가 수십만 건을 넘어가는 순간 응답이 눈에 띄게 느려지는 경우가 대표적이다. 이때 대부분의 개발자가 처음 시도하는 것은 쿼리를 눈으로 다시 읽어보거나, 인덱스를 추가해보거나, 조인 방식을 바꿔보는 것이다.
그런데 원인을 모르는 상태에서 이런 시도를 반복하면 개선이 되는지 안 되는지조차 파악하기 어렵다. 쿼리 최적화의 출발점은 눈대중이 아니라 실행 계획(Execution Plan) 을 읽는 것이다.
EXPLAIN 은 MySQL이 특정 SQL을 어떻게 실행할 것인지를 분석해서 보여주는 명령어다. 실제로 쿼리를 실행하지 않고도 “어떤 테이블을 어떤 방식으로 읽을 것인지”, “인덱스를 사용하는지”, “몇 개의 행을 스캔하는지”를 미리 확인할 수 있다. 이 정보를 바탕으로 어디에 문제가 있는지 정확히 짚어낼 수 있다.
이 글에서는 EXPLAIN의 기본 사용법부터 출력 결과의 각 컬럼이 의미하는 것, 실제로 문제가 되는 패턴, 그리고 개선 전후 비교까지 MySQL 기준으로 단계별로 정리한다.
EXPLAIN이란 무엇인가
EXPLAIN은 MySQL 옵티마이저(Optimizer)가 SQL을 실행하기 위해 선택한 실행 계획을 텍스트 형태로 출력하는 명령어다. 옵티마이저란 동일한 결과를 내는 여러 실행 방법 중에서 가장 효율적인 방법을 선택하는 MySQL 내부 엔진이다.
사용 방법은 단순하다. 기존 SELECT 쿼리 앞에 EXPLAIN만 붙이면 된다.
-- 일반 쿼리
SELECT * FROM users WHERE email = 'hong@example.com';
-- 실행 계획 확인
EXPLAIN SELECT * FROM users WHERE email = 'hong@example.com';
EXPLAIN을 실행하면 쿼리가 실제로 실행되지 않는다. 대신 MySQL이 이 쿼리를 어떻게 처리할 것인지에 대한 계획만 출력된다. 대용량 테이블이라도 부담 없이 실행 계획을 확인할 수 있다.
EXPLAIN 출력 예시:
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 100000 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
이 출력 결과에서 각 컬럼이 무엇을 의미하는지 이해하는 것이 EXPLAIN을 활용하는 핵심이다.
EXPLAIN 출력 결과 컬럼별 의미
id
쿼리 내 SELECT의 순서를 나타낸다. 단순 쿼리는 항상 1이다. 서브쿼리나 UNION이 포함된 경우 각 SELECT마다 고유한 id가 부여된다. id가 같으면 같은 레벨에서 실행되는 쿼리이고, id가 클수록 먼저 실행된다.
select_type
SELECT의 종류를 나타낸다. 자주 보게 되는 값은 다음과 같다.
| 값 | 의미 |
|---|---|
| SIMPLE | 서브쿼리나 UNION이 없는 단순 SELECT |
| PRIMARY | 가장 바깥쪽의 SELECT |
| SUBQUERY | WHERE 절의 서브쿼리 |
| DERIVED | FROM 절의 서브쿼리 (인라인 뷰) |
| UNION | UNION의 두 번째 이후 SELECT |
type — 가장 중요한 컬럼
type은 EXPLAIN 결과에서 가장 먼저 확인해야 하는 컬럼이다. 테이블의 데이터를 어떤 방식으로 읽는지를 나타내며, 성능을 직관적으로 판단할 수 있는 기준이 된다.
성능 좋은 순서로 정렬하면 다음과 같다.
| type | 의미 | 성능 |
|---|---|---|
| system | 테이블에 행이 1개 | 최상 |
| const | PRIMARY KEY 또는 UNIQUE 인덱스로 단 1건 조회 | 최상 |
| eq_ref | 조인에서 PRIMARY KEY 또는 UNIQUE로 1건씩 조회 | 매우 좋음 |
| ref | 인덱스를 사용하지만 결과가 여러 건 | 좋음 |
| range | 인덱스를 범위 조건으로 사용 (BETWEEN, >, < 등) | 보통 |
| index | 인덱스 전체를 스캔 | 나쁨 |
| ALL | 테이블 전체를 스캔 (Full Table Scan) | 최악 |
ALL이 나오면 가장 먼저 의심해야 한다. 테이블의 모든 행을 처음부터 끝까지 읽는 방식이기 때문에, 데이터가 많을수록 성능이 급격히 저하된다.
possible_keys
MySQL이 이 쿼리에 사용할 수 있다고 판단한 인덱스 목록이다. 여기에 인덱스가 나열되어 있다고 해서 반드시 사용되는 것은 아니다.
key
MySQL이 실제로 선택해서 사용한 인덱스다. NULL이면 인덱스를 사용하지 않았다는 의미이므로 주의해야 한다. possible_keys에 인덱스가 있는데도 key가 NULL이면, 옵티마이저가 인덱스를 사용하는 것보다 Full Table Scan이 더 낫다고 판단한 것이다.
key_len
사용된 인덱스의 길이(바이트)다. 복합 인덱스를 사용하는 경우 인덱스의 앞부분 일부만 사용되는지 전체가 사용되는지 판단하는 데 활용된다.
rows
MySQL이 결과를 찾기 위해 읽어야 한다고 예상하는 행의 수다. 정확한 값이 아닌 추정치이지만, 인덱스 효과를 판단하는 중요한 지표다. rows 값이 테이블 전체 행 수에 가까울수록 비효율적인 쿼리라는 신호다.
Extra
쿼리 실행에 대한 추가 정보다. 자주 보게 되는 값은 다음과 같다.
| 값 | 의미 |
|---|---|
| Using where | WHERE 조건으로 필터링 중 |
| Using index | 인덱스만으로 결과를 반환 (커버링 인덱스) |
| Using filesort | 정렬을 위해 별도 작업 발생 — 성능 주의 |
| Using temporary | 임시 테이블 사용 — 성능 주의 |
| Using join buffer | 조인 시 버퍼 사용 — 인덱스 검토 필요 |
Using filesort와 Using temporary가 나타나면 성능 개선 여지가 있다는 신호로 받아들이면 된다.
실전 예제 — 인덱스 없는 쿼리와 있는 쿼리 비교
개념만으로는 EXPLAIN의 가치를 체감하기 어렵다. 실제 예제를 통해 인덱스 유무에 따라 실행 계획이 어떻게 달라지는지 확인해보자.
예제 테이블:
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
product_id INT NOT NULL,
status VARCHAR(20) NOT NULL,
amount DECIMAL(10,2) NOT NULL,
created_at DATETIME DEFAULT NOW()
);
-- 테스트 데이터 100만 건 삽입
INSERT INTO orders (user_id, product_id, status, amount)
SELECT
FLOOR(RAND() * 10000) + 1,
FLOOR(RAND() * 1000) + 1,
ELT(FLOOR(RAND() * 3) + 1, 'pending', 'completed', 'cancelled'),
ROUND(RAND() * 100000, 2)
FROM information_schema.columns
LIMIT 1000000;
인덱스 없는 상태에서의 EXPLAIN:
EXPLAIN SELECT * FROM orders WHERE user_id = 1234;+----+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 996542 | 10.00 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+----------+-------------+
type이 ALL, key가 NULL이다. 100만 건 전체를 스캔해서 user_id가 1234인 행을 찾고 있다. rows가 996,542로 거의 전체를 읽는다.
인덱스 추가 후:
CREATE INDEX idx_user_id ON orders (user_id);
EXPLAIN SELECT * FROM orders WHERE user_id = 1234;+----+-------------+--------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | orders | ref | idx_user_id | idx_user_id | 4 | const | 100 | 100.00 | |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+----------+-------+
type이 ref로 바뀌었고, key에 idx_user_id가 표시된다. rows가 996,542에서 100으로 줄었다. 인덱스 하나로 스캔 행 수가 약 1만 분의 1로 줄어든 것이다.
자주 마주치는 문제 패턴과 해결 방법
패턴 1: Full Table Scan (type = ALL)
type이 ALL인 경우는 인덱스가 없거나, 있더라도 옵티마이저가 사용하지 않는 경우다.
-- 문제 쿼리
EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- type: ALL, key: NULL
-- 해결: 인덱스 추가
CREATE INDEX idx_status ON orders (status);단, status 컬럼처럼 값의 종류가 매우 적은 경우(카디널리티가 낮은 경우) 인덱스를 추가해도 옵티마이저가 Full Table Scan을 선택하는 경우가 있다. 이때는 복합 인덱스를 고려하거나 쿼리 자체를 재구성하는 것이 더 효과적이다.
패턴 2: Using filesort
ORDER BY 절이 포함된 쿼리에서 인덱스를 통한 정렬이 불가능한 경우 발생한다. MySQL이 별도의 정렬 작업을 추가로 수행한다는 의미다.
-- 문제 쿼리
EXPLAIN SELECT * FROM orders WHERE user_id = 1234 ORDER BY created_at DESC;
-- Extra: Using filesort
-- 해결: 복합 인덱스로 정렬까지 커버
CREATE INDEX idx_user_created ON orders (user_id, created_at);
EXPLAIN SELECT * FROM orders WHERE user_id = 1234 ORDER BY created_at DESC;
-- Extra: (Using filesort 사라짐)
WHERE 절의 컬럼과 ORDER BY 절의 컬럼을 하나의 복합 인덱스로 구성하면 filesort를 없앨 수 있다.
패턴 3: Using temporary
GROUP BY나 DISTINCT 처리 시 임시 테이블이 생성되는 경우다.
-- 문제 쿼리
EXPLAIN SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;
-- Extra: Using temporary, Using filesort
-- 해결: GROUP BY 컬럼에 인덱스 추가
CREATE INDEX idx_user_id ON orders (user_id);
EXPLAIN SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;
-- Extra: (Using temporary 사라짐)패턴 4: 인덱스가 있는데 사용되지 않는 경우
인덱스가 있어도 다음과 같은 경우 옵티마이저가 인덱스를 사용하지 않는다.
-- 컬럼에 함수를 적용하면 인덱스 무효화
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- key: NULL
-- 해결: 범위 조건으로 변경
EXPLAIN SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- key: idx_created_at (인덱스 사용)
-- 문자열 컬럼에 숫자를 비교하면 인덱스 무효화
EXPLAIN SELECT * FROM orders WHERE user_id = '1234'; -- user_id가 INT인 경우
-- 타입 불일치로 인해 인덱스가 사용되지 않을 수 있음
-- 해결: 타입을 맞춰서 비교
EXPLAIN SELECT * FROM orders WHERE user_id = 1234;EXPLAIN ANALYZE — 실제 실행 시간까지 확인하기
MySQL 8.0.18 이상에서는 EXPLAIN ANALYZE 명령어를 사용할 수 있다. EXPLAIN이 예상 실행 계획만 보여주는 것과 달리, EXPLAIN ANALYZE는 쿼리를 실제로 실행하고 각 단계별 실제 소요 시간과 처리한 행 수까지 함께 출력한다.
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1234;
-> Index lookup on orders using idx_user_id (user_id=1234)
(cost=35.51 rows=100) (actual time=0.523..1.234 rows=98 loops=1)
출력에서 확인할 수 있는 정보는 다음과 같다.
- cost: 옵티마이저가 추정한 비용
- rows: 예상 행 수
- actual time: 실제 소요 시간 (밀리초 단위, 첫 행 반환 시간..마지막 행 반환 시간)
- rows (actual): 실제 처리한 행 수
- loops: 해당 단계가 반복된 횟수
EXPLAIN만으로는 예상치와 실제 값의 차이를 알 수 없지만, EXPLAIN ANALYZE를 사용하면 옵티마이저의 예측이 실제와 얼마나 일치하는지도 확인할 수 있다. 예측 rows와 실제 rows의 차이가 크다면 테이블 통계 정보가 오래됐을 가능성이 있으며, 이 경우 ANALYZE TABLE 명령어로 통계를 갱신하는 것이 도움이 된다.
나오며: EXPLAIN은 쿼리 최적화의 시작이다
EXPLAIN을 읽을 수 있게 되면 쿼리 성능 문제를 접근하는 방식 자체가 달라진다. 막연하게 “이 쿼리가 느린 것 같은데”에서 벗어나 “type이 ALL이고 rows가 100만이니 인덱스가 없거나 사용되지 않고 있다”는 구체적인 진단이 가능해진다.
이번 글에서 다룬 내용을 정리하면 다음과 같다.
| 확인 항목 | 문제 신호 | 조치 방향 |
|---|---|---|
| type | ALL, index | 인덱스 추가 또는 쿼리 재구성 |
| key | NULL | 인덱스 생성 또는 쿼리 조건 점검 |
| rows | 테이블 전체 행 수에 근접 | 인덱스 효율 점검 |
| Extra | Using filesort | 복합 인덱스로 정렬 커버 |
| Extra | Using temporary | GROUP BY / DISTINCT 컬럼 인덱스 검토 |
EXPLAIN을 통해 문제를 진단했다면, 다음 단계는 인덱스 설계와 쿼리 재작성이다. 인덱스를 어떤 컬럼에, 어떤 순서로 구성해야 하는지에 대한 내용은 인덱스 설계 원칙 글에서 이어서 다룰 예정이다.
Add your first comment to this post