php包含mysql,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中使用的查询.这是一团糟,但你得到了我所说的.)

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

多谢你们.

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

解决方法:

您的查询太长,无法提供明确的解决方案,但我要遵循的方法是通过将子查询转换为连接来简单地进行数据查找,然后将逻辑构建到select语句的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 = ?

(这可能需要扩展：我无法遵循最终条款的逻辑.)

如果您可以快速完成简单选择并包含所需的所有信息,那么您需要做的就是在select列表和where子句中构建逻辑.

标签：php,optimization,mysql,doctrine

来源： https://codeday.me/bug/20190710/1421511.html