带有聊天室的实时聊天项目的数据库架构

Question

对于我的大学项目，我正在开发一个动态的实时聊天网站，房间，用户注册等。我已经规划了整个系统的一个方面。房间。我对如何为房间设计数据库感到困惑。

为了正确地理解它，一个房间是由一个用户创建的，该用户随后是该房间的操作员。用户可以加入房间并在房间内交谈。该系统必须是可伸缩的，每天发送的消息即使不是数百万，也是数十万。

最初，我打算在名为messages的数据库中创建表，并具有如下字段：

| r_id | u_id | message | timestamp |

r_id和u_id分别是房间ID和用户ID的外键。这样做意味着每当用户发送消息时，我都需要插入一条新记录，并定期为每个客户端运行一条SELECT语句(比如每隔3秒左右)来获取最近的消息。我对此的担忧是因为表将是巨大的，运行这些语句可能会产生大量开销并花费很长时间。

我想到的另一种实现方式是为每个房间创建一个新的数据库表。假设用户创建了3个房间，分别名为General、Programming和Gaming，数据库表将如下所示：room_general、room_programming、room_gaming，每个房间的字段如下：

| u_id | message | timestamp |

这将极大地减少每个表的查询量，但在对其进行编程时可能会带来问题。

因此，我被困在做这件事的最好方法上。如果有什么不同的话，我使用的技术将是MySQL和大量的AJAX。

谢谢你的帮助！

Answer 1

为每个房间创建一张桌子是不好的。很难实现，很难支持。

不用担心selects的性能，因为它们非常简单：

SELECT * FROM messages WHERE r_id=X ORDER BY timestamp DESC LIMIT X,Y

只需确保您的(r_id，timestamp)按此顺序索引在一起，以便使用索引进行选择：

ALTER TABLE `messages` ADD KEY `IN_messages_room_time` (`r_id`, `timestamp`);

如果您仍然有性能问题(可能不会)，只需在内存缓存中添加1-3秒(使用memcache)，并每1-3秒从数据库获取一次消息。

另外，看看阿波罗·克拉克的回答:为了防止存储大量不必要的旧消息，您可以将其放入MYISAM表archive中，或者直接删除。

Answer 2

考虑创建一个用于存储消息的“事务表”。基本上，你需要决定，我真的想记录所有发布到聊天室的消息，还是只记录过去一个月/一周/一天/小时发布的消息。如果您真的想要每条消息的历史记录，那么您需要创建两个数据库。如果您不想保留每条消息的历史记录，那么您只需要一个表。

使用事务表，它是如何流动的：

1. 用户进入聊天室
2. 用户键入消息，该消息将保存到交易表中。
3. 每隔500毫秒或3秒，聊天室中的每个用户都会查询交易表，以获取过去500毫秒或3秒内的最新更新

SELECT * FROM message_transactions WHERE timestamp >123456789

• CRON作业每5分钟或1小时运行一次，并删除所有超过5分钟或您希望的历史记录时间长度的条目。

确保同步和舍入每个用户查询transaction表的时间，以便MySQL查询结果缓存生效。例如，将时间戳舍入为每1秒或每500毫秒一次。

现在的情况是，用户只会收到最新的消息，而您的数据库不会随着时间的推移而爆炸式增长，也不会变慢。为此，您将需要在JS中缓存客户端上的消息历史记录。

另一方面，您可以将PHP转换为IRC库，然后将其调用一天。此外，如果您对此感兴趣，请查看Facebook如何实现其基于AJAX的聊天系统。

Answer 3

要提高数据库的速度，请看一看索引表：http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

在您的例子中，我假设您在通过r_id在user表上进行连接时，通过u_id获得了SELECTing消息。我将为r_id和u_id列建立索引。我绝对不是这方面的专家，因为我只为自己的项目做过“有效的”工作。我不理解索引的每一个优点和缺点，只是对那些通常用作索引的列进行索引，这样可以加快速度。搜索"mysql索引教程“，你会发现更多的信息。

不要疯狂地为每一列建立索引，这样会减慢插入和更新的速度。

我还建议您每隔几天/每周清除一次聊天日志，或者将它们移动到另一台服务器上存档，如果这是您想要/需要做的话。