php – MySQL包含/排除帖子

基本上,我所拥有的是数据库中的帖子表,用户可以添加隐私设置.

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

从那里,用户可以添加他们的隐私详细信息,允许所有[privacy_level = 0),朋友(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中使用的查询.这是一团糟,但你得到了我所说的.)

我想我的问题是,你将如何处理这种情况来优化它？有没有更好的方法来设置我的数据库？我愿意完全废弃我目前建立的方法,但我不知道该怎么做.

多谢你们.

更新：修复查询以反映我为上面的隐私级别定义的值(我忘了更新它,因为我简化了值)