当前位置：首页 > 文章列表 > 数据库 > MySQL > MySQL CTE 物化与合并选择的判断清单

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

来源：17golang原创 2026-11-13 11:46:05 0浏览收藏

遇到一条慢查询时，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 查询块与外层查询块的合并边界说明图 — 图1：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 被同一条查询引用多次时，单次物化结果可能避免重复计算。

CTE 物化实验、EXPLAIN 观察和回滚记录关系图 — 图2：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'; -- 完成对照后恢复会话状态