MySQL CTE 多次引用时为什么可能重复物化

把同一个 CTE 写在两个 JOIN 位置后，执行计划里出现两个引用，很容易得出“CTE 被物化了两次”的结论。这个判断通常不对：在同一条语句中，如果 MySQL 选择物化一个 CTE，物化结果只创建一次，后续引用复用它；真正可能增加成本的是每个引用需要的临时索引、重复写了两份等价查询，或某些引用被合并而另一些被物化。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/

先别急着把多处引用等同于多次物化

CTE 是一条语句范围内的命名结果集，`daily_stats` 既可以被 `s1` 引用，也可以被 `s2` 引用。优化器面对 CTE 有两条路：把定义合并进外层查询，或生成内部临时表。带有聚合、DISTINCT、GROUP BY、HAVING、LIMIT、UNION 等结构的 CTE 通常失去合并机会，更容易进入物化路径。

关键在于“物化对象”和“引用节点”不是一回事。多次引用只会让同一份共享临时表被多处读取；官方手册还说明，MySQL 可能按引用的访问方式给这份表加多个索引。执行计划中看到两个 `s1`、`s2`，不能直接推导出底层聚合执行了两遍。

为什么 EXPLAIN 看起来像有两个 CTE

准备一个带分组的查询，让 CTE 的行为更容易观察。下面的例子只描述结构，不依赖某个数据量或固定耗时：

WITH daily_stats AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    WHERE created_at >= '2026-01-01'
    GROUP BY customer_id
)
SELECT s1.customer_id, s1.order_count, s2.order_count AS order_count_copy
FROM daily_stats AS s1
JOIN daily_stats AS s2 ON s2.customer_id = s1.customer_id;
-- 中文说明：GROUP BY 使 daily_stats 更可能走物化路径；s1、s2 是同一 CTE 的两个引用。

先看计划树，再看优化器跟踪。`EXPLAIN FORMAT=JSON` 里的一个完整 `materialized_from_subquery` 节点描述 CTE 的来源计划，其他引用可能只显示精简节点。要判断是否真的重复生成，重点找的是 `creating_tmp_table` 与 `reusing_tmp_table` 的组合：前者表示创建，后者表示复用。不要把“引用出现两次”当成“创建出现两次”。

EXPLAIN FORMAT=JSON
WITH daily_stats AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
)
SELECT s1.customer_id
FROM daily_stats AS s1
JOIN daily_stats AS s2 ON s2.customer_id = s1.customer_id;

SET optimizer_trace = 'enabled=on';
WITH daily_stats AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
)
SELECT s1.customer_id
FROM daily_stats AS s1
JOIN daily_stats AS s2 ON s2.customer_id = s1.customer_id;
-- 中文说明：执行同一查询后读取跟踪，观察创建与复用线索，而不是猜测节点数量。
SELECT TRACE
FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

哪些写法才会真正重复做工作

第一种是把相同聚合分别写成两个派生表：它们没有共享 CTE 名称，优化器只能分别规划。第二种是定义两个内容等价但名字不同的 CTE，再分别引用；可读性看似提高，物化边界却可能增加。第三种是对一个 CTE 的不同引用采用不同策略：一处被 `MERGE` 展开，另一处用 `NO_MERGE` 物化，这时底层表的访问路径可能不同。

还有一个容易误判的情况：多引用 CTE 可能拥有多个自动索引。它们服务于不同的连接键或过滤条件，属于同一临时结果上的访问优化，不是把 CTE 内容重新算一遍。递归 CTE 则始终物化，不能用“关闭合并”来改变这个基本事实。

怎么控制合并与物化

默认不要全局关闭优化。需要做对照时，可以在单条语句上使用 `NO_MERGE`，或在当前会话临时调整 `derived_merge`。提示只是让优化器倾向某条路，技术约束仍可能阻止合并；改完后必须重新比较过滤下推、临时表大小、连接访问和总体耗时。

WITH daily_stats AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
)
SELECT /*+ NO_MERGE(daily_stats) */ s1.customer_id, s1.order_count
FROM daily_stats AS s1;

SET SESSION optimizer_switch = 'derived_merge=off';
-- 中文说明：只在当前会话做实验；对照完成后恢复默认值，避免影响其他连接。
SET SESSION optimizer_switch = 'derived_merge=default';

采用建议很简单：如果 CTE 只被引用一次且定义可合并，先让优化器自由选择；如果同一份聚合结果被多处使用，保留一个 CTE 并观察复用；如果 trace 真正显示多个创建线索，再回头检查是否写成了多个定义、多个查询块或混用了合并策略。不要因为计划树有两个引用就先改 SQL。

常见问题

MySQL CTE 被引用两次，一定只物化一次吗？

在同一条语句中，若该 CTE 选择物化，官方规则是只物化一次；但它可能被合并，或按不同引用建立多个自动索引。

看到两个 materialized_from_subquery 就是执行两遍吗？

不一定。多引用时只有一个节点通常包含完整来源计划，其他节点可能是精简引用。应结合 `creating_tmp_table` 和 `reusing_tmp_table` 判断。

什么时候应该拆掉 CTE？

当两个定义实际过滤条件、聚合粒度或生命周期不同，拆开比追求复用更清楚；如果只是同一结果的不同访问方式，应先保留一个 CTE 并比较计划。

参考：https://dev.mysql.com/doc/refman/8.4/en/derived-table-optimization.html；https://dev.mysql.com/doc/refman/8.4/en/with.html。