MySQL CTE 什么时候会物化而不是合并

MySQL CTE（公共表表达式）并不是写进 WITH 就一定先算出一张临时表。优化器通常会在“把 CTE 合并进外层查询”和“把 CTE 物化为内部临时表”之间选择：能合并时，外层条件更容易下推；不能合并或明确要求保留边界时，才会走物化。判断重点不是 CTE 的名字，而是它的查询结构、引用方式和整个执行计划。

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

先看清 MySQL CTE 的两种处理方式

合并可以理解为把 CTE 查询块展开到外层。比如只做字段投影和过滤的 recent_orders，优化器可能把它和外层的订单表一起重排，让外层条件参与索引访问。物化则把 CTE 结果作为内部临时表，后续查询把它当作一个独立数据源读取。

-- 这个 CTE 只有投影和过滤，具备被合并的基本条件
WITH recent_orders AS (
    SELECT order_id, customer_id, created_at
    FROM orders
    WHERE created_at >= '2026-01-01'
)
SELECT customer_id, COUNT(*) AS order_count
FROM recent_orders
GROUP BY customer_id;

合并不是“少了一步固定执行”，而是给优化器更多重写空间；物化也不等于一定更慢。物化会被延迟到真正需要结果时才进行，某些场景还可能为内部结果自动建立索引。多次引用同一个已经物化的 CTE 时，MySQL 会在本次查询中复用它，而不是为每个引用完整计算一遍。

这些 CTE 结构会让合并失去条件

下面这些结构会阻止派生表、视图引用和 CTE 合并到外层查询块：聚合函数或窗口函数、DISTINCT、GROUP BY、HAVING、LIMIT、UNION 或 UNION ALL、选择列表中的子查询、用户变量赋值，以及只引用字面量而不读取底层表的查询。

-- 聚合和分组形成结果边界，外层查询不能把它当作普通表直接展开
WITH customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, total_amount
FROM customer_totals
WHERE total_amount > 10000;

-- 递归 CTE 是另一类边界：MySQL 对它始终采用物化策略
WITH RECURSIVE tree AS (
    SELECT id, parent_id, 0 AS depth
    FROM category
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.parent_id, tree.depth + 1
    FROM category AS c
    JOIN tree ON c.parent_id = tree.id
)
SELECT id, depth FROM tree;

还有一个容易忽略的上限：如果合并后外层查询块会引用超过 61 张基表，优化器会选择物化。这个判断属于计划结构限制，不是“CTE 超过多少行就物化”，也不能用行数阈值替代。

用 EXPLAIN、提示和开关确认计划

不要只因为 SQL 使用了 WITH 就断定它已经物化。先执行 EXPLAIN，再观察 CTE 是否作为独立的物化来源出现，以及外层谓词是否能参与底层表访问。不同格式的 EXPLAIN 展示细节不同，排查时应以实际计划为准。

-- 先保留原查询，查看优化器是否展开 CTE 或保留独立来源
EXPLAIN FORMAT=TREE
WITH recent_orders AS (
    SELECT order_id, customer_id
    FROM orders
    WHERE created_at >= '2026-01-01'
)
SELECT r.customer_id
FROM recent_orders AS r
WHERE r.order_id > 100000;

-- 仅对当前语句施加倾向，避免把全局开关当成长期修复
WITH recent_orders AS (
    SELECT order_id, customer_id FROM orders
)
SELECT /*+ NO_MERGE(recent_orders) */ customer_id
FROM recent_orders;

-- 检查是否允许派生表、视图和 CTE 采用合并策略
SELECT @@optimizer_switch;

MERGE(cte_name) 和 NO_MERGE(cte_name) 只在其他规则允许时发挥作用；如果 CTE 本身含有聚合或递归结构，提示不能把不合法的合并变成合法。全局关闭 optimizer_switch 中的 derived_merge 会影响更多语句，生产环境应先用单语句提示或灰度计划验证。

按这个清单决定是否干预

实际优化时，先比较两份计划，再结合扫描行数、连接顺序和过滤位置判断代价。物化提供了清晰的结果边界，但可能产生临时表读写；合并减少了边界，却可能让外层查询变得复杂。最终目标是让访问路径匹配数据分布，而不是追求“所有 CTE 都合并”或“所有 CTE 都物化”。

常见问题

CTE 写了 GROUP BY 就一定会物化吗？

它会失去合并条件，通常按物化查询块处理；仍应通过 EXPLAIN 确认实际计划，而不是只看 SQL 文本。

多次引用 CTE 会重复计算吗？

如果该 CTE 被物化，本次查询只物化一次，多个引用可以复用；优化器还可能针对不同引用建立合适的内部索引。

NO_MERGE 能解决所有性能问题吗？

不能。它只是给当前 CTE 增加一个计划方向，仍需对比执行计划、临时结果规模和连接访问代价。