mysql中IN,OR,BETWEEN性能比较

互联网十八般武艺 互联网十八般武艺

经常会有朋友问关于在mysql中IN,OR,BETWEEN那个性能更好,我想有很多朋友会用它们三但确实不知道IN,OR,BETWEEN性能那个更好吧,下面我来总结一下关于IN,OR,BETWEEN性能.

微博上看到@金山 提到了一个MySQL中的一个性能问题,代码如下:

select id from table where id > 100 and id < 200 和 select id from table where id = 101 or id = 103 or id = 104 or id = 105 or id = ...

哪个更快？

这里的查询条件有三种:between,or 和 in,这里id列是索引列,如果不是的话,三个查询都是全表扫描,性能差距应该不大.

1,准备环境,代码如下:

  1. mysql>showcreatetabletinG
  2. ***************************1.row***************************
  3. Table:tin
  4. CreateTable:CREATETABLE`tin`(
  5. `c1`int(11)NOTNULLAUTO_INCREMENT,
  6. `c2`varchar(256)DEFAULTNULL,
  7. PRIMARYKEY(`c1`)
  8. )ENGINE=InnoDBAUTO_INCREMENT=5002DEFAULTCHARSET=latin1
  9. 1rowinset(0.00sec)
  11. mysql>showcreateprocedureinit_tinG
  12. ***************************1.row***************************
  13. Procedure:init_tin
  14. sql_mode:NO_ENGINE_SUBSTITUTION
  15. CreateProcedure:CREATEDEFINER=`root`@`127.0.0.1`PROCEDURE`init_tin`(cntint)
  16. begin
  17. declareiintdefault0;
  18. repeat
  19. insertintotin(c2)values(repeat('a',100));
  20. seti:=i+1;
  21. untili>cnt
  22. endrepeat;
  23. end
  24. character_set_client:utf8
  25. collation_connection:utf8_general_ci
  26. DatabaseCollation:latin1_swedish_ci
  27. 1rowinset(0.00sec)
  28. --phpfensi.com
  29. mysql>callinit_tin(5000)G

2,查看执行计划

为了简单起见,这里并没有选择[100,200]这个区间进行查询,而是只选择了[100,104]这个区间,查询语句为:

  1. SELECT*FROMtinwherec1>=100andc1<=104;
  2. SELECT*FROMtinwherec1in(100,101,102,103,104);
  3. SELECT*FROMtinwherec1=100orc1=101orc1=102orc1=103orc1=104;

首先查看explain输出,会发现三个语句的explain输出是一样的,代码如下:

  1. ***************************1.row***************************
  2. id:1
  3. select_type:SIMPLE
  4. table:tin
  5. type:range
  6. possible_keys:PRIMARY
  7. key:PRIMARY
  8. key_len:4
  9. ref:NULL
  10. rows:5
  11. filtered:100.00
  12. Extra:Usingwhere
  13. 1rowinset,1warning(0.00sec)

MySQL5.6在information_schema中增加了optimizer_trace表,用于跟踪语句生成的执行计划的具体步骤,包含各种关键的优化步骤,分别看下三种不同语句的执行代价:

1.代码如下:

  1. SELECT*FROMtinwherec1>=100andc1<=104;
  2. "chosen_range_access_summary":{
  3. "range_access_plan":{
  4. "type":"range_scan",
  5. "index":"PRIMARY",
  6. "rows":5,
  7. "ranges":[
  8. "100<=c1<=104"
  9. ]
  10. },
  11. "rows_for_plan":5,
  12. "cost_for_plan":2.0188,
  13. "chosen":true
  14. }

2.代码如下:

  1. SELECT*FROMtinwherec1in(100,101,102,103,104);
  2. "chosen_range_access_summary":{
  3. "range_access_plan":{
  4. "type":"range_scan",
  5. "index":"PRIMARY",
  6. "rows":5,
  7. "ranges":[
  8. "100<=c1<=100",
  9. "101<=c1<=101",
  10. "102<=c1<=102",
  11. "103<=c1<=103",
  12. "104<=c1<=104"
  13. ]
  14. },
  15. "rows_for_plan":5,
  16. "cost_for_plan":6.0188,
  17. "chosen":true
  18. }

