开发者学堂课程【MySQL 高级应用 - 索引和锁：为排序使用索引 OrderBy 优化】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/598/detail/8625
为排序使用索引 OrderBy 优化
目录：
一、OrderBy 关键字排序优化
二、优化策略
三、小总结
一、OrderBy 关键字排序优化
1、MySOL 支持二种方式的排序：FileSort 和 Index。Index 是 MySQL 扫描索引本身内部完成排序，所以效率会比较高。FileSort 方式效率较低，在 ORDER BY 查询中，尽量使用 Index 方式排序，避免使用 FileSort 方式排序。所以我们在使用 order by 的时候， 把 order by 查询结果由 :using filesort 优化到 using index。
根据之前学过的知识，在没有办法使用 Using Index 的时候 FileSort 可以接受。其他情况尽量使用 Index 方式排序。
现举例说明：
例1，建表：
CREATE TABLE tblA(
#id int primary key not null auto_increment,
Age INT,
Birth TIMESTAMP NOT NULL
);
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;
Mysql > SELECT * FROM tblA;
执行结果：
3 rows in set ( 0.00 sec)
执行完代码之后，可以看到数据库中已经产生这样的表。
建完表以后，看一下表里产生的 keys
注意：现在我们对索引的分析，不再是 WHERE 之后，ORDER BY 之前的字段。
例 a：输入代码：
mysql > explain SELECT * FROM tblA where age>20 order by age;
重点关注一下：看一下 ORDER BY 之后会不会产生 FileSort。
1 row in set (0.01 sec)
可以看到此次执行之后，并没有产生 Filesort。
例 b:输入代码：
mysql > EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY age, birth;
运行结果：
运行得到了跟例子 a 同样的结果。
例 c：
输入代码：mysql > explain SELECT * FROM tblA where age>20 order by birth;
运行结果：
1 row in set (0.00 sec)
此时产生了 Using FileSort。
简单总结：
例 a，例 b，例 c 不同的地方就是例a,例b查询的时候，order by 查询的第一个字段是 age ，而例 c 查询的时候第一个字段则是 birth。
所以，索引的作用就是排序跟查询，如果建立索引的时候的字段跟 order by 查询的字段顺序一样，那么就不会产生 Using filesort。
例 d：
输入代码：mysql > EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY birth,age;
运行结果：
可以看到，在例 d 中，order by 的顺序跟建立索引的顺序不同，此时产生了 Using Filesort 重新排序。
例 e ：输入代码：
Mysql > EXPLAIN SELECT * FROM tblA ORDER BY birth;
运行结果：
此时也使用了 Filesort。
例 f ，输入代码：
Mysql > EXPLAIN SELECT * FROM tblA WHERE birth > ‘2016-01-28 00:00:00’ ORDER BY birth;
运行结果：
此时，也使用了 Filesort。
例 g，输入代码：
Mysql > EXPLAIN SELECT * FROM tblA WHERE birth > ‘2016-01-28 00:00:00’ORDER BY age;
运行结果：
此时没有使用 Filesort。
例 h，输入代码：
Mysql > EXPLAIN SELECT * FROM tblA ORDER BY age ASC, birth DESC;
运行结果：
可以看到，尽管查询的顺序，查询的字段都是 age，birth，但是此次查询还是产生了 Filesort 。原因在于：ORDER BY 默认是升序，而此次查询 age 使用了 ASC，而 birth 使用了 DESC。此时，Index索引并没有使用上，所以 Mysql 只能使用 Filesort。
总结：
ORDER BY 满足两种情况，会使用 Index 排序：
ORDER BY 语句使用索引最左前列；
使用 Where 子句与 Order By 子句条件列组合满足索引最左前列。
尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀。
此时只能使用 SQL 数据库服务器的参数调优，这是数据库优化的最后一招。
如果不在索引列上，如：例 h，mysql 就要启动双路排序和单路排序。
双路排序：
Mysql4.1 之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 orderby 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值从列表中读取对应的数据输出。取一批数据，就要对磁盘进行两次扫描，众所周知，I\O 非常耗时；所以在 mysql4 之后，出现了第二种改进的算法，就是单路排序；
单路排序：
从磁盘读取查询需要的所有列，按照 orderby 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机 IO 变成了顺序 IO 但是它会使用更多的空间，因为它把每一行都保存在内存中了。
结论及引申出的问题：
由于单路是后出的，总体而言好过双路
但是用单路有问题：如果数据一次性能抓取完，就用单路排序，如果数据不能一次性抓取，就会出现多路排序，性能反而不如双路，就比较麻烦。例如：
在 sort_buffer 中，方法 B 比方法 A 要多占用很多空间，因为方法 B 是把所有字段都取出，所以有可能取出的数据的总大小超出了 sort_bufer 的容量，导致每次只能取 sort_buffer 容量大小的数据，进行排序(创建 tmp 文件，多路合并)，排完再取 sort buffer 容量大小，再排……从而导致了多次 I\O 。本来想省一次 I\O 操作，反而导致了大量的 I\O 操作，反而得不偿失。
二、优化策略
1、增大 sort_buffer_size 参数的设置
2、增大 max_length_for_sort_data 参数的设置
3、提高 Order By 的速度
1. Order by 时 select*是一个大忌只 Query 需要的字段,这点非常重要。在这里的影响是:
1.1当 Query 的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时,会用改进后的算法——单路排序,否则用老算法一一多路排序。
1.2两种算法的数据都有可能超出 sort buffer 的容量,超出之后,会创建 tmp 文件进行合并排序,导致多次 O ,但是用 单路排序算法的风险会更大一些所以要提高 sort_buffer_size 。
尝试提高 sort_buffer_ size
不管用哪种算法,提髙这个参数都会提高效率,当然,要根据系统的能力去提高,因为这个参数是针对每个进程的
3.尝试提高max_length_for_sort_data
提高这个参数,会增加用改进算法的概率。但是如果设的太高,数据总容量超出sort_buffer_size的概率就增大,明显症状是高的磁盘 ⅣO 活动和低的处理器使用率
三、索引跟 order by 的小总结为排序使用索引
Mysql 两种排序方式: 文件排序或扫描有序索引排序
Mysql 能为排序与查询使用相同的索引
KEY a_b_c(a, b, c)
order by 能使用索引最左前缀
-ORDER BY a
-ORDER BY a，b
-ORDERBY a，b，c
-ORDER BY a DESC，b DESC，c DESC
如果 WHERE 使用素引的最左前缀定义为常量，则 orderby 能使用素引
-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
不能使用索引进行排序
-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 /*
不是素引的一部分*
-WHERE a in (…) ORDER BY b, c /*
对于排序来说，多个相等条件也是范围查询*/
