Mysql为排序使用索引

Mysql两种排序方式:文件排序(filesort)或扫描有序索引排序(index)
Mysql能为排序与查询使用相同的索引

• KEY 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 /*对于排序来说,多个相等的条件也是范围查询*/