SQL IN子句比单个查询慢

Question

我在MySQL 5.0.67中使用Hibernate的JPA实现。MySQL配置为使用InnoDB。

在执行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性能限制吗？

Answer 1

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

使用UNION通常比使用您所显示的范围查询执行得更好。对于使用IN (...)的表达式，MySQL并没有非常智能地使用索引。类似的漏洞存在于使用OR的布尔表达式的优化器中。

有关一些解释和详细的基准测试，请参阅http://www.mysqlperformanceblog.com/2006/08/10/using-union-to-implement-loose-index-scan-to-mysql/。

优化器一直在改进。MySQL的一个版本中的缺陷可能会在后续版本中得到改善。所以在不同的版本上测试你的查询是值得的。

使用UNION ALL而不是简单地使用UNION也是有利的。这两个查询都使用临时表来存储结果，但不同之处在于UNION将DISTINCT应用于结果集，这会产生额外的无索引排序。

Answer 2

如果您使用的是IN运算符，则与以下语句没有太大区别：

(p.name = 'Joe' OR p.name = 'Jane' OR p.name = 'Bob' OR p.name = 'Alice')

对于查询必须考虑的每一行，都必须检查这四个条件。当然，您引用的每个其他查询都只有一个条件。我不相信在大多数现实世界中，执行四个这样的查询会更快，因为您必须考虑客户端读取结果集并对其执行某些操作所花费的时间。在这种情况下，In看起来很不错；如果它可以使用索引，那就更好了。

Answer 3

像IN这样简单的查询应该不会出现优化器选择使用索引的问题。只有当您有更复杂的查询时，才需要Bill提到的UNION工作。这可能是指数统计数据的问题。

你对有问题的表格做了分析吗？

表中有多少行，有多少行与in子句匹配？

对于有问题的查询，解释说明了什么？