mysql - 如何解决这个极其慢的 MYSQL 查询

Question

此查询的目的是列出某人有连接的不同用户(即，被 ID 为 256 的用户关注或正在关注的用户，但排除正在阻止或被当前用户阻止的用户，从而使请求(ID为2的用户)

关系表非常简单。状态列可以是两个值之一:“正在关注”或“已阻止”:

mysql> describe relationships;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| follower_id | int(11)      | YES  | MUL | NULL    |                |
| followee_id | int(11)      | YES  | MUL | NULL    |                |
| created_at  | datetime     | YES  |     | NULL    |                |
| updated_at  | datetime     | YES  |     | NULL    |                |
| status      | varchar(191) | YES  | MUL | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

此查询目前大约需要 58 秒才能完成!用户 256 只有 1500 个连接。就上下文而言，大约有 10,000 个用户行，5500 个关系行。

SELECT DISTINCT `users`.*, 
    -- "followed" is just a flag indicating if user #2 is currently following a given user
    (
      SELECT COUNT(*) FROM `relationships`  
      WHERE `relationships`.`followee_id` = `users`.`id` 
        AND `relationships`.`follower_id` = 2
    ) AS 'followed'
FROM `users` 
INNER JOIN `relationships` 
ON (
  (`users`.`id` = `relationships`.`follower_id` 
    AND `relationships`.`followee_id` = 256
  ) 
  OR (`users`.`id` = `relationships`.`followee_id` 
    AND `relationships`.`follower_id` = 256
  )
)
WHERE `relationships`.`status` = 'following' 
  AND (
    -- Ensure we don't return users who are blocked by user #2 
    `users`.`id` NOT IN (
      SELECT `relationships`.`followee_id` 
      FROM `relationships` 
      WHERE `relationships`.`follower_id` = 2
        AND `relationships`.`status` = 'blocked'
    )
  )
  AND (
    -- Ensure we don't return users who are blocking user #2 
    `users`.`id` NOT IN (
      SELECT `relationships`.`follower_id` 
      FROM `relationships` 
      WHERE `relationships`.`followee_id` = 2 
        AND `relationships`.`status` = 'blocked'
    )
  )
ORDER BY `users`.`id` ASC 
LIMIT 10

以下是关系的当前索引:

mysql> show index from relationships;
+---------------+------------+---------------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name                                                      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+---------------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| relationships |          0 | PRIMARY                                                       |            1 | id          | A         |        3002 |     NULL | NULL   |      | BTREE      |         |               |
| relationships |          0 | index_relationships_on_status_and_follower_id_and_followee_id |            1 | status      | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| relationships |          0 | index_relationships_on_status_and_follower_id_and_followee_id |            2 | follower_id | A         |        3002 |     NULL | NULL   | YES  | BTREE      |         |               |
| relationships |          0 | index_relationships_on_status_and_follower_id_and_followee_id |            3 | followee_id | A         |        3002 |     NULL | NULL   | YES  | BTREE      |         |               |
| relationships |          1 | index_relationships_on_followee_id                            |            1 | followee_id | A         |        3002 |     NULL | NULL   | YES  | BTREE      |         |               |
| relationships |          1 | index_relationships_on_follower_id                            |            1 | follower_id | A         |        3002 |     NULL | NULL   | YES  | BTREE      |         |               |
| relationships |          1 | index_relationships_on_status_and_followee_id_and_follower_id |            1 | status      | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| relationships |          1 | index_relationships_on_status_and_followee_id_and_follower_id |            2 | followee_id | A         |        3002 |     NULL | NULL   | YES  | BTREE      |         |               |
| relationships |          1 | index_relationships_on_status_and_followee_id_and_follower_id |            3 | follower_id | A         |        3002 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------------+------------+---------------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

解释结果:

mysql> EXPLAIN SELECT DISTINCT `users`.*, (SELECT COUNT(*) FROM `relationships` WHERE `relationships`.`followee_id` = `users`.`id` AND `relationships`.`follower_id` = 2) AS 'followed' FROM `users` INNER JOIN `relationships` ON(`users`.`id` = `relationships`.`follower_id` AND `relationships`.`followee_id` = 256) OR (`users`.`id` = `relationships`.`followee_id` AND `relationships`.`follower_id` = 256) WHERE `relationships`.`status` = 'following' AND (`users`.`id` NOT IN (SELECT `relationships`.`followee_id` FROM `relationships` WHERE `relationships`.`follower_id` = 2 AND `relationships`.`status` = 'blocked')) AND (`users`.`id` NOT IN (SELECT `relationships`.`follower_id` FROM `relationships` WHERE `relationships`.`followee_id` = 2 AND `relationships`.`status` = 'blocked')) ORDER BY `users`.`id` ASC LIMIT 10;
+----+--------------------+---------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------+---------+-------------------------------+------+----------------------------------------------------------------------------------------------------------------------------------+
| id | select_type        | table         | type        | possible_keys                                                                                                                                                                                     | key                                                                   | key_len | ref                           | rows | Extra                                                                                                                            |
+----+--------------------+---------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------+---------+-------------------------------+------+----------------------------------------------------------------------------------------------------------------------------------+
|  1 | PRIMARY            | relationships | index_merge | index_relationships_on_status_and_follower_id_and_followee_id,index_relationships_on_followee_id,index_relationships_on_follower_id,index_relationships_on_status_and_followee_id_and_follower_id | index_relationships_on_followee_id,index_relationships_on_follower_id | 5,5     | NULL                          |    2 | Using union(index_relationships_on_followee_id,index_relationships_on_follower_id); Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | users         | ALL         | PRIMARY                                                                                                                                                                                           | NULL                                                                  | NULL    | NULL                          | 1534 | Range checked for each record (index map: 0x1)                                                                                   |
|  4 | SUBQUERY           | relationships | ref         | index_relationships_on_status_and_follower_id_and_followee_id,index_relationships_on_followee_id,index_relationships_on_follower_id,index_relationships_on_status_and_followee_id_and_follower_id | index_relationships_on_status_and_follower_id_and_followee_id         | 767     | const                         |    1 | Using where; Using index                                                                                                         |
|  3 | SUBQUERY           | relationships | ref         | index_relationships_on_status_and_follower_id_and_followee_id,index_relationships_on_followee_id,index_relationships_on_follower_id,index_relationships_on_status_and_followee_id_and_follower_id | index_relationships_on_status_and_follower_id_and_followee_id         | 772     | const,const                   |    1 | Using where; Using index                                                                                                         |
|  2 | DEPENDENT SUBQUERY | relationships | ref         | index_relationships_on_followee_id,index_relationships_on_follower_id                                                                                                                             | index_relationships_on_followee_id                                    | 5       | development.users.id |    1 | Using where                                                                                                                      |
+----+--------------------+---------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------+---------+-------------------------------+------+----------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)

