[MySQL] 쿼리 리팩토링 전/후 실행계획으로 성능 변화 분석하기 🔍

MySQL에서 느린 쿼리를 찾고 개선했다면, 진짜 효과가 있었는지 반드시 확인해야 한다.
그 기준은 바로 EXPLAIN 실행계획이다.
단순히 실행 시간이 줄었다고 해서 성공이라고 판단하는 건 위험하다.
MySQL 옵티마이저가 쿼리를 어떻게 처리했는지를 분석해야 병목 원인이 사라졌는지, 새로운 병목이 생기지 않았는지 정확히 알 수 있다.

🧠 EXPLAIN이란 무엇인가?

EXPLAIN은 MySQL에서 쿼리 실행 전, 내부적으로 어떤 방식으로 데이터를 읽고 처리할지를 보여주는 도구다.
즉, 실제로 실행하지 않고 실행 계획만 시뮬레이션해볼 수 있는 기능이다.
복잡한 쿼리일수록 EXPLAIN은 필수적이며, 성능 병목을 정확하게 파악하는 데 반드시 사용해야 한다.

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

또는 MySQL 8.0 이상이라면 아래처럼 더 정밀한 분석도 가능하다.

EXPLAIN ANALYZE FORMAT=JSON SELECT ...

반응형

📋 EXPLAIN 주요 컬럼 설명 요약

컬럼	설명
id	SELECT 단계 구분 (숫자 클수록 내부 쿼리)
select_type	쿼리 유형 (PRIMARY, SUBQUERY, DEPENDENT SUBQUERY 등)
table	처리 중인 테이블
type	접근 방식 (ALL, index, range, ref, eq_ref, const 등)
key	실제 사용된 인덱스 이름
rows	예측되는 row 수 (작을수록 좋음)
Extra	추가 정보 (Using index, Using where, Using temporary 등)

이 중에서도 type, key, rows, Extra 4개는 반드시 해석할 수 있어야 한다.

이 값들로 쿼리의 품질을 판단할 수 있다.

🧪 실전 예제: 서브쿼리 → JOIN 리팩토링 비교

리팩토링 전: 서브쿼리 사용

SELECT name
FROM users
WHERE id IN (
  SELECT user_id FROM orders WHERE amount > 100000
);

EXPLAIN 결과

id	select_type	table	type	key	rows	Extra
1	PRIMARY	users	ALL	NULL	100000	Using where
2	DEPENDENT SUBQUERY	orders	range	amount	50000	Using where; Using index

• users는 전체 테이블 스캔 (type = ALL)
• orders는 조건에 따라 인덱스를 타지만 서브쿼리 구조로 인해 users row마다 반복 실행됨 (DEPENDENT SUBQUERY)
• rows 수가 많을수록 병목 현상 심화됨

실행 시간: 약 3.1초
병목 원인: 반복 서브쿼리, 인덱스 비활용, row 수 폭발

리팩토링 후: JOIN으로 구조 변경

SELECT DISTINCT u.name
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100000;

EXPLAIN 결과

id	select_type	table	type	key	rows	Extra
1	SIMPLE	o	range	amount	8000	Using where
1	SIMPLE	u	eq_ref	PRIMARY	1	Using index

• orders는 amount 조건으로 범위 인덱스 검색 (type = range)
• users는 PRIMARY 키로 정확한 row 하나만 조회 (type = eq_ref)
• 모든 처리에서 인덱스 활용 가능
• 중복 제거를 위해 DISTINCT 사용 (주의: 필요 없는 경우 제거 권장)

실행 시간: 약 0.29초
성능 개선율: 약 10배 이상

반응형

🧠 리팩토링 전후 성능 요소별 비교

항목	리팩토링 전	리팩토링 후
쿼리 구조	서브쿼리 IN	JOIN + WHERE
실행 전략	DEPENDENT SUBQUERY	SIMPLE
접근 방식	ALL (users), range (orders)	range (orders), eq_ref (users)
병목 위치	users 테이블 전체 스캔	없음 (모두 인덱스 탐색)
인덱스 사용	부분 사용	전체 사용
실행 시간	3.1초	0.29초
개선율	-	약 10~11배 향상

🧪 실전 예제 2: SELECT절 N+1 서브쿼리 → JOIN 집계

리팩토링 전

SELECT name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

특징

• users row 수만큼 서브쿼리 반복
• N+1 쿼리 구조 → 병목
• orders 테이블 접근 횟수 폭발

EXPLAIN: Subquery 반복, rows: users × orders

실행 시간: 6.5초

리팩토링 후

SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

특징

• orders는 조인 후 한 번에 집계 처리
• group by로 병렬 계산 가능
• users row 수와 무관하게 orders 한 번만 접근

EXPLAIN: JOIN + GROUP BY, rows: users + orders
실행 시간: 0.48초
성능 개선율: 13배 이상

반응형

⚠️ 실행계획 분석 시 자주 나오는 경고 신호

• type = ALL: 전체 테이블 스캔
• type = DEPENDENT SUBQUERY: row마다 반복 실행
• Extra = Using temporary; Using filesort: 정렬, 임시테이블 발생 → 리팩토링 필요
• rows 수가 테이블 전체 row 수와 비슷하거나 더 크다면 인덱스 미활용

🔧 튜닝 시 반드시 체크할 요소

• JOIN 조건 컬럼에 인덱스가 있는가
• SELECT절에서 불필요한 서브쿼리를 쓰고 있지는 않은가
• 중복된 로직이 여러 곳에서 반복되지 않는가
• WHERE 절과 조인 조건이 옵티마이저가 활용할 수 있는 형태인가
• EXPLAIN rows 수와 실행 시간의 관계가 비정상적으로 벌어져 있는가

✅ 정리

• 리팩토링 후에도 반드시 EXPLAIN을 통해 실제 성능 개선 여부를 검증해야 한다.
• 단순 실행 시간보다도 type, key, rows, Extra 등의 실행계획 지표가 더 중요하다.
• 서브쿼리는 성능상 불리한 구조가 많고, JOIN과 인덱스를 활용하면 획기적인 개선이 가능하다.
• 반복 서브쿼리는 JOIN + GROUP BY 또는 CTE로 리팩토링하는 것이 좋다.
• 실행계획을 읽을 수 있는 능력이 곧 MySQL 튜닝 실력이다.

🔗 공식 문서 참고
MySQL 8.0 Reference Manual - EXPLAIN Output
MySQL 8.0 Reference Manual - ANALYZE Statement