拓冰网站定制 
1. 从一个真实需求说起为什么动态子类别筛选会变成性能瓶颈做过后台管理系统的后端同学应该都见过这样的需求商品分类是个树形结构用户在前端选中数码产品这个父分类页面上要立刻展示出它下面所有层级子分类的商品同时还要显示手机、笔记本电脑、耳机这些直接子类各自有多少件商品。最开始用 parent_id 存分类关系的时候觉得挺简单等分类数据涨到几万条、商品数据几百万条之后这个简单需求就开始折磨人了。先说清楚标题里的动态子类别筛选到底指什么。它包含两层意思一是在运行时根据传入的父分类ID动态地把该父分类下所有层级的子分类ID捞出来不是写死只查一级二是拿这一整包子分类ID去业务表商品、文章、物料等里筛选数据并且通常还要带上每个子分类的统计信息。这两个动作合在一起才是完整的动态子类别筛选。这篇文章以 PostgreSQL 为例用的例子是商品分类场景但思路完全可以平移到任何树形结构数据上——组织架构选人、文章分类检索、SKU维度筛选、BOM物料树展开套路都是一样的。内容覆盖数据模型选型、递归CTE写法、性能优化、API层设计、踩坑修复五块适合后端工程师、DBA、以及在做商城或ERP系统的同学参考。另外说一句热词里关于PostgreSQL安装的问题这里不展开讲不管你是用16还是17版本甚至便携版本文涉及的SQL语法和索引策略在这些版本上行为一致不需要担心版本差异。唯一建议是生产环境尽量用16及以上版本递归查询优化器在16里又有一些改进后面会提到。2. 数据模型选型为什么我最终留在邻接表加递归CTE2.1 四种树形存储方案的横向对比树形结构在关系型数据库里从来都不是一种答案常见的方案有四种邻接表Adjacency List、物化路径Materialized Path、闭包表Closure Table、以及PostgreSQL专有的 ltree 扩展。把它们的核心差异放一起看更直观。方案存储方式查子树难度插入/移动节点典型使用者邻接表每行存 parent_id需要递归CTE改一个字段即可大多数业务系统物化路径每行存 path如 /1/5/12/前缀匹配快改路径字符串要小心评论、分类树闭包表单独一张表存所有祖先-后代关系直接查关联表插入/删除都要维护多行权限树、多级分销ltree专门的树标签类型内置操作符类似物化路径PostgreSQL专用场景各有各的适用面。闭包表查询确实是三范式里最爽的一个 JOIN 就能拿到任意节点的全部后代但写入成本高每挂一个子节点要往闭包表插一批记录删除某个中间节点更是牵一发动全身。物化路径查询性能好但路径字符串的维护逻辑容易漏尤其是做把某个父分类整棵挪到另一个父分类下面这种操作时所有后代的 path 都要重写。ltree 很强但它是PostgreSQL的扩展换数据库就废了而且如果分类ID是业务主键而不是有序标签适配成本也不低。邻接表最大的优点是符合直觉categories 表里每个分类就一行parent_id 指向父亲看起来和业务模型一模一样。但它的劣势也明显——查子树必须递归。很多同学被递归慢这个刻板印象吓住了直接在应用层写循环一次查一层几十层分类要请求几十次数据库这才是真正的性能灾难。2.2 我为什么选择邻接表我最终选择邻接表加 PostgreSQL 递归CTE理由有三个。第一PostgreSQL 的WITH RECURSIVE对邻接表查询的支持成熟度非常高优化器能把递归部分和其他条件合并优化配合好索引几万分类节点、五到六层深度的情况下一次递归查询基本都在几十毫秒级别。第二业务系统的分类树低频写、高频读。商品分类几天甚至几周才调整一次而筛选接口每秒被调几十次。邻接表的写入成本本来就低再加一层物化视图或缓存做读优化整体结构最简单可靠。第三团队协作成本低。换一个人来接手看到 parent_id 立刻明白数据结构如果上闭包表新人第一次维护时大概率会漏插或重复插记录。这不是说其他方案一无是处。如果你的分类树深度固定且层级很少比如三级品类物化路径其实也够用如果你要做极其频繁的祖先-后代判定闭包表更合适。但就动态子类别筛选这个需求来说邻接表是性价比最高、最容易落地和排错的地基。3. 动态筛选择SQL的核心递归CTE的解剖与实战写法3.1 先搞定取全量子分类这个单元假设分类表结构如下CREATE TABLE categories ( id BIGSERIAL PRIMARY KEY, name VARCHAR(128) NOT NULL, parent_id BIGINT REFERENCES categories(id), sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX idx_categories_parent_id ON categories(parent_id);parent_id为 NULL 表示顶层分类。现在要查 ID 为 1001 这个父分类下所有层级的子分类标准写法是这样的WITH RECURSIVE category_tree AS ( -- 非递归部分先取出初始节点 SELECT id, parent_id, name, 1 AS depth, ARRAY[id] AS path FROM categories WHERE id 1001 UNION ALL -- 递归部分沿着 parent_id 往下钻 SELECT c.id, c.parent_id, c.name, ct.depth 1, ct.path || c.id FROM categories c JOIN category_tree ct ON c.parent_id ct.id WHERE NOT c.id ANY(ct.path) -- 防环保险 ) SELECT id, parent_id, name, depth FROM category_tree ORDER BY depth, sort_order;拆开解释一下。非递归部分负责播种递归部分负责一层层往下找孩子每一轮的结果都会重新喂给下一轮 JOIN。depth字段用来记录节点在第几层path数组存的是从根到当前节点的完整路径主要作用是防环——如果数据里有人手工把 parent_id 改成了循环引用没有这个条件递归CTE会无限循环直到数据库把内存吃光。这个path写法在实际生产中我强烈建议保留哪怕是正常数据。后面讲踩坑的时候我会展开说因为我在真实项目里确实遇到过循环引用导致的诡异故障。3.2 把子分类集合接上商品筛选上面拿到了子分类ID列表接下来要筛选商品。这一步最容易犯的错是把递归结果直接当子查询丢进IN代码变成这样SELECT * FROM products WHERE category_id IN ( WITH RECURSIVE category_tree AS ( ... ) SELECT id FROM category_tree );这样写不是不能跑但问题在于如果子分类特别多IN 后面的列表会非常长执行计划可能会变成逐行扫描而且整条 SQL 又长又难维护。更优雅的做法是让递归CTE先运算完再 JOIN 商品表WITH RECURSIVE category_tree AS ( SELECT id, parent_id, 1 AS depth, ARRAY[id] AS path FROM categories WHERE id 1001 UNION ALL SELECT c.id, c.parent_id, ct.depth 1, ct.path || c.id FROM categories c JOIN category_tree ct ON c.parent_id ct.id WHERE NOT c.id ANY(ct.path) ) SELECT p.id, p.name, p.price, p.category_id, ct.depth AS category_depth FROM products p JOIN category_tree ct ON p.category_id ct.id WHERE p.status on_sale ORDER BY p.created_at DESC LIMIT 20;把递归CTE当作一个动态生成的维度表再去 JOIN 业务数据执行计划里 JOIN 顺序由优化器决定配合商品表的category_id索引效果比 IN 子查询稳定很多。还有一个小技巧如果你只需要某个父分类下所有商品的数量可以这样写WITH RECURSIVE category_tree AS ( ... ) SELECT c.name AS category_name, COUNT(p.id) AS product_count FROM category_tree c LEFT JOIN products p ON p.category_id c.id GROUP BY c.id, c.name ORDER BY product_count DESC;这就把动态子类别和子类别各自的数量一次性查出来了前端做联动展示时不需要再发一次统计请求。3.3 用参数化实现真正的动态上面例子把分类ID写死成 1001实际接口里肯定要把它变成参数。PostgreSQL 支持$1这种参数占位WITH RECURSIVE category_tree AS ( SELECT id, parent_id, 1 AS depth, ARRAY[id] AS path FROM categories WHERE id $1 UNION ALL SELECT c.id, c.parent_id, ct.depth 1, ct.path || c.id FROM categories c JOIN category_tree ct ON c.parent_id ct.id WHERE NOT c.id ANY(ct.path) ) SELECT p.id, p.name, p.price, p.category_id, ct.depth AS category_depth FROM products p JOIN category_tree ct ON p.category_id ct.id WHERE p.status on_sale ORDER BY p.created_at DESC LIMIT 20 OFFSET $2;这里要说一个比较容易被忽略的细节LIMIT ... OFFSET ...的分页方式在商品量大、筛选范围大的时候有坑。OFFSET越大数据库要丢掉的行越多性能直线下降。如果帖子列表是按创建时间倒序的更稳妥的方案是用游标分页WHERE p.created_at $3 ORDER BY p.created_at DESC LIMIT 20;调用方把上一次返回结果里最小的created_at传进来下次翻页就从这里继续。这个模式在动态子类别筛选这种高频接口里非常实用能把深分页的性能问题彻底绕开。4. 性能优化从索引设计到物化路径升级4.1 索引策略怎么配合递归CTE递归部分最核心的查询条件是c.parent_id ct.idcategories(parent_id)索引是必须的上面建表时已经加上了。如果再叠加上业务条件比如只统计status active的订单但状态字段在订单表里那就要给订单表的category_id加联合索引。实际项目中我给商品表做的索引是这样的CREATE INDEX idx_products_category_status ON products(category_id, status); CREATE INDEX idx_products_category_created ON products(category_id, created_at DESC);第一个索引支撑某个分类下所有在售商品这种高频过滤第二个索引支撑按分类聚合后按时间排序的分页查询。两个索引的代价是有额外写入开销但商品表读多写少这个成本完全可以接受。递归CTE本身的预热问题值得提一下。每次请求新建独立的CTE、递归扫描分类表如果分类表几千行PostgreSQL会把整个表数据加载进缓存快到毫秒级。但并发一高循环反复扫描同一棵子树浪费明显。这时可以在应用层加一层短TTL缓存比如30秒把分类ID - 全量子分类ID数组这个映射缓存起来。这样SQL直接变成SELECT ... FROM products p JOIN unnest($1::bigint[]) AS cat_id ON p.category_id cat_id WHERE p.status on_sale;这个写法把递归从主查询中挪走了性能提升非常直观。对于分类基本不变、访问量大的场景我推荐优先做这层缓存——比所谓调参数靠谱得多。4.2 升级方案给表加冗余物化路径列如果递归CTE加上缓存后还是觉得每次都要算一遍不舒服还有一个更彻底的方案在分类表里直接加一列存物化路径。ALTER TABLE categories ADD COLUMN path BIGINT[]; -- 用递归CTE一次算完全表的路径 WITH RECURSIVE category_tree AS ( SELECT id, parent_id, ARRAY[id] AS path FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, ct.path || c.id FROM categories c JOIN category_tree ct ON c.parent_id ct.id ) UPDATE categories c SET path ct.path FROM category_tree ct WHERE c.id ct.id;有了path数组后查某个节点全部后代就变成数组前缀匹配SELECT * FROM categories WHERE path[:] $1; -- 数组前N个元素等于父分类路径配合 GIN 索引可以做到相当快的查询速度。这个方案的代价是以后每次增删分类都要同步维护path最常见做法是写触发器或者干脆在应用层事务里更新时同步修一下。我的建议是分类树规模小、更新频率低直接用邻接表加递归CTE就行如果分类规模到了十万级且要求极低延迟物化路径列值得考虑。多数系统的分类树撑不到这个量级不要为了不存在的性能焦虑过度设计。4.3 递归深度与死循环保护PostgreSQL 的WITH RECURSIVE没有内置递归深度上限一旦数据里有环查询就会一直执行下去。除了在递归部分加WHERE NOT c.id ANY(ct.path)之外还有两道保险措施。一个是在表里加CHECK (parent_id IS NULL OR parent_id id)从源头禁止自引用——只能防止自己挂自己防不了 A 挂 B、B 挂 A 这种环。另一个是在递归CTE里设深度上限把防环保险从逻辑层面再上一道锁WITH RECURSIVE category_tree AS ( SELECT id, parent_id, 1 AS depth, ARRAY[id] AS path FROM categories WHERE id $1 UNION ALL SELECT c.id, c.parent_id, ct.depth 1, ct.path || c.id FROM categories c JOIN category_tree ct ON c.parent_id ct.id WHERE NOT c.id ANY(ct.path) AND ct.depth 10 -- 业务上分类深度不可能超过10层 ) SELECT ...加上ct.depth 10之后即使在数据异常的情况下递归也会在10轮之内强行终止。随时随地想想这个查询最坏会跑多久是写递归SQL的基本素养。5. API层联动设计一个接口应对多级联动筛选5.1 动态筛选接口的输入输出设计拿到SQL之后接口侧的设计同样重要。很多项目把动态子类别筛选拆成两个接口一个查子分类树一个查商品列表前端先请求分类树再逐级请求商品。这样做的坏处是用户每次点击一个分类都要等着两次网络往返。做联动体验接口应当砍到一个。我实际用的接口设计是POST /api/categories/filter { category_id: 1001, depth: 0, // 0表示不限深度 page_size: 20, cursor: null, // 游标翻页时传上一次返回的 last_created_at keyword: // 可选关键字过滤 }返回结构{ categories: [ { id: 1002, name: 手机, parent_id: 1001, depth: 1, product_count: 356 }, { id: 1003, name: 笔记本电脑, parent_id: 1001, depth: 1, product_count: 128 } ], products: [ { id: 98765, name: 某品牌手机, price: 3999, category_id: 1002, created_at: 2024-12-01T10:00:00Z } ], last_created_at: 2024-12-01T10:00:00Z }categories里返回的是父分类的直接子分类和各自的商品数量products返回的是该父分类下所有层级子分类的商品分页结果。前端拿到这个返回值左侧展示子分类时只需要遍历categories右侧商品列表直接渲染products不需要再请求任何接口。这就是动态子类别筛选在服务端做和在前端拼的本质区别。5.2 用 JSONB 聚合把树拼出来如果前端不想自己用平铺列表拼树结构PostgreSQL 的jsonb_agg可以把递归结果直接聚合成 JSON 树。比如要返回整棵子分类树而不是只有直接子分类可以这样写WITH RECURSIVE category_tree AS ( SELECT id, parent_id, name, 1 AS depth, ARRAY[id] AS path FROM categories WHERE id $1 UNION ALL SELECT c.id, c.parent_id, c.name, ct.depth 1, ct.path || c.id FROM categories c JOIN category_tree ct ON c.parent_id ct.id WHERE NOT c.id ANY(ct.path) ), product_stats AS ( SELECT p.category_id, COUNT(*) AS cnt FROM products p JOIN category_tree ct ON p.category_id ct.id GROUP BY p.category_id ) SELECT jsonb_build_object( id, ct.id, name, ct.name, product_count, COALESCE(ps.cnt, 0), children, ( SELECT jsonb_agg(child) FROM ( SELECT ... FROM category_tree child WHERE child.parent_id ct.id ) child ) ) FROM category_tree ct LEFT JOIN product_stats ps ON ps.category_id ct.id WHERE ct.id $1;这种写法能直接在数据库里拼出嵌套的 JSON 树后端不用再递归构造对象。但要注意category_tree 里每一行都执行一次子查询来聚合 children当分类规模较大时嵌套子查询的开销不小。我的经验是分类树节点少于几百个时随便拼节点上了几千就老老实实返回平铺列表让前端拼或者用应用层代码组装树。数据库强的是集合运算不是遍历拼树。5.3 联动筛选的常见体验问题联动筛选有一个特别容易翻车的点数据权限范围。比如后台管理员可能只能看某些分类的数据如果接口直接按分类ID过滤一旦有人传了一个他无权访问的父分类ID数据就泄露了。解决方法是接口里把我能访问的分类ID集合和请求的父分类ID集合先求交集再进递归WITH RECURSIVE accessible_tree AS ( SELECT ... FROM categories WHERE id ANY($allowed_category_ids) AND id $1 ... )这个过滤器虽然看起来只是多了个条件但正是这种细节决定了系统能不能上生产。我见过不少项目在联调时才发现这个问题重构接口时还要动前端代价不小。6. 实际排查过的坑循环引用、错误计数与脏数据6.1 自引用循环导致的查询挂死有一次客户反馈后台某个分类一打开页面就转圈数据库CPU飙到100%。查慢查询日志发现有一条递归CTE跑了二十分钟还没结束。我手动执行了一下发现是有人把某个子分类的 parent_id 改成它自己了。由于递归条件里没有防环逻辑CTE每轮都能通过c.parent_id ct.id查到它自己循环往复直到把整个数据库的资源吃干。这也是一次深刻教训。在那之后我所有递归CTE都强制带上NOT c.id ANY(ct.path)这个条件。对绝大多数业务表防环代价几乎为零但能让最糟糕的异常数据只产生一次错误查询而不是一次灾难。6.2 统计数字对不上的灵异现象另一个常见问题左侧子分类数量显示手机有356件右侧商品列表翻完所有页只看到200件。排查后确认问题出在JOIN ... ON p.category_id ct.id时同一件商品挂在分类目录的多个层级下——比如手机既挂在数码产品/手机下又挂在全部商品/数码/手机下。分类和商品的多对多关联或者历史数据冗余都会造成重复计数。遇到这种需求首先要和业务方确认统计口径是商品在分类树上唯一归属还是商品可多挂在多个分类。如果允许一对多聚合前必须去重或者用递归CTE只取最小深度的分类作为归属分类。如果是一对一就要在数据库层面做校验防止业务写入脏数据。动态筛选本身不难难的是统计口径定义清楚。6.3 空分类、孤立节点与删除级联递归CTE的递归部分从初始节点往下找孩子如果某个父分类没有任何子分类category_tree 就只剩一个节点商品列表自然为空。这个没问题。真正容易踩的是父分类删了子分类还在。如果categories.parent_id设置了REFERENCES categories(id)外键删除父分类时数据库会报外键冲突保护数据不会变成孤立节点。但很多项目为了省事没加外键删除分类靠应用层代码一条条删。一旦漏删了某个子分类它就变成孤儿永远不出现在任何父分类的递归结果里但商品数据却还能查到它。这类脏数据的最优解是加外键约束加级联删除ALTER TABLE categories ADD CONSTRAINT fk_category_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE CASCADE;这样删父分类时数据库自动删掉所有后代分类。商品表那边要不要级联删要看业务——一般来说应该设置ON DELETE RESTRICT或者先让商品转移分类防止误删商品数据。6.4 临时表与递归CTE的异常排查技巧排查递归问题有一个很好用的手段把递归CTE单独抽出来不 JOIN 商品表直接看它返回多少行、每个节点的 depth 是否正确。比如WITH RECURSIVE category_tree AS ( ... ) SELECT depth, count(*) FROM category_tree GROUP BY depth ORDER BY depth;这个输出能让你一眼看出递归是不是按预期在逐层展开。如果 depth2 的节点数突然变成几千说明那里有个父分类挂了一个错误根节点属于典型的数据迁移事故——导入分类表时把顶层分类的 parent_id 设置成了某个已经存在的分类ID。7. 总结一点个人体会做动态子类别筛选这件事技术上本身不复杂复杂的是把数据模型、递归SQL、索引策略和接口设计串在一起并且预判到各种边界情况。我个人的习惯是先在分类表那一层把递归CTE调稳定再处理业务表关联最后才做接口封装。一旦哪一步结果对不上就单独跑其中一段SQL排查不要整条大SQL一把梭那样只会浪费时间。也分享一个收尾小技巧正式上线前往分类表里插入几条模拟的循环引用和孤儿节点数据再把动态筛选SQL跑一遍确认不会挂死、结果符合预期。这套脏数据演习每次都能帮我提前发现线上问题。树形数据结构的坑基本都是脏数据引入的提前做过抵抗测试比上线后救火舒服得多。 PostgreSQL递归CTE实现动态子类别筛选：从原理到性能优化实践
1. 从一个真实需求说起为什么动态子类别筛选会变成性能瓶颈做过后台管理系统的后端同学应该都见过这样的需求商品分类是个树形结构用户在前端选中数码产品这个父分类页面上要立刻展示出它下面所有层级子分类的商品同时还要显示手机、笔记本电脑、耳机这些直接子类各自有多少件商品。最开始用 parent_id 存分类关系的时候觉得挺简单等分类数据涨到几万条、商品数据几百万条之后这个简单需求就开始折磨人了。先说清楚标题里的动态子类别筛选到底指什么。它包含两层意思一是在运行时根据传入的父分类ID动态地把该父分类下所有层级的子分类ID捞出来不是写死只查一级二是拿这一整包子分类ID去业务表商品、文章、物料等里筛选数据并且通常还要带上每个子分类的统计信息。这两个动作合在一起才是完整的动态子类别筛选。这篇文章以 PostgreSQL 为例用的例子是商品分类场景但思路完全可以平移到任何树形结构数据上——组织架构选人、文章分类检索、SKU维度筛选、BOM物料树展开套路都是一样的。内容覆盖数据模型选型、递归CTE写法、性能优化、API层设计、踩坑修复五块适合后端工程师、DBA、以及在做商城或ERP系统的同学参考。另外说一句热词里关于PostgreSQL安装的问题这里不展开讲不管你是用16还是17版本甚至便携版本文涉及的SQL语法和索引策略在这些版本上行为一致不需要担心版本差异。唯一建议是生产环境尽量用16及以上版本递归查询优化器在16里又有一些改进后面会提到。2. 数据模型选型为什么我最终留在邻接表加递归CTE2.1 四种树形存储方案的横向对比树形结构在关系型数据库里从来都不是一种答案常见的方案有四种邻接表Adjacency List、物化路径Materialized Path、闭包表Closure Table、以及PostgreSQL专有的 ltree 扩展。把它们的核心差异放一起看更直观。方案存储方式查子树难度插入/移动节点典型使用者邻接表每行存 parent_id需要递归CTE改一个字段即可大多数业务系统物化路径每行存 path如 /1/5/12/前缀匹配快改路径字符串要小心评论、分类树闭包表单独一张表存所有祖先-后代关系直接查关联表插入/删除都要维护多行权限树、多级分销ltree专门的树标签类型内置操作符类似物化路径PostgreSQL专用场景各有各的适用面。闭包表查询确实是三范式里最爽的一个 JOIN 就能拿到任意节点的全部后代但写入成本高每挂一个子节点要往闭包表插一批记录删除某个中间节点更是牵一发动全身。物化路径查询性能好但路径字符串的维护逻辑容易漏尤其是做把某个父分类整棵挪到另一个父分类下面这种操作时所有后代的 path 都要重写。ltree 很强但它是PostgreSQL的扩展换数据库就废了而且如果分类ID是业务主键而不是有序标签适配成本也不低。邻接表最大的优点是符合直觉categories 表里每个分类就一行parent_id 指向父亲看起来和业务模型一模一样。但它的劣势也明显——查子树必须递归。很多同学被递归慢这个刻板印象吓住了直接在应用层写循环一次查一层几十层分类要请求几十次数据库这才是真正的性能灾难。2.2 我为什么选择邻接表我最终选择邻接表加 PostgreSQL 递归CTE理由有三个。第一PostgreSQL 的WITH RECURSIVE对邻接表查询的支持成熟度非常高优化器能把递归部分和其他条件合并优化配合好索引几万分类节点、五到六层深度的情况下一次递归查询基本都在几十毫秒级别。第二业务系统的分类树低频写、高频读。商品分类几天甚至几周才调整一次而筛选接口每秒被调几十次。邻接表的写入成本本来就低再加一层物化视图或缓存做读优化整体结构最简单可靠。第三团队协作成本低。换一个人来接手看到 parent_id 立刻明白数据结构如果上闭包表新人第一次维护时大概率会漏插或重复插记录。这不是说其他方案一无是处。如果你的分类树深度固定且层级很少比如三级品类物化路径其实也够用如果你要做极其频繁的祖先-后代判定闭包表更合适。但就动态子类别筛选这个需求来说邻接表是性价比最高、最容易落地和排错的地基。3. 动态筛选择SQL的核心递归CTE的解剖与实战写法3.1 先搞定取全量子分类这个单元假设分类表结构如下CREATE TABLE categories ( id BIGSERIAL PRIMARY KEY, name VARCHAR(128) NOT NULL, parent_id BIGINT REFERENCES categories(id), sort_order INT NOT NULL DEFAULT 0, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); CREATE INDEX idx_categories_parent_id ON categories(parent_id);parent_id为 NULL 表示顶层分类。现在要查 ID 为 1001 这个父分类下所有层级的子分类标准写法是这样的WITH RECURSIVE category_tree AS ( -- 非递归部分先取出初始节点 SELECT id, parent_id, name, 1 AS depth, ARRAY[id] AS path FROM categories WHERE id 1001 UNION ALL -- 递归部分沿着 parent_id 往下钻 SELECT c.id, c.parent_id, c.name, ct.depth 1, ct.path || c.id FROM categories c JOIN category_tree ct ON c.parent_id ct.id WHERE NOT c.id ANY(ct.path) -- 防环保险 ) SELECT id, parent_id, name, depth FROM category_tree ORDER BY depth, sort_order;拆开解释一下。非递归部分负责播种递归部分负责一层层往下找孩子每一轮的结果都会重新喂给下一轮 JOIN。depth字段用来记录节点在第几层path数组存的是从根到当前节点的完整路径主要作用是防环——如果数据里有人手工把 parent_id 改成了循环引用没有这个条件递归CTE会无限循环直到数据库把内存吃光。这个path写法在实际生产中我强烈建议保留哪怕是正常数据。后面讲踩坑的时候我会展开说因为我在真实项目里确实遇到过循环引用导致的诡异故障。3.2 把子分类集合接上商品筛选上面拿到了子分类ID列表接下来要筛选商品。这一步最容易犯的错是把递归结果直接当子查询丢进IN代码变成这样SELECT * FROM products WHERE category_id IN ( WITH RECURSIVE category_tree AS ( ... ) SELECT id FROM category_tree );这样写不是不能跑但问题在于如果子分类特别多IN 后面的列表会非常长执行计划可能会变成逐行扫描而且整条 SQL 又长又难维护。更优雅的做法是让递归CTE先运算完再 JOIN 商品表WITH RECURSIVE category_tree AS ( SELECT id, parent_id, 1 AS depth, ARRAY[id] AS path FROM categories WHERE id 1001 UNION ALL SELECT c.id, c.parent_id, ct.depth 1, ct.path || c.id FROM categories c JOIN category_tree ct ON c.parent_id ct.id WHERE NOT c.id ANY(ct.path) ) SELECT p.id, p.name, p.price, p.category_id, ct.depth AS category_depth FROM products p JOIN category_tree ct ON p.category_id ct.id WHERE p.status on_sale ORDER BY p.created_at DESC LIMIT 20;把递归CTE当作一个动态生成的维度表再去 JOIN 业务数据执行计划里 JOIN 顺序由优化器决定配合商品表的category_id索引效果比 IN 子查询稳定很多。还有一个小技巧如果你只需要某个父分类下所有商品的数量可以这样写WITH RECURSIVE category_tree AS ( ... ) SELECT c.name AS category_name, COUNT(p.id) AS product_count FROM category_tree c LEFT JOIN products p ON p.category_id c.id GROUP BY c.id, c.name ORDER BY product_count DESC;这就把动态子类别和子类别各自的数量一次性查出来了前端做联动展示时不需要再发一次统计请求。3.3 用参数化实现真正的动态上面例子把分类ID写死成 1001实际接口里肯定要把它变成参数。PostgreSQL 支持$1这种参数占位WITH RECURSIVE category_tree AS ( SELECT id, parent_id, 1 AS depth, ARRAY[id] AS path FROM categories WHERE id $1 UNION ALL SELECT c.id, c.parent_id, ct.depth 1, ct.path || c.id FROM categories c JOIN category_tree ct ON c.parent_id ct.id WHERE NOT c.id ANY(ct.path) ) SELECT p.id, p.name, p.price, p.category_id, ct.depth AS category_depth FROM products p JOIN category_tree ct ON p.category_id ct.id WHERE p.status on_sale ORDER BY p.created_at DESC LIMIT 20 OFFSET $2;这里要说一个比较容易被忽略的细节LIMIT ... OFFSET ...的分页方式在商品量大、筛选范围大的时候有坑。OFFSET越大数据库要丢掉的行越多性能直线下降。如果帖子列表是按创建时间倒序的更稳妥的方案是用游标分页WHERE p.created_at $3 ORDER BY p.created_at DESC LIMIT 20;调用方把上一次返回结果里最小的created_at传进来下次翻页就从这里继续。这个模式在动态子类别筛选这种高频接口里非常实用能把深分页的性能问题彻底绕开。4. 性能优化从索引设计到物化路径升级4.1 索引策略怎么配合递归CTE递归部分最核心的查询条件是c.parent_id ct.idcategories(parent_id)索引是必须的上面建表时已经加上了。如果再叠加上业务条件比如只统计status active的订单但状态字段在订单表里那就要给订单表的category_id加联合索引。实际项目中我给商品表做的索引是这样的CREATE INDEX idx_products_category_status ON products(category_id, status); CREATE INDEX idx_products_category_created ON products(category_id, created_at DESC);第一个索引支撑某个分类下所有在售商品这种高频过滤第二个索引支撑按分类聚合后按时间排序的分页查询。两个索引的代价是有额外写入开销但商品表读多写少这个成本完全可以接受。递归CTE本身的预热问题值得提一下。每次请求新建独立的CTE、递归扫描分类表如果分类表几千行PostgreSQL会把整个表数据加载进缓存快到毫秒级。但并发一高循环反复扫描同一棵子树浪费明显。这时可以在应用层加一层短TTL缓存比如30秒把分类ID - 全量子分类ID数组这个映射缓存起来。这样SQL直接变成SELECT ... FROM products p JOIN unnest($1::bigint[]) AS cat_id ON p.category_id cat_id WHERE p.status on_sale;这个写法把递归从主查询中挪走了性能提升非常直观。对于分类基本不变、访问量大的场景我推荐优先做这层缓存——比所谓调参数靠谱得多。4.2 升级方案给表加冗余物化路径列如果递归CTE加上缓存后还是觉得每次都要算一遍不舒服还有一个更彻底的方案在分类表里直接加一列存物化路径。ALTER TABLE categories ADD COLUMN path BIGINT[]; -- 用递归CTE一次算完全表的路径 WITH RECURSIVE category_tree AS ( SELECT id, parent_id, ARRAY[id] AS path FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, ct.path || c.id FROM categories c JOIN category_tree ct ON c.parent_id ct.id ) UPDATE categories c SET path ct.path FROM category_tree ct WHERE c.id ct.id;有了path数组后查某个节点全部后代就变成数组前缀匹配SELECT * FROM categories WHERE path[:] $1; -- 数组前N个元素等于父分类路径配合 GIN 索引可以做到相当快的查询速度。这个方案的代价是以后每次增删分类都要同步维护path最常见做法是写触发器或者干脆在应用层事务里更新时同步修一下。我的建议是分类树规模小、更新频率低直接用邻接表加递归CTE就行如果分类规模到了十万级且要求极低延迟物化路径列值得考虑。多数系统的分类树撑不到这个量级不要为了不存在的性能焦虑过度设计。4.3 递归深度与死循环保护PostgreSQL 的WITH RECURSIVE没有内置递归深度上限一旦数据里有环查询就会一直执行下去。除了在递归部分加WHERE NOT c.id ANY(ct.path)之外还有两道保险措施。一个是在表里加CHECK (parent_id IS NULL OR parent_id id)从源头禁止自引用——只能防止自己挂自己防不了 A 挂 B、B 挂 A 这种环。另一个是在递归CTE里设深度上限把防环保险从逻辑层面再上一道锁WITH RECURSIVE category_tree AS ( SELECT id, parent_id, 1 AS depth, ARRAY[id] AS path FROM categories WHERE id $1 UNION ALL SELECT c.id, c.parent_id, ct.depth 1, ct.path || c.id FROM categories c JOIN category_tree ct ON c.parent_id ct.id WHERE NOT c.id ANY(ct.path) AND ct.depth 10 -- 业务上分类深度不可能超过10层 ) SELECT ...加上ct.depth 10之后即使在数据异常的情况下递归也会在10轮之内强行终止。随时随地想想这个查询最坏会跑多久是写递归SQL的基本素养。5. API层联动设计一个接口应对多级联动筛选5.1 动态筛选接口的输入输出设计拿到SQL之后接口侧的设计同样重要。很多项目把动态子类别筛选拆成两个接口一个查子分类树一个查商品列表前端先请求分类树再逐级请求商品。这样做的坏处是用户每次点击一个分类都要等着两次网络往返。做联动体验接口应当砍到一个。我实际用的接口设计是POST /api/categories/filter { category_id: 1001, depth: 0, // 0表示不限深度 page_size: 20, cursor: null, // 游标翻页时传上一次返回的 last_created_at keyword: // 可选关键字过滤 }返回结构{ categories: [ { id: 1002, name: 手机, parent_id: 1001, depth: 1, product_count: 356 }, { id: 1003, name: 笔记本电脑, parent_id: 1001, depth: 1, product_count: 128 } ], products: [ { id: 98765, name: 某品牌手机, price: 3999, category_id: 1002, created_at: 2024-12-01T10:00:00Z } ], last_created_at: 2024-12-01T10:00:00Z }categories里返回的是父分类的直接子分类和各自的商品数量products返回的是该父分类下所有层级子分类的商品分页结果。前端拿到这个返回值左侧展示子分类时只需要遍历categories右侧商品列表直接渲染products不需要再请求任何接口。这就是动态子类别筛选在服务端做和在前端拼的本质区别。5.2 用 JSONB 聚合把树拼出来如果前端不想自己用平铺列表拼树结构PostgreSQL 的jsonb_agg可以把递归结果直接聚合成 JSON 树。比如要返回整棵子分类树而不是只有直接子分类可以这样写WITH RECURSIVE category_tree AS ( SELECT id, parent_id, name, 1 AS depth, ARRAY[id] AS path FROM categories WHERE id $1 UNION ALL SELECT c.id, c.parent_id, c.name, ct.depth 1, ct.path || c.id FROM categories c JOIN category_tree ct ON c.parent_id ct.id WHERE NOT c.id ANY(ct.path) ), product_stats AS ( SELECT p.category_id, COUNT(*) AS cnt FROM products p JOIN category_tree ct ON p.category_id ct.id GROUP BY p.category_id ) SELECT jsonb_build_object( id, ct.id, name, ct.name, product_count, COALESCE(ps.cnt, 0), children, ( SELECT jsonb_agg(child) FROM ( SELECT ... FROM category_tree child WHERE child.parent_id ct.id ) child ) ) FROM category_tree ct LEFT JOIN product_stats ps ON ps.category_id ct.id WHERE ct.id $1;这种写法能直接在数据库里拼出嵌套的 JSON 树后端不用再递归构造对象。但要注意category_tree 里每一行都执行一次子查询来聚合 children当分类规模较大时嵌套子查询的开销不小。我的经验是分类树节点少于几百个时随便拼节点上了几千就老老实实返回平铺列表让前端拼或者用应用层代码组装树。数据库强的是集合运算不是遍历拼树。5.3 联动筛选的常见体验问题联动筛选有一个特别容易翻车的点数据权限范围。比如后台管理员可能只能看某些分类的数据如果接口直接按分类ID过滤一旦有人传了一个他无权访问的父分类ID数据就泄露了。解决方法是接口里把我能访问的分类ID集合和请求的父分类ID集合先求交集再进递归WITH RECURSIVE accessible_tree AS ( SELECT ... FROM categories WHERE id ANY($allowed_category_ids) AND id $1 ... )这个过滤器虽然看起来只是多了个条件但正是这种细节决定了系统能不能上生产。我见过不少项目在联调时才发现这个问题重构接口时还要动前端代价不小。6. 实际排查过的坑循环引用、错误计数与脏数据6.1 自引用循环导致的查询挂死有一次客户反馈后台某个分类一打开页面就转圈数据库CPU飙到100%。查慢查询日志发现有一条递归CTE跑了二十分钟还没结束。我手动执行了一下发现是有人把某个子分类的 parent_id 改成它自己了。由于递归条件里没有防环逻辑CTE每轮都能通过c.parent_id ct.id查到它自己循环往复直到把整个数据库的资源吃干。这也是一次深刻教训。在那之后我所有递归CTE都强制带上NOT c.id ANY(ct.path)这个条件。对绝大多数业务表防环代价几乎为零但能让最糟糕的异常数据只产生一次错误查询而不是一次灾难。6.2 统计数字对不上的灵异现象另一个常见问题左侧子分类数量显示手机有356件右侧商品列表翻完所有页只看到200件。排查后确认问题出在JOIN ... ON p.category_id ct.id时同一件商品挂在分类目录的多个层级下——比如手机既挂在数码产品/手机下又挂在全部商品/数码/手机下。分类和商品的多对多关联或者历史数据冗余都会造成重复计数。遇到这种需求首先要和业务方确认统计口径是商品在分类树上唯一归属还是商品可多挂在多个分类。如果允许一对多聚合前必须去重或者用递归CTE只取最小深度的分类作为归属分类。如果是一对一就要在数据库层面做校验防止业务写入脏数据。动态筛选本身不难难的是统计口径定义清楚。6.3 空分类、孤立节点与删除级联递归CTE的递归部分从初始节点往下找孩子如果某个父分类没有任何子分类category_tree 就只剩一个节点商品列表自然为空。这个没问题。真正容易踩的是父分类删了子分类还在。如果categories.parent_id设置了REFERENCES categories(id)外键删除父分类时数据库会报外键冲突保护数据不会变成孤立节点。但很多项目为了省事没加外键删除分类靠应用层代码一条条删。一旦漏删了某个子分类它就变成孤儿永远不出现在任何父分类的递归结果里但商品数据却还能查到它。这类脏数据的最优解是加外键约束加级联删除ALTER TABLE categories ADD CONSTRAINT fk_category_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE CASCADE;这样删父分类时数据库自动删掉所有后代分类。商品表那边要不要级联删要看业务——一般来说应该设置ON DELETE RESTRICT或者先让商品转移分类防止误删商品数据。6.4 临时表与递归CTE的异常排查技巧排查递归问题有一个很好用的手段把递归CTE单独抽出来不 JOIN 商品表直接看它返回多少行、每个节点的 depth 是否正确。比如WITH RECURSIVE category_tree AS ( ... ) SELECT depth, count(*) FROM category_tree GROUP BY depth ORDER BY depth;这个输出能让你一眼看出递归是不是按预期在逐层展开。如果 depth2 的节点数突然变成几千说明那里有个父分类挂了一个错误根节点属于典型的数据迁移事故——导入分类表时把顶层分类的 parent_id 设置成了某个已经存在的分类ID。7. 总结一点个人体会做动态子类别筛选这件事技术上本身不复杂复杂的是把数据模型、递归SQL、索引策略和接口设计串在一起并且预判到各种边界情况。我个人的习惯是先在分类表那一层把递归CTE调稳定再处理业务表关联最后才做接口封装。一旦哪一步结果对不上就单独跑其中一段SQL排查不要整条大SQL一把梭那样只会浪费时间。也分享一个收尾小技巧正式上线前往分类表里插入几条模拟的循环引用和孤儿节点数据再把动态筛选SQL跑一遍确认不会挂死、结果符合预期。这套脏数据演习每次都能帮我提前发现线上问题。树形数据结构的坑基本都是脏数据引入的提前做过抵抗测试比上线后救火舒服得多。