当前位置：首页 > 文章列表 > 数据库 > MySQL > MySQL CTE 物化与合并的执行策略对比

MySQL CTE 物化与合并的执行策略对比

来源：17golang原创 2026-10-10 17:08:14 0浏览收藏

MySQL 的 CTE（公用表表达式）不等于“先执行一次再复用”。优化器通常会在合并（merge）和物化（materialization）之间选择：合并是把 CTE 展开到外层查询块，物化则把它当作内部临时表。判断重点不是 SQL 看起来是否更短，而是过滤条件能否下推、CTE 是否被多次引用，以及中间结果是否值得单独保存。

要点速览

简单、可合并的 CTE 更容易让外层条件继续参与优化。
聚合、DISTINCT、窗口函数、UNION 或 LIMIT 等结构可能阻止合并。
用 EXPLAIN、MERGE 和 NO_MERGE 做对照实验，不能只凭 CTE 语法判断性能。

一、先分清 CTE 合并和物化各自做了什么

合并时，CTE 的查询块会进入外层查询。外层的过滤条件有机会继续下推，基表索引也更容易直接参与访问；代价是查询块被展开后，外层连接和过滤的组合可能变得复杂。

物化时，MySQL 会为 CTE 建立内部临时表，再由外层语句读取它。这样能隔离中间结果，尤其适合无法合并的聚合或需要重复读取的结果。官方文档还说明：如果估计创建索引有帮助，优化器可以为物化 CTE 自动添加相关索引；同一个 CTE 被多次引用时，可能针对不同引用建立多个索引。

MySQL CTE 合并与物化在外层查询和内部临时表之间的静态结构图 — 图1：结构说明图，展示 CTE 合并进入外层查询、物化进入内部临时表的边界；这不是执行截图。

二、用聚合 CTE 对比两种执行策略

下面的例子先按客户统计订单金额，再筛选高价值客户。示例只用于说明优化边界，表名和数据均为原创，运行前应换成自己的业务表。

WITH customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id -- 聚合形成中间结果，通常不能直接合并
)
SELECT c.customer_id, c.total_amount
FROM customer_totals AS c
WHERE c.total_amount > 10000; -- 外层条件用于观察过滤位置

这里的 GROUP BY 是判断关键。聚合结果需要先形成分组语义，优化器不能把它当成普通的单表投影随意展开。若改成只投影订单字段且没有 DISTINCT、LIMIT、窗口函数等阻塞结构，CTE 更有机会被合并，外层条件也可能参与更早的过滤。

三、用 MERGE、NO_MERGE 和 EXPLAIN 做验证

需要做对照实验时，可以只改变提示，不改业务逻辑。MySQL 8.4 支持针对 CTE 的 `MERGE` 与 `NO_MERGE` 提示；提示表达的是优化方向，真正可用仍受查询结构限制。

WITH customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    GROUP BY customer_id -- 保留聚合语义，观察物化边界
)
SELECT /*+ NO_MERGE(customer_totals) */
       customer_id, total_amount
FROM customer_totals
WHERE total_amount > 10000; -- 固定外层过滤条件

然后分别去掉提示、替换为 `MERGE(customer_totals)`，再对三份语句执行 `EXPLAIN FORMAT=TREE`。重点看计划中是否出现独立的物化节点、过滤条件落在哪一层，以及扫描行数和临时表相关信息是否发生变化。不要把某一次计划变化直接当成所有数据规模下的结论。

MySQL CTE 执行计划对照中的聚合节点、过滤条件和物化结果关系图 — 图2：关系说明图，对照聚合 CTE、外层过滤、MERGE/NO_MERGE 提示与执行计划观察点；这不是运行结果截图。

四、按场景选择，并保留回退路径

场景	优先观察	判断提醒
简单投影、外层过滤明显	是否能合并	让条件和基表索引尽量参与同一计划
聚合、DISTINCT、窗口函数、UNION、LIMIT	物化节点与临时表	这些结构可能阻止合并
同一 CTE 多次引用	每个引用的访问方式	自动索引不代表一定更快，需看计划
递归 CTE	递归边界和中间结果规模	递归 CTE 始终物化，重点转向深度与数据量