评论表设计 - 路径枚举、嵌套集、闭包表
场景
设想现在有个需求：设计一个评论系统，要求用户可以评论文章以及相互回复，无层级数限制。
(程序员最常用的邻接表这里就不展开讲了，小伙伴们可以自行百度。)
路径枚举
路径枚举是一个由连续的直接层级关系组成的完整路径。如 /usr/local/lib 的 UNIX 路径是文件系统的一个路径枚举，其中 usr 是 local 的父亲，这也就意味着 usr 是 lib 的祖先。
在 comments 表中，我们使用类型为 VARCHAR 的 path 字段来存储内容为当前节点的最顶层的祖先到它自己的序列，就像 UNIX 的路径一样，你甚至可以使用 ‘/’ 作为路径中的分割符。
表结构：
CREATE TABLE `comments` (
`comment_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`path` varchar(100) DEFAULT NULL,
`bug_id` bigint(20) unsigned NOT NULL,
`author` varchar(60) NOT NULL,
`comment_date` datetime NOT NULL,
`comment` text NOT NULL,
PRIMARY KEY (`comment_id`),
KEY `bug_id` (`bug_id`)
);
INSERT INTO `comments` VALUES (1, '1/', 1, 'Fran', '2021-05-23 10:27:22', '这个Bug的成因是什么');
INSERT INTO `comments` VALUES (2, '1/2/', 1, 'Ollie', '2021-05-23 10:29:26', '我觉得是一个空指针');
INSERT INTO `comments` VALUES (3, '1/2/3/', 1, 'Fran', '2021-05-23 10:30:00', '不，我查过了');
INSERT INTO `comments` VALUES (4, '1/4/', 1, 'Kukla', '2021-05-23 10:30:34', '我们需要查无效输入');
INSERT INTO `comments` VALUES (5, '1/4/5/', 1, 'Ollie', '2021-05-23 10:31:01', '是的，那是个问题');
INSERT INTO `comments` VALUES (6, '1/4/6/', 1, 'Fran', '2021-05-23 10:31:19', '好，查一下吧');
INSERT INTO `comments` VALUES (7, '1/4/6/7', 1, 'Kukla', '2021-05-23 10:31:41', '解决了');| comment_id | path | author | comment |
|---|---|---|---|
| 1 | 1/ | Fran | 这个 Bug 的成因是什么 |
| 2 | 1/2/ | Ollie | 我觉得是一个空指针 |
| 3 | 1/2/3/ | Fran | 不，我查过了 |
| 4 | 1/4/ | Kukla | 我们需要查无效输入 |
| 5 | 1/4/5/ | Ollie | 是的，那是个问题 |
| 6 | 1/4/6/ | Fran | 好，查一下吧 |
| 7 | 1/4/6/7/ | Kukla | 解决了 |
你可以通过比较每个节点的路径来查询一个节点的祖先。比如，要找到评论 #7（路径为 1/4/6/7）以及它祖先，可以这样做：
SELECT * from comments AS c where '1/4/6/7/' like CONCAT(c.path,'%');比如查找评论 #4（路径为 1/4）以及它的所有后代，可以使用如下的语句：
SELECT * from comments AS c where c.path like CONCAT('1/4/','%');如果要计算从评论 #4 扩展出的所有评论中每个用户的评论数量，可以这样做：
SELECT author,count(*) from comments AS c where c.path like CONCAT('1/4/','%') GROUP BY c.author;插入一个节点需要做的只是复制一份要插入节点的逻辑上的父亲节点的路径，并将这个新节点的 ID 追加到路径末尾就行了。
INSERT INTO comments (author,comment_date,bug_id, comment) VALUES ('Ollie','2021-01-11', 1,'Good job!');
UPDATE comments
SET path = ( SELECT b.path FROM ( SELECT CONCAT( path, '/8' ) AS path FROM comments WHERE comment_id = 7 ) AS b )
WHERE
comment_id = 8;路径枚举的缺点：数据库不能确保路径的格式总是正确或者路径中的节点确实存在。依赖于应用程序的逻辑代码来维护路径的字符串，并且验证字符串的正确性的开销很大。无论将 VARCHAR 的长度设定为多大，依旧存在长度限制，因而并不能够支持树结构的无限扩展。
嵌套集
嵌套集解决方案是存储子孙节点的相关信息，而不是节点的直接祖先。我们使用两个数字来编码每个节点，从而表示这一信息，可以将这两个数字称为 nsleft 和 nsright 。
表结构：
CREATE TABLE `comments` (
`comment_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`nsleft` int(11) NOT NULL,
`nsright` int(11) NOT NULL,
`bug_id` bigint(20) unsigned NOT NULL,
`author` varchar(200) NOT NULL,
`comment_date` datetime NOT NULL,
`comment` text NOT NULL,
PRIMARY KEY (`comment_id`),
KEY `bug_id` (`bug_id`)
);
INSERT INTO `comments` VALUES (1, 1, 14, 1, 'Fran', '2021-06-16 18:50:51', '这个Bug的成因是什么');
INSERT INTO `comments` VALUES (2, 2, 5, 1, 'Ollie', '2021-06-16 18:53:07', '我觉得是一个空指针');
INSERT INTO `comments` VALUES (3, 3, 4, 1, 'Fran', '2021-06-16 18:53:36', '不，我查过了');
INSERT INTO `comments` VALUES (4, 6, 13, 1, 'Kukla', '2021-06-16 18:53:58', '我们需要查无效输入');
INSERT INTO `comments` VALUES (5, 7, 8, 1, 'Ollie', '2021-06-16 18:54:19', '是的，那是个问题');
INSERT INTO `comments` VALUES (6, 9, 12, 1, 'Fran', '2021-06-16 18:54:47', '好，查一下吧');
INSERT INTO `comments` VALUES (7, 10, 11, 1, 'Kukla', '2021-06-16 18:55:06', ' 解决了');每个节点通过如下的方式确定 nsleft 和 nsright 的值：nsleft 的数值小于该节点所有后代的 ID，同时 nsright 的值大于该节点所有后代的 ID。这些数字和 comment_id 的值并没有任何关联。
确定这三个值（nsleft，comment_id，nsrigh）的简单方法是对树进行一次深度优先遍历，在逐层深入的过程中依次递增地分配 nsleft 的值，并在返回时依次递增地分配 nsright 的值。
| comment_id | nsleft | nsright | author | comment |
|---|---|---|---|---|
| 1 | 1 | 14 | Fran | 这个 Bug 的成因是什么 |
| 2 | 2 | 5 | Ollie | 我觉得是一个空指针 |
| 3 | 3 | 4 | Fran | 不，我查过了 |
| 4 | 6 | 13 | Kukla | 我们需要查无效输入 |
| 5 | 7 | 8 | Ollie | 是的，那是个问题 |
| 6 | 9 | 12 | Fran | 好，查一下吧 |
| 7 | 10 | 11 | Kukla | 解决了 |
一旦你为每个节点分配了这些数字，就可以使用它们来找到给定节点的祖先和后代。比如，可以通过搜索哪些节点的 ID 在评论 #4 的 nsleft 和 nsright 范围之间来获取评论 #4 及其所有后代
SELECT
c2.*
FROM
comments AS c1
LEFT JOIN comments AS c2 ON c2.nsleft BETWEEN c1.nsleft
AND c1.nsright
WHERE
c1.comment_id = 4;通过搜索评论 #6 的 ID 在哪些节点的 nsleft 和 nsright 范围之内，可以获取评论 #6 及其所有祖先：
SELECT
c2.*
FROM
comments AS c1
JOIN comments AS c2 ON c1.nsleft BETWEEN c2.nsleft AND c2.nsright
WHERE
c1.comment_id = 6;对树进行操作，比如插入和移动节点，使用嵌套集会比其他的设计复杂很多。当插入一个新节点时，你需要重新计算新插入节点的相邻兄弟节点、祖先节点和它祖先节点的兄弟，来确保它们的左右值都比这个新节点的左值大。同时，如果这个新节点是一个非叶子节点，你还要检查它的子孙节点。假设新插入的节点是一个叶子节点，如下的语句可以更新每个需要更新的地方：
假设新插入的节点是一个叶子节点（插入到第 5 个节点下，左右值为 8，9）：
UPDATE comments
SET nsleft =
CASE
WHEN nsleft >= 8 THEN
nsleft + 2 ELSE nsleft
END,
nsright = nsright + 2
WHERE
nsright >= 7;
INSERT INTO comments ( nsleft, nsright, bug_id, author, comment_date, COMMENT )
VALUES
( 8, 9, 1, 'Fran', '2021-06-16 19:55:06', 'Me too!' );
如果简单快速地查询是整个程序中最重要的部分，嵌套集是最佳选择 —— 比操作单独的节点要方便快捷很多。然而，嵌套集的插入和移动节点是比较复杂的，因为需要重新分配左右值，如果你的应用程序需要频繁的插入、删除节点，那么嵌套集可能并不适合，而且在嵌套集中查询一个节点的直接父节点或者直接子节点，SQL 语句会很长很复杂。
闭包表
闭包表是解决分级存储的一个简单而优雅的解决方案，它记录了树中所有节点间的关系，而不仅仅只有那些直接的父子关系。
在设计评论系统时，我们额外创建了一张叫做 treepaths 的表，它包含两列，每一列都是一个指向 comments 中的 comment_id。
表结构：
CREATE TABLE `comments` (
`comment_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`bug_id` bigint(20) unsigned NOT NULL,
`author` varchar(60) NOT NULL,
`comment_date` datetime NOT NULL,
`comment` text NOT NULL,
PRIMARY KEY (`comment_id`),
KEY `bug_id` (`bug_id`)
);
CREATE TABLE `treepaths` (
`ancestor` bigint(20) unsigned NOT NULL,
`descendant` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`ancestor`,`descendant`),
KEY `descendant` (`descendant`)
);
INSERT INTO `comments` VALUES (1, 1, 'Fran', '2021-06-16 19:27:22', '这个Bug的成因是什么');
INSERT INTO `comments` VALUES (2, 1, 'Ollie', '2021-06-16 19:29:26', '我觉得是一个空指针');
INSERT INTO `comments` VALUES (3, 1, 'Fran', '2021-06-16 19:30:00', '不，我查过了');
INSERT INTO `comments` VALUES (4, 1, 'Kukla', '2021-06-16 19:30:34', '我们需要查无效输入');
INSERT INTO `comments` VALUES (5, 1, 'Ollie', '2021-06-16 19:31:01', '是的，那是个问题');
INSERT INTO `comments` VALUES (6, 1, 'Fran', '2021-06-16 19:31:19', '好，查一下吧');
INSERT INTO `comments` VALUES (7, 1, 'Kukla', '2021-06-16 19:31:41', '解决了');
INSERT INTO `treepaths` VALUES (1, 1);
INSERT INTO `treepaths` VALUES (1, 2