MySQL 8.4 CTE 物化怎么判断：EXPLAIN、derived_merge 与临时结果集

把复杂查询拆成 CTE 后，最容易出现的误判是：只要写了 WITH，MySQL 就一定先把结果落成一张临时表。MySQL 8.4 会在 CTE 被引用的位置尝试合并（merge）或物化（materialize），两者的扫描、过滤和内存成本并不一样。

先看一个会被误判的订单查询

假设报表先筛出近 30 天的已支付订单，再同时统计地区和渠道。为了让例子可复现，下面只使用合成的 orders 表和 order_region 表：

WITH recent_paid AS (
  SELECT order_id, customer_id, region_id, channel, amount
  FROM orders
  WHERE status = 'paid'
    AND created_at >= '2026-08-01'
)
SELECT r.region_name, COUNT(*) AS order_count, SUM(p.amount) AS total_amount
FROM recent_paid AS p
JOIN order_region AS r ON r.region_id = p.region_id
GROUP BY r.region_name
ORDER BY total_amount DESC;

这里的 recent_paid 只是查询范围内的命名结果集。优化器可能把它的过滤条件合进外层，也可能先生成内部结果再连接。不要凭 SQL 外观决定策略，先留一份基线计划。

用 EXPLAIN 识别合并还是物化

可以直接用 EXPLAIN 输出的 JSON 格式计划观察节点间的关联关系：

EXPLAIN FORMAT=JSON
WITH recent_paid AS (
  SELECT order_id, customer_id, region_id, channel, amount
  FROM orders
  WHERE status = 'paid' AND created_at >= '2026-08-01'
)
SELECT r.region_name, COUNT(*), SUM(p.amount)
FROM recent_paid AS p
JOIN order_region AS r ON r.region_id = p.region_id
GROUP BY r.region_name;

计划里如果 CTE 的查询块被直接展开到外层，通常说明它走了合并思路；如果出现 materialized_from_subquery、内部临时表或相应的派生表物化节点，则说明优化器为结果集保留了单独的执行阶段。不同格式的字段层级会变化，关键是找“结果集是否有独立生命周期”，不要只搜索一行 Using temporary。

还要注意延迟物化的特性：就算执行计划已经允许走物化逻辑，MySQL 也可能等到外层操作真正需要读取对应结果时，才生成这个临时结果集。如果前面的连接操作已经返回空结果，内部的这个结果集甚至完全不需要生成。

用指标证明计划差异，而不是只看关键词

你可以把同一条查询放到和生产数据量接近的测试环境里，记录执行耗时、实际扫描行数、临时表增长情况。一个简单的核对表至少要覆盖这些维度：

• EXPLAIN 的访问类型、估算行数和使用的索引。
• EXPLAIN ANALYZE（版本与环境允许时）的实际耗时和实际行数。
• 查询开始前后的临时表计数、内存使用峰值与磁盘临时表计数。
• 结果集是否因为排序或聚合操作变大，以及业务侧是否真的需要返回全部列。

单次执行速度变快不代表优化方案是稳定的。换一组日期窗口、支付状态占比和地区分布的参数，再核对优化器估算值和实际执行情况是否偏差太大，才能确认是执行计划本身优化生效，还是刚好赶上数据分布巧合。

derived_merge 和 hint 各自控制什么

MySQL 的 derived_merge 优化器开关影响派生表、视图引用和 CTE 的默认合并行为。它不是“全局强制所有 CTE 物化”的开关；关闭合并后，符合条件的结果集才会更倾向于保留独立阶段，最终仍要结合查询限制和计划验收。

针对单条语句做验证的时候，更适合通过 hint 明确标记出你的实验意图：

WITH recent_paid AS (
  SELECT order_id, region_id, amount
  FROM orders
  WHERE status = 'paid' AND created_at >= '2026-08-01'
)
SELECT /*+ NO_MERGE(recent_paid) */
       r.region_name, COUNT(*), SUM(p.amount)
FROM recent_paid AS p
JOIN order_region AS r ON r.region_id = p.region_id
GROUP BY r.region_name;

NO_MERGE适合验证“保留结果集是否减少重复工作”的假设，MERGE则适合验证“把过滤条件推入外层是否更容易缩小扫描”。hint 不是性能保证；它只是让实验变量更明确。

多次引用时，物化可能更有价值

CTE 和派生表有一个很实际的差异：CTE 可以在同一条 SQL 语句里被多次引用。如果它触发了物化，MySQL 通常会在这条语句范围内只生成一次结果，后续多个引用直接复用这份结果；优化器还可能为不同的引用，自动生成适配各自访问路径的辅助索引。

这不等于“CTE 被引用的次数越多，就越应该走物化”。如果 CTE 本身返回的数据集很大，但实际外层过滤后只需要少数几行，走合并逻辑把外层条件下推进去反而更省资源；如果 CTE 的计算成本很高，还被多个分支反复调用，物化一次再复用才可能更划算。实际判断时要把复用次数、结果集大小、过滤条件下推的可行性放在一起综合测试。

三个容易踩到的边界

• 递归 CTE 固定走物化语义，不能直接套用普通非递归 CTE 的经验来判断。
• 聚合、窗口函数、DISTINCT、LIMIT 等结构可能阻止合并；看到这些结构时先查官方限制，再看计划。
• Using temporary 不是所有 CTE 物化的唯一证据，内部派生结果的字段和 JSON 节点更值得结合阅读。

常见问题

CTE 一定比嵌套子查询快吗？

不一定。CTE 主要是优化了SQL的命名和复用逻辑，优化器完全可能对它和普通派生表使用几乎一致的合并或物化策略。判断性能差异要对比等价SQL的执行计划和实际运行指标，不要仅凭用了什么关键字下结论。

关闭 derived_merge 就能解决临时表问题吗？

不行。直接关闭derived_merge只是调整一类优化器的默认行为，有可能减少重复计算，也有可能生成体积大很多的中间结果。任何参数开关调整，都必须配合执行计划核对和临时表指标回归验证。

怎么判断物化结果是否被重复计算？

先查看EXPLAIN的JSON计划和optimizer trace里有没有出现一次创建、后续直接复用结果的特征，再结合实际执行耗时和临时表生成计数判断，不要光靠CTE在SQL里出现的次数，就推断它的实际执行次数。

CTE 的性能判断可以压缩成三步：保留原始计划，比较合并与物化的证据，再用接近生产的数据验证实际成本。这样既不会把 WITH 误当成临时表，也不会把某一次偶然变快当成长期结论。