关于mysql:关于在-MySQL-排序中使用索引这件事

@[toc]
后面跟小伙伴们分享的索引相干的内容，基本上都是在 where 子句中应用索引，实际上，索引也还有另外一个大的用途，那就是在排序中应用索引，明天咱们就来聊聊这个话题。

1. 排序的两种形式

MySQL 中想给查问后果排序，咱们只须要来一个 order by 即可，SQL 很简略，底层实现起来整体上来说，有两种不同的思路：

1. filesort，有时候咱们也将之称为文件排序，这个名字有时候会给咱们一些误会，让人认为是在磁盘上进行排序的，然而实际上并不一定，数据量比拟小的时候，间接在内存中进行排序就行了，只有当在内存中无奈实现排序的时候，才会用到磁盘文件。
2. 索引排序，因为 InnoDB 中的索引是依照 B+Tree 的模式将数据组织在一起的，B+Tree 中数据自身就是有序的，所以如果可能利用好索引，排序的事件就会事倍功半。

一共就这两种排序的形式，小伙伴们也发现了，如果咱们的索引设计比拟正当，最终可能依照第 2 种形式进行排序，那必定是最好不过了。

不过这里须要留神一个细节，第二种排序形式快有一个前提，那就是不须要回表，如果查问的过程中须要回表，那么第二种形式就不肯定快了。起因也简略：

• 如果不须要回表，也就是咱们想要查问的数据都在索引树上，索引树上的数据自身又都是依照顺序存储的，那么查到数据间接返回即可，自身就是有序的。
• 如果查问的时候，索引树上并没有咱们想要的字段，那么就须要回表，小伙伴们晓得，回表基本上都是随机 IO 了，因为回表的时候，主键值并不一定间断，此时效率就会低一些。那么这个时候第二种排序形式的性能就不肯定强于第一种了，当然，这并无固定论断，还是要联合具体情况剖析，这里我只是通知小伙伴们有各种可能的状况。

2. 索引排序

如果咱们想用上索引排序，那么须要满足哪些条件呢？

还是以咱们上篇文章的数据为例，假如我有如下表构造：

