在消息管理类项目中，使用MySQL支撑实时消息推送并不罕见。很多团队受限于技术栈或运维成本，希望直接复用现有数据库完成消息的存储与分发。核心思路是把消息本身和用户的消费进度分开管理，通过增量查询替代全量轮询，从而在单机MySQL上承受可观的并发推送压力。
一、消息表的核心结构设计
实时消息推送系统最基础的表是消息主体表，通常命名为message或notification。它负责记录每一条需要下发的消息内容、类型、目标用户以及创建时间。自增主键在这里非常关键，因为它天然提供了消息的全局顺序，后续的增量拉取完全依赖这个ID。
除了自增ID，建议将receiver_id（接收者用户ID）、type（消息类型）、content（消息体，可用JSON或文本）、created_at作为基础字段。如果业务需要群发，可增加receiver_group字段或者使用独立的映射表。下面是一个简化的建表语句：
CREATE TABLE `notification` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `receiver_id` BIGINT UNSIGNED NOT NULL, `type` VARCHAR(32) NOT NULL DEFAULT 'system', `content` TEXT NOT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_receiver_created` (`receiver_id`, `id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
上面的索引idx_receiver_created把接收者ID和自增ID组合起来，使得按用户拉取“大于某ID”的消息时可以使用索引范围扫描，而不会触发表级遍历。这是MySQL做轻量推送的底层性能支点。
对于消息管理项目来说，内容字段如果变化较多，用JSON类型存储比频繁改表更灵活。但需注意MySQL 5.7之前对JSON支持较弱，应确认版本。另外，历史消息应定时归档到notification_archive表，避免单表无限膨胀影响推送查询效率。
二、用户消费进度与增量拉取
实时推送并不等于必须依赖长连接。在MySQL方案中，客户端通过轮询接口获取“新消息”，服务端只返回ID大于该用户上一次拉取位置的数据，这就是增量拉取。我们需要一张表记录每个用户的已读位点。
表user_message_offset可以只有两列：user_id和last_pulled_id。每次客户端拉取时，先查这张表得到位点，再查notification中id > last_pulled_id且receiver_id = 当前用户的记录，返回后更新位点。示例代码如下：
-- 获取用户位点 SELECT last_pulled_id FROM user_message_offset WHERE user_id = 1001; -- 拉取新消息 SELECT id, type, content, created_at FROM notification WHERE receiver_id = 1001 AND id > 500 ORDER BY id ASC LIMIT 50; -- 更新位点（在应用层事务中） UPDATE user_message_offset SET last_pulled_id = 550 WHERE user_id = 1001;
这种机制避免了“已读未读”状态写回原表造成的行锁竞争。位点表非常小，更新速度快。即使客户端离线很久，重新打开应用也能从断开的位置连续获取，不会漏消息。
在消息管理项目中，如果担心更新位点失败导致重复推送，可以把“返回消息”和“更新位点”放在同一个数据库事务里，或者使用幂等拉取：客户端自己去重，服务端不强制保证 Exactly-Once。对于大多数业务，At-Least-Once 加客户端去重已经足够。
三、与推送通道的衔接方式
纯MySQL轮询一般用于低频消息，例如系统通知、站内信。若要做更接近实时的聊天，可在MySQL之上加一层WebSocket。具体做法是：WebSocket服务在收到消息写入请求时，先落库notification，再通过内存队列广播给在线节点，离线用户仍靠轮询增量拉取补全。
下面用一段伪代码展示写入与推送的协作：
function sendMessage($receiverId, $content) {
// 1. 事务内写入MySQL
$db->beginTransaction();
$id = $db->insert('notification', [
'receiver_id' => $receiverId,
'content' => $content,
'type' => 'chat'
]);
$db->commit();
// 2. 尝试走WebSocket实时通道
if ($wsServer->isOnline($receiverId)) {
$wsServer->push($receiverId, json_encode([
'id' => $id,
'content' => $content
]));
}
// 离线用户依靠下次轮询增量拉取
}
这种混合架构让MySQL始终作为唯一可信源，WebSocket只是加速层。即便推送服务重启或丢消息，用户下次打开App也能从数据库补齐，非常适合资源有限的项目。
长轮询（客户端请求挂起直到有新消息或超时）也可以基于MySQL实现：服务端在查不到新消息时sleep短时间在循环查询，或用SELECT ... FOR UPDATE配合信号量。但高并发下易造成连接堆积，建议仅在在线用户少时采用。
四、消息可靠性与避坑要点
在消息管理项目里，常见的失误是把“是否已读”布尔字段放在notification表上，每次读取就UPDATE原表行。这会让热用户消息行成为热点，引发行锁等待。正确方式就是前文提到的外部位点表，彻底解耦。
另一个坑是滥用DELETE清理消息。推送系统应保留消息可追溯，用expire_at字段标记过期，由定时任务低频归档。如下面所示：
-- 标记三十天前消息过期 UPDATE notification SET expired = 1 WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY) LIMIT 1000; -- 低频归档并删除 INSERT INTO notification_archive SELECT * FROM notification WHERE expired = 1 LIMIT 1000; DELETE FROM notification WHERE expired = 1 LIMIT 1000;
分批次操作能避免大事务拖垮主库。同时，建议对notification表开启读写分离，推送拉取走从库，写入走主库，进一步缓解压力。
整体来看，用MySQL设计实时消息推送系统重点不在“实时”本身，而在“不丢、不重、可扩展”。只要把握增量ID、位点解耦、归档分离三个原则，就能在普通云数据库上支撑每日数百万级消息管理的业务场景。