什么是在数据库中存储状态更新的更好方法？

I'm developing a web application that will enable users to post short status updates similar to Twitter. The only way I can think of storing these posts is to have a large "status_updates" table that stores EVERY user's status updates:

我正在开发一个Web应用程序,使用户能够发布类似于Twitter的简短状态更新。我能想到存储这些帖子的唯一方法是有一个大的“status_updates”表来存储每个用户的状态更新:

--------------------------------------
| table: status_updates              |
-------------------------------------|
| id | user_who_posted | update_text |
--------------------------------------

This method requires something like this SQL query to get each user's updates:

此方法需要类似此SQL查询的内容来获取每个用户的更新:

SELECT * FROM status_updates where user_who_posted="username"

and I think that wouldn't be very inefficient. Is there a better way of doing this?

而且我觉得效率不高。有更好的方法吗?

3 个解决方案

#1

Build a user table, and have the user_id be an integer foreign key to that user table. Then, build an index on the user_id field to allow for rapid retrieval.

构建用户表,并将user_id设置为该用户表的整数外键。然后,在user_id字段上构建索引以允许快速检索。

In short:

status_updates:
--------------------------------------
|  status_id  |  user_id  |  status  |
--------------------------------------
|          1  |        1  |  Woot!   |
--------------------------------------
|          2  |        1  |  Yeah!   |
--------------------------------------
|          3  |        2  |  Hello!  |
--------------------------------------

users:
--------------------------
|  user_id  |  username  |
--------------------------
|        1  |  'Joe'     |
--------------------------
|        2  |  'John'    |
--------------------------

Then, to retrieve, you would do this:

然后,要检索,你会这样做:

select
    u.username,
    s.status
from
    status_updates s
    inner join users u on
        s.user_id = u.user_id
where
    u.username = 'John'

This will retrieve:

这将检索:

-------------------------
|  username  |  status  |
-------------------------
|  John      |  Hello!  |
-------------------------

Do with that what you will. That will be very performant on millions of rows, so long as you build your indexes right. What RDBMS are you using, so I can point you to the right spot for that?

做你想做的事。只要你正确地构建索引,这将在数百万行上非常高效。你使用什么RDBMS,所以我可以指出你的正确位置?

#2

This actually can be very efficient as long as you properly set up an index for the status_updates table on user.

只要您在用户上正确设置status_updates表的索引,这实际上非常有效。

If you are truly worried about the table becoming very, very large you may want to look into horizontal partitioning of your database(s).

如果您真的担心表变得非常非常大,您可能需要查看数据库的水平分区。

#3

It would be quicker to not have a string as part of your search criteria, and instead have your user replaced with a surrogate key:

没有字符串作为搜索条件的一部分会更快,而是用代理键替换您的用户:

SELECT update_text
FROM status_updates
INNER JOIN users
    ON status_updates.user_id = users.user_id
WHERE users.username = 'username'

Obviously, indexing and potentially partitioning your table could be useful for scalability.

显然,索引和可能对表进行分区对于可伸缩性可能很有用。

#1