当前位置：首页 > 文章列表 > 数据库 > MySQL > MySQL CTE 多次引用时为什么可能重复物化

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

来源：17golang原创 2026-09-10 16:41:36 0浏览收藏

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

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

要点速览

普通 CTE 的“多次引用”不等于“多次物化”，递归 CTE 则总会物化。
多引用时，MySQL 可能为不同访问方式建立多个自动索引，但临时结果仍是一份。
先用 EXPLAIN 和 optimizer_trace 看证据，再决定是否使用 NO_MERGE 或调整 SQL。

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

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

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

MySQL CTE daily_stats 从 orders 生成共享临时表并被 s1、s2 两个引用读取的静态查询结构图 — 图1：把 orders、daily_stats、共享临时表与 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` 的组合：前者表示创建，后者表示复用。不要把“引用出现两次”当成“创建出现两次”。

MySQL CTE 计划树与 optimizer_trace 中 materialized_from_subquery、creating_tmp_table、reusing_tmp_table 的静态证据关系图 — 图2：用计划树、物化节点和跟踪线索区分一次创建、多次复用与按引用增加的自动索引。

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 则始终物化，不能用“关闭合并”来改变这个基本事实。

看到的现象	更准确的解释	先做什么
两个 CTE 引用	可能共享一份物化结果	看 trace 的创建/复用线索
多个临时索引	不同引用需要不同访问路径	比较连接列和过滤条件
两段相同子查询	没有共享定义，可能重复工作	合并为一个 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。