MYSQL或vs IN性能

我想知道下列之间在性能方面是否有任何差异

SELECT ... FROM ... WHERE someFIELD IN(1,2,3,4)

SELECT ... FROM ... WHERE someFIELD between  0 AND 5

SELECT ... FROM ... WHERE someFIELD = 1 OR someFIELD = 2 OR someFIELD = 3 ... 

或者MySQL会像编译器优化代码一样优化SQL吗？

编辑:由于评论中陈述的原因,将's 更改为AND's OR.

1> Cyril..：

---

我肯定需要知道这一点,所以我对这两种方法进行了基准测试.我坚持认为IN比使用更快OR.

不要相信那些给出"意见"的人,科学就是测试和证据.

我运行了1000x等效查询的循环(为了保持一致性,我用过sql_no_cache):

IN:2.34969592094s

OR:5.83781504631s

更新:(
我没有原始测试的源代码,因为它是6年前,虽然它返回的结果与此测试的范围相同)

在请求一些示例代码来测试它时,这是最简单的用例.使用Eloquent简化语法,原始SQL等价物执行相同的操作.

$t = microtime(true); 
for($i=0; $i<10000; $i++):
$q = DB::table('users')->where('id',1)
    ->orWhere('id',2)
    ->orWhere('id',3)
    ->orWhere('id',4)
    ->orWhere('id',5)
    ->orWhere('id',6)
    ->orWhere('id',7)
    ->orWhere('id',8)
    ->orWhere('id',9)
    ->orWhere('id',10)
    ->orWhere('id',11)
    ->orWhere('id',12)
    ->orWhere('id',13)
    ->orWhere('id',14)
    ->orWhere('id',15)
    ->orWhere('id',16)
    ->orWhere('id',17)
    ->orWhere('id',18)
    ->orWhere('id',19)
    ->orWhere('id',20)->get();
endfor;
$t2 = microtime(true); 
echo $t."\n".$t2."\n".($t2-$t)."\n";

1482080514.3635
1482080517.3713
3.0078368186951

$t = microtime(true); 
for($i=0; $i<10000; $i++): 
$q = DB::table('users')->whereIn('id',[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20])->get(); 
endfor; 
$t2 = microtime(true); 
echo $t."\n".$t2."\n".($t2-$t)."\n";

1482080534.0185
1482080536.178
2.1595389842987

---

在这些测试中使用了哪些索引？

---

"不要相信那些给出"意见"的人"你是100%正确的,不幸的是Stack Overflow充满了他们

---

作为"_不相信给予他们"意见的人的推论"_":提供绩效数据而不包括用于获取这些数字的脚本,表格和索引使得它们无法验证.因此,这些数字与"意见"一样好.

---

性能原因(引用MariaDB(一个MySQL新的免费分支)docs):如果expr等于IN列表中的任何值,则返回1,否则返回0.如果所有值都是常量,则根据类型评估它们expr并排序.然后使用二分搜索完成对项目的搜索.这意味着如果IN值列表完全由常量"**"组成,则`**`IN非常快.否则,类型转换将根据类型转换中描述的规则进行,但应用于所有参数.=>**如果列是整数,则将整数传递给"IN"...**

---

我也在优化查询,发现`IN`语句比`OR`快约30%.

---

@eggyal，此例为主键。如jave.web所述，对于整数常量，IN将更快。我在答案中添加了一个简单的示例，显示了基准的基本循环。您可以使用更复杂的查询和/或非恒定索引来修改该测试，并查看得到的结果。对于大多数简单的情况，IN被证明是更快的。

2> Ergec..：

---

我也为未来的Google员工做过测试.返回结果的总数是10000中的7264

SELECT * FROM item WHERE id = 1 OR id = 2 ... id = 10000

此查询耗时0.1239数秒

SELECT * FROM item WHERE id IN (1,2,3,...10000)

此查询耗时0.0433数秒

IN 是快3倍 OR

---

是什么MySQL引擎,你是否清除了两个查询之间的MySQL缓冲区和操作系统文件缓存？

---

您的测试是一个狭窄的用例.查询返回72%的数据,不太可能从索引中受益.

3> beach..：

---

我认为BETWEEN会更快,因为它应该被转换成:

Field >= 0 AND Field <= 5

我的理解是无论如何IN都将被转换为一堆OR语句.IN的值是易用性.(节省必须多次键入每个列名并使其更容易与现有逻辑一起使用 - 您不必担心AND/OR优先级,因为IN是一个语句.使用一堆OR语句,您有确保用括号括起它们,以确保它们被评估为一个条件.)

您问题的唯一真正答案是查询您的查询.然后你就会知道在你的特殊情况下哪种方法最有效.

---

这个答案是正确的,之间转换为"1 <= film_id <= 5".另外两种解决方案没有折叠成单一范围的条件.我有一篇博文,在这里使用OPTIMIZER TRACE进行演示:http://www.tocker.ca/2015/05/25/optimizer-trace-and-explain-formatjson-in-5-7.html

4> Frank V..：

---

这取决于你在做什么; 范围有多宽,数据类型是什么(我知道你的例子使用数字数据类型,但你的问题也适用于很多不同的数据类型).

这是一个你想要双向编写查询的实例; 让它工作,然后使用EXPLAIN来找出执行差异.

我确信这个问题有一个具体的答案,但实际上,这就是我想出的问题.

这可能会有所帮助:http://forge.mysql.com/wiki/Top10SQLPerformanceTips

问候,
弗兰克

---

这应该是选定的答案.

---

链接陈旧 - 我认为这可能是等价的？https://wikis.oracle.com/pages/viewpage.action?pageId=27263381(感谢Oracle ;-P)

5> user658991..：

---

我认为sunseeker观察的一个解释是MySQL实际上对IN语句中的值进行排序,如果它们都是静态值并且使用二进制搜索,这比普通的OR替代更有效.我不记得我读过哪里,但是sunseeker的结果似乎是一个证据.

---

看起来像这个http://lists.mysql.com/mysql/216945这应该是接受的答案虽然

6> 小智..：

---

接受的答案没有说明原因。

下面引用了高性能MySQL第三版。

在许多数据库服务器中，IN（）只是多个OR子句的同义词，因为两者在逻辑上是等效的。在MySQL中不是这样，MySQL对IN（）列表中的值进行排序，并使用快速二进制搜索来查看列表中是否包含值。列表的大小为O（Log n），而等效的OR子句序列的列表的大小为O（n）（即，大列表的速度慢得多）