尧图网页设计 
说实话项目做到“聊天室”这个环节前面Nginx集群、WebSocket长连接、网关路由都折腾完以后我一度以为最难的部分已经过去了。结果在开始填数据层的时候发现自己还是太年轻——聊天室这种场景对数据库表的设计要求和普通管理后台完全是两码事。管理后台你慢慢写慢慢查没关系聊天室的消息表那是要扛高并发写入、高频查询、还要兼顾历史记录容量的。这篇就把我在这套Nginx集群聊天室项目里MySQL数据库表从设计到落地的完整过程写清楚包括每一张表的建表SQL、字段设计理由、外键和索引的取舍以及上线前必须处理好的几个坑。1. 聊天室场景对数据库的真实压力和普通项目差在哪先说清楚一个问题为什么聊天室这种项目数据库表不能照着通用后台管理系统那种思路随便建。1.1 三种数据的读写特征完全不同我在设计表结构之前先把聊天室项目里的数据分成三类这个分类直接决定了每张表的存储引擎、索引策略和写入方式。第一类是用户档案类数据用户名、昵称、头像、注册时间。这类数据的特点是读多写少用户注册一两次就不变了但是每次登录、每次进聊天室都要查一遍。第二类是关系类数据好友关系、群成员关系。这类数据的特点是写操作发生频率不高但查询路径非常固定就两种——查某人的好友列表、查某人加入了哪些群。第三类是消息类数据单聊消息、群聊消息。这是要命的部分写入频率极高而且消息产生之后就是冷数据几乎不会修改只会有新的消息不断追加进来。这三类数据如果混在一套设计思路里必然互相拖累。用户档案类适合用频繁更新的小表关系类适合用联合索引精准命中的窄表消息类适合用追加写入、定期归档的大表。所以我的第一版设计就明确了一点聊天室项目不应该在MySQL里存“所有”数据像用户的在线状态、未读数量这类实时性极强的数据我的方案是直接放RedisMySQL只负责落地的、可追溯的数据。这个分流思路确定下来之后每张表的设计才有明确指向性。1.2 集群环境下数据库的定位这个项目标题里带“nginx集群聊天室”所以数据库设计还必须考虑集群边界。我在前面已经搭好了Nginx负载均衡层后端的聊天室服务节点可以横向扩展但数据库在整套架构里的定位是独立于业务集群之外的共享存储。这意味着表设计上要避免两个问题一是表和表之间的关联查询不能做得太复杂因为业务节点多了以后大家都要抢数据库的连接资源复杂联查就是把压力成倍地甩给数据库二是所有表的连接信息要统一收敛到一个配置中心或统一的配置文件里不能让每个业务节点各连各的。我当时用的是Druid连接池统一配置了初始连接数、最小空闲连接数和最大活跃连接数尤其是最大活跃连接数这块在集群节点多起来以后每个节点都按单机标准去申请连接的话数据库的连接数很快会被打满。这个点我后面在连接池配置部分详细说。2. 六张核心表的建表SQL与逐字段设计说明所有表统一使用InnoDB引擎字符集用utf8mb4排序规则用utf8mb4_unicode_ci。这三项选择不是随手写的InnoDB支持行级锁和事务聊天室写消息的时候需要行锁粒度足够细utf8mb4是因为消息内容里一定会出现emoji表情老旧的utf8mb3存不了四个字节的字符这个坑我早年踩过建库时没注意线上第一条带表情的消息就把我打趴了。2.1 用户表CREATE TABLE users ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(32) NOT NULL COMMENT 用户名登录用, password_hash VARCHAR(128) NOT NULL COMMENT 密码哈希值注意不是明文密码, nickname VARCHAR(64) NOT NULL COMMENT 昵称聊天室显示用, avatar_url VARCHAR(255) DEFAULT NULL COMMENT 头像地址, email VARCHAR(128) DEFAULT NULL COMMENT 邮箱, del_flag TINYINT NOT NULL DEFAULT 0 COMMENT 逻辑删除0未删除 1已删除, 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), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT聊天室用户表;这张表我要特别说明几个细节点。首先是password_hash字段我见过不少项目直接把密码明文字符串存进去这在聊天室这种低安全等级的项目里看起来问题不大但一旦数据库被脱库所有用户的密码习惯就可能关联泄露。我的做法是服务端用BCrypt做哈希库表里只落哈希值校验也在服务端完成。其次是del_flag逻辑删除字段。为什么不用物理删除因为聊天室的好友关系、历史消息都引用用户ID物理删掉用户会导致消息记录里的发送者悬空后面查历史消息时会出现“查不到发送人”的脏数据。我统一用逻辑删除用户注销时只把del_flag置为1所有历史消息仍然可以正常追溯到该用户。username上的唯一索引是刚需登录时根据用户名精确匹配这个查询路径必须走索引不能有任何其他开销。2.2 好友关系表好友关系表是典型的多对多关系建模。我在设计时考虑过两种方案一种是存两条记录A-B一条B-A一条查询好友列表时只按user_id查询另一种是只存一条记录查询时用user_id ? OR friend_id ?的组合条件。对比下来我选了第二种虽然查询条件多一个OR但写入量少一半而且只需要维护一条记录的状态。CREATE TABLE friend_relation ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键ID, user_id BIGINT NOT NULL COMMENT 用户ID, friend_id BIGINT NOT NULL COMMENT 好友用户ID, remark VARCHAR(64) DEFAULT NULL COMMENT 好友备注名, status TINYINT NOT NULL DEFAULT 0 COMMENT 关系状态0正常 1已删除, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 成为好友时间, PRIMARY KEY (id), UNIQUE KEY uk_user_friend (user_id, friend_id), KEY idx_friend_id (friend_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT好友关系表;联合唯一索引uk_user_friend在业务层面保证同一条好友关系不会被重复插入这个索引也同时服务“查某人的好友列表”这个高频查询因为最左前缀原则单独用user_id查询同样能走这个联合索引。另外我特意加了一个idx_friend_id单列索引这是为了反向查询“谁是我的好友”准备的虽然这种场景不如正向查询多但加一个索引成本不高收益却实实在在。status字段不是用来做“拉黑”那种复杂关系状态的我就只用了0和1两个值0正常、1已删除。删除好友时更新这个字段就行保留历史建立关系的痕迹。2.3 群组表与群成员表聊天室如果只有一对一聊天架构会简单很多但群聊是必须有的能力。这涉及到两张表群组本身的基本信息和群成员关系。CREATE TABLE chat_group ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 群组ID, group_name VARCHAR(64) NOT NULL COMMENT 群名称, owner_id BIGINT NOT NULL COMMENT 群主用户ID, announcement VARCHAR(500) DEFAULT NULL COMMENT 群公告, max_members INT NOT NULL DEFAULT 200 COMMENT 最大成员数, status TINYINT NOT NULL DEFAULT 0 COMMENT 群状态0正常 1已解散, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), KEY idx_owner_id (owner_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT聊天群组表; CREATE TABLE group_member ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键ID, group_id BIGINT NOT NULL COMMENT 群组ID, user_id BIGINT NOT NULL COMMENT 成员用户ID, role TINYINT NOT NULL DEFAULT 1 COMMENT 成员角色0群主 1普通成员, join_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 入群时间, PRIMARY KEY (id), UNIQUE KEY uk_group_user (group_id, user_id), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT群成员表;这里有个设计细节值得展开。group_member表我建了uk_group_user和idx_user_id两个索引前者服务“查某个群有哪些成员”以及入群去重后者服务“查某人加入了哪些群“这个查询在用户侧打开群列表时是必用的。如果没有idx_user_id就只能全表扫用户一多就完蛋。chat_group表里的owner_id我加的是普通索引而不是唯一索引因为一个用户可以创建多个群群主和群之间是一对多关系不能用唯一约束。max_members字段是预留的容量控制新成员入群前先COUNT(*)查一下当前成员数超出就直接拒绝避免一个群无限膨胀。2.4 消息表聊天室的核心热表消息表是整个数据库设计中最重要的表没有之一。我在这张表上花的时间比前面所有表加起来都多。CREATE TABLE message ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 消息ID, msg_type TINYINT NOT NULL COMMENT 消息类型1文本 2图片 3语音 4视频, chat_type TINYINT NOT NULL COMMENT 聊天类型1单聊 2群聊, from_user_id BIGINT NOT NULL COMMENT 发送者ID, to_user_id BIGINT DEFAULT NULL COMMENT 接收者ID群聊时为空, group_id BIGINT DEFAULT NULL COMMENT 群组ID单聊时为空, content TEXT NOT NULL COMMENT 消息内容, status TINYINT NOT NULL DEFAULT 0 COMMENT 消息状态0正常 1撤回, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 发送时间, PRIMARY KEY (id), KEY idx_to_user_time (to_user_id, create_time), KEY idx_group_time (group_id, create_time), KEY idx_from_user_time (from_user_id, create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT聊天消息表;单聊和群聊消息我放在同一张表里用chat_type区分而不是拆成两张独立表。原因是单聊消息和群聊消息的字段结构几乎一样只有to_user_id和group_id二者取其一。拆成两张表意味着消息查询逻辑要两套而且未来如果要做跨类型的全局搜索还得做联合查询麻烦很多。合并成一张表之后写入路径统一查询路径也统一从工程上讲更合理。content字段我用了TEXT而不是VARCHAR。原因很实际文本消息虽然一般不长但图片和语音消息可能携带一些元数据JSON长度很可能超过VARCHAR的常规上限。在MySQL里TEXT类型最大支持65535字节对聊天室里的消息内容来说完全够用。这里有个小提示TEXT类型不能有默认值建表时不要尝试给它加DEFAULT MySQL 8.0之前会直接报错8.0之后虽然允许但通过表达式实现没必要。消息表上的三个二级索引是我反复斟酌过的。idx_to_user_time服务单聊历史记录查询idx_group_time服务群聊历史记录查询idx_from_user_time服务“我发送过的消息”这类个人记录查询。三个索引都是复合索引把create_time带进去是为了让排序直接在索引上完成避免文件排序。2.5 离线消息表的设计聊天室项目在用户离线期间产生的新消息需要一种机制在用户上线后补推。我见过两种主流方案一种是建独立的离线消息表用户上线时查这张表拿未读消息另一种是不建离线表而是在消息表上维护一个last_ack_message_id用户上线时拉取该ID之后的所有新消息。我最终选择了第二种思路但为了查起来清晰还是建了一张辅助表。CREATE TABLE offline_message ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键ID, user_id BIGINT NOT NULL COMMENT 离线用户ID, message_id BIGINT NOT NULL COMMENT 离线期间产生的消息ID, is_pushed TINYINT NOT NULL DEFAULT 0 COMMENT 是否已推送0未推送 1已推送, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 离线消息入库时间, PRIMARY KEY (id), KEY idx_user_pushed (user_id, is_pushed, id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT离线消息表;这张表的核心价值是记录“推送进度”。用户在线时不需要查它一旦用户掉线服务端把该时间段内产生的消息ID连带写入这张表用户上线后服务端先查is_pushed 0的记录把对应消息补推给客户端推送成功后把is_pushed置为1。这个流程把“补推哪些消息”这个复杂问题简化成了对一张小表的扫描而且因为只存消息ID数据量不会像消息表那样膨胀。这里我要提醒一个关键细节idx_user_pushed这个索引我特意把id也放进了索引列这样“取某用户未推送消息”的查询可以走覆盖索引查出来的就是有序的ID列表不需要再回表排序。3. 外键与索引的设计取舍这两件事决定后期维护的体感很多初学者在搞这种项目时特别喜欢给每张表都加上物理外键觉得这样数据才“严谨”。我在这套聊天室数据库里一张物理外键都没加。3.1 为什么我坚持不建物理外键物理外键的优点是数据库层面强制引用完整性比如你在message表里插入一条from_user_id 999的消息如果999这个用户不存在外键约束会直接拒绝插入。听起来很美好但代价是每次插入和删除都要做外键检查这个检查在单表单条记录时无感一旦进入集群环境和消息表这种高频写入场景外键检查带来的额外锁和IO损耗会被放大得非常明显。更大的问题在表结构变更时暴露出来。聊天室的群聊消息表和离线消息表都是有归档和分表需求的如果带了物理外键分表时要处理一堆外键约束DBA看了会想打人。而且外键约束还会限制你用批量工具做数据迁移简直是自己给自己上脚镣。所以我的方案是外键只存在于ER图层面和代码校验层面数据库表不加物理外键约束。具体做法是服务端在写入消息之前先校验用户是否存在、群是否存在应用层保证引用关系的正确性数据库层只通过索引来加速关联查询。3.2 索引设计背后的查询路径分析索引不是越多越好每个索引都要占用空间都影响写入性能。消息表是写入量最大的表如果索引建得不合理写入性能会成倍下降。我建索引的判断标准只有一个一这条查询路径是不是真实会发生的二是这条路径能不能命中现有索引。聊天室最核心的查询路径大概有四条用户登录username精确查询走用户表的uk_username拉取好友列表user_id查询走好友关系表的uk_user_friend拉取单聊历史to_user_id create_time组合查询走消息表的idx_to_user_time拉取群聊历史group_id create_time组合查询走消息表的idx_group_time这四条路径之外的查询都属于低频不需要额外建索引。比如按消息内容搜索聊天室项目一般部署ES来做全文检索MySQL的LIKE %关键词%查询在全表扫描下会拖垮数据库能不用就不用。3.3 一个真实的字符集坑我在项目联调早期犯过一个错误建库语句里写了DEFAULT CHARSETutf8mb4但没有给表单独指定字符集结果服务器上MySQL实例的默认字符集还停留在utf8mb3。前端发来一条带emoji的测试消息后端写入时直接报错Incorrect string value: \xF0\x9F\x98\x80 for column content。排查过程绕了不少弯最后定位到问题不在于建表语句而在于实例级别的默认字符集覆盖了表级别的设置。解决方案是把实例的character_set_server和每个库表的字符集统一检查一遍确保从连接层到表结构全部是utf8mb4。所以后来我写了一个统一的检查脚本上线前把三个关键变量打出来确认SHOW VARIABLES LIKE character_set_server; SHOW VARIABLES LIKE character_set_database; SHOW CREATE TABLE message;4. 建表之外必须配套的代码与初始化脚本表结构设计完之后工程上还要解决两个问题建表脚本怎么组织、数据访问层的代码怎么写才能在集群环境下稳定工作。4.1 初始化脚本schema与数据分离我习惯把数据库脚本拆成schema.sql和init_data.sql两份前者只负责建表后者负责写入初始化数据。为什么要分离因为项目部署到不同环境时表结构要统一更新但初始化数据往往因环境而异——本地开发要造一批测试用户生产环境只要默认管理员。-- schema.sql 关键片段 CREATE DATABASE IF NOT EXISTS chatroom DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE chatroom; SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS 0; -- 所有建表语句按依赖顺序排列users - friend_relation - chat_group - group_member - message - offline_message -- 每个建表语句前都加 DROP TABLE IF EXISTS保证可重复执行 SET FOREIGN_KEY_CHECKS 1;这块有一个细节要注意脚本开头要加SET NAMES utf8mb4否则命令行客户端连接MySQL时即使表和库都是utf8mb4客户端传输编码也可能是latin1写入中文内容时会乱码。这个坑在本地用MySQL命令行工具初始化时特别常见。4.2 数据访问层的最简代码结构与连接池配置表落地之后我用MyBatis作为数据访问层框架。以离线消息查询为例核心Mapper的SQL大概是这样的Mapper public interface OfflineMessageMapper { // 查询用户所有未推送的离线消息ID Select(SELECT message_id FROM offline_message WHERE user_id #{userId} AND is_pushed 0 ORDER BY id ASC LIMIT #{limit}) ListLong selectUnpushedMessageIds(Param(userId) Long userId, Param(limit) int limit); // 批量标记为已推送 Update(UPDATE offline_message SET is_pushed 1 WHERE user_id #{userId} AND message_id IN (scriptforeach collectionmessageIds itemmid separator, open( close)#{mid}/foreach/script)) int markAsPushed(Param(userId) Long userId, Param(messageIds) ListLong messageIds); }这个查询走的就是离线消息表那个覆盖索引idx_user_pushed查出来的message_id就是需要补推的消息。配合分页限制LIMIT每次最多取一定量避免一次性推太多把网络打爆。集群环境下的连接池配置我用的Druid核心配置项如下spring: datasource: url: jdbc:mysql://192.168.1.10:3306/chatroom?useSSLfalseserverTimezoneAsia/ShanghaicharacterEncodingutf8mb4 username: chatroom password: xxxxxx druid: initial-size: 5 min-idle: 5 max-active: 50 max-wait: 60000 test-while-idle: true test-on-borrow: false这里最关键的参数是max-active。单机部署时设50听起来保守但在Nginx集群后面有多个业务节点时50乘以节点数很可能就把数据库的max_connections打爆了。我当时的做法是数据库实例max_connections设为500后端3个节点每个节点的max-active设为100留出余量。还要在url上显式带上characterEncodingutf8mb4这个参数能解决大部分乱码问题比在代码里反复设置连接编码可靠得多。4.3 消息分页查询的经典SQL坑群聊历史记录翻页很多人一上来就写LIMIT offset, size。比如查看第100页直接LIMIT 9900, 100。这个写法在小数据量时没问题但消息表的数据量一旦超过百万行LIMIT的偏移量越大MySQL需要扫描并丢弃掉前面所有行才能返回目标数据查询会越来越慢。我的做法是改用游标分页翻页时不传页码传上一页最后一条消息的IDSELECT id, from_user_id, content, create_time FROM message WHERE group_id #{groupId} AND id #{lastMessageId} ORDER BY id DESC LIMIT 100;这里用id做游标而不是create_time是因为主键自带索引且唯一而create_time可能因为数据库精度问题出现相同值导致翻页丢消息。这个改法对聊天室这种“只看增量历史”的场景特别合适越往后翻性能越稳定。5. 上线前要提前规划的消息表容量与分表思路聊天室项目“做出来”容易“扛住”很难。消息表的容量规划如果拖到上线后再想基本就是灾难现场。5.1 容量估算一张表能撑多久假设一个中等活跃的聊天室日均消息量50万条单条消息平均1KB左右包括索引开销和行开销一天就是500MB左右的实际存储增量。一个月15GB一年接近180GB。单表数据量到千万行级别时即使索引建得再好查询性能都会有明显下滑。具体到什么时候必须分表我的经验线是单表行数超过2000万或者表容量超过50GB就需要认真考虑分表方案。这不是一个绝对标准但在这个量级之前做好规划后面会很从容。5.2 按时间分表是最适合聊天室业务的方案聊天室消息天然带时间属性而且历史消息的查询频率远低于近期消息所以最适合的切分维度是时间。我规划的是按月分表message_202506、message_202507每个月一张新表表的定义和原始message表保持一致但把主键改成(id, create_time)组合主键因为单纯自增主键跨月会重复。路由逻辑不放在数据库里放在MyBatis的拦截器里根据当前时间自动拼表名。查询历史消息时按时间范围路由到对应的月表。这就解决了单表膨胀问题而且归档很自然——超过N个月的表直接转储到冷存储业务层无感知。不过这套方案也有代价跨月查询时要同时查多张表然后做结果合并。好在聊天室场景里“一次查多个时间段”属于低频操作而且通常只发生在搜索场景那时已经有ES承接全文检索了不会直接打数据库。综合权衡下来按月分表仍然是最省心、最可靠的方案。表结构设计到这里整个聊天室项目的数据底座就完整了。回想从最开始一股脑建外键、不分读写特征、一个引擎走到底到后来逐步调整为分三类设计、物理外键全拆、按时间分表最大的体会是聊天室数据库表设计的核心不是“把关系理清”而是“预判未来半年的数据量增长和查询模式在源头把扩展空间留出来”。每次看到表数量不大却依然能稳定支撑集群压测的时候就知道当初的设计取舍没有白费。 Nginx集群聊天室MySQL数据库表设计实战：从建表SQL到分表策略
说实话项目做到“聊天室”这个环节前面Nginx集群、WebSocket长连接、网关路由都折腾完以后我一度以为最难的部分已经过去了。结果在开始填数据层的时候发现自己还是太年轻——聊天室这种场景对数据库表的设计要求和普通管理后台完全是两码事。管理后台你慢慢写慢慢查没关系聊天室的消息表那是要扛高并发写入、高频查询、还要兼顾历史记录容量的。这篇就把我在这套Nginx集群聊天室项目里MySQL数据库表从设计到落地的完整过程写清楚包括每一张表的建表SQL、字段设计理由、外键和索引的取舍以及上线前必须处理好的几个坑。1. 聊天室场景对数据库的真实压力和普通项目差在哪先说清楚一个问题为什么聊天室这种项目数据库表不能照着通用后台管理系统那种思路随便建。1.1 三种数据的读写特征完全不同我在设计表结构之前先把聊天室项目里的数据分成三类这个分类直接决定了每张表的存储引擎、索引策略和写入方式。第一类是用户档案类数据用户名、昵称、头像、注册时间。这类数据的特点是读多写少用户注册一两次就不变了但是每次登录、每次进聊天室都要查一遍。第二类是关系类数据好友关系、群成员关系。这类数据的特点是写操作发生频率不高但查询路径非常固定就两种——查某人的好友列表、查某人加入了哪些群。第三类是消息类数据单聊消息、群聊消息。这是要命的部分写入频率极高而且消息产生之后就是冷数据几乎不会修改只会有新的消息不断追加进来。这三类数据如果混在一套设计思路里必然互相拖累。用户档案类适合用频繁更新的小表关系类适合用联合索引精准命中的窄表消息类适合用追加写入、定期归档的大表。所以我的第一版设计就明确了一点聊天室项目不应该在MySQL里存“所有”数据像用户的在线状态、未读数量这类实时性极强的数据我的方案是直接放RedisMySQL只负责落地的、可追溯的数据。这个分流思路确定下来之后每张表的设计才有明确指向性。1.2 集群环境下数据库的定位这个项目标题里带“nginx集群聊天室”所以数据库设计还必须考虑集群边界。我在前面已经搭好了Nginx负载均衡层后端的聊天室服务节点可以横向扩展但数据库在整套架构里的定位是独立于业务集群之外的共享存储。这意味着表设计上要避免两个问题一是表和表之间的关联查询不能做得太复杂因为业务节点多了以后大家都要抢数据库的连接资源复杂联查就是把压力成倍地甩给数据库二是所有表的连接信息要统一收敛到一个配置中心或统一的配置文件里不能让每个业务节点各连各的。我当时用的是Druid连接池统一配置了初始连接数、最小空闲连接数和最大活跃连接数尤其是最大活跃连接数这块在集群节点多起来以后每个节点都按单机标准去申请连接的话数据库的连接数很快会被打满。这个点我后面在连接池配置部分详细说。2. 六张核心表的建表SQL与逐字段设计说明所有表统一使用InnoDB引擎字符集用utf8mb4排序规则用utf8mb4_unicode_ci。这三项选择不是随手写的InnoDB支持行级锁和事务聊天室写消息的时候需要行锁粒度足够细utf8mb4是因为消息内容里一定会出现emoji表情老旧的utf8mb3存不了四个字节的字符这个坑我早年踩过建库时没注意线上第一条带表情的消息就把我打趴了。2.1 用户表CREATE TABLE users ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(32) NOT NULL COMMENT 用户名登录用, password_hash VARCHAR(128) NOT NULL COMMENT 密码哈希值注意不是明文密码, nickname VARCHAR(64) NOT NULL COMMENT 昵称聊天室显示用, avatar_url VARCHAR(255) DEFAULT NULL COMMENT 头像地址, email VARCHAR(128) DEFAULT NULL COMMENT 邮箱, del_flag TINYINT NOT NULL DEFAULT 0 COMMENT 逻辑删除0未删除 1已删除, 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), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT聊天室用户表;这张表我要特别说明几个细节点。首先是password_hash字段我见过不少项目直接把密码明文字符串存进去这在聊天室这种低安全等级的项目里看起来问题不大但一旦数据库被脱库所有用户的密码习惯就可能关联泄露。我的做法是服务端用BCrypt做哈希库表里只落哈希值校验也在服务端完成。其次是del_flag逻辑删除字段。为什么不用物理删除因为聊天室的好友关系、历史消息都引用用户ID物理删掉用户会导致消息记录里的发送者悬空后面查历史消息时会出现“查不到发送人”的脏数据。我统一用逻辑删除用户注销时只把del_flag置为1所有历史消息仍然可以正常追溯到该用户。username上的唯一索引是刚需登录时根据用户名精确匹配这个查询路径必须走索引不能有任何其他开销。2.2 好友关系表好友关系表是典型的多对多关系建模。我在设计时考虑过两种方案一种是存两条记录A-B一条B-A一条查询好友列表时只按user_id查询另一种是只存一条记录查询时用user_id ? OR friend_id ?的组合条件。对比下来我选了第二种虽然查询条件多一个OR但写入量少一半而且只需要维护一条记录的状态。CREATE TABLE friend_relation ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键ID, user_id BIGINT NOT NULL COMMENT 用户ID, friend_id BIGINT NOT NULL COMMENT 好友用户ID, remark VARCHAR(64) DEFAULT NULL COMMENT 好友备注名, status TINYINT NOT NULL DEFAULT 0 COMMENT 关系状态0正常 1已删除, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 成为好友时间, PRIMARY KEY (id), UNIQUE KEY uk_user_friend (user_id, friend_id), KEY idx_friend_id (friend_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT好友关系表;联合唯一索引uk_user_friend在业务层面保证同一条好友关系不会被重复插入这个索引也同时服务“查某人的好友列表”这个高频查询因为最左前缀原则单独用user_id查询同样能走这个联合索引。另外我特意加了一个idx_friend_id单列索引这是为了反向查询“谁是我的好友”准备的虽然这种场景不如正向查询多但加一个索引成本不高收益却实实在在。status字段不是用来做“拉黑”那种复杂关系状态的我就只用了0和1两个值0正常、1已删除。删除好友时更新这个字段就行保留历史建立关系的痕迹。2.3 群组表与群成员表聊天室如果只有一对一聊天架构会简单很多但群聊是必须有的能力。这涉及到两张表群组本身的基本信息和群成员关系。CREATE TABLE chat_group ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 群组ID, group_name VARCHAR(64) NOT NULL COMMENT 群名称, owner_id BIGINT NOT NULL COMMENT 群主用户ID, announcement VARCHAR(500) DEFAULT NULL COMMENT 群公告, max_members INT NOT NULL DEFAULT 200 COMMENT 最大成员数, status TINYINT NOT NULL DEFAULT 0 COMMENT 群状态0正常 1已解散, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), KEY idx_owner_id (owner_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT聊天群组表; CREATE TABLE group_member ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键ID, group_id BIGINT NOT NULL COMMENT 群组ID, user_id BIGINT NOT NULL COMMENT 成员用户ID, role TINYINT NOT NULL DEFAULT 1 COMMENT 成员角色0群主 1普通成员, join_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 入群时间, PRIMARY KEY (id), UNIQUE KEY uk_group_user (group_id, user_id), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT群成员表;这里有个设计细节值得展开。group_member表我建了uk_group_user和idx_user_id两个索引前者服务“查某个群有哪些成员”以及入群去重后者服务“查某人加入了哪些群“这个查询在用户侧打开群列表时是必用的。如果没有idx_user_id就只能全表扫用户一多就完蛋。chat_group表里的owner_id我加的是普通索引而不是唯一索引因为一个用户可以创建多个群群主和群之间是一对多关系不能用唯一约束。max_members字段是预留的容量控制新成员入群前先COUNT(*)查一下当前成员数超出就直接拒绝避免一个群无限膨胀。2.4 消息表聊天室的核心热表消息表是整个数据库设计中最重要的表没有之一。我在这张表上花的时间比前面所有表加起来都多。CREATE TABLE message ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 消息ID, msg_type TINYINT NOT NULL COMMENT 消息类型1文本 2图片 3语音 4视频, chat_type TINYINT NOT NULL COMMENT 聊天类型1单聊 2群聊, from_user_id BIGINT NOT NULL COMMENT 发送者ID, to_user_id BIGINT DEFAULT NULL COMMENT 接收者ID群聊时为空, group_id BIGINT DEFAULT NULL COMMENT 群组ID单聊时为空, content TEXT NOT NULL COMMENT 消息内容, status TINYINT NOT NULL DEFAULT 0 COMMENT 消息状态0正常 1撤回, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 发送时间, PRIMARY KEY (id), KEY idx_to_user_time (to_user_id, create_time), KEY idx_group_time (group_id, create_time), KEY idx_from_user_time (from_user_id, create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT聊天消息表;单聊和群聊消息我放在同一张表里用chat_type区分而不是拆成两张独立表。原因是单聊消息和群聊消息的字段结构几乎一样只有to_user_id和group_id二者取其一。拆成两张表意味着消息查询逻辑要两套而且未来如果要做跨类型的全局搜索还得做联合查询麻烦很多。合并成一张表之后写入路径统一查询路径也统一从工程上讲更合理。content字段我用了TEXT而不是VARCHAR。原因很实际文本消息虽然一般不长但图片和语音消息可能携带一些元数据JSON长度很可能超过VARCHAR的常规上限。在MySQL里TEXT类型最大支持65535字节对聊天室里的消息内容来说完全够用。这里有个小提示TEXT类型不能有默认值建表时不要尝试给它加DEFAULT MySQL 8.0之前会直接报错8.0之后虽然允许但通过表达式实现没必要。消息表上的三个二级索引是我反复斟酌过的。idx_to_user_time服务单聊历史记录查询idx_group_time服务群聊历史记录查询idx_from_user_time服务“我发送过的消息”这类个人记录查询。三个索引都是复合索引把create_time带进去是为了让排序直接在索引上完成避免文件排序。2.5 离线消息表的设计聊天室项目在用户离线期间产生的新消息需要一种机制在用户上线后补推。我见过两种主流方案一种是建独立的离线消息表用户上线时查这张表拿未读消息另一种是不建离线表而是在消息表上维护一个last_ack_message_id用户上线时拉取该ID之后的所有新消息。我最终选择了第二种思路但为了查起来清晰还是建了一张辅助表。CREATE TABLE offline_message ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键ID, user_id BIGINT NOT NULL COMMENT 离线用户ID, message_id BIGINT NOT NULL COMMENT 离线期间产生的消息ID, is_pushed TINYINT NOT NULL DEFAULT 0 COMMENT 是否已推送0未推送 1已推送, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 离线消息入库时间, PRIMARY KEY (id), KEY idx_user_pushed (user_id, is_pushed, id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT离线消息表;这张表的核心价值是记录“推送进度”。用户在线时不需要查它一旦用户掉线服务端把该时间段内产生的消息ID连带写入这张表用户上线后服务端先查is_pushed 0的记录把对应消息补推给客户端推送成功后把is_pushed置为1。这个流程把“补推哪些消息”这个复杂问题简化成了对一张小表的扫描而且因为只存消息ID数据量不会像消息表那样膨胀。这里我要提醒一个关键细节idx_user_pushed这个索引我特意把id也放进了索引列这样“取某用户未推送消息”的查询可以走覆盖索引查出来的就是有序的ID列表不需要再回表排序。3. 外键与索引的设计取舍这两件事决定后期维护的体感很多初学者在搞这种项目时特别喜欢给每张表都加上物理外键觉得这样数据才“严谨”。我在这套聊天室数据库里一张物理外键都没加。3.1 为什么我坚持不建物理外键物理外键的优点是数据库层面强制引用完整性比如你在message表里插入一条from_user_id 999的消息如果999这个用户不存在外键约束会直接拒绝插入。听起来很美好但代价是每次插入和删除都要做外键检查这个检查在单表单条记录时无感一旦进入集群环境和消息表这种高频写入场景外键检查带来的额外锁和IO损耗会被放大得非常明显。更大的问题在表结构变更时暴露出来。聊天室的群聊消息表和离线消息表都是有归档和分表需求的如果带了物理外键分表时要处理一堆外键约束DBA看了会想打人。而且外键约束还会限制你用批量工具做数据迁移简直是自己给自己上脚镣。所以我的方案是外键只存在于ER图层面和代码校验层面数据库表不加物理外键约束。具体做法是服务端在写入消息之前先校验用户是否存在、群是否存在应用层保证引用关系的正确性数据库层只通过索引来加速关联查询。3.2 索引设计背后的查询路径分析索引不是越多越好每个索引都要占用空间都影响写入性能。消息表是写入量最大的表如果索引建得不合理写入性能会成倍下降。我建索引的判断标准只有一个一这条查询路径是不是真实会发生的二是这条路径能不能命中现有索引。聊天室最核心的查询路径大概有四条用户登录username精确查询走用户表的uk_username拉取好友列表user_id查询走好友关系表的uk_user_friend拉取单聊历史to_user_id create_time组合查询走消息表的idx_to_user_time拉取群聊历史group_id create_time组合查询走消息表的idx_group_time这四条路径之外的查询都属于低频不需要额外建索引。比如按消息内容搜索聊天室项目一般部署ES来做全文检索MySQL的LIKE %关键词%查询在全表扫描下会拖垮数据库能不用就不用。3.3 一个真实的字符集坑我在项目联调早期犯过一个错误建库语句里写了DEFAULT CHARSETutf8mb4但没有给表单独指定字符集结果服务器上MySQL实例的默认字符集还停留在utf8mb3。前端发来一条带emoji的测试消息后端写入时直接报错Incorrect string value: \xF0\x9F\x98\x80 for column content。排查过程绕了不少弯最后定位到问题不在于建表语句而在于实例级别的默认字符集覆盖了表级别的设置。解决方案是把实例的character_set_server和每个库表的字符集统一检查一遍确保从连接层到表结构全部是utf8mb4。所以后来我写了一个统一的检查脚本上线前把三个关键变量打出来确认SHOW VARIABLES LIKE character_set_server; SHOW VARIABLES LIKE character_set_database; SHOW CREATE TABLE message;4. 建表之外必须配套的代码与初始化脚本表结构设计完之后工程上还要解决两个问题建表脚本怎么组织、数据访问层的代码怎么写才能在集群环境下稳定工作。4.1 初始化脚本schema与数据分离我习惯把数据库脚本拆成schema.sql和init_data.sql两份前者只负责建表后者负责写入初始化数据。为什么要分离因为项目部署到不同环境时表结构要统一更新但初始化数据往往因环境而异——本地开发要造一批测试用户生产环境只要默认管理员。-- schema.sql 关键片段 CREATE DATABASE IF NOT EXISTS chatroom DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE chatroom; SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS 0; -- 所有建表语句按依赖顺序排列users - friend_relation - chat_group - group_member - message - offline_message -- 每个建表语句前都加 DROP TABLE IF EXISTS保证可重复执行 SET FOREIGN_KEY_CHECKS 1;这块有一个细节要注意脚本开头要加SET NAMES utf8mb4否则命令行客户端连接MySQL时即使表和库都是utf8mb4客户端传输编码也可能是latin1写入中文内容时会乱码。这个坑在本地用MySQL命令行工具初始化时特别常见。4.2 数据访问层的最简代码结构与连接池配置表落地之后我用MyBatis作为数据访问层框架。以离线消息查询为例核心Mapper的SQL大概是这样的Mapper public interface OfflineMessageMapper { // 查询用户所有未推送的离线消息ID Select(SELECT message_id FROM offline_message WHERE user_id #{userId} AND is_pushed 0 ORDER BY id ASC LIMIT #{limit}) ListLong selectUnpushedMessageIds(Param(userId) Long userId, Param(limit) int limit); // 批量标记为已推送 Update(UPDATE offline_message SET is_pushed 1 WHERE user_id #{userId} AND message_id IN (scriptforeach collectionmessageIds itemmid separator, open( close)#{mid}/foreach/script)) int markAsPushed(Param(userId) Long userId, Param(messageIds) ListLong messageIds); }这个查询走的就是离线消息表那个覆盖索引idx_user_pushed查出来的message_id就是需要补推的消息。配合分页限制LIMIT每次最多取一定量避免一次性推太多把网络打爆。集群环境下的连接池配置我用的Druid核心配置项如下spring: datasource: url: jdbc:mysql://192.168.1.10:3306/chatroom?useSSLfalseserverTimezoneAsia/ShanghaicharacterEncodingutf8mb4 username: chatroom password: xxxxxx druid: initial-size: 5 min-idle: 5 max-active: 50 max-wait: 60000 test-while-idle: true test-on-borrow: false这里最关键的参数是max-active。单机部署时设50听起来保守但在Nginx集群后面有多个业务节点时50乘以节点数很可能就把数据库的max_connections打爆了。我当时的做法是数据库实例max_connections设为500后端3个节点每个节点的max-active设为100留出余量。还要在url上显式带上characterEncodingutf8mb4这个参数能解决大部分乱码问题比在代码里反复设置连接编码可靠得多。4.3 消息分页查询的经典SQL坑群聊历史记录翻页很多人一上来就写LIMIT offset, size。比如查看第100页直接LIMIT 9900, 100。这个写法在小数据量时没问题但消息表的数据量一旦超过百万行LIMIT的偏移量越大MySQL需要扫描并丢弃掉前面所有行才能返回目标数据查询会越来越慢。我的做法是改用游标分页翻页时不传页码传上一页最后一条消息的IDSELECT id, from_user_id, content, create_time FROM message WHERE group_id #{groupId} AND id #{lastMessageId} ORDER BY id DESC LIMIT 100;这里用id做游标而不是create_time是因为主键自带索引且唯一而create_time可能因为数据库精度问题出现相同值导致翻页丢消息。这个改法对聊天室这种“只看增量历史”的场景特别合适越往后翻性能越稳定。5. 上线前要提前规划的消息表容量与分表思路聊天室项目“做出来”容易“扛住”很难。消息表的容量规划如果拖到上线后再想基本就是灾难现场。5.1 容量估算一张表能撑多久假设一个中等活跃的聊天室日均消息量50万条单条消息平均1KB左右包括索引开销和行开销一天就是500MB左右的实际存储增量。一个月15GB一年接近180GB。单表数据量到千万行级别时即使索引建得再好查询性能都会有明显下滑。具体到什么时候必须分表我的经验线是单表行数超过2000万或者表容量超过50GB就需要认真考虑分表方案。这不是一个绝对标准但在这个量级之前做好规划后面会很从容。5.2 按时间分表是最适合聊天室业务的方案聊天室消息天然带时间属性而且历史消息的查询频率远低于近期消息所以最适合的切分维度是时间。我规划的是按月分表message_202506、message_202507每个月一张新表表的定义和原始message表保持一致但把主键改成(id, create_time)组合主键因为单纯自增主键跨月会重复。路由逻辑不放在数据库里放在MyBatis的拦截器里根据当前时间自动拼表名。查询历史消息时按时间范围路由到对应的月表。这就解决了单表膨胀问题而且归档很自然——超过N个月的表直接转储到冷存储业务层无感知。不过这套方案也有代价跨月查询时要同时查多张表然后做结果合并。好在聊天室场景里“一次查多个时间段”属于低频操作而且通常只发生在搜索场景那时已经有ES承接全文检索了不会直接打数据库。综合权衡下来按月分表仍然是最省心、最可靠的方案。表结构设计到这里整个聊天室项目的数据底座就完整了。回想从最开始一股脑建外键、不分读写特征、一个引擎走到底到后来逐步调整为分三类设计、物理外键全拆、按时间分表最大的体会是聊天室数据库表设计的核心不是“把关系理清”而是“预判未来半年的数据量增长和查询模式在源头把扩展空间留出来”。每次看到表数量不大却依然能稳定支撑集群压测的时候就知道当初的设计取舍没有白费。