mysql 索引使用测试（group by、order by）

---

mysql 索引使用测试（group by、order by）

 

 

**************************

测试表

 

字段：id、name、age、distance

 

插入10万条数据

drop procedure if exists hh;

delimiter //
create procedure hh()
begin
  declare i int default 1;
	declare count int default 100000;
	
	while i<=count do
	  insert 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();

 

 

**************************

group by 操作

 

age列上无索引：select age from test group by age;

      

说明：age列上没有索引，group by使用临时表

 

age列上建立单列索引：

select age from test group by age; 使用索引

select age,count(*) from test group by age; 使用索引

select age,max(distance) from test group by age; 使用索引

      

 

复合索引：name_age（name，age）

select age,count(*) from test group by age; group by使用索引、临时表

select age,max(distance) from test group by age; 不使用索引

select age,max(distance) from test where name="gtlx" group by age; 使用索引

select age,max(distance) from test where name="gtlx" and age>25 group by age; 使用索引

      

 

复合索引：age_name（age，name）

select age,count(*) from test group by age; 使用索引

select age,max(distance) from test group by age; 使用索引

      

 

 

**************************

oder by

 

order by可根据索引排序，如果不能使用索引排序，则要用filesort（在内存或者磁盘中排序）

 

无索引：select * from test order by age；使用文件排序

      

 

单列索引：age

select * from test order by age；返回数据量太大，不使用索引

select * from test where age>25 order by age；返回数据量太大，不使用索引

select * from test where age>28 order by age；使用索引

      

说明：如果匹配的数据量超过总表的1/3，则可能不会使用索引

 

复合索引：age_distance(age、distance)

select * from test order by age,distance; order by不使用索引排序，使用文件排序

select * from test where age>28 order by age,distance; order by使用索引排序

      

说明：若匹配的数据量过大，则不会使用索引

 

复合索引：age_distance(age、distance)

select * from test where age>28 order by age,distance; order by使用索引

select * from test where age>28 order by distance,age; order by不使用索引排序

select * from test where age>28 order by age desc,distance desc; order by使用索引

select * from test where age>28 order by age,distance desc; order by使用索引、文件排序

select * from test where age>28 order by age desc,distance; order by使用索引、文件排序

      

说明：order by要与索引建立的顺序一致，否则不使用索引

 

      

说明：order by使用反向索引输出返回结果

 

      

说明：age升序、distance降序与索引顺序不一致，同时使用索引、文件排序

 

      

说明：age、distance排序的方向需要一致，否则除使用索引排序外，还会使用文件排序