简单谈谈MySQL的loose index scan
众所周知,InnoDB采用IOT(indexorganizationtable)即所谓的索引组织表,而叶子节点也就存放了所有的数据,这就意味着,数据总是按照某种顺序存储的。所以问题来了,如果是这样一个语句,执行起来应该是怎么样的呢?语句如下:
selectcount(distincta)fromtable1;
列a上有一个索引,那么按照简单的想法来讲,如何扫描呢?很简单,一条一条的扫描,这样一来,其实做了一次索引全扫描,效率很差。这种扫描方式会扫描到很多很多的重复的索引,这样说的话优化的办法也是很容易想到的:跳过重复的索引就可以了。于是网上能搜到这样的一个优化的办法:
selectcount(*)from(selectdistinctafromtable1)t;
从已经搜索到的资料看,这样的执行计划中的extra就从usingindex变成了usingindexforgroup-by。
但是,但是,但是,好在我们现在已经没有使用5.1的版本了,大家基本上都是5.5以上了,这些现代版本,已经实现了looseindexscan:
很好很好,就不需要再用这种奇技淫巧去优化SQL了。
文档里关于groupby这里写的有点意思,说是最大众化的办法就是进行全表扫描并且创建一个临时表,这样执行计划就会难看的要命了,肯定有ALL和usingtemporarytable了。
5.0之后groupby在特定条件下可能使用到looseindexscan,
CREATETABLElog_table( idINTNOTNULLPRIMARYKEY, log_machineVARCHAR(20)NOTNULL, log_timeDATETIMENOTNULL )ENGINE=InnoDBDEFAULTCHARSET=utf8; CREATEINDEXix_log_machine_timeONlog_table(log_machine,log_time);
1
SELECTMAX(log_time)FROMlog_table; SELECTMAX(log_time)FROMlog_tableWHERElog_machineIN('Machine1');
这两条sql都只需一次indexseek便可返回,源于索引的有序排序,优化器意识到min/max位于最左/右块,从而避免范围扫描;
extra显示Selecttablesoptimizedaway;
2
SELECTMAX(log_time)FROMlog_tableWHERElog_machineIN(‘Machine1','Machine2','Machine3','Machine4');
执行计划type为range(extra显示usingwhere;usingindex),即执行索引范围扫描,先读取所有满足log_machine约束的记录,然后对其遍历找出maxvalue;
改进
SELECTMAX(log_time)FROMlog_tableWHERElog_machineIN(‘Machine1','Machine2','Machine3','Machine4') groupbylog_machineorderby1desclimit1;
这满足groupby选择looseindexscan的要求,执行计划的extra显示usingindexforgroup-by,执行效果等值于
SELECTMAX(log_time)FROMlog_tableWHERElog_machineIN(‘Machine1') Union SELECTMAX(log_time)FROMlog_tableWHERElog_machineIN(‘Machine2') …..
即对每个log_machine执行looseindexscan,rows从原来的82636下降为16(该表总共1,000,000条记录)。
Groupby何时使用looseindexscan?
适用条件:
1 针对单表操作
2 Groupby使用索引的最左前缀列
3 只支持聚集函数min()/max()
4 Where条件出现的列必须为=constant操作,没出现在groupby中的索引列必须使用constant
5 不支持前缀索引,即部分列索引,如index(c1(10))
执行计划的extra应该显示usingindexforgroup-by
假定表t1有个索引idx(c1,c2,c3)
SELECTc1,c2FROMt1GROUPBYc1,c2; SELECTDISTINCTc1,c2FROMt1; SELECTc1,MIN(c2)FROMt1GROUPBYc1; SELECTc1,c2FROMt1WHEREc1<constGROUPBYc1,c2; SELECTMAX(c3),MIN(c3),c1,c2FROMt1WHEREc2>constGROUPBYc1,c2; SELECTc2FROMt1WHEREc1<constGROUPBYc1,c2; SELECTc1,c2FROMt1WHEREc3=constGROUPBYc1,c2 SELECTc1,c3FROMt1GROUPBYc1,c2;--无法使用松散索引
而SELECTc1,c3FROMt1 wherec3=constGROUPBYc1,c2;则可以
紧凑索引扫描tightindexscan
Groupby在无法使用looseindexscan,还可以选择tight,若两者都不可选,则只能借助临时表;
扫描索引时,须读取所有满足条件的索引键,要么是全索引扫描,要么是范围索引扫描;
Groupby的索引列不连续;或者不是从最左前缀开始,但是where条件里出现最左列;
SELECTc1,c2,c3FROMt1WHEREc2='a'GROUPBYc1,c3; SELECTc1,c2,c3FROMt1WHEREc1='a'GROUPBYc2,c3;
5.6的改进
事实上,5.6的indexconditionpushdown可以弥补looseindexscan缺失带来的性能损失。
KEY(age,zip)
mysql>explainSELECTnameFROMpeopleWHEREageBETWEEN18AND20ANDzipIN(12345,12346,12347); +----+-------------+--------+-------+---------------+------+---------+------+-------+-------------+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +----+-------------+--------+-------+---------------+------+---------+------+-------+-------------+ |1|SIMPLE|people|range|age|age|4|NULL|90556|Usingwhere| +----+-------------+--------+-------+---------------+------+---------+------+-------+-------------+ 1rowinset(0.01sec)
根据key_len=4可以推测出sql只用到索引的第一列,即先通过索引查出满足age(18,20)的行记录,然后从server层筛选出满足zip约束的行;
pre-5.6,对于复合索引,只有当引导列使用"="时才有机会在索引扫描时使用到后面的索引列。
mysql>explainSELECTnameFROMpeopleWHEREage=18ANDzipIN(12345,12346,12347); +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ |id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra| +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ |1|SIMPLE|people|range|age|age|8|NULL|3|Usingwhere| +----+-------------+--------+-------+---------------+------+---------+------+------+-------------+ 1rowinset(0.00sec)
对比一下查询效率
mysql>SELECTsql_no_cachenameFROMpeopleWHEREage=19ANDzipIN(12345,12346,12347); +----------------------------------+ |name| +----------------------------------+ |888ba838661aff00bbbce114a2a22423| +----------------------------------+ 1rowinset(0.06sec) mysql>SELECTSQL_NO_CACHEnameFROMpeopleWHEREageBETWEEN18AND22ANDzipIN(12345,12346,12347); +----------------------------------+ |name| +----------------------------------+ |ed4481336eb9adca222fd404fa15658e| |888ba838661aff00bbbce114a2a22423| +----------------------------------+ 2rowsinset(1min56.09sec)
对于第二条sql,可以使用union改写,
mysql>SELECTnameFROMpeopleWHEREage=18ANDzipIN(12345,12346,12347) ->UNIONALL ->SELECTnameFROMpeopleWHEREage=19ANDzipIN(12345,12346,12347) ->UNIONALL ->SELECTnameFROMpeopleWHEREage=20ANDzipIN(12345,12346,12347) ->UNIONALL ->SELECTnameFROMpeopleWHEREage=21ANDzipIN(12345,12346,12347) ->UNIONALL ->SELECTnameFROMpeopleWHEREage=22ANDzipIN(12345,12346,12347);
而mysql5.6引入了indexconditionpushdown,从优化器层面解决了此类问题。