Derived table과 CTE 최적화: materialization과 merge의 차이
MySQL이 derived table과 CTE를 merge하거나 materialize하는 조건, 실행 비용, 진단 방법을 운영 관점에서 정리한다.
FROM 절의 derived table과 WITH 절의 CTE(Common Table Expression)는 복잡한 SQL을 읽기 쉬운 단계로 나누는 유용한 문법이다. 그러나 SQL을 여러 블록으로 나누었다고 해서 MySQL이 반드시 각 블록의 결과를 먼저 만들어 두는 것은 아니다. 옵티마이저는 중간 결과를 바깥 query block에 합치는 merge와 내부 임시 테이블로 만드는 materialization 가운데 실행 비용이 낮다고 판단한 방식을 선택한다.
이 선택은 단순한 표기 차이가 아니다. 조건이 원본 테이블까지 내려가는지, 조인 순서를 얼마나 자유롭게 바꿀 수 있는지, 중간 결과를 몇 번 계산하는지, 메모리와 임시 디스크를 얼마나 사용하는지가 달라진다. 운영 중 “CTE로 바꾼 뒤 갑자기 느려졌다”거나 “같은 CTE를 여러 번 참조했는데 예상보다 빠르다”는 현상을 설명하려면 두 실행 방식의 내부 경로를 이해해야 한다.
이 글의 예제는 MySQL 8.0을 기준으로 한다. minor version, 통계, 데이터 분포에 따라 비용 추정치와 세부 계획은 달라질 수 있으므로 EXPLAIN의 숫자보다 Materialize, Table scan on ..., 원본 테이블 접근 조건 같은 구조를 중심으로 읽는다.
1. Derived table과 CTE는 문법 경계이지 항상 실행 경계는 아니다
Derived table은 FROM (SELECT ...) AS d 형태의 이름 있는 중간 query block이다. CTE는 같은 구조를 WITH name AS (...)로 선언하고 본문에서 참조한다. 두 문법 모두 SQL 작성자에게는 명확한 단계처럼 보이지만, 옵티마이저는 그 경계를 그대로 보존할 수도 있고 없앨 수도 있다.
flowchart TD
Q[Derived table 또는 CTE가 있는 SQL] --> P[파싱과 query block 구성]
P --> E{merge 가능한 구조인가}
E -- 아니오 --> M[Materialization 후보]
E -- 예 --> C{힌트·optimizer_switch·비용 판단}
C -- merge --> G[바깥 query block에 결합]
C -- materialization --> M
G --> R[조건 전파·조인 순서·접근 경로 재탐색]
M --> D[필요 시 내부 임시 테이블 생성]
D --> I[선택적 자동 인덱스 구성 가능]
R --> X[실행]
I --> X
1.1 Merge
Merge가 선택되면 derived table 또는 CTE의 query block 경계가 논리적으로 풀리고, 내부 테이블이 바깥 query block의 테이블처럼 최적화된다. 뷰를 ALGORITHM=MERGE로 처리하는 개념과 비슷하다.
Merge의 주요 효과는 다음과 같다.
- 바깥 조건을 원본 테이블 접근 조건으로 사용할 수 있다.
- derived table 안팎의 테이블을 함께 고려해 조인 순서를 정할 수 있다.
- 원본 인덱스를 직접 선택할 수 있다.
- 별도의 중간 결과를 만들고 읽는 비용을 피할 수 있다.
- 같은 논리 블록이 여러 위치에 펼쳐지면 각 참조 위치에서 실행 경로가 달라질 수 있다.
Merge를 “subquery SQL 문자열을 단순히 복사한다”라고만 이해하면 부족하다. 핵심은 옵티마이저가 더 넓은 query block에서 predicate, join order, access path를 함께 탐색할 수 있게 된다는 점이다.
1.2 Materialization
Materialization이 선택되면 MySQL은 derived table 또는 CTE의 결과를 내부 임시 테이블에 저장하고, 바깥 query block은 그 임시 테이블을 하나의 입력으로 읽는다.
이 방식에는 쓰기와 재읽기 비용이 있지만 다음 장점도 있다.
- 집계,
DISTINCT,UNION처럼 query block의 의미적 경계를 보존할 수 있다. - 복잡한 결과를 한 번 계산한 뒤 여러 번 참조할 수 있다.
- 필요하면 임시 테이블에 자동 인덱스를 만들어 바깥쪽 lookup 비용을 줄일 수 있다.
- 실행 중 해당 입력이 필요 없다는 사실이 먼저 확정되면 생성을 늦추거나 피할 수 있다.
Materialization을 항상 “먼저 전체 결과를 만든 뒤 시작하는 eager 실행”으로 보면 안 된다. MySQL은 materialization을 실행 시점까지 지연할 수 있다. 예를 들어 조인의 선행 입력이 빈 결과를 반환하면 뒤의 derived table을 만들 필요가 없을 수 있다.
2. Merge 가능 여부를 결정하는 의미적 경계
옵티마이저는 결과 의미를 바꾸지 않는 범위에서만 query block을 합칠 수 있다. 다음 구조는 일반적으로 merge를 막고 materialization을 요구한다.
SUM(),COUNT()같은 aggregate function과GROUP BY- window function
DISTINCTHAVINGLIMITUNION또는UNION ALL- select list의 subquery
- 사용자 변수에 값을 대입하는 표현식
- 원본 테이블 없이 literal 값만 선택하는 query block
예를 들어 고객별 매출 합계를 계산한 뒤 paid_total >= 1000을 적용하는 SQL에서 집계 전 주문 행에 같은 조건을 직접 적용하면 의미가 달라진다. 따라서 집계 결과라는 경계는 보존되어야 한다.
다만 materialization되면 조건 최적화가 전혀 일어나지 않는다는 설명도 정확하지 않다. MySQL 8.0의 derived condition pushdown은 안전한 조건을 derived table 안으로 밀어 넣을 수 있다. 집계 결과 컬럼에 대한 조건은 HAVING으로 내려갈 수 있고, grouping column에 대한 조건은 더 안쪽의 WHERE까지 내려갈 수 있다. 실제 적용 여부는 SQL 구조와 버전에 따라 확인해야 한다.
3. 재현 환경 만들기
다음 축소 데이터는 실행 시간 비교용 benchmark가 아니라 계획 구조를 관찰하기 위한 예제다. 고객 네 명과 주문 열두 건을 만들고 통계를 갱신한다.
DROP TABLE IF EXISTS dt_orders;
DROP TABLE IF EXISTS dt_customers;
CREATE TABLE dt_customers (
customer_id BIGINT NOT NULL PRIMARY KEY,
customer_name VARCHAR(40) NOT NULL,
customer_tier VARCHAR(10) NOT NULL,
KEY ix_tier (customer_tier)
) ENGINE=InnoDB;
CREATE TABLE dt_orders (
order_id BIGINT NOT NULL PRIMARY KEY,
customer_id BIGINT NOT NULL,
status VARCHAR(12) NOT NULL,
amount DECIMAL(10,2) NOT NULL,
ordered_at DATETIME NOT NULL,
KEY ix_customer_status (customer_id, status),
KEY ix_status_amount (status, amount)
) ENGINE=InnoDB;
INSERT INTO dt_customers VALUES
(1, '고객-1', 'GOLD'),
(2, '고객-2', 'SILVER'),
(3, '고객-3', 'GOLD'),
(4, '고객-4', 'BRONZE');
INSERT INTO dt_orders VALUES
(101, 1, 'PAID', 120.00, '2026-07-01 09:00:00'),
(102, 1, 'PAID', 80.00, '2026-07-02 09:00:00'),
(103, 1, 'CANCELLED', 200.00, '2026-07-03 09:00:00'),
(104, 2, 'PAID', 50.00, '2026-07-01 10:00:00'),
(105, 2, 'PAID', 150.00, '2026-07-02 10:00:00'),
(106, 2, 'SHIPPED', 300.00, '2026-07-03 10:00:00'),
(107, 3, 'PAID', 400.00, '2026-07-01 11:00:00'),
(108, 3, 'PAID', 100.00, '2026-07-02 11:00:00'),
(109, 3, 'CANCELLED', 90.00, '2026-07-03 11:00:00'),
(110, 4, 'PAID', 20.00, '2026-07-01 12:00:00'),
(111, 4, 'SHIPPED', 70.00, '2026-07-02 12:00:00'),
(112, 4, 'PAID', 30.00, '2026-07-03 12:00:00');
ANALYZE TABLE dt_customers, dt_orders;
SELECT COUNT(*) AS customer_count FROM dt_customers;
SELECT COUNT(*) AS order_count FROM dt_orders;
실행 결과(MySQL 8.0.x):
mysql> CREATE TABLE dt_customers (...);
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE dt_orders (...);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO dt_customers VALUES (...);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> INSERT INTO dt_orders VALUES (...);
Query OK, 12 rows affected (0.00 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> ANALYZE TABLE dt_customers, dt_orders;
+------------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------------------+---------+----------+----------+
| mysql_tech_note.dt_customers | analyze | status | OK |
| mysql_tech_note.dt_orders | analyze | status | OK |
+------------------------------+---------+----------+----------+
2 rows in set (0.00 sec)
mysql> SELECT COUNT(*) AS customer_count FROM dt_customers;
+----------------+
| customer_count |
+----------------+
| 4 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) AS order_count FROM dt_orders;
+-------------+
| order_count |
+-------------+
| 12 |
+-------------+
1 row in set (0.00 sec)
작은 데이터에서는 table scan이 index lookup보다 싸게 선택될 수 있다. 따라서 아래 예제의 목적은 운영 성능 수치를 재현하는 것이 아니라 query block이 사라지는지, Materialize iterator가 나타나는지를 확인하는 데 있다.
4. Merge 계획: 조건과 조인을 한 query block에서 최적화한다
다음 derived table에는 merge를 막는 집계나 DISTINCT, LIMIT이 없다. NO_MERGE 힌트도 없으므로 옵티마이저는 내부 쿼리를 바깥 query block에 합칠 수 있다.
EXPLAIN FORMAT=TREE
SELECT c.customer_name, d.order_id, d.amount
FROM dt_customers AS c
JOIN (
SELECT order_id, customer_id, amount
FROM dt_orders
WHERE status = 'PAID'
) AS d
ON d.customer_id = c.customer_id
WHERE c.customer_tier = 'GOLD'
AND d.amount >= 100.00;
실행 결과(MySQL 8.0.x):
mysql> EXPLAIN FORMAT=TREE
-> SELECT c.customer_name, d.order_id, d.amount ...;
-> Nested loop inner join (cost=1.75 rows=1)
-> Index lookup on c using ix_tier (customer_tier='GOLD')
(cost=0.7 rows=2)
-> Filter: (dt_orders.amount >= 100.00)
(cost=0.4 rows=0.5)
-> Index lookup on dt_orders using ix_customer_status
(customer_id=c.customer_id, status='PAID')
(cost=0.4 rows=1.5)
1 row in set (0.01 sec)
계획에서 Materialize와 derived table d에 대한 별도 scan이 보이지 않고, dt_orders와 dt_customers가 직접 조인되면 merge된 것이다. status = 'PAID', amount >= 100.00, customer_tier = 'GOLD' 조건도 하나의 탐색 공간에서 접근 경로 선택에 사용된다.
운영 SQL에서 이 방식이 유리한 대표 상황은 다음과 같다.
- 바깥 조건이 매우 선택적이고 원본 인덱스 범위를 크게 줄일 수 있다.
- derived table 안팎의 조인 순서를 함께 바꾸는 것이 중요하다.
- 중간 결과를 한 번만 사용하며 별도 저장의 이점이 없다.
- 원본 테이블의 covering index를 직접 활용할 수 있다.
하지만 query block이 너무 많이 합쳐지면 조인 탐색 공간이 커질 수 있다. 또한 CTE를 여러 번 참조할 때 매번 merge되면 같은 논리 계산이 참조별로 반복될 수 있다. 그러므로 “merge가 항상 빠르다”가 아니라 조건 전파와 조인 재배치의 이익이 중간 결과 생성·재사용의 이익보다 큰가를 판단해야 한다.
5. NO_MERGE로 materialization 경로 관찰하기
같은 구조에 NO_MERGE(d) optimizer hint를 적용하면 query block의 경계를 보존하도록 요청할 수 있다. 힌트는 교육용 비교와 제한적인 장애 회피에 유용하지만, 영구적인 첫 해법으로 사용해서는 안 된다.
EXPLAIN FORMAT=TREE
SELECT /*+ NO_MERGE(d) */
c.customer_name, d.order_id, d.amount
FROM dt_customers AS c
JOIN (
SELECT order_id, customer_id, amount
FROM dt_orders
WHERE status = 'PAID'
) AS d
ON d.customer_id = c.customer_id
WHERE c.customer_tier = 'GOLD'
AND d.amount >= 100.00;
실행 결과(MySQL 8.0.x):
mysql> EXPLAIN FORMAT=TREE
-> SELECT /*+ NO_MERGE(d) */ ...;
-> Nested loop inner join (cost=2.5 rows=8)
-> Index lookup on c using ix_tier (customer_tier='GOLD')
(cost=0.7 rows=2)
-> Index lookup on d using <auto_key0> (customer_id=c.customer_id)
(cost=2.76..3.06 rows=2)
-> Materialize (cost=2.46..2.46 rows=4)
-> Index range scan on dt_orders using ix_status_amount over
(status = 'PAID' AND 100.00 <= amount),
with index condition: ((dt_orders.`status` = 'PAID')
and (dt_orders.amount >= 100.00))
(cost=2.06 rows=4)
1 row in set (0.00 sec)
이 계획에서는 derived table을 위한 Materialize iterator와 그 결과를 읽는 scan이 나타나야 한다. MySQL은 바깥 조건 가운데 안전한 조건을 안쪽으로 pushdown할 수 있으므로, materialization되었다는 사실만으로 모든 PAID 주문이 무조건 중간 결과에 저장된다고 단정하면 안 된다.
MERGE(d)와 NO_MERGE(d)는 비용 모델을 대체하는 강한 운영 정책이 아니라 특정 query block을 진단하고 계획 회귀를 완화하는 수단으로 다루는 편이 안전하다. 힌트를 배포할 때는 다음을 함께 기록한다.
- 힌트 없이 선택된 기존 계획
- 힌트를 넣은 이유와 대상 MySQL minor version
- 데이터 증가와 분포 변화 후 재검토 조건
EXPLAIN ANALYZE로 확인한 실제 iterator 행 수와 시간- 업그레이드 전 회귀 시험 항목
6. 집계 derived table: 의미상 materialization이 필요한 경우
고객별 결제 합계를 만든 뒤 고객 테이블과 조인하면 GROUP BY가 merge를 막는다. 이때 materialization은 단순한 옵티마이저 취향이 아니라 집계 결과의 의미적 경계를 보존하는 실행 방식이다.
EXPLAIN FORMAT=TREE
SELECT c.customer_name, s.paid_count, s.paid_total
FROM dt_customers AS c
JOIN (
SELECT customer_id,
COUNT(*) AS paid_count,
SUM(amount) AS paid_total
FROM dt_orders
WHERE status = 'PAID'
GROUP BY customer_id
) AS s
ON s.customer_id = c.customer_id
WHERE s.paid_total >= 200.00
ORDER BY s.paid_total DESC;
실행 결과(MySQL 8.0.x):
mysql> EXPLAIN FORMAT=TREE
-> SELECT c.customer_name, s.paid_count, s.paid_total ...;
-> Sort: s.paid_total DESC
-> Stream results (cost=2.65 rows=0)
-> Nested loop inner join (cost=2.65 rows=0)
-> Table scan on c (cost=0.65 rows=4)
-> Index lookup on s using <auto_key0>
(customer_id=c.customer_id)
-> Materialize
-> Filter: (sum(dt_orders.amount) >= 200.00)
-> Table scan on <temporary>
-> Aggregate using temporary table
-> Index lookup on dt_orders
using ix_status_amount (status='PAID')
(cost=1.3 rows=8)
1 row in set (0.00 sec)
계획은 대체로 dt_orders를 읽어 고객별로 aggregate한 결과를 materialize하고, 바깥 query block에서 고객과 조인한다. paid_total >= 200.00 조건은 derived condition pushdown에 의해 집계 단계의 HAVING과 유사한 위치로 이동할 수 있다. ORDER BY를 만족할 적절한 순서가 없으면 마지막에 sort가 추가된다.
중간 결과가 원본보다 작기 때문에 materialization이 오히려 유리할 수도 있다. 수천만 주문을 고객별 수십만 행으로 축약한 뒤 다른 테이블과 조인한다면, 중간 테이블 쓰기 비용을 지불하고도 후속 조인의 입력량이 크게 줄어든다. 반대로 집계 결과가 거의 원본 크기와 같고 임시 테이블이 디스크로 spill하면 비용이 커진다.
7. CTE를 여러 번 참조할 때의 재사용
Materialized CTE는 한 statement 안에서 여러 번 참조되어도 CTE 결과 자체는 한 번 materialize하여 재사용할 수 있다. 이것이 각 참조가 독립된 derived table인 경우와 구별되는 중요한 특성이다.
다음 SQL은 고객별 결제 요약 CTE를 본문 조인과 scalar subquery에서 각각 참조한다. GROUP BY가 있으므로 CTE는 merge할 수 없다.
EXPLAIN FORMAT=TREE
WITH paid_summary AS (
SELECT customer_id,
COUNT(*) AS paid_count,
SUM(amount) AS paid_total
FROM dt_orders
WHERE status = 'PAID'
GROUP BY customer_id
)
SELECT c.customer_name,
s.paid_count,
s.paid_total
FROM dt_customers AS c
JOIN paid_summary AS s
ON s.customer_id = c.customer_id
WHERE s.paid_total > (
SELECT AVG(a.paid_total)
FROM paid_summary AS a
)
ORDER BY s.paid_total DESC;
실행 결과(MySQL 8.0.x):
mysql> EXPLAIN FORMAT=TREE
-> WITH paid_summary AS (...) SELECT ...;
-> Sort: s.paid_total DESC
-> Stream results (cost=3.45 rows=2.67)
-> Nested loop inner join (cost=3.45 rows=2.67)
-> Table scan on c (cost=0.65 rows=4)
-> Filter: (s.paid_total > (select #3))
-> Index lookup on s using <auto_key0>
(customer_id=c.customer_id)
-> Materialize CTE paid_summary if needed
-> Table scan on <temporary>
-> Aggregate using temporary table
-> Index lookup on dt_orders
using ix_status_amount (status='PAID')
(cost=1.3 rows=8)
-> Select #3 (subquery in condition; run only once)
-> Aggregate: avg(a.paid_total)
-> Table scan on a
-> Materialize CTE paid_summary if needed
(query plan printed elsewhere)
1 row in set (0.00 sec)
계획에는 CTE를 처음 만드는 materialization 경로와 이미 만들어진 CTE를 다시 읽는 경로가 함께 나타날 수 있다. 옵티마이저는 참조별 접근 패턴에 맞춰 materialized CTE에 서로 다른 자동 인덱스를 고려할 수도 있다. 따라서 여러 번 참조한다는 이유만으로 매번 같은 전체 scan이 반복된다고 단정해서는 안 된다.
반대로 merge 가능한 CTE가 한 번만 참조되고 바깥 조건의 선택도가 높다면 merge가 더 자연스럽다. CTE 문법 자체는 “한 번 계산해 저장한다”는 성능 계약이 아니다. 결과 재사용이 반드시 필요하다면 실제 계획을 확인하고, 필요할 때만 힌트 또는 merge를 막는 의미 있는 query 구조를 검토해야 한다. 단지 materialization을 강제하려고 의미 없는 LIMIT이나 DISTINCT를 추가하는 방식은 SQL 의도를 흐리고 이후 최적화를 방해하므로 피한다.
8. 내부 임시 테이블의 비용을 어떻게 해석할 것인가
Materialized 결과는 MySQL 내부 임시 테이블에 저장된다. MySQL 8.0의 기본 internal temporary table engine은 일반적으로 TempTable이며, 허용된 메모리 범위를 넘거나 저장할 수 없는 데이터 조건을 만나면 디스크 기반 저장으로 전환될 수 있다.
운영 영향은 다음 네 단계로 나누어 본다.
8.1 생성할 행 수와 row width
중간 결과가 10만 행인지 1천만 행인지뿐 아니라 각 행에 어떤 컬럼을 담는지도 중요하다. 필요하지 않은 큰 TEXT/JSON 컬럼을 CTE select list에 포함하면 메모리와 복사 비용이 커진다. “나중에 쓸 수도 있다”는 이유로 SELECT *를 사용하는 대신 후속 단계에 필요한 컬럼만 선택한다.
8.2 중복 제거·집계·정렬 비용
DISTINCT, GROUP BY, window function, UNION은 materialization을 유발할 뿐 아니라 자체 연산 비용도 가진다. 실행 계획에서 임시 테이블이라는 표지만 찾지 말고 그 앞의 aggregate, sort, deduplication iterator가 몇 행을 처리하는지 확인해야 한다.
8.3 메모리와 디스크 spill
동시 실행이 많으면 query 하나의 작은 임시 테이블도 전체 인스턴스에서는 큰 메모리 사용량이 된다. 반대로 메모리 한도를 무작정 늘리면 동시성 높은 시점에 메모리 압박이 커질 수 있다. Created_tmp_tables, Created_tmp_disk_tables 같은 status counter는 방향을 보는 보조 지표이지 특정 SQL 한 건의 원인을 직접 증명하지 않는다. Performance Schema statement digest, 실행 계획, 세션 단위 재현을 함께 사용한다.
8.4 재사용과 자동 인덱스의 이익
중간 결과를 여러 번 읽거나 원본보다 크게 축약한다면 쓰기 비용을 상쇄할 수 있다. MySQL이 materialized 결과에 자동 인덱스를 구성하는 비용도 일반적으로 ref lookup으로 얻는 이익과 비교해 결정된다. 자동 인덱스가 보인다는 이유만으로 원본 schema에 같은 인덱스를 즉시 추가해서는 안 된다. 원본 인덱스와 임시 결과 인덱스는 행 수, 컬럼 집합, 수명이 다르다.
9. 실행 계획에서 확인할 항목
Traditional EXPLAIN과 FORMAT=TREE는 서로 보완적으로 사용한다.
9.1 Traditional EXPLAIN
select_type=DERIVED가 나타나는지 확인한다.<derivedN>처럼 materialized 결과를 가리키는 table 이름을 찾는다.key,ref,rows,Extra에서 중간 결과의 접근 방식을 본다.- merge되었다면 derived table alias가 사라지고 원본 테이블이 바깥 query block에 직접 나타날 수 있다.
9.2 EXPLAIN FORMAT=TREE
Materializeiterator의 존재 여부를 확인한다.- materialize하기 전에 filter, aggregate, sort가 몇 행을 전달할 것으로 추정되는지 본다.
- materialized 결과를 scan하는지 index lookup하는지 확인한다.
- 같은 CTE를 재사용하는 표지가 있는지 살펴본다.
9.3 EXPLAIN ANALYZE
EXPLAIN ANALYZE는 statement를 실제 실행하며 각 iterator의 실제 시간, rows, loops를 보여준다. 다음 차이를 찾는 데 유용하다.
- 추정 중간 결과와 실제 중간 결과의 차이
- materialization 생성 시간과 후속 read 시간
- CTE 참조별 loop 횟수
- 조건 pushdown 전후의 실제 처리 행 수
- sort와 aggregate에서 예상 밖으로 커진 입력
운영 대용량 SQL에 무분별하게 실행해서는 안 된다. 읽기 전용 문장이라도 실제로 전체 scan, sort, materialization을 수행하므로 staging이나 대표 데이터 복제본에서 먼저 확인한다.
10. 제어 수단과 적용 우선순위
MySQL은 optimizer_switch의 derived_merge 플래그와 MERGE/NO_MERGE optimizer hint를 제공한다. 그러나 적용 순서는 다음처럼 보수적으로 잡는 것이 좋다.
- 통계와 실제 데이터 분포가 올바른지 확인한다.
- 바깥 조건과 조인 조건이 sargable한지 확인한다.
- CTE/derived table이 불필요하게 넓은 행을 만드는지 줄인다.
EXPLAIN ANALYZE로 merge와 materialization의 실제 비용을 비교한다.- SQL 의미를 명확히 유지하면서 query block을 재구성한다.
- 마지막으로 범위가 좁은 query block hint를 검토한다.
- 전역
derived_merge변경은 충분한 회귀 시험 후에만 고려한다.
전역 설정은 기존 workload 전체의 뷰, derived table, CTE 계획을 바꿀 수 있다. 특정 SQL 한 건의 회귀 때문에 인스턴스 전체에서 merge를 끄는 것은 영향 범위가 지나치게 넓다. 가능하면 statement 단위 힌트와 query rewrite로 문제 범위를 제한한다.
또한 힌트가 문법상 허용되었다고 해서 반드시 적용된 것은 아니다. 의미상 merge할 수 없는 query block에 MERGE를 요청하면 옵티마이저는 그 요청을 따를 수 없다. SHOW WARNINGS와 실행 계획으로 힌트의 적용 여부를 확인한다.
11. 흔한 오해와 장애 패턴
11.1 “CTE는 항상 한 번 계산된다”
CTE가 merge되면 별도 결과가 저장되지 않는다. 여러 참조가 각 query block에 합쳐지면 원본 접근이 참조별로 나타날 수 있다. CTE의 가독성 이점과 materialization 재사용 여부를 분리해서 판단한다.
11.2 “Materialization은 항상 느리다”
집계로 입력을 크게 줄이거나 여러 참조가 같은 결과를 재사용하면 더 빠를 수 있다. 조인 선행 입력이 비어 있을 때 delayed materialization으로 생성 자체를 피할 가능성도 있다.
11.3 “Merge되면 모든 조건이 최적으로 내려간다”
표현식이 non-sargable하거나 암시적 형 변환이 있거나 통계가 부정확하면 query block이 합쳐져도 좋은 인덱스 접근이 보장되지 않는다. merge 가능성과 접근 경로 품질은 별도 문제다.
11.4 “임시 테이블 counter가 증가했으므로 이 CTE가 원인이다”
status counter는 세션 또는 인스턴스의 여러 statement가 만든 임시 테이블을 포함할 수 있다. 특정 digest의 실행 전후와 Performance Schema 계측을 연결하고, 같은 SQL을 격리된 세션에서 재현해야 한다.
11.5 “힌트로 빨라졌으니 영구 해결됐다”
힌트는 현재 통계와 데이터 크기에서 한 계획을 제한한다. 데이터 분포, 인덱스, 버전이 바뀌면 과거의 좋은 강제가 미래의 나쁜 계획이 될 수 있다. 힌트에는 만료 조건과 업그레이드 회귀 시험을 붙인다.
12. Aurora MySQL 운영 해석
Aurora MySQL도 호환되는 MySQL optimizer 동작을 기반으로 하므로 merge/materialization의 기본 개념은 같다. 그러나 실제 운영 판단에는 다음 차이를 반영해야 한다.
- Aurora MySQL major version과 patch level이 Community MySQL의 특정 minor version과 완전히 동일하다고 가정하지 않는다. 대상 cluster에서 직접 계획을 확인한다.
optimizer_switch변경은 DB cluster parameter group 또는 DB parameter group의 적용 범위와 동적 변경 가능 여부를 확인한다. 한 쿼리를 위해 넓은 범위의 parameter를 바꾸지 않는다.- materialized 중간 결과의 임시 공간 사용은 Aurora의 공유 cluster storage에 영구 테이블을 쓰는 것과 같은 경로로 단순 해석할 수 없다. 인스턴스 class의 local temporary storage, 메모리 압박, engine version의 TempTable 동작을 함께 확인한다.
- writer와 reader는 역할, 부하, buffer 상태, 통계 시점이 다를 수 있다. reader에서만 느린 보고 쿼리라면 reader endpoint에서 실제 계획과 지연 시간을 확인한다.
- Performance Insights 또는 Database Insights의 digest 수준 부하와 실행 계획을 함께 보되, 한 시점의 평균 latency만으로 merge/materialization 원인을 확정하지 않는다.
- failover나 reader 교체 뒤 cache 상태가 달라지면 같은 materialized plan의 체감 시간이 변할 수 있다. 계획 변화와 cache warm-up 효과를 분리한다.
Aurora에서도 핵심은 engine label보다 실제 iterator다. Materialize가 어떤 행을 얼마나 만들고, 그 결과를 몇 번 읽으며, 임시 공간과 CPU를 얼마나 소비하는지 관찰해야 한다.
13. 운영 점검표
SQL 설계
-
SELECT * -
GROUP BY,DISTINCT,LIMIT
실행 계획
-
EXPLAIN FORMAT=TREE에서Materialize - 안전한 환경에서
EXPLAIN ANALYZE
운영 변경
-
MERGE/NO_MERGE - 특정 SQL 문제를 해결하려고 전역
derived_merge
14. 결론
Derived table과 CTE의 핵심은 문법을 선택하는 데 있지 않고, query block 경계를 옵티마이저가 어떻게 실행하는지 이해하는 데 있다. Merge는 경계를 풀어 조건 전파, 조인 재배치, 원본 인덱스 활용의 기회를 넓힌다. Materialization은 중간 결과 생성 비용을 지불하지만 집계 같은 의미적 경계를 보존하고, 결과 재사용과 자동 인덱스, delayed materialization의 이점을 제공한다.
따라서 DBA가 내려야 할 판단은 “CTE가 좋은가, derived table이 좋은가”가 아니다. 중간 결과를 저장할 이유가 있는가, 경계를 풀었을 때 어떤 조건과 조인 순서가 이득을 얻는가, 실제 계획의 rows와 loops가 추정과 맞는가를 확인해야 한다. 이후 optimizer 학습에서는 semijoin과 subquery transformation을 함께 보면, MySQL이 SQL의 논리적 중첩을 실제 join과 iterator 계획으로 바꾸는 전체 흐름을 더 체계적으로 이해할 수 있다.