做后台管理系统的时候十有八九会遇到树形结构的数据商品分类、部门组织架构、权限菜单、评论回复、地区字典……它们在表里长得都一样一条记录带着一个parent_id指向上级。这种结构设计简单、插入方便但等树深了、节点多了查询就暴露问题了——要么递归循环写一堆代码要么一次性SELECT *出来在内存里拼树数据量一上来就卡得没法看。这篇文章就把 MySQL 树形表的查询优化这件事一次说透从最常见的邻接表模型讲起到递归 CTE 的正确写法再到索引怎么建、闭包表怎么设计最后附上我这些年踩过的坑和排查思路。适合正在用 MySQL 5.7 还想办法绕弯子、或者已经升到 8.0 想用好递归查询的人也适合准备面试被问到树形结构怎么存怎么查的同学。1. 树形表建模先搞清楚你用的是哪种方案很多人一上来就盯着 SQL 怎么写其实树形查询慢的根子往往在建模上。表结构决定了你能用什么方式查、索引能不能生效、数据变更的成本高不高。所以第一步不是写查询是盘点自己这张树表到底属于哪类模型。1.1 邻接表——最常见也最容易踩坑的模型所谓邻接表就是每行存一个parent_id指向父节点顶级节点的parent_id为 0 或 NULL。这是国内业务系统里最主流的做法因为建表简单、插入一条数据只需要知道父节点 ID删除子节点也直接DELETE就行。CREATE TABLE category ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, parent_id INT NOT NULL DEFAULT 0, sort_order INT NOT NULL DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB;邻接表的优点很直白模型直观、写入快、事务控制简单。但它的查询痛点同样直白——查询一棵完整子树没有一条 SQL 能搞定。MySQL 5.7 及以前版本不支持递归查询常规做法是写存储过程循环查或者直接在应用层多次查询后组装。即便 MySQL 8.0 支持了递归 CTE深层次数据查询依然依赖临时表性能上限取决于树深度和数据总量。另一个容易忽略的问题邻接表对移动子树操作其实是很方便的只要改一个节点的parent_id。但对查询某节点的所有祖先和查询某节点的所有后代这类高频操作邻接表天然不友好。如果你发现业务里 70% 的查询都是给我这个分类下所有子分类的商品数那你应该认真考虑要不要换模型了。1.2 进阶方案对比路径枚举、嵌套集与闭包表树形建模不止邻接表一种。做优化之前最好把几个候选方案放在一张表里看清楚差异。方案核心字段查询子树查询祖先插入成本修改成本适用场景邻接表parent_id递归 CTE递归 CTE极低低数据量小、层级浅、写频繁路径枚举path 如 001/002/003前缀 LIKE前缀 LIKE低高改父节点要批量 UPDATE层级固定、查询按路径排序嵌套集left_num / right_num范围查询范围查询很高需平移大量节点极高高频读、低频写、层级固定闭包表独立关系表索引 JOIN索引 JOIN较高需批量插入关系中删除/移动需维护关系大数据量、频繁查子树和祖先我这里直接给结论如果你的树表数据量在几千到几万节点、层级不超过四五层邻接表 递归 CTE 合理索引就足够了别为了一点性能把架构搞复杂。数据量到了几十万节点且查询子树是核心场景老老实实上闭包表。路径枚举适合层级深度固定的场景比如三级分销、固定的省市县关联这时候前缀查询 索引的效率非常稳定。嵌套集最不推荐做动态树因为每插入一个节点就要更新一大片 left/right 值在并发写入场景下很容易锁冲突。1.3 方案选型的判断依据我选方案只看三件事节点量级、树的深度、读写比例。节点少于 10 万、深度低于 6 层、读多写多都有的就邻接表顶住。节点几十万往上、深度能到十几层甚至无限层、并且读远多于写的闭包表更合适。另外要注意闭包表往往要配合一张原数据表一起用单纯一张关系表拿不到节点名称查询时 JOIN 方式要提前设计好。2. 邻接表递归查询从递归 CTE 入手如果你的表就是邻接表并且数据量可控MySQL 8.0 的递归 CTE 是首选方案没有之一。MySQL 5.7 用户只能用存储过程模拟或者升级 8.0两条路二选一。新项目直接 8.0老项目想办法说服业务方加索引至少别在应用层搞递归循环——那是性能灾难。2.1 递归 CTE 的写法与执行逻辑递归 CTE 的语法结构分三块锚点成员seed、递归成员recursive member、外层查询。锚点成员负责找到树根递归成员负责一层层往下扩展然后用UNION ALL把结果合并起来。WITH RECURSIVE category_tree AS ( -- 锚点查根节点 SELECT id, name, parent_id, 1 AS depth FROM category WHERE parent_id 0 UNION ALL -- 递归通过 parent_id 关联 CTE 结果集 SELECT c.id, c.name, c.parent_id, ct.depth 1 FROM category c INNER JOIN category_tree ct ON c.parent_id ct.id ) SELECT id, name, depth FROM category_tree ORDER BY depth, id;要注意第一个SELECT是种子第二个SELECT会反复执行直到某次 JOIN 查不到新数据为止。第二次 SELECT 里的category_tree指的是上一步刚产出的数据而不是全量结果这一点刚开始用很容易误解。执行过程可以理解成先查根再查根的子节点再查子节点的子节点一层一层往下走每一层的结果都被写进临时表直到没有新行产生。有几个细节直接影响正确性和性能。第一UNION ALL不要写成UNION因为UNION会去重可能把本应重复出现的节点干掉而且去重本身有额外开销。第二递归成员里 JOIN 的两侧字段必须类型一致parent_id和id类型不一致的话索引可能用不上临时表也会变大。第三外层ORDER BY是对最终全量结果排序不是对每一层排序如果只想让每一层内部有序递归成员里就得加排序但那开销更大不如外层统一排。2.2 递归的边界条件与深度控制递归 CTE 有一个隐含的坑如果数据里有环——比如 A 的父节点是 A 自己或者 A 指向 B、B 又指向 A——递归会无限循环。MySQL 8.0 为了解决这个问题默认设置了递归上限由cte_max_recursion_depth控制默认 1000。超过这个迭代次数会直接报错ERROR 3636。所以哪怕数据坏掉了查询也不会挂死这一点要感谢数据库兜底。-- 查看当前递归深度上限 SHOW VARIABLES LIKE cte_max_recursion_depth; -- 会话级临时调大不建议全局改 SET SESSION cte_max_recursion_depth 100000;我实测过 10 万节点的树深度大概 10 层默认 1000 的深度上限是够用的因为它限制的是迭代轮数而不是节点数。但如果你的树是单链结构也就是每个节点只有一个子节点节点数 2000 就会触发 1000 上限。这时候要么调大这个变量要么在查询里加一个显式的深度过滤条件WITH RECURSIVE category_tree AS ( SELECT id, name, parent_id, 1 AS depth FROM category WHERE parent_id 0 UNION ALL SELECT c.id, c.name, c.parent_id, ct.depth 1 FROM category c INNER JOIN category_tree ct ON c.parent_id ct.id WHERE ct.depth 20 -- 防止异常树导致过度递归 ) SELECT * FROM category_tree;这种限制深度的条件是双保险。就算cte_max_recursion_depth没被调大查询也会在 20 层停下来避免生产环境出现一次错误的递归把数据库 CPU 打满。3. 索引设计与查询改写实战树形查询优化索引占了至少一半的功劳。很多人的树表就只有一个主键索引和parent_id裸字段查询子节点靠WHERE parent_id ?这种 SQL 在数据量上来之后必然全表扫描。千万别只看数据量小就忽略索引树形表数据增长速度比想象中快得多。3.1 索引怎么建不只是 parent_id 加索引parent_id加索引是基本操作但真正好用的索引要考虑组合业务查询。比如电商分类的典型查询是查某个父节点下的子分类按排序值排好那么(parent_id, sort_order)做复合索引比单列parent_id索引效率高很多因为索引里已经包含排序字段排序不用回表再做文件排序。ALTER TABLE category ADD INDEX idx_parent_sort (parent_id, sort_order);如果你业务里还经常按level或status过滤比如查某个父节点下所有启用状态的分类可以考虑(parent_id, status, sort_order)。但要注意不要盲目加一堆复合索引树表如果写入频繁索引过多会拖慢插入。我的习惯是先收集慢查询日志里出现频率最高的几个 WHERE 组合再针对性建索引而不是一开始就铺满。有些场景还会用到ORDER BY depth或者ORDER BY path这种排序在递归 CTE 外层做走不了索引。如果排序需求确实很重考虑在物化路径或闭包表方案里解决邻接表里强行排序没有太多优化空间。3.2 查询改写排序、分页、聚合场景的处理递归 CTE 的结果是一个临时结果集对它的排序和分页其实是在临时表上操作的。节点数多到一定程度临时表会落到磁盘性能立刻下降。这里有个经验不要对全树做递归后再 LIMIT 分页而是先把树的骨架算出来再回表补业务字段。举个例子。分类表里除了树结构还有商品数量统计字段product_count页面要展示包含商品数量最多的前 10 个叶子节点。如果先递归出所有叶子再做聚合排序整体开销很大。更好的方式是先通过parent_id查叶子节点再用商品数排序WITH RECURSIVE category_tree AS ( SELECT id, parent_id, 1 AS depth FROM category WHERE parent_id 0 UNION ALL SELECT c.id, c.parent_id, ct.depth 1 FROM category c INNER JOIN category_tree ct ON c.parent_id ct.id ) SELECT ct.id, ct.depth, cg.name, cg.product_count FROM category_tree ct INNER JOIN category cg ON cg.id ct.id WHERE NOT EXISTS ( SELECT 1 FROM category child WHERE child.parent_id ct.id ) ORDER BY cg.product_count DESC LIMIT 10;这种先递归骨架、再回表补数据的写法比在递归成员里疯狂 JOIN 业务表要快得多因为递归部分只需要访问两个字段id和parent_id内存占用小临时表不容易落盘。另一个常见的场景是查某节点下的所有叶子节点聚合统计比如统计整个分类树的商品总额。这个需求在有product_count字段后可以改成先递归出后代 ID再SUM商品数WITH RECURSIVE descendants AS ( SELECT id FROM category WHERE id ? UNION ALL SELECT c.id FROM category c INNER JOIN descendants d ON c.parent_id d.id ) SELECT SUM(cg.product_count) FROM descendants d INNER JOIN category cg ON cg.id d.id;这里把SUM放在递归之后做MySQL 会把递归结果物化到临时表后再聚合。如果节点数上万聚合速度依然不错但要注意临时表大小。实际业务中如果这个聚合查询极其频繁我更建议直接在category表里冗余一个root_id字段标记每个节点属于哪棵顶级树把整棵树统计变成按 root_id 分组统计查询效率完全不在一个量级。4. 大数据量下的闭包表实践如果你的树表超过几十万节点邻接表加递归 CTE 怎么优化都有限。递归的本质是逐层扫描节点层级一深扫描次数成倍增长。这时候闭包表是更靠谱的方案用一张独立的表把每一个祖先-后代关系都存下来。查询子树和祖先变成了纯索引查找不再依赖递归。4.1 闭包表的设计与查询优势闭包表的核心是一张关系表category_closure记录每个节点与其所有祖先的关系同时包含节点自身到自身的关系深度为 0。CREATE TABLE category_closure ( ancestor_id INT NOT NULL, descendant_id INT NOT NULL, depth INT NOT NULL, PRIMARY KEY (ancestor_id, descendant_id), KEY idx_descendant (descendant_id) ) ENGINEInnoDB;举个例子A 是顶级节点B 是 A 的子节点C 是 B 的子节点。闭包表里会存在 A-A深度 0、A-B深度 1、A-C深度 2、B-B深度 0、B-C深度 1、C-C深度 0这六条关系。查询 C 的所有祖先一句 SQL 就出来了SELECT ancestor_id, depth FROM category_closure WHERE descendant_id C_ID ORDER BY depth DESC;查询 A 的所有后代同样简单SELECT descendant_id, depth FROM category_closure WHERE ancestor_id A_ID;和递归 CTE 相比闭包表没有任何逐层扩展的过程全部命中主键索引或二级索引。实测二十万节点、平均深度十五层的树查询某个节点下所有后代的耗时尚且能控制在几十毫秒内这在邻接表递归方案里基本做不到。但闭包表也有代价。第一数据量会膨胀。每条数据都会在闭包表里对应多条关系整体数量级大约是节点数 × 平均深度。所以节点多但深度极浅的树闭包表优势不明显节点多且深度深闭包表才是王者。第二增删改需要同步维护关系表不能单独只改主表。这就是很多人不敢用闭包表的真正原因——写逻辑变复杂了。4.2 插入、删除、移动节点时的增量维护闭包表的写入不能直接 INSERT 一条记录插入一个新节点时需要同时给它和它的所有祖先建立关系。正确做法是查一遍当前新节点的父节点的所有祖先然后批量插入。INSERT INTO category_closure (ancestor_id, descendant_id, depth) SELECT ancestor_id, NEW_ID, depth 1 FROM category_closure WHERE descendant_id PARENT_ID UNION ALL SELECT NEW_ID, NEW_ID, 0;这条 SQL 的原理父节点的所有祖先也是新节点的祖先但深度都要加一同时新节点自己到自己的关系单独插入。如果没有闭包表这一步就是一个简单的INSERT INTO category ... VALUES (...),有了闭包表就变成了两条 SQL 的组合而且必须在事务里执行保证主表和关系表一致。删除节点的时候要做两件事物理删除或者逻辑删除主表记录然后删除闭包表里所有与这个节点相关的行。DELETE FROM category_closure WHERE descendant_id DELETED_ID OR ancestor_id DELETED_ID;如果删除的是父节点且希望子节点一并删除级联删除闭包表会复杂很多。我的建议是生产环境尽量不要用物理级联删除树改为逻辑删除标志位然后在应用层处理子树的状态变更这样闭包表维护成本可控。移动节点是闭包表最繁琐的操作。把一个子树从 A 节点移到 B 节点下需要先把子树相关关系全部删除再按新路径重新生成。这里面稍有不慎就会留下孤儿关系。实践中我把这个逻辑封装成存储过程在事务里分步执行每一步都有ROW_COUNT()校验任何一步不匹配就ROLLBACK。DELIMITER // CREATE PROCEDURE move_category(IN node_id INT, IN new_parent_id INT) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; START TRANSACTION; -- 删除节点自身与其所有祖先的关系 DELETE FROM category_closure WHERE descendant_id IN ( SELECT descendant_id FROM ( SELECT descendant_id FROM category_closure WHERE ancestor_id node_id ) tmp ) AND ancestor_id NOT IN ( SELECT ancestor_id FROM ( SELECT ancestor_id FROM category_closure WHERE descendant_id node_id ) tmp2 ); -- 重新建立新路径关系逻辑同插入 INSERT INTO category_closure (ancestor_id, descendant_id, depth) SELECT c1.ancestor_id, c2.descendant_id, c1.depth c2.depth 1 FROM category_closure c1, category_closure c2 WHERE c1.descendant_id new_parent_id AND c2.ancestor_id node_id; -- 更新主表 parent_id UPDATE category SET parent_id new_parent_id WHERE id node_id; COMMIT; END// DELIMITER ;闭包表的维护逻辑一定要和业务写入封装在一起不要散落在各个 service 方法里否则早晚会出数据不一致的问题。我见过不止一次线上事故都是因为在某个角落直接 INSERT 了 category 表忘了同步闭包表导致整棵树的查询结果缺胳膊少腿。5. 常见问题与排查技巧实录树形表优化的坑写代码是一回事上线跑起来又是另一回事。这一部分是我自己排查慢查询和数据异常时总结的经验不一定每个都与你遇到的一致但大概率能提供思路。5.1 递归查询超时卡死怎么查现象是页面打开分类管理转圈数据库 CPU 飙到 100%。第一反应先看是不是递归上环了。如果数据里存在 A 指向 B、B 又指向 A 的环递归 CTE 会反复迭代直到cte_max_recursion_depth上限这期间临时表越来越大CPU 和内存都被吃光。排查方法是在测试环境复现后跑一下 EXPLAINEXPLAIN ANALYZE WITH RECURSIVE category_tree AS ( SELECT id, parent_id, 1 AS depth FROM category WHERE parent_id 0 UNION ALL SELECT c.id, c.parent_id, ct.depth 1 FROM category c INNER JOIN category_tree ct ON c.parent_id ct.id ) SELECT * FROM category_tree;看actual time和rows是不是按指数级增长。正常树的节点数增长是收敛的有环时会一直翻倍直到报错。另外可以检查performance_schema的语句事件表找出对应语句消耗的资源是否集中在Creating temp table阶段。另一个常见原因是递归成员查询没有走索引。parent_id如果没建索引每一层递归都要全表扫描数据量稍大就原地爆炸。这种问题 EXPLAIN 里能看到type: ALL。加上索引后应该变成ref或者eq_ref。如果确认没环、索引也建了还是慢那就是临时表落盘了。递归 CTE 的中间结果会写到临时表节点数太大时临时表从内存转磁盘性能骤降。查看SHOW STATUS LIKE Created_tmp_disk_tables如果数值在查询前后明显增加就说明落盘了。这种情况下要么调整tmp_table_size和max_heap_table_size只对会话级有效全局改有风险要么换闭包表。5.2 树结构数据维护的几个深坑先说自关联外键。很多人建表时给parent_id加了外键约束这在树形结构里相当危险。删除一个父节点时如果还有子节点引用它外键约束直接报错如果级联删除一不小心删掉一整棵子树。生产环境我强烈建议去掉外键约束只在应用层做逻辑校验必要时用定时任务扫描孤儿节点。再说批量导入数据。从 Excel 导入分类树时数据顺序有时候是打乱的父子关系可能还没建立就插入了子节点这时候parent_id指向的记录不存在。导入脚本必须做两层校验先插所有根节点再插子节点或者导入完成后跑一条找孤儿的 SQLSELECT c.id, c.name, c.parent_id FROM category c LEFT JOIN category p ON p.id c.parent_id WHERE c.parent_id ! 0 AND p.id IS NULL;这条 SQL 也适合做日常巡检我习惯放到每周的定时任务里发现孤儿就告警避免问题积累到最后连修复都无从下手。第三个坑是深度字段的冗余。如果你在 category 表里冗余了level或depth字段插入和移动节点时要记得同步更新。这个字段一旦不一致前端展示的层级缩进就会错乱而且很难排查。为了避免这种情况我一般不在邻接表里冗余 depth真的需要深度信息就现场用递归 CTE 算或者用闭包表的关系表里现成的 depth 字段。5.3 实用排查命令与建议速查整理一个速查表遇到对应问题直接照着做。症状可能原因排查方式解决方案递归查询报错 3636数据成环或树过深查日志定位报错语句修复数据环或临时调大 cte_max_recursion_depth查询越来越慢parent_id 无索引EXPLAIN 看 typeALL给 parent_id 建索引或建复合索引分类树缺节点闭包表漏维护对比主表和闭包表数量重建闭包表或触发增量修复任务子节点找不到父节点导入顺序错乱跑孤儿检测 SQL修正导入顺序补全缺失父节点移动节点后数据混乱闭包表删除不全检查关系条数回滚后使用封装好的存储过程临时表落盘导致慢查询节点数过大SHOW STATUS 查临时表落盘次数优化递归范围或迁移闭包表闭包表如果历史数据已经乱了最稳的修复方式是全量重建。停写窗口内清空闭包表然后用递归 CTE 遍历主表所有节点重新生成关系。节点量大时重建很耗时需要提前规划好窗口。我的经验是一百万节点的树重建闭包表大概需要几分钟到十几分钟具体看服务器磁盘速度。最后说一句我自己的实际体会。树形表的优化方案不是越高级越好而是越匹配业务场景越好。我做过一个核心链路是查整棵后代树的数据系统邻接表怎么调都差一口气换成闭包表后查询从秒级降到毫秒级收益巨大。但另一个项目树节点只有几百个用闭包表反而让代码变复杂后来回退到邻接表简单又稳定。如果要在新项目里做设计我的建议是默认用邻接表把业务跑通等数据量真正涨上来了再评估是否引入闭包表或物化路径并且一定要用压测数据验证不要凭感觉拍板。毕竟方案换了周边所有代码逻辑都要跟着动这个成本往往比大多数人预想得高。
MySQL树形表查询优化：递归CTE、索引与闭包表实战
做后台管理系统的时候十有八九会遇到树形结构的数据商品分类、部门组织架构、权限菜单、评论回复、地区字典……它们在表里长得都一样一条记录带着一个parent_id指向上级。这种结构设计简单、插入方便但等树深了、节点多了查询就暴露问题了——要么递归循环写一堆代码要么一次性SELECT *出来在内存里拼树数据量一上来就卡得没法看。这篇文章就把 MySQL 树形表的查询优化这件事一次说透从最常见的邻接表模型讲起到递归 CTE 的正确写法再到索引怎么建、闭包表怎么设计最后附上我这些年踩过的坑和排查思路。适合正在用 MySQL 5.7 还想办法绕弯子、或者已经升到 8.0 想用好递归查询的人也适合准备面试被问到树形结构怎么存怎么查的同学。1. 树形表建模先搞清楚你用的是哪种方案很多人一上来就盯着 SQL 怎么写其实树形查询慢的根子往往在建模上。表结构决定了你能用什么方式查、索引能不能生效、数据变更的成本高不高。所以第一步不是写查询是盘点自己这张树表到底属于哪类模型。1.1 邻接表——最常见也最容易踩坑的模型所谓邻接表就是每行存一个parent_id指向父节点顶级节点的parent_id为 0 或 NULL。这是国内业务系统里最主流的做法因为建表简单、插入一条数据只需要知道父节点 ID删除子节点也直接DELETE就行。CREATE TABLE category ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, parent_id INT NOT NULL DEFAULT 0, sort_order INT NOT NULL DEFAULT 0, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB;邻接表的优点很直白模型直观、写入快、事务控制简单。但它的查询痛点同样直白——查询一棵完整子树没有一条 SQL 能搞定。MySQL 5.7 及以前版本不支持递归查询常规做法是写存储过程循环查或者直接在应用层多次查询后组装。即便 MySQL 8.0 支持了递归 CTE深层次数据查询依然依赖临时表性能上限取决于树深度和数据总量。另一个容易忽略的问题邻接表对移动子树操作其实是很方便的只要改一个节点的parent_id。但对查询某节点的所有祖先和查询某节点的所有后代这类高频操作邻接表天然不友好。如果你发现业务里 70% 的查询都是给我这个分类下所有子分类的商品数那你应该认真考虑要不要换模型了。1.2 进阶方案对比路径枚举、嵌套集与闭包表树形建模不止邻接表一种。做优化之前最好把几个候选方案放在一张表里看清楚差异。方案核心字段查询子树查询祖先插入成本修改成本适用场景邻接表parent_id递归 CTE递归 CTE极低低数据量小、层级浅、写频繁路径枚举path 如 001/002/003前缀 LIKE前缀 LIKE低高改父节点要批量 UPDATE层级固定、查询按路径排序嵌套集left_num / right_num范围查询范围查询很高需平移大量节点极高高频读、低频写、层级固定闭包表独立关系表索引 JOIN索引 JOIN较高需批量插入关系中删除/移动需维护关系大数据量、频繁查子树和祖先我这里直接给结论如果你的树表数据量在几千到几万节点、层级不超过四五层邻接表 递归 CTE 合理索引就足够了别为了一点性能把架构搞复杂。数据量到了几十万节点且查询子树是核心场景老老实实上闭包表。路径枚举适合层级深度固定的场景比如三级分销、固定的省市县关联这时候前缀查询 索引的效率非常稳定。嵌套集最不推荐做动态树因为每插入一个节点就要更新一大片 left/right 值在并发写入场景下很容易锁冲突。1.3 方案选型的判断依据我选方案只看三件事节点量级、树的深度、读写比例。节点少于 10 万、深度低于 6 层、读多写多都有的就邻接表顶住。节点几十万往上、深度能到十几层甚至无限层、并且读远多于写的闭包表更合适。另外要注意闭包表往往要配合一张原数据表一起用单纯一张关系表拿不到节点名称查询时 JOIN 方式要提前设计好。2. 邻接表递归查询从递归 CTE 入手如果你的表就是邻接表并且数据量可控MySQL 8.0 的递归 CTE 是首选方案没有之一。MySQL 5.7 用户只能用存储过程模拟或者升级 8.0两条路二选一。新项目直接 8.0老项目想办法说服业务方加索引至少别在应用层搞递归循环——那是性能灾难。2.1 递归 CTE 的写法与执行逻辑递归 CTE 的语法结构分三块锚点成员seed、递归成员recursive member、外层查询。锚点成员负责找到树根递归成员负责一层层往下扩展然后用UNION ALL把结果合并起来。WITH RECURSIVE category_tree AS ( -- 锚点查根节点 SELECT id, name, parent_id, 1 AS depth FROM category WHERE parent_id 0 UNION ALL -- 递归通过 parent_id 关联 CTE 结果集 SELECT c.id, c.name, c.parent_id, ct.depth 1 FROM category c INNER JOIN category_tree ct ON c.parent_id ct.id ) SELECT id, name, depth FROM category_tree ORDER BY depth, id;要注意第一个SELECT是种子第二个SELECT会反复执行直到某次 JOIN 查不到新数据为止。第二次 SELECT 里的category_tree指的是上一步刚产出的数据而不是全量结果这一点刚开始用很容易误解。执行过程可以理解成先查根再查根的子节点再查子节点的子节点一层一层往下走每一层的结果都被写进临时表直到没有新行产生。有几个细节直接影响正确性和性能。第一UNION ALL不要写成UNION因为UNION会去重可能把本应重复出现的节点干掉而且去重本身有额外开销。第二递归成员里 JOIN 的两侧字段必须类型一致parent_id和id类型不一致的话索引可能用不上临时表也会变大。第三外层ORDER BY是对最终全量结果排序不是对每一层排序如果只想让每一层内部有序递归成员里就得加排序但那开销更大不如外层统一排。2.2 递归的边界条件与深度控制递归 CTE 有一个隐含的坑如果数据里有环——比如 A 的父节点是 A 自己或者 A 指向 B、B 又指向 A——递归会无限循环。MySQL 8.0 为了解决这个问题默认设置了递归上限由cte_max_recursion_depth控制默认 1000。超过这个迭代次数会直接报错ERROR 3636。所以哪怕数据坏掉了查询也不会挂死这一点要感谢数据库兜底。-- 查看当前递归深度上限 SHOW VARIABLES LIKE cte_max_recursion_depth; -- 会话级临时调大不建议全局改 SET SESSION cte_max_recursion_depth 100000;我实测过 10 万节点的树深度大概 10 层默认 1000 的深度上限是够用的因为它限制的是迭代轮数而不是节点数。但如果你的树是单链结构也就是每个节点只有一个子节点节点数 2000 就会触发 1000 上限。这时候要么调大这个变量要么在查询里加一个显式的深度过滤条件WITH RECURSIVE category_tree AS ( SELECT id, name, parent_id, 1 AS depth FROM category WHERE parent_id 0 UNION ALL SELECT c.id, c.name, c.parent_id, ct.depth 1 FROM category c INNER JOIN category_tree ct ON c.parent_id ct.id WHERE ct.depth 20 -- 防止异常树导致过度递归 ) SELECT * FROM category_tree;这种限制深度的条件是双保险。就算cte_max_recursion_depth没被调大查询也会在 20 层停下来避免生产环境出现一次错误的递归把数据库 CPU 打满。3. 索引设计与查询改写实战树形查询优化索引占了至少一半的功劳。很多人的树表就只有一个主键索引和parent_id裸字段查询子节点靠WHERE parent_id ?这种 SQL 在数据量上来之后必然全表扫描。千万别只看数据量小就忽略索引树形表数据增长速度比想象中快得多。3.1 索引怎么建不只是 parent_id 加索引parent_id加索引是基本操作但真正好用的索引要考虑组合业务查询。比如电商分类的典型查询是查某个父节点下的子分类按排序值排好那么(parent_id, sort_order)做复合索引比单列parent_id索引效率高很多因为索引里已经包含排序字段排序不用回表再做文件排序。ALTER TABLE category ADD INDEX idx_parent_sort (parent_id, sort_order);如果你业务里还经常按level或status过滤比如查某个父节点下所有启用状态的分类可以考虑(parent_id, status, sort_order)。但要注意不要盲目加一堆复合索引树表如果写入频繁索引过多会拖慢插入。我的习惯是先收集慢查询日志里出现频率最高的几个 WHERE 组合再针对性建索引而不是一开始就铺满。有些场景还会用到ORDER BY depth或者ORDER BY path这种排序在递归 CTE 外层做走不了索引。如果排序需求确实很重考虑在物化路径或闭包表方案里解决邻接表里强行排序没有太多优化空间。3.2 查询改写排序、分页、聚合场景的处理递归 CTE 的结果是一个临时结果集对它的排序和分页其实是在临时表上操作的。节点数多到一定程度临时表会落到磁盘性能立刻下降。这里有个经验不要对全树做递归后再 LIMIT 分页而是先把树的骨架算出来再回表补业务字段。举个例子。分类表里除了树结构还有商品数量统计字段product_count页面要展示包含商品数量最多的前 10 个叶子节点。如果先递归出所有叶子再做聚合排序整体开销很大。更好的方式是先通过parent_id查叶子节点再用商品数排序WITH RECURSIVE category_tree AS ( SELECT id, parent_id, 1 AS depth FROM category WHERE parent_id 0 UNION ALL SELECT c.id, c.parent_id, ct.depth 1 FROM category c INNER JOIN category_tree ct ON c.parent_id ct.id ) SELECT ct.id, ct.depth, cg.name, cg.product_count FROM category_tree ct INNER JOIN category cg ON cg.id ct.id WHERE NOT EXISTS ( SELECT 1 FROM category child WHERE child.parent_id ct.id ) ORDER BY cg.product_count DESC LIMIT 10;这种先递归骨架、再回表补数据的写法比在递归成员里疯狂 JOIN 业务表要快得多因为递归部分只需要访问两个字段id和parent_id内存占用小临时表不容易落盘。另一个常见的场景是查某节点下的所有叶子节点聚合统计比如统计整个分类树的商品总额。这个需求在有product_count字段后可以改成先递归出后代 ID再SUM商品数WITH RECURSIVE descendants AS ( SELECT id FROM category WHERE id ? UNION ALL SELECT c.id FROM category c INNER JOIN descendants d ON c.parent_id d.id ) SELECT SUM(cg.product_count) FROM descendants d INNER JOIN category cg ON cg.id d.id;这里把SUM放在递归之后做MySQL 会把递归结果物化到临时表后再聚合。如果节点数上万聚合速度依然不错但要注意临时表大小。实际业务中如果这个聚合查询极其频繁我更建议直接在category表里冗余一个root_id字段标记每个节点属于哪棵顶级树把整棵树统计变成按 root_id 分组统计查询效率完全不在一个量级。4. 大数据量下的闭包表实践如果你的树表超过几十万节点邻接表加递归 CTE 怎么优化都有限。递归的本质是逐层扫描节点层级一深扫描次数成倍增长。这时候闭包表是更靠谱的方案用一张独立的表把每一个祖先-后代关系都存下来。查询子树和祖先变成了纯索引查找不再依赖递归。4.1 闭包表的设计与查询优势闭包表的核心是一张关系表category_closure记录每个节点与其所有祖先的关系同时包含节点自身到自身的关系深度为 0。CREATE TABLE category_closure ( ancestor_id INT NOT NULL, descendant_id INT NOT NULL, depth INT NOT NULL, PRIMARY KEY (ancestor_id, descendant_id), KEY idx_descendant (descendant_id) ) ENGINEInnoDB;举个例子A 是顶级节点B 是 A 的子节点C 是 B 的子节点。闭包表里会存在 A-A深度 0、A-B深度 1、A-C深度 2、B-B深度 0、B-C深度 1、C-C深度 0这六条关系。查询 C 的所有祖先一句 SQL 就出来了SELECT ancestor_id, depth FROM category_closure WHERE descendant_id C_ID ORDER BY depth DESC;查询 A 的所有后代同样简单SELECT descendant_id, depth FROM category_closure WHERE ancestor_id A_ID;和递归 CTE 相比闭包表没有任何逐层扩展的过程全部命中主键索引或二级索引。实测二十万节点、平均深度十五层的树查询某个节点下所有后代的耗时尚且能控制在几十毫秒内这在邻接表递归方案里基本做不到。但闭包表也有代价。第一数据量会膨胀。每条数据都会在闭包表里对应多条关系整体数量级大约是节点数 × 平均深度。所以节点多但深度极浅的树闭包表优势不明显节点多且深度深闭包表才是王者。第二增删改需要同步维护关系表不能单独只改主表。这就是很多人不敢用闭包表的真正原因——写逻辑变复杂了。4.2 插入、删除、移动节点时的增量维护闭包表的写入不能直接 INSERT 一条记录插入一个新节点时需要同时给它和它的所有祖先建立关系。正确做法是查一遍当前新节点的父节点的所有祖先然后批量插入。INSERT INTO category_closure (ancestor_id, descendant_id, depth) SELECT ancestor_id, NEW_ID, depth 1 FROM category_closure WHERE descendant_id PARENT_ID UNION ALL SELECT NEW_ID, NEW_ID, 0;这条 SQL 的原理父节点的所有祖先也是新节点的祖先但深度都要加一同时新节点自己到自己的关系单独插入。如果没有闭包表这一步就是一个简单的INSERT INTO category ... VALUES (...),有了闭包表就变成了两条 SQL 的组合而且必须在事务里执行保证主表和关系表一致。删除节点的时候要做两件事物理删除或者逻辑删除主表记录然后删除闭包表里所有与这个节点相关的行。DELETE FROM category_closure WHERE descendant_id DELETED_ID OR ancestor_id DELETED_ID;如果删除的是父节点且希望子节点一并删除级联删除闭包表会复杂很多。我的建议是生产环境尽量不要用物理级联删除树改为逻辑删除标志位然后在应用层处理子树的状态变更这样闭包表维护成本可控。移动节点是闭包表最繁琐的操作。把一个子树从 A 节点移到 B 节点下需要先把子树相关关系全部删除再按新路径重新生成。这里面稍有不慎就会留下孤儿关系。实践中我把这个逻辑封装成存储过程在事务里分步执行每一步都有ROW_COUNT()校验任何一步不匹配就ROLLBACK。DELIMITER // CREATE PROCEDURE move_category(IN node_id INT, IN new_parent_id INT) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END; START TRANSACTION; -- 删除节点自身与其所有祖先的关系 DELETE FROM category_closure WHERE descendant_id IN ( SELECT descendant_id FROM ( SELECT descendant_id FROM category_closure WHERE ancestor_id node_id ) tmp ) AND ancestor_id NOT IN ( SELECT ancestor_id FROM ( SELECT ancestor_id FROM category_closure WHERE descendant_id node_id ) tmp2 ); -- 重新建立新路径关系逻辑同插入 INSERT INTO category_closure (ancestor_id, descendant_id, depth) SELECT c1.ancestor_id, c2.descendant_id, c1.depth c2.depth 1 FROM category_closure c1, category_closure c2 WHERE c1.descendant_id new_parent_id AND c2.ancestor_id node_id; -- 更新主表 parent_id UPDATE category SET parent_id new_parent_id WHERE id node_id; COMMIT; END// DELIMITER ;闭包表的维护逻辑一定要和业务写入封装在一起不要散落在各个 service 方法里否则早晚会出数据不一致的问题。我见过不止一次线上事故都是因为在某个角落直接 INSERT 了 category 表忘了同步闭包表导致整棵树的查询结果缺胳膊少腿。5. 常见问题与排查技巧实录树形表优化的坑写代码是一回事上线跑起来又是另一回事。这一部分是我自己排查慢查询和数据异常时总结的经验不一定每个都与你遇到的一致但大概率能提供思路。5.1 递归查询超时卡死怎么查现象是页面打开分类管理转圈数据库 CPU 飙到 100%。第一反应先看是不是递归上环了。如果数据里存在 A 指向 B、B 又指向 A 的环递归 CTE 会反复迭代直到cte_max_recursion_depth上限这期间临时表越来越大CPU 和内存都被吃光。排查方法是在测试环境复现后跑一下 EXPLAINEXPLAIN ANALYZE WITH RECURSIVE category_tree AS ( SELECT id, parent_id, 1 AS depth FROM category WHERE parent_id 0 UNION ALL SELECT c.id, c.parent_id, ct.depth 1 FROM category c INNER JOIN category_tree ct ON c.parent_id ct.id ) SELECT * FROM category_tree;看actual time和rows是不是按指数级增长。正常树的节点数增长是收敛的有环时会一直翻倍直到报错。另外可以检查performance_schema的语句事件表找出对应语句消耗的资源是否集中在Creating temp table阶段。另一个常见原因是递归成员查询没有走索引。parent_id如果没建索引每一层递归都要全表扫描数据量稍大就原地爆炸。这种问题 EXPLAIN 里能看到type: ALL。加上索引后应该变成ref或者eq_ref。如果确认没环、索引也建了还是慢那就是临时表落盘了。递归 CTE 的中间结果会写到临时表节点数太大时临时表从内存转磁盘性能骤降。查看SHOW STATUS LIKE Created_tmp_disk_tables如果数值在查询前后明显增加就说明落盘了。这种情况下要么调整tmp_table_size和max_heap_table_size只对会话级有效全局改有风险要么换闭包表。5.2 树结构数据维护的几个深坑先说自关联外键。很多人建表时给parent_id加了外键约束这在树形结构里相当危险。删除一个父节点时如果还有子节点引用它外键约束直接报错如果级联删除一不小心删掉一整棵子树。生产环境我强烈建议去掉外键约束只在应用层做逻辑校验必要时用定时任务扫描孤儿节点。再说批量导入数据。从 Excel 导入分类树时数据顺序有时候是打乱的父子关系可能还没建立就插入了子节点这时候parent_id指向的记录不存在。导入脚本必须做两层校验先插所有根节点再插子节点或者导入完成后跑一条找孤儿的 SQLSELECT c.id, c.name, c.parent_id FROM category c LEFT JOIN category p ON p.id c.parent_id WHERE c.parent_id ! 0 AND p.id IS NULL;这条 SQL 也适合做日常巡检我习惯放到每周的定时任务里发现孤儿就告警避免问题积累到最后连修复都无从下手。第三个坑是深度字段的冗余。如果你在 category 表里冗余了level或depth字段插入和移动节点时要记得同步更新。这个字段一旦不一致前端展示的层级缩进就会错乱而且很难排查。为了避免这种情况我一般不在邻接表里冗余 depth真的需要深度信息就现场用递归 CTE 算或者用闭包表的关系表里现成的 depth 字段。5.3 实用排查命令与建议速查整理一个速查表遇到对应问题直接照着做。症状可能原因排查方式解决方案递归查询报错 3636数据成环或树过深查日志定位报错语句修复数据环或临时调大 cte_max_recursion_depth查询越来越慢parent_id 无索引EXPLAIN 看 typeALL给 parent_id 建索引或建复合索引分类树缺节点闭包表漏维护对比主表和闭包表数量重建闭包表或触发增量修复任务子节点找不到父节点导入顺序错乱跑孤儿检测 SQL修正导入顺序补全缺失父节点移动节点后数据混乱闭包表删除不全检查关系条数回滚后使用封装好的存储过程临时表落盘导致慢查询节点数过大SHOW STATUS 查临时表落盘次数优化递归范围或迁移闭包表闭包表如果历史数据已经乱了最稳的修复方式是全量重建。停写窗口内清空闭包表然后用递归 CTE 遍历主表所有节点重新生成关系。节点量大时重建很耗时需要提前规划好窗口。我的经验是一百万节点的树重建闭包表大概需要几分钟到十几分钟具体看服务器磁盘速度。最后说一句我自己的实际体会。树形表的优化方案不是越高级越好而是越匹配业务场景越好。我做过一个核心链路是查整棵后代树的数据系统邻接表怎么调都差一口气换成闭包表后查询从秒级降到毫秒级收益巨大。但另一个项目树节点只有几百个用闭包表反而让代码变复杂后来回退到邻接表简单又稳定。如果要在新项目里做设计我的建议是默认用邻接表把业务跑通等数据量真正涨上来了再评估是否引入闭包表或物化路径并且一定要用压测数据验证不要凭感觉拍板。毕竟方案换了周边所有代码逻辑都要跟着动这个成本往往比大多数人预想得高。
