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

我在一个数据库里找到了几个副本，我想检查一下，所以我做了这样的检查，看哪些是副本:

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

通过这种方式，我将获得所有发生一次以上的 relevant _ field 行。

现在，我想检查每个重复项，所以我认为我可以用上面查询中的 relevant _ field 对 some _ table 中的每一行进行 SELECT，所以我这样做了:

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

由于某种原因，这个过程变得极其缓慢(需要几分钟)。到底发生了什么事让它变得这么慢？Relevant _ field 是索引的。

最后，我尝试从第一个查询 (SELECT relevant_field FROM some_table GROUP BY relevant_field HAVING COUNT(*) > 1)创建一个视图“ temp _ view”，然后像下面这样创建第二个查询:

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

MySQL 可以在几毫秒内完成这项工作。

这里有没有 SQL 专家能解释一下发生了什么？

340367 次浏览
小开

我用 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
);

有帮助吗？

小开

子查询与联接

Http://www.scribd.com/doc/2546837/new-subquery-optimizations-in-mysql-6

小开 
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.*, 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。

更多提示

  1. 如果希望查询速度更快, 不要只做 SELECT *选择 你真正需要的领域。
  2. 确保在 relevant_field上有一个索引来加速等效连接。
  3. 确保主键上的 group by。
  4. 如果你在 InnoDB 还有上，你只选择索引字段 (事情并不太复杂)比 MySQL 将解决你的查询只使用索引，加快事情的方式了。

90% 的 IN (select查询的通用解决方案

用这个密码

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)
小开
最佳答案

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

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
)
小开

试试这个

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;
小开

由于查询优化，有时当数据增长到更大的 mysql 时，WHERE IN 的速度可能会非常慢。尝试使用 STRIGHT _ JOIN 告诉 mysql 按原样执行查询，例如。

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

但是请注意: 在大多数情况下，mysql 优化器工作得非常好，所以我建议只有在您遇到这种问题时才使用它

小开

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

  1. 寻找记录，有 account_code='101.100'在 tabel_buku_besar有 companyarea='20000'，也有 IDR作为 currency

  2. 我需要从 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`
小开

我发现这是最有效的发现如果一个值存在，逻辑可以很容易地被反转，以发现如果一个值不存在(即是 NULL) ;

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

* 用要检查表中是否存在的值的名称替换 relevant _ field

* 用比较表中主键列的名称替换 primaryKey。

小开

首先，你可以找到重复的行，找出行的数量是多少次使用，并按照这样的数字排序;

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
)

最后，删除公开的行。否为开始0。除每组的第一个数字外，删除所有公开的行。

delete from  CopyTable where No!= 0;

小开

它很慢，因为对于 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的派生表(在内存中，除非它太大而不能容纳) ，然后 INNER JOIN与 T1一起创建。JOIN 只发生一次，因此查询只执行一次。

我发现这对于优化这样的情况特别方便: 使用 pivot 将大容量数据表与更具体的数据表关联起来，并且希望基于更具体的相关行的子集生成大容量表的计数。如果可以将大容量行缩小到 < 5% ，那么产生的稀疏访问通常会比全表扫描更快。

即您有一个用户表(条件) ，一个订单表(枢轴)和 LineItems 表(批量) ，其中引用了产品的计数。您想要按用户在 PostCode’90210’中分组的产品总和。在这种情况下，JOIN 的数量级比使用 WHERE relevant_field IN( SELECT * FROM (...) T2 )时要小，因此速度要快得多，特别是当 JOIN 溢出到磁盘上的时候！


提交答案