当前位置：首页 > news >正文

用户评论系统的存储设计：从简单树形到复杂社交图谱的演进

news 2026/7/24 14:48:19

用户评论系统的存储设计：从简单树形到复杂社交图谱的演进

一、当"楼中楼"变成灾难：传统评论存储的坍塌

Reddit/贴吧式的"楼中楼"评论系统，看似简单实则反模式。一条热门帖子有3万条评论，其中前10条评论各有2000条回复。如果使用传统的邻接表模型（comment_id, parent_id），每加载一条评论的子评论都是一次自连接查询——加载前10条热门评论需要11次数据库查询。

更糟糕的是"展开更多"功能：用户点击"查看全部2000条回复"，这意味着一次递归CTE查询扫描2000行，还需要其中的200条再做递归展开。任何开发者面对这个SQL执行计划都会手心冒汗。

二、四种评论存储模型的演进

三、混合方案的实现

实际工程中采用"邻接表+物化路径+Redis缓存"的混合方案：

-- MySQL评论表（兼用邻接表和物化路径） CREATE TABLE comments ( id BIGINT PRIMARY KEY, post_id BIGINT NOT NULL, parent_id BIGINT DEFAULT 0, -- 0=一级评论 root_id BIGINT NOT NULL, -- 根评论ID path VARCHAR(1024) NOT NULL, -- 物化路径 '/1/3/7/' depth SMALLINT DEFAULT 0, -- 评论深度 user_id BIGINT NOT NULL, content TEXT NOT NULL, like_count INT DEFAULT 0, reply_count INT DEFAULT 0, -- 子评论数（冗余） is_pinned TINYINT DEFAULT 0, is_deleted TINYINT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_post_root (post_id, root_id, created_at), INDEX idx_post_hot (post_id, like_count DESC, created_at DESC), INDEX idx_parent (parent_id) ) ENGINE=InnoDB;

评论服务的核心实现：

@Service public class CommentService { private final JdbcTemplate mysql; private final RedisTemplate<String, Comment> redis; @Transactional public Comment addComment(Long postId, Long parentId, Long userId, String content) { Long rootId = 0L; String path = ""; int depth = 0; if (parentId != 0) { // 获取父评论的root_id和path Comment parent = getComment(parentId); if (parent == null) { throw new CommentException("父评论不存在: " + parentId); } rootId = parent.getRootId() > 0 ? parent.getRootId() : parentId; path = parent.getPath() + parentId + "/"; depth = parent.getDepth() + 1; // 限制嵌套深度（防止恶意无限嵌套） if (depth > 5) { // 超过5层的回复统一挂在第5层 depth = 5; // 替换路径为第5层祖先的路径 path = getAncestorPath(parent, 5); } } // 生成分布式ID long commentId = idGenerator.nextId(); String newPath = rootId > 0 ? path : "/" + commentId + "/"; try { mysql.update( "INSERT INTO comments (id, post_id, parent_id, root_id, " + "path, depth, user_id, content) VALUES (?,?,?,?,?,?,?,?)", commentId, postId, parentId, rootId > 0 ? rootId : commentId, newPath, depth, userId, content ); } catch (DataAccessException e) { throw new CommentException("评论插入失败", e); } // 异步更新计数和缓存 asyncExecutor.submit(() -> { mysql.update( "UPDATE comments SET reply_count = reply_count + 1 " + "WHERE id = ?", parentId ); // 清除相关缓存 String cacheKey = "comments:post:" + postId + ":root:" + rootId; redis.delete(cacheKey); }); return getComment(commentId); } public List<Comment> getComments(Long postId, String sortBy, int page, int size) { // 优先从Redis读取热门评论 String hotCacheKey = "comments:hot:" + postId + ":" + page; List<Comment> cached = redis.opsForList() .range(hotCacheKey, 0, -1); if (cached != null && !cached.isEmpty()) { return cached; } // Redis未命中，从MySQL加载 String orderClause; switch (sortBy) { case "hot": orderClause = "like_count DESC, created_at DESC"; break; case "new": orderClause = "created_at DESC"; break; default: orderClause = "like_count DESC, created_at DESC"; } // 先加载一级评论（parent_id=0） String sql = String.format( "SELECT * FROM comments WHERE post_id = ? AND parent_id = 0 " + "AND is_deleted = 0 ORDER BY %s LIMIT ? OFFSET ?", orderClause ); List<Comment> rootComments = mysql.query( sql, commentRowMapper, postId, size, (page - 1) * size ); // 为每条一级评论加载热门子评论 for (Comment root : rootComments) { List<Comment> replies = getHotReplies(root.getId(), 3); root.setHotReplies(replies); } // 缓存30秒 redis.opsForList().rightPushAll( hotCacheKey, rootComments.toArray(new Comment[0]) ); redis.expire(hotCacheKey, 30, TimeUnit.SECONDS); return rootComments; } private List<Comment> getHotReplies(long rootId, int limit) { String cacheKey = "comments:replies:" + rootId; List<Comment> cached = redis.opsForList() .range(cacheKey, 0, limit - 1); if (cached != null && !cached.isEmpty()) { return cached; } // 物化路径查询：加载root_id的直接子评论或热门前5条 String sql = """ SELECT * FROM comments WHERE root_id = ? AND parent_id != 0 AND is_deleted = 0 ORDER BY like_count DESC LIMIT ? """; List<Comment> replies = mysql.query( sql, commentRowMapper, rootId, limit ); redis.opsForList().rightPushAll( cacheKey, replies.toArray(new Comment[0]) ); redis.expire(cacheKey, 60, TimeUnit.SECONDS); return replies; } }