评论系统看起来简单，无非是用户对文章发表看法，但真正动手设计数据库时就会发现坑不少：嵌套回复怎么存、评论数统计要不要实时更新、被删除的评论其子评论如何处理、分页查询在数据量大时会不会拖垮性能。SQLite作为嵌入式数据库，不需要单独部署服务，一个文件就是一个库，特别适合个人博客、小型站点这类场景。这篇文章就拿SQLite来设计一套完整的博客评论数据库，从表结构、索引、查询到并发写入优化，把整个思路讲透。
一、核心表结构设计
先明确系统里有几个实体：用户、文章、评论。评论本身可能又是被评论的对象（回复），这就是设计的关键点。我倾向用四张表来组织：users存用户基础信息，posts存文章，comments存评论主体，评论的点赞、举报等行为单独放一张comment_likes表。职责拆开的好处是，后续要加收藏、举报功能时不用动核心表结构。
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
nickname TEXT NOT NULL,
email TEXT,
avatar TEXT,
created_at TEXT DEFAULT (datetime('now', 'localtime'))
);
CREATE TABLE posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
slug TEXT UNIQUE NOT NULL,
content TEXT,
published_at TEXT,
created_at TEXT DEFAULT (datetime('now', 'localtime'))
);
CREATE TABLE comments (
id INTEGER PRIMARY KEY AUTOINCREMENT,
post_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
parent_id INTEGER,
root_id INTEGER, -- 根评论id，用于楼中楼归组
content TEXT NOT NULL,
status INTEGER DEFAULT 1, -- 1正常 0删除 2审核中
like_count INTEGER DEFAULT 0,
created_at TEXT DEFAULT (datetime('now', 'localtime')),
FOREIGN KEY (post_id) REFERENCES posts(id),
FOREIGN KEY (user_id) REFERENCES users(id),
FOREIGN KEY (parent_id) REFERENCES comments(id)
);
CREATE TABLE comment_likes (
comment_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
created_at TEXT DEFAULT (datetime('now', 'localtime')),
PRIMARY KEY (comment_id, user_id)
);几个字段值得说明。parent_id指向被回复的那条评论，为空表示这是对文章的直接评论；root_id指向整个回复串最顶层的评论id，它的作用是查询楼中楼时可以一次把同组回复捞出来，不用递归。status用整数而不是文本，省空间且比较速度快。时间字段用TEXT配合SQLite内置的datetime('now', 'localtime')，存的是ISO格式字符串，天然支持字符串排序，比存时间戳直观得多。
二、嵌套回复的两种方案对比
回复结构是评论系统设计的核心分叉点，常见做法有两级评论和无限层级两种。两级评论是指文章下挂一级评论，一级评论下挂回复，回复不再有下级。这种模型前端渲染简单，数据查询也直接，主流博客系统（如WordPress默认模式）大多采用它。无限层级则允许回复回复的回复，形成树状结构，讨论氛围更强，但查询和渲染复杂度明显上升。
在SQLite里实现无限层级有几种路子。最朴素的是递归查询，SQLite从3.8.3版本开始支持WITH RECURSIVE，可以直接递归出整棵子树：
WITH RECURSIVE tree AS (
SELECT id, parent_id, content, created_at, 0 AS depth
FROM comments
WHERE id = 100
UNION ALL
SELECT c.id, c.parent_id, c.content, c.created_at, tree.depth + 1
FROM comments c
JOIN tree ON c.parent_id = tree.id
)
SELECT * FROM tree ORDER BY depth, created_at;递归查询可读性好，但每个一级评论都要走一次递归，列表页数据一多效率就下来了。另一种是闭包表方案，额外建一张comment_tree表存所有祖先和后代的关系对，查询子树变成一次等值查找，代价是写入时维护成本高。对于博客场景，我更推荐前面表结构里的折中方案：parent_id记录直接回复对象，root_id记录根评论，前端按楼中楼展示，最多两级视觉层级，数据上却能表达任意回复关系，兼顾了简洁与灵活。
三、索引设计与分页查询优化
SQLite默认只在主键上有索引，外键字段不会自动建索引，所以post_id、parent_id、root_id这几个高频查询条件必须手动加索引。索引不是越多越好，每加一个都会拖慢写入，所以要按真实查询模式来建：
-- 文章详情页取一级评论列表，按时间倒序分页
CREATE INDEX idx_comments_post_root
ON comments(post_id, root_id, created_at DESC);
-- 楼中楼取某根评论下的所有回复
CREATE INDEX idx_comments_root
ON comments(root_id, created_at);
-- 点赞表反查用户点过哪些赞
CREATE INDEX idx_likes_user
ON comment_likes(user_id);分页写法上要避免LIMIT 20 OFFSET 10000这种深分页，offset越大扫描的行越多。用游标分页（也叫键集分页）更高效：记录上一页最后一条评论的id或时间，下一页直接用条件过滤：
-- 第一页 SELECT c.id, c.content, c.created_at, u.nickname FROM comments c JOIN users u ON u.id = c.user_id WHERE c.post_id = 42 AND c.root_id IS NULL AND c.status = 1 ORDER BY c.id DESC LIMIT 20; -- 下一页，假设上页最后一条id为1580 SELECT c.id, c.content, c.created_at, u.nickname FROM comments c JOIN users u ON u.id = c.user_id WHERE c.post_id = 42 AND c.root_id IS NULL AND c.status = 1 AND c.id < 1580 ORDER BY c.id DESC LIMIT 20;
评论总数如果每次都用COUNT(*)实时统计，文章多了会成为慢查询。可以学习主流站点的做法：只显示"500+"这样的粗略数字，或者用触发器把计数维护到posts表的冗余字段里，读取时一步到位。
四、SQLite并发写入的局限与应对
SQLite整个库是一个文件，写操作是库级锁，同一时刻只允许一个写入者。博客评论的写入频率一般不高，这个问题不突出，但如果站点流量上来、多人同时提交评论，就可能碰到database is locked的错误。应对手段有几个层面：连接时开启WAL模式，把锁粒度从读写互斥改成读写可并存；设置busy_timeout让写入请求排队等待而不是立刻报错。
PRAGMA journal_mode = WAL; -- 写前日志模式，读写并发更好 PRAGMA busy_timeout = 5000; -- 遇锁等待5秒再报错 PRAGMA synchronous = NORMAL; -- WAL模式下兼顾安全与速度
应用层还可以把评论写入放进单一队列串行处理，避免多个连接同时争抢写锁。如果评论量真的到了每秒几百条的级别，那就是该考虑迁移到PostgreSQL或MySQL的时候了，SQLite的定位就是中小型应用的零运维方案，认清边界比强行优化更重要。另外别忘了定期备份：VACUUM INTO 'backup.db'可以在运行中安全导出一份完整快照，配合定时任务就能实现低成本的数据保障。
这套设计在个人博客项目里实测表现稳定，十万级评论量下文章详情页的查询依然能控制在几毫秒内。核心思路就三点：表结构围绕真实查询需求建模，索引配合最常用的过滤排序组合，写入瓶颈用WAL加串行队列化解。把SQL直接拿去建库就能跑起来，再根据自己站点的功能需求做加减即可。