MySQL是否会自动优化子查询？

Question

我想运行以下查询：

-- Main Query    
SELECT COUNT(*) FROM table_name WHERE device_id IN 
     (SELECT DISTINCT device_id FROM table_name WHERE NAME = 'SOME_PARA')

以下查询（来自主查询的子查询）：

SELECT DISTINCT device_id FROM table_name WHERE NAME = 'SOME_PARA'

在7秒内执行，从210万行的表中产生了2691行。

我在上面触发了 主查询 ，并且在等待5分钟以上之后它仍在执行。

最后，我分别执行了子查询，从结果中提取了2691条记录，执行了以下查询：

-- Main Query (improvised)    
SELECT COUNT(*) FROM table_name WHERE device_id IN 
     ("device_id_1", "device_id_2", ....., "device_id_2691")

令人惊讶的是，这在40秒内给了我一个答案。

是什么赋予了？为什么MySQL不使用我使用的相同技术并迅速给出答案？难道我做错了什么？

Answer 1

不幸的是，MySQL在用IN优化子查询方面不是很擅长。这来自MySQL文档：

IN的子查询优化不如=运算符或IN（value_list）运算符有效。

IN子查询性能较差的典型情况是，子查询返回的行数很少，而外部查询返回的行数要与子查询结果进行比较。

问题在于，对于使用IN子查询的语句，优化器将其重写为相关子查询。考虑以下使用不相关子查询的语句：

在t1的SELECT … FROM t1.a IN（从t2的SELECT b）中;

优化器将语句重写为相关的子查询：

从t1存在的地方选择…（从t2 t2.b = t1.a的地方选择1）;

如果内部查询和外部查询分别返回M和N行，则执行时间将变成O（M脳N）的顺序，而不是像不相关子查询那样的O（M + N）。

暗示是，IN子查询可能比使用IN（value_list）运算符编写的查询慢得多，该运算符列出了子查询将返回的相同值。

尝试改用JOIN。

因为MySQL从内到外起作用，所以有时您可以通过将子查询包装在另一个子查询中来欺骗MySQL，如下所示：

SELECT COUNT(*) FROM table_name WHERE device_id IN
     (SELECT * FROM (SELECT DISTINCT device_id FROM table_name WHERE NAME = 'SOME_PARA') tmp)

这是JOIN解决方案：

SELECT COUNT(DISTINCT t2.id) FROM table_name t1
  JOIN table_name t2
    ON t2.device_id = t1.device_id
  WHERE t1.NAME = 'SOME_PARA'

请注意，我从内而外开始。