今晚早些时候，我问了this question on StackOverflow关于如何编写 SQL 查询以通过仅返回在一个字段中具有重复项的行来过滤表中的行。

这里是问题，为方便起见重复:

如果我有这些数据:

code1 code2
  1    10       <-- Desired (1 appears more than once)
  1    11       <-- Desired (1 appears more than once)
  2    20
  3    30       <-- Desired (3 appears more than once)
  3    31       <-- Desired (3 appears more than once)
  4    40
  5    50

...我想编写一个单个 SQL 查询，其结果如下:

code1 code2
  1    10       <-- This result appears because 1 appears more than once above
  1    11       <-- This result appears because 1 appears more than once above
  3    30       <-- This result appears because 3 appears more than once above
  3    31       <-- This result appears because 3 appears more than once above

(即，返回 code1 列中的任何数据多次出现的所有行的单个 SQL 查询)...

我该怎么做？

我received an answer有两个可能的 SQL 查询，它们都能完美地工作。

成功的 SQL #1:

SELECT code1, code2
FROM myTable
WHERE code1 IN 
    (SELECT code1 FROM myTable GROUP BY code1 HAVING COUNT(code1) > 1)

成功的 SQL #2:

SELECT t.code1, code2
FROM myTable t
  INNER JOIN
    (SELECT code1 FROM myTable GROUP BY code1 HAVING COUNT(code1) > 1)
     s on s.code1 = t.code1

正如我在答案下方的评论中所描述的:

myTable has ~30000 rows, with only about 400 duplicate groups, and almost always just 2 entries per duplicate group. On my MySQL instance running on a high-end workstation, SQL #1 takes ~30 minutes to execute, whereas SQL #2 requires a fraction of a second.

这在上述两个查询之间在性能上有三到四个数量级的差异。

令我困扰的是，在我的用例中，为什么一个人的表现比另一个人好三个数量级。

我想更好地了解 SQL 执行的内部结构，这个特定示例非常适合帮助我。

我的问题是:为什么在我的用例中，SQL #2 的性能比 SQL #1 快 5,000 倍？

最佳答案

MySQL 有 known issues优化涉及相关子查询或子选择的查询。在 5.6.5 版之前，它不会具体化子查询，但会具体化连接中使用的派生表。

本质上这意味着当您使用连接时，第一次遇到子查询时 MySQL 将执行以下操作:

SELECT code1 FROM myTable GROUP BY code1 HAVING COUNT(code1) > 1

并将结果保存在一个临时表中(经过哈希处理以加快查找速度)，然后对于 myTable 中的每个值，它将查找临时表以查看代码是否存在。

但是，因为当您使用 IN 时，子查询并未具体化并被重写为:

SELECT t1.code1, t1.code2
FROM myTable t1
WHERE EXISTS
    (   SELECT t2.code1 
        FROM myTable t2
        WHERE t2.Code1 = t1.Code1
        GROUP BY t2.code1 
        HAVING COUNT(t2.code1) > 1
    )

这意味着对于 myTable 中的每个 code，它都会再次运行子查询。当您的外部查询非常窄时很好，因为只运行几次子查询比对所有值运行它并将结果存储在临时表中更有效，但是当您的外部查询很宽时，它会产生在执行多次的内部查询中，这就是性能差异的开始。

因此，对于您的行数，不是运行子查询约 30,000 次，而是运行它一次，然后针对只有 400 行的散列临时表查找约 30,000 行。这将解释如此巨大的性能差异。

This article in the online docs更深入地解释子查询优化。

关于mysql - JOIN 与 WHERE : Why do two queries that obtain identical results exhibit 3-4 orders of magnitude performance difference?，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/21723017/