私信表必须包含双向索引才能支撑实时查询
用户查看自己收到的私信、发送的私信、以及与某人的最新对话，这三个场景都依赖高效索引。只建
user_id单字段索引是不够的——它无法加速
WHERE to_user_id = ? ORDER BY created_at DESC这类常见查询。
实际要建两个复合索引：
INDEX idx_to_created (to_user_id, created_at)：支撑「收件箱按时间倒序」
INDEX idx_from_created (from_user_id, created_at)：支撑「发件箱按时间倒序」
别漏掉
created_at在索引中的位置——必须放在第二位，否则范围查询会失效。如果用
(to_user_id, status, created_at)这种带状态的组合，记得
status字段要高基数（比如只有
'sent'/
'read'两种值），否则 MySQL 可能拒绝使用该索引。
未读数不能靠 COUNT(*)
实时计算
每次打开聊天窗口就跑
SELECT COUNT(*) FROM messages WHERE to_user_id = ? AND status = 'unread'，QPS 上千时数据库立刻抖动。真实系统里，未读数必须冗余到用户关系表或缓存中。
推荐做法是维护一张轻量级的
user_conversation_unread表：
CREATE TABLE user_conversation_unread ( user_id BIGINT NOT NULL, other_user_id BIGINT NOT NULL, unread_count INT UNSIGNED NOT NULL DEFAULT 0, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (user_id, other_user_id), INDEX idx_user_updated (user_id, updated_at) );
每条新消息插入后，用
INSERT ... ON DUPLICATE KEY UPDATE unread_count = unread_count + 1原子更新；用户点开对话时，再用
UPDATE清零对应记录。这样查未读数就是主键等值查询，毫秒级响应。
status
字段别用字符串枚举存 'delivered'
/'read'
字符串字段在
WHERE和
ORDER BY中比整型慢，且占更多空间。更麻烦的是，一旦业务加个
'recalled'状态，所有 SQL 里的字符串字面量都要搜一遍改，极易漏。
直接用
TINYINT UNSIGNED存状态码：
0→
'pending'（仅内部用，不暴露给前端）
1→
'sent'
2→
'read'
3→
'deleted_by_sender'
4→
'deleted_by_receiver'
注意：删除不是物理删，而是设状态。双方都删了才可归档。另外，
deleted_by_sender和
deleted_by_receiver必须分开，否则无法实现「撤回只对对方不可见，自己仍可见」这种体验。
分表策略比分库更实用，从 messages_2024_q3
开始
单表超 5000 万行后，即使有索引，
ALTER TABLE加字段也会锁表十几分钟。社交平台私信增长快，但不像订单那样强事务，适合按时间分表。
不要一上来就搞
sharding-jdbc或
vitess分库——运维成本高，且大多数中小团队根本没到那个量级。先用应用层路由： 写入时根据
created_at落到
messages_2024_q3表（如
2024-07-15） 查询时按时间范围确定查哪几张表，
UNION ALL合并结果 每季度自动建下一张表，用
EVENT或定时脚本触发
关键点：分表键必须是
created_at，不能是
user_id。因为用户维度查询（如「查张三所有历史消息」）属于低频管理操作，可以走异步导出；而「最近 20 条」这种高频请求，99% 都集中在最近 3 个月内，分时间表正好命中热数据。
