有人可以解释为什么这些查询之间存在很大差异吗？

他们的结果完全一样。

查询 1 的性能:非常好，查询 2:差，查询 3:好。

为什么在 query 2 select from table test (id 1) 中包含所有行？为什么 possible_keys 不包含实际使用的 PRIMARY？

表:

CREATE TABLE `test` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `test` ADD PRIMARY KEY (`id`);

数据:

DROP PROCEDURE IF EXISTS insert1000;
DELIMITER $$
CREATE PROCEDURE insert1000()
    BEGIN
        SET @i = 1;
        WHILE @i < 1000 DO
            INSERT INTO `test` VALUES (@i);
            SET @i = @i + 1;
        END WHILE;
    END
$$
DELIMITER ;
CALL insert1000();
DROP PROCEDURE insert1000;

查询 1:

SELECT `id` FROM `test` WHERE `id` IN (2, 3)

查询1解释:

+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| 1  | SIMPLE      | test  | range | PRIMARY       | PRIMARY | 4       | NULL | 2    | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

查询 2:

SELECT `id` FROM `test` WHERE `id` IN (SELECT 2 UNION SELECT 3)

查询2解释:

+------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id   | select_type        | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1    | PRIMARY            | test       | index | NULL          | PRIMARY | 4       | NULL | 999  | Using where; Using index |
+------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| 2    | DEPENDENT SUBQUERY | NULL       | NULL  | NULL          | NULL    | NULL    | NULL | NULL | No tables used           |
+------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| 3    | DEPENDENT UNION    | NULL       | NULL  | NULL          | NULL    | NULL    | NULL | NULL | No tables used           |
+------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| NULL | UNION RESULT       | <union2,3> | ALL   | NULL          | NULL    | NULL    | NULL | NULL |                          |
+------+--------------------+------------+-------+---------------+---------+---------+------+------+--------------------------+

查询 3:

SELECT `id` FROM `test` WHERE `id` IN (SELECT * FROM (SELECT 2 UNION SELECT 3) AS `derived`)

查询3解释:

+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+--------------------------+
| id   | select_type  | table       | type   | possible_keys | key     | key_len | ref       | rows | Extra                    |
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+--------------------------+
| 1    | PRIMARY      | <subquery2> | ALL    | distinct_key  | NULL    | NULL    | NULL      | 2    |                          |
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+--------------------------+
| 1    | PRIMARY      | test        | eq_ref | PRIMARY       | PRIMARY | 4       | derived.2 | 1    | Using where; Using index |
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+--------------------------+
| 2    | MATERIALIZED | <derived3>  | ALL    | NULL          | NULL    | NULL    | NULL      | 2    |                          |
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+--------------------------+
| 3    | DERIVED      | NULL        | NULL   | NULL          | NULL    | NULL    | NULL      | NULL | No tables used           |
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+--------------------------+
| 4    | UNION        | NULL        | NULL   | NULL          | NULL    | NULL    | NULL      | NULL | No tables used           |
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+--------------------------+
| NULL | UNION RESULT | <union3,4>  | ALL    | NULL          | NULL    | NULL    | NULL      | NULL |                          |
+------+--------------+-------------+--------+---------------+---------+---------+-----------+------+--------------------------+

最佳答案

MySQL 优化器的内部工作原理...

同时 query 2和 query 3两者都需要全表扫描(不能使用索引)，它们不同的语法使得优化器使用不同的策略。

运行 EXPLAIN EXTENDED SELECT ... 可以更清楚地看到它然后运行 ​​SHOW WARNINGS; .

这是 query 2 的扩展计划:

select `test`.`id` AS `id` 
from `test` 
where <in_optimizer>(`test`.`id`,<exists>(select 2 having (<cache>(`test`.`id`) = <ref_null_helper>(2)) 
                                          union 
                                          select 3 having (<cache>(`test`.`id`) = <ref_null_helper>(3))
                                          ))

优化器翻译IN至 EXISTS然后比较 2 个查询的结果 SELECT 2和 SELECT 3到 test 中扫描的行.

这是 query 3 的扩展计划:

select `test`.`id` AS `id` 
from `test` 
where <in_optimizer>(`test`.`id`,<exists>(select 1 from (select 2 AS `2` union select 3 AS `3`) `derived` where (<cache>(`test`.`id`) = `derived`.`2`)))

您可以看到，在这种情况下，优化器正在运行您原来的 UNION创建一个包含值 2 和 3 的派生表，然后将该表与它在表 test 中扫描的数据进行一次比较.

关于MySQL 对 IN 感到困惑(CONST vs UNION vs SELECT FROM (UNION))，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/40365711/