这篇文章需要花费大量时间来打字，因为我试图尽可能清楚，所以如果仍然不清楚，请多多包涵。

基本上，我有一个数据库中的帖子表，用户可以向其中添加隐私设置。

ID | owner_id | post | other_info | privacy_level (int value)

从那里，用户可以添加他们的隐私详细信息，允许所有 [privacy_level = 0]、 friend (privacy_level = 1)、任何人(privacy_level = 3)或特定的人或过滤器(privacy_level = 4)查看).对于指定特定人员的隐私级别 (4)，查询将在子查询中引用表“post_privacy_includes_for”，以查看该用户(或用户所属的过滤器)是否存在于该表的一行中。

ID | post_id | user_id | list_id

此外，用户还可以通过将某些人排除在外来防止某些人在更大的群组中查看他们的帖子(例如，将其设置为每个人都可以查看但对跟踪者用户隐藏)。为此，添加了另一个引用表“post_privacy_exclude_from”——它看起来与“post_privacy_includes_for”的设置相同。

我的问题是这无法扩展。完全没有。目前，大约有 1-2 百万个帖子，其中大部分设置为所有人都可以查看。对于页面上的每个帖子，它必须检查是否有一行将帖子排除在外，不向用户显示 - 这在可以填充 100-200 个帖子的页面上移动非常慢。这最多可能需要 2-4 秒，尤其是在向查询添加额外约束时。

这还会创建极其庞大和复杂的查询，这些查询非常……笨拙。

SELECT t.*
FROM posts t
WHERE ( (t.privacy_level = 3
         AND t.owner_id = ?)
       OR (t.privacy_level = 4
           AND EXISTS
             ( SELECT i.id
              FROM PostPrivacyIncludeFor i
              WHERE i.user_id = ?
                AND i.thought_id = t.id)
           OR t.privacy_level = 4
           AND t.owner_id = ?)
       OR (t.privacy_level = 4
           AND EXISTS
             (SELECT i2.id
              FROM PostPrivacyIncludeFor i2
              WHERE i2.thought_id = t.id
                AND EXISTS
                  (SELECT r.id
                   FROM FriendFilterIds r
                   WHERE r.list_id = i2.list_id
                     AND r.friend_id = ?))
           OR t.privacy_level = 4
           AND t.owner_id = ?)
       OR (t.privacy_level = 1
           AND EXISTS
             (SELECT G.id
              FROM Following G
              WHERE follower_id = t.owner_id
                AND following_id = ?
                AND friend = 1)
           OR t.privacy_level = 1
           AND t.owner_id = ?)
       OR (NOT EXISTS
             (SELECT e.id
              FROM PostPrivacyExcludeFrom e
              WHERE e.thought_id = t.id
                AND e.user_id = ?
                AND NOT EXISTS
                  (SELECT e2.id
                   FROM PostPrivacyExcludeFrom e2
                   WHERE e2.thought_id = t.id
                     AND EXISTS
                       (SELECT l.id
                        FROM FriendFilterIds l
                        WHERE l.list_id = e2.list_id
                          AND l.friend_id = ?)))
           AND t.privacy_level IN (0, 1, 4))
  AND t.owner_id = ?
ORDER BY t.created_at LIMIT 100

(模拟查询，类似于我现在在 Doctrine ORM 中使用的查询。它是一团糟，但你明白我在说什么。)

我想我的问题是，您将如何处理这种情况以对其进行优化？有没有更好的方法来设置我的数据库？我愿意完全放弃我目前建立的方法，但我不知道该转向什么。

谢谢你们。

更新:修复查询以反射(reflect)我为上述隐私级别定义的值(我忘记更新它，因为我简化了值)

最佳答案

您的查询太长，无法给出明确的解决方案，但我将遵循的方法是通过将子查询转换为连接来简单地进行数据查找，然后将逻辑构建到 where 子句和列列表中选择语句:

select t.*, i.*, r.*, G.*, e.* from posts t
left join PostPrivacyIncludeFor i on i.user_id = ? and i.thought_id = t.id
left join FriendFilterIds r on r.list_id = i.list_id and r.friend_id = ?
left join Following G on follower_id = t.owner_id and G.following_id = ? and G.friend=1
left join PostPrivacyExcludeFrom e on e.thought_id = t.id and e.user_id = ? 

(这可能需要扩展:我无法遵循最后一个子句的逻辑。)

如果您能让简单的选择快速运行并包括所有需要的信息，那么您需要做的就是在选择列表和 where 子句中构建逻辑。

关于php - MySQL 包含/排除帖子，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/3903749/