[MySQL] CTE vs 서브쿼리 성능 비교 및 튜닝 포인트 🧠

MySQL 8.0부터 도입된 CTE(Common Table Expression)는 가독성과 재사용성이 뛰어나 서브쿼리를 대체하기에 충분한 문법이다.
하지만 CTE가 항상 서브쿼리보다 빠른 것은 아니다.
오히려 실수로 작성하면 서브쿼리보다 더 느려지는 병목을 초래할 수도 있다.

🔍 CTE와 서브쿼리의 내부 구조 차이

항목	CTE	서브쿼리
실행 시점	먼저 실행 후 임시 테이블로 저장	필요할 때마다 반복 실행 가능
재사용	동일 쿼리 내 여러 번 참조 가능	재사용 불가, 매번 새로 계산
인덱스 활용	거의 불가 (임시 테이블)	일부 가능 (옵티마이저 최적화 가능)
가독성	높음	중첩 구조로 가독성 낮음
튜닝 여지	제한적	옵티마이저가 다양한 방식으로 병합

CTE는 구조적으로 깨끗하고 유지보수 측면에서 강력하지만, 성능에서는 예상과 다른 결과를 낳을 수 있다.

반응형

🧪 성능 비교 예제: 단순 필터 쿼리

CTE 버전

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

• 실행 시간: 3.9초
• high_value_orders는 내부적으로 임시 테이블로 처리됨
• 인덱스 미활용 → full scan 발생

서브쿼리 버전

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

• 실행 시간: 0.84초
• 옵티마이저가 index_subquery로 변환하여 빠르게 처리함

결론: 단순 조건 필터일 경우는 서브쿼리가 더 유리한 경우가 많다.
MySQL 옵티마이저가 서브쿼리를 내부적으로 최적화할 수 있기 때문.

🧪 성능 비교 예제: 중복 로직 반복

서브쿼리 반복 버전

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

• 실행 시간: 4.8초
• N+1 쿼리 형태 → users 수만큼 orders 테이블 조회

CTE 버전

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

• 실행 시간: 0.47초
• orders를 한 번만 조회하고 재사용 → 성능 크게 향상

결론: 반복되는 서브쿼리는 CTE로 분리하면 훨씬 효율적이다.

반응형

⚠️ 성능 이슈가 생기는 CTE 패턴

• CTE 내부에 ORDER BY가 있고, 외부 쿼리에도 정렬이 존재할 때
• CTE 안에 LIMIT이 있지만 외부 쿼리가 전체 스캔을 유발하는 경우
• CTE가 큰 결과셋을 반환하고, 이를 조건 없이 전체 JOIN할 때

이런 구조에서는 옵티마이저가 CTE를 "풀어 쓰는" 식의 병합을 하지 않기 때문에 성능이 저하된다.

🔧 실무 튜닝 포인트

• 단순 필터, 단일 결과 서브쿼리는 JOIN 또는 IN으로 대체
• 여러 군데에서 반복적으로 쓰이는 서브쿼리는 CTE로 분리
• CTE 내부에 조건, 인덱스를 활용할 수 있는 컬럼을 포함
• CTE로 쪼갠 쿼리도 반드시 EXPLAIN으로 확인
• 복잡한 로직은 쿼리로 해결하기보다 뷰(View) 또는 프로시저로 나누는 것도 고려

✅ 정리

• CTE는 가독성과 구조화에는 강력하지만 성능에서는 항상 우위가 아니다.
• 단순한 조건이나 필터 쿼리는 서브쿼리가 더 빠를 수 있다.
• 반복적인 서브쿼리, 재귀 쿼리, 통계성 쿼리에는 CTE가 효과적이다.
• 성능 차이는 데이터량, 인덱스, 쿼리 구조에 따라 크게 달라지므로 항상 EXPLAIN과 실제 실행 시간을 비교해야 한다.

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