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