MySQL CTE 物化与合并选择的判断清单

遇到一条慢查询时，CTE（WITH）最容易被误解成“先算出一张临时表，再交给外层查询”。在 MySQL 8.4 中，优化器会在合并和物化之间选择：能合并时，CTE 的查询块会并入外层；需要物化时，结果才会作为内部临时表使用。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/derived-table-optimization.html

我实际排查这类问题时，会先看结构，再用 EXPLAIN 做局部对照，最后才考虑 hint 或会话级开关。下面这份清单的重点不是强行让所有 CTE 走同一种路径，而是让选择可解释、可回滚。

先看结构：什么情况下优先判断合并

如果 CTE 只是从基础表投影几列、加一个普通过滤条件，先把它当作“可能被合并的查询块”。合并后，外层条件更容易继续下推，通常也少一个中间结果。

反过来，以下结构应先标记为“可能阻止合并”：DISTINCT、GROUP BY、HAVING、LIMIT、UNION/UNION ALL、选择列表中的子查询、用户变量赋值，以及只引用字面量的查询块。它们不是让查询必然变慢的证据，而是提醒我们不要按“CTE 一定会展开”的假设分析。

-- 先保留一个可合并的 CTE，观察外层过滤能否继续参与计划
EXPLAIN
WITH recent_orders AS (
    SELECT customer_id, order_id
    FROM orders
    WHERE created_at >= '2026-01-01' -- 限定输入范围，避免把历史数据带入判断
)
SELECT customer_id, COUNT(*) AS order_count
FROM recent_orders
GROUP BY customer_id;

再看计划：如何确认物化是否值得

不要只看 SQL 文本推断结果，先保存原始 EXPLAIN。如果 CTE 被合并，计划往往直接呈现基础表和外层查询块；如果选择物化，则会出现内部临时结果相关的访问路径。具体节点名称仍要以当前版本和实际统计信息为准。

物化也不等于“必然先把全部数据算完”：MySQL 会尽量延迟物化，若连接的另一侧已经判定没有行，CTE 可能根本不需要物化。物化时，优化器还可能为内部结果增加索引，以改善后续访问。

-- 只做对照实验：禁用这个查询块的合并，不改变全局策略
EXPLAIN
WITH recent_orders AS (
    SELECT /*+ NO_MERGE() */ customer_id, order_id
    FROM orders
    WHERE created_at >= '2026-01-01' -- 与基线保持相同过滤条件
)
SELECT c.customer_id, r.order_id
FROM customers AS c
JOIN recent_orders AS r ON r.customer_id = c.customer_id;

对照时至少记录三项：访问行数与连接顺序、是否出现临时结果相关节点、总耗时和逻辑读。不要只因为物化计划看起来更“分层”就判定它更差；CTE 被同一条查询引用多次时，单次物化结果可能避免重复计算。

最后做选择：局部 hint 优先于全局开关

判断清单可以压缩成四个问题：第一，CTE 是否包含上面列出的阻止合并结构；第二，外层过滤是否希望下推；第三，CTE 是否在同一查询中被重复引用；第四，物化结果是否可能缩小后续连接成本。

需要实验时，优先对单个查询块使用 MERGE 或 NO_MERGE。它们只表达“允许尝试合并”或“不要合并”，如果其他规则阻止合并，MERGE 也不能强行突破。只有当多条查询都出现同类问题，并且已经完成基线对照，才考虑在会话范围调整 optimizer_switch 的 derived_merge；不要直接把生产全局配置当作第一步。

-- 会话级实验，结束后恢复，避免把试验策略带到连接池中的后续请求
SET SESSION optimizer_switch = 'derived_merge=off'; -- 临时观察物化路径

EXPLAIN
WITH grouped_orders AS (
    SELECT customer_id, COUNT(*) AS total_orders
    FROM orders
    GROUP BY customer_id -- 聚合本身也会影响合并判断
)
SELECT *
FROM grouped_orders
WHERE total_orders > 10;

SET SESSION optimizer_switch = 'derived_merge=on'; -- 完成对照后恢复会话状态

回滚与复盘：把一次选择变成可重复流程

线上变更记录至少保留：原始 SQL、基线计划、对照计划、数据量范围、执行时间、逻辑读、使用的 hint 或会话开关，以及恢复动作。若优化器升级、统计信息变化或数据分布改变，应重新做一次对照。

最终可以按这个简单规则落地：简单投影过滤先让优化器尝试合并；包含聚合、去重、集合运算或需要独立结果边界时，重点评估物化；同一查询重复引用且中间结果可复用时，再把物化的成本与收益放在一起比较。这里的“判断”要由计划和数据证明，而不是由 WITH 关键字单独决定。

常见问题

CTE 被物化后一定会生成磁盘临时表吗？

不一定。文档描述的是内部临时表策略，是否落盘、何时物化以及是否建立内部索引，都取决于执行条件与成本判断，不能仅凭 SQL 外观下结论。

为什么加了 MERGE 还是没有合并？

MERGE 只是允许优化器尝试合并；如果查询结构本身不满足合并规则，或者合并会导致外层查询块超过限制，优化器仍可能选择物化。回到 EXPLAIN 和结构清单逐项排查。