今晚早些时候,我在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查询)。。。
我该怎么做?
我收到了两个可能的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
有大约30000行,只有大约400个重复组,并且几乎总是每个重复组只有2个条目。在我的MySQL实例上SQL#1在高端工作站上运行需要大约30分钟才能执行,而SQL#2需要几分之一秒。
这是上面两个查询之间性能的三到四个数量级的差异。
让我感到困扰的是,通过查看查询,我并没有立即意识到为什么在我的用例中,一个查询的性能要比另一个好三个数量级。
我想更好地了解SQL执行的内部结构,这个特殊的例子非常有助于实现这一点。
我的问题是:为什么在我的用例中,SQL#2的性能比SQL#1快5000倍?
MySQL在优化涉及相关子查询或子选择的查询方面存在已知问题。在版本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
,它都会再次运行子查询。当外部查询非常窄时,这是可以的,因为只运行几次子查询比为所有值运行子查询并将结果存储在临时表中更有效,但是当外部查询很宽时,会导致内部查询执行多次,这就是性能差异的原因。
因此,对于行数,您不需要运行约30000次子查询,而是运行一次,然后在一个只有400行的哈希临时表中查找约30000行。这将导致如此巨大的性能差异。
在线文档中的这篇文章更深入地解释了子查询优化。