Answer 1

如果不进行测试，很难给你一个具体的答案，但我认为这部分查询是问题所在

SELECT DISTINCT `users`.*, (
      SELECT COUNT(*) FROM `relationships`  
      WHERE `relationships`.`followee_id` = `users`.`id` 
        AND `relationships`.`follower_id` = 2
    ) AS 'followed'

您还使用了 order by。删除 DISTINCT 并按顺序排序，看看速度是否加快。我知道它改变了查询，但我怀疑，distinct 基本上是构建一堆临时表，并为需要检查的每一行丢弃它们。看看这里

http://dev.mysql.com/doc/refman/5.7/en/distinct-optimization.html

计数可能会很慢。确保计数是从最快的列开始进行的。看到这个...

https://www.percona.com/blog/2007/04/10/count-vs-countcol/

思考 SQL 的一个好方法是使用 SETS。幸运的是 MySQL 支持子查询。

https://dev.mysql.com/doc/refman/5.7/en/from-clause-subqueries.html

下面是一些伪 SQL...

select user_id
from relationships as follower, relationships as followee
where ...

在上面我们有两个可以操作的集合。使用子查询这变得非常有趣

select user_id
from (select user_id as f1 from relationships where ...) as follower, 
     (select user_id as f2 from relationships where ...) as followee
where ...

我总是发现像上面这样的东西是考虑自引用表的简单方法。