本章介绍MySQL什么时候使用索引来进行order by操作，当不能使用索引时使用filesort操作，以及优化器提供的关于order by的执行计划信息。

带LIMIT和不带LIMIT的ORDER BY返回的记录的顺序可能不同。这点在章节8.2.1.19中介绍，“LIMIT查询优化”。

使用索引来满足ORDER BY
使用filesort来满足ORDER BY
影响ORDER BY优化
关于ORDER BY的执行计划信息

使用索引来满足ORDER BY #

在某些情形下，MySQL可以使用索引来满足order by字句，从而避免因执行filesort操作带来的额外排序。

即使ORDER BY和索引不完全匹配，也可能会用到索引，只要where条件中所有未使用的索引列和额外的order by列都是常量。如果索引中不包含查询用到的索引列，那么只有当索引访问比其它访问方法成本低的时候才会使用索引。

假设有一个索引(key_part1,key_part2)，下面这些查询可能会使用索引来解决order by字句。如果必须读取不在索引中的列，优化器是否会使用索引来解决order by取决于读索引是否比全表扫描更高效。

在这个查询中，(key_part1,key_part2)上的索引使得查询优化器可以避免排序：

select * from t1
    order by key_part1,key_part2;

但是，这个查询使用了select *，这可能会查询key_part1和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排序的。而且，如果where条件的选择性足够好的话，在(key_part1,key_part2)上的索引可能会使得索引范围扫描比全表扫描更高效，从而避免排序：

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，只有在索引同时包含相关的正序列和倒序列时，优化器才会使用索引：

select * from t1
    order by key_part1 desc, key_part2 asc;

如果在索引中key_part1是倒序的，key_part2是正序的，优化器可能会使用索引(key_part1,key_part2)。如果key_part1是正序的，key_part2是倒序的，也可以使用到索引（使用backard 扫描）。见*章节8.3.13，“Descending Indexes”。

下面的两个查询中，key_part1和常量比较。如果where字句的选择性足够好，索引范围扫描比全表扫描成本更低时，会使用索引扫描：

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常量值，索引仍然可以使用到索引：

select * from t1
    where key_part1 = constant1 and key_part2 > constant2
    order by key_part2;

在一些场景中，MySQL无法使用索引来解决orderby，但是仍然可能会使用索引来查找符合where条件的记录。例如：

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;

查询连接了很多表，order by中的列不是全部来自于用来获取记录的第一张非常量表。（就是EXPLAIN输出的第一张连接类型不是constant的表。）
查询有不同的order by和group by表达式。
索引中只有order by字句中的列的前缀。这种情形下，使用索引不能完全解决排序问题。例如，如果一个char(20)列只有钱10个字节在索引中，索引就不能包含第10个字节之后的列，会使用到filesort。
索引并不按照顺序存储记录。例如MEMORY表中的hash索引就是这样。

是否使用索引来解决排序问题可能会受列的别名的影响。假设t1.a列上有索引。在下面这个语句中，select中的列是a。它指向t1.a，和order by中a是同一列，索引可以使用到索引：

select a from t1 order by a;

在这个语句中select中的列也是a，但是是别名。它指向abs(a)，order by中是a，索引不会使用到t1.a上的索引：

select abs(a) as a from t1 order by a;

在下面的语句中，order by中的列不是select中的列。但是t1中有一个类是a，所以order by指向t1.a，所以可以使用到t1.a上的索引。（当然，结果的排序可能和按照abs(a)排序完全不同。）

select abs(a) as b from t1 order by a;

之前的版本中（MySQL5.7和更低的版本），group by在某些条件下会隐式排序。在MySQL8.0中，这种情况不会再发生。所以不需要再在语句后面添加 order by null来进行隐式排序。然而，查询结果可能和之前的MySQL版本不同。想要产生给定的排序，请提供order by字句。

使用filesort来满足ORDER BY #

如果不能使用索引来满足order by字句，MySQL会进行filesort操作，读取表记录，然后对其进行排序。filesort是查询执行过程中的一个额外排序阶段。

从MySQL8.0.12开始，为了获取filesort操作需要的内存，优化器会根据需要增量分配内存，直到达到系统变量sort_buffer_size的值。而不像之前的版本，提前分配大小为sort_buffer_size的内存。这使得用户可以调大sort_buffer_size值来加速大的排序操作而不用担心被小排序耗尽内存。（这点在多线程malloc比较若的windows上进行并发排序时可能不太明显。）

如果结果集太大，内存不足以容纳，filesort操作会使用临时磁盘文件。一些类型的查询可能特别适合全内存排序。例如，下面这种形式的查询（和子查询）中的order by操作，优化器可以使用filesort来在内存中进行高效排序，而不需要临时文件：

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活动。）。这种方案仅适用于MySQL8.0.20之前的版本，从8.0.20开始，max_length_for_sort_data被废弃，因为优化器的变更使其过时，没有效果。

想要加快order by ，检查你是否可以让MySQL使用索引而不是额外的排序。如果不能，尝试以下策略：

调大sort_buffer_size变量的值。理想情况下，这个值应该足够容纳整个结果集（从而避免写磁盘和merge操作）。
要考虑到sort buffer中存储的列的值的大小受max_sort_length系统变量的值的影响。例如，如果存储的元组中有长字符串字段，你增加了max_sort_length的值，sort buffer元组的值随之增大，这可能会需要你调大sort_buffer_size的值。
要监测merge阶段的数量（合并临时文件），检查Sort_merge_passes状态变量。
调大read_rnd_buffer_size变量的值，这样每次就可以读取更多记录。
修改系统变量tmpdir，将其指到具有大量空闲空间的专用文件系统。这个变量可以包含多个以round-robin方式使用的多个路径；你可以使用这个特性将压力分散到多个路径中。在unix系统上使用:来分隔这些系统，在Windows上使用;来分隔。这些路径应该使用不同物理磁盘上的目录，而不是同一磁盘上的不同分区。

关于ORDER BY的执行计划信息 #

利用EXPLAIN(见章节 8.8.1，“Optimizing Queries with EXPLAIN”)，你可以检查MySQL是否使用了索引来解决order by字句：

如果explain输出中的Extra列不包含Using filesort，表明用到了索引，没有进行filesort。
如果explain输出中的Extra列包含Using 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值，但不一定和sort_buffer_size值一样。在MySQL8.0.12之前，这个输出显示的是sort_buffer_size的值。（在8.0.12之前，优化器总是为sort buffer分配大小为sort_buffer_size的内存。从8.0.12开始，优化器增量分配内存，起始值很小，增量分配内存，最大值是sort_buffer_size。）

sort_mode值提供了关于sort buffer元组内容的信息：

<sort_key, rowid>：这表明sort buffer 元组是一对，包含排序列的值，和来源表记录的 row id。使用排序列来排序，使用row id来从表中查询数据。
<sort_key, additional_fields>：sort buffer元组包含排序列的值和查询需要的相关列。根据排序列来排序，然后直接从元组中读取数据。
<sort_key, packed_additional_fields»：和上一个相似，但是额外列被紧凑打包，而不是使用定长排列。

Explain不会分辨优化器是否是在内存中进行的filesort。可以从优化器的trace输出中查看是否使用了内存排序。查看filesort_priority_queue_optimization。想了解更多关于优化器trace的信息，请查看MySQL Internals： Tracing the Optimizer。