首页 / MYSQL / mysql-JOIN vs. WHERE：为什么两个获得相同结果的查询表现出3-4个数量级的性能差异？

mysql-JOIN vs. WHERE：为什么两个获得相同结果的查询表现出3-4个数量级的性能差异？

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql-JOIN vs. WHERE：为什么两个获得相同结果的查询表现出3-4个数量级的性能差异？，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2738字，纯文字阅读大概需要4分钟。

内容图文

今晚早些时候,我问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

(即,一个SQL查询返回的所有行的code1列中的任何数据均出现多次)…

我该怎么做？

我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中的每个代码,它将再次运行子查询.当您的外部查询非常狭窄时,哪种方法比较好,因为只运行几次子查询比对所有值运行并将其存储在临时表中的效率更高,但是当您的外部查询很宽时,结果在内部查询中执行了很多次,这就是性能差异的所在.

因此,对于您的行计数,您无需运行子查询约30,000次,而是运行一次,然后针对仅包含400行的哈希临时表查找约30,000行.这将导致如此巨大的性能差异.

This article in the online docs更深入地说明了子查询优化.

内容总结

以上是互联网集市为您收集整理的mysql-JOIN vs. WHERE：为什么两个获得相同结果的查询表现出3-4个数量级的性能差异？全部内容，希望文章能够帮你解决mysql-JOIN vs. WHERE：为什么两个获得相同结果的查询表现出3-4个数量级的性能差异？所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/881200.html

来源：【匿名】