[MySQL] CTE 인라인과 구체화 판단법: WITH 쿼리의 반복 스캔 줄이기

728x90

데이터베이스를 다루다 보면 복잡한 서브쿼리를 읽기 쉽게 분리하거나 같은 중간 결과를 여러 곳에서 재사용하기 위해 공통 테이블 표현식(Common Table Expression, CTE)을 사용하게 됩니다. 이때 가장 흔히 하는 실수는 WITH 절을 작성하면 결과가 항상 한 번만 계산되어 저장된다고 생각하는 것입니다.

MySQL 옵티마이저는 CTE를 외부 쿼리 블록에 합치는 병합(Merge), 흔히 말하는 인라인 방식으로 처리할 수도 있고, 내부 임시 테이블에 저장하는 구체화(Materialization)를 선택할 수도 있습니다. 어느 쪽이 빠른지는 문법만으로 결정되지 않습니다. CTE 내부 연산의 비용, 외부 조건의 밀어 넣기 가능 여부, 참조 횟수, 구체화된 행 수를 함께 봐야 합니다. 이번 글에서는 MySQL 8.4를 기준으로 선택 조건과 실행 계획 확인법, 반복 스캔을 줄이기 위한 실측 절차까지 깊이 있게 다루어 보겠습니다.

1. CTE 인라인과 구체화의 동작 원리

병합은 CTE를 독립된 결과 집합으로 만들지 않고 CTE의 테이블과 조건을 외부 쿼리 블록 안으로 펼쳐 처리하는 방식입니다. 다음 쿼리의 recent_orders가 병합되면 옵티마이저는 사실상 orders에 created_at, customer_id 조건을 함께 적용할 수 있습니다.

WITH recent_orders AS (
    SELECT order_id, customer_id, created_at
    FROM orders
    WHERE created_at >= '2026-08-01'
)
SELECT order_id
FROM recent_orders
WHERE customer_id = 100;

외부 조건이 원본 테이블 접근 단계까지 내려가면 인덱스(Index)의 범위 검색(Range Scan) 후보가 넓어지고 불필요한 중간 행을 줄일 수 있습니다. 단일 참조이며 선택도가 높은 외부 조건이 있는 쿼리에서 병합이 유리한 대표적인 이유입니다.

반면 구체화는 CTE 결과를 내부 임시 테이블에 만든 뒤 외부 쿼리가 그 결과를 읽는 방식입니다. 생성 비용과 임시 결과 읽기 비용이 추가되지만, 하나의 CTE를 여러 번 참조할 때 선택된 구체화 결과는 쿼리 실행 중 한 번 만들어져 재사용될 수 있습니다. 따라서 비싼 집계나 필터 결과를 참조할 때 원본 작업의 반복을 피할 가능성이 생깁니다.

2. CTE가 병합되거나 구체화되는 조건

기본적으로 옵티마이저는 의미를 바꾸지 않고 병합할 수 있는 CTE를 비용 기반으로 판단합니다. 그러나 다음과 같이 결과 행의 개수나 의미를 먼저 확정해야 하는 연산이 CTE 안에 있으면 병합이 제한되고 구체화 대상이 됩니다.

SUM(), COUNT() 같은 집계 함수 또는 윈도 함수(Window Function)
DISTINCT, GROUP BY, HAVING
LIMIT
UNION 또는 UNION ALL
선택 목록의 서브쿼리나 원본 테이블이 없는 리터럴 전용 CTE 등 병합을 막는 구성

병합 가능한 구조라도 옵티마이저 힌트 MERGE(cte_name)와 NO_MERGE(cte_name)로 후보 선택에 영향을 줄 수 있습니다. 또한 optimizer_switch의 derived_merge 설정도 관련됩니다. 다만 힌트는 문법적·의미적으로 불가능한 병합을 가능하게 만드는 명령이 아닙니다. 예를 들어 GROUP BY가 포함된 CTE에 MERGE 힌트를 적어도 해당 제한 자체가 사라지지는 않습니다.

실무 팁: “한 번 참조하면 병합, 두 번 참조하면 구체화”처럼 참조 횟수를 절대 규칙으로 사용하면 안 됩니다. 참조 횟수는 비용 판단 요소이며, CTE 구조상 병합 가능 여부를 먼저 확인해야 합니다.

3. 어느 쪽이 빠른가: 비용을 나누어 판단하기

