类似 Twitter 的帖子时间线的数据库结构和查询

Question

我将使用 Django 标签来回答你的问题，因为我在这个环境中工作，但实际上这是一个通用的问题：我想展示类似于 Twitter 上的时间轴，即按时间顺序排列我关注的所有人发布的列表。

我的典型数据库结构如下：

Table Name: Users
Columns:
    UserID PK
    EmailAddress
    Password

TableName: Friends
Columns:
    UserID PK FK
    FriendID PK FK

TableName: Posts
Columns:
    UserID PK FK
    Content

如果我想获取给定用户所有朋友的所有帖子，代码如下（这不是有效的SQL！）：

SELECT * FROM Posts WHERE UserID IN (LIST OF "Given user friends' IDs")

这个可以 - 没有问题！然而，这个不具有可扩展性！假设我们有一个繁忙的网站，我们给定的用户有2,000个朋友，并且数据库中有几千万条帖子。在这种场景下，数据库查询将非常低效和缓慢。

像PostgreSQL或MySQL这样的关系型数据库能解决这个问题吗？如果不能，例如Twitter是如何处理的？

- Simon Steinberger

Answer 1

简化版：

SELECT
    Posts.*
FROM
    Posts 
    JOIN Friends ON Friends.FriendID = Posts.UserID
WHERE
    Friends.UserID = 1 /* change as needed */ ;

您可以使用以下方式进行检查：

CREATE TABLE Users
(
    UserID integer PRIMARY KEY,
    EmailAddress text,
    Password text
) ;

CREATE TABLE Friends
(
    UserID integer NOT NULL REFERENCES Users(UserID),
    FriendID integer NOT NULL REFERENCES Users(UserID),
    PRIMARY KEY (UserID, FriendID)
) ;

CREATE TABLE Posts
(
    PostID integer PRIMARY KEY,
    UserID integer NOT NULL REFERENCES Users(UserID),
    Content text
) ;

INSERT INTO Users 
VALUES 
  (1, '[e-mail]', 'pass1'),
  (2, '[e-mail]', 'pass2'),
  (3, '[e-mail]', 'pass3'),
  (4, '[e-mail]', 'pass4') ;

INSERT INTO Friends
VALUES 
   (1, 2),
   (1, 4) ;

INSERT INTO Posts
VALUES
    (1, 2, 'A post from User 2'),
    (2, 2, 'Another post from User 2'),
    (3, 3, 'A post from User 3'),
    (4, 4, 'A post from User 4') ;

你应该得到：

+---+---+--------------------------+
| 1 | 2 | A post from User 2       |
| 2 | 2 | Another post from User 2 |
| 4 | 4 | A post from User 4       |
+---+---+--------------------------+

- joanolo

---

谢谢joanolo！我已将您的答案标记为正确，因为您提供了一个完整的示例。其他答案同样正确。然而，我希望有一种更复杂的解决方案，类似于NoSQL数据库，因为“JOIN”仍然不够高效。我是Pixabay.com的首席开发人员 - 我们的PostgreSQL数据库越来越成为可靠的数据存储，而所有快速读取的数据都由Elastisearch服务器提供。这很有效，但我想知道NoSQL数据库是否是另一种选择。Twitter或Facebook是如何做到的？ - Simon Steinberger

你需要从_两个_表中获取信息。你必须以某种方式将它们join在一起。让数据库决定最佳的方法。NoSQL数据库适合存储整个文档（例如：一篇文章和所有评论，它们总是一起显示）。通常不适合执行_join_（这是你想要的）。你可能只需要一个_更快_的数据库（更快的硬件、SSD磁盘、大量内存，使大部分数据都适合RAM中...）。并确保你拥有所有正确的索引。你能发一个执行计划吗？ - joanolo

关于Twitter和Facebook的做法：我真的不知道（我也不认为他们会公开这些信息）。但我认为他们只是使用了一些技术来给出一个_近似_解决方案。顺便说一下：一个有2000个朋友的用户是否现实？如果你的问题与一个_朋友网络_密切相关，你可能需要看一下*图形数据库*（如Neo4J）。 - joanolo

哦，图数据库，从未听说过。听起来像一个有趣的选项对于这个用例。谢谢 - 还有关于NoSQL的解释！ - Simon Steinberger

Answer 2

   SELECT p.*
     FROM Posts AS p
     JOIN (SELECT FriendID
             FROM Friends
            WHERE UserID = :given_user_id) AS f
       ON (p.UserID = f.FriendID)

扩展性更好。由于您已经标记了PK，因此已经有了所有必要的索引。

- Dario

Answer 3

您也可以跟随您自己的查询，它会起作用：

SELECT
    *
FROM
    Posts
WHERE
    UserID in (SELECT FriendID 
                 FROM Friends 
                WHERE UserID = 1 /* whatever */ ) ;

这将进行扩展（在最新版本的mySQL或PostgreSQL中，它将产生与JOIN相同的执行计划）。

- joanolo