[MySQL] CTE(Common Table Expression) 개념과 성능 특성

MySQL 8.0부터 지원되기 시작한 CTE(Common Table Expression)는 복잡한 SQL 쿼리를 깔끔하게 작성할 수 있게 해주는 유용한 기능이다.
기존의 서브쿼리나 뷰(view)로 해결하던 부분을 WITH문으로 명시적으로 분리해 표현할 수 있기 때문에 가독성, 재사용성, 디버깅 편의성이 훨씬 좋아진다.

하지만 CTE는 무조건 성능이 좋은 문법은 아니다.
잘못 사용하면 오히려 성능이 하락하는 경우도 있기 때문에 개념과 내부 동작 원리를 정확히 이해한 후 사용해야 한다.

🧠 CTE란?

CTE(Common Table Expression)는 WITH 키워드를 사용하여 일시적인 결과 집합(가상의 테이블)을 정의하고, 이를 본 쿼리에서 참조하는 SQL 문법이다.
기존의 서브쿼리보다 더 명확하고 재사용 가능한 방식으로 쿼리를 구조화할 수 있다.

기본 문법

WITH recent_orders AS (
  SELECT * FROM orders WHERE order_date >= CURDATE() - INTERVAL 7 DAY
)
SELECT u.name, r.amount
FROM users u
JOIN recent_orders r ON u.id = r.user_id;

위 예제에서 recent_orders는 일시적인 테이블처럼 사용된다.
일반 서브쿼리보다 코드 가독성이 높고, 여러 곳에서 참조하거나 재귀적 호출도 가능하다.

반응형

🔄 CTE vs 서브쿼리 vs 뷰

• 서브쿼리는 쿼리 내부에 중첩되므로 가독성이 떨어지고 반복되면 유지보수가 어려움
• 뷰는 데이터베이스 객체로 등록되지만, 재사용은 가능해도 동적 구조에 제약이 있음
• CTE는 쿼리 내에서 선언되고, 명확한 이름 지정이 가능하며 반복적으로 재사용 가능

⚙️ CTE의 성능 특성

CTE는 문법적으로 강력하지만 MySQL에서는 항상 성능이 좋은 것은 아니다.

✅ 1. CTE는 내부적으로 임시 테이블로 처리된다

MySQL은 CTE를 처리할 때 기본적으로 내부에서 임시 테이블을 생성하여 데이터를 저장한 후 참조한다.
이로 인해 인덱스를 사용하지 못하고, 메모리 또는 디스크에 저장된 데이터를 순차적으로 읽게 된다.

WITH active_users AS (
  SELECT id FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE id > 100;

위 쿼리에서 active_users는 옵티마이저가 인덱스를 사용할 수 없게 된다.
결과적으로 WHERE 조건이 있어도 풀스캔이 발생할 수 있다.

✅ 2. CTE는 옵티마이저 머지 최적화가 어렵다

일반적인 서브쿼리는 MySQL 옵티마이저가 내부적으로 합치거나 재배치하는 등의 최적화를 수행할 수 있다.
하지만 CTE는 명시적인 구조가 고정되어 있어 쿼리 병합, 조건 이동 등의 최적화가 제한된다.

즉, 쿼리 작성은 더 명확해지지만, 성능은 떨어질 가능성이 생긴다.

✅ 3. CTE는 재귀 쿼리에도 사용 가능

재귀 CTE는 계층형 데이터 구조(예: 카테고리, 조직도 등)를 처리할 때 매우 유용하다.
단, 재귀 CTE는 실수하면 무한 루프에 빠질 수 있으므로 LIMIT 또는 종료 조건을 반드시 포함해야 한다.

예시:

WITH RECURSIVE category_path (id, name, parent_id) AS (
  SELECT id, name, parent_id
  FROM categories
  WHERE id = 1
  UNION ALL
  SELECT c.id, c.name, c.parent_id
  FROM categories c
  JOIN category_path cp ON cp.id = c.parent_id
)
SELECT * FROM category_path;

반응형

🧪 실무 예시: CTE vs 서브쿼리 성능 비교

서브쿼리 버전

SELECT u.name
FROM users u
WHERE u.id IN (
  SELECT user_id FROM orders WHERE amount > 100
);

• 실행 시간: 2.4초
• 서브쿼리는 중첩 구조이지만 옵티마이저가 인덱스를 활용할 수 있는 구조로 변환 가능

CTE 버전

WITH big_orders AS (
  SELECT user_id FROM orders WHERE amount > 100
)
SELECT u.name
FROM users u
JOIN big_orders b ON u.id = b.user_id;

• 실행 시간: 4.8초
• CTE 내부 결과를 임시 테이블로 만들기 때문에 조인 시 인덱스를 사용하지 못함

→ 단순한 조건에서는 CTE보다 서브쿼리나 JOIN이 더 나은 성능을 보일 수 있음

💡 실무 팁

• CTE는 복잡한 쿼리 분할, 가독성 개선, 재귀적 구조 표현에 유용
• 단순 필터링, row 수가 많은 집합에는 성능상 불리할 수 있음
• EXPLAIN으로 Using temporary가 표시되면 성능에 주의
• CTE 안에 인덱스가 적용되는지 여부를 꼭 검토해야 함
• 반복 참조보다는 딱 1~2회 사용하는 구조에서 효과적

✅ 정리

• CTE는 WITH 키워드로 정의된 임시 결과 테이블이며, 쿼리 구조를 깔끔하게 분리하고 재사용 가능하게 만든다.
• MySQL 8.0부터 공식 지원되며, 가독성은 뛰어나지만 성능 면에서는 주의가 필요하다.
• 내부적으로 임시 테이블을 생성하므로 단순 필터나 대량 데이터 조합에선 성능이 떨어질 수 있다.
• 재귀 쿼리나 다단계 서브쿼리를 처리할 땐 CTE가 매우 유용하며, 디버깅이나 유지보수 측면에서도 강점이 많다.

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