打开 order by 的大门，一探究竟《死磕 MySQL 系列 十二》

在日常开发工作中，你一定会经常遇到要根据指定字段进行排序的需求。

这时，你的 SQL 语句类似这样。

select id,phone,code from evt_sms where phone like '13020%' order by id desc limit 10

这个 SQL 的逻辑是十分清晰明了，但其内部的执行原理你知多少。

接下来，本期文章将带你打开 order by 的大门一探究竟。

本期所有结论都基于 MySQL8.0.26 版本

最新文章

字符串可以这样加索引，你知吗？《死磕MySQL系列 七》

无法复现的“慢”SQL《死磕MySQL系列 八》

什么？还在用delete删除数据《死磕MySQL系列 九》

MySQL统计总数就用count(*)，别花里胡哨的《死磕MySQL系列 十》

文章总目录

一、常见的 Extra 几个信息

在 MySQL 中想看一条 SQL 的性能不仅仅看是否用上了索引，还要看 Extra 中的内容，以下内容来自官方文档，给你最准确的学习资料。

using index

根据索引树可直接检索列信息，无需额外的操作来读取实际的行。

索引列即为查询列，也为条件列。

using index condition

下面这条语句 name 为普通索引，age 无索引。

select * from table where name = ? and age = ?

索引下推是在 MySQL5.6 及以后的版本出现的。

之前的查询过程是，先根据 name 在存储引擎中获取数据，然后在根据 age 在 server 层进行过滤。

在有了索引下推之后，查询过程是根据 name、age 在存储引擎获取数据，返回对应的数据，不再到 server 层进行过滤。

当你使用 Explain 分析 SQL 语句时，如果出现了 using index condition 那就是使用了索引下推，索引下推是在组合索引的情况出现几率最大的。

using index for group_by

只查索引列，对索引列使用了 group by

explain select phone from evt_sms where phone = "13054125874" group by phone;

using where

查询的列被索引覆盖，并且 where 筛选条件是索引列之一，但不是索引的前导列，Extra 中为 Using where; Using index，意味着无法直接通过索引查找来查询到符合条件的数据

查询的列被索引覆盖，并且 where 筛选条件是索引列前导列的一个范围，同样意味着无法直接通过索引查找查询到符合条件的数据

zero limit

这个估计很少有小伙伴知道，就是你的 SQL 语句查询数量为 limit 0

using temporary

使用了临时表，一般在使用 group by、order by 时会遇到。

这个也是本文即将要聊的话题。

using filesort

一般在使用 group by、order by 时会遇到，排序过程在内存中完成

Backward index scan

对索引列使用了降序操作

这里只列举了最常见的几个信息，MySQL 官方文档中对 Extra 的解析大概有 37 个，感兴趣的可以去看看，后期咔咔也会逐步完善这块内容。

二、文件排序

由于是在一些统计、排序的业务中会经常见到 Extra 中出现 using filesort 的信息。

在 MySQL8.0.26 版本中对一个没有索引的列进行排序在 Extra 中显示 using filesort。在低版本中需要你进行试验在什么情况下会出现。

在 Extra 中显示的 using filesort 表示的就是排序，MySQL 会给每个线程分配一块内存用于排序，也被称之为sort_buffer。这期文章和下期文章会牵扯到很多名词，记得自己整理一下哈！

再看这条语句

那么这条 SQL 执行的具体流程是什么呢？

1、初始化 sort_buffer，放入字段 phone、code 字段

2、在 phone 的索引树找到主键值

3、根据主键值到主键索引树中检索处 phone、code 对应字段的值，再存储 sort_buffer 中

4、继续从 phone 取下一个主键值

5、重复第三、第四，直到不满足 phone = 条件为止

6、在 sort_buffer 中的数据按照字段 phone 做快排

7、按照快排的结果取出前 10 行返回改客户端即可

问题：所有的排序都是在内存中进行的？

当然不是，任何内存都不是无限制的，是否在内存中排序取决于 MySQL 参数 sort_buffer_sort。

在 MySQL8.0.26 版本中这个值大小默认为 256kb。

当需要排序的数据量大于 256kb 的阀值时，则会利用临时文件进行辅助排序，也就是常说的归并排序算法实现。

sort_buffer_size 跟需要临时文件的个数成正比，如果 sort_buffer_size 越小则临时文件的数量就越多。

如何查看一个排序是否使用了临时文件，这个答案就交给大家来实现，版本不一致会导致很多结果都不同。

问题：你知道归并排序是如何实现的吗？

现在你知道了如果排序的数据大于 sort_buffer_size 会使用临时文件排序，这种排序使用的就是归并排序的思想，接下来让我们看看具体的流程是怎么样的。

1、把需要排序的数据分割，分割成每块数据都可以存放到 sort_buufer 中

2、对每块数据在 sort_buufer 中进行排序，排序好后，写入某个临时文件

3、当所有的数据都写入临时文件后，这时对于每个临时文件内部来说是有序的，但对于所有临时文件是无序的，所以还需要合并数据

4、假设现在存在 tmp1 和 tmp2 两个临时文件，这时分别从 tmp1、tmp2 读入部分数据到内存

