一篇文章搞懂MySQL的order by

准备工作！

1.本文章MySQL使用的是5.7，引擎使用的是innodb
2. 使用的表结构（user）

3.MySQL配置文件sort_buffer大小

1. 全字段排序

SQL语句

> explain select first_name, last_name,score,copy_id 
> from user 
> where first_name='王' 
> order by copy_id limit 10000; 
1
2
3
4

执行结果

possible_keys: 可能用到的索引
key：实际用到的索引
Extra中的Using filesort 表示需要排序,

运行流程
首先我建立了一个first_name索引，所以在上图可以看到使用了first_name索引。
SQL语句执行流程：

MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

1.初始化 sort_buffer，确定放入 first_name, last_name,score,copy_id 这四个字段；

2.从索引 first_name找到第一个满足first_name=‘王’ 条件的主键 id；

3.到主键 id 索引取出整行，first_name, last_name,score,copy_id 这四个字段，存入sort_buffer 中；

4.从索引 first_name取下一个记录的主键 id；

5.重复步骤 3、4 直到 first_name的值不满足查询条件为止；

6.对 sort_buffer 中的数据按照字段 copy_id 做快速排序；按照排序结果取前 10000 行返回给客户端。

sort_buffer

如上就是全字段排序，通过sort_buffer做块速排序。但是sort_buffer它也有一定的大小，就和上面一样，我把我的sort_buffer_size 设置成了32kb。

sort_buffer_size：就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

如何确定是否使用了磁盘文件来进行排序呢？可以使用以下这几个命令

## 打开optimizer_trace，开启统计
set optimizer_trace = "enabled=on";
## 执行SQL语句
 select first_name, last_name,score,copy_id from user where first_name='王' order by copy_id limit 10000; 
## 查询输出的统计信息
select * from information_schema.optimizer_trace 
1
2
3
4
5
6

运行结果

“filesort_execution”: [
],
“filesort_summary”: {
“rows”: 5851, // 预计扫描行数
“examined_rows”: 5851, // 扫描行数
“number_of_tmp_files”: 7, // 临时文件数量
“sort_buffer_size”: 32728,
“sort_mode”: “” //排序过程对字符串做了“紧凑”处理
}

number_of_tmp_files 表示的是，排序过程中使用的临时文件数。你一定奇怪，为什么需要 7 个文件？内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。可以这么简单理解，MySQL 将需要排序的数据分成 7份，每一份单独排序后存在这些临时文件中。然后把这7个有序文件再合并成一个有序的大文件。

2. rowid 排序

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

比如下面的这个一个SQL，select后面要查询很多字段。

select q,w,e,r,t,y,u,i,o,a,s,d,g,h,j,k
from test 
where A='22' order by b limit 10000;
1
2
3

此时如果采用全字段排序，就会导致sort_buffer_size中能够存放的数据行会很少，导致使用大量的临时文件来做归并排序。 所以这样的情况下，全字段排序对这样的查询没有什么优势。

max_length_for_sort_data

max_length_for_sort_data表示MySQL用于排序行数据的长度的一个参数，如果单行的长度超过这个值，MySQL 就认为单行太大，就换rowid 排序

怎么计算单行长度？
单行长度是根据你查询的数据大小
比如我的数据库表结构 first_name字段varchar（10）、 last_name字段varchar（10）
如果我 SQL查询first_name，last_name两个字段，那么我的行数据大小就是20.

// 获取max_length_for_sort_data大小
show variables like 'max_length_for_sort_data';
// 设置max_length_for_sort_data大小
SET max_length_for_sort_data = 10;
1
2
3
4

在上图中，我把max_length_for_sort_data 设置成10让它走rowid 排序，执行同样的SQL语句

## 打开optimizer_trace，开启统计
set optimizer_trace = "enabled=on";
## 执行SQL语句
 select first_name, last_name,score,copy_id from user where first_name='王' order by copy_id limit 10000; 
## 查询输出的统计信息
 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

1
2
3
4
5
6
7

“filesort_execution”: [
],
“filesort_summary”: {
“rows”: 5851, // 预计扫描行数
“examined_rows”: 5851, // 实际扫描行数
“number_of_tmp_files”: 3, // 临时文件数量
“sort_buffer_size”: 32768,
“sort_mode”: “” // 使用rowid进行排序
}

大家仔细点可以发现，同样是扫描5851行，这里的临时文件是3个，上面使用的临时文件是7个，为什么会这样？
SQL语句执行流程：

因为上面全字段排序sort_buffer确定的是first_name, last_name,score,copy_id四个字段，而这里rowid排序确定的是copy_id 和 id，大大的减少sort_buffer的大小。

1.初始化 sort_buffer，确定放入两个字段，即 copy_id 和 id；

2.从索引 first_name找到第一个满足 first_name='王’条件的主键 id；

3.到主键 id 索引取出整行，取 copy_id 、id 这两个字段，存入 sort_buffer 中；

4.从索引 first_name 取下一个记录的主键 id；

5.重复步骤 3、4 直到不满足 first_name='王’条件为止；

6.对 sort_buffer 中的数据按照字段 copy_id 进行排序；

但是同样的操作rowid 排序会多一次回表操作。

7.遍历排序结果，并按照 id 的值回到原表中取出first_name, last_name,score,copy_id四个字段返回给客户端。

3.使用order by一定就会排序吗

使用order by不一定会进行排序。如果我们查询出来的数据是有序的，那是不是就不用排序了呢？
首先我建立了一个联合索引 first_name（first_name, copy_id）

 alter table test add index first_name(first_name, copy_id);
1

大家也知道，MySQL的InnoDB引擎使用的数据结构是B+树，而B+树它支持范围搜索，并且它的叶子节点也是有序的。

EXPLAIN SELECT
	first_name,last_name,score,copy_id 
FROM  USER 
WHERE
	first_name = '王' 
ORDER BY copy_id LIMIT 10000;
1
2
3
4
5
6

执行流程

1.从索引 (first_name, copy_id) 找到第一个满足 first_name = '王’条件的主键 id；

2.到主键 id 索引取出整行，取 first_name,last_name,score,copy_id 四个字段的值，作为结果集的一部分直接返回；

3.从索引 (first_name, copy_id) 取下一个记录主键 id；

4.重复步骤 2、3，直到查到第 10000 条记录，或者是不满足 first_name = '王’条件时循环结束。

运行结果，未进行排序

如果想在进去一步优化，那就可以在使用到我们的覆盖索引

覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。也就是不用在回表

EXPLAIN 
select first_name, copy_id 
from user 
where first_name='王' order by copy_id limit 10000; 
1
2
3
4

执行流程

1.从索引 (first_name, copy_id) 找到第一个满足 first_name = '王’条件的主键 id；

2.从索引 (first_name, copy_id) 取下一个记录主键 id；

3.直到查到第 10000 条记录，或者是不满足 first_name = '王’是循环结束，并返回索引树上记录first_name和copy_id的值。

运行结果未进行排序，“Using index”使用了覆盖索引

总结：
1.如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

2.如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。