CTE 物化与合并

做的事：同一个CTE，只改引用次数

CTE本身定义没有变化，改变的只是外层查询引用它一次还是两次。

代码WITH cust_totals AS ( SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id ) SELECT * FROM cust_totals WHERE customer_id = 500; -- 5万行orders，按8000个customer_id分组求和

验证：只引用一次时，整条查询在优化阶段就被算完了

EXPLAIN FORMAT=JSON（节选query字段）with `cust_totals` as ( select customer_id, sum(amount) as total from orders where (orders.customer_id = 500) ← WHERE条件被下推进了CTE内部 group by customer_id ) select '500' as customer_id, '722.43' as total from dual ← 整个查询被替换成了字面量！

-> Rows fetched before execution  (cost=0..0 rows=1)
   (actual time=125e-6..166e-6 rows=1 loops=1)   ← 0.000125～0.000166秒

外层WHERE customer_id = 500被下推进了CTE定义内部，GROUP BY因此从"对8000个分组挨个求和"退化成了"只对customer_id=500这一个分组求和"。既然分组只剩一个、结果在优化阶段就能唯一确定，MySQL干脆把整条查询在优化时直接求值，执行计划变成了Rows fetched before execution——这是通常只在SELECT 1这类常量查询里才会见到的计划类型，真实耗时降到了0.1毫秒量级。

验证：引用两次，优化器改为物化成一张临时表

代码WITH cust_totals AS ( SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id ) SELECT a.customer_id, a.total, b.total FROM cust_totals a JOIN cust_totals b ON a.customer_id = b.customer_id WHERE a.customer_id = 500;

-> Nested loop inner join  (actual time=73.9..73.9 rows=1 loops=1)
    -> Index lookup on a  (actual time=73.9..73.9 rows=1 loops=1)
        -> Materialize CTE cust_totals if needed
           (actual time=73.9..73.9 rows=20000 loops=1)   ← 真实对全部8000个分组物化
            -> Group aggregate: sum(orders.amount)  (actual time=15.6..63.6 rows=20000 loops=1)
                -> Index scan on orders using idx_cust  (actual time=15.5..58.5 rows=50000 loops=1)
    -> Index lookup on b  (actual time=0.00325..0.00367 rows=1 loops=1)
        -> Materialize CTE cust_totals if needed (query plan printed elsewhere)  (never executed)

结论：CTE被引用的次数，决定了优化器选哪条路

只引用一次时，条件下推 + GROUP BY退化 + 优化时常量折叠，三步叠加让整条查询几乎零成本(0.0001秒级)；引用两次以上时，为了保证多处引用看到的是同一份一致结果，优化器改为物化成临时表，退化成了"先算完整个CTE、再从物化结果里查"的经典路径(0.074秒)。两者相差接近600倍，但这不是"CTE性能差"，而是CTE被引用的次数直接决定了优化器能不能做条件下推——这和上一篇《派生表条件下推》里派生表(derived table)的行为逻辑是一致的，CTE本质上也是一种派生表，只是多了一个"可以被多次引用"的名字。

分步演示：引用1次 vs 引用2次的分叉点

每一步都是本文上面用EXPLAIN FORMAT=JSON和EXPLAIN ANALYZE跑出来的原始结果。