5、假设从 tmp1 和 tmp2 中分别读入[0-5]的数据，然后分别使用 tmp1[0]、tmp2[0] 进行对比，一直到 tmp1[5]、tmp2[5]，这样两两比较就可以把 tmp1、tmp2 合并为一个文件。经过几轮下来所有分割的数据都会合并为一个有序的大文件

三、文件排序很慢，还有其它办法吗

通过上面的案例，如果排序的数据量非常大则会超过 sort_buffer_size 的最大值，就只能使用文件排序，文件排序涉及了多次的文件合并是非常消耗性能的。

在上文你有没有发现一个细节，SQL 中只需要排序 code 字段，但把 phone 字段也加到了 sort_buufer 中了。

这样单行的数据大小无形中就增大了，这样内存中能够存放的行数就减少了，需要分割成多个临时文件，排序性能会很差，那么有没有其它方案可以解决这种问题呢？

答案是肯定有的，就是接下来要聊的 rowid 排序。

先看一个参数 max_length_for_sort_data

默认 max_length_for_sort_data 的大小为 4096 字节，假设现在要排序的数据非常多，我们可以修改这个参数让其使用 rowid 的算法。

MySQL 中专门控制用户排序的行数据长度的参数，如果单行的数据长度超过了这个值，则 MySQL 会自动更换为 rowid 算法。

rowid 排序的思想就是把不需要的数据不放到 sort_buufer 中，让 sort_buffer 中只存放需要排序的字段。

问题：如果你是设计者，你会存放那些字段

假设现在存放只需要排序的字段，排序很快完成了，拿到排序后的数据结果你应该怎么办呢？你已经无从下手了。

因此，你可以把主键 ID 的值也存放到 sort_buufer 中，当排序完成后通过 ID 回表即可得到排序后的数据。

执行流程

试想一下，这个执行流程其实跟文件排序的流程大差不差。

只是存放到 sort_buufer 中的字段变为需要排序的字段加上主键字段。

接着在 sort_buufer 中按照排序字段进行排序

最后再遍历排序结果，取需要的行数，并使用 id 进行回表一次，查出你需要的列即可。

注意点

这不是说使用了 rowid 的排序算法后就不使用临时文件排序了，不是这样的。

使用 rowid 只是存放到 sort_buffer 中的数据多个，若需要排序的数据很多还是需要使用临时文件的。

四、优化文件排序

如果 MySQL 发现 sort_buufer 内存太小，会影响排序效率，才会采用 rowid 排序算法，使用 rowid 算法的好处就是 sort_buffer 中可以一次排序更多的行，缺点就是需要回表。

在 MySQL 中如果内存够用，就多利用内存，尽量减少磁盘访问。所有 rowid 的算法不会被优先选择，因为回表会造成过的磁盘读。

不是所有的 order by 语句，都需要排序操作的，上面分析的两种排序算法的由来都是因为原来的数据都是无序的。

问题：什么是有序的？

看过了索引那一期文章后，你现在应该知道以下两点。

索引本身具有顺序性，在进行范围查询时，获取的数据已经排好了序，从而避免服务器再次排序和建立临时表的问题。

索引的底层实现本身具有顺序性，通过磁盘预读使得在磁盘上对数据的访问大致呈顺序的寻址，也就是将随机的 I/O 变为顺序 I/O。

问题：如何防止进行排序

现在你应该知道答案了，就是给需要排序的列创建联合索引。

现在给 phone、code 建立一个联合索引，对应的 SQL 语句如下

alter table evt_sms add index idx_phone_code (phone,code);

那么执行同样的语句就不会使用排序操作了，接下来看一下执行流程

执行流程

1、从索引(phone,code)找到满足 phone='123456'的记录，取出 phone、code 的值，作为结果集的一部分直接返回

3、从索引(phone、code)取下一个记录，同样取出 phone、code 的值，作为结果集的一部分直接返回

4、重复步骤 2 直到查出 1000 行数据，或者不满足查询条件为止

五、总结

order by 没有用到索引时，执行计划中会出现 using filesort

using filesort 根据参数 sort_buffer_size 的值来决定使用需要使用临时文件

max_length_for_sort_data 参数决定是否使用 rowid 算法，若放入 sort_buffer 的每行数据大于设置的值就会使用 rowid 算法

现在你应该知道了 rowid 排序只是把需要排序的字段和主键 ID 放入 sort_buffer 中，而文件排序则是把查询的所有字段全部放入 sort_buffer 中。

还有 rowid 会多造成一次回表操作，这个你也要知道。

最后提到了优化 order by 语句，这里提到了建立覆盖索引，利用索引的有序性直接返回结果不用进行排序。

这里并不是提倡大家在实际生产环境中盲目建立，而是根据具体业务情况，如果数据非常的小在内存排序是非常快的。并且覆盖索引会占用更多的存储空间和维护开销。

坚持学习、坚持写作、坚持分享是咔咔从业以来所秉持的信念。愿文章在偌大的互联网上能给你带来一点帮助，我是咔咔，下期见。