乐趣区

关于mysql:层次数据结构的数据表设计

jiezi

档次数据结构的数据表设计

存储树形构造数据是一个常见的问题，同时也有多种解决方案。

这里介绍三种树形构造的表设计方案：

  • 邻接表模型
  • 基于门路和层级的表设计
  • 基于左右值编码的表设计（MPT）

这里以一个在线食品店作为例子，食品通过类别、色彩和种类组织食品。

示例如下：

一、邻接表模型

最简略的办法就是应用邻接表模型或者叫做递归模型。通过显示地形容某一节点的父节点，从而可能建设二维的关系表，你只须要一个简略的函数去迭代查问即可获取你的数据。

示例如下：

长处：

  • 设计简略
  • 实现容易
  • 直观

毛病：

  • 因为是递归模型 CRUD 操作低效

二、基于门路和层级的表设计

在一的根底上加上一个 level 字段来示意以后节点到根节点的间隔和一个 key 字段来示意搜寻门路。

  1. Node_id 主键
  2. Name 名字
  3. Parent_id 父节点的 id
  4. key 搜寻门路
  5. level 示意以后节点到根节点的间隔或者层级

示例如下：

两种需要查问解决方案如下：

  1. 查找 d 的所有子孙节点：select * from table_name where key like "${d.key}-${d.id}-%"
  2. 查找某个节点的所有子节点：select * from table_name where key like "${d.key}-${d.id}-%" and level=${d.level}+1

此设计构造简略，利用 key 和 level 两个辅助字段能够实现查问操作比一更加高效，而且保护这两个字段老本很低。

三、基于左右值编码的表设计 (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

查问后果如下：

那么某个节点到底有多少的子孙节点呢？

通过该节点的左、右值咱们能够将其子孙节点圈进来，则子孙总数 = (右值 – 左值– 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/684490…

树形构造的数据库表 Schema 设计:https://blog.csdn.net/monkey_…

退出移动版