共计 4283 个字符，预计需要花费 11 分钟才能阅读完成。
在日常开发工作中，你肯定会常常遇到要依据指定字段进行排序的需要。
这时，你的 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 语句，这里提到了建设笼罩索引，利用索引的有序性间接返回后果不必进行排序。
这里并不是提倡大家在理论生产环境中自觉建设，而是依据具体业务状况，如果数据十分的小在内存排序是十分快的。并且笼罩索引会占用更多的存储空间和保护开销。
保持学习、保持写作、保持分享是咔咔从业以来所秉持的信念。愿文章在偌大的互联网上能给你带来一点帮忙，我是咔咔，下期见。
