深入探讨 MySQL 的 order by 优化 _MySQL

文章转载自yangyidba，作者杨奇龙
一前言为什么是再说呢？因为前面已经写过《order by 原理以及优化》，介绍 order by 的基本原理以及优化。如果觉得对 order by 原理了解不透彻可以参考其他同行的文章《MySQL排序内部原理探秘》.本文是基于官网文档的二刷(基本翻译+测试验证),看完本文大部分开发同学可以了解到什么样的select + order by 语句可以使用索引，什么样的不能利用到索引排序。
二分析2.1 官方标准介绍
对于select order by语句如何能够利用到索引，官方表述如下:

"The index can also be used even if the ORDER BY does not match the index exactly, as long as all of the unused portions of the index and all the extra ORDER BY columns are constants in the WHERE clause."

翻译一下就是
即使ORDER BY语句不能精确匹配(组合)索引列也能使用索引,只要WHERE条件中的所有未使用的索引部分和所有额外的ORDER BY列为常数就行。如何理解这句话呢？我们通过具体用例来解释。
2.2 准备工作

文章插图

文章插图
2.3 能够利用索引的例子分析
官方的文档中介绍有7个例子可以使用索引进行排序。如果使用explain/desc工具查看执行计划中的extra中出现了Using filesort则说明sql没有用到排序优化。
案例一
SELECT * FROM t1 ORDER BY key_part1,key_part2,...;

文章插图
分析:

显然上述sql没有利用到索引排序. type=ALL Extra=Using filesort,因为where字句没有条件，优化器选择全表扫描和内存排序。

文章插图
分析:

从type=index，extra=Using index 可以看出当select 的字段包含在索引中时，能利用到索引排序功能，进行覆盖索引扫描。使用select * 则不能利用覆盖索引扫描且由于where语句没有具体条件MySQL选择了全表扫描且进行了排序操作。

案例二
SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;
使用组合索引中的一部分做等值查询，另一部分作为排序字段。更严谨的说法是where条件使用组合索引的左前缀等值查询，使用剩余字段进行order by排序。

文章插图
分析:
where 条件字句可以基于 shid 进行索引查找并且利用(shid,gid)中gid的有序性避免额外的排序工作。我们基于本例解释"即使ORDER BY语句不能精确匹配(组合)索引列也能使用索引,只要WHERE条件中的所有未使用的索引部分和所有额外的ORDER BY列为常数就行。"
该语句的order by gid 并未精确匹配到组合索引(shid,gid)，where条件 shid利用了组合索引的最左前缀且为等值常量查询，对order by 而言shid就是额外的字段，没有出现在order by子句中却是组合索引的一部分。这样的条件既可以使用索引来排序。
案例三
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;