MySQL 混合排序 ASC、DESC 无法完全利用索引

MySQL 混合排序 ASC、DESC 无法完全利用索引

本文所讲内容，默认情况下都是基于 5.7 版本进行验证的。

在小于 5.8 的版本中，asc、desc 混用无法完全利用索引，除非你的数据库是 MySQL 8.0+。

首先说下 asc 和 desc 排序

• asc 升序排列大家知道，沿着页中单链表遍历即可。
• desc 降序排列就要注意了，你需要知道页中有 Infimum + Supremum 记录，可以先简单理解为头指针和尾指针。首先从头指针处遍历到最后一条用户记录，接着从头指针遍历到倒数第二条记录，从头指针遍历到倒数第三条记录…

这样就能获取到倒序排列的结果集了，很显然，asc 升序排列获取结果集要快于 desc 降序排列。

实际上，一个页中的记录分了很多组，页中有一块 Page Directory 的空间存放了叫"槽"的东西，槽中存放着每个分组内最后一条记录在页面中的地址偏移量。只要找到了最后一条记录，然后找到分组的第一条记录(上一个槽的下一条记录)，就可以在这个分组内小范围的遍历获取倒序的结果。这可比从页中第一条记录开始遍历获取倒序结果好的多，大大减少遍历时间。

如果非要 asc 和 desc 混合排序，我们来分析一下。

explain select * from demo_info where key_part1 = 'a' order by key_part2, key_part3 desc;

按照key_part2升序排列，key_part2相同的情况下就按key_part3降序排列。

MySQL 8.0 以前无法直接利用索引进行 asc 和 desc 混合排序，所以过程如下：

• 先筛选到满足key_part1 = 'a'的记录
• 再按照单链表取出key_part2最小的记录(假设为'b'，有多条满足key_part2 = 'b'的记录)
• key_part3无法直接利用索引完成，将key_part2 = 'b'的记录取出进行一次文件排序，即无需全部回表获得完整记录后再排序，直接在非聚集索引进行文件排序（也称为外部排序，一般是归并排序）
• 依次循环1~3过程，直到找到不满足key_part1 = 'a'的记录为止，最后回表取出的结果集就是有序的完整的用户记录。

MySQL 8.0 引入了 Descending Index 的特性，允许利用索引直接 asc 和 desc 混合排序。

但是这里用到的联合索引却是升序的KEY idx_key_part(key_part1, key_part2, key_part3)，Using filesort是因为这里要取出第二次按照key_part3降序排列，索引声明是升序的，降序排列一定提示Using filesort。当拿到key_part2相等的记录时，还要按照key_part3降序排一次（在满足条件的记录分组内小范围遍历获取倒序结果）

如果在MySQL 8.0+将key_part3为desc，这里索引改为KEY idx_key_part(key_part1, key_part2, key_part3 desc)，再次执行。

explain select * from demo_info where key_part1 = 'a' order by key_part2, key_part3 desc;

结果是 asc 和 desc 完美的利用索引进行混合排序。而在 MySQL8.0 以下，索引声明 desc 是直接被忽略的。