판단 항목	병합이 유리한 방향	구체화가 유리한 방향
CTE 참조 횟수	한 번 참조	여러 번 참조하며 같은 비싼 결과를 재사용
외부 조건	선택도가 높고 원본 접근까지 내려갈 수 있음	참조별 조건 차이가 작고 대부분의 중간 결과가 필요함
CTE 내부 작업	단순 필터·조인으로 재계산 비용이 낮음	집계·중복 제거 등 생성 비용이 큼
중간 결과 크기	구체화하면 많은 행과 넓은 컬럼을 저장해야 함	강한 필터로 작고 재사용 가치가 높은 결과가 생성됨
주요 위험	참조마다 원본 범위가 반복 스캔될 수 있음	임시 결과 생성·쓰기·읽기 비용이 추가될 수 있음

가장 중요한 점은 구체화가 무조건 반복 스캔을 제거하는 무료 캐시가 아니라는 점입니다. 필요한 열보다 넓은 CTE를 구체화하면 행 크기가 커지고, 외부 조건으로 대부분 버릴 행까지 저장할 수 있습니다. 반면 병합하면 참조별 조건을 원본 테이블에 적용할 수 있으므로 같은 CTE를 두 번 참조하더라도 서로 다른 좁은 인덱스 범위를 읽는 편이 더 저렴할 수 있습니다.

MySQL은 구체화를 선택한 경우에도 결과가 실제로 필요해질 때까지 생성을 늦출 수 있습니다. 앞선 조인 입력에서 결과가 없어 CTE를 읽을 필요가 사라지면 생성 자체를 피할 수 있습니다. 또한 구체화된 결과에 접근하기 위해 내부 인덱스를 자동으로 추가할 수 있습니다. 따라서 SQL 텍스트의 모양만 보고 임시 테이블 비용을 확정하지 말고 실행 계획을 확인해야 합니다.

4. EXPLAIN에서 병합과 구체화 확인하기

먼저 EXPLAIN FORMAT=TREE로 실행 연산의 계층을 확인하고, 이어 EXPLAIN ANALYZE로 실제 반복 횟수와 행 수를 확인하는 것이 안전합니다.

EXPLAIN FORMAT=TREE
WITH c AS (
    SELECT id, customer_id, amount
    FROM cte_orders
    WHERE status = 'PAID'
)
SELECT COUNT(*)
FROM c
WHERE customer_id BETWEEN 100 AND 199;

병합되었다면 계획에서 독립적인 CTE 임시 결과를 읽기보다 원본 cte_orders 접근과 조건이 외부 쿼리 안에 결합된 형태를 찾을 수 있습니다. 구체화되었다면 Materialize CTE, 임시 결과 스캔 또는 그와 연결된 연산을 확인합니다. 다만 전통적인 EXPLAIN의 Extra에 Using temporary가 없다는 이유만으로 CTE가 병합되었다고 단정해서는 안 됩니다. 계획의 전체 트리와 JSON 정보를 함께 봐야 합니다.

실행 계획을 계층적으로 읽는 방법은 EXPLAIN FORMAT=TREE 읽는 순서를, JSON의 필드 단위 확인은 EXPLAIN FORMAT=JSON 해석법을 함께 참고할 수 있습니다. 임시 결과가 커지는 원인은 Using temporary 진단 체크리스트의 점검 순서와도 연결됩니다.

5. 단일 참조와 반복 참조를 실제로 비교하는 절차

아래 비교는 동일한 데이터에서 병합 허용과 NO_MERGE 강제를 번갈아 실행해 옵티마이저 선택의 효과를 분리하는 방식입니다. 시간 하나만 기록하지 말고 계획 형태, 원본 스캔의 실제 반복 횟수, 반환 행 수를 함께 남겨야 합니다.

측정 케이스	실제 실행 시간
단일 참조 병합 허용	801
단일 참조 구체화 강제	801
반복 참조 병합 허용	1602
반복 참조 구체화 강제	1602

