介绍
本节描述MySQL何时可以使用索引来满足ORDER BY子句，无法使用索引时使用的filesort操作，和优化器提供的有关ORDER BY的执行计划信息。
一个ORDER BY有和没有 LIMIT可能以不同的顺序返回行：
- 使用索引来优化ORDER BY
- 使用filesort来优化ORDER BY
- 影响ORDER BY优化
- 可用的执行计划信息收集
使用索引来优化ORDER BY
在某些情况下，MySQL可能会使用索引来优化一个 ORDER BY子句，并避免执行filesort 操作时涉及的额外排序。
即使索引与order by子句与索引不完全匹配一样可以使用索引，只要索引未被使用的部分和ORDER BY额外的列包含在WHERE条件里。假如索引不包含查询中的所有访问到的列，仅当索引访问比其他访问方法代价更低时才使用索引。
假设有一个索引(key_part1, key_part2),以下查询可能会使用索引解决ORDER BY的部分。如果还必须读取索引中不存在的列，优化程序是否实际执行此操作取决于读取索引是否比表扫描更有效。
- 在下面这个查询中，这个索引(key_part1, key_part2)启用优化程序，避免排序：
SELECT * FROM t1
ORDER BY key_part1, key_part2;然而，这个查询使用SELECT *, 他可能使用比(key_part1 and key_part2)更多的列。在这种情况下，扫描整个索引，然后再去获取全部数据，可能比获取全部数据然后在排序的代价更高。所以，这个优化器可能不适用索引。假如将SELECT * 变成只查询索引字段,查询将使用索引并且避免排序。
假如t1使用InnoDB引擎，这个表的主键字段是这个索引的隐式字段，所以可以通过索引优化ORDER BY：
SELECT pk, key_part1, key_part2 FROM t1
ORDER BY key_part1, key_part2;- 在下面这个查询中，key_part1是一个常量值，所以通过索引访问的所有行的key_part2是有序的，并且(key_part1, key_part2)避免了排序，因为where条件有足够的选择性来执行ndex range scan，所以使用索引的代价比table scan的代价小。
SELECT * FROM t1
WHERE key_part1 = constant
ORDER BY key_part2;- 在下面了两个查询中，是否使用索引类似于前面没有DESC的相同查询
SELECT * FROM t1
ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1
WHERE key_part1 = constant
ORDER BY key_part2 DESC;- ORDER BY后可以跟两个相同的描述符（全是ASC或者全是DESC），或者跟两个不同的描述符（一个ASC一个DESC），索引使用的条件是索引必须具有相同的同质性，但不一定要求有相同的方向。
假如一个查询混合了ASC和DESC，优化器可以使用一个和列相同的混合了ASC和DESC的索引：
SELECT * FROM t1
ORDER BY key_part1 DESC, key_part2 ASC;优化器可以在（key_part1，key_part2）上使用索引， 如果 key_part1是降序并且 key_part2正在升序。它还可以在这些列上使用索引（使用向后扫描），如果它key_part1是升序和key_part2降序。
- 在下面两个查询中，key_part1的条件是常量值，假如WHERE条件具有足够的选择性使用索引的范围扫描比全表扫描的代价更小，那么下面的SQL将使用索引
SELECT * FROM t1
WHERE key_part1 > constant
ORDER BY key_part1 ASC;
SELECT * FROM t1
WHERE key_part1 < constant
ORDER BY key_part1 DESC;- 在下面这个查询中，ORDER BY不包含key_part1，不过有key_part1 = constant1这个条件，所以仍然使用索引：
SELECT * FROM t1
WHERE key_part1 = constant1 AND key_part2 > constant2
ORDER BY key_part2;在某些情况下，MYSQL无法使用索引解决ORDER BY的问题，但是假如可以通过WHERE条件减少查询的数量，MYSQL依然会使用索引：
- 下面这个查询的ORDER BY 使用不同的索引：
SELECT * FROM t1 ORDER BY key1, key2;- 下面这个ORDER BY使用索引的不同部分：
SELECT * FROM t1 WHERE key2=constant ORDER BY key1_part1, key1_part3;- 用于获取行的索引与ORDER BY中使用的索引不同
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;- ORDER BY包含一个索引列之外的表达式：
SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;- 一个包含很多表的JOIN操作，ORDER BY中的列并非全部来自用于检索行的第一个非常量表。（在第一个表的EXPALIN输出中，不会包含const这个join类型）。
- 一个查询包含不同的ORDER BY和GROUP BY表达式。
- 在ORDER BY的条件中只有列的前缀包含索引。在这种情况下，索引不能完全解决排序问题。举例，在char(20)的字段中只有前10个有索引，索引无法区分超过10个字节的值，而且必须使用filesort。
- 索引可能不按照顺序存储行。比如，在MEMORY表中的HASH索引。
列的别名可能影响在排序时的索引的使用。假如t1.a被定义为索引，在这个声明中，select语句中的是a，他指代的是t1.a，和ORDER BY的引用一样，所以这个t1.a的索引可以使用。
SELECT a FROM t1 ORDER BY a;在这个声明中，查询的列仍然是a但是他是ABS(a)的别名，然后ORDER BY a的意思是ORDER BY ABS(a)，所以他无法使用索引：
SELECT ABS(a) AS a FROM t1 ORDER BY a;下面这个例子中，ORDER BY中依赖的字段并没有在SELECT中的别名不相同，所以他仍然可以使用索引：
SELECT ABS(a) AS b FROM t1 ORDER BY a;在以前的版本中，GROUP BY可能包含隐式排序，在MYSQL8.0中，这个已经不会再发生，因此，不再需要在末尾指定ORDER BY NULL来抑制隐式排序，但是现在的版本结果可能和以前版本不同，假如有需要请指定ORDER BY条件。
使用filesort优化ORDER BY
假如索引无法满足ORDER BY的条件，MySQL会使用filesort操作读取表中的行并进行排序。filesort是构成一个额外的阶段在所执行的查询中。
为了获得filesort操作的内存，从MySQL 8.0.12开始，优化器根据需要逐步分配内存缓冲区，最大为sort_buffer_size设置的大小。而不是像以前的版本中，提前分配 sort_buffer_size 的指定大小的固定的内存缓冲区。这个参数 sort_buffer_size 可以设置更大的值来跑更大的排序，而不用担心较小的排序占用较大的内存。（对于Windows上的多个并发排序，可能不会出现此优势，因为它具有较弱的多线程malloc。）
一个filesort可能需要申请临时的硬盘空间，假如排序的数据太大以至于内存无法装下的时候。有些类型的排序操作非常适用于内存中的filesort操作。举个例子，优化器可以在内存中有效的处理filesort操作，而不需要临时文件，以下形式的查询（和子查询）的ORDER BY操作：
SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;此类查询在Web应用程序中很常见，只显示较大结果集中的几行。例子：
SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;
SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;影响ORDER BY优化
对于一个没有使用filesort的比较慢的ORDER BY 查询，可以尝试降低max_length_for_sort_data的值，用来适当的触发filesort。（将这个值设置的过高的表现是过高的磁盘活动以及很低的CPU使用率。）
为了提高ORDER BY的速度，检查是否可以让MySQL使用索引而不是额外的排序阶段。假如无法使用索引，可以尝试以下方法：
- 提高 sort_buffer_size 的值。理想情况下应该使值足够大，让所有的排序操作在内存中的排序缓冲区中进行（避免写入磁盘和合并传递）。
- 考虑到存储在排序缓冲区中的列值的大小受max_sort_length系统变量值的影响。例如：如果元祖存储的长字符串的列的值，并且增加了max_sort_length，那么排序缓冲区的元组也需要增加，并且可能需要提高sort_buffer_size的值。
监控merge passes的数量（用于合并临时文件），并且检查Sort_merge_passes变量的值。 - 提高read_rnd_buffer_size的变量的值可以同时读取更多的行数。
- 将tmpdir系统变量更改为指向具有大量可用空间的专用文件系统。这个变量可以同时循环使用多个路径。您可以使用此功能将负载分散到多个目录中。在UNIX中使用(:)分割，在WINDOWS使用(;)分割。这个路径应该是本地多个不同的物理磁盘，而不是同一个磁盘的不同目录。
可用的执行计划信息收集
在EXPLAIN，可以判断ORDER BY是否使用了索引：
- 在EXPLAIN的Extra的输出中不包含 filesort ，那么索引被使用并且没有使用filesort。
- 在EXPLAIN的Extra的输出中包含 filesort ，那么索引未被使用并且使用了filesort。
此外，如果filesort被使用，优化器的trace输出包含filesort_summary部分。例如：
"filesort_summary": {
"rows": 100,
"examined_rows": 100,
"number_of_tmp_files": 0,
"peak_memory_used": 25192,
"sort_mode": "<sort_key, packed_additional_fields>"
}peak_memory_used表示排序期间任何一次使用的最大内存。这个值需要增加，但并不需要与sort_buffer_size一样大。在MySQL 8.0.12之前，输出显示sort_buffer_size，指示sort_buffer_size的值。（在MySQL 8.0.12之前，优化器总是为sort缓冲区分配sort_buffer_size字节。从8.0.12开始，优化器逐步分配排序缓冲区内存，从较少的值并且慢慢增加，最多为sort_buffer_size字节。）
sort_mode值提供有关排序缓冲区中元组内容的信息：
- <sort_key, rowid>: 这表示排序缓冲区元组是包含原始表行的排序的key值和行rowid。通过key排序然后通过rowid查询数据。
- <sort_key, additional_fields>:这表示排序缓冲区元组是包含原始表行的排序的key值和查询引用的列。元组按排序键值排序，列值直接从元组中读取。
- <sort_key, packed_additional_fields>: 与前一个版本一样，但是其他列紧密排列在一起，而不是使用固定长度编码。
EXPLAIN不区分优化器是否在内存中执行了filesort。
