MySQL - EXPLAIN 분석 가이드

MySQL 실행 계획(Execution Plan)을 분석하는 것은 성능 최적화의 핵심 중 하나이다.

마치 하나의 프로젝트처럼, 문제 발견 → 실행 계획 확인 → 분석 → 개선안 도출 → 재검증의 흐름으로 진행해야 하고, 이를 통해 쿼리 성능을 체계적으로 개선할 수 있다.

---

1. 문제 인식: 왜 실행 계획을 봐야 하는가?

시나리오 예시

• 보고서용 쿼리가 너무 느려서 페이지 로딩이 지연됨.
• A/B 테스트 로그 분석 쿼리가 10초 이상 걸림.
• 대시보드에서 필터링을 적용하면 응답 속도가 급격히 저하됨.

이런 상황에서 우리는 쿼리를 단순히 튜닝하는 것이 아니라 MySQL이 내부적으로 쿼리를 어떻게 실행하는지를 알아야 한다. 이때 사용하는 도구가 실행 계획(EXPLAIN)이다.

---

2. 도구 선택: EXPLAIN 및 EXPLAIN ANALYZE

기본 사용법

EXPLAIN SELECT * FROM users WHERE id = 123;

MySQL 8.0 이상에서는:

EXPLAIN ANALYZE SELECT * FROM users WHERE id = 123;

---

3. 실행 계획 읽기: EXPLAIN 결과 해석하기

주요 컬럼

컬럼명	설명
id	SELECT의 순서 또는 중첩 수준
select_type	쿼리 유형 (SIMPLE, PRIMARY, SUBQUERY 등)
table	액세스되는 테이블명
partitions	사용된 파티션
type	조인 방식 (매우 중요)
possible_keys	사용 가능한 인덱스 목록
key	실제로 사용된 인덱스
key_len	인덱스 길이
ref	조인 시 참조된 열
rows	예측된 스캔 row 수
filtered	필터 후 남은 비율 (%)
Extra	추가 정보 (Using index 등)

예시

EXPLAIN SELECT * FROM orders WHERE customer_id = 101;

• type: ref (좋음)
• key: idx_customer_id
• rows: 5
• Extra: Using where; Using index

---

4. 유형별 병목 요소 분석

type 분석

조인 유형 우선순위 (좋음 → 나쁨):

system > const > eq_ref > ref > range > index > ALL

Extra 해석

• Using filesort: 정렬 → 디스크 작업 가능성 → 정렬 최적화 필요
• Using temporary: 임시 테이블 사용 → GROUP BY/DISTINCT 튜닝 필요
• Using index: Covering Index 사용 → 매우 좋음

---

5. 개선 전략 설계

인덱스 최적화

-- Covering index 예시
CREATE INDEX idx_email_name ON users (email, name);

쿼리 구조 개선

• SELECT * 지양
• 서브쿼리 제거 및 JOIN 활용
• IN → JOIN 변환

조인 순서 조정

• 필요 시 STRAIGHT_JOIN 사용

파티셔닝/샤딩

• 날짜 파티셔닝 또는 범위 기반 샤딩 고려

---

6. 재검증 및 성능 측정

실행 시간 확인

EXPLAIN ANALYZE SELECT ...

또는

SET PROFILING = 1;
SELECT * FROM ...
SHOW PROFILES;

지표 비교

항목	개선 전	개선 후
실행 시간	3.5s	0.4s
스캔 row 수	100,000	1,000
조인 방식	ALL	ref
인덱스 사용 여부	없음	있음

---

7. 프로젝트 문서화 예시

목표: order_summary 쿼리의 응답 시간을 5초 → 0.5초로 줄이기
분석: customer_id에 인덱스 없고, FULL SCAN 발생
개선 조치:

• customer_id 인덱스 생성
• 서브쿼리 제거, JOIN으로 재작성
  결과: 실행 시간 0.3초, index range scan으로 전환됨

---

자동 실행 계획 분석 도구

도구	설명
MySQL Workbench	시각화된 실행 계획 확인 가능
Percona Toolkit	슬로우 쿼리 분석 (pt-query-digest)
Performance Schema	MySQL 자체 프로파일링
SHOW PROFILE / SHOW STATUS	상세 단계별 시간 측정

---

마무리

MySQL 실행 계획은 단순한 쿼리 속도 확인 도구가 아니다.
단계적으로 접근하면 다음과 같은 효과가 있다:

• 병목의 원인을 정확히 파악
• 성능 개선의 근거 제시 가능
• 개선 효과 검증 가능