树结构数据的关系型数据库表结构设计方案

qoggy
659 阅读4分钟

前言

在现代数据库应用中，树形结构的存储和查询问题常常面临复杂的挑战，示例：组织/部门表、汇报线表。如何有效地管理和访问树形数据结构，直接影响到系统的性能、可维护性和扩展性。

本文将详细探讨四种不同的方法来设计树形结构在数据库中的存储方案。其中，方案二和方案四因其在性能和扩展性上的优越性以及在实践中的优秀表现（如：阿里生态主数据平台、金蝶云）被特别推荐。通过比较各种方案的优缺点，读者可以根据实际需求选择最合适的实现方式。

方案一

表结构设计

字段名称意义
id唯一主键，id>0
parent_id父节点的id，每个节点只会有一个父节点。根节点parent_id=0
其他字段，略

对id、parent_id字段分别建立索引

示例：查询某部门的所有子节点及自身信息

 WITH RECURSIVE t1 AS (

     SELECT *
     FROM org p
     WHERE id = #{id}

     UNION ALL

     SELECT t.*
     FROM org t
     INNER JOIN t1 ON t1.id = t.parent_id
)

SELECT *
FROM t1

示例：查询某部门的所有父节点及自身信息

WITH RECURSIVE t1 AS (

     SELECT *
     FROM org p
     WHERE id = #{id}

     UNION ALL
  
     SELECT t.*
     FROM org t 
     INNER JOIN t1 ON t1.parent_id = t.id
)

SELECT *
FROM t1

示例：判断是否有下游节点

SELECT id
FROM org t
WHERE t.parent_id = #{id}

示例：判断是否有上游节点

如果不知道parent_id，就先根据id查parent_id

SELECT id
FROM org t
WHERE t.id = #{parent_id}

优点：只需要一条SQL语句，不需要频繁连接数据库，查询可以走索引。没有存储任何冗余信息，存储结构和逻辑上的结构基本一致，方便增删改

缺点：依赖MySQL，而需要MySQL版本达到8.0及以上，递归SQL性能差；扩展性差，数据量大时难以分库存储。

对于低于8.0版本的MySQL：

扩展表格模型

假设使用扩展表格模型需要创建一个递归查询视图，可以使用以下SQL语句：

CREATE VIEW org_tree AS
SELECT id, name, parent_id, 0 AS level
FROM org
WHERE parent_id = 0        -- 0代表是根节点
UNION ALL
SELECT o.id, o.name, o.parent_id, ot.level + 1
FROM org o
INNER JOIN org_tree ot ON o.parent_id = ot.id;

然后，可以使用以下SQL语句，根据查询ID其组织机构及其所有子结构：

SELECT id, name, parent_id, level
FROM org_tree
WHERE id = #{id}

方案二

使用路径编码的方式存储树结构信息，本质上是使用冗余信息来提高性能

表结构设计：

字段名称意义
id唯一主键
path代表树的结构路径，比如：/1/2/3
其他字段，略

对id字段建立索引，对path建立索引（前缀索引）

path规则如下：

关于数据库树形结构表的处理-流程图1.jpg

示例：查询Chris Jones的所有子节点

SELECT *
FROM org
WHERE path LIKE '/0/1/2%'

示例：查询Chris Jones的所有父节点

SELECT *
FROM org
WHERE path IN ('/0', '/0/1')

优点：查询性能更高，扩展性好

缺点：空间开销更大，而且更新结构关系时性能开销大

方案三

Nested Sets

表结构设计

字段名称意义
id唯一主键
ns_left节点左数值
ns_right节点右数值
其他字段，略

对id字段建立索引，对（ns_left, ns_right）建立索引

采用深度优先遍历给树中的每个节点分配两个值,分别存在ns_left和ns_right中。如下图

关于数据库树形结构表的处理-流程图2.jpg

示例：查询Chris Jones的所有下属员工

SELECT *
FROM org
WHERE ns_left > 5 and ns_right < 10

示例：查询Chris Jones的所有领导

SELECT *
FROM org
WHERE ns_left < 5 and ns_right > 10

优点：查询性能更高

缺点：新增节点、移动节点时性能开销大，而且实现复杂，可维护性差

方案四

使用关系的冗余来提高性能

表结构设计：

字段名称意义
id唯一主键
ancestor_id祖先节点id
child_id子节点id
distance深度，祖先到孩子的距离
其他字段，略

对ancestor_id、child_id字段建立索引

存储示例如下：

idancestor_idchild_iddistance
11000011000010
21000011000021
31000011000032
41000021000020
51000021000031
61000031000030

示例：查询Helen的所有下属员工

SELECT *
FROM org
WHERE ancestor_id = 100002

示例：查询Helen的所有领导

SELECT *
FROM org
WHERE child_id = 100002

优点：查询性能高，扩展性好

缺点：更新组织结构时性能低，磁盘存储空间开销大

avatar
文章
阅读
粉丝