CTE不是性能优化开关，而是语法组织方式；MySQL 8.0默认不物化，PostgreSQL 12+默认内联展开，SQL Server多视图展开，相同逻辑常生成 identical 执行计划，仅当显式强制物化或含递归时才可能提升性能。
CTE 和子查询在执行计划里经常一模一样
MySQL 8.0 默认不物化 CTE，PostgreSQL 12+ 默认 inline 展开，SQL Server 大多时候也当视图展开——这意味着你写 WITH cte AS (SELECT ...) SELECT * FROM cte JOIN cte，优化器很可能真地执行两次底层查询，而不是“算一次存起来”。实际执行计划里，cte 和等价的内联子查询往往生成完全相同的树结构。所以别默认以为“用了 WITH 就更快”，它不是性能开关，只是语法组织方式。
JOIN 改写相关子查询几乎总是更优
当子查询出现在 WHERE 或 HAVING 中且引用外层列（比如 WHERE t1.id IN (SELECT t2.ref_id FROM t2 WHERE t2.status = t1.status)），这就是相关子查询，性能极易崩坏：t1 每行都触发一次 t2 扫描。这类场景必须改写：
- 优先用
INNER JOIN或LEFT JOIN ... WHERE t2.ref_id IS NOT NULL - 用
EXISTS替代IN（尤其当子查询可能返回 NULL） - CTE 在这里没用——它无法改变相关性，反而可能让优化器更难识别半连接意图
CTE 真正能提升性能的两个硬条件
CTE 只有在满足以下任一条件时，才可能比等效子查询快：
- 数据库版本支持强制物化，且你显式写了提示：
WITH cte AS MATERIALIZED (...)（MySQL 8.0.31+）、/*+ Materialize */（PostgreSQL 需 pg_hint_plan）、或 SQL Server 的OPTION (RECOMPILE)触发临时缓存 - CTE 内含递归逻辑（如
WITH RECURSIVE tree AS (... UNION ALL ...)），这是子查询根本无法表达的
除此之外，所谓“CTE 更快”基本是错觉——它只是让代码看起来分步了，但引擎未必真分步执行。
数据量 > 5 万行且需多次引用时，别硬扛 CTE
如果你发现同一个中间结果要被 JOIN 两次、或既要聚合又要过滤再排序，CTE 很可能反复计算，还无法建索引。这时候该切到临时表：
CREATE TEMPORARY TABLE #tmp AS SELECT ...CREATE INDEX idx_tmp_col ON #tmp(col)- 再
SELECT FROM #tmp JOIN #tmp—— 行数预估准、I/O 可控、索引生效
CTE 不建索引、不持久、不统计，大中间集上它只是个漂亮的幻觉。
