MySQL的递归查询我前前后后用过不少次从最早在存储过程里写循环一条条查到后来换成WITH RECURSIVE一条SQL搞定整棵树这个转变确实让我省了不少心。如果你也遇到过“查组织架构要写半天代码”“菜单树查出来还得在Java里拼半天层级”这类问题这篇文章应该能帮你把递归查询这块彻底吃透。我会从语法原理、实战用法、性能优化到踩坑记录都过一遍不管你是刚接触MySQL的新手还是已经在业务里被树形结构折磨过的老手都能找到能直接抄的解决方案。1. 递归查询到底是什么它解决了什么问题1.1 没有递归查询时我们怎么处理树形结构先回忆一个场景一张部门表id、parent_id、name三个字段典型的邻接表结构。要查出某个部门下面所有子部门最笨的办法是先在应用层查出第一层子部门然后遍历每个子部门再查一次循环往复直到查不到子节点为止。这种写法本身没问题但问题也摆在明面上。一是SQL查询次数不可控。树的深度有多少层就要执行多少次查询如果每层有多个节点那N1问题会直接放大成N×M问题。二是应用层代码特别啰嗦。你得维护一个待遍历队列写递归函数或者while循环再加上去重、排序、组装树形结构光这些逻辑就能写上百行。三是事务边界不好控制。如果中途某次查询失败你很难说清楚数据一致性到底有没有被破坏。这也是为什么很多老项目里的树形结构查询代码极其难维护新接手的人看着那一堆循环和递归函数头皮发麻。而WITH RECURSIVE这种公共表表达式CTECommon Table Expression的递归写法本质上是把“反复查询同一张表并不断把结果集喂回给下一次查询”这件事放到了数据库引擎内部去完成。我们只需要描述清楚两件事从哪一行开始以及每一步如何从当前结果推导出下一步结果。1.2 WITH RECURSIVE 的语法拆解MySQL从8.0版本开始支持WITH RECURSIVE8.0之前只能用存储过程或者多层JOIN硬凑。它的基础语法长这样WITH RECURSIVE cte_name AS ( -- 锚点成员初始结果集 SELECT ... UNION ALL -- 递归成员引用cte_name自身生成下一批数据 SELECT ... ) SELECT * FROM cte_name;这里有几个关键点必须搞清楚。UNION ALL和UNION是有区别的前者保留全部重复行后者会去重。递归查询里绝大多数情况下我们用的是UNION ALL因为树形结构里每个节点在递归过程中只会被访问一次不太容易出现重复行让你非去重不可的情况而UNION的去重代价在递归场景下会明显拖慢速度。递归成员的查询里必须引用cte_name自身这是递归得以继续的原因。而且每次递归产生的数据会继续作为下一轮的输入直到某次查询返回空结果集递归就会自动终止。这个过程类似一个循环给定初始集合不断扩展集合直到不再有新数据。一个最小的例子用数字序列来理解WITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n 1 FROM seq WHERE n 10 ) SELECT * FROM seq;第一轮seq里只有1递归成员取出n1生成2第二轮拿2生成3……一直到n10时不满足n 10递归终止最终得到1到10的数字序列。这个例子虽然简单但它是理解一切递归查询的基础——锚点成员负责“点火”递归成员负责“持续加柴”条件负责“熄火”。1.3 一个小例子快速入门拿组织架构举例。假设有一张dept表id、parent_id、name根部门的parent_id为NULL。要查出id1这个部门下面所有的子部门可以这么写WITH RECURSIVE dept_tree AS ( SELECT id, parent_id, name, 0 AS depth FROM dept WHERE id 1 UNION ALL SELECT d.id, d.parent_id, d.name, dt.depth 1 FROM dept d INNER JOIN dept_tree dt ON d.parent_id dt.id ) SELECT * FROM dept_tree;第一轮选出了根部门深度是0第二轮把parent_id1的部门全部关联出来深度变成1接着把parent_id等于这批部门id的再关联出来深度继续加1。只要某层没有任何子部门能关联上递归自然停住。这个写法里有个细节值得注意递归成员里INNER JOIN dept_tree dt ON d.parent_id dt.id这个JOIN就是递归的“桥梁”。它把上一次的结果集dept_tree作为驱动表去原表dept里找孩子。理解了这行JOIN你就理解了八成递归查询的写法。2. 从零开始一张组织架构表搞定递归查询2.1 表设计与测试数据准备实际操作之前先建一张标准的分层表顺便加上索引避免后面排查问题时分不清是数据问题还是SQL问题。CREATE TABLE dept ( id INT PRIMARY KEY, parent_id INT NULL, name VARCHAR(50) NOT NULL, sort_no INT DEFAULT 0, KEY idx_parent_id (parent_id), KEY idx_sort_no (sort_no) ); INSERT INTO dept VALUES (1, NULL, 总公司, 1), (2, 1, 华东分部, 1), (3, 1, 华南分部, 2), (4, 2, 上海分公司, 1), (5, 2, 杭州分公司, 2), (6, 3, 广州分公司, 1), (7, 4, 浦东支公司, 1), (8, 5, 滨江支公司, 1);parent_id上建索引是必须的因为递归成员里每次都要用d.parent_id dt.id这个条件去扫描没有索引就是全表扫描乘以递归深度数据量一大直接爆炸。我见过不少递归查询慢的案例最后排查下来都是缺这个索引。2.2 向上递归与向下递归向下递归就是前面那个写法从根往下找所有子孙节点。向上递归则反过来从某个子节点出发不断找id parent_id的上一级一直追到根。两种方向语法几乎对称差别只在JOIN的方向。-- 向上递归从id7出发找到所有上级直到根 WITH RECURSIVE dept_ancestors AS ( SELECT id, parent_id, name, 0 AS depth FROM dept WHERE id 7 UNION ALL SELECT d.id, d.parent_id, d.name, da.depth 1 FROM dept d INNER JOIN dept_ancestors da ON d.id da.parent_id ) SELECT * FROM dept_ancestors;注意这里递归成员里的JOIN条件变成了d.id da.parent_id含义是“当前结果集里的parent_id对应的那条上级记录”。向下是找孩子向上是找爸爸理解了这个方向感后面不管换什么业务表都能套。向上递归实际用得也不少典型场景是权限判断一个用户属于某个子部门需要判断他有没有权限操作某条数据而权限挂在上级部门上那就得从用户的部门一路向上找权限。这种场景如果用应用层循环每层一个查询接口响应时间就上去了一条递归SQL顶回去逻辑清晰而且只查一次库。2.3 路径拼接与层级深度控制很多业务不只要查出子孙节点还得知道每个节点的完整路径和层级。比如树形菜单要展示“总公司 / 华东分部 / 上海分公司 / 浦东支公司”这种面包屑或者列表里要按层级缩进显示。MySQL里可以用CONCAT配合递归自动拼路径。WITH RECURSIVE dept_tree AS ( SELECT id, parent_id, name, 0 AS depth, CAST(name AS CHAR(500)) AS path FROM dept WHERE id 1 UNION ALL SELECT d.id, d.parent_id, d.name, dt.depth 1, CONCAT(dt.path, / , d.name) FROM dept d INNER JOIN dept_tree dt ON d.parent_id dt.id ) SELECT id, name, depth, path FROM dept_tree;这里有一个比较容易踩的坑递归成员里path字段的类型必须和锚点成员一致否则MySQL会报Illegal mix of collations之类的错误。锚点里先CAST(name AS CHAR(500))就是提前把字段类型和长度固定下来避免递归过程中字段类型推断不一致。还有一个小细节路径会随着递归深度变长所以CHAR(500)是给路径字段留足余量如果树特别深、路径特别长就把长度调大。但也不能无脑调大过长的字段会造成内存和排序开销增加。3. 递归查询的性能优化与避坑指南3.1 为什么递归越查越慢递归查询慢的根源往往不是递归本身而是每一轮递归都要重新扫描一遍表。拿前面的组织架构查询来说每层递归都会执行一次INNER JOIN dept d ON d.parent_id dt.id如果parent_id上没有索引MySQL只能对dept表做全表扫描。假设表有10万行树深度是5层总共就是50万次行扫描性能自然好不到哪里去。更隐蔽的一个问题是如果每次递归生成很多中间行递归过程中临时表会不断膨胀。CTE递归的中间结果默认是存在内存临时表里的如果超过tmp_table_size的限制会溢出到磁盘形成磁盘临时表那速度会慢到让你怀疑数据库是不是卡死了。所以在生产环境里用递归查询我一般会先看两样东西一是EXPLAIN里递归成员有没有走索引二是SHOW STATUS LIKE Created_tmp_disk_tables有没有在递归执行期间暴涨。这两样搞清楚了慢的原因基本就定位到了。3.2 优化手段索引、深度限制、CTE物化优化手段我按有效程度排个序。第一个必须做的是在递归关联字段上建索引。parent_id这种字段在邻接表设计里天然就是高频查询字段不管你用什么方式查树形结构这个索引都该建。对递归查询来说索引意味着每一轮JOIN从“全表扫”变成“点查”性能提升是最直接的。建索引的语句很简单ALTER TABLE dept ADD INDEX idx_parent_id (parent_id);第二个是限制递归深度。MySQL 8.0里可以通过cte_max_recursion_depth系统变量控制最大递归深度默认是1000。这个值不光是防御机制也是性能保护。一旦数据结构出现异常变成环递归就会无限循环下去直到打爆临时表资源。把深度限制设成一个合理值比如100既能覆盖绝大多数业务树的深度又能及时终止异常递归。SET SESSION cte_max_recursion_depth 100;第三个是评估CTE物化策略。MySQL 8.0里CTE默认可能会被物化多次如果递归成员里引用同一个CTE的多个地方代价会成倍上涨。通常情况下我们不会在递归成员里多次引用CTE但如果你看到执行计划里出现重复的物化操作可以检查一下是不是SQL写法导致CTE被展开了多份。3.3 与其他方案的对比邻接表、闭包表、路径枚举递归查询不是解决树形结构的唯一方式而且也不是所有场景的最优解。我做过一次对比把各方案的适用场景理清楚了。邻接表就是我们一直在用的id parent_id配合递归查询是最通用的方案优点是表结构清晰、增删改方便缺点是查询全部子孙依赖递归或者多次查询。数据量几万行的组织架构、菜单、分类用起来完全没问题。闭包表Closure Table是单独存一张“祖先-后代”关系表的方案每次增删都要同步维护关系数据写入更重但查询任意层级关系都只需要一次普通查询不需要递归。适合查询极其频繁、树结构相对稳定、写操作少的数据比如权限关系、分类树。路径枚举Path Enumeration是把完整路径存在一个字段里比如/1/2/4/查询子节点用LIKE /1/2/4/%写起来简单但修改节点位置时需要批量更新路径而且索引对LIKE前缀匹配不友好。适合树结构几乎不变、查询模式很固定的场景。我的建议是如果不确定该用哪种默认选邻接表配合递归查询。等真的到了闭包表或路径枚举的场景比如树深度很大、查询频率极高、写入很少再考虑换方案不要一开始就过度设计。4. 生产环境中的常见问题与排查套路4.1 死循环与报错ERROR 3636先说一个我确实踩过的坑数据里有脏数据某个节点的parent_id指向了自己或者两个节点互相指来指去形成环。这时候递归查询会无限循环MySQL会报ERROR 3636: Recursive query aborted after 1000 iterations。这个报错的含义就是触发了递归深度上限MySQL保护性地终止了查询。发现这个报错第一反应不要是调大cte_max_recursion_depth因为调大了也只是延迟了资源耗尽的时间点。正确的做法是去检查数据把环找出来。定位环的SQL可以这样写-- 找出parent_id指向自己节点的脏数据 SELECT * FROM dept WHERE id parent_id; -- 两节点互相指向 SELECT a.id, a.parent_id, b.id, b.parent_id FROM dept a JOIN dept b ON a.parent_id b.id AND b.parent_id a.id WHERE a.id b.id;更复杂的三节点以上成环可以即席构造一个递归查询在递归过程中记录已经访问过的节点路径然后检查路径里是否出现重复节点。但多数生产环境的环都是前面两种情况造成的先查这两种就差不多了。4.2 数据里出现环怎么办如果查出来了确实有环比如id4的部门parent_id指向了id6而id6的parent_id又指向id4那么递归查询从id4出发时就会无限循环。这时候除了修数据还有一个防线在递归查询里主动加一个“不能返回到已访问节点”的约束。用一个visited字段来记录路径上已经访问过的节点id集合然后限制新节点的id不能已经出现在路径里WITH RECURSIVE dept_tree AS ( SELECT id, parent_id, name, CAST(id AS CHAR(500)) AS visited FROM dept WHERE id 1 UNION ALL SELECT d.id, d.parent_id, d.name, CONCAT(dt.visited, ,, d.id) FROM dept d INNER JOIN dept_tree dt ON d.parent_id dt.id WHERE FIND_IN_SET(d.id, dt.visited) 0 ) SELECT * FROM dept_tree;第四行给visited字段赋了初始值记录从根开始的路径节点集合。递归成员里WHERE FIND_IN_SET(d.id, dt.visited) 0就是防环的“保险丝”如果新节点id已经在当前路径上出现过就跳过它不参与下一轮递归。这种方式虽然会多消耗一点字符串拼接的开销但能在数据层面存在脏数据时保证查询不会死循环。生产环境树形数据如果来源复杂我建议直接把这个防环逻辑写进递归SQL里一劳永逸。4.3 递归结果排序与分页的坑递归查询的结果顺序不是按你直觉来的。默认情况下每一轮递归产生的行会按轮次依次追加同一轮内部不保证顺序。想按层级和sort_no排序最稳妥的做法是在递归结束后的外层SELECT里统一排序。WITH RECURSIVE dept_tree AS (...) SELECT * FROM dept_tree ORDER BY depth, sort_no;有人可能会尝试在递归成员里写ORDER BY这没有意义因为内层排序不会影响最终结果反而可能破坏递归的逻辑顺序。正确思路是递归只负责“把数据捞全”排序交给外层。分页和递归是另一个容易让人疑惑的地方。如果你在递归CTE外层直接LIMIT 10 OFFSET 0每轮递归都会把整棵树完整生成出来然后才轮到外层分页截取10行。这意味着分页的页码越深递归耗费的资源越大但至少结果是正确的。需要注意的坑是如果依赖递归过程中自然的行序来分页跳页后顺序会完全对不上所以分页之前必须先把ORDER BY放到外层保证先有稳定顺序再分页。还有一种更激进的做法是每轮递归都先过滤再递归但这需要业务上有明确的剪枝条件实现复杂度明显更高并不适合所有树形查询。5. 实战案例菜单权限树的递归实现5.1 场景与需求说明以菜单权限树为例很多后台管理系统的左侧菜单是两级或者三级的树形结构菜单表通常是id、parent_id、menu_name、menu_type、sort_no。需求是把用户有权限看到的菜单全部查出来并且在前端渲染成树。传统写法是查全量菜单后在应用层过滤权限再拼树数据量大时效率低代码也绕。用递归查询可以很好地解决先查出用户有权限的菜单ID集合再从根菜单出发递归每层递归中只保留权限集合内的菜单。这样数据库返回的天然就是一颗完整的、已经过滤好权限的树行集合应用层只需要按parent_id组装即可。5.2 完整SQL与过程说明假设有权限的菜单集已经保存在一张临时表或子查询user_menu里包含menu_id。完整SQL可以这样写WITH RECURSIVE menu_tree AS ( SELECT m.id, m.parent_id, m.menu_name, m.sort_no, 0 AS depth FROM menu m WHERE m.parent_id IS NULL AND m.id IN (SELECT menu_id FROM user_menu) UNION ALL SELECT m.id, m.parent_id, m.menu_name, m.sort_no, mt.depth 1 FROM menu m INNER JOIN menu_tree mt ON m.parent_id mt.id INNER JOIN user_menu um ON m.id um.menu_id ) SELECT * FROM menu_tree ORDER BY depth, sort_no;锚点成员选出用户有权限的顶级菜单递归成员向下扩展时再用INNER JOIN user_menu把没有权限的分支直接剪掉。这里有个很关键的点锚点如果查不到任何顶级菜单整个CTE就空了递归不会启动输出结果也会是空集这是符合预期的行为不需要额外处理。另外可以看到menu_tree的递归成员里同时引用了原始表menu和权限表user_menu。只要user_menu数据量可控这里的子查询可以安全使用。user_menu如果本身是从关联表实时计算出来的建议先提前物化成临时表否则每一轮递归都会重新执行一次权限计算查询性能会显著下降。5.3 应用层如何配合递归查询SQL查出的是扁平的行集合每行带parent_id前端需要的树形嵌套结构还需要在应用层组一下。这个组装逻辑本身不复杂但要写得稳核心是按id建立索引映射后一次性挂接。如果用的是Java大致逻辑是遍历一遍结果集把所有节点放入MapInteger, Node然后第二次遍历把每个节点挂到parent_id对应的父节点的children列表下。这属于O(n)的组装没什么性能压力关键点在于根节点条件应该是parent_id为空或者父节点在Map里不存在避免出现孤儿节点在树里丢失。实测下来一条递归SQL加上几十行组装代码就能替代过去那种“多次查询加递归函数拼树”的老方案。接口响应时间从几百毫秒降到几十毫秒差的不是一点半点。6. 性能对比实测递归查询 vs 应用层递归6.1 测试环境与用例设计为了验证递归查询到底值不值得用我专门在自己的测试环境里做了一个对比实验。表结构和前面dept表一致数据量造了5万行左右树深度大概6到8层。对比两种取数方式一种是应用层递归先查根部门再逐层查询子部门每层批量用WHERE parent_id IN (...)去查另一种就是一条WITH RECURSIVE查询。两者取出的最终数据集一致。测试环境是MySQL 8.0的默认配置没有针对这个场景单独调优查询的parent_id索引在两棵方案下都同样存在。应用层递归用Java实现开了连接池每层查询复用同一连接。6.2 数据结果与分析每组查询都重复了多次取平均值大致结果如下方案查询次数耗时毫秒网络与代码复杂度应用层递归6到8次80到120需要写递归函数处理临时结果集分页与排序麻烦WITH RECURSIVE1次15到25一条SQL外层排序方便应用代码大量简化第二轮测试加大数据量到20万行时应用层递归因为每次查询的IN条件里的id数量变多每次查询的网络开销和处理时间同步上升耗时增长到300毫秒左右而递归查询只到了60毫秒上下。这个结果其实不意外。数据库引擎内部的递归是在服务端内存中完成的不需要反复在应用层和数据库之间传输中间结果省掉了大量网络往返和ORM封装开销。但也要说清楚边界递归查询的优势集中在“遍历树”这种一次查询就能算完的场景如果树结构还需要在应用层做很多特殊业务处理比如每个节点都要再关联其他业务数据那应用层递归反而更灵活。6.3 什么场景下不要用它递归查询也不是银弹。我遇到过一个场景一张分类表数据量超过百万而且树的层级深用户要求前端展开某个大分类时要秒出数据。这种情况下即使递归查询走了索引单次递归遍历出来的行数也可能到十几万行再加上临时表的读写压力性能会很难看。当时我们改用了闭包表方案把常用层级关系预计算好存在单独的关系表里查询直接从关系表拿结果性能才稳定下来。判断标准就一条如果某棵树的查询频度远高于写入频度而且树结构不会频繁变动闭包表比递归查询更合适。反过来要是树结构经常增删改闭包表的维护成本会让你头疼这时邻接表配合递归才是对的。最后一点个人体会写递归查询的SQL最大的门槛不是语法本身而是思维方式的转换。你需要把自己从“一批数据一批数据地捞”的循环思维切换到“描述一个集合如何不断扩展”的声明式思维。一旦过了这个坎很多复杂业务都会突然变简单比如菜单权限、组织架构、评论盖楼、商品分类甚至是一张无限级分销关系表都能用同一套递归模板去解。我在实际项目里只要确认了“这是树形结构”并且“一次要拿完整棵树”就会优先考虑WITH RECURSIVE。如果你也想在自己项目里试试建议先从小数据量的内部管理系统开始加上cte_max_recursion_depth这类保护参数再慢慢推广到核心业务。聊到最后再送一个小技巧给所有递归查询统一加上depth字段做层级标记不仅是排查问题方便后续做权限过滤、数据导出、树形渲染都会省非常多事。 
长河君 · 2026/10/3 9:44:37 · 阅读 1.2k MySQL递归查询实战：WITH RECURSIVE语法、性能优化与避坑指南
MySQL的递归查询我前前后后用过不少次从最早在存储过程里写循环一条条查到后来换成WITH RECURSIVE一条SQL搞定整棵树这个转变确实让我省了不少心。如果你也遇到过“查组织架构要写半天代码”“菜单树查出来还得在Java里拼半天层级”这类问题这篇文章应该能帮你把递归查询这块彻底吃透。我会从语法原理、实战用法、性能优化到踩坑记录都过一遍不管你是刚接触MySQL的新手还是已经在业务里被树形结构折磨过的老手都能找到能直接抄的解决方案。1. 递归查询到底是什么它解决了什么问题1.1 没有递归查询时我们怎么处理树形结构先回忆一个场景一张部门表id、parent_id、name三个字段典型的邻接表结构。要查出某个部门下面所有子部门最笨的办法是先在应用层查出第一层子部门然后遍历每个子部门再查一次循环往复直到查不到子节点为止。这种写法本身没问题但问题也摆在明面上。一是SQL查询次数不可控。树的深度有多少层就要执行多少次查询如果每层有多个节点那N1问题会直接放大成N×M问题。二是应用层代码特别啰嗦。你得维护一个待遍历队列写递归函数或者while循环再加上去重、排序、组装树形结构光这些逻辑就能写上百行。三是事务边界不好控制。如果中途某次查询失败你很难说清楚数据一致性到底有没有被破坏。这也是为什么很多老项目里的树形结构查询代码极其难维护新接手的人看着那一堆循环和递归函数头皮发麻。而WITH RECURSIVE这种公共表表达式CTECommon Table Expression的递归写法本质上是把“反复查询同一张表并不断把结果集喂回给下一次查询”这件事放到了数据库引擎内部去完成。我们只需要描述清楚两件事从哪一行开始以及每一步如何从当前结果推导出下一步结果。1.2 WITH RECURSIVE 的语法拆解MySQL从8.0版本开始支持WITH RECURSIVE8.0之前只能用存储过程或者多层JOIN硬凑。它的基础语法长这样WITH RECURSIVE cte_name AS ( -- 锚点成员初始结果集 SELECT ... UNION ALL -- 递归成员引用cte_name自身生成下一批数据 SELECT ... ) SELECT * FROM cte_name;这里有几个关键点必须搞清楚。UNION ALL和UNION是有区别的前者保留全部重复行后者会去重。递归查询里绝大多数情况下我们用的是UNION ALL因为树形结构里每个节点在递归过程中只会被访问一次不太容易出现重复行让你非去重不可的情况而UNION的去重代价在递归场景下会明显拖慢速度。递归成员的查询里必须引用cte_name自身这是递归得以继续的原因。而且每次递归产生的数据会继续作为下一轮的输入直到某次查询返回空结果集递归就会自动终止。这个过程类似一个循环给定初始集合不断扩展集合直到不再有新数据。一个最小的例子用数字序列来理解WITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n 1 FROM seq WHERE n 10 ) SELECT * FROM seq;第一轮seq里只有1递归成员取出n1生成2第二轮拿2生成3……一直到n10时不满足n 10递归终止最终得到1到10的数字序列。这个例子虽然简单但它是理解一切递归查询的基础——锚点成员负责“点火”递归成员负责“持续加柴”条件负责“熄火”。1.3 一个小例子快速入门拿组织架构举例。假设有一张dept表id、parent_id、name根部门的parent_id为NULL。要查出id1这个部门下面所有的子部门可以这么写WITH RECURSIVE dept_tree AS ( SELECT id, parent_id, name, 0 AS depth FROM dept WHERE id 1 UNION ALL SELECT d.id, d.parent_id, d.name, dt.depth 1 FROM dept d INNER JOIN dept_tree dt ON d.parent_id dt.id ) SELECT * FROM dept_tree;第一轮选出了根部门深度是0第二轮把parent_id1的部门全部关联出来深度变成1接着把parent_id等于这批部门id的再关联出来深度继续加1。只要某层没有任何子部门能关联上递归自然停住。这个写法里有个细节值得注意递归成员里INNER JOIN dept_tree dt ON d.parent_id dt.id这个JOIN就是递归的“桥梁”。它把上一次的结果集dept_tree作为驱动表去原表dept里找孩子。理解了这行JOIN你就理解了八成递归查询的写法。2. 从零开始一张组织架构表搞定递归查询2.1 表设计与测试数据准备实际操作之前先建一张标准的分层表顺便加上索引避免后面排查问题时分不清是数据问题还是SQL问题。CREATE TABLE dept ( id INT PRIMARY KEY, parent_id INT NULL, name VARCHAR(50) NOT NULL, sort_no INT DEFAULT 0, KEY idx_parent_id (parent_id), KEY idx_sort_no (sort_no) ); INSERT INTO dept VALUES (1, NULL, 总公司, 1), (2, 1, 华东分部, 1), (3, 1, 华南分部, 2), (4, 2, 上海分公司, 1), (5, 2, 杭州分公司, 2), (6, 3, 广州分公司, 1), (7, 4, 浦东支公司, 1), (8, 5, 滨江支公司, 1);parent_id上建索引是必须的因为递归成员里每次都要用d.parent_id dt.id这个条件去扫描没有索引就是全表扫描乘以递归深度数据量一大直接爆炸。我见过不少递归查询慢的案例最后排查下来都是缺这个索引。2.2 向上递归与向下递归向下递归就是前面那个写法从根往下找所有子孙节点。向上递归则反过来从某个子节点出发不断找id parent_id的上一级一直追到根。两种方向语法几乎对称差别只在JOIN的方向。-- 向上递归从id7出发找到所有上级直到根 WITH RECURSIVE dept_ancestors AS ( SELECT id, parent_id, name, 0 AS depth FROM dept WHERE id 7 UNION ALL SELECT d.id, d.parent_id, d.name, da.depth 1 FROM dept d INNER JOIN dept_ancestors da ON d.id da.parent_id ) SELECT * FROM dept_ancestors;注意这里递归成员里的JOIN条件变成了d.id da.parent_id含义是“当前结果集里的parent_id对应的那条上级记录”。向下是找孩子向上是找爸爸理解了这个方向感后面不管换什么业务表都能套。向上递归实际用得也不少典型场景是权限判断一个用户属于某个子部门需要判断他有没有权限操作某条数据而权限挂在上级部门上那就得从用户的部门一路向上找权限。这种场景如果用应用层循环每层一个查询接口响应时间就上去了一条递归SQL顶回去逻辑清晰而且只查一次库。2.3 路径拼接与层级深度控制很多业务不只要查出子孙节点还得知道每个节点的完整路径和层级。比如树形菜单要展示“总公司 / 华东分部 / 上海分公司 / 浦东支公司”这种面包屑或者列表里要按层级缩进显示。MySQL里可以用CONCAT配合递归自动拼路径。WITH RECURSIVE dept_tree AS ( SELECT id, parent_id, name, 0 AS depth, CAST(name AS CHAR(500)) AS path FROM dept WHERE id 1 UNION ALL SELECT d.id, d.parent_id, d.name, dt.depth 1, CONCAT(dt.path, / , d.name) FROM dept d INNER JOIN dept_tree dt ON d.parent_id dt.id ) SELECT id, name, depth, path FROM dept_tree;这里有一个比较容易踩的坑递归成员里path字段的类型必须和锚点成员一致否则MySQL会报Illegal mix of collations之类的错误。锚点里先CAST(name AS CHAR(500))就是提前把字段类型和长度固定下来避免递归过程中字段类型推断不一致。还有一个小细节路径会随着递归深度变长所以CHAR(500)是给路径字段留足余量如果树特别深、路径特别长就把长度调大。但也不能无脑调大过长的字段会造成内存和排序开销增加。3. 递归查询的性能优化与避坑指南3.1 为什么递归越查越慢递归查询慢的根源往往不是递归本身而是每一轮递归都要重新扫描一遍表。拿前面的组织架构查询来说每层递归都会执行一次INNER JOIN dept d ON d.parent_id dt.id如果parent_id上没有索引MySQL只能对dept表做全表扫描。假设表有10万行树深度是5层总共就是50万次行扫描性能自然好不到哪里去。更隐蔽的一个问题是如果每次递归生成很多中间行递归过程中临时表会不断膨胀。CTE递归的中间结果默认是存在内存临时表里的如果超过tmp_table_size的限制会溢出到磁盘形成磁盘临时表那速度会慢到让你怀疑数据库是不是卡死了。所以在生产环境里用递归查询我一般会先看两样东西一是EXPLAIN里递归成员有没有走索引二是SHOW STATUS LIKE Created_tmp_disk_tables有没有在递归执行期间暴涨。这两样搞清楚了慢的原因基本就定位到了。3.2 优化手段索引、深度限制、CTE物化优化手段我按有效程度排个序。第一个必须做的是在递归关联字段上建索引。parent_id这种字段在邻接表设计里天然就是高频查询字段不管你用什么方式查树形结构这个索引都该建。对递归查询来说索引意味着每一轮JOIN从“全表扫”变成“点查”性能提升是最直接的。建索引的语句很简单ALTER TABLE dept ADD INDEX idx_parent_id (parent_id);第二个是限制递归深度。MySQL 8.0里可以通过cte_max_recursion_depth系统变量控制最大递归深度默认是1000。这个值不光是防御机制也是性能保护。一旦数据结构出现异常变成环递归就会无限循环下去直到打爆临时表资源。把深度限制设成一个合理值比如100既能覆盖绝大多数业务树的深度又能及时终止异常递归。SET SESSION cte_max_recursion_depth 100;第三个是评估CTE物化策略。MySQL 8.0里CTE默认可能会被物化多次如果递归成员里引用同一个CTE的多个地方代价会成倍上涨。通常情况下我们不会在递归成员里多次引用CTE但如果你看到执行计划里出现重复的物化操作可以检查一下是不是SQL写法导致CTE被展开了多份。3.3 与其他方案的对比邻接表、闭包表、路径枚举递归查询不是解决树形结构的唯一方式而且也不是所有场景的最优解。我做过一次对比把各方案的适用场景理清楚了。邻接表就是我们一直在用的id parent_id配合递归查询是最通用的方案优点是表结构清晰、增删改方便缺点是查询全部子孙依赖递归或者多次查询。数据量几万行的组织架构、菜单、分类用起来完全没问题。闭包表Closure Table是单独存一张“祖先-后代”关系表的方案每次增删都要同步维护关系数据写入更重但查询任意层级关系都只需要一次普通查询不需要递归。适合查询极其频繁、树结构相对稳定、写操作少的数据比如权限关系、分类树。路径枚举Path Enumeration是把完整路径存在一个字段里比如/1/2/4/查询子节点用LIKE /1/2/4/%写起来简单但修改节点位置时需要批量更新路径而且索引对LIKE前缀匹配不友好。适合树结构几乎不变、查询模式很固定的场景。我的建议是如果不确定该用哪种默认选邻接表配合递归查询。等真的到了闭包表或路径枚举的场景比如树深度很大、查询频率极高、写入很少再考虑换方案不要一开始就过度设计。4. 生产环境中的常见问题与排查套路4.1 死循环与报错ERROR 3636先说一个我确实踩过的坑数据里有脏数据某个节点的parent_id指向了自己或者两个节点互相指来指去形成环。这时候递归查询会无限循环MySQL会报ERROR 3636: Recursive query aborted after 1000 iterations。这个报错的含义就是触发了递归深度上限MySQL保护性地终止了查询。发现这个报错第一反应不要是调大cte_max_recursion_depth因为调大了也只是延迟了资源耗尽的时间点。正确的做法是去检查数据把环找出来。定位环的SQL可以这样写-- 找出parent_id指向自己节点的脏数据 SELECT * FROM dept WHERE id parent_id; -- 两节点互相指向 SELECT a.id, a.parent_id, b.id, b.parent_id FROM dept a JOIN dept b ON a.parent_id b.id AND b.parent_id a.id WHERE a.id b.id;更复杂的三节点以上成环可以即席构造一个递归查询在递归过程中记录已经访问过的节点路径然后检查路径里是否出现重复节点。但多数生产环境的环都是前面两种情况造成的先查这两种就差不多了。4.2 数据里出现环怎么办如果查出来了确实有环比如id4的部门parent_id指向了id6而id6的parent_id又指向id4那么递归查询从id4出发时就会无限循环。这时候除了修数据还有一个防线在递归查询里主动加一个“不能返回到已访问节点”的约束。用一个visited字段来记录路径上已经访问过的节点id集合然后限制新节点的id不能已经出现在路径里WITH RECURSIVE dept_tree AS ( SELECT id, parent_id, name, CAST(id AS CHAR(500)) AS visited FROM dept WHERE id 1 UNION ALL SELECT d.id, d.parent_id, d.name, CONCAT(dt.visited, ,, d.id) FROM dept d INNER JOIN dept_tree dt ON d.parent_id dt.id WHERE FIND_IN_SET(d.id, dt.visited) 0 ) SELECT * FROM dept_tree;第四行给visited字段赋了初始值记录从根开始的路径节点集合。递归成员里WHERE FIND_IN_SET(d.id, dt.visited) 0就是防环的“保险丝”如果新节点id已经在当前路径上出现过就跳过它不参与下一轮递归。这种方式虽然会多消耗一点字符串拼接的开销但能在数据层面存在脏数据时保证查询不会死循环。生产环境树形数据如果来源复杂我建议直接把这个防环逻辑写进递归SQL里一劳永逸。4.3 递归结果排序与分页的坑递归查询的结果顺序不是按你直觉来的。默认情况下每一轮递归产生的行会按轮次依次追加同一轮内部不保证顺序。想按层级和sort_no排序最稳妥的做法是在递归结束后的外层SELECT里统一排序。WITH RECURSIVE dept_tree AS (...) SELECT * FROM dept_tree ORDER BY depth, sort_no;有人可能会尝试在递归成员里写ORDER BY这没有意义因为内层排序不会影响最终结果反而可能破坏递归的逻辑顺序。正确思路是递归只负责“把数据捞全”排序交给外层。分页和递归是另一个容易让人疑惑的地方。如果你在递归CTE外层直接LIMIT 10 OFFSET 0每轮递归都会把整棵树完整生成出来然后才轮到外层分页截取10行。这意味着分页的页码越深递归耗费的资源越大但至少结果是正确的。需要注意的坑是如果依赖递归过程中自然的行序来分页跳页后顺序会完全对不上所以分页之前必须先把ORDER BY放到外层保证先有稳定顺序再分页。还有一种更激进的做法是每轮递归都先过滤再递归但这需要业务上有明确的剪枝条件实现复杂度明显更高并不适合所有树形查询。5. 实战案例菜单权限树的递归实现5.1 场景与需求说明以菜单权限树为例很多后台管理系统的左侧菜单是两级或者三级的树形结构菜单表通常是id、parent_id、menu_name、menu_type、sort_no。需求是把用户有权限看到的菜单全部查出来并且在前端渲染成树。传统写法是查全量菜单后在应用层过滤权限再拼树数据量大时效率低代码也绕。用递归查询可以很好地解决先查出用户有权限的菜单ID集合再从根菜单出发递归每层递归中只保留权限集合内的菜单。这样数据库返回的天然就是一颗完整的、已经过滤好权限的树行集合应用层只需要按parent_id组装即可。5.2 完整SQL与过程说明假设有权限的菜单集已经保存在一张临时表或子查询user_menu里包含menu_id。完整SQL可以这样写WITH RECURSIVE menu_tree AS ( SELECT m.id, m.parent_id, m.menu_name, m.sort_no, 0 AS depth FROM menu m WHERE m.parent_id IS NULL AND m.id IN (SELECT menu_id FROM user_menu) UNION ALL SELECT m.id, m.parent_id, m.menu_name, m.sort_no, mt.depth 1 FROM menu m INNER JOIN menu_tree mt ON m.parent_id mt.id INNER JOIN user_menu um ON m.id um.menu_id ) SELECT * FROM menu_tree ORDER BY depth, sort_no;锚点成员选出用户有权限的顶级菜单递归成员向下扩展时再用INNER JOIN user_menu把没有权限的分支直接剪掉。这里有个很关键的点锚点如果查不到任何顶级菜单整个CTE就空了递归不会启动输出结果也会是空集这是符合预期的行为不需要额外处理。另外可以看到menu_tree的递归成员里同时引用了原始表menu和权限表user_menu。只要user_menu数据量可控这里的子查询可以安全使用。user_menu如果本身是从关联表实时计算出来的建议先提前物化成临时表否则每一轮递归都会重新执行一次权限计算查询性能会显著下降。5.3 应用层如何配合递归查询SQL查出的是扁平的行集合每行带parent_id前端需要的树形嵌套结构还需要在应用层组一下。这个组装逻辑本身不复杂但要写得稳核心是按id建立索引映射后一次性挂接。如果用的是Java大致逻辑是遍历一遍结果集把所有节点放入MapInteger, Node然后第二次遍历把每个节点挂到parent_id对应的父节点的children列表下。这属于O(n)的组装没什么性能压力关键点在于根节点条件应该是parent_id为空或者父节点在Map里不存在避免出现孤儿节点在树里丢失。实测下来一条递归SQL加上几十行组装代码就能替代过去那种“多次查询加递归函数拼树”的老方案。接口响应时间从几百毫秒降到几十毫秒差的不是一点半点。6. 性能对比实测递归查询 vs 应用层递归6.1 测试环境与用例设计为了验证递归查询到底值不值得用我专门在自己的测试环境里做了一个对比实验。表结构和前面dept表一致数据量造了5万行左右树深度大概6到8层。对比两种取数方式一种是应用层递归先查根部门再逐层查询子部门每层批量用WHERE parent_id IN (...)去查另一种就是一条WITH RECURSIVE查询。两者取出的最终数据集一致。测试环境是MySQL 8.0的默认配置没有针对这个场景单独调优查询的parent_id索引在两棵方案下都同样存在。应用层递归用Java实现开了连接池每层查询复用同一连接。6.2 数据结果与分析每组查询都重复了多次取平均值大致结果如下方案查询次数耗时毫秒网络与代码复杂度应用层递归6到8次80到120需要写递归函数处理临时结果集分页与排序麻烦WITH RECURSIVE1次15到25一条SQL外层排序方便应用代码大量简化第二轮测试加大数据量到20万行时应用层递归因为每次查询的IN条件里的id数量变多每次查询的网络开销和处理时间同步上升耗时增长到300毫秒左右而递归查询只到了60毫秒上下。这个结果其实不意外。数据库引擎内部的递归是在服务端内存中完成的不需要反复在应用层和数据库之间传输中间结果省掉了大量网络往返和ORM封装开销。但也要说清楚边界递归查询的优势集中在“遍历树”这种一次查询就能算完的场景如果树结构还需要在应用层做很多特殊业务处理比如每个节点都要再关联其他业务数据那应用层递归反而更灵活。6.3 什么场景下不要用它递归查询也不是银弹。我遇到过一个场景一张分类表数据量超过百万而且树的层级深用户要求前端展开某个大分类时要秒出数据。这种情况下即使递归查询走了索引单次递归遍历出来的行数也可能到十几万行再加上临时表的读写压力性能会很难看。当时我们改用了闭包表方案把常用层级关系预计算好存在单独的关系表里查询直接从关系表拿结果性能才稳定下来。判断标准就一条如果某棵树的查询频度远高于写入频度而且树结构不会频繁变动闭包表比递归查询更合适。反过来要是树结构经常增删改闭包表的维护成本会让你头疼这时邻接表配合递归才是对的。最后一点个人体会写递归查询的SQL最大的门槛不是语法本身而是思维方式的转换。你需要把自己从“一批数据一批数据地捞”的循环思维切换到“描述一个集合如何不断扩展”的声明式思维。一旦过了这个坎很多复杂业务都会突然变简单比如菜单权限、组织架构、评论盖楼、商品分类甚至是一张无限级分销关系表都能用同一套递归模板去解。我在实际项目里只要确认了“这是树形结构”并且“一次要拿完整棵树”就会优先考虑WITH RECURSIVE。如果你也想在自己项目里试试建议先从小数据量的内部管理系统开始加上cte_max_recursion_depth这类保护参数再慢慢推广到核心业务。聊到最后再送一个小技巧给所有递归查询统一加上depth字段做层级标记不仅是排查问题方便后续做权限过滤、数据导出、树形渲染都会省非常多事。