SQL WHERE 子句中的 IN 与 OR [英] IN vs OR in the SQL WHERE Clause

问题描述

在处理大型数据库时，哪个性能更好，SQLWhere子句中的IN或OR?

When dealing with big databases, which performs better, IN or OR in the SQL Where-clause?

它们的执行方式有什么不同吗?

Is there any difference about the way they are executed?

推荐答案

我假设您想知道以下内容之间的性能差异:

I assume you want to know the performance difference between the following:

WHERE foo IN ('a', 'b', 'c')
WHERE foo = 'a' OR foo = 'b' OR foo = 'c'

根据 MySQL 手册如果值是常量 IN 对列表进行排序，然后使用二进制搜索.我会想象 OR 没有特定的顺序一个一个地评估它们.所以 IN 在某些情况下更快.

According to the manual for MySQL if the values are constant IN sorts the list and then uses a binary search. I would imagine that OR evaluates them one by one in no particular order. So IN is faster in some circumstances.

最好的了解方法是使用您的特定数据在您的数据库中对两者进行分析，以查看哪个更快.

The best way to know is to profile both on your database with your specific data to see which is faster.

我在一个有 1000000 行的 MySQL 上都试过.当该列被索引时，性能没有明显差异 - 两者几乎是即时的.当该列未编入索引时，我得到了以下结果:

I tried both on a MySQL with 1000000 rows. When the column is indexed there is no discernable difference in performance - both are nearly instant. When the column is not indexed I got these results:

SELECT COUNT(*) FROM t_inner WHERE val IN (1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000);
1 row fetched in 0.0032 (1.2679 seconds)

SELECT COUNT(*) FROM t_inner WHERE val = 1000 OR val = 2000 OR val = 3000 OR val = 4000 OR val = 5000 OR val = 6000 OR val = 7000 OR val = 8000 OR val = 9000;
1 row fetched in 0.0026 (1.7385 seconds)

所以在这种情况下，使用 OR 的方法慢了大约 30%.添加更多项会使差异更大.结果可能因其他数据库和其他数据而异.

So in this case the method using OR is about 30% slower. Adding more terms makes the difference larger. Results may vary on other databases and on other data.

这篇关于SQL WHERE 子句中的 IN 与 OR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！