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

| 我要检查的数据库中有几个重复项，因此，为了查看哪些重复项，我执行了以下操作：

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

这样，我将获得与related_field一起出现的所有行不止一次。该查询需要毫秒来执行。现在，我想检查每个重复项，因此我想可以在上述查询中选择带有some_table的每一行以及一个related_field，因此我做到了：

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

由于某种原因，这实际上是缓慢的（需要几分钟）。到底是什么使它变慢了？ related_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专家都可以解释发生了什么情况？

已邀请:

10 个回复

豪抱怒掳

将查询重写为此

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

我认为ѭ5select必须在选择中，因为否则having子句会给出错误，但我不确定100％切勿在子查询中使用ѭ7。众所周知，这很慢。只能将IN与固定值列表一起使用。更多提示如果您想更快地进行查询，不选择SELECT * 您真正需要的字段。确保在relevant_field上有一个索引以加快等参连接的速度。确保在主键上ѭ11。如果您在InnoDB上并且仅选择索引字段（并且事情不太复杂），那么MySQL将仅使用索引来解决您的查询，从而加快了工作速度。 90％的“ 12”查询通用解决方案使用此代码

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
)

穗揣

子查询与联接 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 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;

骨酚柯

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

有帮助吗？

坊岔埠绵

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

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

但要注意：在大多数情况下，mysql优化器运行良好，因此，我建议仅在遇到此类问题时才使用它

渐首洽陈染

首先，您可以找到重复的行，并找到使用的行数，并按这样的编号对其进行排序；

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
)

济畦刨

这与我的情况类似，我有一个名为tabel_buku_besar的表。我需要的是寻找在tabel_buku_besar中具有account_code=\'101.100\'且具有companyarea=\'20000\'并且也具有IDR作为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`

辩谷变充

我发现这是查找值是否存在的最有效方法，可以很容易地将逻辑取反以查找值是否不存在（即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。

要回复问题请先登录或注册

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

10 个回复

发起人

mysql

subquery

where_in

问题状态

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

与内容相关的链接

10 个回复

发起人

mysql

subquery

where_in

问题状态