尧图网站建设 
nginx集群聊天室四项目的mysql数据库表的设置及代码做聊天室项目前面的nginx负载均衡、WebSocket集群通信都搞定之后最绕不开的一件事就是数据存储。用户消息、离线消息、好友关系、群组成员、会话记录这些东西不能全塞在Redis里Redis挂了就全没了而且做多节点集群部署每个节点的内存数据根本不同步。我在做nginx集群聊天室这个系列的时候数据库设计这一块其实是花了不少心思的踩的坑也不少。这篇我就把自己当时怎么设计这套MySQL表结构、怎么写建表语句和对应代码的过程完整捋一遍适合正在做同类集群项目、或者打算把单机聊天室往分布式方向改造的朋友参考。这次设计的目标很明确一套能把用户、好友、群组、消息、离线消息这几大块业务全部撑起来的MySQL表结构既能配合nginx做水平扩展又不能让SQL查询复杂到没法维护。下文会从表结构设计思路讲起再逐个给出建表SQL和配套的Java代码片段最后补充我自己实际运行中遇到的几个坑。1. 整体设计思路先拆业务域再决定表结构1.1 聊天室业务到底需要几张表单机版聊天室你可能只需要两张表一张存用户一张存消息。但集群聊天室不一样因为要支持好友单聊、群组群聊、离线消息推送、历史消息拉取这些场景对数据维度的要求是不同的。我在动手建表之前先把自己需要的业务能力逐条列出来了用户注册、登录、用户资料查询好友关系的建立、删除、查询群组的创建、加入、退出、解散单聊消息的发送、接收、历史记录群聊消息的发送、广播、历史记录用户不在线时消息的离线存储和上线后的拉取拆完之后表结构就非常清晰了。核心表是用户表、好友关系表、群组表、群组成员表、单聊消息表、群聊消息表外加一张离线消息表。后面三张表都属于消息域但查询频率和数据特征差别很大放一起反而不利于索引设计和分库分表。1.2 为什么要额外设计一张离线消息表一开始我也考虑过直接给消息表加一个已读状态字段比如is_read为0表示未读。但后来实测发现消息表的写入量会随着用户量增长迅速膨胀等到要按用户查未读消息时如果消息表没有正确的索引全表扫描会直接把数据库拖垮。更麻烦的是单聊消息表里一条消息只能有一条收件记录群聊消息表里一条消息可能对应几百上千个收件人用is_read字段表达群聊未读状态在逻辑上就非常别扭。所以我在设计时单独拆了一张offline_message表。每次生产消息落库时先判断收件人是否在线。如果不在线就把消息ID和收件人ID写进离线表等收件人上线之后消费掉再标记状态。这样做的好处是把消息内容和投递状态彻底解耦后续就算引入RabbitMQ或者Kafka做异步投递这张表也可以无缝衔接。1.3 外键到底要不要用这个是被问得最多的问题。我最终的结论是建表时不建物理外键只在逻辑层维护关联。原因有两个。第一是性能。集群聊天室的高频操作就是消息写入和用户关系查询MySQL在每次插入和更新时都要额外校验外键约束这种开销在单机小项目里感觉不出来但在高并发写入场景下会被明显放大。第二是分库分表的兼容性。随着用户量增长用户表和消息表大概率要做分片。物理外键要求关联的数据必须在同一个MySQL实例上否则查询直接报错。而逻辑外键配合应用层的事务控制反而可以更灵活地做数据迁移和拆分。提示不是所有项目都不建外键如果你的系统并发不高、规模稳定、并且团队对数据一致性要求非常刚性物理外键依然可以降低应用层代码的复杂度。我这个方案是为了集群场景做的取舍仅供参考。2. 核心表结构解析与建表实操2.1 用户表别把登录态和资料字段混在一起用户表是一张核心表设计时容易犯的错误是把密码、token、资料一股脑全塞进来。我的做法是把用户身份认证字段和用户资料字段放在同一张表里但尽量精简字段数量同时预留扩展空间。CREATE TABLE user ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 用户ID, username VARCHAR(64) NOT NULL COMMENT 用户名全局唯一, password_hash VARCHAR(128) NOT NULL COMMENT 密码哈希值, salt VARCHAR(32) NOT NULL COMMENT 加盐值, nickname VARCHAR(64) DEFAULT NULL COMMENT 昵称, avatar_url VARCHAR(255) DEFAULT NULL COMMENT 头像地址, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常0禁用, online_status TINYINT NOT NULL DEFAULT 0 COMMENT 在线状态1在线0离线, last_login_time DATETIME DEFAULT NULL COMMENT 最后登录时间, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT用户表;几个关键点说一下。username字段加了唯一键应用层注册时也要先查一遍是否重复数据库的唯一键是兜底方案防止并发注册时两个请求同时插入了相同的用户名。password_hash和salt是配套的密码不能明文存储也不能只做一次MD5。我这边使用的是加盐后多次哈希盐值是每次注册时生成的随机字符串和哈希值一起存。校验登录时从库里取出盐值对用户输入的密码做同样的哈希运算再比对结果。online_status这个字段看起来和登录态有关但实际上它只是一个最近一次心跳时记录的状态。真正的在线状态应该以WebSocket网关的活跃连接为准数据库里的这个值仅供参考比如展示好友列表时优先显示在线。2.2 好友关系表双向关系的持久化设计好友关系天然是双向的。我见过有些项目在插入好友关系时写两条记录方向相反这样查询我的好友列表非常方便但删除和修改时容易漏掉另一条。我的做法是只存一条记录并且约定user_id小于friend_id查询时通过条件判断方向来取。CREATE TABLE user_friend ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键, user_id BIGINT NOT NULL COMMENT 用户ID, friend_id BIGINT NOT NULL COMMENT 好友ID, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1有效0已解除, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 建立时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_user_friend (user_id, friend_id), KEY idx_friend_id (friend_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT好友关系表;这里最核心的设计就是唯一键uk_user_friend。我们约定插入时总是把较小的用户ID放在user_id较大的放在friend_id这样A加B和B加A在表里是同一行记录天然去重也避免了互相加好友造成的冗余数据。查询时会稍微绕一点。查A的好友列表需要这样写SELECT friend_id AS uid FROM user_friend WHERE user_id A AND status 1 UNION SELECT user_id AS uid FROM user_friend WHERE friend_id A AND status 1这样写虽然多了一次UNION但数据一致性得到了保证。实际压测下来在百万级好友关系数据下这个查询配合索引也能在几十毫秒内返回完全够用。2.3 群组表与群组成员表多对多关系拆成两张表群组业务涉及两个核心实体群组本身和群组成员。群组表存群的名字、创建人、公告这些基础信息群组成员表单独存成员关系。CREATE TABLE group_info ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 群组ID, group_name VARCHAR(64) NOT NULL COMMENT 群组名称, owner_user_id BIGINT NOT NULL COMMENT 群主用户ID, announcement VARCHAR(512) DEFAULT NULL COMMENT 群公告, member_count INT NOT NULL DEFAULT 0 COMMENT 成员数冗余统计字段, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常0已解散, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), KEY idx_owner_user_id (owner_user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT群组表;member_count这个字段是冗余的。群组页基本都要展示成员数如果每次都用COUNT(*)去查成员表在群组多、成员多的情况下对数据库压力不小。更关键的问题是群组成员表的数据量通常远大于群组表把统计值缓存在群组表里查询群列表时可以减少一次子查询。当然冗余带来的问题是需要维护一致性我是在应用层完成加群退群操作时同步更新这个计数的具体见后面的代码部分。CREATE TABLE group_member ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键, group_id BIGINT NOT NULL COMMENT 群组ID, user_id BIGINT NOT NULL COMMENT 用户ID, role TINYINT NOT NULL DEFAULT 0 COMMENT 角色0普通成员1管理员2群主, join_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 入群时间, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常0已退群, last_read_msg_id BIGINT DEFAULT NULL COMMENT 最后已读消息ID, PRIMARY KEY (id), UNIQUE KEY uk_group_user (group_id, user_id), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT群组成员表;群组成员表的唯一键uk_group_user保证了同一个用户在同一群组只能有一条记录。last_read_msg_id字段很有用它记录用户在这个群里最后读到哪条消息了配合群消息表的主键就能算未读数。一开始没加这个字段后来做已读回执功能时发现查不到数据才重新补的。2.4 单聊消息表按消息ID分页比按时间分页稳单聊消息表存的是两个用户之间的私聊消息这张表的写入量是最大的所以索引设计格外重要。CREATE TABLE private_message ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 消息ID, sender_id BIGINT NOT NULL COMMENT 发送者ID, receiver_id BIGINT NOT NULL COMMENT 接收者ID, content_type TINYINT NOT NULL DEFAULT 1 COMMENT 内容类型1文本2图片3文件4语音, content TEXT NOT NULL COMMENT 消息内容, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常0已撤回, sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 发送时间, PRIMARY KEY (id), KEY idx_sender_receiver_sent (sender_id, receiver_id, id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT单聊消息表;这里要重点解释一下索引idx_sender_receiver_sent的设计思路。聊天记录查询的场景是进入和某个好友的聊天窗口后按时间倒序加载最近的消息。常见的错误做法是只给sender_id和receiver_id建联合索引不带排序字段导致每次查询都要做文件排序。这个索引把id也放进来是有讲究的。InnoDB的主键是聚簇索引id天然有序所以把id放在联合索引的最后一个字段查询时可以顺便利用B树的有序性省掉ORDER BY的filesort操作。实测下来在千万级数据量下同样的分页查询带id的索引比不带快了接近一倍。分页查询建议用id游标而不是LIMIT offsetSELECT id, sender_id, receiver_id, content_type, content, sent_at FROM private_message WHERE sender_id ? AND receiver_id ? AND id ? -- 上一页最后一条消息的ID ORDER BY id DESC LIMIT 20;用id ?的方式做游标分页即使翻到几百页查询速度也依然稳定。而LIMIT 10000, 20这种写法offset越大MySQL需要扫描的行数越多性能会急剧恶化。2.5 群聊消息表内容和投递关系分离群聊消息和单聊消息的最大区别是广播性。一条群消息要推给群里所有成员如果直接在设计上做一张群消息收件人明细表每条群消息就要产生N条记录存储膨胀非常严重。我采用的消息表和收件状态分离方案可以做到消息只存一份收件人的投递状态动态计算。CREATE TABLE group_message ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 消息ID, group_id BIGINT NOT NULL COMMENT 群组ID, sender_id BIGINT NOT NULL COMMENT 发送者ID, content_type TINYINT NOT NULL DEFAULT 1 COMMENT 内容类型1文本2图片3文件4语音, content TEXT NOT NULL COMMENT 消息内容, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常0已撤回, sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 发送时间, PRIMARY KEY (id), KEY idx_group_id_id (group_id, id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT群聊消息表;查询某个群的历史消息时直接通过group_id过滤再用消息ID作为游标分页。群成员表里的last_read_msg_id字段此时就派上用场了拉取离线消息时只需要找出last_read_msg_id小于群最新消息ID的记录即可。SELECT gm.* FROM group_message gm WHERE gm.group_id ? AND gm.id ? AND gm.id ? ORDER BY gm.id ASC;这里的?参数分别是用户最后已读的消息ID和群最新消息ID中间的差值就是用户需要拉取的离线消息数。这个方案不需要额外表查询依赖于idx_group_id_id索引数据量很大时依然高效。2.6 离线消息表消息投递状态的中转站CREATE TABLE offline_message ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键, user_id BIGINT NOT NULL COMMENT 接收者用户ID, msg_type TINYINT NOT NULL COMMENT 消息类型1单聊2群聊, msg_id BIGINT NOT NULL COMMENT 对应的消息ID, group_id BIGINT DEFAULT NULL COMMENT 如果是群聊消息记录群组ID, is_delivered TINYINT NOT NULL DEFAULT 0 COMMENT 是否已投递0未投递1已投递, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), KEY idx_user_delivered (user_id, is_delivered) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT离线消息表;这张表的写入时机是在消息落库后、发送给目标用户前。用户接收完成再标记is_delivered 1。查询未读消息时SELECT om.msg_type, om.msg_id, om.group_id FROM offline_message om WHERE om.user_id ? AND om.is_delivered 0;等用户成功拉到消息后批量更新状态UPDATE offline_message SET is_delivered 1 WHERE user_id ? AND is_delivered 0;2.7 连接池与数据库配置的落地参数表建好了连接池配置跟不上也是白搭。我这里使用的是HikariCP实测在集群聊天室场景下性能比Druid更稳定配置如下spring: datasource: url: jdbc:mysql://192.168.1.10:3306/chatroom?useUnicodetruecharacterEncodingutf8mb4rewriteBatchedStatementstrueserverTimezoneAsia/Shanghai username: chatroom password: your_password driver-class-name: com.mysql.cj.jdbc.Driver hikari: maximum-pool-size: 20 minimum-idle: 5 idle-timeout: 300000 connection-timeout: 3000 pool-name: ChatroomHikariPoolrewriteBatchedStatementstrue这个参数容易被忽略但对批量插入性能影响巨大。开启后MyBatis的foreach批量insert会被MySQL重写为一条多值的INSERT语句大批量写入离线消息时性能能提升好几倍。3. 配套代码实现MyBatis-Plus下的DAO层实战3.1 实体类映射与小技巧表结构定好了代码层就要一一映射。我用的是MyBatis-Plus实体类和表字段的对应关系比较直接但有几个细节值得说一下。Data TableName(user) public class User { TableId(type IdType.AUTO) private Long id; private String username; private String passwordHash; private String salt; private String nickname; private String avatarUrl; private Integer status; private Integer onlineStatus; private LocalDateTime lastLoginTime; TableField(fill FieldFill.INSERT) private LocalDateTime createdAt; TableField(fill FieldFill.INSERT_UPDATE) private LocalDateTime updatedAt; }TableField(fill FieldFill.INSERT)和FieldFill.INSERT_UPDATE配合MetaObjectHandler可以自动填充创建时间和更新时间不需要每条插入语句都手动set时间字段。Component public class MyMetaObjectHandler implements MetaObjectHandler { Override public void insertFill(MetaObject metaObject) { this.strictInsertFill(metaObject, createdAt, LocalDateTime.class, LocalDateTime.now()); this.strictInsertFill(metaObject, updatedAt, LocalDateTime.class, LocalDateTime.now()); } Override public void updateFill(MetaObject metaObject) { this.strictUpdateFill(metaObject, updatedAt, LocalDateTime.class, LocalDateTime.now()); } }这样处理之后代码里insert和update时就不需要关心时间字段了减少了很多模板代码。3.2 好友关系维护的并发问题好友关系表的代码并不复杂但并发场景下有一个很隐蔽的坑。Service RequiredArgsConstructor public class FriendService { private final UserFriendMapper userFriendMapper; private final TransactionTemplate transactionTemplate; public void addFriend(Long userId, Long friendId) { if (userId.equals(friendId)) { throw new IllegalArgumentException(不能添加自己为好友); } Long minId Math.min(userId, friendId); Long maxId Math.max(userId, friendId); transactionTemplate.executeWithoutResult(status - { UserFriend existing userFriendMapper.selectOne( new LambdaQueryWrapperUserFriend() .eq(UserFriend::getUserId, minId) .eq(UserFriend::getFriendId, maxId) ); if (existing null) { UserFriend friend new UserFriend(); friend.setUserId(minId); friend.setFriendId(maxId); friend.setStatus(1); userFriendMapper.insert(friend); } else { existing.setStatus(1); userFriendMapper.updateById(existing); } }); } }注意这里必须做唯一键冲突兜底。如果是第一次插入直接用insert即可但如果两个人之前解除过好友关系表里已经有记录只是status为0那么直接insert会触发唯一键冲突。所以要先查询一次存在就更新状态不存在才插入。这里的查询和插入放在同一个事务里并且依赖数据库的唯一键保证并发安全。3.3 群成员加入与统计字段的原子性加群操作涉及两条数据的变更插入群成员记录、更新群组的member_count。这个操作必须保证要么都成功要么都失败。public void joinGroup(Long groupId, Long userId) { transactionTemplate.executeWithoutResult(status - { int inserted groupMemberMapper.insert( GroupMember.builder() .groupId(groupId) .userId(userId) .role(0) .status(1) .build() ); if (inserted 1) { groupInfoMapper.incrMemberCount(groupId); } }); }incrMemberCount在Mapper里这样写Update(UPDATE group_info SET member_count member_count 1 WHERE id #{groupId}) void incrMemberCount(Long groupId);这里不使用member_count member_count 1之外的方案是因为直接基于当前值累加比先查询再更新的方式更安全避免了并发下两个请求同时读到旧值导致计数偏小的问题。同理退群时使用decrMemberCount。3.4 发送消息的完整事务链路消息发送是聊天室的核心链路也是事务最重的地方。用户点击发送后后端要做的事情如下public void sendPrivateMessage(Long senderId, Long receiverId, MessageContent msg) { transactionTemplate.executeWithoutResult(status - { PrivateMessage pm new PrivateMessage(); pm.setSenderId(senderId); pm.setReceiverId(receiverId); pm.setContentType(msg.getContentType()); pm.setContent(msg.getContent()); privateMessageMapper.insert(pm); boolean receiverOnline userStatusService.isOnline(receiverId); if (!receiverOnline) { OfflineMessage om new OfflineMessage(); om.setUserId(receiverId); om.setMsgType(1); om.setMsgId(pm.getId()); offlineMessageMapper.insert(om); } }); // 事务提交后再推送实时消息 messagePushService.push(senderId, receiverId, pm); }这里的核心思路是事务内只负责落库事务提交后才去推送实时消息。如果用事务内的数据库状态去做WebSocket推送在推送失败时可能导致整个事务回滚把正常落库的消息也弄丢了。注意WebSocket推送应该在事务提交之后进行。如果推送失败有两种处理方式一种是重试另一种是等用户下次上线时通过离线消息补偿。我的实际做法是两者都保留推送失败先记录日志用户上线时自动拉取离线消息兜底。3.5 群消息广播的批量落库群消息的落库比单聊复杂在两点一是消息本身要存一份二是要给所有群成员判断是否需要写离线消息。一次性把几百上千条离线消息逐条insert性能会很差所以要批量处理。Transactional public void sendGroupMessage(Long groupId, Long senderId, MessageContent msg) { GroupMessage gm new GroupMessage(); gm.setGroupId(groupId); gm.setSenderId(senderId); gm.setContentType(msg.getContentType()); gm.setContent(msg.getContent()); groupMessageMapper.insert(gm); ListLong memberIds groupMemberMapper.selectOnlineMemberIds(groupId); ListLong offlineMemberIds groupMemberMapper.selectOfflineMemberIds(groupId); if (!offlineMemberIds.isEmpty()) { offlineMessageMapper.batchInsertForGroup(offlineMemberIds, gm.getId(), groupId); } }batchInsertForGroup对应的SQLINSERT INTO offline_message (user_id, msg_type, msg_id, group_id) VALUES foreach collectionlist itemuserId separator, (#{userId}, 2, #{msgId}, #{groupId}) /foreach配合前面提到的rewriteBatchedStatementstrue这条SQL在MySQL端会被优化为批量插入实测插入2000条离线消息只需要几十毫秒性能可以接受。4. 索引优化与慢查询排查实录4.1 联合索引顺序的踩坑经历我有一个阶段被线上慢查询搞得非常痛苦。现象是群聊历史消息接口偶尔会慢到2秒以上用EXPLAIN一看发现走了idx_group_id_id索引的where条件但排序还是要filesort。后来排查发现是ORDER BY id DESC方向和索引扫描方向不一致导致的。这个问题的解法有两条路。一条是用ORDER BY id ASC正向查询然后应用层倒序返回另一条是保持SQL不变把索引改成(group_id, id DESC)。MySQL 8.0支持降序索引实测后者的效果更好但仍然要注意8.0之前的版本不支持所以我的最终方案是在代码层用正向查询加倒序处理兼容性最好。ListGroupMessage messages groupMessageMapper.selectList( new LambdaQueryWrapperGroupMessage() .eq(GroupMessage::getGroupId, groupId) .gt(GroupMessage::getId, lastMsgId) .orderByAsc(GroupMessage::getId) .last(LIMIT pageSize) ); Collections.reverse(messages);这样既有索引有序性又不会产生filesort性能稳定。4.2 消息内容字段用TEXT还是VARCHAR聊天消息的内容长短不一短的可能只有一个字长的可能是一整段富文本。设计时我最初把content字段定义为VARCHAR(500)结果发长消息时直接报data too long。后来改成TEXT又发现TEXT字段的索引支持受限只能在非TEXT字段上建索引。我的建议是不超过255个字符的用VARCHAR可能超过的用TEXT但注意TEXT字段不能有默认值插入时不能省略。另外如果以后要支持消息内容检索应该配合全文索引或者搜索引擎而不是直接在TEXT字段上做LIKE模糊查询。这里是聊天室场景对内容检索的要求不高所以TEXT就够用了。4.3 慢查询日志的开启与典型日志分析建议在开发环境就开启MySQL慢查询日志很多潜在问题在生产事故之前就能发现。SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;之前从慢日志里抓到过一条非常典型的SQLSELECT * FROM private_message WHERE sender_id 1001 AND receiver_id 2002 ORDER BY sent_at DESC LIMIT 20;这条SQL慢在ORDER BY sent_at上。sent_at没有索引MySQL只能先查出所有满足条件的记录再做文件排序。排查后改成按主键id排序因为InnoDB的主键本身就是插入顺序在聊天场景下和消息发送时间基本一致所以直接用主键排序既快又没有语义损失。4.4 分页查询深翻页问题消息记录往上翻翻到几百页之后LIMIT 20000, 20的写法让数据库越来越慢。这个问题在单聊界面里非常容易遇到因为用户可能聊了大半年。我先给出自己验证过有效的三种方案按优先级排序游标分页用WHERE id 上一页最小ID性能最稳定推荐。延迟关联先用覆盖索引查出主键ID再关联回表查完整数据。禁止深翻页产品层面限制只能查看最近N条消息。-- 延迟关联示例 SELECT pm.* FROM private_message pm INNER JOIN ( SELECT id FROM private_message WHERE sender_id ? AND receiver_id ? ORDER BY id DESC LIMIT 20 OFFSET 20000 ) tmp ON pm.id tmp.id ORDER BY pm.id DESC;游标分页是聊天场景的最佳方案我也在实际代码中全面采用了。5. 常见问题与避坑技巧速查表问题现象可能原因解决方案好友加不上报Duplicate entry唯一键约束先查后插状态置1不要重复insert群聊消息拉取慢有filesort索引方向和排序方向不一致改为正向查询应用层倒序离线消息大量堆积推送失败未补偿离线表写入时设置状态上线统一拉取member_count越来越不准并发加群退群丢失更新用SQL原子自增自减不用先查后更消息内容太长导致写入失败VARCHAR长度不够改用TEXT注意不能有默认值翻到几十页后接口超时深分页导致的扫描行数过大用id游标分页替代offset批量插入离线消息极慢没有开启rewriteBatchedStatementsJDBC URL加上该参数5.1 关闭表结构变更的外键校验时机如果对已有表做结构变更比如修改字段类型、加索引在数据量很大的情况下会导致表长时间锁定。这里有一个MySQL的优化技巧就是在变更前关闭外键校验变更后重新打开。SET FOREIGN_KEY_CHECKS 0; ALTER TABLE group_member ADD INDEX idx_last_read_msg_id (last_read_msg_id); SET FOREIGN_KEY_CHECKS 1;不过这个操作需要在低峰期执行并且变更前一定要做好备份。另外如果真的在线上环境做大表DDL更推荐用pt-osc或者gh-ost这类在线表结构变更工具它们可以边变更边提供正常读写服务。5.2 一个容易忽略的应用层坑事务内查询不到刚插入的数据我遇到过一次比较诡异的问题在同一个事务里先insert了一条群消息随后去查这条群消息结果查不到。排查后发现是MyBatis-Plus的二级缓存导致的。一级缓存是SqlSession级别的事务未提交前同一个SqlSession的查询会优先走缓存但返回的结果是旧数据。解决方法是把查询放在事务提交之后或者显式调用clearCache清空缓存再执行查询。在消息推送链路里我最终选择了事务提交后再查一次的方案逻辑更清晰。5.3 软删除和唯一键的冲突以及解决思路好友关系和群成员表都用了唯一键。如果做软删除比如把status置为0此时如果用同一对userId/friendId再次插入会因为唯一键冲突而失败。这个问题在用户先删好友再加好友时非常容易出现。解决思路有几种一个是在插入前先物理删除旧记录或者把旧记录的status更新为1另一个是通过调整唯一键设计来规避。考虑到聊天室项目的规模我用的是更直接的方式把删除操作设计为更新操作而不是物理删除确保唯一键对应的那行数据始终存在。更新状态为0表示关系解除再次建立时直接更新为1即可。6. 关于删库跑路的备份与恢复建议数据库表结构设计和代码完成只是万里长征的一半。没有备份策略的数据库本质上属于硬造风险。在实际项目中我每天凌晨会用mysqldump做一次全量备份同时开启binlog日志方便数据误删后的时间点恢复。# 全量备份 mysqldump -u chatroom -p chatroom --single-transaction --master-data2 /data/backup/chatroom_$(date %F).sql # 恢复全量备份 mysql -u chatroom -p chatroom /data/backup/chatroom_2025-01-01.sql--single-transaction参数对InnoDB表很重要它通过开启一个一致性快照来备份数据不锁表生产环境执行时不影响业务读写。--master-data2会在备份文件中记录binlog位置做时间点恢复时可以直接定位到备份点位。这里再补充一点。如果你在集群环境下部署MySQL备份策略要区分主从节点。主节点备份用于恢复从节点备份用于日常数据导出和分析。别把从节点备份当成高可用方案从节点挂了一样要重新同步备份文件才是真正的保险。7. 从单机到集群这套表结构还够用吗说实话上面这套表结构支撑的是单主库架构。如果你的聊天室用户量真的起来了比如需要分库分表这套设计里的几个点依然能平滑过渡。用户表和好友关系表可以按用户ID做水平分片比如取模分片。好友关系表因为唯一的(user_id, friend_id)约束放在分片键上查询好友列表时可以通过user_id或friend_id路由到对应分片。消息表最麻烦。如果按消息ID做全局唯一主键分片规则要么用雪花算法生成全局ID要么用分段ID方案。我之前提到private_message表主键用AUTO_INCREMENT这个在单库下没有问题但分库后多个库各自生成的主键会冲突。如果你预判将来要分库建议从一开始就使用雪花ID或者其他分布式ID方案而不是AUTO_INCREMENT。分库分表引入后会有一个比较麻烦的问题——跨分片的事务。聊天室的消息发送和离线消息写入天然是一个事务里要操作多个表。如果用分库分表中间件跨片事务需要用分布式事务方案来兜底但分布式事务的性能开销又比较高。所以更务实的做法是尽量按业务域去分片让同一个用户的数据尽量落在一个分片里这样单聊场景就不需要跨分片事务只有群聊广播才需要额外处理。最后再抛一个方向。如果把这套表和Redis、Kafka配合起来消息发送链路可以做成MySQL负责持久化Redis负责缓存最近活跃会话和未读数Kafka负责异步分发消息到各nginx网关节点。我目前正在做这部分改造等跑通了再单独写一篇分享。 Nginx集群聊天室MySQL数据库表设计与代码实现
nginx集群聊天室四项目的mysql数据库表的设置及代码做聊天室项目前面的nginx负载均衡、WebSocket集群通信都搞定之后最绕不开的一件事就是数据存储。用户消息、离线消息、好友关系、群组成员、会话记录这些东西不能全塞在Redis里Redis挂了就全没了而且做多节点集群部署每个节点的内存数据根本不同步。我在做nginx集群聊天室这个系列的时候数据库设计这一块其实是花了不少心思的踩的坑也不少。这篇我就把自己当时怎么设计这套MySQL表结构、怎么写建表语句和对应代码的过程完整捋一遍适合正在做同类集群项目、或者打算把单机聊天室往分布式方向改造的朋友参考。这次设计的目标很明确一套能把用户、好友、群组、消息、离线消息这几大块业务全部撑起来的MySQL表结构既能配合nginx做水平扩展又不能让SQL查询复杂到没法维护。下文会从表结构设计思路讲起再逐个给出建表SQL和配套的Java代码片段最后补充我自己实际运行中遇到的几个坑。1. 整体设计思路先拆业务域再决定表结构1.1 聊天室业务到底需要几张表单机版聊天室你可能只需要两张表一张存用户一张存消息。但集群聊天室不一样因为要支持好友单聊、群组群聊、离线消息推送、历史消息拉取这些场景对数据维度的要求是不同的。我在动手建表之前先把自己需要的业务能力逐条列出来了用户注册、登录、用户资料查询好友关系的建立、删除、查询群组的创建、加入、退出、解散单聊消息的发送、接收、历史记录群聊消息的发送、广播、历史记录用户不在线时消息的离线存储和上线后的拉取拆完之后表结构就非常清晰了。核心表是用户表、好友关系表、群组表、群组成员表、单聊消息表、群聊消息表外加一张离线消息表。后面三张表都属于消息域但查询频率和数据特征差别很大放一起反而不利于索引设计和分库分表。1.2 为什么要额外设计一张离线消息表一开始我也考虑过直接给消息表加一个已读状态字段比如is_read为0表示未读。但后来实测发现消息表的写入量会随着用户量增长迅速膨胀等到要按用户查未读消息时如果消息表没有正确的索引全表扫描会直接把数据库拖垮。更麻烦的是单聊消息表里一条消息只能有一条收件记录群聊消息表里一条消息可能对应几百上千个收件人用is_read字段表达群聊未读状态在逻辑上就非常别扭。所以我在设计时单独拆了一张offline_message表。每次生产消息落库时先判断收件人是否在线。如果不在线就把消息ID和收件人ID写进离线表等收件人上线之后消费掉再标记状态。这样做的好处是把消息内容和投递状态彻底解耦后续就算引入RabbitMQ或者Kafka做异步投递这张表也可以无缝衔接。1.3 外键到底要不要用这个是被问得最多的问题。我最终的结论是建表时不建物理外键只在逻辑层维护关联。原因有两个。第一是性能。集群聊天室的高频操作就是消息写入和用户关系查询MySQL在每次插入和更新时都要额外校验外键约束这种开销在单机小项目里感觉不出来但在高并发写入场景下会被明显放大。第二是分库分表的兼容性。随着用户量增长用户表和消息表大概率要做分片。物理外键要求关联的数据必须在同一个MySQL实例上否则查询直接报错。而逻辑外键配合应用层的事务控制反而可以更灵活地做数据迁移和拆分。提示不是所有项目都不建外键如果你的系统并发不高、规模稳定、并且团队对数据一致性要求非常刚性物理外键依然可以降低应用层代码的复杂度。我这个方案是为了集群场景做的取舍仅供参考。2. 核心表结构解析与建表实操2.1 用户表别把登录态和资料字段混在一起用户表是一张核心表设计时容易犯的错误是把密码、token、资料一股脑全塞进来。我的做法是把用户身份认证字段和用户资料字段放在同一张表里但尽量精简字段数量同时预留扩展空间。CREATE TABLE user ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 用户ID, username VARCHAR(64) NOT NULL COMMENT 用户名全局唯一, password_hash VARCHAR(128) NOT NULL COMMENT 密码哈希值, salt VARCHAR(32) NOT NULL COMMENT 加盐值, nickname VARCHAR(64) DEFAULT NULL COMMENT 昵称, avatar_url VARCHAR(255) DEFAULT NULL COMMENT 头像地址, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常0禁用, online_status TINYINT NOT NULL DEFAULT 0 COMMENT 在线状态1在线0离线, last_login_time DATETIME DEFAULT NULL COMMENT 最后登录时间, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT用户表;几个关键点说一下。username字段加了唯一键应用层注册时也要先查一遍是否重复数据库的唯一键是兜底方案防止并发注册时两个请求同时插入了相同的用户名。password_hash和salt是配套的密码不能明文存储也不能只做一次MD5。我这边使用的是加盐后多次哈希盐值是每次注册时生成的随机字符串和哈希值一起存。校验登录时从库里取出盐值对用户输入的密码做同样的哈希运算再比对结果。online_status这个字段看起来和登录态有关但实际上它只是一个最近一次心跳时记录的状态。真正的在线状态应该以WebSocket网关的活跃连接为准数据库里的这个值仅供参考比如展示好友列表时优先显示在线。2.2 好友关系表双向关系的持久化设计好友关系天然是双向的。我见过有些项目在插入好友关系时写两条记录方向相反这样查询我的好友列表非常方便但删除和修改时容易漏掉另一条。我的做法是只存一条记录并且约定user_id小于friend_id查询时通过条件判断方向来取。CREATE TABLE user_friend ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键, user_id BIGINT NOT NULL COMMENT 用户ID, friend_id BIGINT NOT NULL COMMENT 好友ID, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1有效0已解除, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 建立时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_user_friend (user_id, friend_id), KEY idx_friend_id (friend_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT好友关系表;这里最核心的设计就是唯一键uk_user_friend。我们约定插入时总是把较小的用户ID放在user_id较大的放在friend_id这样A加B和B加A在表里是同一行记录天然去重也避免了互相加好友造成的冗余数据。查询时会稍微绕一点。查A的好友列表需要这样写SELECT friend_id AS uid FROM user_friend WHERE user_id A AND status 1 UNION SELECT user_id AS uid FROM user_friend WHERE friend_id A AND status 1这样写虽然多了一次UNION但数据一致性得到了保证。实际压测下来在百万级好友关系数据下这个查询配合索引也能在几十毫秒内返回完全够用。2.3 群组表与群组成员表多对多关系拆成两张表群组业务涉及两个核心实体群组本身和群组成员。群组表存群的名字、创建人、公告这些基础信息群组成员表单独存成员关系。CREATE TABLE group_info ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 群组ID, group_name VARCHAR(64) NOT NULL COMMENT 群组名称, owner_user_id BIGINT NOT NULL COMMENT 群主用户ID, announcement VARCHAR(512) DEFAULT NULL COMMENT 群公告, member_count INT NOT NULL DEFAULT 0 COMMENT 成员数冗余统计字段, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常0已解散, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), KEY idx_owner_user_id (owner_user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT群组表;member_count这个字段是冗余的。群组页基本都要展示成员数如果每次都用COUNT(*)去查成员表在群组多、成员多的情况下对数据库压力不小。更关键的问题是群组成员表的数据量通常远大于群组表把统计值缓存在群组表里查询群列表时可以减少一次子查询。当然冗余带来的问题是需要维护一致性我是在应用层完成加群退群操作时同步更新这个计数的具体见后面的代码部分。CREATE TABLE group_member ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键, group_id BIGINT NOT NULL COMMENT 群组ID, user_id BIGINT NOT NULL COMMENT 用户ID, role TINYINT NOT NULL DEFAULT 0 COMMENT 角色0普通成员1管理员2群主, join_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 入群时间, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常0已退群, last_read_msg_id BIGINT DEFAULT NULL COMMENT 最后已读消息ID, PRIMARY KEY (id), UNIQUE KEY uk_group_user (group_id, user_id), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT群组成员表;群组成员表的唯一键uk_group_user保证了同一个用户在同一群组只能有一条记录。last_read_msg_id字段很有用它记录用户在这个群里最后读到哪条消息了配合群消息表的主键就能算未读数。一开始没加这个字段后来做已读回执功能时发现查不到数据才重新补的。2.4 单聊消息表按消息ID分页比按时间分页稳单聊消息表存的是两个用户之间的私聊消息这张表的写入量是最大的所以索引设计格外重要。CREATE TABLE private_message ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 消息ID, sender_id BIGINT NOT NULL COMMENT 发送者ID, receiver_id BIGINT NOT NULL COMMENT 接收者ID, content_type TINYINT NOT NULL DEFAULT 1 COMMENT 内容类型1文本2图片3文件4语音, content TEXT NOT NULL COMMENT 消息内容, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常0已撤回, sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 发送时间, PRIMARY KEY (id), KEY idx_sender_receiver_sent (sender_id, receiver_id, id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT单聊消息表;这里要重点解释一下索引idx_sender_receiver_sent的设计思路。聊天记录查询的场景是进入和某个好友的聊天窗口后按时间倒序加载最近的消息。常见的错误做法是只给sender_id和receiver_id建联合索引不带排序字段导致每次查询都要做文件排序。这个索引把id也放进来是有讲究的。InnoDB的主键是聚簇索引id天然有序所以把id放在联合索引的最后一个字段查询时可以顺便利用B树的有序性省掉ORDER BY的filesort操作。实测下来在千万级数据量下同样的分页查询带id的索引比不带快了接近一倍。分页查询建议用id游标而不是LIMIT offsetSELECT id, sender_id, receiver_id, content_type, content, sent_at FROM private_message WHERE sender_id ? AND receiver_id ? AND id ? -- 上一页最后一条消息的ID ORDER BY id DESC LIMIT 20;用id ?的方式做游标分页即使翻到几百页查询速度也依然稳定。而LIMIT 10000, 20这种写法offset越大MySQL需要扫描的行数越多性能会急剧恶化。2.5 群聊消息表内容和投递关系分离群聊消息和单聊消息的最大区别是广播性。一条群消息要推给群里所有成员如果直接在设计上做一张群消息收件人明细表每条群消息就要产生N条记录存储膨胀非常严重。我采用的消息表和收件状态分离方案可以做到消息只存一份收件人的投递状态动态计算。CREATE TABLE group_message ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 消息ID, group_id BIGINT NOT NULL COMMENT 群组ID, sender_id BIGINT NOT NULL COMMENT 发送者ID, content_type TINYINT NOT NULL DEFAULT 1 COMMENT 内容类型1文本2图片3文件4语音, content TEXT NOT NULL COMMENT 消息内容, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1正常0已撤回, sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 发送时间, PRIMARY KEY (id), KEY idx_group_id_id (group_id, id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT群聊消息表;查询某个群的历史消息时直接通过group_id过滤再用消息ID作为游标分页。群成员表里的last_read_msg_id字段此时就派上用场了拉取离线消息时只需要找出last_read_msg_id小于群最新消息ID的记录即可。SELECT gm.* FROM group_message gm WHERE gm.group_id ? AND gm.id ? AND gm.id ? ORDER BY gm.id ASC;这里的?参数分别是用户最后已读的消息ID和群最新消息ID中间的差值就是用户需要拉取的离线消息数。这个方案不需要额外表查询依赖于idx_group_id_id索引数据量很大时依然高效。2.6 离线消息表消息投递状态的中转站CREATE TABLE offline_message ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键, user_id BIGINT NOT NULL COMMENT 接收者用户ID, msg_type TINYINT NOT NULL COMMENT 消息类型1单聊2群聊, msg_id BIGINT NOT NULL COMMENT 对应的消息ID, group_id BIGINT DEFAULT NULL COMMENT 如果是群聊消息记录群组ID, is_delivered TINYINT NOT NULL DEFAULT 0 COMMENT 是否已投递0未投递1已投递, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), KEY idx_user_delivered (user_id, is_delivered) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT离线消息表;这张表的写入时机是在消息落库后、发送给目标用户前。用户接收完成再标记is_delivered 1。查询未读消息时SELECT om.msg_type, om.msg_id, om.group_id FROM offline_message om WHERE om.user_id ? AND om.is_delivered 0;等用户成功拉到消息后批量更新状态UPDATE offline_message SET is_delivered 1 WHERE user_id ? AND is_delivered 0;2.7 连接池与数据库配置的落地参数表建好了连接池配置跟不上也是白搭。我这里使用的是HikariCP实测在集群聊天室场景下性能比Druid更稳定配置如下spring: datasource: url: jdbc:mysql://192.168.1.10:3306/chatroom?useUnicodetruecharacterEncodingutf8mb4rewriteBatchedStatementstrueserverTimezoneAsia/Shanghai username: chatroom password: your_password driver-class-name: com.mysql.cj.jdbc.Driver hikari: maximum-pool-size: 20 minimum-idle: 5 idle-timeout: 300000 connection-timeout: 3000 pool-name: ChatroomHikariPoolrewriteBatchedStatementstrue这个参数容易被忽略但对批量插入性能影响巨大。开启后MyBatis的foreach批量insert会被MySQL重写为一条多值的INSERT语句大批量写入离线消息时性能能提升好几倍。3. 配套代码实现MyBatis-Plus下的DAO层实战3.1 实体类映射与小技巧表结构定好了代码层就要一一映射。我用的是MyBatis-Plus实体类和表字段的对应关系比较直接但有几个细节值得说一下。Data TableName(user) public class User { TableId(type IdType.AUTO) private Long id; private String username; private String passwordHash; private String salt; private String nickname; private String avatarUrl; private Integer status; private Integer onlineStatus; private LocalDateTime lastLoginTime; TableField(fill FieldFill.INSERT) private LocalDateTime createdAt; TableField(fill FieldFill.INSERT_UPDATE) private LocalDateTime updatedAt; }TableField(fill FieldFill.INSERT)和FieldFill.INSERT_UPDATE配合MetaObjectHandler可以自动填充创建时间和更新时间不需要每条插入语句都手动set时间字段。Component public class MyMetaObjectHandler implements MetaObjectHandler { Override public void insertFill(MetaObject metaObject) { this.strictInsertFill(metaObject, createdAt, LocalDateTime.class, LocalDateTime.now()); this.strictInsertFill(metaObject, updatedAt, LocalDateTime.class, LocalDateTime.now()); } Override public void updateFill(MetaObject metaObject) { this.strictUpdateFill(metaObject, updatedAt, LocalDateTime.class, LocalDateTime.now()); } }这样处理之后代码里insert和update时就不需要关心时间字段了减少了很多模板代码。3.2 好友关系维护的并发问题好友关系表的代码并不复杂但并发场景下有一个很隐蔽的坑。Service RequiredArgsConstructor public class FriendService { private final UserFriendMapper userFriendMapper; private final TransactionTemplate transactionTemplate; public void addFriend(Long userId, Long friendId) { if (userId.equals(friendId)) { throw new IllegalArgumentException(不能添加自己为好友); } Long minId Math.min(userId, friendId); Long maxId Math.max(userId, friendId); transactionTemplate.executeWithoutResult(status - { UserFriend existing userFriendMapper.selectOne( new LambdaQueryWrapperUserFriend() .eq(UserFriend::getUserId, minId) .eq(UserFriend::getFriendId, maxId) ); if (existing null) { UserFriend friend new UserFriend(); friend.setUserId(minId); friend.setFriendId(maxId); friend.setStatus(1); userFriendMapper.insert(friend); } else { existing.setStatus(1); userFriendMapper.updateById(existing); } }); } }注意这里必须做唯一键冲突兜底。如果是第一次插入直接用insert即可但如果两个人之前解除过好友关系表里已经有记录只是status为0那么直接insert会触发唯一键冲突。所以要先查询一次存在就更新状态不存在才插入。这里的查询和插入放在同一个事务里并且依赖数据库的唯一键保证并发安全。3.3 群成员加入与统计字段的原子性加群操作涉及两条数据的变更插入群成员记录、更新群组的member_count。这个操作必须保证要么都成功要么都失败。public void joinGroup(Long groupId, Long userId) { transactionTemplate.executeWithoutResult(status - { int inserted groupMemberMapper.insert( GroupMember.builder() .groupId(groupId) .userId(userId) .role(0) .status(1) .build() ); if (inserted 1) { groupInfoMapper.incrMemberCount(groupId); } }); }incrMemberCount在Mapper里这样写Update(UPDATE group_info SET member_count member_count 1 WHERE id #{groupId}) void incrMemberCount(Long groupId);这里不使用member_count member_count 1之外的方案是因为直接基于当前值累加比先查询再更新的方式更安全避免了并发下两个请求同时读到旧值导致计数偏小的问题。同理退群时使用decrMemberCount。3.4 发送消息的完整事务链路消息发送是聊天室的核心链路也是事务最重的地方。用户点击发送后后端要做的事情如下public void sendPrivateMessage(Long senderId, Long receiverId, MessageContent msg) { transactionTemplate.executeWithoutResult(status - { PrivateMessage pm new PrivateMessage(); pm.setSenderId(senderId); pm.setReceiverId(receiverId); pm.setContentType(msg.getContentType()); pm.setContent(msg.getContent()); privateMessageMapper.insert(pm); boolean receiverOnline userStatusService.isOnline(receiverId); if (!receiverOnline) { OfflineMessage om new OfflineMessage(); om.setUserId(receiverId); om.setMsgType(1); om.setMsgId(pm.getId()); offlineMessageMapper.insert(om); } }); // 事务提交后再推送实时消息 messagePushService.push(senderId, receiverId, pm); }这里的核心思路是事务内只负责落库事务提交后才去推送实时消息。如果用事务内的数据库状态去做WebSocket推送在推送失败时可能导致整个事务回滚把正常落库的消息也弄丢了。注意WebSocket推送应该在事务提交之后进行。如果推送失败有两种处理方式一种是重试另一种是等用户下次上线时通过离线消息补偿。我的实际做法是两者都保留推送失败先记录日志用户上线时自动拉取离线消息兜底。3.5 群消息广播的批量落库群消息的落库比单聊复杂在两点一是消息本身要存一份二是要给所有群成员判断是否需要写离线消息。一次性把几百上千条离线消息逐条insert性能会很差所以要批量处理。Transactional public void sendGroupMessage(Long groupId, Long senderId, MessageContent msg) { GroupMessage gm new GroupMessage(); gm.setGroupId(groupId); gm.setSenderId(senderId); gm.setContentType(msg.getContentType()); gm.setContent(msg.getContent()); groupMessageMapper.insert(gm); ListLong memberIds groupMemberMapper.selectOnlineMemberIds(groupId); ListLong offlineMemberIds groupMemberMapper.selectOfflineMemberIds(groupId); if (!offlineMemberIds.isEmpty()) { offlineMessageMapper.batchInsertForGroup(offlineMemberIds, gm.getId(), groupId); } }batchInsertForGroup对应的SQLINSERT INTO offline_message (user_id, msg_type, msg_id, group_id) VALUES foreach collectionlist itemuserId separator, (#{userId}, 2, #{msgId}, #{groupId}) /foreach配合前面提到的rewriteBatchedStatementstrue这条SQL在MySQL端会被优化为批量插入实测插入2000条离线消息只需要几十毫秒性能可以接受。4. 索引优化与慢查询排查实录4.1 联合索引顺序的踩坑经历我有一个阶段被线上慢查询搞得非常痛苦。现象是群聊历史消息接口偶尔会慢到2秒以上用EXPLAIN一看发现走了idx_group_id_id索引的where条件但排序还是要filesort。后来排查发现是ORDER BY id DESC方向和索引扫描方向不一致导致的。这个问题的解法有两条路。一条是用ORDER BY id ASC正向查询然后应用层倒序返回另一条是保持SQL不变把索引改成(group_id, id DESC)。MySQL 8.0支持降序索引实测后者的效果更好但仍然要注意8.0之前的版本不支持所以我的最终方案是在代码层用正向查询加倒序处理兼容性最好。ListGroupMessage messages groupMessageMapper.selectList( new LambdaQueryWrapperGroupMessage() .eq(GroupMessage::getGroupId, groupId) .gt(GroupMessage::getId, lastMsgId) .orderByAsc(GroupMessage::getId) .last(LIMIT pageSize) ); Collections.reverse(messages);这样既有索引有序性又不会产生filesort性能稳定。4.2 消息内容字段用TEXT还是VARCHAR聊天消息的内容长短不一短的可能只有一个字长的可能是一整段富文本。设计时我最初把content字段定义为VARCHAR(500)结果发长消息时直接报data too long。后来改成TEXT又发现TEXT字段的索引支持受限只能在非TEXT字段上建索引。我的建议是不超过255个字符的用VARCHAR可能超过的用TEXT但注意TEXT字段不能有默认值插入时不能省略。另外如果以后要支持消息内容检索应该配合全文索引或者搜索引擎而不是直接在TEXT字段上做LIKE模糊查询。这里是聊天室场景对内容检索的要求不高所以TEXT就够用了。4.3 慢查询日志的开启与典型日志分析建议在开发环境就开启MySQL慢查询日志很多潜在问题在生产事故之前就能发现。SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;之前从慢日志里抓到过一条非常典型的SQLSELECT * FROM private_message WHERE sender_id 1001 AND receiver_id 2002 ORDER BY sent_at DESC LIMIT 20;这条SQL慢在ORDER BY sent_at上。sent_at没有索引MySQL只能先查出所有满足条件的记录再做文件排序。排查后改成按主键id排序因为InnoDB的主键本身就是插入顺序在聊天场景下和消息发送时间基本一致所以直接用主键排序既快又没有语义损失。4.4 分页查询深翻页问题消息记录往上翻翻到几百页之后LIMIT 20000, 20的写法让数据库越来越慢。这个问题在单聊界面里非常容易遇到因为用户可能聊了大半年。我先给出自己验证过有效的三种方案按优先级排序游标分页用WHERE id 上一页最小ID性能最稳定推荐。延迟关联先用覆盖索引查出主键ID再关联回表查完整数据。禁止深翻页产品层面限制只能查看最近N条消息。-- 延迟关联示例 SELECT pm.* FROM private_message pm INNER JOIN ( SELECT id FROM private_message WHERE sender_id ? AND receiver_id ? ORDER BY id DESC LIMIT 20 OFFSET 20000 ) tmp ON pm.id tmp.id ORDER BY pm.id DESC;游标分页是聊天场景的最佳方案我也在实际代码中全面采用了。5. 常见问题与避坑技巧速查表问题现象可能原因解决方案好友加不上报Duplicate entry唯一键约束先查后插状态置1不要重复insert群聊消息拉取慢有filesort索引方向和排序方向不一致改为正向查询应用层倒序离线消息大量堆积推送失败未补偿离线表写入时设置状态上线统一拉取member_count越来越不准并发加群退群丢失更新用SQL原子自增自减不用先查后更消息内容太长导致写入失败VARCHAR长度不够改用TEXT注意不能有默认值翻到几十页后接口超时深分页导致的扫描行数过大用id游标分页替代offset批量插入离线消息极慢没有开启rewriteBatchedStatementsJDBC URL加上该参数5.1 关闭表结构变更的外键校验时机如果对已有表做结构变更比如修改字段类型、加索引在数据量很大的情况下会导致表长时间锁定。这里有一个MySQL的优化技巧就是在变更前关闭外键校验变更后重新打开。SET FOREIGN_KEY_CHECKS 0; ALTER TABLE group_member ADD INDEX idx_last_read_msg_id (last_read_msg_id); SET FOREIGN_KEY_CHECKS 1;不过这个操作需要在低峰期执行并且变更前一定要做好备份。另外如果真的在线上环境做大表DDL更推荐用pt-osc或者gh-ost这类在线表结构变更工具它们可以边变更边提供正常读写服务。5.2 一个容易忽略的应用层坑事务内查询不到刚插入的数据我遇到过一次比较诡异的问题在同一个事务里先insert了一条群消息随后去查这条群消息结果查不到。排查后发现是MyBatis-Plus的二级缓存导致的。一级缓存是SqlSession级别的事务未提交前同一个SqlSession的查询会优先走缓存但返回的结果是旧数据。解决方法是把查询放在事务提交之后或者显式调用clearCache清空缓存再执行查询。在消息推送链路里我最终选择了事务提交后再查一次的方案逻辑更清晰。5.3 软删除和唯一键的冲突以及解决思路好友关系和群成员表都用了唯一键。如果做软删除比如把status置为0此时如果用同一对userId/friendId再次插入会因为唯一键冲突而失败。这个问题在用户先删好友再加好友时非常容易出现。解决思路有几种一个是在插入前先物理删除旧记录或者把旧记录的status更新为1另一个是通过调整唯一键设计来规避。考虑到聊天室项目的规模我用的是更直接的方式把删除操作设计为更新操作而不是物理删除确保唯一键对应的那行数据始终存在。更新状态为0表示关系解除再次建立时直接更新为1即可。6. 关于删库跑路的备份与恢复建议数据库表结构设计和代码完成只是万里长征的一半。没有备份策略的数据库本质上属于硬造风险。在实际项目中我每天凌晨会用mysqldump做一次全量备份同时开启binlog日志方便数据误删后的时间点恢复。# 全量备份 mysqldump -u chatroom -p chatroom --single-transaction --master-data2 /data/backup/chatroom_$(date %F).sql # 恢复全量备份 mysql -u chatroom -p chatroom /data/backup/chatroom_2025-01-01.sql--single-transaction参数对InnoDB表很重要它通过开启一个一致性快照来备份数据不锁表生产环境执行时不影响业务读写。--master-data2会在备份文件中记录binlog位置做时间点恢复时可以直接定位到备份点位。这里再补充一点。如果你在集群环境下部署MySQL备份策略要区分主从节点。主节点备份用于恢复从节点备份用于日常数据导出和分析。别把从节点备份当成高可用方案从节点挂了一样要重新同步备份文件才是真正的保险。7. 从单机到集群这套表结构还够用吗说实话上面这套表结构支撑的是单主库架构。如果你的聊天室用户量真的起来了比如需要分库分表这套设计里的几个点依然能平滑过渡。用户表和好友关系表可以按用户ID做水平分片比如取模分片。好友关系表因为唯一的(user_id, friend_id)约束放在分片键上查询好友列表时可以通过user_id或friend_id路由到对应分片。消息表最麻烦。如果按消息ID做全局唯一主键分片规则要么用雪花算法生成全局ID要么用分段ID方案。我之前提到private_message表主键用AUTO_INCREMENT这个在单库下没有问题但分库后多个库各自生成的主键会冲突。如果你预判将来要分库建议从一开始就使用雪花ID或者其他分布式ID方案而不是AUTO_INCREMENT。分库分表引入后会有一个比较麻烦的问题——跨分片的事务。聊天室的消息发送和离线消息写入天然是一个事务里要操作多个表。如果用分库分表中间件跨片事务需要用分布式事务方案来兜底但分布式事务的性能开销又比较高。所以更务实的做法是尽量按业务域去分片让同一个用户的数据尽量落在一个分片里这样单聊场景就不需要跨分片事务只有群聊广播才需要额外处理。最后再抛一个方向。如果把这套表和Redis、Kafka配合起来消息发送链路可以做成MySQL负责持久化Redis负责缓存最近活跃会话和未读数Kafka负责异步分发消息到各nginx网关节点。我目前正在做这部分改造等跑通了再单独写一篇分享。