CREATE TABLE `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gender` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_prop_index` (`username`,`age`,`address`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

这个表中有一个联结索引，联结索引的字段蕴含 username、age 和 address 三个。

表中的数据如下：

还是假如 username、age、address 三个字段组成联结索引，B+Tree 如下：

小伙伴们就想想，怎么样查问，查出来的后果是有序的？

给大家 1 分钟总结一下。

咱们来梳理下：只有当索引的程序和 order by 子句的程序完全一致，并且所有列的排序方向也都统一的状况下，MySQL 能力通过索引来对后果进行排序，同时，如果是联结索引，order by 子句也须要满足最左匹配准则。

我举几个例子。

2.1 案例一

先来看如下 SQL：

select address from user order by username;

这个是查问 address 字段，依据 username 进行排序。很显著，咱们想要的 address 字段就存在于这个联结索引的 B+Tree 上，并且这个联结索引的 B+Tree 就是依照 username 进行升序排序的，所以这个 SQL 就能够通过索引进行排序，如下图：

type:index 就阐明了 MySQL 应用了索引扫描来进行排序的。

2.2 案例二

再来看上面这条 SQL：

select address from user order by username asc,age desc\G

这个 SQL 还是查问 address 字段，是依据 username 和 age 进行排序的，其中 username 是依照升序排序，age 则是依照倒序排序，小伙伴们想想，在后面这个联结索引的 B+Tree 中，username 是升序的没问题，当 username 雷同的时候，age 也是依照升序排序的，然而 SQL 中却要一个升序一个倒序，显然从索引树中拿到的数据无奈满足这样的条件，所以这个查问并不会应用索引排序，如下图：

Extra 中的 Using filesort 就阐明了这里须要文件排序，无奈通过索引排序实现需要。

2.3 案例三

再来看如下 SQL：

select address from user order by username desc

这个 SQL 和 2.1 大节的 SQL 相比就是排序的程序变了，第一个 SQL 没有写程序，默认就是升序，这个里边写了是依照倒序来排列。B+Tree 中的 username 是升序，那么这个能用到索引排序吗？这个是能够应用到索引排序的，在 MySQL5.7 中，执行打算如下：

在 MySQL8.x 中，执行打算如下：

小伙伴们看到，区别在于 Extra 中多了一个 Backward index scan。

这是啥意思呢？

在 MySQL8 之前，索引是能够被反向扫描的，然而反向扫描效率会低一些，所以小伙伴们看到，在 MySQL5.7 中用到了索引排序，而且也没说其余的，这其实就是索引反向扫描了。

从 MySQL8 开始，索引定义时候的降序关键字 DESC 将不再被疏忽，索引树在存储数据的时候能够降序存储了，这样在未来查问的时候扫描索引就能够依照正向扫描了，正向扫描效率绝对于反向扫描效率会高一些。

这块我来举个例子阐明问题。假如我有如下创立表的 SQL：

CREATE TABLE t (
  c1 INT, c2 INT,
  INDEX idx1 (c1 ASC, c2 ASC),
  INDEX idx2 (c1 ASC, c2 DESC),
  INDEX idx3 (c1 DESC, c2 ASC),
  INDEX idx4 (c1 DESC, c2 DESC)
);

当我在 MySQL5.7 中执行如上 SQL 之后，再来查看表的定义，后果如下：

能够看到，尽管我在执行的时候定了索引字段的程序，然而这个程序实际上是被忽略了。

再来看看 MySQL8 中执行之后的后果：

能够看到，在 MySQL8 中，索引定义时字段的程序被保留了。这印证了咱们后面所说的没有问题。

最初，回到咱们的问题，Backward index scan 示意优化器在查问的时候将可能应用降序索引。

2.4 案例四

再来看如下 SQL：

select gender from user where username='ab' order by age

这个 SQL 中曾经给 username 指定了具体的值了，在后面的 B+Tree 中，当 username 曾经确定的时候，那么接下来就是依照 age 排序的，如果 age 雷同则是依照 address 排序，所以下面这个 SQL 是能够通过索引排序的：

2.5 案例五

再来看如下 SQL：

select gender from user where username='ab' order by address

这个 SQL 中 username 也是给指定了具体的值了，然而排序却是依照 address 排序的，小伙伴们晓得，当 username 确定后，首先是依照 age 排序，其次才是依照 address 排序，所以，对于下面这个 SQL，从索引树中读取进去的数据，程序并不一定是依照 address 排的，所以下面这个 SQL 无奈用到索引排序：

2.6 案例六

再来看上面这个 SQL：

select gender from user where username like 'a%' order by age

这个 SQL 中的查问条件 username 是范畴搜寻，当 username 是范畴搜寻的时候，就无奈保障相应的 age 是有序的了，所以这个 SQL 也无奈应用索引排序：

另外须要留神的是，像查问条件中的 IN 和 BETWEEN 这样的关键字，也算是范畴搜寻，如果 where 子句中呈现这些关键字，也是有可能导致无奈应用索引排序的。

2.7 案例七

再来看上面这个 SQL：

select gender from user where username like 'a%' order by username,age

这个尽管 username 也是依照范畴搜寻，然而最终排序的时候却是依照 username 和 age 排序的，依照范畴搜寻拿进去的 username 和 age 自身就是有序的，所以这里也能够应用索引排序：

2.8 案例八

再来看上面这个 SQL：

select gender from user where username like 'a%' order by username,gender

这个 SQL 就不必多说了，排序字段中呈现了索引之外的列，那必定没法应用索引排序了：

总之，就是当咱们依据 where 子句中的条件从 B+Tree 中定位到数据之后，定位到的这个数据到底是否有序？如果有序且是 SQL 中要求的程序，就能应用索引排序，否则就不能够。

当初咱们再来回过头看一下一开始的论断，大家这个时候应该就好了解了：

只有当索引的程序和 order by 子句的程序完全一致，并且所有列的排序方向也都统一的状况下，MySQL 能力通过索引来对后果进行排序，同时，如果是联结索引，order by 子句也须要满足最左匹配准则。

3. 其余状况

3.1 多表联查

当咱们在查问的时候是多表连贯查问时，如果用到了排序，那么 order by 子句中波及到的字段，必须全副在第一个表中，此时才会用到索引排序。

松哥举一个 TienChin 我的项目中的例子，TienChin 中有一个流动渠道表 tienchin_channel，还有一个流动表 tienchin_activity，流动表中援用到了渠道表的 id，咱们来做如下一个多表联结查问：

select ta.name from tienchin_activity ta inner join tienchin_channel tc using(`channel_id`)

咱们来看下这个 SQL 的执行打算：

能够看到，在这个查问中，优化器将 ta 表作为了第一张表，tc 表作为了第二张表，那么依据后面的论断，如果应用第一个表中的索引排序，就会用到索引排序，第二张表的则用不了，咱们来验证一下。

能够看到，如果是第一张表的索引，就用到了索引排序；如果是第二张表的索引，就没有用到索引排序，如果两张表的索引都用了，也不会应用索引排序。

3.2 order by null

还有一种非凡的状况就是 order by null，不晓得有没有小伙伴见到过有人这样写？

在 MySQL8 之前，默认会依照 group by 的字段进行排序，此时加上 order by null 就是通知 MySQL，不必帮我排序了，间接返回后果就行了，因为如果不加 order by null，则可能会进行 filesort 排序，升高查问效率。

不过从 MySQL8 开始，默认曾经不会依照 group by 字段排序了，所以这句当初其实能够不必写了。

4. 小结

好啦，对于 MySQL 中的索引排序就和小伙伴们聊这么多，心愿大家都有所播种～