SQL IN子句比单个查询慢 [英] SQL IN clause slower than individual queries

问题描述

我在MySQL 5.0.67中使用Hibernate的JPA实现。在执行JPA查询（将其转换为SQL）时，我发现使用 IN 子句比执行单个查询要慢。示例：

  SELECT p FROM Person p WHERE p.name IN（'Joe'，'Jane'，'Bob'，' Alice'）
  

比四个单独的查询慢：

  SELECT p FROM Person p WHERE p.name ='Joe'
 SELECT p FROM Person p WHERE p.name ='Jane'
 SELECT p FROM Person p WHERE p.name ='Bob'
 SELECT p FROM Person p WHERE p.name ='Alice'
  

这是为什么？这是一个MySQL性能限制吗？

解决方案

这是MySQL中一个已知的缺陷。

通常情况下，使用 UNION 的性能要好于您所显示的范围查询。 MySQL不会非常智能地使用 IN（...）表达式来使用索引。在优化器中存在一个与或一样的布尔表达式的类似漏洞。 http://www.mysqlperformanceblog.com/2006/ 08/10 / using-union-to-implement-loose-index-scan-to-mysql / ，以获得一些解释和详细的基准测试。 优化器正在不断改进。在一个版本的MySQL中的缺陷可能会在后续版本中得到改进。所以值得在不同版本上测试你的查询。

使用 UNION ALL 代替简单的 UNION 。两个查询都使用临时表来存储结果，但不同之处在于 UNION 对结果集应用了 DISTINCT ，其中招致额外的未索引排序。

I'm using Hibernate's JPA implementation with MySQL 5.0.67. MySQL is configured to use InnoDB.

In performing a JPA query (which is translated to SQL), I've discovered that using the IN clause is slower than performing individual queries. Example:

SELECT p FROM Person p WHERE p.name IN ('Joe', 'Jane', 'Bob', 'Alice')

is slower than four separate queries:

SELECT p FROM Person p WHERE p.name = 'Joe'
SELECT p FROM Person p WHERE p.name = 'Jane'
SELECT p FROM Person p WHERE p.name = 'Bob'
SELECT p FROM Person p WHERE p.name = 'Alice'

Why is this? Is this a MySQL performance limitation?

解决方案

This is a known deficiency in MySQL.

It is often true that using UNION performs better than a range query like the one you show. MySQL doesn't employ indexes very intelligently for expressions using IN (...). A similar hole exists in the optimizer for boolean expressions with OR.

See http://www.mysqlperformanceblog.com/2006/08/10/using-union-to-implement-loose-index-scan-to-mysql/ for some explanation and detailed benchmarks.

The optimizer is being improved all the time. A deficiency in one version of MySQL may be improved in a subsequent version. So it's worth testing your queries on different versions.

It is also advantageous to use UNION ALL instead of simply UNION. Both queries use a temporary table to store results, but the difference is that UNION applies DISTINCT to the result set, which incurs an additional un-indexed sort.

这篇关于SQL IN子句比单个查询慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！