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

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

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

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

物化时，MySQL 会为 CTE 建立内部临时表，再由外层语句读取它。这样能隔离中间结果，尤其适合无法合并的聚合或需要重复读取的结果。官方文档还说明：如果估计创建索引有帮助，优化器可以为物化 CTE 自动添加相关索引；同一个 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`。重点看计划中是否出现独立的物化节点、过滤条件落在哪一层，以及扫描行数和临时表相关信息是否发生变化。不要把某一次计划变化直接当成所有数据规模下的结论。

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

生产环境建议保留不带提示的基线语句，把提示当作经过执行计划和代表性数据验证后的局部决策。数据分布、统计信息、索引和连接顺序改变后，原来的优势可能消失；如果提示让计划变差，先撤回提示，再检查过滤条件、聚合粒度和基表索引，而不是继续叠加提示。

相关问题

CTE 物化一定比合并慢吗？

不一定。物化会增加中间表成本，但也可能避免重复计算，并让临时结果获得适合外层访问的索引，最终要以代表性数据的执行计划和耗时判断。

为什么写了 MERGE 仍然没有合并？

提示不能消除查询结构本身的限制。先检查 CTE 是否包含聚合、DISTINCT、窗口函数、UNION、LIMIT 等可能阻止合并的元素，再用 EXPLAIN 确认计划。

多次引用 CTE 时应该直接使用 NO_MERGE 吗？

不要直接套用。先比较重复计算成本、物化大小和各引用的过滤选择性；只有对照计划显示独立中间结果更合适时，才保留 NO_MERGE。