关于在 MySQL 排序中使用索引这件事！

分手后的思念是犯贱 2023-09-27 21:50 122阅读 0赞

![format_png][]

@\[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 三个。

表中的数据如下：

<table> 
 <thead> 
  <tr> 
   <th>id(主键)</th> 
   <th>username</th> 
   <th>age</th> 
   <th>address</th> 
   <th>gender</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>ab</td> 
   <td>99</td> 
   <td>深圳</td> 
   <td>男</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>bw</td> 
   <td>95</td> 
   <td>天津</td> 
   <td>男</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>cx</td> 
   <td>93</td> 
   <td>深圳</td> 
   <td>男</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>bc</td> 
   <td>80</td> 
   <td>上海</td> 
   <td>女</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>bg</td> 
   <td>85</td> 
   <td>重庆</td> 
   <td>女</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>ac</td> 
   <td>98</td> 
   <td>广州</td> 
   <td>男</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>bw</td> 
   <td>99</td> 
   <td>海口</td> 
   <td>女</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>ck</td> 
   <td>90</td> 
   <td>深圳</td> 
   <td>男</td> 
  </tr> 
  <tr> 
   <td>9</td> 
   <td>cc</td> 
   <td>92</td> 
   <td>武汉</td> 
   <td>男</td> 
  </tr> 
  <tr> 
   <td>10</td> 
   <td>af</td> 
   <td>88</td> 
   <td>北京</td> 
   <td>女</td> 
  </tr> 
 </tbody> 
</table>

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

![format_png 1][]

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

给大家 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 就可以通过索引进行排序，如下图：

![format_png 2][]

`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 中却要一个升序一个倒序，显然从索引树中拿到的数据无法满足这样的条件，所以这个查询并不会使用索引排序，如下图：

![format_png 3][]

Extra 中的 `Using filesort` 就说明了这里需要文件排序，无法通过索引排序完成需求。

#### 2.3 案例三 ####

再来看如下 SQL：

select address from user order by username desc

这个 SQL 和 2.1 小节的 SQL 相比就是排序的顺序变了，第一个 SQL 没有写顺序，默认就是升序，这个里边写了是按照倒序来排列。B+Tree 中的 username 是升序，那么这个能用到索引排序吗？这个是可以使用到索引排序的，在 MySQL5.7 中，执行计划如下：

![format_png 4][]

在 MySQL8.x 中，执行计划如下：

![format_png 5][]

小伙伴们看到，区别在于 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 之后，再来查看表的定义，结果如下：

![format_png 6][]

可以看到，虽然我在执行的时候定了索引字段的顺序，但是这个顺序实际上是被忽略了。

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

![format_png 7][]

可以看到，在 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 是可以通过索引排序的：

![format_png 8][]

#### 2.5 案例五 ####

再来看如下 SQL：

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

这个 SQL 中 username 也是给指定了具体的值了，但是排序却是按照 address 排序的，小伙伴们知道，当 username 确定后，首先是按照 age 排序，其次才是按照 address 排序，所以，对于上面这个 SQL，从索引树中读取出来的数据，顺序并不一定是按照 address 排的，所以上面这个 SQL 无法用到索引排序：

![format_png 9][]

#### 2.6 案例六 ####

再来看下面这个 SQL：

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

这个 SQL 中的查询条件 username 是范围搜索，当 username 是范围搜索的时候，就无法保证相应的 age 是有序的了，所以这个 SQL 也无法使用索引排序：

![format_png 10][]

另外需要注意的是，像查询条件中的 IN 和 BETWEEN 这样的关键字，也算是范围搜索，如果 where 子句中出现这些关键字，也是有可能导致无法使用索引排序的。

#### 2.7 案例七 ####

再来看下面这个 SQL：

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

这个虽然 username 也是按照范围搜索，但是最终排序的时候却是按照 username 和 age 排序的，按照范围搜索拿出来的 username 和 age 本身就是有序的，所以这里也可以使用索引排序：

![format_png 11][]

#### 2.8 案例八 ####

再来看下面这个 SQL：

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

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

![format_png 12][]

总之，就是当我们根据 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 的执行计划：

![format_png 13][]

可以看到，在这个查询中，优化器将 ta 表作为了第一张表，tc 表作为了第二张表，那么根据前面的结论，如果使用第一个表中的索引排序，就会用到索引排序，第二张表的则用不了，我们来验证一下。

![format_png 14][]

![format_png 15][]

![format_png 16][]

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

#### 3.2 order by null ####

还有一种特殊的情况就是 `order by null`，不知道有没有小伙伴见到过有人这样写？

在 MySQL8 之前，默认会按照 group by 的字段进行排序，此时加上 `order by null` 就是告诉 MySQL，不用帮我排序了，直接返回结果就行了，因为如果不加 `order by null`，则可能会进行 filesort 排序，降低查询效率。

不过从 MySQL8 开始，默认已经不会按照 group by 字段排序了，所以这句现在其实可以不用写了。

### 4. 小结 ###

好啦，关于 MySQL 中的索引排序就和小伙伴们聊这么多，希望大家都有所收获～

[format_png]: https://img-blog.csdnimg.cn/img_convert/0c2c639b39079a5fdbe2fc2f6604e082.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/3d4c66dd5f3813ae8fc701e30c818c45.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/35d279d34f9b7bf5411f4a57f40e61ed.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/d309db1940780a1cdd119d315e07a69a.webp?x-oss-process=image/format,png
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/3a3629b81c238f3e01196a26f7e6c513.webp?x-oss-process=image/format,png
[format_png 5]: https://img-blog.csdnimg.cn/img_convert/9e1dd3f9e8ef7015535ec5a2ad727020.webp?x-oss-process=image/format,png
[format_png 6]: https://img-blog.csdnimg.cn/img_convert/5668f099e618ae784b8f59d96bdac078.webp?x-oss-process=image/format,png
[format_png 7]: https://img-blog.csdnimg.cn/img_convert/6c0df6ef2a7e7fbf0778f4de8680c843.webp?x-oss-process=image/format,png
[format_png 8]: https://img-blog.csdnimg.cn/img_convert/f313461f41595a7c97c8435308642e1f.webp?x-oss-process=image/format,png
[format_png 9]: https://img-blog.csdnimg.cn/img_convert/40bfbc92d583fd9c3e6748af0bb085b8.webp?x-oss-process=image/format,png
[format_png 10]: https://img-blog.csdnimg.cn/img_convert/ef2bbed84156466139d8ac3ca712c842.webp?x-oss-process=image/format,png
[format_png 11]: https://img-blog.csdnimg.cn/img_convert/89cf9e8db679cee5c3ff9d3219530df0.webp?x-oss-process=image/format,png
[format_png 12]: https://img-blog.csdnimg.cn/img_convert/b03b95c28c00071fe570c9372d7ecc5a.webp?x-oss-process=image/format,png
[format_png 13]: https://img-blog.csdnimg.cn/img_convert/d383c6671a1ad812b0f8b2725b6912db.webp?x-oss-process=image/format,png
[format_png 14]: https://img-blog.csdnimg.cn/img_convert/347bb5b946fe7900959437b2805458bb.webp?x-oss-process=image/format,png
[format_png 15]: https://img-blog.csdnimg.cn/img_convert/83ca432daa9326ad08276bae273b20ab.webp?x-oss-process=image/format,png
[format_png 16]: https://img-blog.csdnimg.cn/img_convert/f2ca5c2c5f3fbfbe32e37d0f6b88c1b8.webp?x-oss-process=image/format,png