MySQL-SELECT WHERE字段IN(子查询)-为什么极慢?

问题描述

我要检查的数据库中有几个重复项，因此我做了以下操作:

I've got a couple of duplicates in a database that I want to inspect, so what I did to see which are duplicates, I did this:

SELECT relevant_field
FROM some_table
GROUP BY relevant_field
HAVING COUNT(*) > 1

这样，我将获得与related_field一起出现的所有行不止一次.该查询需要毫秒才能执行.

This way, I will get all rows with relevant_field occuring more than once. This query takes milliseconds to execute.

现在，我想检查每个重复项，所以我想可以在上述查询中选择带有相关字段的some_table中的每一行，所以我做到了:

Now, I wanted to inspect each of the duplicates, so I thought I could SELECT each row in some_table with a relevant_field in the above query, so I did like this:

SELECT *
FROM some_table
WHERE relevant_field IN
(
    SELECT relevant_field
    FROM some_table
    GROUP BY relevant_field
    HAVING COUNT(*) > 1
)

由于某种原因，这实际上是缓慢的(需要几分钟).到底是什么使它变慢了?索引了related_field.

This turns out to be extreeeemely slow for some reason (it takes minutes). What exactly is going on here to make it that slow? relevant_field is indexed.

最终，我尝试从第一个查询(SELECT relevant_field FROM some_table GROUP BY relevant_field HAVING COUNT(*) > 1)创建视图"temp_view"，然后像这样进行第二个查询:

Eventually I tried creating a view "temp_view" from the first query (SELECT relevant_field FROM some_table GROUP BY relevant_field HAVING COUNT(*) > 1), and then making my second query like this instead:

SELECT *
FROM some_table
WHERE relevant_field IN
(
    SELECT relevant_field
    FROM temp_view
)

那很好. MySQL在几毫秒内完成了此操作.

And that works just fine. MySQL does this in some milliseconds.

这里有任何SQL专家可以解释发生了什么事吗?

Any SQL experts here who can explain what's going on?

推荐答案

正在为每一行运行子查询，因为它是一个相关查询.通过从子查询中选择所有内容，可以将相关查询变成不相关查询，如下所示:

The subquery is being run for each row because it is a correlated query. One can make a correlated query into a non-correlated query by selecting everything from the subquery, like so:

SELECT * FROM
(
    SELECT relevant_field
    FROM some_table
    GROUP BY relevant_field
    HAVING COUNT(*) > 1
) AS subquery

最终查询如下:

SELECT *
FROM some_table
WHERE relevant_field IN
(
    SELECT * FROM
    (
        SELECT relevant_field
        FROM some_table
        GROUP BY relevant_field
        HAVING COUNT(*) > 1
    ) AS subquery
)

这篇关于MySQL-SELECT WHERE字段IN(子查询)-为什么极慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！