세션과 서버의 부하가 안정된 시간에 각 SELECT를 먼저 한 번 실행해 데이터 페이지 접근 조건을 맞춥니다.
각 케이스를 여러 차례 교차 실행합니다. 한 케이스를 모두 실행한 뒤 다음 케이스로 넘어가면 캐시와 동시 부하의 영향을 특정 방식에 몰아줄 수 있습니다.
각 SELECT 앞에 EXPLAIN ANALYZE를 붙여 실제 시간, 행 수, 반복 횟수를 별도로 기록합니다. 단, 계획 수집 자체의 비용이 있으므로 애플리케이션에서 측정한 SELECT 시간과 같은 값으로 취급하지 않습니다.
EXPLAIN FORMAT=TREE 또는 JSON 계획에서 CTE 구체화 연산과 원본 테이블 접근이 몇 번 나타나는지 확인합니다.
중앙값과 느린 구간을 함께 비교하고, 결과 행 수와 실행 계획이 동일한지 검증한 뒤 결론을 내립니다.

테스트 쿼리는 단일 참조에서는 외부의 좁은 customer_id 조건을 원본 접근에 결합할 수 있게 하고, 반복 참조에서는 같은 CTE를 서로 다른 별칭으로 두 번 읽게 구성합니다. 따라서 병합의 조건 밀어 넣기 이점과 구체화의 재사용 이점을 각각 관찰할 수 있습니다.

6. 실무에서 흔히 하는 실수와 경계 조건

6.1 CTE 안에 모든 컬럼을 넣는 실수

SELECT *로 작성한 CTE가 구체화되면 외부 쿼리에서 쓰지 않는 큰 문자열 컬럼까지 중간 결과에 포함될 수 있습니다. 필요한 조인 키, 필터 키, 최종 출력 컬럼만 선택하는 것이 더 안전합니다.

6.2 결과가 같은지만 확인하는 실수

두 방식이 같은 값을 반환해도 접근 경로는 크게 다를 수 있습니다. EXPLAIN ANALYZE의 실제 행 수가 예상 행 수와 크게 다르면 통계 정보나 데이터 분포 문제를 먼저 점검해야 합니다. 실행 계획의 type 한 항목만으로 성능을 판단하면 안 됩니다.

6.3 강제 힌트를 영구 처방으로 사용하는 실수

MERGE와 NO_MERGE는 원인을 분리하는 실험 도구로 먼저 사용해야 합니다. 데이터가 증가하거나 분포가 바뀌면 우세한 방식도 달라질 수 있기 때문입니다. 운영 쿼리에 힌트를 유지하려면 대표 데이터와 최대 부하 구간에서 재검증하고 MySQL 버전 변경 때도 계획을 비교해야 합니다.

6.4 비결정적 표현식과 부작용을 기대하는 실수

CTE를 절차형 변수나 영구 캐시처럼 취급하면 안 됩니다. 표현식 평가 횟수에 의존하는 설계는 병합 여부에 따라 예상과 다른 결과를 만들 여지가 있으므로, 현재 시각이나 난수처럼 평가 시점이 중요한 값은 별도로 확정한 뒤 전달하는 것이 더 안전합니다.

7. 최종 판단 기준

CTE가 병합되는 조건은 단순 필터·조인처럼 외부 쿼리에 의미 변화 없이 펼칠 수 있는 구조이고, 구체화되는 대표 조건은 집계, 윈도 함수, DISTINCT, GROUP BY, HAVING, LIMIT, 집합 연산처럼 병합을 제한하는 연산이 포함된 경우입니다. 병합 가능한 CTE에서는 옵티마이저 비용 판단, 관련 설정과 힌트가 최종 선택에 영향을 줍니다.

어느 쪽이 빠른가에 대한 답은 다음과 같습니다. 단일 참조이거나 외부의 선택도 높은 조건을 원본 테이블까지 밀어 넣을 수 있으면 병합이 유리할 가능성이 큽니다. 반면 생성 비용이 큰 작은 결과를 여러 번 참조하고 참조별 조건 차이가 작다면 한 번 구체화하여 재사용하는 방식이 유리할 수 있습니다. 하지만 참조 횟수만으로 선택해서는 안 되며, 강제 힌트로 양쪽 계획을 만든 뒤 실제 반복 횟수와 임시 결과 크기, 실행 시간을 함께 측정해야 합니다. 이 절차를 지키면 읽기 좋은 WITH 문법을 유지하면서도 반복 스캔을 효율적으로 줄일 수 있습니다.

측정 환경

이 글의 측정값은 로컬 개발 환경에서 직접 실행한 결과입니다. MySQL 8.4.3, InnoDB 버퍼 풀 128MB, 정렬 버퍼 256KB, 각 쿼리 3회 실행 후 중앙값입니다. 운영 서버의 사양과 데이터 분포에 따라 값은 달라집니다.