数据库树形表设计

1、调研背景

对于数据库树形结构的设计，前端页面需要显示为树形的结构，主要涉及的操作：

（1）查询全量树结构：返回一个json串，以层级嵌套的方式

（2）添加树节点

（3）修改树节点名称

（4）删除树节点

（5）移动树节点

（6）查询节点的路径

对于数据库树形表结构设计的不同，操作的复杂度有很大的区别，现有的设计方式就是使用邻接表的方式进行实现。

2、树形表实现方式

2.1、邻接表（Adjacency List）

2.1.1、数据库表设计

想要描述一棵树，树有一个明显的性质：每一个节点只有一个父节点，根节点除外，那么最简单的模型就是设计四个字段来描述一颗树，其中parent_id为当前节点的父节点id，根节点的parent_id设置为0，sort_order用来进行定义同一父节点下，子节点的顺序；

设计表 t_adjacency_list 如下所示：

字段名	类型	注释
id	int	id，主键
name	varchar(255)	节点名称
parent_id	int	父节点id
sort_order	int	排序位，越小越在前

2.1.2、对于树操作

（1）查询全量树结构

可以使用两种方式进行查询，一种是递归操作，需要频繁操作数据库，一种是使用全量查询，先按照父节点id降序，再按照sort_order节点升序，然后遍历整合即可；

• 递归查询组装

  public List<Node> getTree(){
     return getTree(0L)；
  }
  public List<Node> getTree(Long pId){
     	List<Node> childList = findNodeByPIdAndSort(pId);
     	if(childList.size() == 0) return null;
      for(Node node : childList){
          // 递归组装子节点
          node.setChilds(getTree(node.getId));
      }
      return childList;
  }
  

• 全量查询，排序组装，思想是对原来的递归进行模拟，空间换时间；

  public List<Node> getTree(){
      List<Node> nodeList = getAllNode();
      // 先按照parentId降序，再按照sorOrder升序
    	nodeList.sort(
       	 Comparator.comparing(Tree::getParentId).reversed().thenComparing(Tree::getSortOrder)
   	);
      Map<Long, Node> nodeMap = nodeList.stream()
          .collect(Collectors.toMap(Node::getId, node -> node));
      Tree node;
      for (int i = 1, j = 0; i <= nodeList.size(); i++) {
          if (i == nodeList.size()) {
              return nodeList.subList(j, i);
          }
          if (!nodeList.get(j).getParentId().equals(nodeList.get(i).getParentId())) {
              node = nodeMap.get(nodeList.get(j).getParentId());
              if (node != null) {
                  node.setChilds(nodeList.subList(j, i));
              }
              j = i;
          }
      }
  }
  

返回的结构类似于如下JSON串：

[
    {
        "id": 1,
        "name": "目录1",
        "parentId": 0,
        "sortOrder": 1,
        "childs": [
            {
                "id": 2,
                "name": "文件11",
                "parentId": 1,
                "sortOrder": 1
            },
            {
                "id": 3,
                "name": "文件12",
                "parentId": 1,
                "sortOrder": 2
            }
        ]
    },
    {
        "id": 4,
        "name": "目录2",
        "parentId": 0,  
        "sortOrder": 2
    }
]

（2）添加

实现简单，接受parent_id和name即可，根据parent_id查询树下最大的sort_order，然后进行加一即可；

insert into t_adjacency_list(name, parent_id, sort_order) values ("目录"，"0", "1");

（3）修改节点名称

根据传入的节点id和newName，进行修改就行；

update t_adjacency_list set name = "newName" where id = "id";

（4）删除

删除实际上分为两种（此处用物理删除，也可以使用逻辑删除，原理一样）；

• 递归删除

  删除节点是，递归删除此节点所有的子节点；

  public void deleteNodeAndChild(Long id){
      deleteNodeById(id);
      List<Long> childNodeIds = findChildByPId(id);
      for(Long childId : childNodeIds){
          // 进行递归操作
          deleteNodeAndChild(id)
      }
  }
  

• 只能删除空节点

  delete from t_adjacency_list where id = "id";
  

（5）移动

移动时需要定义个规则：节点只可以移动到 同级或父级节点 下面；

实现也比较简单，修改移动节点的父节点id和sort_order即可；

update t_adjacency_list set parent_id = "newParentId", sort_order="newSortOrder" where id = "id";

（6）查询节点路径

需要进行递归查询，稍微复杂一点，查询结果类似于：目录1/目录11/文件111；

public String getPath(Long id){
    if(id == 0L) return ""
    Node node = findNodeById(id);
    // 递归组装路径
    return getPath(node.getParentId()) + "/" + node.getName();
}

2.1.3、小结

