session 消息系统的SQL表设计

Question

在我的由 MySQL 数据库支持的 Web 应用程序中，我想提供一个消息传递系统，其中消息被分组为多个用户之间的对话，但我坚持设计一个满足我的一些需求的表结构:

• 多个用户可以参与一个对话
• 用户可以加入、阅读、离开和删除对话
• 收件箱 View 应尽可能少地生成查询

现在，可以使用联结表来解决多对多关系的第一个要求。但事实证明，在为收件箱 View 编写选择查询时会产生很多问题。

第二个要求也被证明是一个挑战。如果用户离开对话，他应该仍然可以阅读旧消息。对话中其余用户之间的新消息不应与离开的用户共享。我首先想到的是使用树状结构进行对话。每次用户加入或离开对话时，都会创建一个引用父对话的新对话，并创建与联结表中其余参与者的新关系。

第三个要求似乎也不是微不足道的。收件箱 View 应显示与特定用户作为参与者的对话列表。此外，还应为每个对话显示附加信息:所有当前参与者的姓名、对话的最后回复以及该回复的作者。将收件箱 View 视为消息列表，其中包含有关它们所属对话的附加信息。

我目前的做法是这样的:

CREATE TABLE `conversation` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `parentId` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parentId` (`parentId`),
  CONSTRAINT `conversation_ibfk_1` FOREIGN KEY (`parentId`) REFERENCES `conversation` (`id`)
);
CREATE TABLE `participant` (
  `userId` int(11) unsigned NOT NULL,
  `conversationId` int(11) unsigned NOT NULL,
  `readAt` datetime DEFAULT NULL,
  PRIMARY KEY (`userId`,`conversationId`),
  KEY `conversationId` (`conversationId`),
  CONSTRAINT `participant_ibfk_2` FOREIGN KEY (`conversationId`) REFERENCES `conversation` (`id`),
  CONSTRAINT `participant_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
);
CREATE TABLE `reply` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `conversationId` int(11) unsigned NOT NULL,
  `userId` int(11) unsigned NOT NULL,
  `text` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `conversationId` (`conversationId`),
  KEY `userId` (`userId`),
  CONSTRAINT `reply_ibfk_2` FOREIGN KEY (`userId`) REFERENCES `user` (`id`),
  CONSTRAINT `reply_ibfk_1` FOREIGN KEY (`conversationId`) REFERENCES `conversation` (`id`)
);
CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);

我在这里碰壁了，找不到满足我所有需求的解决方案。也许这里有人可以就如何处理此数据库设计给我一些建议。

Answer 1

用户事件

我仍然会制作联结表，但在元组上使用索引。

USER_CONV   
---------------
id_user 
id_conversation
active_flag
begin_flag              - flag indicating if id_first_reply == id_first_visible_reply
id_first_reply          - first reply in the conversation
id_first_visible_reply  - first visible for a given user
id_last_reply           - last visible for a given user, user if active_flag = false, otherwise NULL

index (id_user, id_conversation, active_flag) 


USER_REPLY
---------------
id_user
id_conversaion
id_reply

index (id_user, id_conversation, id_reply) 

USER_CONV 应该可以轻松创建收件箱。您仍然需要使用 USER_REPLY 加入它，然后使用 REPLY。

收件箱

如果你想让它更快，那么创建一种 CACHED_INBOX 和一个包含 MOST_RECENT_USER_ACTIVITY 的表。 CACHED_INBOX 表将包含所有收件箱数据，您不需要执行任何连接来获取相关数据，但您只需对来自 MOST_RECENT_USER_ACTIVITY 的数据执行 UNION。 MOST_RECENT_USER_ACTIVITY 应该很小(工作速度很快)，并且 CACHED_INBOX 将是“静态的”。然后每天一次，或者每隔几天在服务器不太可能被使用的时候进行一次批量 CACHED_INBOX 更新。除非您的用户决定永远留在对话中，否则它会起作用。

优化

当然，您需要稍微解释一下，以了解查询优化器如何使用索引(如果它确实使用的话)。也许您需要一个像 ACTIVE_CONVERSATIONS 这样的表，它没有任何索引，这会稍微影响性能。但是随后您会对 USER_CONV 进行一些批量更新，这将具有更广泛的索引。您必须尝试一下，看看您对用户行为有何期望，这应该是架构驱动因素之一。

注意:关于索引，有一个专门讨论索引主题的好网站 use-the-index-luke.com