文章目录

• • 1.ORDEY BY排序查询优化方面的概念
  • 2.ORDER BY排序的优化原则
  • 3.ORDER BY排序优化的案例
  • • 3.1.准备排序优化的表以及索引
    • 3.2.同时对nl和lxfs字段使用升序排序
    • 3.3.同时对nl和lxfs字段使用降序排序
    • 3.4.排序时调整联合索引中字段的位置顺序
    • 3.5.排序时一个字段使用升序一个字段使用降序
    • 3.6.解决两个字段不同排序规则出现Using filesort
  • 4.ORDER BY多字段多排序方式的索引结构图

1.ORDEY BY排序查询优化方面的概念

在MySQL数据库中，有两种排序类型：

• Using filesort：
  • 通过表的索引或者全表扫描，读取满足条件的数据行，然后在排序缓冲区sortbuffer中完成排序的操作，所有不是通过索引直接返回的排序结果都是filesort排序，效率相对来说比较低。
  • 索引本身具有排序的功能，如果索引的排序没有满足我们的排序条件，此时读取到满足条件的数据后，还需要在缓冲区中重新排序，最后完成排序的操作，这类的排序都是filesort。
  • 就和回表查询一样，本来可以在二级索引中拿到数据，但是还需要走回表查询才能完成需求，filesort也一样，如果不能再索引排序中拿到结果，就需要在缓冲区中再次排序。
• Using index：
  • 通过有序索引排序扫描后，直接就能返回有序的数据，满足排序需求，这类的排序都是using index，不需要额外的排序，效率很高。
  • 索引排序默认是ASC升序排序，如果我们的排序规则都是升序，并且排序的字段也是索引字段，此时就是using index排序。

对于这两种排序方式，Using index的性能最高，Using filesort的性能较低，我们再优化排序操作时，尽量的使用Using index。

索引的默认排序规则是升序排序，下面来观察几种排序需求，看看哪些是Using filesort，那些是Using index。

• 当排序的字段都是升序时，排序方式就是Using index。

• 当排序的字段都是降序时，排序方式就是Using index，因为还是在一个索引排序集里，只不过反向排序即可。

• 当排序的字段一个是升序、一个是降序，此时就会出现Using filesort，因为第一个字段是升序，也是默认的规则，直接返回排序即可，但是第二个字段是降序，相当于又要重新读取一遍数据，然后拿到缓冲区里再次排序。

2.ORDER BY排序的优化原则

• 将需要排序的字段建立联合索引，多字段排序时也是需要遵循最左前缀法则的。
• 查询返回的字段尽量使用覆盖索引的字段，此时不需要回表查询，效率很高。
• 多字段排序时，如果一个字段升序、一个字段降序，那么就在创建联合索引时，为字段设置排序规则。
• 如果不能避免出现filesort，那么可以适当的增大排序缓冲区的大小，参数是sort_buffer_size，默认为256k。

3.ORDER BY排序优化的案例

3.1.准备排序优化的表以及索引

1）表数据

2）索引

我们主要以nl和lxfs两个字段排序，将这两个字段创建一个联合索引。

mysql> create index idx_user_nl_lxfs on tb_user(nl,lxfs);

3.2.同时对nl和lxfs字段使用升序排序

同时对nl和lxfs字段进行升序排序，观察执行计划中排序方式是什么。

mysql> explain select nl,lxfs from tb_user order by nl,lxfs;

根据执行计划的输出，我们可以看到同时对nl和lxfs字段进行升序排序时，都走了索引，排序方式是Using index，此时的效率最高。

当然如果我们只根据nl排序，排序方式也是Using index，因为nl字段也在联合索引中。

3.3.同时对nl和lxfs字段使用降序排序

同时对nl和lxfs字段进行降序排序，观察执行计划中的排序方式是什么。

mysql> explain select nl,lxfs from tb_user order by nl desc,lxfs desc;

根据执行计划的输出，我们可以看到也是走的Using index，但是还多了个一个Backward index scan，这个代表使用了反向扫描索引，因为我们使用的降序排序，默认是升序排序，叶子节点从小到大排序，所以就会反向扫描整个索引结构。

3.4.排序时调整联合索引中字段的位置顺序

我们在排序时，调整一下排序字段的位置顺序，在创建联合索引时，nl字段在最左侧，lxfs字段在右侧。

在3.2.中排序时nl字段在左侧，lxfs字段在右侧，观察会有什么样的排序方式。

mysql> explain select nl,lxfs from tb_user order by lxfs,nl;

观察执行计划的输出，我们可以看到既出现了Using index又出现了Using filesort，原因就是调整了索引字段的位置，nl字段是最左前缀，我们将lxfs放在最前面，相当于违背了最左前缀法则，就会出现Using filesort。

3.5.排序时一个字段使用升序一个字段使用降序

排序时一个字段采用升序排序，一个字段使用降序排序，观察效果。

mysql> explain select nl,lxfs from tb_user order by nl asc,lxfs desc;

观察执行计划的输出，我们可以看到既出现了Using index又出现了Using filesort，那么为什么会出现这样的现象呢？原因就是nl字段使用升序排序没问题，只需要在索引结构中顺序拿到结果，但是lxfs字段是降序排序，只能拿着结果去排序缓冲区中再进行降序排序，最终拿到结果。

3.6.解决两个字段不同排序规则出现Using filesort

针对3.5中排序后的现象，两个字段不同的排序规则，索引默认是升序排序，nl字段的升序排序没有任何效率问题，但是lxfs字段当下拿到的是nl字段升序后的结果集，还想要对lxfs字段降序排序，只能拿到排序缓冲区中进行额外的排序，因为在nl字段升序后的结果集里，lxfs字段也是升序的，但是我们要的是降序的效果，所以只能额外排序了。

想要解决这个问题其实非常简单，只要能够实现一个索引结构里不同字段有不同的排序方式就行了，例如nl字段在索引结构里走升序排序，lxfs字段在索引结构里走降序就可以完美解决了。

不同字段的排序方式可以在创建索引时指定即可。

mysql> create index idx_user_nl_lxfs_ad on tb_user (nl asc,lxfs desc);

Column_name一列是该索引字段的排序规则，A表示asc升序排序，D表示desc降序排序。

创建完索引后，我们再执行相同的SQL，观察执行计划。

mysql> explain select nl,lxfs from tb_user order by nl asc,lxfs desc;

观察执行计划，此时已经是Using index了。

4.ORDER BY多字段多排序方式的索引结构图

为不同的字段设置了不同的排序规则后，在索引的叶子节点里就已经排序好了，上面图的所有字段排序规则都是升序，下面图中lxfs字段是降序，可以看到每个索引元素中手机号都是降序排序了。