MySQL排序“order by”如何实现

定义表:

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`city` varchar(16) NOT NULL,
`name` varchar(16) NOT NULL,
`age` int(11) NOT NULL,
`addr` varchar(128) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `city` (`city`)
) ENGINE=InnoDB;

待查询的sql语句：  
select city,name,age from t where city='杭州' order by name limit 1000 ;
复制代码

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

通过查看 OPTIMIZER_TRACE，number_of_tmp_files表示排序使用的临时文件数，外部排序一般使用归并排序算法。MySQL 将需要排序的数据分成 12 份，每一份单独排序后存在这些临时文件中。然后把这 12个有序文件再合并成一个有序的大文件

执行过程：

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

该过程称为全字段排序

如果查询返回字段很多的，MySQL会将需要排序的列和主键放入sort_buffer,此时执行流程为：

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

这个过程称为rowid排序,相比较全字段排序，它只取了排序字段和主键再内存中进行排序，排序结束后还需要遍历主键索引树，取出需要查询的值，这个回表的过程比较耗时。

注：

如果order by后面的字段走联合索引，联合索引中的数据有顺序的，比如（city, name, age）三个字段组成联合索引，会按照city排序，再按照name排序，最后按照age排序，这样查询的出的结果集可以直接返回，不需要使用sort_buffer内存进行排序,可以使用explain查看extra属性是否有Using filesort。

本文是极客时间丁奇老师《MySQL45讲》的读书笔记，希望大家前去订阅