数据库设计

结绳记事，记录、思考，方有成长

在做数据库设计时，有时我们需要保存一些树形的数据结构，比如评论、组织架构、话题讨论、知识管理、商品分类、省市县，这些数据存在一种递归关系。很多人想到的第一个解决方案往往是记录每个节点的父节点，例如以下的评论表：

CREATE TABLE comments (
	comment_id int(10) NOT NULL comment '评论id',
	parent_id int(10) DEFAULT NULL comment '父评论id',
	comment text NOT NULL comment '评论内容',
	PRIMARY KEY(comment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

comment表的实际数据

comment_id	parent_id	comment
1	0	新冠病毒传播这么厉害，作为普通人应该注意什么
2	1	哪儿都不能去啊，太危险了
3	1	应该做好防护，多洗手、不出门
4	2	是啊，还是待在家安全
5	2	出门都不敢做公交了
6	3	小区封了
7	4	口罩买不到，捉急

如果采用这样的结构，当一篇帖子回复讨论的内容很多的时候，就需要编写复杂的代码递归检索很多记录，查询的效率就会很低。如果数据量不大、讨论内容相对固定，数据层次较少，采用这样的结构是很简单、清晰的。比如商品分类，通常采用3级分类，我们在设计表时，再增加一个字段level，就可以满足业务场景。那如果数据量很大，层次又不固定，查询就会变得复杂。
下面介绍两种更通用，扩展性更好的解决方案，路径枚举和闭包表

（1） 路径枚举
对于上述表的结构，增加一个字段path，用于记录节点的所有祖先信息。记录的方式是把所有的祖先信息组织成一个字符串。结构如下：
comment表的实际数据(记录了父节点和祖先信息)

comment_id	parent_id	path	comment
1	0	1/	新冠病毒传播这么厉害，作为普通人应该注意什么
2	1	1/2/	哪儿都不能去啊，太危险了
3	1	1/3/	应该做好防护，多洗手、不出门
4	2	1/2/4/	是啊，还是待在家安全
5	2	1/2/5/	出门都不敢做公交了
7	4	1/2/4/7/	口罩买不到，捉急

接下来分析下涉及到的各个场景：

• 查询所有祖先：因为路径path字段包含了该节点的所有祖先信息，所以可以轻易地获取某个节点的所有祖先节点，可以用程序先获取path字符串，然后再使用切割字符串的函数处理得到所有的祖先节点
• 查询所有后代：例如查找comment_id等于2的所有后代，可以使用如下方式

select * from comment where path like '1/2/_%'

• 查询直接后代：例如查询comment_id等于2的直接后代

select * from comment where parent_id = 2

• 插入：比如回复了评论#7，产生的新评论为8，则首先复制评论#7内容，path再拼接上/8即可

insert intio comment
select 8, 7, path+'/8', '评论8内容' from comment where comment_id = 7

• 删除：比如删除了评论2，则其下的所有子评论都需要删除

# 首先查出评论2的path，为1/2/
delete from comment where path like '1/2/%'

总结：枚举路径的方式使得查询子树和祖先都变得更加简单，查看分隔符即可知道节点的层次，虽然冗余存储了一些数据，应用程序需要额外增加代码以确保路径信息的准确性，但这种设计的扩展性更好。
弊端：如果层次过深，path路径长度存在过长情况，字段长度越长，索引查询效率越低。

（2）闭包表
闭包表也是一种通用的方案，它需要额外增加一张表，用于记录节点之间的关系。它不仅记录了节点之间的父子关系。也记录了树中所有节点之间的关系，以及深度。使用如下命令建立path表：

CREATE TABLE path (
	ancestor int(11) NOT NULL,
	descendant int(11) NOT NULL,
	deep int(11) NOT NULL,
	PRIMARY KEY(ancestor, descendant)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

ancestor表示祖先， descendant表示后代，deep表示祖先和后代之间的深度，存储的是对应的comment_id。数据类似于
path表的数据(记录了所有节点之间的关系)

ancestor	descendant	deep
1	1	0
1	2	1
1	3	1
1	4	2
1	5	2
1	6	2
1	7	3
2	2	0
2	4	1
2	5	1
2	7	2
3	3	0
4	4	0
4	7	1
5	5	0
6	6	0
7	7	0

有了如表所示的完整的节点间关系，查找后代节点、祖先节点也变得更容易。

• 查找评论 #2的所有后代，不包含自身

select * from path where ancestor = 2 and descendant <> 2

• 查询评论 #2的直接后代

select * from path where ancestor = 2 and deep = 1

• 插入：比如回复了评论#7，产生的新评论id为 #8。注意：所有的祖先节点都需要再关联上节点#8，并且深度在原来基础上加1。同时记录节点自身，深度为0

insert into path 
select ancestor, 8, deep+1 from path where descendant = 7
UNION ALL
select 8, 8, 0

• 删除：比如删除了评论#2，则需要把#2的 所有子孙节点删除，同时也要和#2的父节点解除关系

delete from path where ancestor =2 or descendant = 2

总结：如上所述的数据结构，增加了一个表，用于存储节点之间的信息，是一种典型的“以空间换时间”的方案，而且一个节点可以属于多棵树。相对于路径枚举，闭包表的节点关系更容易维护。
缺点：对于层级为N的评论树，至少对应N*(N+1)/2条记录，不过，这也是它优于路径枚举而要付出的代价。