MySQL简单了解“order by”是怎么工作的

针对排序来说，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;

这时我们写一条查询语句

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

根据上面的表定义来看，city=xxx 可以使用到我们定义的一个索引。但是 order by name 明显我们没有索引，所以肯定需要先用索引查询到 city=xxx 然后再进行回表查询，最后再排序。

全字段排序

在 city 字段上面创建索引之后，我们使用执行计划来查看这个语句

可以看到有索引的情况下我们这里还是使用了 "Using filesort" 表示需要排序，MySQL 会给每个线程分配一块内存用于排序称为 sort_buffer。

我们在执行上面 select 语句的时候通常经历了这样一个过程

1. 初始化 sort_buffer, 确认放入 name, city, age 这三个字段。

2. 从索引 city 找到第一个满足 city='杭州'条件的主键 id。

3. 回表取到 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 的默认值是1048576 byte 也就是 1M,如果要排序的数据量小于 1m 排序就在内存中完成，如果排序数据量大，内存放不下，则使用磁盘临时文件辅助排序。

Rowid 排序

如果单行很大，需要的字段全部放进 sort_buffer 效果就不会很好。

MySQL 中专门用于控制排序的行数据长度有个参数 max_length_for_sort_data 默认是1024，如果超过了这个值就会使用 rowid 排序。那么执行上面语句的流程就变成了

1. 初始化 sort_buffe 确定放入两个字段即 name 和 id 。

2. 从索引 city 找到第一个满足 city = '杭州'条件的主键 id。

3. 回表取 name 和 id 两个字段存入 sort_buffer 中。

4. 取下个满足条件的记录重复 2 3 步骤。

5. 对 sort_buffer 中的 name 进行排序。

6.遍历结果取前 1000 行。然后按照 id 再回一次表取的结果字段返回给客户端。