CTE优化需满足四前提:谓词下推、分号补全、列精简、递归收敛,否则反拖慢;它不缓存不索引,仅提供重写机会。

在存储过程中用 CTE 优化计算,不是加个 WITH 就自动变快——它不缓存、不建索引、不物化,只是给查询优化器多一次重写机会。真正起效的前提是:你得把谓词下推、分号补全、列精简、递归收敛这四件事做对,否则反而拖慢执行。
WITH 前必须加分号,否则存储过程直接编译失败
SQL Server 和 PostgreSQL 都要求 WITH 是语句开头;如果前一条是 SET @tenant_id = 123 或 IF @debug = 1 PRINT 'start',末尾没分号,就会报 Incorrect syntax near the keyword 'with'。
- 所有 CTE 定义前统一加
;,哪怕前面是空行或注释 - DBeaver / SSMS 的自动补全不可靠,生产脚本必须显式写
- 特别注意
DECLARE、SET、PRINT等语句末尾是否带分号
谓词不能只写在外层 WHERE,必须下推到每个 CTE 内部
比如查某租户的活跃订单,若只在最终 SELECT 加 WHERE tenant_id = @tenant_id AND status = 'active',CTE 可能先扫描全表再裁剪——而 @tenant_id 本可走索引。
- 每个 CTE 子查询里都带上
WHERE tenant_id = @tenant_id - 避免在 CTE 中用
SELECT *,只取后续真正需要的列 - 如果 CTE 被
UNION ALL多次引用且基表大,考虑改用临时表并建索引
递归 CTE 在存储过程中不能“封装复用”,必须单条语句直跑
MySQL 和 PostgreSQL 均不支持在存储过程中参数化重用递归 CTE;强行塞进 EXECUTE 或拼接字符串,会导致每次执行都重新解析,无法复用执行计划。
- 锚点查询(如
WHERE id = @root_id)必须走主键或唯一索引 - 递归成员的 JOIN 字段类型必须严格一致(
INT对VARCHAR会跳过索引) - 加
level并显式限制深度:WHERE st.level ,别依赖数据库默认终止 - 真要“存储过程式调用”,应让过程只做参数校验,然后
EXECUTE一个预编译好的递归查询(如 PostgreSQL 的PREPARE或带参数视图)
CTE 不是临时表,别指望它缓存中间结果
CTE 没有物理存储,不支持索引,也不保留统计信息。你看到的“逻辑拆分”只是语法层面的组织方式,实际是否物化完全取决于数据库版本和查询复杂度。
- 如果同一个 CTE 在主查询中被引用多次(如
JOIN t1 ON ... UNION ALL SELECT * FROM t1),绝大多数场景下会被执行两次 - PostgreSQL 12+ 支持
MATERIALIZED提示,但 SQL Server 和 MySQL 仍无等效机制 - 性能瓶颈真正在于执行计划里的
Table Spool或Compute Scalar节点是否出现——先看EXPLAIN ANALYZE,再改代码

















