mysql - 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 documentation :

Subquery optimization for IN is not as effective as for the = operator or for the IN(value_list) operator.

A typical case for poor IN subquery performance is when the subquery returns a small number of rows but the outer query returns a large number of rows to be compared to the subquery result.

The problem is that, for a statement that uses an IN subquery, the optimizer rewrites it as a correlated subquery. Consider the following statement that uses an uncorrelated subquery:

SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);

The optimizer rewrites the statement to a correlated subquery:

SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);

If the inner and outer queries return M and N rows, respectively, the execution time becomes on the order of O(M×N), rather than O(M+N) as it would be for an uncorrelated subquery.

An implication is that an IN subquery can be much slower than a query written using an IN(value_list) operator that lists the same values that the subquery would return.

尝试使用 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'

注意，我是从内而外的。