如何优化MySQL查询以提高查询速度的建议。

Question

SELECT * 
FROM   members 
WHERE  memberid IN (SELECT follows.followingid 
                    FROM   follows 
                    WHERE  follows.memberid = '$memberid' 
                           AND follows.followingid NOT IN (SELECT memberid 
                                                           FROM   userblock)) 
       AND memberid NOT IN (SELECT blockmemberid 
                            FROM   userblock 
                            WHERE  memberid = '$memberid')

上述查询在MySQL中执行时间接近4秒，我想知道有没有什么建议可以帮助我改进/优化它，以实现更快的执行时间？

- thisisready

---

我想问“为什么？”您能详细说明一下这个查询的逻辑吗？也许应该完全用更好的查询替换它。 - Malkocoglu

你使用的是哪个版本的MySQL？ MySQL在子查询优化方面非常弱。 - zavg

@Malkocoglu 我正在尝试搜索与“follows”表中的memberid有关系的表中的值。然后，我想过滤掉在userblock表中发生该关系的任何值。 - thisisready

@zavg 我正在使用MySQL 5.5.30版本。 - thisisready

我认为存在逻辑问题。您如何使用userblock表？userblock.memberid是阻止他人的人吗？而userblock.blockmemberid则是被阻止的人。 - Malkocoglu

请展示表格和索引定义。 - Andy Lester

Answer 1

将in子句替换为连接。我认为以下内容捕捉了逻辑。请注意，not in变成了一个带有条件的left join，在where子句中查找非匹配项。

SELECT m.* 
FROM members m
     follow f
     on m.memberid = f.followingid and 
        f.memberid = $memberid left join
     userblock ubf
     on follows.followingid = ubf.memberid left join
     userblock ub
     on m.memberid = ub.blockmemberid and
        ub.memberid = '$memberid'
where ub.blockmemberid is null and
      ubf.memberid is null;

- Gordon Linoff

Answer 2

这看起来很相似，但你有更少的嵌套查询。

SELECT * 
FROM   members m
WHERE  EXIST (SELECT f.followingid 
              FROM   follows f
              WHERE  f.memberid = '$memberid'
                     AND f.followingid = m.memberid)

       AND NOT EXIST (SELECT u.blockmemberid 
                      FROM   userblock u
                      WHERE  (m.memberid = '$memberid'
                             AND u.blockmemberid = m.memberid)
                          OR 
                             (u.blockmemberid = m.memberid
                             AND u.memberid = m.memberid) )

这是我从你的代码中反向工程出来的逻辑，没有看到表格。

- Malkocoglu

Answer 3

SELECT m.* 
FROM   members m
INNER JOIN follows f ON f.followingid = m.memberid AND
                        f.memberid = '$memberid'
LEFT OUTER JOIN userblock ub1 ON f.followingid = ub1.memberid 
LEFT OUTER JOIN userblock ub2 ON m.memberid = ub2.blockmemberid AND
                            ub2.memberid = '$memberid'
WHERE ub1.memberid IS NULL AND ub2.blockmemberid IS NULL

- Fabien TheSolution