用户名:
密 码: 记住
您当前的位置:首页 > 网络编程 > mysql教程

mysql中IN,OR,BETWEEN性能比较

时间:2015-01-23  来源:西部数据  作者:西部数据

经常会有朋友问关于在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> show create table tinG 
  2. *************************** 1. row *************************** 
  3.        Table: tin 
  4. Create TableCREATE TABLE `tin` ( 
  5.   `c1` int(11) NOT NULL AUTO_INCREMENT, 
  6.   `c2` varchar(256) DEFAULT NULL
  7.   PRIMARY KEY (`c1`) 
  8. ) ENGINE=InnoDB AUTO_INCREMENT=5002 DEFAULT CHARSET=latin1 
  9. 1 row in set (0.00 sec) 
  10.  
  11. mysql> show create procedure init_tinG 
  12. *************************** 1. row *************************** 
  13.            Procedure: init_tin 
  14.             sql_mode: NO_ENGINE_SUBSTITUTION 
  15.     Create ProcedureCREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `init_tin`(cnt int
  16. begin 
  17. declare i int default 0; 
  18. repeat 
  19. insert into tin(c2) values(repeat('a', 100)); 
  20. set i:= i+1; 
  21. until i > cnt 
  22. end repeat; 
  23. end 
  24. character_set_client: utf8 
  25. collation_connection: utf8_general_ci 
  26.   Database Collation: latin1_swedish_ci 
  27. 1 row in set (0.00 sec) 
  28. --phpfensi.com 
  29. mysql> call init_tin(5000)G 

2,查看执行计划

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

  1. SELECT * FROM tin where c1 >= 100 and c1 <= 104; 
  2. SELECT * FROM tin where c1 in (100, 101, 102, 103, 104); 
  3. SELECT * FROM tin where c1 = 100 or c1 = 101 or c1 = 102 or c1 = 103 or c1 = 104; 

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

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

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

1.代码如下:

  1. SELECT * FROM tin where c1 >=100 and c1 <=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 * FROM tin where c1 in (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 * FROM tin where c1 = 100 or c1 = 101 or c1 = 102 or c1 = 103 or c1 =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内部的优化流程,可能单独测试的时候语句执行效率 差别不是很大。好了,收拾行李,明天回家,年前最后一篇。

来顶一下
返回首页
返回首页
推荐资讯
WiFi太不安全:7岁女孩11分钟内入侵公共网络 WiFi太不安全:7岁女孩11分钟内入侵近期刚刚发布研究说WiFi网络能获得人们手机里多少私人信息,
不服跑个分?人工智能也出现“刷分”乱象 不服跑个分?人工智能也出现“刷分2014年,人工智能领域突然爆发,成为了科研和科技创业的热门
相关文章
    无相关信息
栏目更新
栏目热门