MYSQL OR vs IN性能 [英] MYSQL OR vs IN performance
问题描述
我想知道以下内容在性能方面是否有差异
I am wondering if there is any difference in regards to performance between the following
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?
or will MySQL optimize the SQL in the same way compilers will optimize code ?
由于注释中所述的原因,将AND
更改为OR
.
Changed the AND
's to OR
's for the reason stated in the comments.
推荐答案
我需要确定这一点,因此我对这两种方法进行了基准测试.我始终发现IN
比使用OR
快得多.
I needed to know this for sure, so I benchmarked both methods. I consistenly found IN
to be much faster than using OR
.
不要相信给出意见的人,科学就是测试和证据.
Do not believe people who give their "opinion", science is all about testing and evidence.
我运行了1000倍等效查询的循环(为了保持一致,我使用了sql_no_cache
):
I ran a loop of 1000x the equivalent queries (for consistency, I used sql_no_cache
):
IN
:2.34969592094s
IN
: 2.34969592094s
OR
:5.83781504631s
OR
: 5.83781504631s
更新:
(我没有6年前的原始测试源代码,尽管它返回的结果与此测试范围相同)
在请求一些示例代码进行测试时,这是最简单的用例.使用Eloquent简化语法,等效的原始SQL执行相同的操作.
In request for some sample code to test this, here is the simplest possible use case. Using Eloquent for syntax simplicity, raw SQL equivalent executes the same.
$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
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
1482080534.0185
1482080536.178
2.1595389842987
这篇关于MYSQL OR vs IN性能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!