MySQL - SELECT WHERE 字段 IN（子查询） - 为什么非常慢？

发布于 2024-11-10 09:39:57 字数 845 浏览 0 评论 0原文

我想检查数据库中有几个重复项，所以为了查看哪些是重复项，我做了以下操作：

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

这样，我将获得所有相关字段多次出现的行。该查询需要几毫秒的时间来执行。

现在，我想检查每个重复项，所以我想我可以在上面的查询中使用相关字段来选择 some_table 中的每一行，所以我这样做了：

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

由于某种原因，这变得极其缓慢（需要几分钟）。到底发生了什么事情让它变得这么慢？ related_field 已建立索引。

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

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

：工作得很好。 MySQL 在几毫秒内完成此操作。

这里有 SQL 专家可以解释一下发生了什么吗？

原文

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.

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.

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
)

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

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

暮年 2024-11-17 09:39:58

子查询针对每一行运行，因为它是相关查询。通过从子查询中选择所有内容，可以将相关查询变成非相关查询，如下所示：

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
)

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

The final query would look like this:

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
)

回复收藏 0 原文

赠佳期 2024-11-17 09:39:58

将查询重写为这个

SELECT st1.*, st2.relevant_field FROM sometable st1
INNER JOIN sometable st2 ON (st1.relevant_field = st2.relevant_field)
GROUP BY st1.id  /* list a unique sometable field here*/
HAVING COUNT(*) > 1

我认为st2.relevant_field必须在选择中，因为否则having子句会给出错误，但我不是100%确保

永远不要将 IN 与子查询一起使用；这是出了名的慢。
仅将 IN 与固定值列表一起使用。

更多提示

如果您想让查询更快，
不执行 SELECT * 仅选择
您真正需要的领域。
确保您在 relevant_field 上有索引，以加快等连接速度。
确保在主键上group by。
如果您使用 InnoDB并且您只选择索引字段（并且事情并不太复杂），那么 MySQL 将仅使用索引来解析您的查询，从而加快速度。

90% IN（选择 查询）的通用解决方案

使用此代码

SELECT * FROM sometable a WHERE EXISTS (
  SELECT 1 FROM sometable b
  WHERE a.relevant_field = b.relevant_field
  GROUP BY b.relevant_field
  HAVING count(*) > 1)

Rewrite the query into this

SELECT st1.*, st2.relevant_field FROM sometable st1
INNER JOIN sometable st2 ON (st1.relevant_field = st2.relevant_field)
GROUP BY st1.id  /* list a unique sometable field here*/
HAVING COUNT(*) > 1

I think st2.relevant_field must be in the select, because otherwise the having clause will give an error, but I'm not 100% sure

Never use IN with a subquery; this is notoriously slow.
Only ever use IN with a fixed list of values.

More tips

If you want to make queries faster,
don't do a SELECT * only select
the fields that you really need.
Make sure you have an index on relevant_field to speed up the equi-join.
Make sure to group by on the primary key.
If you are on InnoDB and you only select indexed fields (and things are not too complex) than MySQL will resolve your query using only the indexes, speeding things way up.

