MySQL的“order by”是如何对200G文件进行排序的？

前言

学习编程语言的时候，我们第一个行代码往往是“Hello World”。我们学会的第一个真正意义上的算法，我想就是冒泡排序吧。我们创建一个数组，通过N次比较，每次找出最大或最小的元素，把它放到队列最末尾就完成将无序数组转化成为了有序数组，这些排序都是再内存中进行的。

我们开发系统的时候，经常会有按照某种条件就行排序的需求。你有没有这样的疑问：我们的数据都是存放硬盘上的，如果数据有200G，但是我的内存只有16G，它是怎么排序的？

今天我们带着这个疑问来看一看MySQL中“order by”是如何实现的。

全字段排序

首先我们创建一张表，里面插入4条元素

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) CHARACTER SET utf8 DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `city` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

INSERT INTO `user` VALUES (1, '张三', 18, '成都');
INSERT INTO `user` VALUES (2, '李四', 30, '西安');
INSERT INTO `user` VALUES (3, '王五', 27, '杭州');
INSERT INTO `user` VALUES (4, '赵六', 24, '深圳');

如果我们要按照年龄对用户表进行降序排列只需要执行这条语句

SELECT id, `name`, age from `user` order by age DESC;

通过这条语句我们和容易的就得到了结果，我们查看一下它的执行计划

Extra这个列中“Using filesort”表示需要排序，MySQL会给每一个线程分配一块内存用于排序，这块内存成为sort_buffer，具体怎么执行的我们看下图。

1：初始化sort_buffer，确定需要字段为name，age

2：根据条件获取User表中的数据，存入sort_buffer中

3：对sort_buffer中的数据按照字段age进行快速排序

4：将排序结果的结果集返回给客户端

我们将这个动作暂时称为“全字段排序”，其中第3步“按照age进行快速排序”这个动作是再内存中完成的。但是如果需要排序的数量过大，内存放不下，则这时候就不得不利用磁盘临时文件辅助排序。

sort_buffer_size这个参数就是sort_buffer的大小，如果超出这个值就需要借助文件辅助了。这时候MySQL会将需要排序的数据分成12份，没一个单独排序后存放在这些临时文件中。然后再把这12个有序文件合并成一个有序大文件。

rowid排序

上面算法的执行过程中把所有的数据读取出来，如果可以在内存中排序就使用sort_buffer，如果sort_buffer放不下就借助临时文件。但是又有一个问题，我们需要排序的行数不是特别多，但是要返回的数据行数特别多，这样的话sort_buffer里面要放的字段很多，很快就超出最大内存容量，这时候就退化成临时文件排序性能就会降低很多。

如果单行太大，临时文件排序效率肯定不够好。MySQL中有一个参数max_length_for_sort_data是专门控制排序的行的长度的一个参数。它的意思是说，如果单行的长度超过了这个值，MySQL就认为单行太大，这时候就会换一个算法。

我们name、age定义的长度是28，我们设置max_length_for_sort_data为16，这时候我们的算法就会变成另一种。

 

1：初始化sort_buffer，确定需要字段为id，age

2：根据条件获取User表中的数据，存入sort_buffer中

3：对sort_buffer中的数据按照字段age进行快速排序

4：根据sort_buffer中的id回表取出name、age构造结果集返回

sort buffer中的字段只有需要排序的字段和id，和上个算法相比字段少了占用内存自然就少了，但是和之前相比多了一步回表。而“结果集”也是一个逻辑概念。MySQL服务端从排序后的sort_buffer中依次取出id，然后到原表查询就可以，不再需要服务端再耗费内存，而是直接返回给客户端。

天然有序

如果我们需要返回的数据本身就是有序的话，那么就不需要order by排序了。我们可以再user表上增加age、name的链接索引，

alter table user add index age_name(age, name);

 

这时候我们查看一下执行计划就会发现extra字段中没有Using filesort就是说明不需要排序，而Using index就说明使用了覆盖索引。覆盖索引是指，索引上的信息已经可以满足查询，不需要再回到主键索引上去取数据。

前面基于age/name创建的联合索引它本身有序，所以需不要再把数据读入sort_buffer排序再返回，而是直接返回就可以了。

 

最后，如果感觉对你有帮助就来个二连吧：关注、点赞！