当 WHERE 子句有多个 IN 时，MySQL 检查太多行

Question

我目前使用的是 MySQL 5.6.10。

我的实际查询比较复杂，但这里有一个重现问题的简单方法。我知道下面的查询是无用的(select id from x where id in (select id from x...))，但它证明了我的观点。

我创建了这个表:

CREATE  TABLE test (
  id INT NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (id));

然后运行此命令 5 次 - 它在表中创建了 50 行:

INSERT INTO test (id) VALUES(null),(null),(null),(null),(null),(null),(null),(null),(null),(null);

然后运行这个解释:

EXPLAIN SELECT id FROM test WHERE 
       id in (SELECT id FROM test WHERE id < 5);

得到这个:

这对我来说非常有意义。但是如果我用另一个 IN 在 WHERE 子句中添加一个 OR，就像这样:

EXPLAIN SELECT id FROM test WHERE 
       id IN (SELECT id FROM test WHERE id < 5)
    OR id IN (SELECT id FROM test WHERE id > 45);

MySQL 突然查看所有 50 行:

我知道查询可以重写为 SELECT id FROM test WHERE id < 5 OR id > 45 ，或 UNION 等，这不是重点。关键是 MySQL 检查的行太多了。

如果我在第一个查询上运行 FLUSH STATUS/SHOW STATUS LIKE "Handler%"，这就是我得到的:

Handler_read_key 5
Handler_external_lock 4
Handler_read_next 4
Handler_read_first 1

但是如果我对第二个查询这样做，我会得到:

Handler_read_key 99
Handler_write 9
Handler_external_lock 6
Handler_read_next 59
Handler_read_first 2

为什么会有这么大的不同？我想知道它是否是优化器，如果是的话，我可以在查询中包含一些选项来阻止这种“优化”吗？这对我正在开发的查询具有真正的实际意义。 MySQL 不是只检查几百行，而是检查 120,000 行。

Answer 1

一般来说，RDBMS 不能像优化适当的表连接那样优化子查询。如 Rewriting Subqueries as Joins 下所述(强调):

Sometimes there are other ways to test membership in a set of values than by using a subquery. Also, on some occasions, it is not only possible to rewrite a query without a subquery, but it can be more efficient to make use of some of these techniques rather than to use subqueries. One of these is the IN() construct:

For example, this query:

SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);

Can be rewritten as:

SELECT DISTINCT t1.* FROM t1, t2 WHERE t1.id=t2.id;

在您的抽象案例中(即忽略在现实中对该查询做出的其他明显改进):

SELECT DISTINCT t1.*
FROM   test t1
  JOIN test t2 USING (id)
  JOIN test t3 USING (id)
WHERE  t2.id < 5
    OR t3.id > 45;

执行计划是:

+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------------------------------------+| ID | SELECT_TYPE | TABLE |  TYPE  | POSSIBLE_KEYS |   KEY   | KEY_LEN |       REF        | ROWS |                   EXTRA                   |+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------------------------------------+|  1 | SIMPLE      | t1    | range  | PRIMARY       | PRIMARY |       4 | (null)           |    9 | Using where; Using index; Using temporary ||  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY |       4 | db_2_129b4.t1.id |    1 | Using index; Distinct                     ||  1 | SIMPLE      | t3    | eq_ref | PRIMARY       | PRIMARY |       4 | db_2_129b4.t1.id |    1 | Using index; Distinct                     |+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------------------------------------+

查看 sqlfiddle .