Nginx集群聊天室项目：MySQL表结构设计与连接池配置实践

我做了一个nginx集群聊天室的项目前面几篇都在折腾nginx反向代理、负载均衡策略和WebSocket会话保持流量分发到几个Tomcat节点之后功能层面的问题就全压到了数据层。聊天室要真正能跑起来用户要注册登录、好友要有增删关系、群聊要有成员关系、消息要落地这些功能背后全是一张张MySQL表。如果不提前把表结构设计清楚后面开发时就会陷入“改一张表要连带改五张表”的泥潭。这篇就是整个系列的第四篇专门记录这套聊天室的MySQL数据库表设置和配套代码从建表SQL、MyBatis调用到集群环境下的连接池配置都会提到。如果你正在做类似的聊天室项目或者被社交关系表和逻辑删除反复折磨这篇里应该能找到想要的答案。1. 数据库表整体设计思路1.1 聊天室项目到底需要哪些表我在设计之前先把业务实体捋了一遍。聊天室的核心场景无非是用户注册登录、添加好友、创建群聊、群成员管理、单聊消息、群聊消息。这六个场景指向的实体都能收敛到几张基础表上用户表user要承担认证和用户画像好友关系表user_friend用来存双向好友关系群组表chat_group存群的基本信息群成员表group_member解决一个人加入多个群、一个群有多个人的多对多关系聊天消息表chat_message存所有消息内容。如果还要做会话列表、已读未读、未读消息数这些功能就得再加入会话表和会话成员表。我这一版把会话也单独拆出来了因为后面做消息列表、已读回执会轻松很多。这组表的关系很明确user_id是贯穿所有表的线索好友表里两个user_id对映一段关系群组表和群成员表通过group_id关联消息表通过session_id归属到某个会话。表与表之间我刻意没有加任何物理外键原因后面会详细说。从实际经验看这个表结构已经能覆盖一个中小型聊天室的所有功能而且比较容易扩展。表名业务作用核心关联user用户账号与资料主键被多表引用user_friend好友关系user_id, friend_user_idchat_group群组信息owner_user_idgroup_member群成员关系group_id, user_idchat_session会话单聊/群聊关联双人或群成员chat_session_member会话成员与已读位置session_id, user_idchat_message聊天消息内容session_id, sender_id1.2 字段设计、索引取舍和“为什么不用外键”表结构设计的时候我给自己定了几条铁律。第一主键统一用BIGINT AUTO_INCREMENT。聊天室不是高并发金融系统用自增主键不会遇到什么瓶颈而且InnoDB聚簇索引对自增主键非常友好插入顺序和物理存储顺序一致可以减少页分裂。如果以后要迁移到分布式ID方案BIGINT也留足了空间。第二每张表都保留create_time、update_time两个时间字段用DATETIME类型默认值设为CURRENT_TIMESTAMP更新时自动刷新。这样做的好处是后端代码里完全不用手动维护时间排查线上数据问题时也能一眼看出记录是何时写入的。第三每张表都加一个is_deleted逻辑删除标记。聊天室这类C端产品用户可能注销、退群、删好友但业务上往往想保留历史关联数据用于审计或者悔撤销恢复。直接用DELETE物理删数据太不优雅我统一用TINYINT的删除标记查询时强制带is_deleted 0条件。第四索引设计一定要围绕“怎么查”来设计。比如好友表要按“我的好友列表”查就要在user_id上建索引消息表要按会话查最近消息就建(session_id, send_time)复合索引群成员表要按群查成员就在group_id上建索引。不要听网上的人说“索引越多越好”聊天室每个写操作都涉及多条表索引过多会让插入性能明显下降我最终的策略是在每个表只建少数几个高频查询的索引。第五坚决不用物理外键。很多教程让建表时用FOREIGN KEY约束我在真实项目里吃过亏物理外键会让每次insert/update都去检查关联表集群环境下MySQL压力本来就大这种额外的约束检查会放大延迟而且一旦要分库分表物理外键会成为最大的迁移障碍。我的做法是只保留逻辑外键也就是通过SQL的JOIN或者应用层代码去维护关系MySQL只管存数据不要干审查关系的活。还有一个很多人容易忽略的点字符集必须用utf8mb4。做聊天室消息里难免有emoji老版本的utf8只能存三个字节表情一发就报错乱码。排序规则我用utf8mb4_unicode_ci虽然比general_ci略慢一点点但对中文和特殊符号的排序更准确聊天室里这个损耗可以忽略。2. 建表SQL与表结构详解2.1 用户表和好友关系表用户表是整个系统的基础我建表SQL是这样写的CREATE TABLE user ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 用户ID, username VARCHAR(50) NOT NULL COMMENT 登录名, password VARCHAR(100) NOT NULL COMMENT 登录密码存BCrypt哈希, nickname VARCHAR(50) NOT NULL COMMENT 用户昵称, avatar VARCHAR(255) DEFAULT NULL COMMENT 头像URL, signature VARCHAR(255) DEFAULT NULL COMMENT 个性签名, status TINYINT NOT NULL DEFAULT 1 COMMENT 状态1在线2离线3隐身, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT 逻辑删除0正常1已删, PRIMARY KEY (id), UNIQUE KEY uk_username (username), KEY idx_status (status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT用户表;用户名做了真正的唯一索引因为登录名不允许重复注销用户如果要保留历史数据我宁愿用改名或换注销方案也不会牺牲这种强约束。密码字段长度给到100因为BCrypt每次生成的哈希长度是60个字符左右历史兼容项目还可能有其他算法留足余量。头像和签名这类信息都允许为空减少不必要的存储。好友关系表我用了两个用户ID表达关系同时存了备注和分组名称CREATE TABLE user_friend ( id BIGINT NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL COMMENT 用户ID, friend_user_id BIGINT NOT NULL COMMENT 好友用户ID, remark VARCHAR(50) DEFAULT NULL COMMENT 好友备注, group_name VARCHAR(50) DEFAULT NULL COMMENT 好友分组名称, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, is_deleted TINYINT NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uk_user_friend (user_id, friend_user_id, is_deleted), KEY idx_friend_user_id (friend_user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT好友关系表;注意这个表的唯一键我最初直接建了(user_id, friend_user_id, is_deleted)。它的想法是A加B一次正常记录是user_idAfriend_user_idBis_deleted0如果解除好友就把is_deleted置为1以后再添加B还能有一条is_deleted0的新记录。实际用下来发现一个大坑如果用户反复删除再添加第二次删除后新记录也被置为1此时会和第一次删除的旧记录在(user_id, friend_user_id, is_deleted)上撞车直接报唯一键冲突。这个问题的解决办法我留到第4章专门讲因为踩的人实在太多了。2.2 群组表和群成员表群组的建表SQL很直接CREATE TABLE chat_group ( id BIGINT NOT NULL AUTO_INCREMENT, group_name VARCHAR(50) NOT NULL COMMENT 群名称, owner_user_id BIGINT NOT NULL COMMENT 群主用户ID, notice VARCHAR(500) DEFAULT NULL COMMENT 群公告, max_members INT NOT NULL DEFAULT 500 COMMENT 最大人数, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, is_deleted TINYINT NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY idx_owner_user_id (owner_user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT群组表;群主信息我放在chat_group里冗余了一份但群主同时也是群成员所以group_member里也要有一条role1的记录。这样查询群列表或者做权限校验很方便不需要每次拿owner_user_id再去关联查询群成员表。群成员表是典型的多对多关系表CREATE TABLE group_member ( id BIGINT NOT NULL AUTO_INCREMENT, group_id BIGINT NOT NULL COMMENT 群ID, user_id BIGINT NOT NULL COMMENT 用户ID, role TINYINT NOT NULL DEFAULT 0 COMMENT 角色0普通成员1群主2管理员, muted_until DATETIME DEFAULT NULL COMMENT 禁言截止时间, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, is_deleted TINYINT NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uk_group_member (group_id, user_id, is_deleted), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT群成员表;查询“某人加入了哪些群”就靠group_member表上的idx_user_id索引。查询“群里有哪些成员”就靠uk_group_member的前缀(group_id)索引。这里和好友表一样(group_id, user_id, is_deleted)的唯一键也会遇到“退群后重复退群”的冲突问题。后面会一起解释。2.3 会话表、会话成员表和聊天消息表我把单聊和群聊收敛成了统一会话模型。每个双人聊天会创建一条chat_session记录然后往chat_session_member里塞两条成员关系每个群聊在创建群的同时也会生成一条session_type1的会话记录。消息表里不直接存对方的user_id而是存session_id这是为了简化查询逻辑。CREATE TABLE chat_session ( id BIGINT NOT NULL AUTO_INCREMENT, session_type TINYINT NOT NULL COMMENT 会话类型0单聊1群聊, name VARCHAR(100) DEFAULT NULL COMMENT 会话名称群聊时冗余群名, avatar VARCHAR(255) DEFAULT NULL COMMENT 会话头像群聊时冗余群头像, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, is_deleted TINYINT NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY idx_session_type (session_type) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT会话表;CREATE TABLE chat_session_member ( id BIGINT NOT NULL AUTO_INCREMENT, session_id BIGINT NOT NULL COMMENT 会话ID, user_id BIGINT NOT NULL COMMENT 用户ID, last_read_msg_id BIGINT NOT NULL DEFAULT 0 COMMENT 该用户在此会话中最后已读的消息ID, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, is_deleted TINYINT NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uk_session_member (session_id, user_id, is_deleted), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT会话成员表;消息表是聊天室数据量最大的表我这样建CREATE TABLE chat_message ( id BIGINT NOT NULL AUTO_INCREMENT, session_id BIGINT NOT NULL COMMENT 会话ID, sender_id BIGINT NOT NULL COMMENT 发送者用户ID, content TEXT NOT NULL COMMENT 消息内容, msg_type TINYINT NOT NULL DEFAULT 0 COMMENT 消息类型0文本1图片2文件3语音, reply_to_msg_id BIGINT DEFAULT NULL COMMENT 回复的消息ID, client_msg_id VARCHAR(64) DEFAULT NULL COMMENT 客户端去重ID, status TINYINT NOT NULL DEFAULT 0 COMMENT 状态0未读1已读2撤回, send_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 发送时间, is_deleted TINYINT NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY idx_session_send (session_id, send_time, id), KEY idx_sender_id (sender_id), UNIQUE KEY uk_client_msg_id (client_msg_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT聊天消息表;这里有几个细节要解释。idx_session_send是核心索引查询一个会话的历史消息时SQL会走WHERE session_id ? ORDER BY send_time DESC, id DESC这个复合索引让MySQL只需要查找该session的索引段然后按顺序回表拿数据。client_msg_id是客户端生成的一个UUID用来做消息幂等防止客户端网络重试导致一条消息被插入两次MySQL唯一索引天然保证client_msg_id不能重复而且多个NULL值在MySQL的UNIQUE索引中是允许的所以客户端没传时留NULL不会有冲突。send_time在查询时会参与排序所以必须跟session_id建联合索引如果只建session_id索引排序就要走内存filesort数据量大时非常慢。会话成员表里的last_read_msg_id是已读列表方案的一个关键字段。用户打开某个会话时把他在这个会话里的last_read_msg_id更新到当前最大的消息ID计算未读数就是COUNT(*) WHERE session_id? AND idlast_read_msg_id。这个方案比在消息表里逐条维护已读状态要省很多空间。3. 数据库连接与访问代码实现3.1 nginx集群下的数据库连接池配置表建好了代码要能连上库。这个项目用Spring Boot数据库连接池用的是HikariCP。在nginx集群场景下每个Web节点都是独立进程每个进程都有自己的连接池。我一开始没仔细算直接把每个节点连接池最大值设成了50四个节点就是200个连接结果MySQL默认的max_connections只有151应用启动后还没跑业务数据库就报警告了。配置如下这是按每个节点20个最大连接来的spring.datasource.urljdbc:mysql://192.168.10.20:3306/chatroom?useUnicodetruecharacterEncodingutf8serverTimezoneAsia/ShanghairewriteBatchedStatementstrue spring.datasource.usernamechatroom spring.datasource.password你的密码 spring.datasource.driver-class-namecom.mysql.cj.jdbc.Driver spring.datasource.hikari.minimum-idle5 spring.datasource.hikari.maximum-pool-size20 spring.datasource.hikari.connection-timeout30000 spring.datasource.hikari.idle-timeout600000 spring.datasource.hikari.max-lifetime1800000rewriteBatchedStatementstrue特别重要MySQL驱动在批量插入时会自己把多条insert重写为一条多值insert性能提升非常明显做群发消息或者初始化群成员时能快一个数量级。多节点并发时连接池大小不能拍脑袋。我后来按“单节点并发数/单查询耗时毫秒/1000”粗算简单说就是一个连接在同一时刻只能服务一个SQL你希望一个节点同时能处理40个请求单个请求平均查3次库每次5毫秒那连接池20就够。给每个节点设204个节点80个连接MySQL这边还有系统连接和其他开销余量还算健康。如果节点数量扩展到10个以上建议要么减小每个节点的pool大小要么直接升配MySQL并修改max_connections。另外nginx集群里的业务都是通过nginx反向代理进来的数据库连接池的异常处理必须考虑“节点重启”。我在实际部署时发现某台节点被杀掉重启后老连接还在MySQL端没有释放新节点又建新连接很容易把连接数顶满。所以Hikari的max-lifetime我刻意设成30分钟比MySQL的wait_timeout默认8小时短很多让连接池主动老化旧连接避免用到一个被服务端掐断的“僵尸连接”。3.2 基于MyBatis的Mapper代码示例项目用的是MyBatis。用户注册和登录是最基础的两个接口。用户注册的Mapper接口写法如下Mapper public interface UserMapper { int insertUser(User user); User selectByUsername(String username); }对应的XMLinsert idinsertUser useGeneratedKeystrue keyPropertyid INSERT INTO user (username, password, nickname, avatar, signature, status) VALUES (#{username}, #{password}, #{nickname}, #{avatar}, #{signature}, #{status}) /insert select idselectByUsername resultTypeUser SELECT id, username, nickname, avatar, signature, status FROM user WHERE username #{username} AND is_deleted 0 /select登录密码别用明文存建议用BCrypt加密BCryptPasswordEncoder可以直接塞进Spring容器里。查询时把password排除掉避免密码哈希传到前端。逻辑删除条件一定要写在XML里而且不要用SELECT *只select自己需要的列既省带宽也方便后续维护字段。好友添加涉及关系写入我直接在Service层加事务Transactional(rollbackFor Exception.class) public void addFriend(long userId, long friendUserId) { UserFriend relation new UserFriend(); relation.setUserId(userId); relation.setFriendUserId(friendUserId); friendMapper.insert(relation); UserFriend reverseRelation new UserFriend(); reverseRelation.setUserId(friendUserId); reverseRelation.setFriendUserId(userId); friendMapper.insert(reverseRelation); }这个事务很必要A添加B成功但B添加A失败会出现单向好友的脏数据聊天室的好友列表就乱了。事务保证两条记录同时成功或同时失败。创建群组也同理插入群表、插入群主作为成员的记录、插入会话表和会话成员记录这四步必须在同一个事务里执行。3.3 消息发送与历史消息查询的实现细节发送消息的Mapper插入写法我用自增主键需要把插入后的消息ID返回insert idinsertMessage useGeneratedKeystrue keyPropertyid INSERT INTO chat_message (session_id, sender_id, content, msg_type, reply_to_msg_id, client_msg_id, status) VALUES (#{sessionId}, #{senderId}, #{content}, #{msgType}, #{replyToMsgId}, #{clientMsgId}, 0) /insert这里有一个实际项目里很关键的幂等处理客户端发送消息时会先本地生成一个client_msg_id如果客户端因为网络超时重发了同一个请求后端通过唯一索引uk_client_msg_id捕获到冲突直接忽略重复插入。代码里要处理这个异常不能让它直接抛给用户。我一般用try-catch捕获DuplicateKeyException然后查询出原消息返回给客户端而不是报错。拉取历史消息用游标分页避免深分页那种OFFSET越来越慢的问题SELECT id, session_id, sender_id, content, msg_type, send_time FROM chat_message WHERE session_id #{sessionId} AND id #{lastMsgId} AND is_deleted 0 ORDER BY id ASC LIMIT 100这种方式是增量拉取配合idx_session_send索引即使消息表有几百万行也只扫描该session对应的那一段索引查询毫秒级返回。如果直接用LIMIT 100000, 100这种深分页MySQL要扫10万行后再丢前99900行性能很灾难。4. 常见问题与排查技巧实录4.1 软删除和唯一键冲突为什么软删除之后无法新建了这个坑我前面反复提到。很多人在唯一键里直接拼上is_deleted字段比如UNIQUE KEY uk_user_friend (user_id, friend_user_id, is_deleted)假设A添加了B然后解除好友。第一次解除时把这条记录is_deleted置为1。之后A又想加B插入新记录(user_idA, friend_user_idB, is_deleted0)此时不冲突因为(…,0)和(…,1)不一样。问题出在第二次解除A把这条新记录再次置为1数据库里就有两条(A,B,1)唯一键直接爆炸。群成员退群再重进再退群是同一个道理。这个场景我试过几种方案分享我最终的解法。最推荐的是增加一个delete_token列唯一键不再用is_deleted而是用业务唯一字段加上delete_tokenALTER TABLE user_friend ADD COLUMN delete_token VARCHAR(36) DEFAULT NULL COMMENT 逻辑删除标记删除时填入UUID, DROP INDEX uk_user_friend, ADD UNIQUE KEY uk_user_friend_del (user_id, friend_user_id, delete_token);未删除的数据delete_token为NULLMySQL的UNIQUE索引允许多个NULL值存在所以同一对好友只允许存在一条delete_token为空的数据。删除好友时把delete_token更新成一个随机UUID这样多次删除都会生成不同的UUID永远不会冲突同时is_deleted字段还能用于查询过滤。这个方案同样用在群成员表、会话成员表上适用面很广。如果你的业务允许物理删除也可以干脆不保留历史直接DELETE掉旧记录然后重建新记录但既然用逻辑删除还是用delete_token最省心。4.2 nginx集群下连接池、主从延迟和事务的真实问题多节点部署时数据库一旦扛不住不会是MySQL自己挂掉而是连接被占满导致所有接口都变慢。我的排查流程是这样先看SHOW PROCESSLIST里有没有大量Sleep连接再看应用的连接池是不是不够用最后查是不是有慢SQL长时间占用连接。之前我遇到过一条查询消息列表的SQL因为WHERE字段类型不匹配导致索引失效全表扫描单条查询跑了3秒把连接池里的连接全部拖住其他请求都在排队等连接整个聊天室看起来就是卡死状态。用EXPLAIN看到typeALL之后就立刻去改了索引。主从读写分离在集群聊天室里是必然的但会引入一个很微妙的延迟问题用户刚注册完马上跳转登录如果登录查询走的是从库而主从同步还没完成就会报“用户不存在”。我的处理是在读写分离框架里配置“读操作强制走主库”的规则比如用户完成注册后30秒内的请求走主库或者对特定Mapper方法标记强制主库。网上有人分享说“写完立即读用缓存”那也可以但我更喜欢直接让关键读走主库逻辑简单不容易出现缓存不一致。事务方面也有一个我在集群下踩过的坑不要在一个事务里做远程调用。比如点了“创建群聊”你在这个事务里往chat_group插入群然后又调用文件服务上传群头像并等待返回这时候事务一直开着数据库连接被占用如果文件服务慢连接池就被拖死。正确做法是先上传文件拿到结果再开启数据库事务业务事务越小越好。4.3 消息表数据量膨胀和索引失效怎么办聊天室最怕的就是消息表无限膨胀。单表几千万行之后即使有索引也会因为B树层级变深和回表代价升高而变慢。我在设计时就想过这个事常用的处理方案有三种按月分区、按会话哈希分表、冷热数据归档。按月分区最简单适合消息量增长平稳、需要按时间查历史消息的场景。建表可以这样ALTER TABLE chat_message PARTITION BY RANGE (TO_DAYS(send_time)) ( PARTITION p202501 VALUES LESS THAN (TO_DAYS(2025-02-01)), PARTITION p202502 VALUES LESS THAN (TO_DAYS(2025-03-01)), PARTITION p202503 VALUES LESS THAN (TO_DAYS(2025-04-01)) );分区不是让你每个分区都有一张表它底层还是一张表但MySQL查询时可以根据send_time条件只扫描匹配分区历史消息的清理也变成DROP PARTITION瞬间完成不会产生大事务。如果消息量大到单实例MySQL已经扛不住那时候才考虑按session_id哈希分库分表不过那是另一个大工程了。索引失效这个问题我在这个项目中实际遇到过两个经典场景。第一个是对索引字段用函数比如WHERE DATE_FORMAT(send_time, %Y-%m-%d) 2025-01-01这样索引直接失效因为MySQL要每条记录先算函数结果。正确写法是WHERE send_time 2025-01-01 AND send_time 2025-01-02利用范围查询才能走索引。第二个是隐式类型转换比如session_id是BIGINT但代码里传了一个字符串数字给MapperMySQL内部类型转换后可能会让索引失效。我后来把Mapper参数类型钉死所有ID都用Long再没犯过这个错。最后说一个我自己的习惯这套表上线后我又花了半小时把所有SELECT语句都检查了一遍确保没有SELECT *没有多余的显示事务嵌套没有在WHERE后面对索引字段做任何运算。聊天室项目功能看着简单其实数据层最考验细节。如果你现在也正在做类似的聊天室建议先花半天把表结构和唯一索引想清楚特别是逻辑删除和唯一键的兼容方案想清楚再动手写代码后面能省掉好几个通宵。