可见对于以上树的操作，添加、修改、移动不需要进行递归操作，操作相对比较简单；对于查询树结构操作算是属于比较频繁的，使用递归操作多次查询数据库不是明智的选择，使用第二种排序加遍历组装的方式更好；对于删除树节点操作，当需求明确定义为空节点才可以删除，操作才简单；最后就是查询节点的路径必须使用递归操作，频繁查询数据库，效果一般。

2.2、路径枚举（Path Enumeration）

邻接表中有一个缺点就是查询节点的路径必须使用递归查询数据库，很消耗性能，而路径枚举的设计，通过将所有的祖先信息连接为一个字符串，巧妙的解决了这个问题。

2.2.1、数据库表设计

使用path字段来存储当前节点的路径，其中以 / 结尾的代表目录，例如 1/2/：表示1目录下的2目录，1/3 表示：1目录下的3文件；

设计表（t_path_enum）如下所示：

字段名	类型	注释
id	int	id，主键
name	varchar(255)	节点名称
path	varchar(255)	节点路径

2.2.2、对于树的操作

（1）查询全量树结构

操作复杂，使用递归的方式去查，方法类似于邻接表；

（2）添加

操作简单，指定添加的文件还是目录，传入父节点目录，添加一个目录或文件到数据库，成功之后修改path；

public void insert(Long pId, String name, Boolean isFolder){
    Node node = insertNode(name);
    Node parentNode = getNodeByParent(pId);
    if(isFolder){
        updateNode(node.getId(), parentNode.getPath() + "/" + node.getId() + "/");
    }else{
         updateNode(node.getId(), parentNode.getPath() + "/" + node.getId());
    }  
}

（3）修改节点名称

操作简单，根据传入的节点id和newName，进行修改就行；

update t_path_enum set name = "newName" where id = "id";

（4）删除

操作简单，传入节点的id，查询获取节点的path，使用like语法即可完成全部的删除；

• 删除所有子节点

  delete from t_path_enum where path like "path%";
  

• 只能删除空节点

  delete from t_path_enum where path = "path";
  -- 或者
  delete from t_path_enum where id = "id";
  

（5）移动

移动时需要定义个规则：节点只可以移动到 同级或父级节点 下面；

操作相对比较复杂，需要两步操作，首先修改移动节点的路径，其次查询出移动节点的子节点，可以使用like查询，修改所有子节点的路径；在java代码操作中可以将需要修改的对象批量传入，批量更新也是OK的。

（6）查询节点路径

操作简单，查询出节点的path，然后使用 / 进行分割，取出所有的id，一次查询数据库组装返回即可；

select path from t_path_enum where id = "id";

2.2.3、小结

使用path字段进行节点路径的记录，对于每一个节点的父节点以及祖宗节点查询非常快；同时添加、修改、删除、移动操作也非常的简单；对于查询全量树操作必须借助递归实现且移动操作，代价相对比较大；同时还可以快速知道当前节点在树中的深度；

缺点：若树的层级不限时，path字段的长度不可以估量，可以使用text进行存储；数据库不能确保路径的格式总是正确的或者路径中节点确实存在，依赖于程序的逻辑代码去维护路径的字符串，并且验证的字符串正确性的开销很大；

2.3、嵌套集（Nested Sets）

嵌套集解决方案时存储子孙节点的相关信息，而不是节点的直接祖先，使用两个数字编码每一个节点，从而表示这一信息，可以将这两个值称为left和right；

2.3.1、数据库表设计

设计表（t_nested_sets）如下所示：

字段名	类型	注释
id	int	id，主键
name	varchar(255)	节点名称
left	int	左编码值
right	int	右编码值

为了清晰的表示出节点的左右值是怎么来的，可根据下图进行理解，从根节点开始进行先序遍历，依次标记数字，最后回到根节点，怎可以计算出所有节点的左右值；

数据库存储示例：

id	name	left	right
1	目录1	1	8
2	目录11	2	5
3	文件111	3	4
4	文件11	6	7

由此可以看出一些简单的性质：

• 叶子节点 right-left=1
• 节点A的后代节点 left 必在 A的 [left，right]

2.3.2、对于树的操作

（1）查询全量树结构

感觉使用递归应该可以实现的；

（2）添加

操作复杂，需要维护整棵树节点的左右值变化；

（3）修改节点名称

操作简单，根据传入的节点id和newName，进行修改就行；

update t_nested_sets set name = "newName" where id = "id";

（4）删除

操作困难，传入节点的id，查询节点的left和right ，其后代节点left都在此节点的 [left，right]，需要重新分配树左右节点的值

• 删除所有子节点，以 目录11 为例

  delete from t_nested_sets where `left` >= 2 and `right` <= 5; 
  

• 只能删除空节点，以 文件11 为例

  delete from t_nested_sets where id = 4;
  

（5）移动

操作困难，需要维护整棵树节点的左右值变化；

（6）查询节点路径

