mysql - 如何让这个SQL查询更加高效？

Question

我不知道如何让下面的 SQL 查询更高效。目前，在相当快的服务器上查询需要 8 - 12 秒，但是当用户尝试加载包含此代码的页面时，对于网站来说这还不够快。它正在查看包含许多行的表，例如“Post”表有 717,873 行。基本上，查询列出了与用户关注的内容相关的所有帖子(从最新到最旧)。

有没有办法通过仅根据 PostTimeOrder 获取最后 20 个结果总数来加快速度？

如果您能提供任何帮助或了解可以改善这种情况的任何方法，我们将不胜感激。谢谢。

这是完整的 SQL 查询(大量嵌套):

SELECT DISTINCT p.Id, UNIX_TIMESTAMP(p.PostCreationTime) AS PostCreationTime, p.Content AS Content, p.Bu AS Bu, p.Se AS Se, UNIX_TIMESTAMP(p.PostCreationTime) AS PostTimeOrder
                FROM Post p 
                WHERE (p.Id IN (SELECT pc.PostId
                                FROM PostCreator pc
                                WHERE (pc.UserId IN (SELECT uf.FollowedId
                                                    FROM UserFollowing uf
                                                    WHERE uf.FollowingId = '100')
                                                    OR pc.UserId = '100')
                                ))
                OR (p.Id IN (SELECT pum.PostId
                            FROM PostUserMentions pum
                            WHERE (pum.UserId IN (SELECT uf.FollowedId
                                                    FROM UserFollowing uf
                                                    WHERE uf.FollowingId = '100')
                                                    OR pum.UserId = '100')
                            ))  
                OR (p.Id IN (SELECT ssp.PostId
                                FROM SStreamPost ssp
                                WHERE (ssp.SStreamId IN (SELECT ssf.SStreamId
                                                    FROM SStreamFollowing ssf
                                                    WHERE ssf.UserId = '100'))
                                ))
                OR (p.Id IN (SELECT psm.PostId
                                FROM PostSMentions psm
                                WHERE (psm.StockId IN (SELECT sf.StockId
                                                    FROM StockFollowing sf
                                                    WHERE sf.UserId = '100' ))
                                ))



            UNION ALL
            SELECT DISTINCT p.Id AS Id, UNIX_TIMESTAMP(p.PostCreationTime) AS PostCreationTime, p.Content AS Content, p.Bu AS Bu, p.Se AS Se, UNIX_TIMESTAMP(upe.PostEchoTime) AS PostTimeOrder                 
                FROM Post p
                INNER JOIN UserPostE upe 
                    on p.Id = upe.PostId 
                INNER JOIN UserFollowing uf 
                    on (upe.UserId = uf.FollowedId AND (uf.FollowingId = '100' OR upe.UserId = '100'))  
            ORDER BY PostTimeOrder DESC;    

Answer 1

更改您的p.ID in (...)具有相关子查询的存在谓词的谓词可能有帮助。此外，由于所有联合查询的两半都从 Post 表中提取，并且可能返回几乎相同的记录，您可能可以通过左外连接到 UserPostE 将两者合并为一个查询。并添加 upe.PostID不为空 OR条件在 WHERE条款。 UserFollowing仍将内联至UPE。如果您想要使用 upe.PostEchoTime 两次相同的帖子记录一次和一次 p.PostCreationTime作为PostTimeOrder您需要保留UNION ALL

SELECT 
       DISTINCT -- <<=- May not be needed
       p.Id
     , UNIX_TIMESTAMP(p.PostCreationTime) AS PostCreationTime
     , p.Content AS Content
     , p.Bu AS Bu
     , p.Se AS Se
     , UNIX_TIMESTAMP(coalesce( upe.PostEchoTime
                              , p.PostCreationTime)) AS PostTimeOrder
  FROM Post p 
  LEFT JOIN UserPostE upe 
       INNER JOIN UserFollowing uf 
          on (upe.UserId = uf.FollowedId AND
             (uf.FollowingId = '100' OR
              upe.UserId = '100'))
    on p.Id = upe.PostId 
 WHERE upe.PostID is not null
    or exists (SELECT 1
                 FROM PostCreator pc
                WHERE pc.PostId = p.ID
                  and pc.UserId = '100'
                   or exists (SELECT 1
                                FROM UserFollowing uf
                               WHERE uf.FollowedId = pc.UserID
                                 and uf.FollowingId = '100')
              )
    OR exists (SELECT 1
                 FROM PostUserMentions pum
                WHERE pum.PostId = p.ID
                  and pum.UserId = '100'
                   or exists (SELECT 1
                                FROM UserFollowing uf
                               WHERE uf.FollowedId = pum.UserId
                                 and uf.FollowingId = '100')
              )
    OR exists (SELECT 1
                 FROM SStreamPost ssp
                WHERE ssp.PostId = p.ID
                  and exists (SELECT 1
                                FROM SStreamFollowing ssf
                               WHERE ssf.SStreamId = ssp.SStreamId
                                 and ssf.UserId = '100')
              )
    OR exists (SELECT 1
                 FROM PostSMentions psm
                WHERE psm.PostId = p.ID
                  and exists (SELECT 
                                FROM StockFollowing sf
                               WHERE sf.StockId = psm.StockId
                                 and sf.UserId = '100' )
              )
 ORDER BY PostTimeOrder DESC

from 部分也可以重写为也使用带有相关子查询的存在子句:

  FROM Post p 
  LEFT JOIN UserPostE upe 
    on p.Id = upe.PostId 
   and ( upe.UserId = '100'
      or exists (select 1
                   from UserFollowing uf
                  where uf.FollwedID = upe.UserID
                    and uf.FollowingId = '100'))