面向Facebook的消息系统的数据库规范化 [英] Database normalization for facebook-like messaging system

问题描述

有关于邮件系统的几个讨论，但主要涉及电子邮件结构。

There are several discussions about messaging systems but mostly related to email structure. How can be the most efficient way for members messaging in a normalized database?

我想创建一个包含五列的消息表：

I am thinking of creating a messages table with five columns:

ID (PRIMARY KEY)
First_Person (FK user_id)
Second_Person (FK user_id)
Message
date

我担心的是读这个大表。

My worry is about reading this large table.

找到一个人的所有消息（例如user_id 876）

finding all message for a person (e.g. user_id 876)

SELECT * FROM messages WHERE First_Person='876' OR Second_Person='876'

和两人之间的通信

SELECT * FROM messages WHERE (First_Person='876' OR Second_Person='876') 
AND (First_Person='1500' OR Second_Person='1500') ORDER DESC BY date

由于这种类型的消息传递就像聊天，成千上万的成员，这个表可以增长到数十亿的行（不是百万）。那么，在这么大的表中搜索消息是有效的？

Since this kind of messaging is like chat, for thousands of members, this table can grow to billions of row (not millions). Then, it is efficient to search for messages in such a large table?

推荐答案

可用。如果你需要一个真正的消息保持系统更好地看看NoSQL解决方案（如HBase，Cassandra，MongoDB等）只是你必须忘记你知道的关系数据库的任何东西。

You're right, such big table is not usable. If you need a real messages keeping system better look at NoSQL solutions (like HBase, Cassandra, MongoDB etc) just you'll have to forget anything you know about relational databases.

使用MySQL，你仍然可以做一些可扩展的，如果将表拆分成非常小的部分。使一个表保留最多1k个用户的消息（您需要写入所有消息两次，除非两个用户都来自同一个表）。此外，在一个数据库中保留不超过1k个表，在达到此限制时自动创建另一个数据表。拥有多个数据库（即使在一个物理服务器上）将使DBA在当前数据库变得过载时，轻松地将每个数据库传输到新的服务器。要获取特定用户的消息，您的代码必须从您将拥有的地图中获取所需的数据库/表。

With MySQL you still however may do something scalable if split the table into very small pieces. Make one table keep messages of max 1k users (you will need to write all messages twice unless both users are from same table). Plus keep no more than 1k tables in one DB, auto-create another one when this limit is reached. Having several databases (even on one physical server) will make it easy for DBA to transfer each to a new server when the current one becomes overloaded. To get messages of a certain user your code will have to obtain the required DB/table from a map you will have.

这篇关于面向Facebook的消息系统的数据库规范化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！