层次数据结构的数据表设计
存储树形结构数据是一个常见的问题,同时也有多种解决方案。
这里介绍三种树形结构的表设计方案:
- 邻接表模型
- 基于路径和层级的表设计
- 基于左右值编码的表设计(MPT)
这里以一个在线食品店作为例子,食品通过类别、颜色和品种组织食品。
示例如下:
一、邻接表模型
最简单的方法就是使用邻接表模型或者叫做递归模型。通过显示地描述某一节点的父节点,从而能够建立二维的关系表,你只需要一个简单的函数去迭代查询即可获取你的数据。
示例如下: 优点:
- 设计简单
- 实现容易
- 直观
缺点:
- 由于是递归模型CRUD操作低效
二、基于路径和层级的表设计
在一的基础上加上一个 level 字段来表示当前节点到根节点的距离和一个 key 字段来表示搜索路径。
- Node_id 主键
- Name 名字
- Parent_id 父节点的id
- key 搜索路径
- level 表示当前节点到根节点的距离或者层级
示例如下: 两种需求查询解决方案如下:
- 查找d的所有子孙节点:
select * from table_name where key like "${d.key}-${d.id}-%"
- 查找某个节点的所有子节点:
select * from table_name where key like "${d.key}-${d.id}-%" and level=${d.level}+1
此设计结构简单,利用key和level两个辅助字段可以完成查询www.rsxedu.com操作比一更加高效,而且维护这两个字段成本很低。
三、基于左右值编码的表设计(MPT)
为了避免对于树形结构查询时的“递归”过程,基于Tree的前序遍历设计一种全新的无递归查询、无限分组的左右值编码方案,来保存该树的数据。 如下图所示, 从根节点Food左侧开始,标记为1,并沿前序遍历的方向,依次在遍历的路径上标注数字,最后我们回到了根节点Food,并在右边写上了18。
注:lft 和 rgt 分别对应 DFS 的发现时间 d 和完成时间相同 f 如果我们需要查询Fruit的后续节点,只需找出所有左值大于2,并且右值小于11的节点即可。
1. 获取某节点的子孙节点
返回某节点子孙节点的前序遍历列表,以Fruit为例:
SQL: SELECT* FROM Tree WHERE Lft BETWEEN 2 AND 11 ORDER BY Lft ASC
查询结果如下: 那么某个节点到底有多少的子孙节点呢?
通过该节点的左、右值我们可以将其子孙节点圈www.meimeitu8.com进来,则子孙总数 = (右值 – 左值– 1) / 2,以Fruit为例,其子孙总数为:(11 –2 – 1) / 2 = 4。
同时,为了更为直观地展现树形结构,我们需要知道节点在树中所处的层次,通过左、右值的SQL查询即可实现。以Fruit为例:SELECT COUNT(*) FROM Tree WHERE Lft <= 2 AND Rgt >=11
。为了方便描述,我们可以为Tree建立一个视图,添加一个层次数列,该列数值可以写一个自定义函数来计算,函数定义如下:
CREATE FUNCTION dbo.CountLayer
(
@node_id int
)
RETURNS int
AS
begin
declare @result int
set @result = 0
declare @lft int
declare @rgt int
if exists(select Node_id from Tree where Node_id = @node_id)
begin
select @lft = Lft, @rgt = Rgt from Tree where node_id = @node_id
select @result = count(*) from Tree where Lft <= @lft and Rgt >= @rgt
end
return @result
end
GO
基于层次计算函数,我们创建一个视图,添加了新的记录节点层次的数列:
CREATE VIEW dbo.TreeView
AS
SELECT Node_id, Name, Lft, Rgt, dbo.CountLayer(Node_id) AS Layer FROM dbo.Tree ORDER BY Lft
GO
创建存储过程,用于计算给定节点的所有子孙节点及相应的层次:
CREATE PROCEDURE [dbo].[GetChildrenNodeList]
(
@node_id int
)
AS
declare @lft int
declare @rgt int
if exists(select Node_id from Tree where node_id = @node_id)
begin
select @lft = Lft, @rgt = Rgt from Tree where Node_id = @node_id
select * from TreeView where Lft between @lft and @rgt order by Lft ASC
end
GO
现在,我们使用上面的存储过程来计算节点Fruit所有子孙节点及对应层次,查询结果如下: 在进行树的查询遍历时,只需要进行2次数据库查询,消除了递归,再加上查询条件都是数字的比较,查询的效率是极高的,随着树规模的不断扩大,基于左右值编码的设计方案将比传统的递归方案查询效率提高更多。
2. 获取某节点的族谱路径
假定我们要获得某节点的族谱路径,则根据左、右值分析只需要一条SQL语句即可完成。
以Fruit为例:SELECT* FROM Tree WHERE Lft < 2 AND Rgt > 11 ORDER BY Lft ASC
相对完整的存储过程如下:
CREATE PROCEDURE [dbo].[GetParentNodePath]
(
@node_id int
)
AS
declare @lft int
declare @rgt int
if exists(select Node_id from Tree where Node_id = @node_id)
begin
select @lft = Lft, @rgt = Rgt from Tree where Node_id = @node_id
select * from TreeView where Lft < @lft and Rgt > @rgt order by Lft ASC
end
GO
3. 为某节点添加子孙节点
假定我们要在节点“Red”下添加一个新的子节点“Apple”,该树将变成如下图所示,其中红色节点为新增节点。 相对完整的插入子节点的存储过程:
CREATE PROCEDURE [dbo].[AddSubNode]
(
@node_id int,
@node_name varchar(50)
)
AS
declare @rgt int
if exists(select Node_id from Tree where Node_id = @node_id)
begin
SET XACT_ABORT ON
BEGIN TRANSCTION
select @rgt = Rgt from Tree where Node_id = @node_id
update Tree set Rgt = Rgt + 2 where Rgt >= @rgt
update Tree set Lft = Lft + 2 where Lft >= @rgt
insert into Tree(Name, Lft, Rgt) values(@node_name, @rgt, @rgt + 1)
COMMIT TRANSACTION
SET XACT_ABORT OFF
end
GO
4. 删除某节点
如果我们想要删除某个节点,会同时删除该节点的所有子孙节点,而这些被删除的节点的个数为:(被删除节点的右值 – 被删除节点的左值+ 1) / 2,而剩下的节点左、右值在大于被删除节点左、右值的情况下会进行调整。来看看树会发生什么变化,以Beef为例,删除效果如下图所示。 则我们可以构造出相应的存储过程:
CREATE PROCEDURE [dbo].[DelNode]
(
@node_id int
)
AS
declare @lft int
declare @rgt int
if exists(select Node_id from Tree where Node_id = @node_id)
begin
SET XACT_ABORT ON
BEGIN TRANSCTION
select @lft = Lft, @rgt = Rgt from Tree where Node_id = @node_id
delete from Tree where Lft >= @lft and Rgt <= @rgt
update Tree set Lft = Lft – (@rgt - @lft + 1) where Lft > @lft
update Tree set Rgt = Rgt – (@rgt - @lft + 1) where Rgt > @rgt
COMMIT TRANSACTION
SET XACT_ABORT OFF
end
GO
小结
优点:
- 消除了递归查询,实现了无限嵌套
- 查询是基于整数的比较,效率很高
缺点:
- 节点的添加、删除及修改代价较大
在基于数据库的一般应用中,查询的需求总要大于删除和修改,同时我们可以扩展MPT来实现更多的优化,例如:如果对层级需求较高,可以结合MPT和二中的方法来实现。
Reference
层次数据结构的数据表设计:https://juejin.cn/post/6844903861413478407#heading-7
树形结构的数据库表Schema设计:https://blog.csdn.net/monkey_d_meng/article/details/6647488