SQL递归查询通过递归公用表表达式（CTE）实现树形或图状数据的遍历，常见于组织架构、分类目录和物料清单等场景。它在语法上非常直观，但在数据量增长或层级加深后，往往成为系统最严重的性能瓶颈之一。理解其执行机制和潜在风险，是写出稳定查询的前提。
递归查询的基本写法与执行逻辑
以 PostgreSQL 和 MySQL 8.0 都支持的递归 CTE 为例，通常分为锚点查询和递归部分。锚点取出根节点，递归部分不断关联自身产生下一层。数据库会迭代执行递归体，直到没有新行产生或达到深度限制。
下面示例查询某个部门及其所有子部门。emp_dept 表中 parent_id 指向父部门，id 为主键。
WITH RECURSIVE sub_dept AS ( -- 锚点：根部门 SELECT id, parent_id, dept_name, 1 AS level FROM emp_dept WHERE id = 10 UNION ALL -- 递归：找直接子部门 SELECT e.id, e.parent_id, e.dept_name, s.level + 1 FROM emp_dept e INNER JOIN sub_dept s ON e.parent_id = s.id ) SELECT * FROM sub_dept;
从执行角度看，数据库会把上一次递归的结果集当作临时表，再与 emp_dept 做连接。每一层产生的中间结果都会保留，直到最终合并。如果树很宽或很深，中间行数会迅速膨胀。
这种“逐层扩散”的模型意味着，第 N 层的扫描成本取决于前 N-1 层累积的行数。一旦没有合适索引，连接就会退化为嵌套循环中的全表扫描，代价极高。
递归查询的主要性能风险
中间结果集膨胀
递归 CTE 不是先算完再返回，而是边迭代边累积。对于扁平但庞大的树，比如一个根下面有上万直接子节点，第一轮递归就已经产生上万行，后续任何操作都基于这个体量。
更严重的是图结构数据中存在循环引用，若没写防环条件，递归会无限进行，直到超出系统的递归步数限制或耗尽临时空间。因此生产环境必须明确终止逻辑。
缺失索引导致嵌套循环全表扫
递归部分一般是子表字段关联 CTE 的 id。如果 parent_id 上没有索引，每次连接都要全表扫描子表。假设层级为 10，每层扫描一次全表，总成本就是十次全表扫描叠加。
可以通过在 parent_id 上建立普通索引来显著改善，让连接变为索引查找。以下为建索引示例：
CREATE INDEX idx_emp_dept_parent ON emp_dept (parent_id);
添加索引后，优化器能用索引范围扫描替代全表嵌套循环，递归每一层的代价从 O(N) 降到 O(log N) 级别，整体响应时间通常能缩短数倍到数十倍。
递归深度与系统限制
多数数据库对递归 CTE 有最大迭代次数或栈深限制，例如 MySQL 默认递归 1000 次。超过会报错，但这本身也是一种保护。若业务真的需要更深遍历，应评估是否适合用 SQL 实时递归。
另外，递归查询难以并行化，很多优化器会把递归部分视为串行流水线，无法利用多核。面对超大规模层级数据，单条 SQL 递归的扩展性很差。
常见优化方案与实践
限制递归深度
如果业务只需要展示三级以内部门，直接在递归里加 level 限制，避免无谓下层展开。这样既能提速，也能防止意外深链。
WITH RECURSIVE sub_dept AS ( SELECT id, parent_id, dept_name, 1 AS level FROM emp_dept WHERE id = 10 UNION ALL SELECT e.id, e.parent_id, e.dept_name, s.level + 1 FROM emp_dept e INNER JOIN sub_dept s ON e.parent_id = s.id WHERE s.level < 3 ) SELECT * FROM sub_dept;
上述写法在递归体内过滤 level，使第四层不再生成，大幅减少行数。需要注意不同数据库对递归内 WHERE 的处理位置略有差异，应结合执行计划确认生效。
该方式简单有效，但仅适用于深度已知且较浅的场景。如果层级动态且深，单纯限深会丢失数据。
冗余路径字段
在插入或维护数据时，把从根到当前节点的路径写成字符串或数组存到 path 字段，例如 /1/10/25。查询某节点所有子孙时，只需一次前缀匹配，不再递归。
-- 查询 id=10 部门的所有后代 SELECT id, dept_name, path FROM emp_dept WHERE path LIKE '/1/10/%';
这种方法把 O(树高×宽) 的递归变成一次索引范围或前缀扫描，性能极稳定。代价是写入时要维护 path，且重排树结构需批量更新。
对于读多写少、层级复杂的系统，路径冗余往往是性价比最高的方案。配合 path 上的索引，可支撑千万级数据的秒级树查询。
闭包表设计
闭包表用一张独立关系表记录任意两节点的祖先-后代对及距离。查询子树只是一次等值连接，不依赖递归。虽然占用更多空间，但彻底解耦了遍历与实时计算。
-- 闭包表 ancestor_descendant 含 ancestor, descendant, depth SELECT d.* FROM ancestor_descendant a JOIN emp_dept d ON d.id = a.descendant WHERE a.ancestor = 10;
闭包表在频繁做层级查询、又不想每次跑递归的系统里非常合适。写入时通过触发器或应用层维护关系表，读性能则接近常数级。
相比路径字段，闭包表能自然表达多父节点和距离，更适合图型或复杂权限树。缺点是表体积随节点对增长，需控制历史数据。
利用执行计划定位慢点
不要凭感觉优化。用 EXPLAIN 观察递归 CTE 的执行计划，重点看递归部分是否出现 Seq Scan 或全表 Materialize。若出现，基本就是缺索引信号。
EXPLAIN ANALYZE WITH RECURSIVE sub_dept AS ( SELECT id, parent_id FROM emp_dept WHERE id = 10 UNION ALL SELECT e.id, e.parent_id FROM emp_dept e JOIN sub_dept s ON e.parent_id = s.id ) SELECT count(*) FROM sub_dept;
在输出里，若看到 Recursive Union 下方子查询有索引扫描，说明已走索引；若是反复全表扫，就要回到索引和模型设计上改。同时关注每次迭代返回行数，异常多即膨胀点。
把执行计划与业务数据分布结合，才能判断是该限深、加索引，还是换存储模型。递归查询不是不能用，而是要在清楚代价的前提下用。