避免递归查询的树形结构的表设计与实现

避免递归查询的树形结构的表设计与实现

最近我阅读了一篇关于讲解如何避免查询递归查询所有子部门的树形结构的表设计，实现了高效的部门树查询设计，避免了低效的递归查询，我将这篇文章的设计分享出现。

1.传统设计的问题

我们还是以最常见的组织架构为例，下图是比较常见的一种组织架构中的一部分。我将为这部分为例，来讲解：

传统的数据库表结构设计：

id	name	parent_id	level
1	CEO	0	1
2	财务中心	1	2
3	研发中心	1	2
4	账务部	2	3
5	商务部	2	3
6	产品部	3	3
7	架构部	3	3

表结构说明

id  部门编号
name 部门名称
parent_id 上级部门编号
level 所在的层级

这种结构很不错，很直观的体现了各层级之间的关系，通常可以满足大多数场景的需求。但是当业务需求变多了，数据量庞大了，这样的方式便不再适合。

2. 遇到的问题

假如目前来了如下几个需求，需要如何处理：

1.查询某个部门下了的所有子部门信息？

2.查询子部门的总数？

3.判断节点是否为叶子节点？

3.传统方式来解决以上的问题

3.1 查询某个部门下的所有子部门信息?

方案1：使用指定部门的编号，一层一层的向下递归，直到递归出所有的子部信息。此方案会随着部门的层级深度而变差。

方案2：一次性将所有的部门数据加载到内存，使用程序递归过滤载加所有的数据。此方案数据量少还是可行的，但是当出现非常庞大组织结构时，采用此方案会被人打。

3.2 查询子部门的总数？

递归每一层的数量，最后相加。

3.3 判断节点是否为叶子节点？

方案1：在表中增加isLeaf的字段，来表示这个字段是否为叶子节点.

方案2：直接通过查询parent_id=当前节点id，count节点是否大于0，如果大于0，表示存在子节点。当前非叶子节点，如果等于0，表示当前就是叶子节点。

在日常的开发中，我们通常采用的就是以上类似的方案去类型此类问题，但这样的方案，肯定不是最优解。那有没有更好的的方案呢？

4.一种新的解决方案

这并非我原创，如果想看原创，可参见:《改进后的先序树遍历》(https://www.sitepoint.com/hierarchical-data-database-2)文章（是一篇 2003 年发表的文章）

还是回到我们原来的结构结构图

从根节点开始，给CEO左值设1，下级账务中心设2，以此类推沿着边缘开始遍历，遇到叶子节点给节点加上右值，再继续向上沿着边缘遍历，将得到一个类似这样的结构：

这时候在原来的表中添加上left和right,来存储左右值。

id	name	parent_id	left	right	level
1	CEO	0	1	14	1
2	财务中心	1	2	7	2
3	研发中心	1	8	13	2
4	账务部	2	3	4	3
5	商务部	2	5	6	3
6	产品部	3	9	10	3
7	架构部	3	11	12	3

5.以新的方案来解决问题

数据结构准备完毕。来试试解决上面的需要：

5.1 查询所有子孙部门

根据当前数据的规律，要查询出子孙部门，只需要以左值为起起点，右值为结束点，之间的节点即为子节点。例如查询财务中心的所有子部门，财务中心的左右数是2和7，只需要以left字段做一个between查询，结果就是【被查询部门数据和子孙部门数据】

SET @left := 2;
SET @right := 7;
SELECT * FROM organization WHERE `left` BETWEEN @left AND @right ORDER BY `left` ASC;

结果

id	name	parent_id	left	right	level
2	财务中心	1	2	7	2
3	财务部	2	3	4	3
4	商务部	2	5	6	3

这个数据就查出来了。

5.2 查询子孙部门总数

这个问题，我第一想到的，就是直接count，按查询子孙部门的条件，直接统计下，就有总数了。确实没错，这样做确实可以解决。但现在有另外一种更简便的解决方案：

公式：
总数: (右值 - 左值 - 1) / 2

现在来做个验证

CEO下的所有部门总数 ： (14 - 1 -1)/2 = 6
财务中心下的所有部门总数: (7 - 2 - 1)/2 = 2
研发中心下的所有部门总数: (13 - 8 -1)/2 = 2

经过验证，是正确的

5.3 判断是否为叶子节点

有了计算子孙部门数的经验，这个的是否为叶子节点的判断，也容易了，那就是：

右值 - 1 == 左值 或者 左值 + 1 == 右值

如果这个等式成立那就是一个叶子节点，否则，将不是叶子节点。

做下验证

CEO：    14 -1 != 1 不是叶子节点
财务中心： 7 - 1 != 2 不是叶子节点
财务部：   4 - 1 == 3 是叶子节点

至此新需要已经完全满足了

5.4 新增部门

当新增加一个部门时，需要对新增节点的后续位置做加2处理。因为每个节点都有左右两个数值，这需要放到事务中进行。现在来做下演示增加一个IOS部门：

对应的SQL

begin;
-- 将后续所有的边缘位置进行加2操作
update organization set `left`=`left` + 2 where  `left` > 9;
update organization set `right` =`right` + 2 where  `right` >= 9;
-- 增加子部门，按`left`节点+1和加2
insert into organization(`id`,`name`,`parent_id`,`left`,`right`,level)
values(8, 'IOS产品', 6,10, 11, 5);
commit;

5.5 删除部门操作

删除部门与添加部门类似，不是的是需要对后续节点做减2操作，例如删除刚刚新加的IOS部门。

对应的SQL操作

begin;
-- 对后续的所有节点执行减2操作
update organization set `left` = `left` -2 where `left` > 10;
update organization set `right` = `right` -2 where `right` > 10;
-- 删除节点操作
delete from organization where `id` = 8;
commit;

5.6 查询直接子部门

例如查询CEO下的直接子部门，返回的应该是财务中心和研发中心

-- 查询CEO的信息
select * from organization where `id` = 1;
-- 再以CEO的信息做为查询条件作查询level+1即只查询CEO的下一级
select * from organization where `left` > 1 and `right` < 14 and `level` = 1 + 1;

5.7 查询某个节点的路径链

例如查询产品部的路径链。则应该得到CEO和研发中心。

-- 查询产品部的信息
select * from organization where `id` = 6;

-- 查询产品部的路径链
select * from organization where `left` < 9  and `right` >  10 order by `left` asc

6. 结束：

目前就这些，这个设计相当的经典，也非常的具有实用价值。值得收藏。