IN和OR在SQL WHERE子句中 [英] IN vs OR in the SQL WHERE Clause

问题描述

在处理SQL数据库中的大数据库时， IN 或或 >

h2_lin>解决方案

我假设你想知道以下的性能差异：

  WHERE foo IN（'a'，'b'，'c'）
 WHERE foo ='a'OR foo ='b'OR foo ='c'
  

根据 MySQL的手册如果值是常数 IN 排序列表，然后使用二进制搜索。我想象 OR 按照特定顺序逐个评估它们。因此在某些情况下 IN 更快。

最好的方法是使用您的特定数据对数据库进行配置，以了解更快。

我试过在一个MySQL与1000000行。当列被索引时，在性能上没有可辨别的差异 - 两者几乎是即时的。当列没有索引时，我得到这些结果：

  SELECT COUNT（*）FROM t_inner WHERE val IN（1000， 3000，4000，5000，6000，7000，8000，9000）; 
在0.0032（1.2679秒）中获取的1行
 
 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; 
在0.0026（1.7385秒）中获取的1行
  

是慢约30％。添加更多术语会使得差异变大。结果可能因其他数据库和其他数据而异。

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'

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.

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)

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.

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