小小的排序，到底隐藏着什么奥秘，让我们通过生活中的例子去快速了解吧！
一、排序怎么用
我们都知道，排序就是使用 order by 这个关键字 ，可以是升序（asc），也可以是降序 (desc)，在我们日常的使用中经常会有各种排序的需求，例如隔壁老王想给他班里的学生按成绩降序排下序，来获得班级的排名, 就是 order by grade desc ，使用是十分简单的，但是，有没有发觉有时候加上了排序会让你的SQL突然执行的特慢，体验感从开飞机变成了开摩托。此时，你该思考，这该死的 order by 到底做了什么？
二、排序的原理
我们先抛开 MySQL 不讲，先来聊聊一个场景，假设有一个班的学生，你需要按照数学考试的成绩给他们进行排序，最后让他们拿着成绩单并且背着他们的书包按照成绩从高到低进行排队，你会怎么做呢？
首先我们假设成绩单上都有学生的姓名并且学生都知道他们的书包是哪一个。而且你只有几个小办公室，可能不能同时把学生放到一个房间里，但是走廊是可以用的。
那么首先你要先考虑，办公室够不够所有存下所有背着书包并且拿着试卷的学生呢？
场景1： 假设是够的，那么你可能会直接让学生背好书包拿着试卷，然后你就直接按照成绩来让学生站好队，不管你用的是快速排序，还是插入排序，还是希尔排序，我们暂时不关心。就当做你知道怎么排序就好了。
这种情况看似可取啊，因为空间够大啊，我一次性让他们都带好装备直接排不是挺好吗？还不麻烦
场景2： 办公室空间不够大啊，可能你就只有一张桌子，那咋办啊？？？ 能不能不管学生和书包，直接先把试卷排好序呢？哦呦，可以啊，试卷排好序后，然后到走廊叫名字，让对应的学生带好书包过来排队不就好了。喔，好聪明啊。
场景3： 哎呀我的办公室又小，但是我又不想用先排序试卷的方式咋办，对哦，我不是还有几个小办公室可以用吗？那就先排前十名学生，就放到一个办公室里，然后再拍十一名到二十名放到第二个办公室里，最后按顺序一个办公室一个办公室把学生叫出来走廊，拼接起来不也行。
好，我们现在分析了上面三种场景。可以知道，空间是很关键的因素啊，房子够大，你在房子里打高尔夫都行，我穷我能咋办。
三、MySQL的排序机制
接下来我们进入正题 ，MySQL的排序机制是什么呢？
现在我们把上面的场景实例化一下，假设成绩单就是 主键id，分数、学生和书包分别是另外三个字段, MySQL主要有以下两种类型的排序方式。
1.全字段排序
全字段排序，顾明思议，就是我们排序的时候已经有所有需要返回的字段了，可以直接排序后就返回，不需要再回表查询，例如场景1，我们需要的是 学生、试卷、书包 三个字段，一开始，我们就让学生们带好试卷背好书包，这样目的就是不需要再重新再喊一遍学生过来排队了（回表），但是这得要求你有足够的空间，一般来说，这个空间就是内存，因为数据在内存中计算机才可以处理，在数据库领域就叫做 “sort buff”，那么执行过程就像下面那样：
（1）初始化 sort buff （收拾好办公室） ,确定好需要的字段 （学生、试卷、分数、书包）、
（2）根据查询条件找到第一个满足条件的数据（在一堆试卷中找到第一个老王班的学生试卷）
（3）根据主键id（试卷），取出分数、学生和学生书包（根据试卷到班里叫学生带上书包和试卷到办公室等）
（4）重复 （2）（3）步骤，直到叫完了所有学生
（5）对数据进行排序（根据成绩分数让学生排好队）
办公室的大小我们怎么确定呢，在 mysql 中有这么一个参数 sort_buff_size ，你可以在执行下列 SQL获取到它的值：
root@localhost : (none) 12:19:05> SELECT @@sort_buffer_size;
+--------------------+
| @@sort_buffer_size |
+--------------------+
| 8388608 |
+--------------------+
1 row in set (0.00 sec)
这个值如果没有特殊要求，一般都是采用默认配置，因为每一个线程都是会开辟这么一个内存空间，因此，如果这个值过大，很可能会造成数据库内存占用过高，引起系统 oom ，除了这个排序内存空间，mysql 还有一种通过外置文件辅助来排序的方式。我们可以通过以下 SQL 来判断 SQL是否用到了文件排序，并且用到了几个文件：
以下的 SQL 只能在 mysql 5.6 及以上版本能够使用
/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; /* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';/* 执行语句 */
/* 你的SQL语句 *//* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';/* 计算Innodb_rows_read差值 */
select @b-@a;
如果你的数据库是 MariaDB 或者 mysql 5.6以下，在打开optimizer_trace 时会报 Unknown system variable ‘optimizer_trace’，这是因为版本不支持，你可以使用一下语句检查你的数据库版本：
SELECT @@VERSION, @@VERSION_COMMENT;
+---------------------+-------------------+
| @@VERSION | @@VERSION_COMMENT |
+---------------------+-------------------+
| 10.0.13-MariaDB-log | MariaDB Server |
+---------------------+-------------------+
1 row in set (0.00 sec)
我们可以看到, 在此我们解释一下这些字段的意思：
rows : 扫描的函数
examined_rows: 参与排序的行数
number_of_tmp_files: 参数排序的文件数，最后会将这些文件合并到一起，其实类似场景3
sort_buffer_size :排序内存大小
sort_mode :排序模式，packed_additional_fields
的意思是，排序过程对字符串做了“紧凑”处理，即使字段是vachar类型，排序时也是按照实际长度来分配空间
注：这里需要注意的是，为了避免对结论造成干扰，需要把 internal_tmp_disk_storage_engine 设置成 MyISAM。否则，select @b-@a 的结果会显示为 4001。这是因为查询 OPTIMIZER_TRACE 这个表时，需要用到临时表，而 internal_tmp_disk_storage_engine 的默认值是 InnoDB。如果使用的是 InnoDB 引擎的话，把数据从临时表取出来的时候，会让 Innodb_rows_read 的值加 1
MySQL的宗旨是尽可能的使用内存，如果内存够放，就不要多事去用文件了，因为文件有很多的性能问题，要考虑 io 等方面的磁盘访问，一般来说，内存如果放不下，就需要用到外部排序，而且外部排序一般会使用归并排序算法。
那么我们如何去判断有没有用到外部文件呢？
根据 number_of_tmp_files 的值就可以了，如果值为 0 ，说这个排序可以直接在内存中实现，不需要使用到文件，否则，说明还需要外部文件进行辅助排序。这就好比我办公室够大，就不用别人的办公室来辅助呢
2.Row id 排序
考虑到一种情况，如果我们所需要的列太多了，很可能 sort_buff 能存的行数就会减少，那么排序就需要用到更多了辅助文件了，这就好比说每个学生的书包都贼大，搞得办公室能存下的学生就变少了，这种情况下 我们就需要换种策略来实现了。也就是我们常说的 row_id 排序，MySQL 有个字段是用来指定一行的最大长度的。
max_length_for_sort_data：
这是MySQL中专门控制用于排序的行数据长度的一个参数，如果一行的数据长度超过这个值，那么，就需要换一个算法。上面的执行过程就会变成了下面这样：
（1）初始化 sort buff （收拾好办公室） ,确定好需要的字段 （试卷和分数）
（2）根据查询条件找到第一个满足条件的数据（在一堆试卷中找到第一个老王班的学生试卷）
（3）根据主键id（试卷），取出整行数据，把主键id（试卷）和分数 放进 sort_buff
（4）重复 （2）（3）步骤，直到找到了所有试卷
（5）对数据进行排序（根据成绩让学生排好队）
（6）根据主键 id的值到原表中取出对应所需的其他字段（根据排好的试卷，到班里叫对应的学生背好书包过来认领）
我们可以对比两种算法，会发现这一种多了一次访问表的操作，就是最后一个步骤
但是这里需要强调的一点是，MySQL服务端从 sort_buff 依次取出 id 然后查表拿其他字段的结果，不再需要在服务端暂存结果，而是直接返回给客户端，因为没有必要去把结果存起来，让客户端存储即可
3.其他优化方式
我们对比了上面的两种排序方式，认真的想一想，为什么需要排序，归根结底就是原来的数据是无序的，那什么情况下拿到的数据本身就是有序的呢？索引树不是本身就有序吗？
如果我们建立对应的覆盖索引不是就可以免去排序的痛苦了。
也就是说，在上面的例子中，如果我们给班级和分数做了一个联合索引，那么取出来的数据本身不就是按照分数排好序的。
然后我们再考虑一种索引，覆盖索引，覆盖索引是指索引上的信息足够满足查询的请求，不需要再回到主键索引中取数据。
因此，如果我们建立一个 班级、分数、学生 的索引，不就更好了，这样就不需要回表（因为普通索引存储的是主键id的值，其他字段都是通过这个主键id去主键索引数取行数据）
到了这里，排序的大致原理也就说完了，
例子还是有些小残缺，例如试卷作为主键id，本身来说不是特别准确，但是不影响使用哦！