COUNT(*)需遍历索引B+树所有叶子节点做聚合,而维护unread_count字段只需主键更新,毫秒级完成,避免全索引扫描与并发锁阻塞。

未读消息表不能只存 user_id 和 is_read,否则轮询时会扫全表或频繁 JOIN,压力直接打到主库。
为什么用单独的 unread_count 字段比每次 COUNT(*) 更快
每次查“你有几条未读”都执行 SELECT COUNT(*) FROM messages WHERE user_id = ? AND status = 'pending',在百万级消息表上是灾难——即使有索引,InnoDB 仍要遍历索引 B+ 树所有叶子节点做聚合。
更稳的做法是:维护一个轻量级计数表,只存 user_id 和 unread_count,每次发新消息或用户标为已读时,用原子更新:UPDATE user_unread SET unread_count = unread_count + 1 WHERE user_id = ?UPDATE user_unread SET unread_count = GREATEST(0, unread_count - 1) WHERE user_id = ?
- 这个表行数 = 用户数,不是消息数,通常十万级以内,
UPDATE是主键更新,毫秒级完成 - 避免
SELECT ... FOR UPDATE锁整行或间隙锁带来的并发阻塞 - 如果业务允许短时不准(比如±1条),甚至可用 Redis 的
INCR/DECR先扛住峰值,再异步落库对账
联合索引必须覆盖 user_id + status + created_at
当用户点开通知列表,需要拉取“最近 20 条未读”,典型 SQL 是:SELECT * FROM messages WHERE user_id = ? AND status IN ('pending', 'sent') ORDER BY created_at DESC LIMIT 20
安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。
如果只建了 INDEX(user_id),MySQL 仍需回表过滤 status,再排序 created_at,I/O 成倍上升。
- 正确索引是:
INDEX(user_id, status, created_at)—— 满足等值查询 + 范围排序,且能覆盖status过滤 - 不要把
created_at放最前:它区分度太高,user_id等值才是驱动查询的起点 - 如果还要支持按
trigger_type筛选(如只看评论类通知),可扩展为INDEX(user_id, trigger_type, status, created_at),但注意索引宽度别超 2000 字节
轮询接口必须带 last_check_time,而不是只依赖 status 字段
单纯靠 status = 'pending' 判断新通知,会导致两个问题:
• 用户长时间不在线,积压几百条,一上线就触发大批量推送,压垮下游 FCM/APNs 网关
• 多设备登录时,A 设备标为已读,B 设备还在轮询旧数据,出现状态不一致
- 轮询请求应携带客户端上次检查时间戳:
last_check_time = 2026-11-04 12:00:00 - 服务端查:
SELECT id, content, trigger_type FROM messages WHERE user_id = ? AND created_at > ? AND status IN ('pending', 'sent') - 配合
INDEX(user_id, created_at),这个查询能走索引范围扫描,不回表,延迟稳定在 5ms 内 - 客户端收到后,用本地时间更新
last_check_time,不依赖服务端响应时间校准
真正难的不是建表,而是让每条写入都精准命中索引、每次读取都不触发文件排序或临时表。很多团队卡在“功能跑通就行”,结果上线两周,SHOW PROCESSLIST 里全是 Copying to tmp table。










