MySQL8中文手册-Order by的优化

写在前面

网上充斥着各种MySQL的学习资料，有所谓的小白学习笔记也有大牛的分享文章，有免费的也有收费的，文章的质量也是鱼龙混杂，经常看到文章与文章之间相互矛盾，甚至文章前后矛盾。
至于为什么要翻译官方文档，还要源于上次学习极客时间的付费文章，其中一篇对change buffer的说明，文章中描述change buffer是用于更新数据缓存，后面又对change buffer和redo log的关系进行了说明，然而评论中有各种观点，有些说change buffer只针对索引数据进行缓存，文章前后翻了好几遍，始终有个疑问，change buffer到底缓存的是什么内容？在度娘上查找，也是各种说法都有。
为了彻底弄明白这个问题，我去查找了MySQL8的官方英文文档，发现文档中描述非常清楚，change buffer的缓存对象是非唯一二级索引，缓存的是二级索引的变更操作（insert、update、delete），前面的一切疑问都明白了。
经过这件事后，我开始翻译MySQL8的使用手册，这才是最权威的最值得参考的资料，翻译过程参考了百度和谷歌翻译，但大部分还是人工翻译，因此难免有些理解偏差，请大家及时指正。

更多内容请点击MySQL8中文手册

8.2.1.16 ORDER BY的优化

本节内容说明MySQL查询语句需要ORDER BY排序时，在什么情况下会使用索引进行排序，以及在不能使用索引的情况下如何使用filesort排序，以及由优化器提供的执行计划中有关ORDER BY的信息。

需要ORDER BY排序的查询中，使用和不使用LIMIT返回的数据顺序可能会不同，在Section 8.2.1.19, “LIMIT Query Optimization”.已经讨论过。

• 使用索引满足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排序。优化器是否会选择使用索引，取决于使用索引查找，是否比检索没有在索引中的其他列而进行的全表扫描更加有效率。

• 在下面的查询中，优化器会使用（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是降序的，同样也会使用。

• 在下面的两个查询语句中，查找条件时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;

• 在下面的查询中，虽然不以key_part1排序，但是所有要查询的行key_part1值都为常量，所以也可以使用索引满足。

SELECT * FROM t1
  WHERE key_part1 = constant1 AND key_part2 > constant2
  ORDER BY key_part2;

在某些情况下，虽然MySQL会使用索引来匹配WHERE查询的行，但是不会使用索引来满足ORDER BY排序。例如

• 查询语句的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字段不全是第一个表用来检索行数据的字段。

• 查询语句使用了不同的ORDER BY和GROUP BY表达式。

• ORDER BY的列使用的是前缀作为索引。在这种情况下，不能使用索引来满足完整的排序。例如，CHAR(20)的字段使用前面10个字符作为索引，索引不能区别后面的10个字符，后面10个字符也是需要进行排序的。

• 索引中的行没有顺序，比如内存表中的HASH索引。

在排序中能否使用上索引可能会被列别名影响。假如t1.a加了索引，在这个语句中，在查询列表中使用的列名是a，引用的是t1.a，与在ORDER BY中的a一样，所以t1.a上的索引可以使用。

SELECT a FROM t1 ORDER BY a;

列别名可能会影响在排序中能否使用索引来满足。假如t1.a加了索引，在这个语句中，查询的列名是a，引用的是t1.a，与在ORDER BY中的a一样，所以t1.a上的索引可以满足。

SELECT ABS(a) AS a FROM t1 ORDER BY a;

在下面的这个语句中，ORDER BY的列名不是查询列表中的字段。但是表t1中有一个字段叫做a，所以ORDER BY中的a指向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来防止进行隐式排序。

使用文件排序来进行ORDER BY

如果不能使用索引来满足ORDER BY排序，MySQL会使用filesort操作来读取行数据并进行排序。在查询语句执行过程中，filesort算是一种额外的排序阶段。

在MySQL8.0.12中，优化器会逐步分配内存空间来满足filesort排序需要的内存，最大值通过变量sort_buffer_size来指定，而不是直接分配sort_buffer_size的内存空间，8.0.12之前版本是采用的这种策略。

filesort在查询返回数据过大无法完全放到内存中时，会使用临时磁盘文件进行排序。有些类型的查询特别适合完全在内存中使用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版本前有效，后面的版本max_length_for_sort_data参数已被禁用，因为优化器进行了调整，让这个参数不起作用而被废弃。

为了加快ORDER BY的速度，最好能让MySQL使用索引来排序，而不需要进行额外的排序步骤。如果做不到，可以尝试以下方法：

• 加大sort_buffer_size参数值，理想情况下，这个值最好足够大，让整个查询返回结果都放到sort buffer 中。

也要考虑max_sort_length参数会影响缓存到sort buffer中列数据的多少。例如，为了排序时保存长字符串列，你需要增加max_sort_length参数，同时sort buffer的大小也要增加，你需要调整sort_buffer_size参数。

可以通过查看Sort_merge_passes状态值来监控file sort排序传给临时文件的文件数。

• 调高read_rnd_buffer_size参数，可以一次多读入几行数据。

• 修改tmpdir参数，为临时文件夹分配更大的磁盘空间。可以同时配置多个路径循环访问，实现多个文件夹访问的负载均衡。Linux系统中使用‘：’来分割，Windows系统中使用‘；’来分割。不同的路径应该在不同的磁盘上，而不是在同一磁盘的不同分区上。

执行计划中关于ORDER BY信息

使用EXPLAIN查看语句的执行计划，确认MySQL是否使用索引来进行ORDER BY排序。

• 如果EXPLAIN的Extra信息没有包含Using filesort，说明使用了索引进行排序。

• 如果EXPLAIN的Extra信息包含Using filesort，说明没有使用索引，而是用了filesort进行排序。

另外，如果进行了filesort文件排序，优化器的调试输出中会包含filesort_summery字段，如下：

"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值。在MySQL8.0.12之前的版本，显示的是sort_buffer_size值。（MySQL8.0.12之前，优化器会直接分配sort_buffer_size大小的内存用来排序，之后的版本优化器会逐步分配内存，开始分配少量内存，最大分配sort_buffer_size大小）

sort_mode显示的是sort buffer中缓存的内容：

• <sort_key,row_id>:说明sort buffer中存储的是排序的key和行id。排序key用来排序，行id用来从表中读取数据。

• <sort_key,additional_filelds>:说明sort buffer中存储的是排序key和查询的列数据，排序key用来排序，查询的列数据直接返回。

• <sort_key,packed_additional_fields>：与上面的类似，但是列数据会被压缩存储，而不是使用固定长度存储。

EXPLAIN执行计划不会区分优化器是否在内存中进行filesort。在优化器的调试输出中可以看到是否使用的内存filesort，可以查看filesort_priority_queue_optimization信息。优化器调试信息的详情可参考：MySQL Internals: Tracing the Optimizer.

原文链接：https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html