树形结构表3种设计优劣分析与分享

简介

在开发中经常遇到树形结构的场景,本文将以部门表为例对比几种设计的优缺点;

问题

需求背景:根据部门检索人员,
问题:选择一个顶级部门情况下,跨级展示当前部门以及子部门下的所有人员,表怎么设计更合理 ?
image.png

  • 递归吗 ?递归可以解决,但是势必消耗性能

设计1:邻接表

注:(常见父Id设计)

表设计

CREATE TABLE `dept_info01` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `dept_id` int(10) NOT NULL COMMENT '部门id',
  `dept_name` varchar(100) NOT NULL COMMENT '部门名称',
  `dept_parent_id` int(11) NOT NULL COMMENT '父部门id',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

image.png

这样是最常见的设计,能正确的表达菜单的树状结构且没有冗余数据,但在跨层级查询需要递归处理。

SQL示例

1.查询某一个节点的直接子集

SELECT * FROM dept_info01  WHERE dept_parent_id =1001

优点

  • 结构简单 ;

缺点

1.不使用递归情况下无法查询某节点所有父级,所有子集

设计2:路径枚举

在设计1基础上新增一个父部门id集字段,用来存储所有父集,多个以固定分隔符分隔,比如逗号。

表设计

CREATE TABLE `dept_info02` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `dept_id` int(10) NOT NULL COMMENT '部门id',
  `dept_name` varchar(100) NOT NULL COMMENT '部门名称',
  `dept_parent_id` int(11) NOT NULL COMMENT '父部门id',
  `dept_parent_ids` varchar(255) NOT NULL DEFAULT '' COMMENT '父部门id集',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

image.png

SQL示例

1.查询所有子集
1).通过模糊查询

SELECT
 *
FROM
	dept_info02
WHERE
	dept_parent_ids like '%1001%'

2).推荐使用 FIND_IN_SET 函数

SELECT
	* 
FROM
	dept_info02 
WHERE
	FIND_IN_SET( '1001', dept_parent_ids )

优点

  • 方便查询所有的子集 ;
  • 可以因此通过比较字符串dept_parent_ids长度获取当前节点层级 ;

缺点

  • 新增节点时需要将dept_parent_ids字段值处理好 ;
  • dept_parent_ids字段的长度很难确定,无论长度设为多大,都存在不能够无限扩展的情况 ;
  • 节点移动复杂,需要同时变更所有子集中的dept_parent_ids字段值 ;

设计3:闭包表

  • 闭包表是解决分级存储的一个简单而优雅的解决方案,这是一种通过空间换取时间的方式 ;
  • 需要额外创建了一张TreePaths表它记录了树中所有节点间的关系 ;
  • 包含两列,祖先列与后代列,即使这两个节点之间不是直接的父子关系;同时增加一行指向节点自己 ;

表设计

  • 主表
CREATE TABLE `dept_info03` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `dept_id` int(10) NOT NULL COMMENT '部门id',
  `dept_name` varchar(100) NOT NULL COMMENT '部门名称',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

image.png

  • 祖先后代关系表
CREATE TABLE `dept_tree_path_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `ancestor` int(10) NOT NULL COMMENT '祖先id',
  `descendant` int(10) NOT NULL COMMENT '后代id',
  `depth` tinyint(4) NOT NULL DEFAULT '0' COMMENT '层级深度',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

注:depth 层级深度字段 ,自我引用为 1,直接子节点为 2,再一下层为 3,一次类推,第几层就是几 。

image.png

SQL示例

插入新节点

INSERT INTO dept_tree_path_info (ancestor, descendant,depth)
SELECT t.ancestor, 3001,t.depth+1 FROM dept_tree_path_info AS t 
WHERE t.descendant = 2001
UNION ALL
SELECT 3001,3001,1

查询所有祖先

SELECT
	c.*
FROM
	dept_info03 AS c
INNER JOIN dept_tree_path_info t ON c.dept_id = t.ancestor
WHERE
	t.descendant = 3001

查询所有后代

SELECT
	c.*
FROM
	dept_info03 AS c
INNER JOIN dept_tree_path_info t ON c.dept_id = t.descendant
WHERE
t.ancestor = 1001

删除所有子树

DELETE 
FROM
	dept_tree_path_info 
WHERE
	descendant IN 
	( 
		SELECT
			a.dept_id 
		FROM
		( SELECT descendant dept_id FROM dept_tree_path_info WHERE  ancestor = 1001 ) a
	)

删除叶子节点

DELETE 
FROM
	dept_tree_path_info 
WHERE
	descendant = 2001

移动节点

  • 删除所有子树(先断开与原祖先的关系)
  • 建立新的关系

优点

  • 非递归查询减少冗余的计算时间 ;
  • 方便非递归查询任意节点所有的父集 ;
  • 方便查询任意节点所有的子集 ;
  • 可以实现无限层级 ;
  • 支持移动节点 ;

### 缺点
  • 层级太多情况下移动树节点会带来关系表多条操作 ;
  • 需要单独一张表存储对应关系,在新增与编辑节点时操作相对复杂 ;

结合使用

可以将邻接表方式与闭包表方式相结合使用。实际上就是将父id冗余到主表中,在一些只需要查询直接关系的业务中就可以直接查询主表,而不需要关联2张表了。在需要跨级查询时祖先后代关系表就显得尤为重要。

表设计

  • 主表
CREATE TABLE `dept_info04` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `dept_id` int(10) NOT NULL COMMENT '部门id',
  `dept_name` varchar(100) NOT NULL COMMENT '部门名称',
  `dept_parent_id` int(11) NOT NULL COMMENT '父部门id',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  • 祖先后代关系表
CREATE TABLE `dept_tree_path_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `ancestor` int(10) NOT NULL COMMENT '祖先id',
  `descendant` int(10) NOT NULL COMMENT '后代id',
  `depth` tinyint(4) NOT NULL DEFAULT '0' COMMENT '层级深度',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

总结

其实,在以往的工作中,曾见过不同类型的设计,邻接表,路径枚举,邻接表路径枚举一起来的都见过。每种设计都各有优劣,如果选择设计依赖于应用程序中哪种操作最需要性能上的优化。

设计表数量查询直接子查询子树同时查询多个节点子树插入删除移动
邻接表1简单需要递归需要递归简单简单简单
枚举路径1简单简单查多次相对复杂简单复杂
闭包表2简单简单简单相对复杂简单复杂

综上所述

  • 只需要建立子父集关系中可以使用邻接表方式 ;
  • 涉及向上查找,向下查找的需要建议使用闭包表方式 ;

公众号
更多精彩内容、编程故事、心得分享,欢迎关注公众号
在这里插入图片描述

  • 16
    点赞
  • 70
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 7
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 7
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

程序员小强

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值