MySQL CTE 执行成本：合并、物化与语句内复用

把子查询改写成 WITH，SQL 变得好读，不代表数据库一定先把 WITH 里的结果算完并缓存下来。在 MySQL 8.4 中，非递归 CTE 可能合并进外层查询，也可能物化成语句内部结果。

这两个路径的成本不同，但没有“物化一定慢”或“复用一定快”的统一结论。要看外层条件能否尽早缩小数据，以及中间结果是否值得被多个引用共同使用。

同一段 WITH，可能没有独立的中间结果

用完整的小表先看结构：

CREATE DATABASE cte_plan_lab;
USE cte_plan_lab;
CREATE TABLE payment (
    id BIGINT NOT NULL PRIMARY KEY,
    account_id BIGINT NOT NULL,
    pay_state VARCHAR(20) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    KEY idx_account_state (account_id, pay_state)
) ENGINE=InnoDB;
INSERT INTO payment VALUES
    (1, 10, 'paid', 20.00), (2, 10, 'paid', 30.00),
    (3, 20, 'paid', 90.00), (4, 20, 'new', 10.00);

EXPLAIN FORMAT=TREE
WITH paid AS (
    SELECT account_id, amount FROM payment WHERE pay_state = 'paid'
)
SELECT SUM(amount) FROM paid WHERE account_id = 10;

如果 CTE 被合并，优化器可以把它与外层 account_id 条件一起考虑，直接规划 payment 的访问路径。WITH 在这里主要是查询表达层次，并没有强迫数据库先取出全部 paid 记录。

“合并”也不是把文本机械拼接。优化器仍需保持语义，是否允许合并还受查询结构与优化开关影响。小样本的实际计划以本机输出为准，不能从语法外观猜执行顺序。

用提示做受控对照，不是永久钉死计划

EXPLAIN FORMAT=TREE
WITH paid AS (
    SELECT account_id, amount FROM payment WHERE pay_state = 'paid'
)
SELECT /*+ NO_MERGE(p) */ SUM(p.amount)
FROM paid AS p
WHERE p.account_id = 10;

这里提示针对引用别名 p，目的是观察不合并时的结构。执行后看 SHOW WARNINGS，确认提示有没有被接受，再检查计划是否出现物化相关节点。不能在提示无效时，仍把两份相同计划解释成“两个方案性能一样”。

物化通常涉及建立语句内部结果，并可能为访问它增加索引。成本取决于中间行数、宽度与后续访问；优化器也可能推迟物化，若外层已经不需要任何行，就避免做那份工作。不能把物化想成执行一开始必然无条件完成的大文件。

某些结构天然阻止合并，例如聚合或分组。下面的汇总 CTE 与前一条逐行 CTE 就不是同一类对象：

EXPLAIN FORMAT=TREE
WITH totals AS (
    SELECT account_id, SUM(amount) AS paid_amount
    FROM payment WHERE pay_state = 'paid'
    GROUP BY account_id
)
SELECT account_id, paid_amount
FROM totals WHERE paid_amount >= 40;

这个条件作用在聚合结果上。改写时若把它误当成原始每行金额过滤，结果会变。SQL 优化首先必须保持聚合语义，不能只追求少一个计划节点。

多次引用的复用，仍只属于这条语句

MySQL 对选择物化的 CTE 可以在同一条语句的多个引用之间复用物化结果，甚至为不同引用构建合适的内部访问索引。但这不代表 CTE 变成跨请求缓存。

一次查询结束，再执行相同文本，仍是一条新的语句。源表提交了新数据，下一次查询需要按自己的事务视图解释；WITH 不会替应用保留上一次结果和失效时间。

如果报表需要一分钟刷新一次的汇总，那已经是独立的缓存或预计算设计，需要处理刷新、过期和失败。把大 SQL 包进 WITH，只改善了表达，并没有建立这个生命周期。

怎样比较才不被缓存冷热误导

先核对两种写法的结果集合和数值，再比较计划。涉及 SUM、NULL、重复连接或 LIMIT 时，即使返回行数相同，也可能计算了不同东西。

真正要测成本时，使用代表性数据分布和参数。记录实际读取、循环次数、中间结果规模以及资源使用，而不是只跑两遍计时，把第二次命中缓冲池的收益归到 CTE 写法上。EXPLAIN ANALYZE 会执行查询，也要按真实负载处理。

如果 CTE 中无意选择了很宽的正文或 JSON 列，中间结果的宽度可能比是否写 WITH 更值得先修。只保留后续需要的字段，比给所有 CTE 加统一的 NO_MERGE 提示更容易解释。

另一方面，同一个昂贵结果被多处重复使用时，合理物化可能减少重复工作。不要为了“所有条件都下推”无限展开同一计算，最后让数据库做了多份相同聚合。

判断停在执行计划能说明的地方：合并让哪些条件一起规划，物化生成多少中间数据，复用减少了哪些重复工作。WITH 是组织查询的工具，执行策略需要由当前结构、数据和版本共同验证。