PHP站内信表怎么设计

本文目录导读：

1. 方案一：极简版（小项目/单机应用）
2. 方案二：标准版（推荐，适合大多数业务）
3. 方案三：进阶版（高并发 / 多人群聊 / 企业微信风格）
4. 关键索引设计（防死库）
5. 软删除（回收站）逻辑
6. 未读消息计数（性能优化）
7. 给你的PHP代码建议（PDO示例）
8. 总结建议

PHP站内信（私信/消息系统）的表设计通常需要考虑性能、扩展性和业务场景。

最核心的诀窍是：尽量避免“消息表”和“用户表”直接多对多关联的复杂查询，用“线程/会话”和“消息”分离的设计。

以下是三种从简单到复杂的表设计方案,你可以根据项目规模选择：

极简版（小项目/单机应用）

适合：用户量少，或仅需要类似“通知公告”或“系统通知”的场景,无需会话管理。

表结构：messages（消息表）

缺点：如果A发消息给B，B回复，会新建两条记录，查询“我和某人的聊天记录”需要查两次（from=A,to=B 或 from=B,to=A），索引效率低，且无法区分“会话”。

标准版（推荐，适合大多数业务）

这是最常用且扩展性良好的设计，将“会话”和“消息”分开。

会话表 conversations（线程）

存储两个用户之间的对话关系。

消息表 messages

字段名	类型	说明
id	BIGINT (PK)	主键
conversation_id	INT	外键索引（指向会话表）
sender_id	INT	发送者ID
receiver_id	INT	接收者ID（冗余存储，便于查询未读）
message_type	TINYINT	类型：1文字，2图片，3文件，4系统
content	TEXT	（或JSON存储附件URL）
is_read	TINYINT(1)	是否已读（0/1）
read_at	DATETIME	已读时间
created_at	DATETIME	发送时间

核心SQL设计要点：

1. 在 conversations 表建立联合唯一索引 (user_one_id, user_two_id) 防止重复会话。
2. 查询会话列表时：SELECT * FROM conversations WHERE user_one_id = 我 OR user_two_id = 我 ORDER BY updated_at DESC。
3. 查询聊天记录：SELECT * FROM messages WHERE conversation_id = ? ORDER BY id ASC。

---

进阶版（高并发 / 多人群聊 / 企业微信风格）

如果站内信包含群聊、会话成员管理或云端同步，需要拆分成“会话成员表”。

会话表 conversations

字段名	类型	说明
id	BIGINT (PK)	会话ID
type	TINYINT	1=单聊，2=群聊
name	VARCHAR	群名称（单聊为空）
owner_id	INT	创建者ID
created_at	DATETIME	创建时间

会话成员表 conversation_members

这是关键，用于解决“我删掉了会话，但对方没删”的问题。

字段名	类型	说明
id	BIGINT (PK)	主键
conversation_id	INT	会话ID
user_id	INT	用户ID
last_read_id	INT	用户在此会话中读到的最后一条消息ID（实现“未读消息数”）
is_muted	TINYINT	是否免打扰
is_deleted	TINYINT	该用户是否删除了此会话

消息表 messages

与方案二相同，但没有 receiver_id（因为可能是群发），只保留 sender_id。

---

关键索引设计（防死库）

无论选择哪种方案，以下索引必须建立,否则数据量大时必卡：

1. 方案二：INDEX (conversation_id, created_at)。
2. 方案二：INDEX (to_user_id, is_read) —— 这是查询未读消息数的关键。
3. 方案三：INDEX (user_id, conversation_id) 在成员表中。

---

软删除（回收站）逻辑

绝对不建议直接物理删除 messages 行，因为这会破坏会话的历史连续性。 推荐方案：在“会话表”或“成员表”中增加 is_deleted 字段。

• 当A删除与B的会话时，只更新 conversation_members 表中 A记录 的 is_deleted = 1。
• 当A再次发消息给B时，只需将A的 is_deleted 改回 0,旧消息仍然存在。

---

未读消息计数（性能优化）

避免每次都 COUNT(*) 整个消息表。 常用优化方案：

1. 在 conversation_members 表存 last_read_id（最后读取的消息ID）。
2. 计算未读数 = SELECT COUNT(*) FROM messages WHERE conversation_id = ? AND id > last_read_id AND sender_id != 当前用户。
3. 如果计数很大，可以定时任务（Cron）异步聚合，或将计数缓存到 Redis。

---

给你的PHP代码建议（PDO示例）

查询“与我相关的会话列表”时（方案二），SQL 可以这样写：

SELECT c.*, 
       -- 联查最后一条消息用于预览
       m.content AS last_message,
       m.sender_id AS last_sender,
       m.created_at AS last_time,
       -- 计算未读数（假设当前用户ID是：:user_id）
       (SELECT COUNT(*) FROM messages mc 
        WHERE mc.conversation_id = c.id 
        AND mc.receiver_id = :user_id 
        AND mc.is_read = 0) AS unread_count
FROM conversations c
LEFT JOIN messages m ON c.last_message_id = m.id
WHERE c.user_one_id = :user_id OR c.user_two_id = :user_id
ORDER BY c.updated_at DESC
LIMIT 20;

总结建议

• 如果只是系统发通知（单向）：用方案一（一张表）就行。
• 如果是用户互聊：直接用方案二,最简单实用。
• 如果有群聊功能：用方案三。