实战篇| 当关系型数据库遇到树

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个字段组成：

字段名称	字段含义
ancestor	祖先：上级节点的id
descendant	子代：下级节点的id
distance	距离：子代到祖先中间隔了几级

依然以上面图为例，我们有节点表：

对应的闭包表是：

这三个字段的组合是唯一的，因为在树中，一条路径可以标识一个节点，所以可以直接把它们的组合作为主键。

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

https://blog.51cto.com/u_14150796/6510961