首页 > 数据库 > MySQL

MySQL CTE 怎样判断是否应该物化

来源：17golang原创

时间：2026-10-20 21:30:26 315浏览收藏

MySQL 的 CTE（公共表表达式）不是“写了 WITH 就一定生成临时表”。优化器通常会在合并和物化之间做选择：能合并时，把 CTE 的查询块并入外层；需要独立结果时，才物化为内部临时表。判断是否应该物化，关键不是凭感觉改 SQL，而是先看 CTE 的引用次数、是否允许过滤条件下推，再用 MERGE、NO_MERGE 和执行计划确认。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/

要点速览

单次引用、结构简单的 CTE 通常更适合让优化器尝试合并。
需要多次复用、包含聚合或明确希望隔离查询块时，可以考虑物化。
EXPLAIN FORMAT=JSON 要关注 materialized_from_subquery、临时表创建/复用和自动索引等证据。

先分清 CTE 的合并与物化职责

“合并”是把 CTE 当成外层查询的一部分继续优化，外层条件有机会下推到 CTE 内部；“物化”则是把 CTE 当成一个独立结果，放入内部临时表，外层通过这个结果继续连接或过滤。两者都不是绝对的快慢开关，最终仍要结合数据量、过滤选择性和引用方式看计划。

一个容易忽略的点是：同一个 CTE 被查询多次时，如果 MySQL 选择物化，它会为本次查询物化一次，并允许不同引用使用合适的自动索引。这是物化最有价值的场景之一；如果 CTE 只引用一次，物化则可能增加中间结果的写入和读取成本。

MySQL CTE、外层查询块、MERGE、NO_MERGE、derived_merge 与内部临时表的静态关系说明图 — 图1：CTE 合并与物化的静态关系说明图，不是运行截图。

用查询形态判断默认倾向

先看 CTE 的内部结构，再谈提示。聚合函数或窗口函数、DISTINCT、GROUP BY、HAVING、LIMIT、UNION 或 UNION ALL、选择列表中的子查询，以及用户变量赋值，都可能阻止 CTE 被合并。递归 CTE 则始终物化。

下面的写法把“需要汇总的订单”单独命名。它只是判断结构的示例，不代表某个真实业务表的执行结果：

WITH order_total AS (
    SELECT customer_id, SUM(amount) AS total_amount
    FROM orders
    -- GROUP BY 会形成独立的聚合结果，通常不能按普通简单查询合并。
    GROUP BY customer_id
)
SELECT c.id, ot.total_amount
FROM customers AS c
JOIN order_total AS ot ON ot.customer_id = c.id
-- 外层条件表达最终筛选范围，是否下推要以执行计划为准。
WHERE c.status = 'active';

这里不应简单地把“有 GROUP BY”理解成“物化一定更快”。聚合结果很小、外层只取少量客户时，物化可能合理；如果聚合本身扫描了很大的订单表，真正需要先优化的可能是过滤条件、索引或统计信息。

用 MERGE 和 NO_MERGE 表达意图

当默认计划与工程意图不一致时，可以用优化器提示做单条语句级控制。MERGE(cte_name) 表示允许该 CTE 合并，NO_MERGE(cte_name) 表示禁用合并、倾向于物化。提示写在顶层 SELECT 上，并使用 CTE 名称：

WITH recent_orders AS (
    SELECT customer_id, order_id, created_at
    FROM orders
    -- 时间范围是示例边界，生产环境仍需结合索引和数据分布。
    WHERE created_at >= '2026-01-01'
), customer_tags AS (
    SELECT customer_id, tag
    FROM customer_tag
    -- 同一 CTE 被多个引用使用时，物化的复用价值需要单独评估。
    WHERE enabled = 1
)
SELECT /*+ MERGE(recent_orders) NO_MERGE(customer_tags) */
       ro.order_id, ct.tag
FROM recent_orders AS ro
JOIN customer_tags AS ct ON ct.customer_id = ro.customer_id;

提示不是无条件命令。MySQL 文档说明，技术约束不能被提示绕过；如果 CTE 的结构本身不允许合并，MERGE 不会把它变回可合并状态。相反，NO_MERGE 更适合表达“这个引用需要独立结果”的意图，而不是作为所有慢查询的固定补丁。

如果需要做更大范围的实验，可以关注 optimizer_switch 中的 derived_merge。但它会影响后续语句，生产排查时优先使用单条语句提示，避免把一个会话里的其它查询一起改变。

用 EXPLAIN 找到物化证据

不要只看传统表格格式中的一行估算。可以先用 JSON 计划观察查询块和子查询描述：

EXPLAIN FORMAT=JSON
WITH customer_tags AS (
    SELECT customer_id, tag
    FROM customer_tag
    -- 过滤条件帮助明确 CTE 的输入边界，结果是否物化仍由优化器决定。
    WHERE enabled = 1
)
SELECT /*+ NO_MERGE(customer_tags) */
       c.id, ct.tag
FROM customers AS c
JOIN customer_tags AS ct ON ct.customer_id = c.id
-- 只保留需要观察计划结构的业务过滤条件。
WHERE c.status = 'active';

在 JSON 计划中，可以重点找几类信号：

materialized_from_subquery：说明计划中存在来自子查询的物化描述；CTE 的完整子查询计划通常只在一个引用节点展开。
creating_tmp_table：优化器跟踪信息中可用于识别创建物化临时表的记录。
reusing_tmp_table：同一物化 CTE 被其它引用复用时，可看到复用关系。
自动索引：MySQL 可能在物化结果上添加索引，以便顶层语句通过更合适的访问方式读取。

MySQL EXPLAIN FORMAT=JSON 中 query_block、materialized_from_subquery、临时表复用和自动索引的证据关系图 — 图2：从 JSON 计划节点判断 CTE 物化与复用的结构图，不是运行截图。

合并后的 CTE 不一定会作为独立节点出现在计划里，可能只剩它底层表和外层查询块。因此“没有看到 CTE 节点”不能直接等同于“查询没有执行”；应结合提示、查询结构和实际计划层级一起判断。

形成一份可复用的选择清单

排查问题	更偏向合并	更值得评估物化
引用次数	只引用一次	同一 CTE 被多次引用
过滤下推	希望外层条件尽早过滤底层表	需要一个稳定的中间结果供多个引用使用
内部结构	简单投影和过滤	聚合、DISTINCT、窗口函数或递归 CTE
计划证据	底层表直接出现在外层计划中	出现物化子查询、临时表复用或自动索引线索