操作简单，查询A节点的路径，只需要查询出left小于A节点的left，right大于A节点的right，按照left进行排序即可；

以节点 文件111 为例：

select * from t_nested_sets where `left` <= 3 and `right` >= 4 order by `left` ASC; 

2.3.3、小结

如果存储的树结构不常变化。查询是主要的业务，则嵌套集是最佳的选择，结合它的性质，比操作单点更加的方便，但是，对于新增、移动和删除是比较复杂的，因为需要重新分配节点的左右值，如果程序中需要频繁的新增、移动和删除节点，则不适合。

2.4、闭包表（Closure Table）

闭包表时解决树形表存储一个简单而优雅的方案，它单独建立一张表记录树中所有节点之间的关系，而不仅仅像邻接表那样只有父子关系，其实也是使用空间换取时间；

2.4.1、数据库表设计

主表 t_node_info

字段名	类型	注释
id	int	id，主键
name	varchar(255)	节点名称

节点之间的关系表 t_node_relationship

字段名	类型	注释
ancestor	int	祖先id
descendant	int	后代id
distance	int	祖先距离后代的距离

示例：

数据库存储示例：

id	name
1	目录1
2	目录11
3	文件111
4	文件11

ancestor	descendant	distance
1	1	0
1	2	1
1	3	2
1	4	1
2	2	0
2	3	1
3	3	0
4	4	0

注意：每一个节点存储时都有一条到其本身的记录，距离为0，其中distance就是祖先到后代之间的距离。

2.4.2、对于树的操作

（1）查询全量树结构

查询关系表，距离为1，按照祖先进行升序，查出数据进行遍历整理即可;

select * from t_node_relationship where distance = 1 order by ancestor;

（2）添加

如果新加入一个节点 文件112 在 目录11 的下面时，首先插入节点到t_node_info中，其次 查询 目录11 所有的祖先节点，然后对每一个祖先节点添加一条关系信息，距离取当前信息的距离值加一即可；

public void createNode(String name, Long parentId){
    Node newNode = insertNode(name);
    List<Relationship> relationshipList = getRleationshipByDescendantId(parentId);
    insertRelationship(newNode.getId(), relationshipList);
}

（3）修改

操作简单，根据传入的节点id和newName，进行修改就行；

update t_node_info set name = "newName" where id = "id";

（4）删除

删除节点为 deleteId

• 删除所有子节点

  首先查出deleteId作为祖先节点下所有的后代id，然后依次删除节点信息和关系表中节点关系信息;

  public void deleteNode(Long deleteId){
      List<Long> ancestorIdList = getAncestorsByDescendantId(deleteId);
      deleteNodeById(ancestorIdList);
      deleteRlationship(ancestorIdList);
  }
  

• 只能删除空节点，这样符合删除的数据在关系表中只会存在一条，就是自身的关系，依次删除节点信息和节点关系信息；

  delete from t_node_info where id = "deleteId";
  delete from t_node_relationship where ancestor = "deleteId"
  

（5）移动

移动节点moveId，移动到的目标newParentId，需要分两步进行，操作相对比较复杂；

第一步：删除移动节点以及其孩子节点与 移动节点的祖先们的关系；

第二步：重新建立移动节以及其孩子节点 与 newParentId 及其 祖先节点的关系；

（6）查询节点路径

操作简单，在 t_node_relationship 表中记录所有节点之间的关系，查询关系表，后代的值为要查的节点id，按照距离倒序即可；

select ancestor from t_node_relationship where descendant = "id" order by distance desc;

2.4.3、小结

由于存储了节点之间所有的关系和距离，对于查询节点之间的关系、路径和距离有着明显的优势，可以快速进行查询；同时对于新增、修改、删除、移动，查询全量树操作也相对简单，并没有涉及到递归的操作，但是维护节点之间的关系逻辑操作会相当多一点，同时节点关系表的存储开销会非常大；

3、总结

简单 < 复杂 < 困难

设计	表数量	增加	移动	删除	查询全量树	查询路径
邻接表	1	简单	简单	简单	复杂	困难
路径枚举	1	简单	复杂	简单	困难	简单
嵌套集	1	困难	困难	困难	复杂	简单
闭包表	2	简单	复杂	简单	复杂	简单

• 邻接表是最简单的设计，其中维护起来很方便，但是有些关于树的操作必须使用递归才可以进行；
• 枚举路径能够很直观的展示出祖先到后代的关系之间的路径，但是也因此使得数据的存储变得比较冗余；
• 嵌套集是一个很聪明的方案，对查询的性能提高了很多，但是对于其他操作显得非常复杂；
• 闭包表是最通用的设计，使用额外的一张关系表来存储节点之间的关系，空间换时间的方法减少操作过程中由冗余的计算所造成的消耗，同时关系表将占据大量的数据存储；

4、参考博客

树形结构数据存储方案
sql反模式-单纯的树