树状结构数据的数据库表设计及使用 - 1. 邻接表

最新推荐文章于 2024-06-08 21:19:26 发布

菠萝y

最新推荐文章于 2024-06-08 21:19:26 发布

阅读量6.8k

点赞数 1

分类专栏：算法 mysql 数据结构与算法文章标签：数据库 java 算法

原文链接：https://www.modb.pro/db/378603

版权

数据结构与算法同时被 3 个专栏收录

31 篇文章 1 订阅

订阅专栏

mysql

23 篇文章 3 订阅

订阅专栏

算法

17 篇文章 2 订阅

订阅专栏

本文以 MySQL 为例。文档比较长，故分为5部分发出：

📝 邻接表（Adjacency List）模型

🎨 路径枚举（Path Enumeration）模型

🧑‍💻 闭包表（Closure Table）模型

🤹 嵌套集（Nested Set）模型

🧮 性能比较与分析

树状结构或层次结构的数据在企业应用里非常常见，例如公司的组织架构、文档库的目录结构、仓库的库位组织以及物件的分类等等。

通常的树状图是一种数据结构。把它叫做“树”是因为它看起来像一棵倒挂的树，也就是说它是根朝上，而叶朝下的。

它具有以下的特点：每个结点有零个或多个子结点；没有父结点的结点称为根结点；每一个非根结点有且只有一个父结点；除了根结点外，每个子结点可以分为多个不相交的子树。

树结构是一种非线性存储结构，存储的是具有“一对多”关系的数据元素的集合。

邻接表模型

在设计树状结构的数据库表的时候，大部分开发者会下意识的选择邻接表（Adjacency List）模型。例如：
在这里插入图片描述

CREATE TABLE category(

        category_id INT AUTO_INCREMENT PRIMARY KEY,

        name VARCHAR(20) NOT NULL,

        parent INT DEFAULT NULL

);



INSERT INTO category VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),

        (4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),(7,'MP3 PLAYERS',6),(8,'FLASH',7),

        (9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);



SELECT * FROM category ORDER BY category_id;

+-------------+----------------------+--------+

| category_id | name                 | parent |

+-------------+----------------------+--------+

|           1 | ELECTRONICS          |   NULL |

|           2 | TELEVISIONS          |      1 |

|           3 | TUBE                 |      2 |

|           4 | LCD                  |      2 |

|           5 | PLASMA               |      2 |

|           6 | PORTABLE ELECTRONICS |      1 |

|           7 | MP3 PLAYERS          |      6 |

|           8 | FLASH                |      7 |

|           9 | CD PLAYERS           |      6 |

|          10 | 2 WAY RADIOS         |      6 |

+-------------+----------------------+--------+

在上面的分类表里，我们定义了分类ID（category_id
）、分类名称（name
）以及父分类ID（parent
）三个字段。

这里有一个真相是，上面这种简单的邻接表模型不是一种归一化（Nomalized）的结构。归一化的简短定义是，所有数据冗余都已被删除，并且不会出现数据异常。在一个归一化的数据模型里，数据应该是“一事、一地、一次”（one simple fact, in one place, one time），即一件事只在一处记录一次，以及一次只在一处记录一件事。

归一化表的第一个特征是只记录一件事，而前面的做法是在一个表里既记录了分类的名称，也记录了分类的层级关系，是一个混合对象。正确的做法是用两个表，一个记录分类的各种属性，一个记录分类之间的从属关系。

归一化表的第二个特征是每个事实都出现在“一地”（即，它属于一个表的一行），但是邻接表每个节点的子树可以位于多行中。归一化表第三个特征是每个事实在架构中出现“一次”（即，希望避免数据冗余）。如果同时违反了这两个条件，我们可能遇到异常。

下面列举一些由邻接表模型产生的非归一化行为。

比如新建分类和更改分类，如果不小心写错了 parent，那么很容易构造一个环形从属关系：

INSERT INTO category VALUES(11,'TV123',3);

UPDATE category SET parent=11 WHERE name='TUBE';

这里 TV123 和 TUBE 就互为父分类了。

或者：

INSERT INTO category VALUES(12,'TV456',12);

这里 TV456 为自己的父分类了。

另外简单邻接表模型不支持从属关系的继承，删除某行会将树拆分为几个较小的树，例如：

DELETE FROM category WHERE name='PORTABLE ELECTRONICS';

