MYSQL45讲学习笔记

16. order by 是怎么工作的

相信大家在日常开发中，经常会遇到一些需要排序的业务，以一个市民表为例，假设你要查询城市是“杭州”的所有人名字，并且按照姓名排序返回前 1000 个人的姓名、年龄。

假设市民表定义如下：

create table t(
id int AUTO_INCREMENT,
name varchar(20),
age varchar(3),
city varchar(10),
addr varchar(120),
primary key ('id'),
key 'idx_city'('city')
)engine = innodb;

这时SQL语句可以这样写：

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

那么这条语句在MYSQL内部是怎样执行的呢？我们一起来看一下。

全字段排序

我们可以使用explain命令看下这条语句的执行计划：

using filesort 表示需要排序，MYSQL会给每个线程分配一块内存sort_buffer来进行排序。

SQL执行过程如下：

1.初始化sort_buffer，确定放入city,name,age三个值。

2.在city索引上查找到city为‘杭州’的记录，拿到主键id，

3.根据id在主键索引树上查找到全部行记录，拿到city,name,age,将其放入sort_buffer，

4.查找city索引下一条为‘杭州’的记录，拿到主键id，

5.重复3、4步骤，知道city索引下一条记录不是‘杭州’为止，

6.sort_buffer根据name对数据进行快速排序，

7.取排序结果前1000行返回给客户端，

这个排序的过程我们称之为 全字段排序，流程图如下图所示：

其中对name进行排序的过程，可能在内存中完成，也可能需要外部存储辅助排序。其取决于排序需要的内存大小和参数sort_buffer_size。

当排序所需内存小于sort_buffer_size时，就会用到外部排序，外部排序使用归并排序算法。

如何判断排序是否使用磁盘临时文件？

这部分还有点疑惑

rowid 排序

采用全字段排序时，如果一个行数据量很大，那么sort_buffer可能放不下，会用到比较多的磁盘临时文件，这样会影响查询效率。

max_length_for_sort_data，是MYSQL专门用来判断单行数据是否过大的一个参数，如果单行数据大于这个值，则会采用另一种排序算法。

对于这种单行数据量比较大的查询，MYSQL会采用另外一种排序算法--rowid排序。

新的排序算法在sort_buffer中只放入 主键id和要排序的字段，使sort_buffer可以放入更多的行数据。

此处体现了MYSQL设计的原则：尽量多用内存，能在内存中完成的就尽量用内存完成。

rowid排序的过程如下：

1.初始化sort_buffer,确定放入 name、id 两个字段，

2.在city索引上查找到city为‘杭州’的记录，拿到主键id，

3.根据主键id找到全部行记录，拿到id、name，将其放入sort_buffer中，

4.找到下一行city为‘杭州’的记录，拿到主键id，

5.重复3、4步骤，直到下一个city不为’杭州‘的记录为止，

6.对sort_buffer中的数据根据name进行排序，

7.对排序后的数据去主键索引拿到age，然后将city、name、age作为结果集的一部分直接返回，直到拿到1000条数据为止。

我们可以看到 rowid排序比起全字段排序多了一次回表的过程。

全字段排序 VS rowid排序

1.如果MYSQL认为内存足够大，则会优先使用全字段排序，将所需要的数据都放入sort_buffer中，排序之后直接返回，不需要再次访问磁盘。

2.如果MYSQL认为内存不够大， 放不下需要排序的数据，则会采用rowid排序，这样一次可以排序更多数据，但是需要回表查询数据，多访问一次磁盘。

其实也并不是所有的 order by 语句需要排序，只有在数据无序的时候才需要排序，那么对于上面这种场景， 我们可以建立一个复合索引来进行优化，

alter table t add index idx_city_name (city,name);

这样查询语句的执行过程就变成了这样：

1.在索引(city,name)上找到第一条city为‘杭州’的记录， 拿到主键id，

2.根据主键id拿到city、name、age 这三个数据，作为结果集的一部分直接返回，

3.在索引(city,name)上找到下第一条city为‘杭州’的记录， 拿到主键id，

4.重复步骤2、3，直到下一条city不为‘杭州’的记录，或者满1000条数据为止。

我们可以使用explain验证一下：

可以看到，Extra中没有了using filesort,说明不需要排序了。

对于这条查询语句，我们还可以使用覆盖索引来继续进行优化，

alter table t add index idx_city_name_age (city,name,age);

这样，查询语句的执行过程就变成了这样：

1.在索引(city,name,age)上找到第一条city为‘杭州’的记录，拿到city、name、age,作为结果集的一部分直接返回，

2.在索引(city,name,age)上找到下一条city为‘杭州’的记录，拿到city、name、age,作为结果集的一部分直接返回，

3.重复步骤2，直到直到下一条city不为‘杭州’的记录，或者满1000条数据为止。

当然并不是说我们的所有场景都要使用覆盖索引，还需具体场景具体分析，权衡利弊。

小结

本文讲述了全字段排序和rowid排序的执行过程，一般情况下MYSQL会优先使用全字段排序，当需要排序的数据量比较大时，会采用rowid排序。