General solution for 90% of your IN (select queries

Use this code

SELECT * FROM sometable a WHERE EXISTS (
  SELECT 1 FROM sometable b
  WHERE a.relevant_field = b.relevant_field
  GROUP BY b.relevant_field
  HAVING count(*) > 1)

回复收藏 0 原文

黯淡〆 2024-11-17 09:39:58

SELECT st1.*
FROM some_table st1
inner join 
(
    SELECT relevant_field
    FROM some_table
    GROUP BY relevant_field
    HAVING COUNT(*) > 1
)st2 on st2.relevant_field = st1.relevant_field;

我已经在我的一个数据库上尝试过您的查询，并且还尝试将其重写为子查询的联接。

这样效果快多了，试试吧！

SELECT st1.*
FROM some_table st1
inner join 
(
    SELECT relevant_field
    FROM some_table
    GROUP BY relevant_field
    HAVING COUNT(*) > 1
)st2 on st2.relevant_field = st1.relevant_field;

I've tried your query on one of my databases, and also tried it rewritten as a join to a sub-query.

This worked a lot faster, try it!

回复收藏 0 原文

胡渣熟男 2024-11-17 09:39:58

我已经使用 www.prettysql.net 重新格式化了您的慢速 sql 查询

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

在查询和子查询中使用表时，您应该始终为两者添加别名，如下所示：

SELECT *
FROM some_table as t1
WHERE
 t1.relevant_field in
 (
  SELECT t2.relevant_field
  FROM some_table as t2
  GROUP BY t2.relevant_field
  HAVING COUNT ( t2.relevant_field ) > 1
 );

这有帮助吗？

I have reformatted your slow sql query with www.prettysql.net

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

When using a table in both the query and the subquery, you should always alias both, like this:

SELECT *
FROM some_table as t1
WHERE
 t1.relevant_field in
 (
  SELECT t2.relevant_field
  FROM some_table as t2
  GROUP BY t2.relevant_field
  HAVING COUNT ( t2.relevant_field ) > 1
 );

Does that help?

回复收藏 0 原文

梦行七里 2024-11-17 09:39:58

子查询与联接

http://www.scribd.com/doc/2546837/New-Subquery-Optimizations-In-MySQL-6

回复收藏 0 原文

旧人九事 2024-11-17 09:39:58

首先，您可以查找重复的行，并查找行数被使用了多少次，并按数字排序；

SELECT q.id,q.name,q.password,q.NID,(select count(*) from UserInfo k where k.NID= q.NID) as Count,
(
		CASE q.NID
		WHEN @curCode THEN
			@curRow := @curRow + 1
		ELSE
			@curRow := 1
		AND @curCode := q.NID
		END
	) AS No
FROM UserInfo q,
(
		SELECT
			@curRow := 1,
			@curCode := ''
	) rt
WHERE q.NID IN
(
    SELECT NID
    FROM UserInfo
    GROUP BY NID
    HAVING COUNT(*) > 1
)

之后创建一个表并将结果插入其中。

create table CopyTable 
SELECT q.id,q.name,q.password,q.NID,(select count(*) from UserInfo k where k.NID= q.NID) as Count,
(
		CASE q.NID
		WHEN @curCode THEN
			@curRow := @curRow + 1
		ELSE
			@curRow := 1
		AND @curCode := q.NID
		END
	) AS No
FROM UserInfo q,
(
		SELECT
			@curRow := 1,
			@curCode := ''
	) rt
WHERE q.NID IN
(
    SELECT NID
    FROM UserInfo
    GROUP BY NID
    HAVING COUNT(*) > 1
)

最后，删除重复行。No从0开始。除了每组的第一个数字之外，删除所有重复行。

delete from  CopyTable where No!= 0;

Firstly you can find duplicate rows and find count of rows is used how many times and order it by number like this;

SELECT q.id,q.name,q.password,q.NID,(select count(*) from UserInfo k where k.NID= q.NID) as Count,
(
		CASE q.NID
		WHEN @curCode THEN
			@curRow := @curRow + 1
		ELSE
			@curRow := 1
		AND @curCode := q.NID
		END
	) AS No
FROM UserInfo q,
(
		SELECT
			@curRow := 1,
			@curCode := ''
	) rt
WHERE q.NID IN
(
    SELECT NID
    FROM UserInfo
    GROUP BY NID
    HAVING COUNT(*) > 1
)

after that create a table and insert result to it.

create table CopyTable 
SELECT q.id,q.name,q.password,q.NID,(select count(*) from UserInfo k where k.NID= q.NID) as Count,
(
		CASE q.NID
		WHEN @curCode THEN
			@curRow := @curRow + 1
		ELSE
			@curRow := 1
		AND @curCode := q.NID
		END
	) AS No
FROM UserInfo q,
(
		SELECT
			@curRow := 1,
			@curCode := ''
	) rt
WHERE q.NID IN
(
    SELECT NID
    FROM UserInfo
    GROUP BY NID
    HAVING COUNT(*) > 1
)

Finally, delete dublicate rows.No is start 0. Except fist number of each group delete all dublicate rows.

delete from  CopyTable where No!= 0;

回复收藏 0 原文

裸钻 2024-11-17 09:39:58

试试这个

SELECT t1.*
FROM 
 some_table t1,
  (SELECT relevant_field
  FROM some_table
  GROUP BY relevant_field
  HAVING COUNT (*) > 1) t2
WHERE
 t1.relevant_field = t2.relevant_field;

Try this

SELECT t1.*
FROM 
 some_table t1,
  (SELECT relevant_field
  FROM some_table
  GROUP BY relevant_field
  HAVING COUNT (*) > 1) t2
WHERE
 t1.relevant_field = t2.relevant_field;

回复收藏 0 原文

不疑不惑不回忆 2024-11-17 09:39:58

有时，当数据变得更大时，由于查询优化，mysql WHERE IN 可能会非常慢。尝试使用 STRAIGHT_JOIN 告诉 mysql 按原样执行查询，例如

SELECT STRAIGHT_JOIN table.field FROM table WHERE table.id IN (...)

，但要注意：在大多数情况下 mysql 优化器工作得很好，所以我建议仅在遇到此类问题时才使用它

sometimes when data grow bigger mysql WHERE IN's could be pretty slow because of query optimization. Try using STRAIGHT_JOIN to tell mysql to execute query as is, e.g.

SELECT STRAIGHT_JOIN table.field FROM table WHERE table.id IN (...)

but beware: in most cases mysql optimizer works pretty well, so I would recommend to use it only when you have this kind of problem

回复收藏 0 原文

-残月青衣踏尘吟 2024-11-17 09:39:58

这与我的情况类似，我有一个名为 tabel_buku_besar 的表。我需要的是

在tabel_buku_besar中查找具有account_code='101.100'的记录，其中具有companyarea='20000'并且还具有IDR as currency
我需要获取所有来自 tabel_buku_besar 的记录，其 account_code 与步骤 1 相同，但在步骤 1 结果中具有 transaction_number

在使用 select ... from...where....transaction_number in ( select transaction_number from ....)，我的查询运行速度非常慢，有时会导致请求超时或使我的应用程序没有响应...

我尝试了这种组合，结果...还不错...

`select DATE_FORMAT(L.TANGGAL_INPUT,'%d-%m-%y') AS TANGGAL,
      L.TRANSACTION_NUMBER AS VOUCHER,
      L.ACCOUNT_CODE,
      C.DESCRIPTION,
      L.DEBET,
      L.KREDIT 
 from (select * from tabel_buku_besar A
                where A.COMPANYAREA='$COMPANYAREA'
                      AND A.CURRENCY='$Currency'
                      AND A.ACCOUNT_CODE!='$ACCOUNT'
                      AND (A.TANGGAL_INPUT BETWEEN STR_TO_DATE('$StartDate','%d/%m/%Y') AND STR_TO_DATE('$EndDate','%d/%m/%Y'))) L 
INNER JOIN (select * from tabel_buku_besar A
                     where A.COMPANYAREA='$COMPANYAREA'
                           AND A.CURRENCY='$Currency'
                           AND A.ACCOUNT_CODE='$ACCOUNT'
                           AND (A.TANGGAL_INPUT BETWEEN STR_TO_DATE('$StartDate','%d/%m/%Y') AND STR_TO_DATE('$EndDate','%d/%m/%Y'))) R ON R.TRANSACTION_NUMBER=L.TRANSACTION_NUMBER AND R.COMPANYAREA=L.COMPANYAREA 
LEFT OUTER JOIN master_account C ON C.ACCOUNT_CODE=L.ACCOUNT_CODE AND C.COMPANYAREA=L.COMPANYAREA 
ORDER BY L.TANGGAL_INPUT,L.TRANSACTION_NUMBER`

This is similar to my case, where I have a table named tabel_buku_besar. What I need are

Looking for record that have account_code='101.100' in tabel_buku_besar which have companyarea='20000' and also have IDR as currency
I need to get all record from tabel_buku_besar which have account_code same as step 1 but have transaction_number in step 1 result

while using select ... from...where....transaction_number in (select transaction_number from ....), my query running extremely slow and sometimes causing request time out or make my application not responding...

I try this combination and the result...not bad...

`select DATE_FORMAT(L.TANGGAL_INPUT,'%d-%m-%y') AS TANGGAL,
      L.TRANSACTION_NUMBER AS VOUCHER,
      L.ACCOUNT_CODE,
      C.DESCRIPTION,
      L.DEBET,
      L.KREDIT 
 from (select * from tabel_buku_besar A
                where A.COMPANYAREA='$COMPANYAREA'
                      AND A.CURRENCY='$Currency'
                      AND A.ACCOUNT_CODE!='$ACCOUNT'
                      AND (A.TANGGAL_INPUT BETWEEN STR_TO_DATE('$StartDate','%d/%m/%Y') AND STR_TO_DATE('$EndDate','%d/%m/%Y'))) L 
INNER JOIN (select * from tabel_buku_besar A
                     where A.COMPANYAREA='$COMPANYAREA'
                           AND A.CURRENCY='$Currency'
                           AND A.ACCOUNT_CODE='$ACCOUNT'
                           AND (A.TANGGAL_INPUT BETWEEN STR_TO_DATE('$StartDate','%d/%m/%Y') AND STR_TO_DATE('$EndDate','%d/%m/%Y'))) R ON R.TRANSACTION_NUMBER=L.TRANSACTION_NUMBER AND R.COMPANYAREA=L.COMPANYAREA 
LEFT OUTER JOIN master_account C ON C.ACCOUNT_CODE=L.ACCOUNT_CODE AND C.COMPANYAREA=L.COMPANYAREA 
ORDER BY L.TANGGAL_INPUT,L.TRANSACTION_NUMBER`

回复收藏 0 原文

揽清风入怀 2024-11-17 09:39:58

我发现这是查找值是否存在最有效的方法，逻辑可以轻松地反转以查找值是否不存在（即 IS NULL）；

SELECT * FROM primary_table st1
LEFT JOIN comparision_table st2 ON (st1.relevant_field = st2.relevant_field)
WHERE st2.primaryKey IS NOT NULL

*将 related_field 替换为您要检查的表中是否存在的值的名称

*将 PrimaryKey 替换为比较表上主键列的名称。

I find this to be the most efficient for finding if a value exists, logic can easily be inverted to find if a value doesn't exist (ie IS NULL);

SELECT * FROM primary_table st1
LEFT JOIN comparision_table st2 ON (st1.relevant_field = st2.relevant_field)
WHERE st2.primaryKey IS NOT NULL

*Replace relevant_field with the name of the value that you want to check exists in your table

*Replace primaryKey with the name of the primary key column on the comparison table.

回复收藏 0 原文

欲拥i 2024-11-17 09:39:58

它很慢，因为每次 relevant_field 和 IN 子查询的子查询之间的比较都会执行一次子查询。您可以像这样避免这种情况：

SELECT *
FROM some_table T1 INNER JOIN 
(
    SELECT relevant_field
    FROM some_table
    GROUP BY relevant_field
    HAVING COUNT(*) > 1
) T2 
USING(relevant_field)

这将创建一个派生表（在内存中，除非它太大而无法容纳）作为 T2，然后将其与 T1 进行 INNER JOIN 。 JOIN 发生一次，因此查询被执行一次。

我发现这对于优化以下情况特别方便：使用枢轴将批量数据表与更具体的数据表关联起来，并且您希望根据更具体的相关行的子集生成批量表的计数。如果您可以将批量行的范围缩小到 <5%，那么所得的稀疏访问通常会比全表扫描更快。

即，您有一个用户表（条件）、一个订单表（数据透视表）和一个引用产品计数的 LineItems 表（批量）。您想要按邮政编码“90210”中的用户分组的产品总和。在这种情况下，JOIN 将比使用 WHERE related_field IN( SELECT * FROM (...) T2 ) 时小几个数量级，因此速度要快得多，特别是当该 JOIN 溢出到磁盘时！

It's slow because your sub-query is executed once for every comparison between relevant_field and your IN clause's sub-query. You can avoid that like so:

SELECT *
FROM some_table T1 INNER JOIN 
(
    SELECT relevant_field
    FROM some_table
    GROUP BY relevant_field
    HAVING COUNT(*) > 1
) T2 
USING(relevant_field)

This creates a derived table (in memory unless it's too large to fit) as T2, then INNER JOIN's it with T1. The JOIN happens one time, so the query is executed one time.

I find this particularly handy for optimising cases where a pivot is used to associate a bulk data table with a more specific data table and you want to produce counts of the bulk table based on a subset of the more specific one's related rows. If you can narrow down the bulk rows to <5% then the resulting sparse accesses will generally be faster than a full table scan.

ie you have a Users table (condition), an Orders table (pivot) and LineItems table (bulk) which references counts of Products. You want the sum of Products grouped by User in PostCode '90210'. In this case the JOIN will be orders of magnitude smaller than when using WHERE relevant_field IN( SELECT * FROM (...) T2 ), and therefore much faster, especially if that JOIN is spilling to disk!

回复收藏 0 原文

~没有更多了~