SQL CTE（Common Table Expression） vs 子查询 vs 临时表的性能与可读性对比

CTE有时比子查询慢，因其默认不物化，执行时可能被反复展开计算；mysql 8.0.31+需MATERIALIZED提示，postgresql需hint，SQL Server仅递归或特定条件下才物化。

CTE 为什么有时候比子查询还慢

CTE 不是性能优化语法糖，它不自动物化（PostgreSQL 12+ 默认不物化，SQL Server 可能递归才物化，MySQL 8.0 默认非物化），执行计划里往往和内联子查询一样被反复展开。你写 WITH cte AS (select ...) SELECT * FROM cte JOIN cte，引擎可能真的执行了两次底层查询。

• MySQL 8.0 中，WITH 默认不缓存结果；加 MATERIALIZED 提示（如 WITH cte AS MATERIALIZED (...) ...）才能强制物化，但仅限于 MySQL 8.0.31+
• PostgreSQL 对非递归 CTE 默认“inline 展开”，除非加 /*+ Materialize */ hint（需启用 pg_hint_plan）或改用 CREATE TEMP table
• SQL Server 在大多数情况下会把非递归 CTE 当作视图展开，只有显式加 OPTION (RECOMPILE) 或统计信息剧烈变化时，才可能触发临时物化

什么时候必须用临时表而不是 CTE

需要多次引用、中间结果集较大、涉及排序/聚合后再次过滤，或者要加索引——这些场景 CTE 基本扛不住，临时表才是正解。

• 典型信号：SELECT ... INTO #tmp 后紧接着 CREATE INDEX #tmp_idx ON #tmp(col) —— CTE 不支持建索引
• 数据量 > 10 万行且被 JOIN 多次时，#temp_table 的统计信息更准，执行计划更稳定；CTE 反复计算容易让优化器误估行数
• 跨批处理：CTE 生命周期只在单个语句内；若要 INSERT #t → UPDATE #t → SELECT FROM #t，只能用临时表

子查询嵌套过深导致可读性崩坏的识别点

当你发现 WHERE 里套了三层 (SELECT ... FROM (SELECT ...))，或 JOIN 条件里出现 (SELECT TOP 1 ...)，说明逻辑已经脱离“一次表达清楚”的范畴，该拆了。

• 嵌套子查询无法复用计算结果：同一子查询在 SELECT 列表和 WHERE 中各写一遍，不仅难维护，还可能被算两次
• 相关子查询（含外部列引用）在大表上极易拖慢：比如 WHERE t1.id IN (SELECT t2.ref_id FROM t2 WHERE t2.status = t1.status)，t1 每行都触发一次 t2 扫描
• 替代方案优先级：先试 CTE（逻辑分层清晰）→ 再试临时表（需复用/索引/多步加工）→ 最后保留子查询仅用于简单标量值（如 (SELECT MAX(created_at) FROM logs)）

CTE 真正不可替代的场景：递归和命名依赖链

只有两种情况 CTE 是语法刚需：递归查询（组织树、路径展开），以及多个 CTE 之间存在明确依赖顺序（A 依赖 B，B 依赖 C），且你不想用一堆嵌套子查询糊成一团。

• 递归必须用 WITH RECURSIVE：比如查部门上下级关系，cte AS (SELECT id, parent_id FROM dept WHERE id = 1 union ALL SELECT d.id, d.parent_id FROM dept d INNER JOIN cte ON d.parent_id = cte.id)
• 命名依赖链提升可读性：比如先 raw AS (...)，再 cleaned AS (SELECT ..., CASE WHEN ... END AS flag FROM raw)，最后 final AS (SELECT * FROM cleaned WHERE flag = 1) —— 比三层嵌套子查询直观得多
• 注意：这种“链式 CTE”在 PostgreSQL 和 SQL Server 中会被优化为流水线执行；但在 MySQL 8.0 中仍可能重复计算上游 CTE，慎用于大数据量

真正卡住性能的往往不是语法选型本身，而是没意识到 CTE 默认不缓存、临时表没建索引、或者把相关子查询当成了“理所当然”的写法。查执行计划里的实际行数和重用次数，比背口诀管用。