CTE 物化代价怎么配置或排查

MySQL 的 CTE（公共表表达式）变慢，不等于“物化一定有问题”。优化器会在把 CTE 合并进外层查询块和写入内部临时表之间选择；合并有利于条件下推，物化则可能让同一个 CTE 在一次查询中复用，还可能为引用自动添加索引。真正要排查的是：当前语句走了哪条路径，物化生成了多少行，以及下游连接是否值得这次准备成本。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/

先确认 CTE 到底有没有物化

先用同一份只读查询做估算和实测。EXPLAIN 只描述计划，不会因为查看计划就把 CTE 真正跑完；EXPLAIN ANALYZE 会执行语句，更适合放在测试库或只读副本上观察真实行数和耗时。

-- 先看优化器估算的树形计划
EXPLAIN FORMAT=TREE
WITH recent_orders AS (
    SELECT customer_id, order_id, total_amount
    FROM orders
    WHERE created_at >= '2026-01-01'
)
SELECT c.customer_id, r.total_amount
FROM customers AS c
JOIN recent_orders AS r ON r.customer_id = c.customer_id;

-- 只在可接受执行成本的环境做实测
EXPLAIN ANALYZE
WITH recent_orders AS (
    SELECT customer_id, order_id, total_amount
    FROM orders
    WHERE created_at >= '2026-01-01'
)
SELECT c.customer_id, r.total_amount
FROM customers AS c
JOIN recent_orders AS r ON r.customer_id = c.customer_id;

树形计划里若出现物化相关节点，说明 CTE 没有简单地并入外层；但不要只看节点名字。比较 estimated rows、actual rows、loops 和执行时间：估算远小于实际行数，往往比“是否物化”更值得先修正统计信息或过滤条件。

需要固定策略时，先用语句级提示

如果同一 CTE 被多次引用，物化一次并复用可能更合适；如果外层过滤条件很强，合并后让条件进入底层表，反而可能少读很多数据。可以先用提示做对照，而不是直接改服务器级设置。

-- 只对本条语句尝试物化，便于和默认计划做对照
WITH recent_orders AS (
    SELECT customer_id, order_id, total_amount
    FROM orders
    WHERE created_at >= '2026-01-01'
)
SELECT /*+ NO_MERGE(recent_orders) */
       c.customer_id, r.total_amount
FROM customers AS c
JOIN recent_orders AS r ON r.customer_id = c.customer_id
WHERE c.customer_id = 1001;

-- 反向测试合并，让外层过滤尽量参与底层访问
WITH recent_orders AS (
    SELECT customer_id, order_id, total_amount
    FROM orders
    WHERE created_at >= '2026-01-01'
)
SELECT /*+ MERGE(recent_orders) */
       c.customer_id, r.total_amount
FROM customers AS c
JOIN recent_orders AS r ON r.customer_id = c.customer_id
WHERE c.customer_id = 1001;

提示只改变当前语句的尝试方向，并不保证所有规则都能合并：聚合、窗口函数、DISTINCT、GROUP BY、LIMIT、集合操作等构造本身可能阻止合并。物化也不是无索引的黑盒，MySQL 可能按引用方式为物化结果增加相关索引。

多次引用时要特别留意两件事：CTE 物化通常在一次查询中只生成一次，但不同引用可能需要不同的访问方式；递归 CTE 则始终物化。这里的成本不能只用“临时表变慢”一句话概括。

全局开关只做会话级实验

optimizer_switch 里的 derived_merge 默认开启，用于控制优化器是否尝试把派生表、视图和 CTE 合并到外层查询块。排查时可以在当前连接临时关闭它，观察计划变化；不要把一次 SQL 的异常直接改成全局配置。

-- 仅在当前连接关闭 CTE/派生表合并，保留其他开关原值
SET SESSION optimizer_switch = 'derived_merge=off';

-- 在同一连接恢复默认的合并尝试
SET SESSION optimizer_switch = 'derived_merge=on';

-- 记录当前开关，避免凭记忆判断环境差异
SELECT @@SESSION.optimizer_switch;

如果关闭合并后查询明显变慢，说明这条语句可能依赖条件下推或底层索引访问；如果关闭后反而稳定，继续比较物化结果规模和连接方式。不要把 materialization 开关与 CTE 的 derived_merge 混为一谈：前者主要涉及子查询物化策略，当前问题先围绕 CTE 的合并决策定位。

用 optimizer_trace 找到“为什么这么选”

当两个计划看起来差不多，或只看到物化节点却不知道代价来源，可以在当前会话打开优化器跟踪。它只记录本会话执行的语句，适合把合并尝试、延迟物化和访问路径判断留成证据。

-- 打开当前会话的优化器跟踪
SET optimizer_trace = 'enabled=ON';

-- 执行待排查的只读 CTE 查询
WITH recent_orders AS (
    SELECT customer_id, order_id, total_amount
    FROM orders
    WHERE created_at >= '2026-01-01'
)
SELECT customer_id, SUM(total_amount) AS amount
FROM recent_orders
GROUP BY customer_id;

-- 查看本会话最近一次跟踪结果
SELECT TRACE
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

-- 完成排查后关闭跟踪
SET optimizer_trace = 'enabled=OFF';

最后把三份材料放在一起看：执行计划说明“走了什么”，实际分析说明“花了多少”，optimizer trace 说明“为什么这样选”。若实际行数长期偏离估算，先检查过滤条件、索引和统计信息；若物化结果很大且只被引用一次，优先测试合并；若结果会被多次复用，再评估一次物化和自动索引是否值得。

常见问题

关闭 derived_merge 就等于强制所有 CTE 物化吗？

它只表示当前会话不再尝试合并，仍要受 CTE 结构和其他优化规则影响。更精确的单条语句控制优先使用 NO_MERGE。

物化结果一定会落盘吗？

不能仅凭 CTE 语法判断内存或磁盘位置。应结合执行计划、实际耗时和临时表相关指标分析，不要把“内部临时表”直接等同于磁盘文件。

为什么 EXPLAIN 看起来很快，真实查询却很慢？

EXPLAIN主要给出估算计划，不执行完整查询。用安全环境运行 EXPLAIN ANALYZE，再比较 actual rows、loops 和耗时，才能判断物化代价是否真的成为瓶颈。