mysql索引使用测试(groupby、orderby)
**************************
测试表
字段:id、name、age、distance
插入10万条数据
drop procedure if exists hh;delimiter //create procedure hh()begindeclare i int default 1;declare count int default 100000;while i<=count doinsert into test(id,name,age,distance) values(i,concat('gtlx ',i),(i%20)+10,round(rand()*1000)+100);set i=i+1;end while;end //delimiter ;call hh();
**************************
groupby 操作
age列上无索引:selectagefromtestgroupby age;
说明:age列上没有索引,groupby使用临时表
age列上建立单列索引:
selectagefromtestgroupby age;使用索引
selectage,count(*) from test group by age;使用索引
select age,max(distance) from test group by age;使用索引
复合索引:name_age(name,age)
selectage,count(*) from test group by age;groupby使用索引、临时表
select age,max(distance) from test group by age;不使用索引
select age,max(distance) from testwherename="gtlx"group by age;使用索引
select age,max(distance) from testwherename="gtlx"andage>25group by age;使用索引
复合索引:age_name(age,name)
selectage,count(*) from test group by age;使用索引
selectage,max(distance) from test group by age;使用索引
**************************
oderby
orderby可根据索引排序,如果不能使用索引排序,则要用filesort(在内存或者磁盘中排序)
无索引:select *fromtestorderbyage;使用文件排序
单列索引:age
select *fromtestorderbyage;返回数据量太大,不使用索引
select *fromtestwhereage>25 order by age;返回数据量太大,不使用索引
select * from test where age>28 order by age;使用索引
说明:如果匹配的数据量超过总表的1/3,则可能不会使用索引
复合索引:age_distance(age、distance)
select *fromtestorderbyage,distance;order by不使用索引排序,使用文件排序
select * from test where age>28 order by age,distance;orderby使用索引排序
说明:若匹配的数据量过大,则不会使用索引
复合索引:age_distance(age、distance)
select * from test where age>28 order by age,distance;orderby使用索引
select *fromtestwhereage>28 order by distance,age;order by不使用索引排序
select * from test where age>28 order by agedesc,distancedesc;orderby使用索引
select * from test where age>28 order by age,distancedesc;orderby使用索引、文件排序
select * from test where age>28 order by agedesc,distance;orderby使用索引、文件排序
说明:order by要与索引建立的顺序一致,否则不使用索引
说明:orderby使用反向索引输出返回结果
说明:age升序、distance降序与索引顺序不一致,同时使用索引、文件排序
说明:age、distance排序的方向需要一致,否则除使用索引排序外,还会使用文件排序