为什么我的MySQL查询这么慢？

Question

我试图弄清楚为什么查询这么慢(大约需要6秒才能得到结果)

SELECT DISTINCT
    c.id
FROM
    z1
        INNER JOIN
    c ON (z1.id = c.id)
        INNER JOIN
    i ON (c.member_id = i.member_id)
WHERE
    c.id NOT IN (... big list of ids which should be excluded)

这是执行计划

+----+-------------+-------+--------+-------------------+---------+---------+--------------------+--------+----------+--------------------------+
| id | select_type | table | type   | possible_keys     | key     | key_len | ref                | rows   | filtered | Extra                    |
+----+-------------+-------+--------+-------------------+---------+---------+--------------------+--------+----------+--------------------------+
|  1 | SIMPLE      | z1    | index  | PRIMARY           | PRIMARY | 4       | NULL               | 318563 |    99.85 | Using where; Using index; Using temporary |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY,member_id | PRIMARY | 4       | z1.id              |      1 |   100.00 |                          |
|  1 | SIMPLE      | i     | eq_ref | PRIMARY           | PRIMARY | 4       | c.member_id        |      1 |   100.00 | Using index              |
+----+-------------+-------+--------+-------------------+---------+---------+--------------------+--------+----------+--------------------------+

是不是因为mysql几乎要拆掉第一张表？可以调整吗？

Answer 1

您可以尝试用子查询替换c。

SELECT DISTINCT
    c.id
FROM
    z1
        INNER JOIN
    (select c.id 
     from c 
     WHERE
       c.id NOT IN (... big list of ids which should be excluded)) c ON (z1.id = c.id)
        INNER JOIN
    i ON (c.member_id = i.member_id)

只留下必要的id

Answer 2

从你提供的信息中不可能说出是否有更快的解决方案来获得相同的数据(我们需要知道数据分布和哪些外键是必需的)。但是，假设这是一个分层数据集，那么该计划可能不是最优的:减少行数的惟一谓词是c.id NOT IN....。

优化任何查询时要问自己的第一个问题是:我需要所有行吗？这将返回多少行？

我很难在返回一个'id‘值列表(意味着一组自动递增的整数)的查询中看到任何实用性。

您不能将索引用于NOT IN (或<>)，因此最有效的解决方案可能是从'c‘上的全表扫描开始--这应该是StanislavL查询的结果。

由于您不使用i和z的值，因此可以将连接替换为“exists”，这可能有助于提高性能。

Answer 3

我会考虑为c(id, member_id)创建一个复合索引。这样，查询应该只在索引级工作，而不扫描表中的任何行。