bitscn.com
mysql internals——in，or，between哪个更快？louis hust 0  前言 微博上看到@金山 提到了一个mysql中的一个性能问题，
 select id from table where id > 100 and id  
哪个更快？
 这里的查询条件有三种：between，or 和 in。这里id列是索引列，如果不是的话，三个查询都是全表扫描，性能差距应该不大。
 1  准备环境mysql> show create table tin/g*************************** 1. row ***************************       table: tincreate table: create table `tin` (  `c1` int(11) not null auto_increment,  `c2` varchar(256) default null,  primary key (`c1`)) engine=innodb auto_increment=5002 default charset=latin11 row in set (0.00 sec)mysql> show create procedure init_tin/g*************************** 1. row ***************************           procedure: init_tin            sql_mode: no_engine_substitution    create procedure: create definer=`root`@`127.0.0.1` procedure `init_tin`(cnt int)begindeclare i int default 0;repeatinsert into tin(c2) values(repeat('a', 100));set i:= i+1;until i > cntend repeat;endcharacter_set_client: utf8collation_connection: utf8_general_ci  database collation: latin1_swedish_ci1 row in set (0.00 sec)mysql> call init_tin(5000)/g
2  查看执行计划为了简单起见，这里并没有选择[100,200]这个区间进行查询，而是只选择了[100,104]这个区间。 查询语句为：
 select * from tin where c1 >= 100 and c1 select * from tin where c1 in (100, 101, 102, 103, 104);
select * from tin where c1 = 100 or c1 = 101 or c1 = 102 or c1 = 103 or c1 = 104;
 首先查看explain输出，会发现三个语句的explain输出是一样的：
*************************** 1. row ***************************           id: 1  select_type: simple        table: tin         type: rangepossible_keys: primary          key: primary      key_len: 4          ref: null         rows: 5     filtered: 100.00        extra: using where1 row in set, 1 warning (0.00 sec)
mysql5.6在information_schema中增加了optimizer_trace表，用于跟踪语句生成的执行计划的具体步骤，包含各种关键的优化步骤。 分别看下三种不同语句的执行代价：
1. select * from tin where c1 >=100 and c1  从上面可以看出执行代价最小的语句为select * from tin where c1 >= 100 and c1  
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内部的优化流程。可能单独测试的时候语句执行效率 差别不是很大。好了，收拾行李，明天回家，年前最后一篇。
file translated fromtexby tth,version 4.03.
on 7 feb 2013, 22:39.
bitscn.com