语句查询MySQL Internals——IN，OR，BETWEEN哪个更快？ 语句查询

最近笔者几篇文章介绍了改语句查询的文章. 关联文章的地址

    

MySQL Internals——IN，OR，BETWEEN哪个更快？

    

Louis Hust

    

 

    

0  言前

    微博上看到@金山 提到了一个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  预备环境

    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 <= 104;
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 <=104;"chosen_range_access_summary": {                    "range_access_plan": {                      "type": "range_scan",                      "index": "PRIMARY",                      "rows": 5,                      "ranges": [                        "100 <= c1 <= 104"                      ]                    },                    "rows_for_plan": 5,                    "cost_for_plan": 2.0188,                    "chosen": true                    }2. SELECT * FROM tin where c1 in (100, 101, 102, 103, 104);"chosen_range_access_summary": {                    "range_access_plan": {                      "type": "range_scan",                      "index": "PRIMARY",                      "rows": 5,                      "ranges": [                        "100 <= c1 <= 100",                        "101 <= c1 <= 101",                        "102 <= c1 <= 102",                        "103 <= c1 <= 103",                        "104 <= c1 <= 104"                      ]                    },                    "rows_for_plan": 5,                    "cost_for_plan": 6.0188,                    "chosen": true                  }3. SELECT * FROM tin where c1 = 100 or c1 = 101 or c1 = 102 or c1 = 103 or c1 =104;"chosen_range_access_summary": {                    "range_access_plan": {                      "type": "range_scan",                      "index": "PRIMARY",                      "rows": 5,                      "ranges": [                        "100 <= c1 <= 100",                        "101 <= c1 <= 101",                        "102 <= c1 <= 102",                        "103 <= c1 <= 103",                        "104 <= c1 <= 104"                      ]                    },                    "rows_for_plan": 5,                    "cost_for_plan": 6.0188,                    "chosen": true                  }

    

 

    从面上可以看出执行价值最小的语句为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内部的化优流程。可能单独测试的时候语句执行效率 差异不是很大。好了，收拾行李，明天回家，年前最后一篇。

    

---

    File translated from T_EX by T_TH, version 4.03.
On 7 Feb 2013, 22:39.

文章结束给大家分享下程序员的一些笑话语录： 警告
有一个小伙子在一个办公大楼的门口抽着烟，一个妇女路过他身边，并对他 说， “你知道不知道这个东西会危害你的健康？我是说， 你有没有注意到香烟 盒上的那个警告（Warning）？”
小伙子说，“没事儿，我是一个程序员”。
那妇女说，“这又怎样？”
程序员说，“我们从来不关心 Warning，只关心 Error”