在这里插入图片描述最后，我们需要保留表中的树结构。我们需要确保结构中只有一个 NULL，而简单的邻接表模型不能防止多个 NULL 或循环从属关系。问题在于邻接表模型实际上可以是任何图形，而树是图形的特殊情况，因此我们需要对邻接表模型加上限制条件，以确保只有一棵树。

获取邻接表全树

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4

FROM category AS t1

LEFT JOIN category AS t2 ON t2.parent = t1.category_id

LEFT JOIN category AS t3 ON t3.parent = t2.category_id

LEFT JOIN category AS t4 ON t4.parent = t3.category_id

WHERE t1.name = 'ELECTRONICS';



+-------------+----------------------+--------------+-------+

| lev1        | lev2                 | lev3         | lev4  |

+-------------+----------------------+--------------+-------+

| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |

| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |

| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |

| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |

| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |

| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |

+-------------+----------------------+--------------+-------+

6 rows in set (0.00 sec)

以上 SQL 通过自连接（self-join）实现了全树的信息获取。这种方法的弊病很明显，有多少层，就得套多少自连接，这在旧版的 MySQL 中是唯一的办法，因为它不支持递归。

在 Oracle 中可以通过 connect by
语法来实现递归查询，在 MySQL 8 中使用 SQL-99 标准的 CTE (common table expression) 语法里的 RECURSIVE
来实现递归：

WITH RECURSIVE T1(category_id,name,parent) AS (

SELECT * FROM category T0 WHERE

    T0.parent IS NULL    -- ANCHOR MEMBER

UNION ALL

SELECT T2.category_id,T2.name,T2.parent FROM category T2, T1  -- RECURSIVE MEMBER

  WHERE T2.parent = T1.category_id

)

SELECT * FROM T1;

+-------------+----------------------+--------+

| category_id | name                 | parent |

+-------------+----------------------+--------+

|           1 | ELECTRONICS          |   NULL |

|           2 | TELEVISIONS          |      1 |

|           6 | PORTABLE ELECTRONICS |      1 |

|           3 | TUBE                 |      2 |

|           4 | LCD                  |      2 |

|           5 | PLASMA               |      2 |

|           7 | MP3 PLAYERS          |      6 |

|           9 | CD PLAYERS           |      6 |

|          10 | 2 WAY RADIOS         |      6 |

|           8 | FLASH                |      7 |

+-------------+----------------------+--------+

递归执行过程如下：

1.查找 parent IS NULL 的第一种类别，我们可以得到 ELECTRONICS；

2.接着查找 parent = ELECTRONICS 的第二类电器种类，可以看出我们可以得到 TELEVISIONS 和 PORTABLE ELECTRONICS；

3.接着查找 parent = TELEVISIONS 和 parent = PORTABLE ELECTRONICS，我们可以得到第三类电器分别是 PLASMA，MP3 PLAYERS，CD PLAYERS，2 WAY RADIOS，TUBE，LCD；

4.接着继续查找属于第三类电器种类的产品，最后得到 FLASH；

5.执行完毕。

众所周知递归的效率是比较低的，递归查询的问题是随着数据量的增加、层级的增加，递归的嵌套层数也会增加，所以到后面我们会不得不对这些查询进行优化。

获取邻接表子树

WITH RECURSIVE T1 AS (

SELECT * FROM category T0 WHERE

    T0.name = 'TELEVISIONS'    -- ANCHOR MEMBER

UNION ALL

SELECT T2.category_id,T2.name,T2.parent FROM category T2, T1  -- RECURSIVE MEMBER

  WHERE T2.parent = T1.category_id

)

SELECT * FROM T1;

+-------------+-------------+--------+

| category_id | name        | parent |

+-------------+-------------+--------+

|           2 | TELEVISIONS |      1 |

|           3 | TUBE        |      2 |

|           4 | LCD         |      2 |

|           5 | PLASMA      |      2 |

+-------------+-------------+--------+

通过 MySQL 8 下使用 CTE 递归查询。使用自连接的方式这里就不赘述了，与全树类似。

获取邻接表叶节点

SELECT t1.name FROM category AS t1 

LEFT JOIN category as t2

ON t1.category_id = t2.parent

WHERE t2.category_id IS NULL;

+--------------+

| name         |

+--------------+

| TUBE         |

