01 前言
最近在做亚马逊类目Top版跟卖的事情, 发现亚马逊的类目是一棵树,层级不是很固定, 每点击类目下转,会有新的类目展开。
这种层级结构我们在生活中非常常见,比如我们企业的通讯录、部门组织架构、电脑上的文件夹结构等等。只要有层级结构,我们一般都喜欢用树形方式展示。树形结构我们在数据结构课程中经常会遇到,在内存中我们一般是用以下方式表达树形结构(以二叉树为例):
static class Node {
Node rightNode;
Node leftNode;
String data;
}
需要将树形结构存储在磁盘上,方便后续增删改读, 在实际的系统中,我们经常使用关系数据库比如mysql、sqlserver、postgresql、oracle等进行数据存储, 关系数据库数据一般都是扁平化的,跟树递归结构完全不一样。今天我们就一起探讨一下当关系型数据库遇到树结构,我们应当如何设计数据库。
02 常见的数据库设计模式
通过大量的资料查阅, 以下是我们常见关系数据库如何存储树结构:
第一种方案:邻接表(Adjacency List)
简单的说是根据节点之间的继承关系,显现的描述某一节点的父节点,从而建立二位的关系表。结构简单易懂,由于互相之间的关系只由一个parent_id维护:
CREATE TABLE `Food`
( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(200) NULL DEFAULT NULL COMMENT '名称',
`parent_id` int(11) NULL DEFAULT NULL COMMENT '父id',
PRIMARY KEY (`id`) USING BTREE
)
第二种方案:路径表(Path Enumeration)
物化路径其实更加容易理解,其实就是在创建节点时,将节点的完整路径进行记录,此种方案借助了unix文件目录的思想,主要以空间换时间。
CREATE TABLE `Food`
( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(200) NULL DEFAULT NULL COMMENT '名称',
`path` varchar(200) NULL DEFAULT NULL COMMENT '路径',
PRIMARY KEY (`id`) USING BTREE
)
第三种方案:左右值编码
在基于数据库的一般应用中,查询的需求总要大于删除和修改。为了避免对于树形结构查询时的“递归”过程,基于Tree的前序遍历设计一种全新的无递归查询、无限分组的左右值编码方案,来保存该树的数据。看见这种表结构,相信大部分人都不清楚左值(Lft)和右值(Rgt)是如何计算出来的,而且这种表设计似乎并没有保存父子节点的继承关系。其实它的左右值对应的是整个树的前序遍历的顺序。
要求开发人员对树形结构了然于胸。我们可以推断出所有左值大于1,并且右值小于18的节点都是Food的后续节点,以此类推所有左值大于12,并且右值小于17的节点都是Meat的后续节点, 整棵树的结构通过左值和右值存储了下来。
方案四 :闭包表(ClosureTable)
之前的方案中,都是对原有的记录添加列,然后对新增的列进行查询获取父子节点信息关系。而ClosureTable则是新增一张表,用于记录节点的关系(父节点,子节点,深度),闭包表的思路和路径表方案差不多,都是空间换时间,Closure Table,一种更为彻底的全路径结构,分别记录路径上相关结点的全展开形式。能明晰任意两结点关系而无须多余查询,级联删除和结点移动也很方便。但是它的存储开销会大一些,除了表示结点的Meta信息,还需要一张专用的关系表。它的表结构主要由最关键的3个字段组成:
字段名称 | 字段含义 |
| 祖先:上级节点的id |
| 子代:下级节点的id |
| 距离:子代到祖先中间隔了几级 |
依然以上面图为例,我们有节点表:
对应的闭包表是:
这三个字段的组合是唯一的,因为在树中,一条路径可以标识一个节点,所以可以直接把它们的组合作为主键。
03 传统方案总结
方案名称 | 优点 | 缺点 | 适用场景 |
邻接表 | (1)结构简单易懂。 (2)叶子节点的增删改都是非常容易,只需要改动和他直接相关的记录就可以。 (3)只是获取上下级的关系非常简单。(比如树都是一层级一层级点开查询)。 | (1)相对,如果要查询整个树或者叶子节点需要递归”操作,递归过程不断地访问数据库,每次数据库IO都会有时间开销。 (2)删除中间节点时比较复杂,你需要先把被删节点的子树查出来删除。 | 树的层级比较少的时候就非常实用。这种方法的优点是存储的信息少,查直接上司和直接下属的时候很方便,缺点是多级查询的时候很费劲。所以当只需要用到直接上下级关系的时候,用这种方法还是不错的,可以节省很多空间。 |
路径表 | (1)更新数据是比较方便快捷的,添加数据时直接找准路径就好。 (2)组织部门变更时,也直接找准路径就好,直接删除。至于子节点是否删,看自己的业务需求。 (3)层级数量是确定的,可以再将所有的列都展开,比较适用于于类似行政区划、生物分类法(界、门、纲、目、科、属、种)这些层级确定的内容。 | (1)树的层级太深有可能会超过PATH字段的长度,所以其能支持的最大深度并非无限的。 (2)通过path这个字段查询起来是比较困难的,一般都需要使用like,CONCAT函数、REPLACE函数等做字符串的处理逻辑,查询起来比较复杂。 | 这种方式不推荐单独使用,一般是建议和邻接表相结合,同时增加 parent_id 和 path。 |
左右值 | (1)无需递归获取树结构 (2)支持无限层级树结构 (3)占用的空间相对少 | (1)难以理解 (2)查找上个节点或下个节点难 (3)更新、修改树结构难 | 适用对数据结构比较清楚的开发人员,且这个树形结构不经常变化的场景 |
闭包表 | (1)能明晰任意两结点关系而无须多余查询 (2)在查询树形结构的任意关系时都很方便 | (1)需要存储的数据量比较多 (2)增加和删除节点相对麻烦。 | 查询诉求大,树形结构变动频率小的场景 |
以上都是一些常规的方法来解决, 现在有很多数据库支持一些json数据、xml数据存储,如果树结构不是无限的,可以考虑使用json、xml结构存储。这种结构可阅读性很强,json和xml结构就是树形结构。
04 高阶解决方案
比如postgresql数据库已经支持json和xml:
create table foods (
food jsonb
);
insert into foods values (
'{
"Fruit": {
"Red": [
{
"name": "Cherry"
}
],
"Yellow": [
{
"name": "Banana"
}
]
},
"Meat": [
{
"name": "Beef"
},
{
"name": "Pork"
}
]
}'
);
xml结构:
create table foods (
food xml
);
insert into foods values (
'<Food>
<Fruit>
<Red>
<name>Cherry</name>
</Red>
<Yellow>
<name>Banana</name>
</Yellow>
</Fruit>
<Meat>
<name>Beef</name>
<name>Pork</name>
</Meat>
</Food>'
);
这些数据加载在内存,很容易增删改,如果使用这两个方案,不一定是关系型数据库。当然这种方案也有缺点,就是无法和其他数据产生关联。
Postgresql 还提供另外一种数据结构是ltree。可以很方便处理树形结构:
create table foods (
id integer,
name varchar(15),
path ltree
);
insert into path_tree (id,tree_p, path) values (1,'Food', 'Food');
insert into path_tree (id,tree_p, path) values (2,'Fruit', 'Food.Fruit');
insert into path_tree (id,tree_p, path) values (3,'Red', 'Food.Fruit.Red');
.......
postgresql提供了很丰富的操作,比如 @> path 包含path的所有节点等。总之很方便增删改读操作。可惜的是postgresql ltree只支持英文和数字,对于一些中文路径不支持。更多操作方式见推荐阅读文章。
05总结
如果你使用postgresql作为数据库,优先考虑ltree这种方式,其他数据库可以搜索一下有类似postgresql的设计吗?如果没有,可以考虑从传统设计中选择一个最适合自己的场景,涉及到经常变更的树形结构,可以优先考虑json、xml存储,变更修改直接替换即可。
感谢您完成阅读
推荐阅读:
https://www.vsc.cc/article/208108.html