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