评论表的落地方案（SQL反设计读书笔记）

---

一、评论样式

1. 列表式评论区

● 时间顺序：
○ 微博：评论按时间顺序排列，最新的评论显示在最上方。
○ 知乎：回答的评论按时间顺序排列，最新评论显示在最下方。
● 热度排序：
○ 小红书：评论区默认按热度排序，但用户可以选择按时间顺序排列。
○ 淘宝：商品评论可以按“默认”、“最新”、“追评”等排序。
● 嵌套式：
○ 知乎：评论和回复呈现嵌套结构，可以展开和收起回复。

2. 卡片式评论区

● 大众点评：每条评论显示在独立的卡片中，包括用户信息、评分、评论内容、点赞按钮等。
● 微信读书：书籍的评论显示在卡片中，包括用户评分、评论内容和附加的图片或视频。

3. 弹幕式评论区

● Bilibili：视频播放过程中，评论以弹幕形式实时滚动显示。
● 斗鱼：直播视频的弹幕评论实时显示。

4. 评分和评论结合

● 豆瓣：用户可以对电影、书籍进行评分并写下文字评论，评分和评论显示在一起。
● 美团：用户可以对餐馆、服务进行评分并写下评论，评分和评论结合显示。

5. 主题和标签分类

● 知乎：问答评论按话题分类，用户可以选择特定话题下的回答和评论。
● 豆瓣：书籍、电影评论可以按照不同的分类查看。

6. 匿名评论区

● 知乎匿名区：用户可以匿名发布问题和回答。

7. 多语言评论区

● 阿里巴巴国际站：支持多语言评论，用户可以选择不同语言查看评论，或使用自动翻译功能。
然而上述网站其实大多数评论场景都是复合场景

二、评论场景

如果谈解决方案那么必然涉及到业务场景
业务场景： 一个网站，读者可以评论原文甚至相互回复,类似社区评论

CREATE TABLE Comments (
 comment_id SERIAL PRIMARY KEY,
 parent_id BIGINT UNSIGNED,
 comment TEXT NOT NULL,
 FOREIGN KEY (parent_id) REFERENCES Comments(comment_id)
);

书中有两个解决方案
● 每条评论引用它所回复的评论
● 先获取一个主题的所有评论，再整合数据
第一个写着写着会发现要检索一个很长的回复分支很难很难，只能获取到固定的深度，如果这个帖子无限深那么就可能需要执行很多次SQL查询才能获取给定主题的所有评论。
第二个当量级多了之后你先先读取出所有评论也是非常耗时且不显示的。
数据常会像树或者以层级方式组织

三、评论解决方案

最常见的简单解决方案是添加parent_id字段，引用同一张 表中的其他回复。可以建一个外键约束来维护这种关系。

这种做法叫做邻接表，大概是这样的结构

反模式

但其实这是一个反模式因为它无法完成书树操作的： 查询一个节点的所有后代。

SELECT c1.*, c2.*
FROM Comments c1 LEFT OUTER JOIN Comments c2
 ON c2.parent_id = c1.comment_id;

这个查询只能获取两层的数据，且即使增加一个联结也至多获取到四层，并且还把sql搞得很麻烦。

SELECT c1.*, c2.*, c3.*, c4.*
FROM Comments c1 -- 1st level
 LEFT OUTER JOIN Comments c2
 ON c2.parent_id = c1.comment_id -- 2nd level
LEFT OUTER JOIN Comments c3
 ON c3.parent_id = c2.comment_id -- 3rd level
LEFT OUTER JOIN Comments c4
 ON c4.parent_id = c3.comment_id; -- 4th level

倘若你再执行一个COUNT()那将是灾难。
那么既然这样麻烦的话那我通过先查询出所有行然后再重新聚合呢。
同理也是低效的因为存在大量的复制并且有可能你只需要一个子树，并不需要这么庞大的树。
真的一点优点都没有了吗这个方案？当然不是。对于树来说，新增和修改则会异常简单。但是对于删除来说有些糟糕。那么这种邻接表适用在哪里呢？

邻接表设计的优势在于能快速地获取一个给定节点的直接父子节点，它也很容易插入新节点。如果这样的需求就是你的应用程序对于分层数据的全部操作，那使用邻接表就可以很好地工作了。

如何识别？

● 当需要思考多少层树结构能满足这些需求的时候
● 觉得管理这些分层数据很麻烦
● 需要定期清理这些节点数据

其他树模型

1. 路径枚举

邻接表的缺点之一是从树中获取一个给定节点的所有祖先的开销很大

但是路径枚举是可以巧妙的解决这点，因为路径枚举是由连续的直接层级关系组成的完整路径如： /usr/local/lib 这是很典型的，其中usr是local的父亲，这也就意味着 usr是lib的祖先。

这样不仅可以快速的找到所有后代也可以找到祖先了。这样也可以方便计算一颗子树的总和或者单纯计算节点数。
先对应的缺点就是存储格式是VARCHAR长度有限制，并且需要逻辑代码来维护和验证字符串导致开销会很大。

