在数据库中存储聊天消息的最佳方式？

我正在建一个聊天应用程序，我想要一个完整的历史关闭所有的消息曾经发送在聊天对话。目前，我将每条消息作为一行存储在一个称为“消息”的表中。我知道这个表可能会变得很大，因为即使是像“ Hi”这样的小消息也会有自己的数据库记录。

有人能推荐一个更可伸缩的 mysql 解决方案吗？我不要求个人信息是可搜索的、可编辑的或可删除的。整个对话可以存储在一个巨大的领域吗？

我很想听听你的想法！

155376 次浏览
小开

If we assume that you do not read the data too.

This sounds to me like an audit\logging requirement, if it is, you do not need a database to store the chat messages.

Just append the conversation to a text file (1 file per day?). The file could look like this:

chat-1-bob 201101011029, hi
chat-1-jen 201101011030, how are you?
chat-1-bob 201101011030, fine thanks.
chat-1-jen 201101011035, have you spoken to bill recently?
chat-2-bob 201101021200, hi
chat-2-bill 201101021201, Hey Bob,
chat-2-bill 201101021203, what time do you call this?
chat-2-bob 201101021222, about 12:22

I think you will find it hard to get a more simple scaleable audit solution.

If your requirements change and you need to search\edit\delete then a database would be more appropriate.

小开

You could create a database for x conversations which contains all messages of these conversations. This would allow you to add a new Database (or server) each time x exceeds. X is the number conversations your infrastructure supports (depending on your hardware,...).

The problem is still, that there may be big conversations (with a lot of messages) on the same database. e.g. you have database A and database B an each stores e.g. 1000 conversations. It my be possible that there are far more "big" conversations on server A than on server B (since this is user created content). You could add a "master" database that contains a lookup, on which database/server the single conversations can be found (or you have a schema to assign a database from hash/modulo or something).

Maybe you can find real world architectures that deal with the same problems (you may not be the first one), and that have already been solved.

小开
最佳答案

There's nothing wrong with saving the whole history in the database, they are prepared for that kind of tasks.

Actually you can find here in Stack Overflow a link to an example schema for a chat: example

If you are still worried for the size, you could apply some optimizations to group messages, like adding a buffer to your application that you only push after some time (like 1 minute or so); that way you would avoid having only 1 line messages


提交答案