MySQL CTE 物化对复杂查询性能的影响

MySQL CTE 不一定物化。可以合并时，优化器可能把它并入外层查询；物化时则建立内部临时结果。性能取决于过滤范围、中间结果大小和复用方式，而不是 WITH 语法本身。建议先保留默认优化，再用相同 SQL 的 MERGE 与 NO_MERGE 对照计划，确认差异后决定是否固定策略。

下文针对 MySQL 8.4 的非递归 CTE、派生表与执行计划。官方地址：https://dev.mysql.com/doc/refman/8.4/en/。示例未实际连接数据库执行，不提供测速结论；四条演示记录也不足以代表复杂生产查询的成本。

先用一条可合并 CTE 比较两种计划

先建一个只包含必要字段的测试表。示例中的 paid 只做投影与过滤，没有聚合、去重或 LIMIT，便于观察合并和物化的差别。

-- 在独立测试库使用本示例，表中数据只用于观察计划形状。
CREATE TABLE cte_order_demo (
    id BIGINT NOT NULL PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    state VARCHAR(12) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    INDEX ix_state_customer (state, customer_id)
) ENGINE=InnoDB;

INSERT INTO cte_order_demo VALUES
(1, 17, 'paid', 28.00),
(2, 17, 'paid', 62.00),
(3, 18, 'paid', 41.00),
(4, 18, 'new', 19.00);

-- 两条查询只改变提示；CTE定义及结果条件保持一致。
EXPLAIN FORMAT=TREE
WITH paid AS (
    SELECT id, customer_id, amount
    FROM cte_order_demo
    WHERE state = 'paid'
)
SELECT /*+ MERGE(paid) */ id, amount
FROM paid
WHERE customer_id = 17;

-- 查看提示语法或解析产生的警告，不把注释存在当成提示已生效。
SHOW WARNINGS;

EXPLAIN FORMAT=TREE
WITH paid AS (
    SELECT id, customer_id, amount
    FROM cte_order_demo
    WHERE state = 'paid'
)
SELECT /*+ NO_MERGE(paid) */ id, amount
FROM paid
WHERE customer_id = 17;

SHOW WARNINGS;

两条查询都要求返回客户 17 的已付款订单，提示写在引用 CTE 的外层 SELECT 上。MERGE 并不能推翻其他不允许合并的规则；NO_MERGE 也不是性能提升指令。读者需要看实际计划，而不是只看 SQL 中是否包含提示注释。

若计划直接呈现底表访问与过滤，应结合整体结构判断是否已合并；若出现物化及其临时结果访问，则继续关注生成了多少内容、被如何访问。不应要求所有版本、输出格式和数据规模都出现完全相同的节点文字。

物化的收益和代价取决于中间结果

对于已经物化的 CTE，同一条查询中的多次引用可以复用该物化结果；这不是跨请求缓存。优化器还可能为临时结果建立有助于访问的索引，但不保证每次建立，也不能像永久表一样要求一个固定的索引方案。

代价也不能只看“算一次”。生成大量行、携带大字段、构建内部访问结构以及重复读取，都属于可能的成本。优先让 CTE 只投影真正需要的字段，并把语义允许的限制放在清楚的位置；不要通过改变聚合输入或过滤时机偷偷改变结果。

物化也不等于所有行都会先无条件算完。优化器存在延迟物化等策略，派生条件下推也有其适用条件。因此，“强制物化后外层条件绝不可能影响内部工作量”同样是过度概括，应回到计划观察。

聚合与派生表会改变可选方案

GROUP BY、聚合函数、窗口函数、DISTINCT、LIMIT 等结构会限制合并。下面的 totals 按客户汇总，paid_amount 是汇总后的结果，不能把金额门槛随意挪成对每笔订单的筛选。

-- GROUP BY及聚合使这一查询块不适合被直接合并到外层。
EXPLAIN FORMAT=TREE
WITH totals AS (
    SELECT customer_id, SUM(amount) AS paid_amount
    FROM cte_order_demo
    WHERE state = 'paid'
    GROUP BY customer_id
)
SELECT customer_id, paid_amount
FROM totals
WHERE paid_amount >= 50;

-- 改为派生表仍要面对相同类型的优化约束，不是消除物化的开关。
EXPLAIN FORMAT=TREE
SELECT t.customer_id, t.paid_amount
FROM (
    SELECT customer_id, SUM(amount) AS paid_amount
    FROM cte_order_demo
    WHERE state = 'paid'
    GROUP BY customer_id
) AS t
WHERE t.paid_amount >= 50;

把 WITH 改写为 FROM 中的派生表，并不会自动消除相同的结构约束。两种写法都由优化器选择适用方案；是否保留 CTE，可以同时考虑复用、可读性和计划，而不是把某种语法当作“临时表开关”。递归 CTE 的规则不同，本文不把这个对照扩展到递归查询。

执行计划里应记录哪些证据

普通 EXPLAIN 提供计划与估计，不等于实际耗时。需要实际执行信息时，可由读者在受控环境使用下面的形式；这会真正运行 SELECT，不能当作没有负载的静态检查。

-- 此语句会真正运行SELECT；仅在允许的测试环境评估耗时与资源。
EXPLAIN ANALYZE FORMAT=TREE
WITH paid AS (
    SELECT id, customer_id, amount
    FROM cte_order_demo
    WHERE state = 'paid'
)
SELECT /*+ NO_MERGE(paid) */ id, amount
FROM paid
WHERE customer_id = 17;

比较时至少记录：实际采用的组织方式、底表访问条件、中间结果输出行数、各访问节点的 loops，以及完整查询耗时。父子节点计时存在包含关系，不要把所有节点的时间直接相加；loops 也应与每轮行数、计时口径一起解读。估计行数和实际行数差距大时，结论应保留数据分布这一因素。

用相同参数、相同数据快照和一致的运行条件比较两个方案。结果集合必须保持一致，不能一边改变条件，一边把耗时下降归功于物化。没有原始计划与测试条件时，只能提出候选优化方向，不能报告加速比例。

保留默认策略，还是固定物化

单次引用且过滤很窄的查询，可先让优化器保持选择空间；多次引用一个较昂贵且紧凑的结果，值得评估物化复用。如果真实计划和重复测试没有稳定收益，就不必为了 SQL 看起来更“优化”而保留提示。

固定 NO_MERGE 后，应记录适用参数范围和撤销条件。数据规模、引用方式或查询条件变化，原先的取舍可能不再成立。不要为了局部查询试验而改变整个实例的合并策略，也不要在没有对照的情况下把提示复制到所有 CTE。

写 WITH 就只计算一次吗？不能仅凭语法这样判断。先看是否物化；物化结果在同一查询内的复用，也不等于后续请求还能复用。

发现 Materialize 就说明查询慢吗？不是。它只描述策略，是否合适取决于生成和使用这个结果的总工作量。

提示写了但计划没变怎么办？先读警告、核对引用名称与合并限制。相同计划不意味着提示解析成功，更不意味着某一种策略一定更快。

参考：MySQL：CTE 与派生表的合并、物化及复用；EXPLAIN 与实际执行信息。