with recursive 是数据库内建的高效迭代机制，而所谓“循环嵌套查询”实为静态嵌套子查询，并非真正循环；递归cte在单次查询中完成层级遍历，支持物化缓存、剪枝优化和深度控制，性能显著优于模拟循环方案。
递归查询（WITH RECURSIVE）不是“比循环嵌套查询更高效”，而是根本不在同一维度——SQL里压根没有原生循环语句，所谓“循环嵌套查询”通常是误用或硬凑出来的反模式。
为什么你看到的“循环嵌套”其实不是循环
很多人把多层嵌套子查询（比如 SELECT ... FROM (SELECT ... FROM (SELECT ...))）当成“循环”，但这是误解。它只是静态展开的、一次性执行的嵌套结构，数据库优化器会尝试扁平化，但不会迭代执行。真正意义上的“循环”在标准 SQL 中必须依赖存储过程、游标或客户端代码，而这些方案天然带来开销：
- 每次循环调用都是一次独立查询往返，网络/解析/计划生成成本叠加
- 游标默认逐行处理，无法利用向量化执行或并行扫描
- 中间结果无法被优化器统一调度，容易绕过索引
WITH RECURSIVE 是数据库内建的迭代机制
WITH RECURSIVE 让数据库引擎在单次查询中完成层级遍历，整个过程在内存或临时工作区中完成，不暴露给客户端。关键点在于：
- 锚成员（
SELECT初始集）和递归成员（SELECT ... FROM cte_name）被合并进一个执行计划，引擎可做联合估算、提前剪枝 - 现代数据库（PostgreSQL、SQL Server、MySQL 8.0+）会对递归 CTE 做物化缓存，避免重复计算上层结果
- 支持
MAXRECURSION（SQL Server）或search_depth（PostgreSQL）等控制参数，防止无限递归失控
性能差异最明显的三个场景
不是所有递归都该用 WITH RECURSIVE，但以下情况它几乎总是更优：
- 组织架构下钻：查某部门下所有子部门时，
WITH RECURSIVE一次扫描即可；用“自连接 N 次”要手动写 5 层 JOIN，且无法动态适配深度 - 路径展开：比如查用户推荐链路（A→B→C→D），递归 CTE 天然支持任意长度；模拟循环则需预估最大层级并硬编码 UNION ALL
- 日期/数字序列生成：
WITH RECURSIVE seq AS (SELECT 1 n UNION ALL SELECT n+1 FROM seq WHERE n 比用程序循环插入快一个数量级——因为全程在服务端内存运算，无 I/O 和事务开销
容易踩的坑：递归不等于自动高效
递归 CTE 的性能优势是有前提的：
- 锚成员必须能走索引，否则第一层就全表扫，后续所有递归都慢
- 递归成员的
JOIN条件或WHERE必须能利用索引，否则每轮都在膨胀数据集上暴力匹配 - MySQL 默认禁用递归（需
SET SESSION cte_max_recursion_depth = xxx），PostgreSQL 要显式加SEARCH DEPTH FIRST BY ...才能控制遍历顺序 - 别在递归成员里写聚合或窗口函数——多数引擎不支持，会报错或退化成多次子查询
真正决定效率的从来不是“用了递归”这个动作，而是你是否让每一层递归都落在索引键上。没索引的递归 CTE，比手写五层 JOIN 还慢。
