MySQL 消息已读表怎么设计：从写入去重到未读数清理

站内信、系统通知、审批消息都绕不开一个问题：用户点开消息后，要把它标记为已读；列表页又要快速显示未读数。刚开始很多项目会在消息表里加一个 is_read 字段，单用户场景还可以，多用户广播消息一上来就会变得难维护。

本文按数据生命周期来设计一张 MySQL 消息已读表：消息从哪里来，已读事件怎么校验，表结构怎么建，未读数怎么查，重复点击和并发写入怎么处理，最后历史数据如何清理。

数据来源：消息表和已读事件要分开

先看一个常见误区：把消息和已读状态放在同一行。

CREATE TABLE user_message (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  title VARCHAR(120) NOT NULL,
  content TEXT NOT NULL,
  is_read TINYINT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL
);

如果每条消息只发给一个用户，这样设计可以工作。但系统通知常常是一条消息发给一批用户，甚至发给全站用户。此时把 is_read 放在消息表里，就无法表示“同一条消息，A 已读，B 未读”。

更稳的拆法是：消息表保存消息本体，已读表保存用户和消息之间的已读关系。

CREATE TABLE message (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  scope VARCHAR(32) NOT NULL,
  title VARCHAR(120) NOT NULL,
  body TEXT NOT NULL,
  published_at DATETIME NOT NULL,
  expire_at DATETIME DEFAULT NULL,
  status TINYINT NOT NULL DEFAULT 1
);

消息本体只描述“这条消息是什么、什么时候发布、是否有效”。用户是否读过，是另一类数据，应该单独进入已读关系表。

写入校验：先确认用户能看到这条消息

用户点开消息详情时，前端通常会调用“标记已读”接口。后端不要直接写入已读表，要先校验两件事：消息是否存在，当前用户是否在消息可见范围内。

这条写入路径可以按下面的顺序处理：

1. 根据 message_id 查消息，排除已撤回或已过期消息。
2. 校验用户是否属于消息可见范围，例如部门、角色或用户列表。
3. 写入 message_read，依赖唯一索引处理重复点击。
4. 如果已经存在已读记录，返回成功，不把它当作业务错误。

这里的关键是“已读”是一个幂等动作。用户重复点开同一条消息，最终状态仍然是已读，接口不应该因为重复写入而报错。

存储模型：用唯一索引拦住重复已读

已读表可以这样建：

CREATE TABLE message_read (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  message_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  read_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY uk_msg_user (message_id, user_id),
  KEY idx_user_read_at (user_id, read_at),
  KEY idx_msg_read_at (message_id, read_at)
);

UNIQUE KEY uk_msg_user 是这张表的核心。它表达了一个业务事实：同一个用户对同一条消息只能有一条已读记录。应用层可以先查再写，但最终防线应该放在数据库唯一索引上。

写入时可以使用“插入忽略”或“冲突时更新”。如果只关心是否已读，使用 INSERT IGNORE 就够了：

INSERT IGNORE INTO message_read (
  message_id, user_id, read_at, created_at
) VALUES (
  ?, ?, NOW(), NOW()
);

如果希望重复点击时刷新最后阅读时间，可以使用更新写法：

INSERT INTO message_read (
  message_id, user_id, read_at, created_at
) VALUES (
  ?, ?, NOW(), NOW()
)
ON DUPLICATE KEY UPDATE read_at = VALUES(read_at);

两种写法的差异要提前定好。站内信一般只需要记录第一次已读时间，用 INSERT IGNORE 更简单；如果要分析最近一次打开时间，就保留更新逻辑。

查询路径：未读数不要每次全表反查

未读数的定义通常是：用户可见消息数减去用户已读消息数。最直接的查询是 LEFT JOIN 已读表后筛出未匹配记录：

SELECT COUNT(*)
FROM message m
LEFT JOIN message_read r
  ON r.message_id = m.id
 AND r.user_id = ?
WHERE m.status = 1
  AND m.published_at  NOW())
  AND r.id IS NULL;

这条 SQL 适合消息量不大、可见范围简单的系统。但当消息越来越多、每个用户都频繁打开首页时，未读数会成为高频查询。此时要考虑两个方向：

• 消息列表按时间分页，只查询当前页是否已读，不在每次请求里计算全量未读数。
• 把用户未读数做成汇总字段或缓存，在消息发布、已读写入、消息过期时增减。

如果采用汇总方案，不能只在读取时修正。消息发布、已读写入、撤回、过期清理都会影响未读数。也就是说，未读数是一份派生数据，必须有明确的更新入口和兜底校验任务。

异常处理：重复点击、并发写入和消息撤回

已读表常见异常主要有三类。

重复点击

用户连续点同一条消息，或者前端重试请求，都会造成重复写入。唯一索引能把问题拦住，接口应把“已存在”视为成功。

并发写入

同一个账号在两个设备上同时打开同一条消息，也可能并发写入。不要依赖应用层先查再插的判断，最终仍要交给 uk_msg_user。

消息撤回

消息撤回后，已读记录不一定要立即删除。更常见的做法是消息表更新 status，查询时排除无效消息；后续清理任务再按消息状态和过期时间批量处理。

清理策略：按消息过期时间清理已读记录

已读表是典型的关系事件表，会随着用户规模和消息量持续增长。如果消息只保留 90 天，已读记录也不应该永久堆积。

清理可以分两步：

DELETE r
FROM message_read r
JOIN message m ON m.id = r.message_id
WHERE m.expire_at IS NOT NULL
  AND m.expire_at 

这里故意保留 7 天缓冲，是为了避免消息刚过期就被清理，导致一些异步统计或审计任务读不到数据。大表清理时还要分批处理，避免一次删除太多行造成锁等待和主从延迟。

如果消息量很大，可以按月份或业务线拆分归档策略，但不要一开始就过度设计。先保证索引、批量删除和监控到位，再根据增长速度决定是否分区或归档。

总结

MySQL 消息已读表的核心是把消息本体和用户已读关系分开。写入时先校验消息可见范围，再用 (message_id, user_id) 唯一索引保证幂等；查询时按数据量选择直接反查、分页标记或未读数汇总；清理时跟随消息过期策略分批删除。这样设计后，已读状态就不再是一个散落在业务代码里的布尔值，而是一条可追踪、可查询、可清理的数据生命周期。