评论表设计 - 路径枚举、嵌套集、闭包表

MySQL
462
0
0
2022-05-06
如何创建一张规范的MySQL表
上一篇
MySQL 连接数过多的处理方法合集 - Too many connections
下一篇

场景

设想现在有个需求：设计一个评论系统，要求用户可以评论文章以及相互回复，无层级数限制。

(程序员最常用的邻接表这里就不展开讲了，小伙伴们可以自行百度。)

路径枚举

路径枚举是一个由连续的直接层级关系组成的完整路径。如 /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 解决了

image.png

你可以通过比较每个节点的路径来查询一个节点的祖先。比如，要找到评论#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 解决了

image.png

一旦你为每个节点分配了这些数字，就可以使用它们来找到给定节点的祖先和后代。比如，可以通过搜索哪些节点的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!' );

image.png

如果简单快速地查询是整个程序中最重要的部分，嵌套集是最佳选择——比操作单独的节点要方便快捷很多。然而，嵌套集的插入和移动节点是比较复杂的，因为需要重新分配左右值，如果你的应用程序需要频繁的插入、删除节点，那么嵌套集可能并不适合，而且在嵌套集中查询一个节点的直接父节点或者直接子节点，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);
INSERT INTO `treepaths` VALUES (1, 3);
INSERT INTO `treepaths` VALUES (1, 4);
INSERT INTO `treepaths` VALUES (1, 5);
INSERT INTO `treepaths` VALUES (1, 6);
INSERT INTO `treepaths` VALUES (1, 7);
INSERT INTO `treepaths` VALUES (2, 2);
INSERT INTO `treepaths` VALUES (2, 3);
INSERT INTO `treepaths` VALUES (3, 3);
INSERT INTO `treepaths` VALUES (4, 4);
INSERT INTO `treepaths` VALUES (4, 5);
INSERT INTO `treepaths` VALUES (4, 6);
INSERT INTO `treepaths` VALUES (4, 7);
INSERT INTO `treepaths` VALUES (5, 5);
INSERT INTO `treepaths` VALUES (6, 6);
INSERT INTO `treepaths` VALUES (6, 7);
INSERT INTO `treepaths` VALUES (7, 7);

我们不再使用 comments 表来存储树的结构，而是将树中任何具有祖先—后代关系的节点对都存储在 treepaths 表的一行中，即使这两个节点之间不是直接的父子关系；同时，我们还增加一行指向节点自己。

祖先 后代 祖先 后代 祖先 后代 1 1 1 7 4 6 1 2 2 2 4 7 1 3 2 3 5 5 1 4 3 3 6 6 1 5 4 4 6 7 1 6 4 5 7 7

这里以 comment_id 为1 作为例子，当 comment_id 为1 treepaths 需要存储的结构则是表格标红的内容

image.png

通过 treepaths 表来获取祖先和后代比使用嵌套集更加地直接。例如要获取评论#4的后代，只需要在 treepaths 表中搜索祖先是评论#4的行就可以了：

SELECT
    c.*,
    t.* 
FROM
    comments AS c
    JOIN TreePaths AS t ON c.comment_id = t.descendant 
WHERE
    t.ancestor = 4;

要获取评论#6的所有祖先，只需要在 treepaths 表中搜索后代为评论#6的行就可以了：

SELECT
    c.* 
FROM
    comments AS c
    JOIN treepaths AS t ON c.comment_id = t.ancestor 
WHERE
    t.descendant = 6;

要插入一个新的叶子节点，比如评论#5的一个子节点，应首先插入一条自己到自己的关系，然后搜索 treepaths 表中后代是评论#5的节点，增加该节点和新插入节点的“祖先—后代”关系（包括评论#5的自我引用）：

{int} 为新增的 comment_id
INSERT INTO comments (author,comment_date,bug_id, comment) VALUES ('Ollie','2021-01-11', 1,'Good job!');

INSERT INTO treepaths ( ancestor, descendant ) SELECT
t.ancestor,
{int} 
FROM
    TreePaths AS t 
WHERE
    t.descendant = 5 UNION ALL
SELECT
    {int},
    {int};

要删除一个叶子节点，比如评论#7，应删除所有 treepaths 表中后代为评论#7的行：

DELETE FROM treepaths WHERE descendant = 7;

要删除一棵完整的子树，比如评论#4和它所有的后代，可删除所有在 treepaths 表中后代为#4的行，以及那些以评论#4的后代为后代的行：

DELETE 
FROM
    treepaths 
WHERE
    descendant IN ( SELECT descendant FROM ( SELECT descendant FROM treepaths WHERE ancestor = 4 ) AS b );

闭包表的设计比嵌套集更加地直接，两者都能快捷地查询给定节点的祖先和后代，但是闭包表能更加简单地维护分层信息。这两个设计都比路径枚举更方便地查询给定节点的直接后代和父代。

然而，你可以优化闭包表来使它更方便地查询直接父亲节点或子节点：在 treepaths 表中增加一个 path_length 字段。一个节点的自我引用的 path_length 为0，到它直接子节点的 path_length 为1，再下一层为2，以此类推。查询评论#4的子节点就变得很直接：

SELECT *
FROM treepaths
WHERE ancestor = 4 AND path_length = 1;

总结

路径枚举能够很直观地展示出祖先到后代之间的路径，但同时由于它不能确保引用完整性，使得这个设计非常地脆弱。枚举路径也使得数据的存储变得比较冗余。

嵌套集是一个聪明的解决方案——但可能过于聪明了，它不能确保引用完整性。最好在一个查询性能要求很高而对其他需求要求一般的场合来使用它

闭包表是最通用的设计，它要求一张额外的表来存储关系，使用空间换时间的方案减少操作过程中由冗余的计算所造成的消耗。

如何创建一张规范的MySQL表
上一篇
MySQL 连接数过多的处理方法合集 - Too many connections
下一篇