树形结构的各个存取方案对比--《sql反模式》

问题

需要存储树型结构的数据, 比如存储公司组织架构, 或论坛的评论区. 如何设计库表

下面提供多种方案并分析各方案的优缺点

1. 邻接表
2. 递归查找
3. 路径枚举
4. 嵌套集
5. 闭包集

(具体的库表方案需要结合具体业务 , 充分考虑各个方案的优缺点后选择 , 没有万能的方案 , 也不要过度设计)

邻接表

简介

最容易想到的方式就是邻接表了(单表, 每行存储一个parentId外键).
(因为节点与父节点属于多对一关系, 所以在多的一方存储外键).
表结构如下:(tree表)

id	node_info	parent_id
1	根节点…	0
2	第一层节点…	1

优势

1. 库表结构简单直观
2. 查询直接父节点或直接子节点简单(inner join 或 子查询等)
3. 插入单个节点简单(只需设置新节点的parentId, 并更新子节点的parentId)

劣势

1. 查询某节点的 子树 麻烦. 比如想一次性看到某部门的所有子部门
2. 查询某节点的 所有父节点 麻烦
3. 删除 子树 麻烦, 比如想删除某评论以及该评论延伸的所有子评论(有点绕)

邻接表适用于层次比较少且基本不扩展的树型结构.(比如省/市/县的存取)

递归查询

简介

递归查询就是在邻接表的基础上改良的(解决劣势问题).
邻接表 在查询节点的子树或节点的所有父节点麻烦, 那么 递归查询 的做法就是创建DB的函数实现递归查找.
下图是查询某个节点的所有孩子节点的DB函数(查询节点的所有父节点类似)

优势

1. 库表结构简单直观
2. 查询直接父节点或直接子节点简单
3. 代码层面简洁(dao层调用简单)

劣势

1. DB函数不直观
2. 业务逻辑迁移到DB , 调试定位问题不方便

路径枚举

简介

(用的比较多)
存储跟节点到当前节点的完整路径.比如下面这棵树:

id	path
1	1/
2	1/2/
3	1/3/
4	1/2/4/
5	1/2/5/

查询
比如要查询2号节点的所有子节点:

select * from tree where path like '1/2/' || '%'; // (最左前缀匹配)

这样, 就能搜索到1/2/4/ , 1/2/5/路径的节点;

要查找4号节点的所有父节点:

select * from tree where '1/2/4/' like path || '%';

这样, 就能匹配到1/2/, 1/ 这几个路径的节点;

要查找某个节点的下两层子节点, 只需在上面两个sql基础上添加过滤条件即可.

select * from tree where ( path like '1/2/' || '%') and (LENGTH(path) - LENGTH( REPLACE(path,'/','')) = 4);

注: (LENGTH(path) - LENGTH( REPLACE(path,’/’,’’)) = 4) 是判断path的层次高度=4.
mysql没有直接查询某个字符在字符串中出现的次数的.囧…

删除
删除叶子节点只需要按照查询方式定位到节点并删除即可.
删除非叶子节点时, 除了要删除删除对应的节点, 还需要更新该节点的所有子孙节点的路径, 使用mysql的replace函数即可;
(业务场景:比如要在公司内解散某个中间部门实现扁平化管理)

新增
在某个节点下面插入叶子节点
思路:在父节点路径基础上添加自身路径作为新节点的路径
如果插入的是中间节点, 除了需要插入当前节点外, 还需要修改子节点的路径.(mysql的replace函数)

优势

1. 可以快速查找到节点的父节点/子节点或进行修改删除
2. 插入(叶子/非叶子)节点简单

劣势

1. 需要业务代码维护路径, 没法设置外键约束
2. path的字段长度不好设置

嵌套集

(很少用到, 比较复杂)

简介

其他方案的节点与节点之间都是强关联的, 比如邻接表直接存储了parentId, 路径枚举的路径直接包含了parent的id. 但嵌套集的节点与节点之间属于弱关联关系;
每个节点不仅包含节点自身信息, 还包含节点的左右脚(left, right) 来表示该节点的嵌套范围.(节点的id和左右脚编号没有关联)
例如:
下面的库表的记录. 1号节点不仅包含自身信息, 还包含nsleft和nsright字段信息.
所以1号节点的区间是1~14.其他节点的左右脚在这个范围内都属于1号节点的子节点.比如2号节点的2 ~ 5就被嵌套了.(这是查询子孙节点的思路)

