上周排查一个慢接口时发现业务代码里用了一个while循环去查“该部门下还有没有子部门”一层一层拼查询累计对数据库发起了上百次请求接口响应直接跑到了 3.8 秒。我的第一反应是这种树形结构查询本该在 MySQL 里一条 SQL 一次搞定。MySQL 8.0 的递归查询WITH RECURSIVE就是专门解决这类问题的它能把“循环查库”变成“数据库内部迭代”既省掉了网络往返也让代码逻辑干净很多。这篇文章我会完整拆解递归查询的语法、执行原理、实战场景和踩坑记录适合后端开发、数据分析师、DBA以及准备 MySQL 面试题的人。1. 树形数据查询的经典困境为什么业务代码里一层层查不靠谱1.1 我踩过的 N1 查询坑曾在项目里接一个组织架构页面需求很简单给定一个部门 ID查出它下面所有子部门以及人员。最初版本是在 Java 里写递归函数每层查一次库。部门层级只有 5 层节点数不到 300 个接口却跑了 3.8 秒。问题就出在 N1 查询每次递归都要发起一次数据库往返然后逐层拼接。层数一深查询次数呈指数级增长。如果流量上来数据库连接池很快被占满接口雪崩只是时间问题。后来我在慢日志里数了一下一次接口产生了 1000 多次简单的SELECT这些时间几乎全部浪费在网络往返和 SQL 解析上。1.2 传统取巧方案为什么治标不治本没有递归 CTE 的年代大家用过不少替代方案应用层递归查询逻辑清晰但存在 N1数据量大时性能不忍直视。存储过程循环把迭代放在数据库里代码写法绕而且游标循环性能一般调试也麻烦。冗余路径字段比如加一个ancestor_path字段每次写数据时维护/1/2/3/这样的字符串。查询效率高但写入逻辑容易漏数据一乱就出大问题。一次性查全表内存里组树数据量小的时候很舒服但表到百万级之后一次全量读既浪费内存又拖慢其他查询。这些方案都能跑但都有明显的“补偿成本”。1.3 递归查询的核心价值MySQL 8.0 引入的 WITH RECURSIVE本质是把“循环迭代”下沉到数据库执行器数据库自己维护工作台、逐层扫描、拼接结果最终只返回一次最终结果集。你的代码只需要一条 SQL拿到的是一棵完整的树。这套机制非常适合组织架构、权限树、菜单树、BOM 物料清单这类“父子节点同一张表”的场景。前提只有一个你的 MySQL 版本不低于 8.0。如果你的环境还是 5.7 甚至更老的版本建议先参照官方安装教程把版本升上来否则后面的东西都用不了。2. WITH RECURSIVE 语法拆解一条 SQL 把“循环”写进数据库2.1 先跑通最小示例生成 1 到 10 的数字序列递归查询最经典的入门用法是生成连续数字序列。先跑通这个小例子后面看复杂的树形查询就不慌了WITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n 1 FROM seq WHERE n 10 ) SELECT * FROM seq;这段 SQL 会输出 1 到 10。WITH RECURSIVE关键字声明这是一个递归的公共表表达式seq是这个临时结果集的名字后面可以像子查询表一样引用它。2.2 锚点成员、递归成员和执行顺序三个要理解对这段查询里有两次SELECT它们各有分工锚点成员anchor memberSELECT 1 AS n它是整个递归的起点只执行一次产生第一批结果。递归成员recursive memberSELECT n 1 FROM seq WHERE n 10它引用了seq自己反复执行每次都在上一批结果上继续计算。执行顺序不要理解错。数据库不是先把seq整个算完再跑递归而是先跑锚点把结果放进工作台working table。此时seq里只有一行1。执行递归成员读取工作台里的上一批数据拼出新一批结果。此时从1推出2seq的结果集变为1,2。继续迭代把上一批的2推出33推出4……直到递归成员不再产生新行。所有结果累积合并输出最终的seq。锚点负责“种子”递归成员负责“繁衍”外层查询负责“收获”。理解这个三步是看懂一切递归查询的基础。2.3 为什么递归部分不允许 ORDER BY、LIMIT、聚合和 DISTINCT很多人第一次写递归时想把中间结果排序或者分页结果直接报错。原因在于递归语义每一轮的输出集都作为下一轮输入如果你在中间层用ORDER BY或LIMIT下一轮的“驱动数据”就变了结果不再稳定。聚合函数和窗口函数也是同理它们会让“每一层基于上一层的全集计算”这个规则被打破。所以 MySQL 的语法限制很明确递归成员里不能用DISTINCT、GROUP BY、ORDER BY、LIMIT也不能用聚合函数和窗口函数。整个递归 CTE 算完之后外层查询可以做排序和分页那完全没问题。2.4 循环是怎么停下来的工作台枯竭机制递归如果没有终止条件理论上会无限循环。MySQL 靠两个机制兜底递归成员不再产生新行迭代自然结束。迭代次数超过系统上限强制报错终止。理解第一点很关键。比如数字序列例子里的WHERE n 10当工作台里最新一批是9时递归成员生成10当工作台是10时下一轮因10 10不成立产生 0 行于是循环停止。如果递归部分写的是UNION DISTINCT默认的UNION就是UNION DISTINCT还有一个额外机制当本轮生成的所有行都已经存在于前面累积的结果集中时也会判定终止。这在防数据环时有帮助但别指望它兜住所有脏数据后面我会专门说防环的写法。3. 三个实战场景组织架构、汇报链路、BOM 展开3.1 组织架构向下展开查某个部门的所有子部门假设有一张员工表CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50), manager_id INT, KEY idx_manager (manager_id) ); INSERT INTO employees VALUES (1, 老板, NULL), (2, 技术总监, 1), (3, 产品总监, 1), (4, 后端组长, 2), (5, 前端组长, 2), (6, 后端开发, 4), (7, 前端开发, 5), (8, 产品专员, 3);现在要查“技术总监”及其所有下级SQL 这样写WITH RECURSIVE emp_tree AS ( SELECT id, name, manager_id, 1 AS lvl FROM employees WHERE id 2 UNION ALL SELECT e.id, e.name, e.manager_id, t.lvl 1 FROM employees e INNER JOIN emp_tree t ON e.manager_id t.id ) SELECT id, name, manager_id, lvl FROM emp_tree ORDER BY lvl, id;执行结果idnamemanager_idlvl2技术总监114后端组长225前端组长226后端开发437前端开发53这里锚点用WHERE id 2选中起点部门递归成员用e.manager_id t.id找“谁的上级是上一批人”t.lvl 1记录层级深度。外层排序只是为了输出美观不影响递归本身。3.2 向上溯源从某个节点找回整条汇报链路向下的场景很多人会写向上的场景容易卡壳。我一开始也把关联条件写反过。要找id6的后端开发的所有上级SQL 长这样WITH RECURSIVE up_tree AS ( SELECT id, name, manager_id, 1 AS lvl FROM employees WHERE id 6 UNION ALL SELECT e.id, e.name, e.manager_id, t.lvl 1 FROM employees e INNER JOIN up_tree t ON e.id t.manager_id ) SELECT id, name, manager_id, lvl FROM up_tree ORDER BY lvl DESC;注意递归成员的连接条件变成了e.id t.manager_id。意思是拿上一批记录的manager_id去匹配员工表的主键从而找到直属上级。这就是“从叶子往根走”的方向。结果会是后端开发 - 后端组长 - 技术总监 - 老板。理解连接方向向上和向下只是一念之差。3.3 树形输出用 lvl 控制缩进用外层排序控制展示顺序实际做页面时光拿到数据还不够需要在展示层按层级缩进。可以在递归里带上lvl然后输出时用CONCAT拼出缩进WITH RECURSIVE emp_tree AS ( SELECT id, name, manager_id, 1 AS lvl FROM employees WHERE id 1 UNION ALL SELECT e.id, e.name, e.manager_id, t.lvl 1 FROM employees e INNER JOIN emp_tree t ON e.manager_id t.id ) SELECT id, CONCAT(REPEAT( , lvl - 1), name) AS tree_name, manager_id, lvl FROM emp_tree ORDER BY lvl, id;REPEAT( , lvl - 1)会根据层级生成前导空格缩进效果一目了然。要注意一点MySQL 默认没有启用PIPES_AS_CONCAT||是逻辑或不是字符串拼接。拼字符串老老实实用CONCAT或CONCAT_WS否则结果会很诡异。3.4 BOM 物料多级展开递归中做乘积和汇总BOM物料清单是递归查询的又一个高频场景。假设一张物料关系表CREATE TABLE bom ( parent_id INT, component_id INT, qty DECIMAL(10,2), PRIMARY KEY (parent_id, component_id) ); INSERT INTO bom VALUES (1, 2, 2.00), (1, 3, 1.00), (2, 4, 3.00), (2, 5, 4.00), (3, 5, 2.00), (3, 6, 1.00);展开成品1到底需要哪些基础物料每个物料累计需要多少数量WITH RECURSIVE bom_tree AS ( SELECT component_id, qty, 1 AS lvl FROM bom WHERE parent_id 1 UNION ALL SELECT b.component_id, b.qty * t.qty, t.lvl 1 FROM bom b INNER JOIN bom_tree t ON b.parent_id t.component_id ) SELECT component_id, SUM(qty) AS total_qty FROM bom_tree GROUP BY component_id;这个例子的精髓在于递归成员里的b.qty * t.qty每一层的物料数量都要乘以上一层的累计数量最终才能算出“成品 1 需要多少个零件 5”。如果你只展开结构不去乘数量那 BOM 算出来的就是错账。最后用GROUP BY汇总是因为同一个物料可能从多个父节点到达需要合并数量。4. 深度与性能默认 1000 层上限背后的设计哲学4.1 现场还原ERROR 3636 是怎么来的有一次生产环境跑组织架构定时任务突然报错ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing cte_max_recursion_depth to a larger value.当时的第一反应是“递归死循环了”查了数据才发现组织架构表里有两条脏数据A 的上级是 BB 的上级又是 A形成了一个环。递归查询在环上每转一圈就产生新行永远无法通过“产生 0 行”来自然终止直到撞上上限 1000 层强制结束。这正是 MySQL 给递归上保险丝的原因只靠业务逻辑保证“数据无环”太脆弱必须在执行器层面加一道硬限制。默认 1000 层对绝大多数组织架构、菜单树都够用了超过它的数据形态第一反应应该是查数据质量而不是反手调大参数。4.2 两个上限参数设置成多少才安全MySQL 8.0 里跟递归深度上限相关的参数有两个参数名默认值作用cte_max_recursion_depth1000限制递归 CTE 的迭代次数max_recursive_iterations1000存储程序里递归 CTE 的迭代上限遇到 ERROR 3636先别急着改配置。正确的排查顺序检查数据里有没有环。重点看父子节点是否相互引用或者自己指向自己。如果数据正常但业务确实需要超过 1000 层再考虑调参数。比如一个多级分销系统的链路真的可能跑到两千层。使用SET SESSION临时调整确认有效后再写进 my.cnf。两个参数建议一起改SET SESSION cte_max_recursion_depth 1000000; SET SESSION max_recursive_iterations 1000000;如果写进配置文件在[mysqld]段添加这两行重启后全局生效。我个人其实不建议把上限调得太大1 万层以上一旦遇到数据环数据库会被迭代任务拖得很惨接口超时都是小事严重时会把实例 CPU 打满。4.3 索引是递归查询性能的支点递归查询每一轮迭代都要把“上一轮的结果集”和业务表做连接连接列上没有索引就意味着每次都要全表扫描。我见过一张百万级员工表的递归查询深度只有 6 层全表扫描 6 次跑了 8 秒多。解决办法很朴素给递归列建索引。向下查询时manager_id是连接列CREATE INDEX idx_emp_manager ON employees(manager_id);向上查询时连接列是主键id主键自带索引不需要额外处理。BOM 表同理component_id上要建索引否则展开深度超过 4 层后性能会直线下降。想验证效果MySQL 8.0 可以用EXPLAIN ANALYZEEXPLAIN ANALYZE WITH RECURSIVE emp_tree AS (...) SELECT ...;它能看到每一轮迭代扫描了多少行、执行了多长时间是排查递归性能瓶颈最直接的武器。4.4 数据量和临时表空间什么量级的递归能扛递归的中间结果会存在临时表里深度越大、每层产生的行数越多临时表空间压力就越大。比如一个二叉结构每层翻倍到第 20 层就是百万行级别的中间结果对磁盘 IO 是不小的压力。所以写递归时要多留一个心眼锚点最好能先“收窄起点”比如常见做法是先把当前用户有权限的部门集合查出来再以这个集合为起点去递归而不是从大树的根部全量展开。递归内部能用WHERE提前过滤的别拖到外层才开始挡数据。数据库不是不让你做复杂查询但你得先替它把不必要的中间结果减掉。4.5 什么情况下别用递归高频深树场景要换思路递归 CTE 不是银弹。它最怕的场景是“树很深、查询极高频”。比如一个商品分类树有 15 层用户每次打开首页都要查整棵树每次递归都在线计算成本的浪费很可观。这种场景我会建议换成闭包表Closure Table维护一张独立的表把每对祖先-后代关系都存成一行。查询任意子树的成本从递归变成一次普通JOIN响应时间轻松到毫秒级。代价是写入时要维护多对关系写入链路变重。读写比高的场景闭包表的收益远大于成本。选型时先看你这个树是“读多写少”还是“写多读少”递归 CTE 适合写多读少、深度可控的实时计算场景。5. 常见坑与防护数据环、脏数据、类型不匹配5.1 环路数据汇报链断不了的现场上面提到的 ERROR 3636本质就是数据环。最常见的有两种自己指向自己UPDATE employees SET manager_id id WHERE id 5。互相指向A 的上级是 BB 的上级是 A。遇到第一种递归会在一个节点上反复迭代每轮结果都是同一行遇到第二种每轮会交替出现 A、B 两行。即便UNION DISTINCT能去重迭代也无法因“本轮结果被重复”而终止最终只能等上限报错。5.2 路径字段防环给递归装上保险丝最稳妥的防环办法是在递归路径里记录已经走过的节点。我给组织架构写的递归都会带一个path字段WITH RECURSIVE emp_tree AS ( SELECT id, manager_id, CONCAT(,, id, ,) AS path FROM employees WHERE id 2 UNION ALL SELECT e.id, e.manager_id, CONCAT(t.path, e.id, ,) FROM employees e INNER JOIN emp_tree t ON e.manager_id t.id WHERE INSTR(t.path, CONCAT(,, e.id, ,)) 0 ) SELECT id, manager_id, path FROM emp_tree;递归成员里的WHERE INSTR(...) 0就是保险丝如果当前员工 ID 已经出现在路径里说明又回到了已访问过的节点直接丢弃迭代继续推不下去。路径字符串用逗号包裹头尾是为了避免id2被id12误判这种边界问题。这个写法在数据已经脏了的情况下能把一次可能要撞上限 1000 的递归直接控制住。5.3 类型不一致与列数对不齐递归查询要求锚点成员和递归成员返回的列数必须一致MySQL 检查得很严格。常见报错是ERROR 1222 (21000): The used SELECT statements have a different number of columns锚点返回id, name两列递归成员却拼了三列必然报错。同理两部分的字段顺序也要一一对应否则数据就会张冠李戴。还有类型问题锚点里用了INT递归成员里却用字符串拼接导致隐式转换遇到排序或比较时结果容易出乎意料。建议递归 CTE 里所有列都先显式CAST成目标类型尤其在 BOM 那种还要做乘法运算的场景。5.4 5.7 及更早版本没有递归 CTE 怎么办如果你的生产库还在 MySQL 5.7这段可以直接看。5.7 不支持 WITH RECURSIVE只能用三种替代方案存储过程 临时表写一个循环把每层查到的结果插进临时表直到不再产生新行。代码量在 30 行以上但能复用数据库连接的效率。程序侧递归如果数据量不大应用层递归查询可以接受注意控制循环次数提前预防环。路径冗余字段写数据时维护ancestor_path读数据时用LIKE 前缀%或FIND_IN_SET。查询快但写入逻辑要严密建议在业务写入口统一封装。从长期维护的角度这些方案都是过渡手段。MySQL 8.0 的递归 CTE、窗口函数配合排序、分组场景、公用表表达式整体比 5.7 时代好用太多。与其在旧版本上补丁叠补丁不如立项升级。6. 面试与选型递归查询题目背后的考察点6.1 面试官最爱问的 5 个递归问题把递归查询相关的面经翻一遍高频问题基本就这五个问题一MySQL 递归查询怎么实现答用WITH RECURSIVECTE由锚点成员产出初始集递归成员基于上一批结果继续展开两者用UNION ALL或UNION DISTINCT合并直到递归成员不再产生新行。问题二递归查询如何终止答正常情况是递归成员产生 0 行后自然终止异常情况靠cte_max_recursion_depth兜底超过迭代上限会报 ERROR 3636此时优先排查数据环。问题三如何避免递归死循环答数据建模时保证父子关系无环查询时用路径字段记录已访问节点检测到重复就停止扩展必要时配合深度上限lvl n限制迭代层数。问题四递归查询性能瓶颈在哪答每轮迭代都要连接业务表连接列没索引会引发多次全表扫描中间结果集过大导致临时表空间压力深度过大导致迭代次数增多。优化手段是建索引、锚点收窄、提前过滤。问题五递归 CTE 和存储过程比有什么优势答CTE 声明式写法更简洁一条 SQL 可读性好存储过程过程式代码维护成本高。但遇到复杂业务逻辑时存储过程可以加事务、动态 SQL灵活性更强。大多数树形查询用 CTE 就够。6.2 MySQL、Oracle、SQL Server 三种写法的差异很多团队历史项目同时维护多种数据库面试也常拿这个对比考人。三种数据库的递归写法区别明显MySQL 8.0WITH RECURSIVE ... UNION ALL ...迭代上限默认 1000。Oracle传统写法是START WITH ... CONNECT BY PRIOR ...11gR2 以后也支持WITH RECURSIVE语法。CONNECT BY 的父子方向表达更紧凑但对理解执行机制有额外要求。SQL ServerWITH cte AS (... UNION ALL ...)通过OPTION (MAXRECURSION N)控制最大递归次数0表示不限制慎用。语法虽然有差异背后的“锚点 迭代 终止”思想完全一致。会了 MySQL 的递归看其他数据库的官方文档基本半小时就能上手。6.3 我的建议深树、完整树还是扁平引用建模时就要决定递归查询解决的是“同一张表里的父子结构”问题但设计表结构时就要想清楚这个树会多深、读多还是写多、要不要频繁查子树。深度 5 层以内、读写均衡直接用递归 CTE最省事。深度 10 层以上、高频读优先考虑闭包表查询复杂度降为一层 JOIN。只需要查“某个节点的所有祖先或后代”读量大路径枚举Path Enumeration也可以存/root/child/grandson/这样的字段查询用前缀匹配。整个树在几千节点以内内存里一次加载组树在应用层缓存连数据库压力都省了。没有万能的方案只有适不适合的表结构。递归 CTE 适合大多数“数据量可控、结构完整”的场景但碰上大规模深树高频读老老实实上闭包表。最后分享一个我自己的习惯写递归查询时除了最终业务字段我一定会带一个lvl深度列。不要只想着用路径字段防环lvl还可以在递归成员最前面的WHERE里加一层保险比如WHERE t.lvl 20。这个习惯在一次生产事故里帮我兜住了底脏数据形成的环还没来得及被路径字段发现时深度上限先一步拦住了递归避免数据库被空转迭代拖垮。给递归留一道硬性的安全闸口出任何问题都不会太难看。