MySQL에서 EXPLAIN 명령어로 쿼리 성능 분석

느린 쿼리는 반드시 이유가 있습니다. EXPLAIN은 MySQL이 쿼리를 어떻게 실행하려는지 보여주며, 병목 구간을 정확히 짚어줍니다.

EXPLAIN 기본부터 FORMAT=JSON, EXPLAIN ANALYZE를 정리했습니다.

 

 

1) EXPLAIN 기본 사용법

EXPLAIN
SELECT o.id, o.user_id, u.name
FROM orders o
JOIN users  u ON u.id = o.user_id
WHERE o.created_at >= CURDATE() - INTERVAL 30 DAY
  AND u.country = 'KR'
ORDER BY o.created_at DESC
LIMIT 20;

결과는 테이블(또는 서브쿼리) 단위로 한 행씩 출력됩니다. 위에서부터 실행 순서라고 생각하면 이해가 쉽습니다.

 

 

2) EXPLAIN 핵심 컬럼 빠르게 해석하기

컬럼	의미/해석 포인트
id	쿼리의 단계/순서. 숫자가 클수록 먼저 실행되는 경향(서브쿼리 등 구분).
select_type	SIMPLE, PRIMARY, SUBQUERY, DERIVED 등 쿼리 유형.
table	접근 중인 테이블(별칭 포함).
partitions	파티셔닝 사용 시 어떤 파티션을 읽는지.
type	접근 방식. 성능 중요도 1순위. 좋음: system > const > eq_ref > ref > range > index > ALL(테이블 풀스캔)
possible_keys	사용 가능했던 인덱스 후보.
key	실제로 선택된 인덱스. NULL이면 인덱스 미사용.
key_len	사용된 인덱스 길이(바이트). 선두 컬럼만 쓰였는지 추정할 때 유용.
ref	조인 시 어떤 컬럼/상수가 인덱스를 타는지.
rows	예상 스캔 행 수(작을수록 좋음). 추정치이므로 실제와 다를 수 있음.
filtered	필터 후 남는 비율(%) 추정. 낮으면 불필요한 읽기가 많은 것.
Extra	Using where, Using index, Using temporary, Using filesort 등 힌트성 메시지.

체크포인트: type=ALL 또는 Extra에 Using temporary/Using filesort가 계속 보인다면 개선 여지가 큽니다.

 

 

3) 예제로 보는 해석 & 튜닝

3-1. WHERE + ORDER BY가 느린 목록

EXPLAIN
SELECT * FROM posts
WHERE status = 'PUBLISHED'
ORDER BY published_at DESC
LIMIT 20;

문제 신호: type=ALL, key=NULL, rows가 매우 큼, Extra=Using filesort.

개선: (status, published_at) 순서의 복합 인덱스 추가.

CREATE INDEX ix_posts_status_published_at
  ON posts (status, published_at DESC);  -- MySQL 8.0는 DESC 인덱스 지원

EXPLAIN
SELECT * FROM posts
WHERE status = 'PUBLISHED'
ORDER BY published_at DESC
LIMIT 20;

이제 type=range/ref로 바뀌고, 정렬이 인덱스만으로 처리되어 Using filesort가 사라질 수 있습니다.

 

 

3-2. JOIN 순서와 key_len

EXPLAIN
SELECT o.id, u.name
FROM orders o
JOIN users  u ON u.id = o.user_id
WHERE o.user_id = 10
  AND u.country = 'KR';

개선 아이디어:

• orders(user_id), users(id), users(country) 인덱스 확인
• key_len이 선두 컬럼까지만 사용되는지 체크(복합 인덱스 설계 재검토)

 

4) 더 풍부한 정보: EXPLAIN FORMAT=JSON

EXPLAIN FORMAT=JSON
SELECT o.id, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.country = 'KR';

JSON 출력에는 cost 정보, chosen range, attached conditions, used_columns 등 세부 계획이 포함돼 원인 분석이 쉬워집니다. 파싱해서 로그/대시보드에 넣어도 좋습니다.

 

 

5) 실제 실행 시간을 보자: EXPLAIN ANALYZE (8.0.18+)

EXPLAIN ANALYZE
SELECT /* 실제 실행됨 */ *
FROM posts
WHERE status = 'PUBLISHED'
ORDER BY published_at DESC
LIMIT 20;

EXPLAIN은 “계획”이고, EXPLAIN ANALYZE는 쿼리를 실제로 실행하며 실측 시간과 각 단계의 rows examined를 보여줍니다. 계획 대비 오차를 바로 확인할 수 있어 튜닝 정확도가 높아집니다.

 

6) Extra 메시지 빠르게 판독

• Using index: 커버링 인덱스. 테이블 본문을 읽지 않음(좋음).
• Using where: 인덱스로 후보를 좁힌 뒤 추가 필터 수행.
• Using temporary: 임시 테이블 생성. GROUP BY/ORDER BY 조합에서 자주 발생(가능하면 인덱스로 해소).
• Using filesort: 추가 정렬 수행. 정렬 키 포함 인덱스로 대체 고려.
• Range checked for each record: 적절한 인덱스를 못 골라 레코드마다 범위 체크(인덱스 재설계 필요).

 

7) 튜닝 체크리스트 (바로 적용)

1. WHERE, JOIN, ORDER BY, GROUP BY에 사용되는 컬럼부터 인덱스 후보에 올리기.
2. 복합 인덱스는 선행 선택도 높은 컬럼 → 정렬/그룹 컬럼 순으로 설계.
3. SELECT * 대신 필요한 컬럼만 선택 → 커버링 인덱스 유도.
4. 큰 OFFSET 페이지네이션은 커서 방식(키 기반)으로 변경.
5. 통계가 계획과 다르면 ANALYZE TABLE 또는 인덱스 재구축 고려.
6. 서브쿼리를 JOIN/EXISTS로 바꿔 계획 단순화 시도.

 

8) 자주 쓰는 EXPLAIN 스니펫

-- 1) 일반/JSON/ANALYZE 3종 비교
EXPLAIN SELECT ...;
EXPLAIN FORMAT=JSON SELECT ...;
EXPLAIN ANALYZE SELECT ...;

-- 2) 특정 인덱스 사용 유도(힌트, 8.0+)
SELECT /*+ INDEX(posts ix_posts_status_published_at) */
  id, title
FROM posts
WHERE status='PUBLISHED'
ORDER BY published_at DESC
LIMIT 20;

-- 3) 통계 최신화
ANALYZE TABLE posts;

-- 4) 정렬/그룹 최적화용 복합 인덱스
CREATE INDEX ix_orders_user_date ON orders (user_id, created_at);

 

 

 

EXPLAIN → FORMAT=JSON → EXPLAIN ANALYZE 순으로 살펴보면 계획과 실제를 모두 파악할 수 있습니다.

핵심은 type, key, rows, Extra 해석과, 그에 맞춘 인덱스/쿼리 구조 재설계입니다. 작은 수정 하나가 응답 시간을 수십 배 줄일 수 있습니다.