mysql在社交平台中的私信系统数据库设计

私信表需建两个复合索引：idx_to_created(to_user_id, created_at)和idx_from_created(from_user_id, created_at)，未读数须冗余存储，status用TINYINT存状态码，分表按created_at按季度进行。

私信表必须包含双向索引才能支撑实时查询

用户查看自己收到的私信、发送的私信、以及与某人的最新对话，这三个场景都依赖高效索引。只建 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 个月内，分时间表正好命中热数据。