MySQL CTE 怎样用物化提示控制中间结果

排查 MySQL 查询计划时，最容易误判的一点是：写了 CTE，就以为一定会先生成一张中间表。实际上，优化器可以把 CTE 合并进外层查询，也可以把它物化为内部临时表。需要明确表达“这段 CTE 不要被合并”时，在顶层 SELECT 后使用 NO_MERGE(cte_name)；希望保留合并意图时使用 MERGE(cte_name)。提示表达的是优化方向，最终仍要用 EXPLAIN 结合真实参数检查计划。

下面从一个“CTE 没有按预期独立处理”的问题现场出发，逐步判断、修改并复查；范围只覆盖 MySQL 8.4 的 CTE 合并与物化控制，不把物化提示当作固定的性能保证。

问题现场：CTE 为什么没有独立中间结果

下面的查询把订单筛选封装成一个 CTE。CTE 名称只是查询中的命名关系，是否单独保存结果由优化器决定：

WITH recent_orders AS (
    SELECT customer_id, order_id, total_amount
    FROM orders
    WHERE created_at >= '2026-01-01'
)
SELECT customer_id, SUM(total_amount) AS total_amount
FROM recent_orders
GROUP BY customer_id;

采用合并路径时，CTE 的查询块会被并入外层查询，外层条件有机会继续下推到基础表；采用物化路径时，CTE 会作为外层语句中的独立结果处理。MySQL 文档将这两类策略分别称为 merge 和 materialization。

验证动作：用 NO_MERGE 表达物化意图

物化提示写在顶层查询块的 SELECT 注释中，参数是 CTE 名称。下面的 NO_MERGE(recent_orders) 表示不要把这个 CTE 合并进外层查询：

WITH recent_orders AS (
    SELECT customer_id, order_id, total_amount
    FROM orders
    WHERE created_at >= '2026-01-01'
)
SELECT /*+ NO_MERGE(recent_orders) */
       customer_id, SUM(total_amount) AS total_amount
FROM recent_orders
GROUP BY customer_id;

如果要明确表达允许合并，可以改成 MERGE(recent_orders)：

WITH recent_orders AS (
    SELECT customer_id, order_id, total_amount
    FROM orders
    WHERE created_at >= '2026-01-01'
)
SELECT /*+ MERGE(recent_orders) */
       customer_id, SUM(total_amount) AS total_amount
FROM recent_orders
GROUP BY customer_id;

这里的关键词是“控制合并”，而不是直接调用一个名为 MATERIALIZATION() 的 CTE 提示。MySQL 8.4 文档给出的 CTE 控制提示是 MERGE 和 NO_MERGE；禁止合并后，优化器才会沿物化方向处理该 CTE。

定位取舍：物化解决什么，又可能带来什么

如果一段 CTE 在同一条语句中被多次引用，物化结果可以在这条语句内复用。MySQL 文档还说明，物化 CTE 可能按顶层语句的访问方式自动增加相关索引；当同一个 CTE 有多次引用时，优化器可能为不同引用建立不同的索引。

但这不等于“引用次数多就一定更快”。物化会引入内部临时表，若外层查询本来可以有效下推过滤条件，强行禁止合并反而可能增加中间数据量。建议只在计划稳定性、重复计算或特定写入限制确实需要时保留 NO_MERGE。

递归 CTE 是一个明确边界：MySQL 会对递归 CTE 进行物化，不能把普通 CTE 的提示选择直接套到递归查询上。

修复后复查：用 EXPLAIN 对照计划变化

写入提示后，先把同一条 SQL 保留两份：一份不带提示，一份带 NO_MERGE。使用 EXPLAIN FORMAT=TREE 查看计划结构，重点观察 CTE 是否仍表现为独立的物化节点，以及外层过滤是否还能够下推。

-- 先查看不带提示的计划，保留原始 SQL 作为对照
EXPLAIN FORMAT=TREE
WITH recent_orders AS (
    SELECT customer_id, order_id, total_amount
    FROM orders
    WHERE created_at >= '2026-01-01'
)
SELECT customer_id, SUM(total_amount) AS total_amount
FROM recent_orders
GROUP BY customer_id;

-- 再只增加 NO_MERGE，便于比较提示带来的计划差异
EXPLAIN FORMAT=TREE
WITH recent_orders AS (
    SELECT customer_id, order_id, total_amount
    FROM orders
    WHERE created_at >= '2026-01-01'
)
SELECT /*+ NO_MERGE(recent_orders) */
       customer_id, SUM(total_amount) AS total_amount
FROM recent_orders
GROUP BY customer_id;

对比时不要只看某一个节点名称。还要结合表规模、过滤条件、索引、返回行数和业务参数判断：提示是否减少了重复计算，是否让临时结果过大，是否让原本可以下推的条件停留在外层。

防止复发：把提示写进回归清单

常见问题

MySQL CTE 有没有直接叫 MATERIALIZATION 的提示？

针对 CTE 合并与物化，MySQL 8.4 文档使用的是 MERGE 和 NO_MERGE。要表达物化意图，通常写 NO_MERGE(cte_name)，而不是虚构一个 MATERIALIZATION(cte_name) 语法。

NO_MERGE 写了就一定使用磁盘临时表吗？

不能这样推断。它表达的是禁止合并，物化结果仍由 MySQL 的内部临时表机制管理，内存或磁盘等细节还取决于结果特征和相关配置。

提示后查询变慢怎么办？

先去掉提示保留对照计划，再检查外层过滤是否原本可以下推、物化结果是否过大、引用方式是否真的重复。没有稳定收益时，应删除提示，让优化器重新选择。

总结

MySQL CTE 的物化控制可以记成一句话：想禁止 CTE 合并，就在顶层 SELECT 中使用 NO_MERGE(cte_name)；想表达合并意图，就使用 MERGE(cte_name)。提示只是计划约束，最终决策仍应以对照后的 EXPLAIN 和真实业务回归为准。