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

这时，你的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语句，这里提到了建设笼罩索引，利用索引的有序性间接返回后果不必进行排序。

这里并不是提倡大家在理论生产环境中自觉建设，而是依据具体业务状况，如果数据十分的小在内存排序是十分快的。并且笼罩索引会占用更多的存储空间和保护开销。

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