mysql-为什么使用IN(子查询)的查询要比使用IN(离散列表)的查询花费更长的时间

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql-为什么使用IN(子查询)的查询要比使用IN(离散列表)的查询花费更长的时间，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含1519字，纯文字阅读大概需要3分钟。

内容图文

这一直困扰着我,为什么这个查询

SELECT 
  * 
FROM
  `TABLE` 
WHERE `value` IN 
  (SELECT 
    val 
  FROM
    OTHER_TABLE 
  WHERE `date` < '2014-01-01')

比顺序运行这两个查询要慢几个数量级

SELECT 
  `val` 
FROM
  OTHER_TABLE 
WHERE `date` < '2014-01-01' 

Result:
+----+
| val |
+-----+
| v1  |
| v2  |
| v3  |
| v7  |
| v12 |
+-----+

和这个查询：

SELECT 
  * 
FROM
  `TABLE` 
WHERE `value` IN ('v1', 'v2', 'v3', 'v7', 'v12')

解决方法:

从文档中：(重点由我添加)

Subquery optimization for IN is not as effective as for the = operator
or for the IN(value_list) operator.

A typical case for poor IN subquery performance is when the subquery
returns a small number of rows but the outer query returns a large
number of rows to be compared to the subquery result.

The problem is that, for a statement that uses an IN subquery, the
optimizer rewrites it as a correlated subquery. Consider the following
statement that uses an uncorrelated subquery:

SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);

The optimizer rewrites the statement to a correlated subquery:

SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);

If the inner and outer queries return M and N rows, respectively, the
execution time becomes on the order of O(M×N), rather than O(M+N) as
it would be for an uncorrelated subquery.

An implication is that an IN subquery can be much slower than a query
written using an IN(value_list) operator that lists the same values
that the subquery would return.

http://dev.mysql.com/doc/refman/5.7/en/subquery-restrictions.html

希望这对其他可能好奇的人有所帮助

内容总结

以上是互联网集市为您收集整理的mysql-为什么使用IN(子查询)的查询要比使用IN(离散列表)的查询花费更长的时间全部内容，希望文章能够帮你解决mysql-为什么使用IN(子查询)的查询要比使用IN(离散列表)的查询花费更长的时间所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

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

内容手机端