【MySQL疑难杂症】如何将树形结构存储在数据库中(方案三 Closure Table)

  今天介绍将树形结构存储在数据库中的第三种方法——终结表(原谅我这生硬的翻译。。)。

  继续用上一篇的栗子,下面是要存储的结构图:

image.png

  需要回答的问题依旧是这样几个:

  1.查询小天的直接上司。

  2.查询老宋管理下的直属员工。

  3.查询小天的所有上司。

  4.查询老王管理的所有员工。

方案三、Closure Table 终结表法,保存每个节点与其各个子节点的关系,也就是记录以其为根节点的全部子节点信息。直接上代码就明白了:

  这里要创建两个表,一个表用来存储信息:

CREATE TABLE employees3(
eid INT,
ename VARCHAR(100),
position VARCHAR(100)
)

  一个表用来存储关系:

CREATE TABLE emp_relations(
root_id INT,
depth INT,
is_leaf TINYINT(1),
node_id INT
)

  这里的root_id用来存放以其为根节点的路径,node_id表示节点处的eid,depth表示根节点到该节点的深度,is_leaf表示该节点是否为叶子节点。

 接下来插入数据:

  可以看出,这个关系表有点大,我们先来看看查询效果如何:

  1.查询小天的直接上司。

  这里只需要在关系表中找到node_id为小天id,depth为1的根节点id即可。

SELECT e2.ename BOSS FROM employees3 e1,employees3 e2,emp_relations rel 
WHERE e1.ename='小天' AND rel.node_id=e1.eid AND rel.depth=1 AND e2.eid=rel.root_id

  查询结果如下:

image.png

  2.查询老宋管理下的直属员工。

  思路差不多,只要查询root_id为老宋eid且深度为1的node_id即为其直接下属员工id

SELECT e1.eid,e1.ename 直接下属 FROM employees3 e1,employees3 e2,emp_relations rel 
WHERE e2.ename='老宋' AND rel.root_id=e2.eid AND rel.depth=1 AND e1.eid=rel.node_id

  查询结果如下:

image.png

  3.查询小天的所有上司。

  只要在关系表中找到node_id为小天eid且depth大于0的root_id即可

SELECT e2.eid,e2.ename 上司 FROM employees3 e1,employees3 e2,emp_relations rel 
WHERE e1.ename='小天' AND rel.node_id=e1.eid AND rel.depth>0 AND e2.eid=rel.root_id

  查询结果如下:

image.png

  4.查询老王管理的所有员工。

  只要在关系表中查找root_id为老王eid,depth大于0的node_id即可

SELECT e1.eid,e1.ename 下属 FROM employees3 e1,employees3 e2,emp_relations rel 
WHERE e2.ename='老王' AND rel.root_id=e2.eid AND rel.depth>0 AND e1.eid=rel.node_id

  查询结果如下:

image.png

  我们可以发现,这四个查询的复杂程度是一样的,这就是这种存储方式的优点,而且可以让另一张表只存储跟节点紧密相关的信息,看起来更简洁。但缺点也显而易见,关系表会很庞大,当层次很深,结构很庞大的时候,关系表数据的增长会越来越快,相当于用空间效率来换取了查找上的时间效率。

  至此,树形结构在数据库中存储的三种方式就介绍完了,接下来对比一下三种方法:

  方案一:Adjacency List

  优点:只存储上级id,存储数据少,结构类似于单链表,在查询相邻节点的时候很方便。添加删除节点都比较简单。

  缺点:查询多级结构的时候会显得力不从心。

  适用场合:对多级查询需求不大的场景比较适用。

  方案二:Path Enumeration

  优点:查询多级结构的时候比较方便。查询相邻节点时也比较ok。增加或者删除节点的时候比较简单。

  缺点:需要存储的path值可能会很大,甚至超过设置的最大值范围,理论上无法无限扩张。

  适用场合:结构相对简单的场景比较适合。

  方案三:Closure Table

  优点:在查询树形结构的任意关系时都很方便。

  缺点:需要存储的数据量比较多,索引表需要的空间比较大,增加和删除节点相对麻烦。

  适用场合:纵向结构不是很深,增删操作不频繁的场景比较适用。

  当然,也可以再自己创新出其他更好的存储方案,如果有更好的想法,欢迎提出交流。

  至此三种方案全部介绍完毕,欢迎大家继续关注。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏代码世界

MYSQL之索引原理与慢查询优化

一、索引 1、介绍   一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,在生产环境中,我们遇到最多的也是最容易出现问题的,...

529130
来自专栏Java3y

移动商城第七篇【购物车增删改查、提交订单】

把商品加入购物车 接下来我们要做的就是将商品加入到购物车中。我们这次使用的是Cookie来将用户的信息存储起来。那为什么要用cookie呢?? 如果将购物车存储...

1.4K140
来自专栏JavaEdge

MySQL索引及其实现原理(基于MyISAM及InnoDB引擎)

查询是数据库的最主要功能之一。我们都希望查询速度能尽可能快,因此数据库系统的设计者会从查询算法角度优化

5.1K100
来自专栏WindCoder

网易MySQL微专业学习笔记(五)-SQL语言进阶

这个系列属于个人学习网易云课堂MySQL数据库工程师微专业的相关课程过程中的笔记,本篇为其“MySQL数据库对象与应用”中的MySQL数据类型相关笔记。

6910
来自专栏西枫里博客

rand()随机的效率问题

在平时开发过程中,数据量不超过1W条的,通常执行随机查询是通过对order进行rand操作的进行的。但是随着数据量的增加,rand严重制约了整站的访问速度。...

5810
来自专栏架构师小秘圈

高效sql性能优化极简教程

一,sql性能优化基础方法论 对于功能,我们可能知道必须改进什么;但对于性能问题,有时我们可能无从下手。其实,任何计算机应用系统最终队可以归结为: cpu消耗 ...

56550
来自专栏Java进阶架构师

「mysql优化专题」90%程序员面试都用得上的索引优化手册(5)【面试重点】

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。更通俗的说,索引就相当于目录。当你在用新华字典...

14030
来自专栏抠抠空间

MySQL 之 索引原理与慢查询优化

浏览目录 一 索引介绍 二 索引方法 三 索引类型 四 聚合索引和辅助索引  五 测试索引 六 正确使用索引 七 组合索引 八 注意事项 九 查询计划 十 慢日...

43870
来自专栏开发

mysql学习之优化总结(2)--索引的那些事

上一篇文章我们在研究MySQL查询过程的查询优化步骤中提到过优化索引可以优化查询优化的过程,索引到底是什么?它在查询过程中是一个怎样的角色?索引适用于什么场景?...

22050
来自专栏栗霖积跬步之旅

java多线程编程核心技术——第六章总结

1.0立即加载/“饿汉式”   立即加载:实用类的时候已经将对象创建完毕,常见的实现方法就是直接new实例化。   注:是在调用方法前,就已经实例化了(通常是...

19360

扫码关注云+社区

领取腾讯云代金券