同理, 查询父节点只需要查找哪些节点包含了当前节点的左右脚 . 比如5号节点是7~8, 那么嵌套了这个范围的节点有1 ~14 , 6 ~ 13. 也就是1号节点和4号节点.(查询祖先节点的思路)
(当然, 不存在只嵌套一个脚的情况, 因为左右脚的编号采用深度遍历的算法).

树型结构如下:

库表如下:

优势

1. 查询所有孩子节点或所有祖先节点简单.(按上面的嵌套就行)
2. 删除单个节点快 且 不断层(就是删除当前节点时, 该节点的子孙节点会自动成为当前节点的父节点的子节点…有点绕)

//比如查询节点的所有后代:
select 
	c2.*
from 
	Comment as c1 join Comment as c2 
	on (c2.nsleft between c1.nsleft and c1.nsright )
where 
	c1.id = 4;

//查询某节点的所有祖先:
select 
	c2.*
from 
	Comment as c1 join Comment as c2 
	on (c1.nsleft between c2.nsleft and c2.nsright)
where c1.id = 7;

劣势

1. 查询直接父节点/直接子节点困难
   因为节点与父节点没有强关联, 都是通过嵌套来确定祖先后代节点的.
   如果需要找到直接父节点(直接子节点同理), 思路:
   找到当前节点的所有父节点, 然后假定父节点中的某个节点与当前节点中间已经没有其他节点了. 那么该节点就是当前节点的直接父节点.
   

2. 插入和移动节点复杂(涉及左右脚的编号调整)
   插入和移动节点时,涉及到左右脚编号的重新排序, 略复杂. 讲下思路:
   比如要在3号节点下面插入一个节点, 最直观的思路就是让3号节点的左右脚从3~4变成3 ~6, 这样3号节点下面就可以放一个节点, 新节点的左右脚为4 ~5; 可以看到需要调整3号节点的左右脚, 但是因为整个树的左右脚都是通过深度遍历来编号的. 因此, 需要调整涉及到的节点的左右脚.(这里需要调整整棵树的左右脚编号.更新的行数会比较多).
   

闭包集

简介

闭包, 顾名思义, 存储了两个相关联节点的所有路径.将有关联的关系都存储起来 , 跟上面的所有方案不同, 闭包集有两个表, 一个表存储每个节点的基础信息 , 另外一个表存储关联关系.(有些关联关系表还会为每个路径带上该路径的高度)
(关联关系表的数据量增长很快, 慎重)

下面主要讨论关联关系表.
(闭包: 比如A->B,B->C , 那么闭包集为:{A->B, A->C,B->C}).

关联关系表:(树型图的所有关系都会存储到下面这个表里)
表名:TreePaths

ancestor	descendant	height
1	1	0
1	2	1
1	3	2
…	…

优势

1. 查询/更新 快
   如查询某节点的高度为2的子节点id

select 
	descendant 
from 
	TreePaths
where 
	ancestor = xx and height = 2;

查询某节点的高度为2的父节点id.(只有一个):

select 
	ancestor 
from 
	TreePaths 
where 
	descendant = xx and height = 2;

(查询所有父节点id或者所有子节点id就将height条件去掉就行.)
(需要获取节点的基础信息再通过上面的查询结果去查询基础信息表即可. 生产环境很少进行join操作)

2. 插入快
   要在某个节点下面插入叶子节点, 只需要将指向父节点的所有节点都指向一遍新的节点(再加上自己指向自己).
   比如5号节点下面插入一个子节点, 首先插入一条自己指向自己的关系, 然后搜索TreePaths表的后代是5的节点, 增加该节点和新节点的"祖先-后代" 关系:
   
   如果是插入中间节点, 除了需要像插入叶子节点那样操作之外, 还需要让新节点指向所有孩子节点.(闭包)

3. 删除
   要删除某个节点, 只需要删除该节点指向孩子节点的路径 并且删除所有父节点指向该节点的路径即可.

总结

上面的所有方案的优缺点浓缩到下面这个表中:
(如果层次结构比较简单可以直接使用邻接表, 复杂些的可以使用枚举路径/路径枚举. 嵌套集比较复杂, 闭包集在增加节点时关联关系表的数据量增长速度很快.)