MySQL / 系统课时
判断派生表与 CTE 的合并或物化
判断派生表与 CTE 的合并或物化
派生表和 CTE 写出来都像“先算一个中间结果”,但优化器未必真的创建中间结果。判断性能时要先确认它是被 merge 到外层查询,还是 materialize 成独立结果,再评估中间结果大小、复用次数和后续访问方式。
学习目标
- 判断 derived table 与 CTE 何时可被合并、何时会被物化
- 用执行计划验证 merge 与 materialization 的实际选择
- 评估中间结果规模、复用次数与临时结果访问成本
两条执行路径的本质
Merge 会把派生查询块并入外层查询块,让外层条件、连接顺序和索引选择有机会一起优化。它通常适合只是投影、过滤、简单连接的派生表,因为没有必要先生成完整中间结果。
Materialization 则先生成中间结果,再由外层读取。聚合、DISTINCT、窗口函数、GROUP BY、HAVING、LIMIT、集合运算等结构会阻止或限制派生表合并,因为外层查询不能跨过这些语义边界随意重排。最终采用哪条路径,应以实际执行计划为准。

用一组 SQL 对比合并与物化
下面两个派生表只有一个关键差异:第一个只是过滤,第二个包含聚合。前者通常具备 merge 条件,后者需要保留聚合边界。
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
status VARCHAR(20) NOT NULL,
amount DECIMAL(10,2) NOT NULL,
KEY idx_status_customer (status, customer_id)
);
INSERT INTO orders VALUES
(1, 101, 'PAID', 120.00),
(2, 101, 'PAID', 80.00),
(3, 102, 'NEW', 50.00),
(4, 103, 'PAID', 200.00);
EXPLAIN FORMAT=TREE
SELECT dt.customer_id, dt.amount
FROM (
SELECT customer_id, amount
FROM orders
WHERE status = 'PAID'
) AS dt
WHERE dt.amount >= 100;
EXPLAIN FORMAT=TREE
SELECT dt.customer_id, dt.total_amount
FROM (
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
WHERE status = 'PAID'
GROUP BY customer_id
) AS dt
WHERE dt.total_amount >= 100;预期结果
第一条计划通常不会保留一个必须先完整生成的派生结果,而会把相关过滤纳入统一查询计划。
第二条计划应保留聚合形成的结果边界,再由外层读取 total_amount。实际节点名称、成本和行数会依 MySQL 版本与统计信息而不同。不要用“SQL 里出现了子查询括号”来判断是否物化。更可靠的依据是执行计划中是否出现物化相关节点,以及外层条件有没有被下推到更靠近基表的位置。对复杂查询还应结合实际执行情况验证,而不是只凭 SQL 形状推断。
CTE 复用会改变成本模型
CTE 的关键不只是“语法更清晰”,还在于它可能被多个位置引用。若 CTE 因聚合等原因需要物化,同一查询中的多次引用可以共享已物化的结果;与此同时,多次引用本身也可能影响优化器对 merge 与 materialization 的选择,因此不能简单认为“用了 CTE 就一定只计算一次”。
EXPLAIN FORMAT=TREE
WITH paid_totals AS (
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
WHERE status = 'PAID'
GROUP BY customer_id
)
SELECT a.customer_id, a.total_amount
FROM paid_totals AS a
JOIN paid_totals AS b
ON b.customer_id = a.customer_id
WHERE a.total_amount >= 100;预期结果
paid_totals 因 GROUP BY 不能直接与外层完全合并。计划应体现 CTE 结果被物化或以等价的临时结果方式处理,并由后续引用读取;具体显示形式会因 MySQL 版本而异。物化不等于一定慢。若中间结果很小、需要多次读取,或者物化结果能够获得高效访问路径,物化反而可能更便宜。反过来,如果物化前会产生数百万行,而外层本来能把高选择性过滤下推到基表,物化就可能放大扫描、临时结果和内存成本。
用规模和复用判断是否需要干预
先看中间结果预计行数和行宽,再看它被引用几次,最后看外层过滤能否下推。对于可合并的查询,可以用 MERGE、NO_MERGE 优化器提示做对照实验;但提示不能突破聚合、窗口等语义上禁止合并的边界。
如果怀疑物化过大,使用 EXPLAIN ANALYZE 比单看估算更有价值:比较相关节点的实际行数、执行时间和循环次数,并观察外层最终只读取了其中多小的一部分。优化方向通常是把可安全下推的过滤提前、缩窄中间结果列宽、为基表补合适索引,或在确有复用收益时接受物化。
最终判断标准不是“CTE 一定慢”或“merge 一定快”,而是计划是否避免了不必要的大中间结果,同时让高选择性条件尽早生效,并让需要复用的计算只承担合理的执行成本。