| LCD          |

| PLASMA       |

| FLASH        |

| CD PLAYERS   |

| 2 WAY RADIOS |

+--------------+

以上通过判断没有子节点获取所有叶子节点。

获取邻接表完整单路径

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4

FROM category AS t1

LEFT JOIN category AS t2 ON t2.parent = t1.category_id

LEFT JOIN category AS t3 ON t3.parent = t2.category_id

LEFT JOIN category AS t4 ON t4.parent = t3.category_id

WHERE t4.name = 'FLASH';



+-------------+----------------------+-------------+-------+

| lev1        | lev2                 | lev3        | lev4  |

+-------------+----------------------+-------------+-------+

| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |

+-------------+----------------------+-------------+-------+

以上通过自连接获取一条完整路径。

这种方法的主要限制是，层次结构中的每个级别都需要一个自连接，并且随着连接复杂性的增加，每个级别的添加都会降低性能。

在 MySQL 8 以后同样使用 CTE 来递归查找：

WITH RECURSIVE T1(category_id,name,parent) AS (

SELECT * FROM category T0 WHERE

    T0.name = 'FLASH'   -- ANCHOR MEMBER

UNION ALL

SELECT T2.category_id,T2.name,T2.parent FROM category T2, T1  -- RECURSIVE MEMBER

  WHERE T2.category_id = T1.parent

)

SELECT * FROM T1;

+-------------+----------------------+--------+

| category_id | name                 | parent |

+-------------+----------------------+--------+

|           8 | FLASH                |      7 |

|           7 | MP3 PLAYERS          |      6 |

|           6 | PORTABLE ELECTRONICS |      1 |

|           1 | ELECTRONICS          |   NULL |

+-------------+----------------------+--------+

添加节点

邻接表里添加节点比较方便，直接 Insert 一条记录即可，只需要注意父节点要设置正确。

删除节点

删除叶节点，即没有子节点的节点是很简单的，直接 Delete 即可。但是如果要删除中间节点，为了防止出现孤立子树，我们需要确定删除中间节点后其原有的子节点如何处理：

一种方式是直接找到删除的中间节点的原父节点，让它成为其子节点的新父节点，即所谓爷爷收养孙子；

一种是提升某一个子节点（所谓长子）为新的父节点，将其它子节点的父节点重新指向这个新父节点，所谓父业子承；

还有一种就是把相关的中间节点的子树全部删掉，这就是灭门了……

删除子树

删除邻接表的一个子树，一般是知道父节点，然后递归找到所有子节点并一一删除。这也能通过一个 ON DELETE CASCADE 级联删除的外键约束来自动完成这个过程。

WITH RECURSIVE T1 AS (

SELECT * FROM category T0 WHERE

  T0.name = 'TELEVISIONS'    -- ANCHOR MEMBER

UNION ALL

SELECT T2.category_id,T2.name,T2.parent FROM category T2, T1  -- RECURSIVE MEMBER

 WHERE T2.parent = T1.category_id

)

DELETE FROM category WHERE category_id IN (SELECT category_id FROM T1);

总结

在纯 SQL 中使用邻接表模型是比较直观，但隐含一些困难的。我们需要给邻接表加上限制以防止前面描述到的问题，其中一些限制可以通过使用客户端代码或存储过程来解决。由于需要用到递归来实现子节点的查询，对于数据量比较大的树，其查询效率会比较低。

转：
树状结构数据的数据库表设计及使用 - 1. 邻接表

菠萝y

关注

1
点赞
踩
26

收藏

觉得还不错? 一键收藏
0
评论
树状结构数据的数据库表设计及使用 - 1. 邻接表

树状结构或层次结构的数据在企业应用里非常常见，例如公司的组织架构、文档库的目录结构、仓库的库位组织以及物件的分类等等。通常的树状图是一种数据结构。把它叫做“树”是因为它看起来像一棵倒挂的树，也就是说它是根朝上，而叶朝下的。它具有以下的特点：每个结点有零个或多个子结点；没有父结点的结点称为根结点；每一个非根结点有且只有一个父结点；除了根结点外，每个子结点可以分为多个不相交的子树。树结构是一种非线性存储结构，存储的是具有“一对多”关系的数据元素的集合。在设计树状结构的数据库表的时候，大部分开发者会下意识的选择邻
复制链接

扫一扫

专栏目录