3.代码如下:

  1. SELECT*FROMtinwherec1=100orc1=101orc1=102orc1=103orc1=104;
  2. "chosen_range_access_summary":{
  3. "range_access_plan":{
  4. "type":"range_scan",
  5. "index":"PRIMARY",
  6. "rows":5,
  7. "ranges":[
  8. "100<=c1<=100",
  9. "101<=c1<=101",
  10. "102<=c1<=102",
  11. "103<=c1<=103",
  12. "104<=c1<=104"
  13. ]
  14. },
  15. "rows_for_plan":5,
  16. "cost_for_plan":6.0188,
  17. "chosen":true
  18. }

从上面可以看出执行代价最小的语句为SELECT * FROM tin WHERE c1 >= 100 and c1 <=104,代价为2.0118，其他两个计划的代价 为6.0118.

3,计划分析

看了上面的代价结果,是不是就理所当然的任务第一个语句的代价真的是最小呢？这就需要知道MySQL代价计算的方法,一个计划的代价体现在硬件上就是I/O+CPU,I/O就是将所需的物理页载入内存的时间,CPU则是数据计算所消耗的时间,有些语句是I/O密集的,有些语句是CPU运算密集的.

为什么MySQL计算出来的代价会差别这么大呢？ MySQL在计算上面三个语句的代价时，I/O代价的计算是由range的个数n_ranges和最终的结果集的行数total_rows得出来的， 语句1的n_ranges=1，语句2和语句3的n_ranges=5，totol_rows都为5，故语句1的在I/O上的代价明显小于语句2和语句3（具体的函数 参见ha_innobase::read_time）。至于CPU的代价，由于返回的行数一致，故CPU的代价一致，CPU的代价主要体现在获取数据后，进行WHERE 条件的匹配操作。

这只是MySQL的对于上面三个语句的代价模型，而实际上，上面三个语句所进行的I/O操作其实是一致的，因为数据范围是一样的。所以，仅凭 MySQL给出的代价结果还是不能立刻判断出语句1就肯定好。

既然I/O操作的代价可以考虑是一致的，那么只能来看三条语句执行时的区别了。语句2和语句3的range个数都为5个，而且range的范围都是一致的， 这其实是MySQL的优化结果，IN和OR都被优化成了相同的结果。只有语句1只有1个range。MySQL执行时是遍历每个range，而每个range遍历时其实 是两种操作，read_first和read_next，read_first是根据每个range的start key定位到相应的位置，read_next则是根据上次BTREE读到的位置,继续往后读，read_next是以end key为结束。

对于语句1,只有一个range,故需要1次read_first和5次read_next（最后一次read_next不符合end_key，返回结束）,对于语句2和语句3,有5个range,每个range需要1此read_first和一次read_next,总共需要5此read_first和5次read_next。从数据获取的次数来看,语句2和语句3基本是语句1的调用次数的两倍.

除了获取数据调用次数的区别外,在获取数据之后,还需要进行数据合法性的验证，即匹配WHERE条件,对于语句1的WHERE条件十分简单,匹配 上下界限即可，即对于每返回的一行数据需要两次验证,时间复杂度为常量O（2）,而对于语句2和语句3，则需要对IN或OR中的每个条件进行验证,知道找到某一匹配项为止,时间复杂度为O（n）。 但是MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的,故匹配的时候是二分查找,时间复杂度为O（lgn）.

在忽略I/O的情况下,仅仅从CPU的耗时来看,语句1应该是最少的,其次是IN,最差的就是OR了.

先就分析到这吧,具体的执行时间的数据我就不测试了,主要是想通过测试了解MySQL内部的优化流程,可能单独测试的时候语句执行效率 差别不是很大。好了，收拾行李，明天回家,年前最后一篇。

相关广告
  • mysql中IN,OR,BETWEEN性能比较 mysql中IN,OR,BETWEEN性能比较 mysql中IN,OR,BETWEEN性能比较
相关阅读

mysql中IN,OR,BETWEEN性能比较

2019/10/10 17:34:23 | 谷歌SEO算法 | SEO