最近笔者几篇文章介绍了改语句查询的文章. 关联文章的地址
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 TEX by TTH, version 4.03.
On 7 Feb 2013, 22:39.
文章结束给大家分享下程序员的一些笑话语录: 警告
有一个小伙子在一个办公大楼的门口抽着烟,一个妇女路过他身边,并对他 说, “你知道不知道这个东西会危害你的健康?我是说, 你有没有注意到香烟 盒上的那个警告(Warning)?”
小伙子说,“没事儿,我是一个程序员”。
那妇女说,“这又怎样?”
程序员说,“我们从来不关心 Warning,只关心 Error”