MySQL CTE 物化后查询变慢怎么办

MySQL 的 CTE（Common Table Expression）写起来更清楚，但清晰的 SQL 不等于更快的执行计划。CTE 被物化后，优化器会把结果放进内部临时表；如果外层条件原本可以尽早过滤大量数据，额外的中间结果就可能让查询变慢。处理这类问题的关键不是盲目删除 WITH，而是先确认计划，再用合并和物化做对照。

先确认 CTE 真的发生了物化

MySQL 优化器对 CTE 有两条路：把 CTE 合并进外层查询块，或者把 CTE 物化成内部临时表。官方文档明确说明，派生表、视图引用和 CTE 都可能采用这两种策略；优化器会尽量避免不必要的物化，以便把外层条件继续下推。

因此，看到查询使用 WITH 并不能直接得出“已经物化”的结论。先保留原 SQL，查看计划结构：

-- 先观察查询块结构，不要凭 WITH 关键字猜测物化结果
EXPLAIN FORMAT=JSON
WITH cte_sales AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    WHERE created_at >= '2026-01-01'
    GROUP BY customer_id
)
SELECT customer_id, total_amount
FROM cte_sales
WHERE total_amount > 10000;

阅读计划时，可以把“外层查询块”“CTE定义”“优化器合并或物化决策”“内部临时表”“CTE结果”分开看。若计划只呈现底层表和合并后的条件，通常说明 CTE 被展开了；若出现物化子查询或临时结果节点，则说明需要继续评估中间结果成本。不要把 EXPLAIN 的估算行数当成真实耗时，验证仍应使用同一数据集和同一参数。

为什么物化会让这条查询变慢

最常见的原因是过滤时机改变了。CTE 先产出较大的中间结果，再由外层 WHERE 筛选时，可能产生临时表写入、读取和额外的聚合成本。若 CTE 原本只会返回少量行，物化未必是坏事；问题在于中间结果的规模与外层条件是否匹配。

排查时先问三个问题：

官方说明还提到，物化并不意味着一定没有索引：优化器估计有收益时，可以为物化结果自动建立相关索引；CTE 被多次引用时，不同引用甚至可能需要不同索引。也就是说，不能只因为计划里出现临时表就直接判定它必然更慢，必须结合访问方式和估算成本判断。

用执行计划比较合并与物化

如果需要验证“合并是否更适合当前查询”，可以用优化器提示做可逆对照。下面把同一份查询拆成两个实验版本，重点比较 MERGE(cte_sales)、NO_MERGE(cte_sales)、orders索引、CTE临时表 和 外层WHERE 在计划中的位置。

-- 只改变 CTE 策略，便于把计划差异归因到合并/物化
WITH cte_sales AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    WHERE created_at >= '2026-01-01'
    GROUP BY customer_id
)
SELECT /*+ MERGE(cte_sales) */ customer_id, total_amount
FROM cte_sales
WHERE total_amount > 10000;

-- 对照版本：保留 CTE 临时结果，再观察读取路径和估算行数
WITH cte_sales AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    WHERE created_at >= '2026-01-01'
    GROUP BY customer_id
)
SELECT /*+ NO_MERGE(cte_sales) */ customer_id, total_amount
FROM cte_sales
WHERE total_amount > 10000;

两版都执行 EXPLAIN FORMAT=JSON，比较底层表访问、过滤位置、物化节点和预计读取量；不要只比较 SQL 文本长度。提示只是实验工具，若查询结构本身包含聚合、DISTINCT、GROUP BY、HAVING、LIMIT、集合操作或特定子查询，合并可能被规则阻止。官方文档也说明，derived_merge 优化开关默认允许合并，关闭它会阻止合并，但不建议把全局开关当作单条慢查询的第一修复手段。

什么时候应该保留物化

如果 CTE 被同一条语句多次引用，物化可能避免每个引用都重复计算；MySQL 文档说明，CTE 物化后通常只为该查询物化一次，并可为不同引用建立合适的索引。递归 CTE 则始终会物化，这时应把优化重点放在递归边界、输出列和外层访问路径上，而不是强行要求合并。

反过来，如果 CTE 只被引用一次、外层过滤很有选择性、且 CTE 内部没有阻止合并的结构，可以优先比较合并版本。实际调整顺序建议是：先用 EXPLAIN 找到物化节点，再减少中间列和无效行，接着核对 orders索引 是否服务于过滤或连接，最后才考虑保留 NO_MERGE。每次只改一个变量，并记录计划差异和真实执行时间。

相关问题

CTE 物化一定比子查询慢吗？

不一定。物化会增加中间结果成本，但多次引用、复用计算结果或自动索引生效时也可能更合适；应以两版计划和实际数据为准。

关闭 derived_merge 能解决所有 CTE 慢查询吗？

不能。它是系统级优化器开关，可能影响其他语句。先用单条语句的 MERGE 或 NO_MERGE 做对照，更容易控制影响范围。

为什么 EXPLAIN 看不到完整的 CTE 子查询？

合并后的 CTE 可能只显示底层表；多次引用时，传统格式也可能只展示一个完整子查询节点。需要结合 JSON 计划或优化器跟踪信息理解结构。

判断 MySQL CTE 物化是否导致变慢，核心是把“SQL 写法”和“优化器最终采用的查询结构”分开。先确认，再对照，最后根据引用次数与过滤选择性决定是否改写，通常比直接删除 CTE 更稳妥。