在聊天系统中如何在MySQL中高效存储和拉取离线消息记录

阿芳吖_5858

阿芳吖_5858

2026-09-29

722人浏览

原创

离线消息表结构设计需避开大文本字段和冗余索引:content用MEDIUMTEXT,仅对receiver_id+status建联合索引,created_at单独建索引,删除非必要索引;采用覆盖索引(含id、content等)避免回表,用分区表+DROP PARTITION替代DELETE清理已读消息,写入时批量INSERT并禁用autocommit。

在聊天系统中如何在mysql中高效存储和拉取离线消息记录

离线消息表结构设计要避开大文本字段和冗余索引

聊天系统里离线消息最怕“一查就卡”,根源常在表结构。别把 content 字段设成 TEXT 还加全文索引——它不参与 WHERE 条件时纯属拖累,且 InnoDB 对大字段会外存溢出页,反而增加随机 IO。

推荐方案:content 用 MEDIUMTEXT(上限 16MB,够用),仅对高频查询字段建索引:

  • receiver_id + status(如未读/已读)必须联合索引,拉取未读消息时能跳过全表扫描
  • created_at 单独建索引,用于按时间倒序分页(但避免 ORDER BY created_at DESC LIMIT 100,20 这种深分页)
  • 删掉所有非必要索引,比如 sender_id 单独索引——除非你真有“查某人发的所有离线消息”这种需求

拉取未读消息必须用覆盖索引+LIMIT,不能依赖OFFSET

用户上线时批量拉取未读消息,常见写法是 SELECT * FROM offline_msg WHERE receiver_id = ? AND status = 'unread' ORDER BY created_at DESC LIMIT 50。问题在于:如果没走覆盖索引,InnoDB 要回表查 content,每行都触发一次磁盘 IO。

改成覆盖索引查询,让所有字段从索引中直接拿到:

ALTER TABLE offline_msg ADD INDEX idx_receiver_status_time (receiver_id, status, created_at DESC, id, content);

注意:content 放在索引末尾是允许的(MySQL 8.0+ 支持索引包含列),这样 SELECT id, content, created_at FROM ... 就完全不用回表。同时强制用 LIMIT 控制单次拉取量,别用 OFFSET ——离线消息表一旦积累百万级,LIMIT 10000, 50 会扫完前一万行才取后50条,CPU 和 IO 都扛不住。

删除已读消息别用DELETE,用分区表+DROP PARTITION

离线消息读完就该清理,但 DELETE FROM offline_msg WHERE receiver_id = ? AND status = 'read' 在大数据量下会锁表、打满 undo log,还产生碎片。更糟的是,如果没加 receiver_id 索引,就是全表扫描删。

正确做法是按天分区:

win可用的mysql指令skill
win可用的mysql指令skill

安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。

下载
ALTER TABLE offline_msg PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p20260920 VALUES LESS THAN (TO_DAYS('2026-09-21')),
  PARTITION p20260921 VALUES LESS THAN (TO_DAYS('2026-09-22')),
  PARTITION p20260922 VALUES LESS THAN (TO_DAYS('2026-09-23')),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

每天凌晨执行:ALTER TABLE offline_msg DROP PARTITION p20260920; ——秒级完成,不锁表、不写 binlog、不生成 undo。前提是业务能接受“只保留最近 N 天离线消息”,这在聊天场景里通常合理。

写入离线消息要用批量INSERT+禁用autocommit

IM 网关收到多条离线消息时,如果逐条 INSERT,每条都开事务、刷 redo log、写 binlog,吞吐直接腰斩。实测 100 条消息逐条写要 800ms,批量写只要 60ms。

应用层聚合后执行:

INSERT INTO offline_msg (sender_id, receiver_id, content, created_at, status) VALUES 
(1001, 2001, 'hi', '2026-09-28 11:00:00', 'unread'),
(1002, 2001, 'ok', '2026-09-28 11:00:01', 'unread'),
...;

同时连接需设置:SET autocommit = 0;,批量完成后 COMMIT;。别忘了检查 max_allowed_packet 是否足够(默认 4MB,100 条中等长度消息一般够用)。

真正容易被忽略的点是:分区表的 created_at 必须为 NOT NULL,否则 MySQL 会拒绝分区;另外,idx_receiver_status_time 索引里 created_at DESC 在 MySQL 8.0 才支持,低版本得去掉 DESC,靠应用层补排序。

相关专题

更多
数据分析工具有哪些
数据分析工具有哪些

数据分析工具有Excel、SQL、Python、R、Tableau、Power BI、SAS、SPSS和MATLAB等。详细介绍:1、Excel,具有强大的计算和数据处理功能;2、SQL,可以进行数据查询、过滤、排序、聚合等操作;3、Python,拥有丰富的数据分析库;4、R,拥有丰富的统计分析库和图形库;5、Tableau,提供了直观易用的用户界面等等。

2023.10.12

5043

8

SQL中distinct的用法
SQL中distinct的用法

SQL中distinct的语法是“SELECT DISTINCT column1, column2,...,FROM table_name;”。本专题为大家提供相关的文章、下载、课程内容,供大家免费下载体验。

2023.10.27

1031

4

SQL中months_between使用方法
SQL中months_between使用方法

在SQL中,MONTHS_BETWEEN 是一个常见的函数,用于计算两个日期之间的月份差。想了解更多SQL的相关内容,可以阅读本专题下面的文章。

2024.02.23

1329

5

SQL出现5120错误解决方法
SQL出现5120错误解决方法

SQL Server错误5120是由于没有足够的权限来访问或操作指定的数据库或文件引起的。想了解更多sql错误的相关内容,可以阅读本专题下面的文章。

2024.03.06

7121

10

sql procedure语法错误解决方法
sql procedure语法错误解决方法

sql procedure语法错误解决办法:1、仔细检查错误消息;2、检查语法规则;3、检查括号和引号;4、检查变量和参数;5、检查关键字和函数;6、逐步调试;7、参考文档和示例。想了解更多语法错误的相关内容,可以阅读本专题下面的文章。

2024.03.06

3763

4

oracle数据库运行sql方法
oracle数据库运行sql方法

运行sql步骤包括:打开sql plus工具并连接到数据库。在提示符下输入sql语句。按enter键运行该语句。查看结果,错误消息或退出sql plus。想了解更多oracle数据库的相关内容,可以阅读本专题下面的文章。

2024.04.07

7120

11

sql中where的含义
sql中where的含义

sql中where子句用于从表中过滤数据,它基于指定条件选择特定的行。想了解更多where的相关内容,可以阅读本专题下面的文章。

2024.04.29

9961

6

sql中删除表的语句是什么
sql中删除表的语句是什么

sql中用于删除表的语句是drop table。语法为drop table table_name;该语句将永久删除指定表的表和数据。想了解更多sql的相关内容,可以阅读本专题下面的文章。

2024.04.29

1350

5

sql中删除一列的命令是什么
sql中删除一列的命令是什么

在sql中,使用alter table语句可以删除一列,语法为:alter table table_name drop column column_name。想了解更多sql的相关内容,可以阅读本专题下面的文章。

2024.04.29

1172

5

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程