2. 嵌套集
   嵌套集的解决方案是:是存储子孙节点的相关信息，而不是节点的直接祖先。
   用两个字段表示：nsleft和nsright.

CREATE TABLE Comments (
 comment_id SERIAL PRIMARY KEY,
 nsleft INTEGER NOT NULL,
 nsright INTEGER NOT NULL,
 bug_id BIGINT UNSIGNED NOT NULL,
 author BIGINT UNSIGNED NOT NULL,
 comment_date DATETIME NOT NULL,
 comment TEXT NOT NULL,
 FOREIGN KEY (bug_id) REFERENCES Bugs (bug_id),
 FOREIGN KEY (author) REFERENCES Accounts(account_id)
);

如何通过这两值来确认呢？
nsleft的数值小于该节点 所有后代的ID，同时nsright的值大于该节点所有后代的ID。

确定这三个值（nsleft，comment_id，nsrigh）的简单方法是对树进行一次深度优先遍历，在逐层深入的过程中依次递增地分配nsleft的值，并在返回时依次递 增地分配nsright的值。

优势在于操作单独的节点方便快捷一些。

当你想要删除一个非叶子节点时，它的后代会自动地代替被删除的节点，成为其直接祖先节点的直接后代。尽管每个节点的左右两 个值在示例图中是有序分配，而每个节点也总是和它相邻的父兄节点进行比较，但嵌套集设计并不必须保存分层关系。因而当删除一个节点造成数值不连续时，并不会对树的结构产生任何影响。

缺点在于查询起来很麻烦相比邻接表并且插入和移动节点也是不擅长的。

在嵌套集中，如果需要查询一 个节点的直接父亲，我们会这么做：给定节点c1的直接父亲是这个节点的一个祖 先，且这两个节点之间不应该有任何其他的节点，因此，你可以用一个递归的外联 结来查询一个节点x，它即是c1的祖先，也同时是另一个Y节点的后代，随后我们使 Y=x并继续查询，直到查询返回空，即不存在这样的节点，此时的Y便是c1的直接父亲节点。

嵌套表所应用的场景一定是简单快速地查询是整个程序中最重要的部分。

3. 闭包表
   闭包表是解决分级存储的一个简单而优雅的解决方案，它记录了树中所有节点间的关系，而不仅仅只有那些直接的父子关系。它需要额外的加一张表为TreePaths.

CREATE TABLE Comments (
 comment_id SERIAL PRIMARY KEY,
 bug_id BIGINT UNSIGNED NOT NULL,
 author BIGINT UNSIGNED NOT NULL,
 comment_date DATETIME NOT NULL,
 comment TEXT NOT NULL,
 FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
 FOREIGN KEY (author) REFERENCES Accounts(account_id)
);
CREATE TABLE TreePaths (
 ancestor BIGINT UNSIGNED NOT NULL,
 descendant BIGINT UNSIGNED NOT NULL,
 PRIMARY KEY(ancestor, descendant),
 FOREIGN KEY (ancestor) REFERENCES Comments(comment_id),
 FOREIGN KEY (descendant) REFERENCES Comments(comment_id)
);

这个时候评论表不在存储树结构了，而是讲树中的任何祖先和后代的关系存到TreePaths里的一行，即使不是父子关系则可以指向自己。

对于闭包表来说无论是获取，新增，删除都是比较快速的。
但是要注意 删除了TreePahts中的一条记录，并不是真正删除了这条评论。

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

最后闭包表可以优化方便更好的查询

TreePaths表中增加一个path_length字段。一个节点的自我引用的path_length为0，到它直接子节点的path_length为1，再下一层为2，以此类推。

真·落地方案？

● 如果你使用的数据库支持WITH或者CONNECT BY PRIOR的递归查询，那能使得邻接表的查询更为高效。
● 枚举路径能够很直观地展示出祖先到后代之间的路径，但同时由于它不能确保 引用完整性，使得这个设计非常地脆弱。枚举路径也使得数据的存储变得比较冗余。
● 嵌套集是一个聪明的解决方案——但可能过于聪明了，它不能确保引用完整 性。最好在一个查询性能要求很高而对其他需求要求一般的场合来使用它。
● 闭包表是最通用的设计，并且本章所描述的设计中只有它能允许一个节点属于 多棵树。它要求一张额外的表来存储关系，使用空间换时间的方案减少操作过 程中由冗余的计算所造成的消耗。

从上面再来思考落地方案。
场景： 评论表只分一级和二级 二级评论只一级。
那么这种就趋近于邻接表，再这个表了每个评论（节点）会存储其父节点的ID，从而形成一种父子关系。一级评论没有父节点，而二级评论会存储其对应的一级评论的ID。

其他

这里其实更多的是评论的基本功能的实现，实际上在开发中需要考虑的太多，例如内容是否脱敏，热评，置顶，评论的类型，敏感词的设置，ai审查，网安，扩展的功能还有点赞数量等。