那天二面面试官问我的题目是设计一个“评论盖楼”系统。我当时脑子里第一反应就是递归因为楼中楼这个结构本质就是树。我噼里啪啦说了一堆“递归查子评论啊”“递归删除子树啊”说完还挺得意。面试官笑了笑反问一句“你递归查的时候SQL是啥索引建了吗”我当场卡住了。递归我是知道可具体到MySQL里怎么查、怎么建索引脑子里全是浆糊。面完整场回去复盘我才意识到这道题根本不是考递归而是考你有没有把“数据结构—SQL写法—索引设计”串成一条线。只答“递归”就好比面试官问怎么做菜你说要用锅但用什么锅、火多大、先放油还是先放盐全都没交代那这顿饭没人敢让你做。所以这篇就好好聊聊评论盖楼系统到底该怎么设计。我会从表结构开始把四种存储方案对比清楚再把索引怎么建、递归怎么查、深翻页怎么办这些点挨个讲透。无论你是准备面试还是真要在业务里做一套楼中楼这篇文章都值得看完。1. 评论盖楼为什么不能只靠递归1.1 我们先说清楚这个系统到底要干什么评论盖楼也叫楼中楼、嵌套评论。你刷B站、逛小红书、看知乎评论区里那种能一层一层展开的回复结构就是这套系统。表面上看起来只是“子评论挂在父评论下面”但真实业务需求远不止这点支持任意层级的嵌套不能只做两层否则用户没法聊“在吗—在—好的—欢迎”这种连续对话。首页通常只展示顶级评论用户点“展开”才加载这个顶级评论下的整棵子树。删除一个评论时它下面的所有子评论也要一起处理不然会冒出很多“孤儿节点”。展示顺序很复杂楼层可能按时间排也可能按点赞数排。单条顶级评论下的子评论数量可能非常大像热门视频第一热评下面几千条回复很常见不能一次性全查出来。把这些需求摆到桌面上就会发现递归确实绕不开因为你面对的就是一棵树父评论是树根子评论是叶子。树的遍历天然适合递归“查某个节点下的所有后代”这句需求翻译过来就是深度优先遍历。1.2 但“递归”只是手段不是方案问题在于很多候选人答到这里就停了。我当时的回答就是典型的反面例子只说了“我用递归去查子评论”但完全没有回答面试官真正关心的几个问题评论数据存在哪个表字段怎么设计递归查询在MySQL里写出来长什么样每次递归都查一次数据库还是内存里拼如果每层评论都要从数据库查一遍这个查询怎么走索引万一评论嵌套了几百层会不会把数据库打爆一句话总结递归是“怎么算”的思路但面试官更想听“算什么”“东西存在哪”“怎么把数据高效捞出来”。后面这三个问题全部要落到表结构和索引上。我后来复盘那个面试官的笑里其实是有一点恨铁不成钢的他是想引导我去想索引因为一个危险的数据库查询往往就是递归导致的——每递归一层就执行一条SQL如果这条SQL没走索引数据量一大系统直接就雪崩了。2. 评论表怎么设计四种存储模型选哪个2.1 邻接表最直观也是大多数人的默认答案所谓邻接表就是一张评论表里有id、parent_id两个字段顶级评论的parent_id是0或者NULL子评论指向父评论。这是最经典的“树”表示法也是我那次面试答的第一个方案。CREATE TABLE comment ( id bigint unsigned NOT NULL AUTO_INCREMENT, article_id bigint unsigned NOT NULL COMMENT 文章ID, parent_id bigint unsigned NOT NULL DEFAULT 0 COMMENT 父评论ID0表示顶级评论, content varchar(2000) NOT NULL COMMENT 评论内容, user_id bigint unsigned NOT NULL COMMENT 评论人ID, like_count int unsigned NOT NULL DEFAULT 0 COMMENT 点赞数, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), KEY idx_article_parent (article_id, parent_id), KEY idx_article_time (article_id, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;邻接表的优点是真的简单插入一条评论只需要知道父评论ID不管多深的楼层都只需写一行数据业务代码几乎不用多花心思。但它的查询弱点同样明显要查一棵完整的子树如果只知道根节点你得先查第一层子节点再拿这一层的结果去查第二层层层推进。写过这类代码的人都体会过那个“攒了一大堆ID再IN一把”的酸爽。2.2 路径枚举把祖先关系直接写进字段路径枚举Path Enumeration的思路是给每条评论加一个path字段存从根节点到当前节点的完整ID路径比如0/100/200/300。这样一来你想查某棵子树直接WHERE path LIKE 0/100/%就能一次性捞出来无需递归。看起来很棒但代价很致命路径是字符串维护起来很麻烦。如果你要移动一条评论它下面所有子节点的path都要UPDATE一旦ID很大字符串长度也会失控LIKE0/100/%也没法用普通索引加速无法匹配前缀的中间部分。所以实际业务里用路径枚举做评论系统的非常少倒是用来做商品分类、权限树的场景偶尔能看到。2.3 嵌套集左右值模型查询飞快但写入会崩嵌套集用lft和rgt两个整数左右界来标记每个节点在树里的位置给整棵树做一次深度优先遍历每进入一个节点就lft离开时再rgt。最后的视觉效果是每个节点都包住它所有子节点。查一棵子树时只需WHERE lft 父.lft AND lft 父.rgt查询效率极高。不过这个模型有两个硬伤第一每插入一个评论都可能要调整一大批节点的左右值写入成本极高而评论系统恰恰是写入极频繁的系统第二左右值和业务完全不直观排查数据非常反人类。我在真实的业务项目里基本只会在“数据基本不变、查询极重”的分类树场景考虑嵌套集评论系统直接排除。2.4 闭包表空间换时间的狠角色闭包表的做法是额外建一张关系表comment_tree里面每一行存一个“祖先节点—后代节点”对不管中间隔了多少层只要存在祖先关系就记录下来。比如2是5的爸爸5是9的爸爸那comment_tree里会有(2,9)这一行。这样查“节点2下的整棵子树”就变成一条简单等值查询SELECT descendant_id FROM comment_tree WHERE ancestor_id 2。配合索引查询性能和速度都非常理想。代价是写入时要一次性插入多行从根到当前节点的所有祖先都得各来一条删除和移动也更麻烦而且表会膨胀。2.5 四种方案横向对比方案插入评论查整棵子树查直接子节点删除子树空间占用适用场景邻接表O(1)极快需要递归层数多则代价高一条SQL索引快需要递归或软删除小评论、回复等动态树路径枚举快一条写入前缀匹配一次性查出需要处理path末尾容易中分类、静态多级目录嵌套集可能触发大量节点更新范围查询极快复杂相对简单但更新多小静态树、极多读少写闭包表需插入多行慢等值查询极快需要distinct或深度过滤需删除多行大读极多、层级深但改动少的场景我的建议很直接通用评论系统第一选择永远是邻接表。原因是它最简单可靠插入和更新都方便配合MySQL 8.0的递归CTE后查询的短板也能补上。如果你的数据量到了千万级、单根节点下有几万条子评论、评论区几乎只读再考虑用闭包表做一层“离线汇总”数据而不是一开始就上复杂模型。面试时如果你能主动说出“我会用邻接表为主数据量上来后用闭包表做读优化”这已经比只答一个“递归”高了一个段位。3. 索引设计才是这道题的核心考点3.1 连最基础的 parent_id 索引都想不起才是致命伤回到开头那个场景面试官说“你连索引都不会建”通常指的就是parent_id。邻接表模型下查子评论的SQL长这样SELECT * FROM comment WHERE parent_id 5;如果parent_id上没有任何索引MySQL只能对这个表做全表扫描。一张1000万行的评论表为了查出某个父节点下的30条子评论可能要扫掉全部数据这就是灭顶之灾。所以第一反应应该是parent_id必须建索引。单列索引用的是KEY idx_parent (parent_id)如果文章维度查询更常用更优的选择是多列复合索引比如KEY idx_article_parent (article_id, parent_id)。3.2 复合索引的顺序怎么定先看等值条件再看区分度日常业务里查评论的SQL往往有两个条件比如“查文章A下面父评论ID为5的所有子评论”SELECT * FROM comment WHERE article_id 12345 AND parent_id 5 ORDER BY created_at ASC;这种SQL的索引怎么建很多人会犹豫(article_id, parent_id)还是(parent_id, article_id)。其实判断标准就两条等值条件优先、区分度高者优先。article_id的区分度通常远高于parent_id一篇文章里的顶级评论可能有几百条但所有文章里parent_id 5的只有少数几条更关键的是如果只按parent_id找MySQL必须先扫描全表里所有parent_id 5的数据再在内存里过滤article_id效率远不如先按article_id精确定位到一篇文章再在这篇文章内部的索引树上查parent_id。所以复合索引的左侧应该放article_id二级索引结构变成 (article_id, parent_id)。这样对一条SQL来说先走等值定位到文章再在索引树里继续走parent_id每一步都是B树的有序查找。如果你确实存在“只查某条父评论下所有子评论、不关心文章”的场景那单独的parent_id索引还是有必要的。记住一个原则复合索引能覆盖“文章父评论”查询但纯等值parent_id查询走不了以article_id开头的复合索引必须单独建。3.3 覆盖索引查询性能提升的隐藏加分项再看刚才那条SQL它SELECT *是直接回表拿完整评论内容的。如果业务展示只需要id、user_id、content、created_at这些字段我们可以把索引设计成能覆盖查询的形态KEY idx_tree_query (article_id, parent_id, id, created_at, user_id)这句索引建好后MySQL可以直接从二级索引里取到所有需要的字段连主键回表都省了。评论这种典型“读多写少”的业务覆盖索引的收益极其明显。唯一要克制的是不要把content这种2000字的大字段也塞进索引否则索引体积会失控反而拖垮写入和查询。3.4 分页查询到底用 limit 还是游标评论分页是另一个频繁踩坑的点。最常见的初级写法是LIMIT 100000, 20数据一多它就悲剧了——MySQL会先查出前100020行再丢掉前10万行前面那10万次回表、排序全部白干。这个问题的标准解法有两种一是游标分页利用索引有序性每次只取上次看到的最后一条记录位置。SELECT * FROM comment WHERE article_id 12345 AND parent_id 5 AND created_at 2024-05-01 10:30:00 ORDER BY created_at ASC LIMIT 20;这种写法能完美命中(article_id, parent_id, created_at)复合索引每一页只扫描20行性能非常稳。二是“ID偏移”法适合用自增主键按楼层排的场景直接把上一页最大ID作为条件。SELECT * FROM comment WHERE article_id 12345 AND parent_id 5 AND id 100000 ORDER BY id ASC LIMIT 20;如果你要按点赞数排序那就别想用索引硬抗了典型的解决方案是定时把热门评论的点赞数同步到一张冗余表或者走数据平台离线预计算而不是让线上数据库去扫百万行评论做排序。3.5 索引失效的几个常见坑现场就能问面试官可能会顺手追问“什么情况索引会失效”这块必须答得上。我把最容易被问到的几个场景整理一下失效场景示例原因对索引列使用函数WHERE DATE(created_at)2024-05-01函数导致索引列被转换索引树无法匹配隐式类型转换WHERE parent_id 5而parent_id是数值类型不一致MySQL会做转换索引失效复合索引前导列缺失索引是(article_id, parent_id)条件只有parent_id跳过了最左列无法用联合索引LIKE 通配符开头WHERE content LIKE %你好%前缀不固定无法用索引树定位OR 连接非索引条件WHERE parent_id 5 OR user_id 999无法统一走单个索引常见优化是OR改UNION这些内容你不用死记核心逻辑就一条索引就是字典的拼音目录你得按目录的顺序和原始形式去查一旦给字改了形、跳了目录页它就查不动了。4. 递归查询在 MySQL 里到底怎么写4.1 基础数据准备讲完存储和索引我们动手写一条真正的递归查询。假设评论表里已经有下面这些数据评论1是顶级评论评论2、3挂在1下面评论4挂在2下面评论5挂在4下面评论6挂在3下面。对应到表里的parent_id分别是INSERT INTO comment (id, article_id, parent_id, content) VALUES (1, 1000, 0, 热评第一), (2, 1000, 1, 回复1顶一个), (3, 1000, 1, 回复1博主说得对), (4, 1000, 2, 回复21), (5, 1000, 4, 回复4层中楼), (6, 1000, 3, 回复3我也这么觉得);4.2 MySQL 8.0 的 WITH RECURSIVE 用法MySQL 8.0引入了公用表表达式CTE结合WITH RECURSIVE可以优雅地完成递归查询。查评论1下面所有子孙评论的SQL写法如下WITH RECURSIVE comment_tree AS ( -- 锚点查询先找到根节点 SELECT id, parent_id, content, 1 AS depth FROM comment WHERE id 1 UNION ALL -- 递归查询用上一轮结果查下一层 SELECT c.id, c.parent_id, c.content, ct.depth 1 FROM comment c INNER JOIN comment_tree ct ON c.parent_id ct.id ) SELECT * FROM comment_tree;这段SQL看起来简单但背后有几件事值得你仔细品味第一UNION ALL不是UNION不要漏写否则MySQL会对结果做去重不仅多出排序开销还可能打乱楼层顺序。第二comment_tree这个名字在递归部分会被反复引用每一轮查询都会拿上一轮生成的行作为连接条件这就是递归展开的过程。第三CTE的递归深度默认上限是1000取决于max_recursion_depth参数如果不加限制遇到脏数据形成环时SQL会一直跑到底甚至报错。4.3 限制递归深度和环路说再见评论系统本身不该有环但现实里总会出现各种数据脏了的情况。一个稳妥的办法是在递归SQL里显式限制深度既能防环路也能防止用户恶意构造超深楼层打爆数据库。WITH RECURSIVE comment_tree AS ( SELECT id, parent_id, content, 1 AS depth FROM comment WHERE id 1 UNION ALL SELECT c.id, c.parent_id, c.content, ct.depth 1 FROM comment c INNER JOIN comment_tree ct ON c.parent_id ct.id WHERE ct.depth 20 ) SELECT * FROM comment_tree;加一个depth 20的过滤条件即使哪条数据不小心形成了环递归到20层也会稳稳停下。你要是真想彻底防止环可以通过建表约束、写入前检查父评论是否存在、定期跑脚本扫描环路三种方式组合。不要指望一条SQL解决所有问题数据库本身没有内建的树结构约束。4.4 别把所有树都丢给SQL应用层递归也是正解很多场景下我不会在MySQL里做深递归尤其是当评论系统已经有缓存、消息队列等基础组件时。推荐的做法是一次把一个顶级评论下的所有子评论ID查出来扔到应用层拼树。具体思路分三步用一条SQL查出某个顶级评论下的全部节点这一步在数据量可控时就是WHERE parent_id 根ID配合多个层级的ID收集或者直接用递归CTE。把查出来的行全部放到内存里利用parent_id做一次哈希索引映射构建一棵评论树。业务侧控制展示层级比如最多显示三层第四层折叠成“查看全部回复”。这样做的好处非常明显数据库只承受一次查询压力递归发生在应用内存里完全不消耗数据库连接对热门评论还可以顺手加一层Redis缓存缓存里存拼装好的树JSON命中就直接返回。我做过一个数据量比较大的社区系统线上热评的展开就是这么玩的Redis扛下了超过90%的读请求数据库的压力小了很多。5. 高频追问与踩坑清单千万别在细节里翻车5.1 删除评论的正确姿势软删除优于物理删除面试官常追问“删掉一条父评论怎么办”。如果你傻乎乎说“DELETE FROM comment WHERE id 父ID”那就掉坑了。子评论还在但它的parent_id已经找不到爹了前端一渲染就是悬空节点。我的建议是用软删除。给评论表加一个deleted字段默认0删的时候改成1。查询时永不删除数据只是在SQL条件里加上deleted 0。展示端可以用“该评论已删除”占位它的子评论依然完整挂在树上。这么做虽然表会越来越大但配合定期归档、冷热分离整体可控。如果真的需要物理删除那先用WITH RECURSIVE查出所有后代ID再批量DELETE顺序是从叶子往根删否则外键关系会先闹脾气。5.2 评论的展示顺序楼层按时间但从第二层起按点赞数评论排序是产品需求里最拧巴的点。常见组合是第一层顶级评论按热度或时间排展开后的第二层及更深层按点赞数降序排。这种需求改成SQL就复杂了因为你不能只在一个ORDER BY里同时满足“顶级按时间、子孙按点赞”。我常用的做法是先在树结构层面保证父子关系完整最后在应用层排一次序。具体来说递归CTE查完整棵子树后按depth分组再对每个非顶级层级按like_count DESC排序。如果数据量特别大那就不实时排干脆做成“热门楼层”预计算表靠离线任务刷新线上只查表。5.3 新版 MySQL 和老版本的区别有一点必须提醒MySQL 5.7以及更早版本是不支持WITH RECURSIVE的。如果你还在用老版本递归查询只能靠存储过程、循环查库或者应用层递归。这也是我在面试时一定会问清楚“你们的MySQL版本”的原因。千万别满嘴跑火车地推荐CTE结果对方公司还是5.7那就露馅了。遇到老版本大方地说“我这边会用应用层递归代替”顺便给出批量查询和内存拼树的方案反而显得你更有实战经验。5.4 评论表的写入怎么优化批量插入与事务控制评论系统的写入并不像想象中那么轻热门视频上线后高峰期一秒钟来几千条评论很正常。这时候每条评论都单独INSERTTPS就会非常难看。优化的方向一个是合并写入前端在用户快速连续发评时把多条评论合并成一次SQL的多值INSERT另一个是异步化先写消息队列由消费端批量落库主线程立刻返回“评论成功”。但异步写入有一个隐患用户刚发完评论刷新可能因为延迟没有立刻看到。对评论这种强一致体验不高的场景异步其实可以接受但你要想清楚产品预期。5.5 千万别忽视缓存与读扩散最后讲一个评论系统最容易踩的扩展性坑热门评论的子树查询天然是读热点。如果所有人的“展开回复”都实时打到MySQL即使你有索引单库单表也会被高频读打到瓶颈。经典解法是两级缓存一级用Redis保存“顶级评论ID列表”按文章的评论楼层排序控制首页只展示前N条热评二级用Redis保存“某条顶级评论下整个子树JSON”设置过期时间比如5分钟热点期间反复命中缓存过期后重新从MySQL加载。还要注意缓存和数据库之间的数据一致性。我的经验是不要追求那种毫秒级的缓存同步因为评论本身带有社交的时间属性大家默认“刚发的评论可能延迟几秒出现”是可以接受的。你只要设置合理的TTL再在写入时主动删除对应文章的缓存key就能保证最终一致。6. 面试现场怎么答才不会被面试官“笑”6.1 用“需求→模型→查询→索引”四步框架去组织答案复盘这次面试我给大家一个特别实用的回答框架按这个顺序说面试官想打断你都难。第一步确认需求。主动问“这套系统是只展示两层还是无限楼中楼评论量预计多大要不要支持删除子树”这些问题能体现你的思考深度。第二步选型。根据需求说明用邻接表来存储因为插入评论只需要一条记录和业务天然贴合。第三步查数据。说明查询分两种查直接子节点用一条等值SQL查整棵子树用MySQL 8.0的递归CTE或应用层递归。第四步谈索引。这一步必须主动说“我们的查询条件是article_id和parent_id所以核心索引要建复合索引(article_id, parent_id)排序时配合(article_id, created_at)分页用游标方式避免深翻页”说完这段面试官基本就点头了。6.2 主动抛出优化点展示“系统思维”在回答了基础方案后再主动补两个优化点非常加分。一个是“删除用软删除保留整棵树结构避免孤儿节点”。另一个是“读场景重开发时优先用覆盖索引避免回表热点评论区引入Redis做子树缓存DB只做兜底”。这两个方案都不复杂但它们说明你不是只盯着一条SQL而是把整个系统从数据流到存储再到缓存放进了一体考虑。6.3 最后送上几句压箱底的话我自己这几年做后端的一个最深的体会是面试里遇到树形结构题千万别急着写递归。先画表结构再写最核心的两三条查询SQL最后思考索引怎么建。只要这三者能自洽成一个闭环这套方案在线上才是真正跑得起来的。如果你已经看过这篇文章再有人问你“评论盖楼怎么设计”至少你已经知道答案的层次邻接表打底、递归CTE做深度查询、复合索引保证每个查询都不全表扫描、软删除保住树形完整性、缓存扛住热门评论的读压力。能把这五句话逻辑自洽地讲清楚你已经不是那个只会说“递归”的人了。