做一个聊天功能，很多人第一反应是上MySQL或者MongoDB，其实对于单机部署的小型聊天系统、内网IM工具或者客户端本地缓存来说，SQLite是完全够用的选择。它零配置、单文件存储、事务支持完善，处理读写混合的聊天消息场景并不吃力。这篇文章就以一个实时聊天系统为例，完整讲讲如何用SQLite设计消息存储层，包括表结构、索引、分页查询和常见的统计需求。
一、消息表结构设计与核心字段
聊天系统最核心的表就是消息表。设计之前先明确需求：一条消息需要记录谁发的、发给谁、什么时间发的、消息内容是什么、当前处于什么状态（发送中、已送达、已读）。针对单聊场景，可以直接用sender_id和receiver_id两个字段来描述方向，这样查询未读数和会话记录都很直观。
下面是一份可以直接使用的建表语句，实际项目中把user_id换成你自己的用户体系即可：
CREATE TABLE chat_message (
id INTEGER PRIMARY KEY AUTOINCREMENT,
message_id TEXT NOT NULL UNIQUE, -- 客户端生成的唯一ID，用于去重
sender_id INTEGER NOT NULL, -- 发送者ID
receiver_id INTEGER NOT NULL, -- 接收者ID
msg_type INTEGER DEFAULT 1, -- 1文本 2图片 3语音 4文件
content TEXT, -- 消息内容，多媒体类型存URL
status INTEGER DEFAULT 0, -- 0发送中 1已送达 2已读 3已撤回
created_at INTEGER NOT NULL, -- 发送时间戳（毫秒）
UNIQUE (sender_id, receiver_id, message_id)
);这里有几个设计细节值得展开。message_id由客户端生成（比如UUID），配合UNIQUE约束可以在网络重试时天然去重，避免同一条消息被存储两次。created_at用毫秒时间戳而不是DATETIME字符串，一方面省空间，另一方面排序时直接走数值比较，性能更好。msg_type建议用整型枚举而不是字符串，聊天记录动辄几十万条，字段类型的微小差异累积起来很可观。
如果系统还要支持群聊，不建议另建一张群消息表，而是加一个conversation_id的概念，把单聊也抽象成一种会话。也就是引入conversation表，消息表通过conversation_id关联会话，单聊会话的成员就是两个人，群聊会话的成员是一群人。这种统一模型后期扩展性好得多。
二、索引设计与分页查询优化
聊天场景最频繁的查询有两个：拉取某个会话的历史消息、拉取会话列表。如果只依赖主键id，这两类查询都会慢。对于单聊模式，“查我和某人的聊天记录”涉及两个方向：我发给他的、他发给我的，一个普通的单列索引覆盖不了，需要建复合索引或者利用OR条件改写。
先看索引部分：
-- 覆盖两个方向的会话查询 CREATE INDEX idx_msg_sender ON chat_message(sender_id, receiver_id, created_at); CREATE INDEX idx_msg_receiver ON chat_message(receiver_id, sender_id, created_at);
有了这两个索引，无论是我发给谁的消息还是谁发给我的消息，都能走索引快速定位。拉取历史消息的分页SQL可以这样写：
-- 第一页，拉取A和B之间最新的20条消息
SELECT * FROM chat_message
WHERE (sender_id = 1001 AND receiver_id = 1002)
OR (sender_id = 1002 AND receiver_id = 1001)
ORDER BY id DESC
LIMIT 20;
-- 下一页，基于上一页最小id做游标分页
SELECT * FROM chat_message
WHERE ((sender_id = 1001 AND receiver_id = 1002)
OR (sender_id = 1002 AND receiver_id = 1001))
AND id < 18650
ORDER BY id DESC
LIMIT 20;注意这里用的是基于id的游标分页，而不是LIMIT 20 OFFSET 40。OFFSET分页在深分页时需要扫描并丢弃前面所有行，消息记录越多越慢；游标分页直接从上一条记录的id开始，性能恒定。id是自增主键且插入有序，天然单调递增，用它做游标非常合适。
另外提醒一点，SQLite的OR条件查询优化器处理得不错，但最好用EXPLAIN QUERY PLAN验证一下是否真的走了索引。如果发现走了全表扫描，可以用UNION ALL改写两个方向的查询，再在外层排序合并，通常能拿到更稳定的执行计划。
三、未读数统计与会话列表聚合
聊天界面顶部通常要展示会话列表，每个会话显示对方昵称、最后一条消息、未读数。这是聊天数据层最容易被写坏的地方，很多人在应用层循环查每条会话，N个会话就执行N次SQL，数据一多页面直接卡死。正确做法是用聚合SQL一次性算出来。
未读数统计比较直接，一条SQL搞定：
-- 统计用户1001的每个会话对方的未读数
SELECT sender_id,
COUNT(*) AS unread_count
FROM chat_message
WHERE receiver_id = 1001
AND status = 1
AND is_deleted = 0
GROUP BY sender_id;会话列表的“最后一条消息”聚合稍微麻烦，SQLite提供了窗口函数（3.25版本以后），可以用ROW_NUMBER来取每个会话最新的一条：
SELECT * FROM (
SELECT m.*,
ROW_NUMBER() OVER (
PARTITION BY CASE WHEN sender_id < receiver_id
THEN sender_id || '-' || receiver_id
ELSE receiver_id || '-' || sender_id END
ORDER BY id DESC
) AS rn
FROM chat_message m
WHERE sender_id = 1001 OR receiver_id = 1001
) WHERE rn = 1 ORDER BY id DESC LIMIT 50;PARTITION BY里用CASE把A发B和B发A归到同一个分区，保证会话的最后一条消息不管谁发的都能取到。如果SQLite版本低于3.25不支持窗口函数，退化方案是先查出每个会话的MAX(id)，再用IN子查询关联回消息表，虽然SQL长一点但兼容性更好。
四、写入性能与数据维护策略
实时聊天对写入的敏感性高于读取。SQLite默认每个事务落盘一次，如果每条消息单独一个事务，高频聊天场景下磁盘I/O会成为瓶颈。解决办法是在服务端做一个写入队列，把短时间内的多条消息合并到一个事务里批量提交，吞吐量能提升一个数量级。同时开启WAL模式，读写不再互相阻塞，读写并发的表现会明显改善：
PRAGMA journal_mode = WAL; -- 写前日志模式，读写并发更好 PRAGMA synchronous = NORMAL; -- WAL模式下兼顾安全与性能 PRAGMA cache_size = -8000; -- 使用8MB缓存 PRAGMA busy_timeout = 3000; -- 锁等待3秒，避免立即报错
数据膨胀是聊天数据库绕不开的问题。消息表只增不减，一年下来单表几百万条很正常。可行的策略有两种：一是归档，把超过一定时间的消息搬到按月分表的archive表中，主表保持轻量；二是软删除配合定期物理清理，给表加is_deleted标记，后台任务定期真正删除。对于撤回消息，不建议物理删除，改status为已撤回即可，这样会话双方的记录才一致。
最后别忘了定期执行ANALYZE更新统计信息，以及VACUUM压缩空间。VACUUM会锁表，一定要放在业务低峰期执行。如果聊天系统是移动端本地存储，这些维护操作放在应用启动或夜间静默时段触发即可。
整体来看，SQLite承担一个中小规模聊天系统的消息层是完全可行的，关键点在于表结构建模清晰、索引贴合查询模式、写入端做好批量合并。等单机真的撑不住了，再考虑迁移到MySQL这类独立数据库，而前期合理设计的表结构在迁移时几乎可以原样搬过去，这也是用SQLite起步的一个隐性优势。