使用WITH子句会拖慢SQL查询吗？多WITH子句性能咨询

WITH子句的后端处理机制 #

不同数据库对WITH子句（公共表表达式CTE）的处理逻辑差异很大，没有统一的“临时表/子查询等效”结论：

• 非递归CTE：
  • 多数主流数据库（如PostgreSQL、SQL Server）的优化器会根据场景自动选择：如果CTE只被引用一次，大概率会直接展开成等效的嵌套子查询执行；如果被多次引用，或者CTE本身是复杂聚合、返回数据量小，会选择物化——生成临时表存储结果，后续引用直接读取临时表
  • MySQL 8.0+默认会把非递归CTE展开成子查询，只有显式加MATERIALIZED关键字才会强制生成临时表
• 递归CTE：所有支持的数据库都会做特殊处理，不会展开成子查询，而是通过迭代方式处理递归逻辑

WITH子句的合理数量上限 #

语法层面几乎没有硬性上限，但实际业务中的“合理值”取决于三个核心因素：

• 单个CTE的开销：如果每个CTE都要全表扫描大表、做复杂多表关联，哪怕3个都可能拖慢整体查询
• 优化器的全局优化能力：部分数据库优化器在处理大量CTE时，可能无法做跨CTE的执行计划优化，导致执行效率下降
• 代码的必要性：如果拆分CTE只是为了代码可读性，那只要性能达标，8个甚至更多都没问题；但如果是为了绕开某些查询限制而强行拆分，反而可能引入冗余计算

经验上，当CTE数量超过5个时，建议逐一检查：是否有可以合并的CTE（比如两个独立的聚合查询可以合并成一个多列聚合）、是否有CTE只被引用一次可以直接替换成子查询。

如何对比WITH方案与其他方案的速度 #

1. 分析执行计划
   用数据库自带的执行计划工具：

   • PostgreSQL：EXPLAIN ANALYZE 查看是否有Materialize节点
   • MySQL：EXPLAIN FORMAT=JSON 检查CTE是否被展开
   • SQL Server：启用图形化执行计划，查看是否有“临时表扫描”步骤
     重点关注扫描行数、索引命中率、关联类型，判断CTE的处理方式是否合理

2. 实际跑测对比
   在和生产环境数据量一致的测试环境中：

   • 先跑一次查询预热缓存，再多次执行取平均时间
   • 分别测试WITH版本、嵌套子查询版本、显式临时表版本（CREATE TEMP TABLE ... AS SELECT ...）的执行时间、CPU/IO占用

3. 改写验证

   • 把只被引用一次的CTE直接替换成嵌套子查询，对比性能变化
   • 把被多次引用的复杂CTE改成显式临时表，手动控制物化，看是否比自动处理的WITH更快

内容的提问来源于stack exchange，提问作者Alexander The Great