MySQL - SELECT WHERE field IN(子查询) - 为什么非常慢?

2021-11-20 00:00:00 subquery mysql 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

这样，我将获得相关字段出现不止一次的所有行.此查询需要几毫秒才能执行.

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
)

由于某种原因，这变得非常缓慢(需要几分钟).这里到底发生了什么让它变得这么慢?相关字段已编入索引.

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 related_field FROM some_table GROUP BY related_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
)

相关文章