类似 facebook 的消息系统的数据库规范化
关于消息传递系统有一些讨论，但主要与电子邮件结构有关。在规范化数据库中，如何才能最有效地实现成员消息传递？
我正在考虑创建一个包含五列的消息表：
ID (PRIMARY KEY)
First_Person (FK user_id)
Second_Person (FK user_id)
Message
date
我担心的是如何读取这个大表。
查找一个人的所有消息（例如 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
由于这种消息传递就像聊天一样，对于数千名成员来说，该表可能会增长到数十亿行（而不是数百万行）。那么，在这么大的表中查找消息效率高吗？
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.
finding all message for a person (e.g. user_id 876)
SELECT * FROM messages WHERE First_Person='876' OR Second_Person='876'
and communications between two persons
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
你说得对，这么大的桌子根本用不上。如果您需要一个真正的消息保存系统，请更好地查看 NoSQL 解决方案（如 HBase、Cassandra、MongoDB 等），您将不得不忘记有关关系数据库的任何知识。
然而，使用 MySQL，如果将表分成非常小的部分，您仍然可以执行一些可扩展的操作。让一张表保存最多 1k 个用户的消息（除非两个用户都来自同一个表，否则您需要将所有消息写入两次）。另外，在一个数据库中保留不超过 1k 个表，达到此限制时自动创建另一个数据库。拥有多个数据库（甚至在一台物理服务器上）将使 DBA 在当前服务器过载时可以轻松地将每个数据库转移到新服务器。要获取特定用户的消息，您的代码必须从您拥有的地图中获取所需的数据库/表。
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.
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.