mysql-为排序使用索引order by，group by

一.排序原则的纲领

原则.png

二.case

• case1

CREATE TABLE `tblA`( 
  `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,  
   `age` INT(11) DEFAULT NULL,   
   `birth` timestamp not NULL 
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
insert into tblA(age,birth) values (22,now());
insert into tblA(age,birth) values (23,now());
insert into tblA(age,birth) values (24,now());
 
create index idx_A_ageBirth on tblA(age,birth);
select * from tblA;

explain select * from tbla where age>20 order by age;
explain select * from tbla where age>20 order by age,birth;
explain select * from tbla where age>20 order by birth;
explain select * from tbla where age>20 order by birth,age;
explain select * from tbla order by birth;
explain select * from tbla where birth>'2018-10-05 12:00:00' order by birth;
explain select * from tbla where birth>'2018-10-05 12:00:00' order by age;
explain select * from tbla order by  age asc, birth desc;

分析.png

• order by使用索引，的关键就看，是否产生了Using filesort.

• mysql默认的排序是asc。而现在提交的birth是desc，所以mysql会在内部产生一个内部排序，再提交。所以，有Using filesort。

• MySQL支持二种方式的排序，FileSort和Index，Index效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

• case2
  index ---->a_b_c(a,b,c)
  1.order by 能使用索引最做前缀

- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC

• 2.如果WHERE使用索引的最左前缀定义为常量,则ORDER BY 能使用索引

-WHERE a = const  ORDER BY b,c
-WHERE a = const AND b = const ORDER BY c
-WHERE a = const  ORDER BY b,c
-WHERE a = const AND b > const ORDER BY b,c

• 3.不能使用索引排序

-ORDER BY a ASC,b DESC,c DESC /*排序不一致*/
-WHERE g = const ORDER BY b,c    /*丢失a索引*/
-WHERE a = const ORDER BY c     /*丢失b索引*/
-WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
-WHERE a in(...) ORDER BY b,c /*对于排序来说,多个相等的条件也是范围查询*/

二. 总结

order by.png

如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序

FileSort两种方式.png

针对这种情况，要避免多次io：

image.png
提高order by速度.png group by优化策略