[MySQL] 복잡한 쿼리 리팩토링: 서브쿼리 → JOIN → CTE 변환 사례 분석

초기에 작성한 SQL 쿼리는 돌아가기만 하면 OK라는 기준으로 작성되는 경우가 많다.
하지만 데이터가 많아지고 비즈니스 로직이 복잡해질수록 이전엔 문제없던 쿼리가 갑자기 느려지거나 병목이 발생하는 상황이 생긴다.
이럴 때 필요한 것이 바로 쿼리 리팩토링이다.

특히 서브쿼리로 작성된 복잡한 SQL을 JOIN이나 CTE로 변환하면 성능과 가독성, 유지보수성까지 개선되는 효과가 크다.

🧪 초기 쿼리: 중첩 서브쿼리 구조

SELECT name FROM users
WHERE id IN (
  SELECT user_id FROM orders
  WHERE id IN (
    SELECT order_id FROM refunds WHERE reason = 'delay'
  )
);

위 쿼리는 사용자 중 '배송 지연' 사유로 환불한 이력이 있는 유저를 찾는 쿼리다.
단순해 보이지만 서브쿼리 안에 또 서브쿼리가 중첩된 구조이며, 실행 시 서브쿼리가 모두 따로 수행되기 때문에 인덱스를 사용하기 어렵고 성능 저하가 크다.

실행 시간: 약 3.6초 (데이터 50만 건 기준)
EXPLAIN 결과: type = DEPENDENT SUBQUERY, rows = 수천 ~ 수만

반응형

🔁 리팩토링 1단계: JOIN으로 변환

SELECT DISTINCT u.name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN refunds r ON o.id = r.order_id
WHERE r.reason = 'delay';

단순하게 서브쿼리를 모두 JOIN으로 바꿨을 뿐인데도 성능은 급격히 향상된다.
JOIN 조건에 인덱스가 적절히 설정돼 있다면 각 테이블을 효율적으로 탐색할 수 있다.

실행 시간: 0.42초
장점: 인덱스 활용 가능, 옵티마이저 최적화 용이
주의: 중복 사용자 발생 가능 → DISTINCT 추가

📦 리팩토링 2단계: CTE 기반 구조화

WITH delayed_refunds AS (
  SELECT order_id FROM refunds WHERE reason = 'delay'
),
matched_orders AS (
  SELECT user_id FROM orders
  WHERE id IN (SELECT order_id FROM delayed_refunds)
)
SELECT name FROM users
WHERE id IN (SELECT user_id FROM matched_orders);

위 쿼리는 실제 성능 면에서는 JOIN보다는 느리지만, 가독성과 디버깅 편의성이 크게 향상된다.
특히 쿼리 로직이 길어질 경우 CTE를 이용한 단계적 설계가 유지보수에 매우 유리하다.

실행 시간: 0.83초
장점: 논리적 단계 분리, 테스트 편리
주의: CTE는 임시 테이블처럼 처리되어 인덱스 비활용 가능성 존재

반응형

💡 리팩토링 포인트 요약

방식	장점	단점	추천 상황
서브쿼리	작성 간단	성능 최악 (N+1, 중첩), 인덱스 미활용	단일 조건, 테스트용 간단 쿼리
JOIN	성능 우수, 인덱스 활용 가능	복잡해지면 가독성 낮아짐	실무 전용 성능 최적화 쿼리
CTE	가독성, 유지보수성 탁월	성능 저하 가능	복잡 로직, 재귀, 분석 목적 쿼리

 

실무에서는 JOIN을 기본으로 두고, 구조 복잡성에 따라 CTE를 보조적으로 활용하는 전략이 가장 일반적이다.
또한 모든 리팩토링 후에는 반드시 EXPLAIN 또는 실제 실행 시간 비교가 필요하다.

✅ 정리

• 서브쿼리는 중첩될수록 성능이 급격히 저하되며, JOIN 또는 CTE로의 변환이 필요하다.
• JOIN은 인덱스를 활용할 수 있어 성능 면에서 가장 유리하다.
• CTE는 구조화에 강하지만, 임시 테이블 처리로 인해 성능이 떨어질 수 있다.
• 가독성과 성능의 균형을 맞추는 것이 리팩토링의 핵심이다.

🔗 공식 문서 참고
MySQL 8.0 Reference Manual - WITH (Common Table Expressions)
MySQL 8.0 Reference Manual - Join Optimization