MySQL-order by 是怎么工作的？

在开发系统的时候，你总是不可避免地会使用到 order by 语句。你心里要清楚每个语句的排序逻辑是怎么实现的，还要能够分析出在最坏情况下，每个语句的执行对系统资源的消耗，这样才能做到下笔如有神，不犯低级错误。

MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer，内存不够用的时候会使用外部文件。

目录

一、全字段排序 内存够用，全部字段获取后在进行排序

二、rowid 排序 只获取排序字段和主键，返回结果前需要回表

三、全字段排序 VS rowid 排序 内存，回表

四、所有order by都需要排序吗？

1、联合索引  通过联合索引各字段依次有序特性，省略排序过程

2、覆盖索引 通过联合索引和覆盖索引的应用，在省略排序过程的同时，进一步省略回表。但这需要效率和空间消耗的权衡。

---

一、全字段排序

select city,name,age from t where city='杭州' order by name limit 1000 ;

执行以上sql，city是索引，通过explain语句，Extra 这个字段中的“Using filesort”表示的就是需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

通常情况下，这个语句执行流程如下所示 ：

1. 初始化 sort_buffer，确定放入 name、city、age 这三个字段；
2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id；
3. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
4. 从索引 city 取下一个记录的主键 id；
5. 重复步骤 3、4 直到 city 的值不满足查询条件为止；
6. 对 sort_buffer 中的数据按照字段 name 做快速排序；
7. 按照排序结果取前 1000 行返回给客户端。

以上过程称为全字段排序，“按 name 排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。需要外部临时文件情况，MySQL 会按需将数据分成N份，每一份单独排序后存在这些临时文件中。然后把这 N个有序文件再合并成一个有序的大文件。—— 桶排序

在上面这个算法过程里面，只对原表的数据读了一遍，剩下的操作都是在 sort_buffer 和临时文件中执行的。但这个算法有一个问题，就是如果查询要返回的字段很多的话，那么 sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。所以如果单行很大，这个方法效率不够好。

max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。

二、rowid 排序

由于所需返回的长度过长（长度标准由max_length_for_sort_data来定义），因此Mysql的另一个算法选择只在sort_buffer中放入name（排序字段）和 主键id，算法的排序流程是：

1. 初始化 sort_buffer，确定放入两个字段，即 name 和 id；
2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id；
3. 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
4. 从索引 city 取下一个记录的主键 id；
5. 重复步骤 3、4 直到不满足 city='杭州’条件为止；
6. 对 sort_buffer 中的数据按照字段 name 进行排序；
7. 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

最后的“结果集”是一个逻辑概念，实际上 MySQL 服务端从排序后的 sort_buffer 中依次取出 id，然后到原表查到 city、name 和 age 这三个字段的结果，不需要在服务端再耗费内存存储结果，是直接返回给客户端的。 

三、全字段排序 VS rowid 排序

全字段：MySQL认为内存足够大，将所有查询字段放在sort_buffer中，排序后直接返回，不用回表；

rowid：MySQL认为内存不够大，减少字段，一次可以排序更多行，但是需要回表；

四、所有order by都需要排序吗？

1、联合索引

如果能够保证从 city 这个索引上取出来的行，天然就是按照 name 递增排序的话，那么就可以不用再排序了。因此我们可以在这个市民表上创建一个 city 和 name 的联合索引，依据 索引下推 原理，怎name字段天然自增排序

那么我们在执行查询语句的时候过程：

1. 从索引 (city,name) 找到第一个满足 city='杭州’条件的主键 id；
2. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回；
3. 从索引 (city,name) 取下一个记录主键 id；
4. 重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束。

2、覆盖索引

以上步骤可以通过 覆盖索引 继续优化，通过建立联合索引ity、name 和 age，使得查询无需回表。

这样整个查询语句的执行流程就变成了：

1. 从索引 (city,name,age) 找到第一个满足 city='杭州’条件的记录，取出其中的 city、name 和 age 这三个字段的值，作为结果集的一部分直接返回；
2. 从索引 (city,name,age) 取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；
3. 重复执行步骤 2，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束。

并不是说要为了每个查询能用上覆盖索引，就要把语句中涉及的字段都建上联合索引，毕竟索引还是有维护代价的。这是一个需要权衡的决定。

MySQL实战-16【5525】