order by 是怎么工作的

假设现在有一个表，需要查询城市为“江门”的所有人的名字，并按照姓名排序返回前 1000 个人的姓名、年龄

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

一条可行的 SQL 如下： select city, name, age from t where city='江门' order by name limit 1000

全字段排序

为了避免全表扫描，在 city 字段上加了索引。此时，这条 SQL 需要进行排序，因为 name 是无序的，大致的执行过程如下：

初始化一块内存 sort_buffer 用于排序，放入 name、age、city 这三个字段
从索引 city 中找到第一个满足条件的主键 id
到主键 id 索引中取出整行，取 name、age、city 这三个字段放入 sort_buffer
从索引 city 中取下一个记录的主键 id
重复上述过程，直到 city 不满足查询条件
对 sort_buffer 中的数据按字段 name 做快速排序
按照排序结果返回前 1000 条

如果排序的数据量过大，sort_buffer 放不下，此时就会利用磁盘的临时文件辅助排序，也称外部排序，外部排序一般使用归并排序算法。每一个临时文件都是排好序的文件，最后将这若干个有序文件合并成一个大文件

rowid 排序

在全字段排序中，只读了一次原表的数据，剩下的操作都是在 sort_buffer 和临时文件中执行的。但如果查询返回的字段很多，内存放不下，要分成多个临时文件，排序的性能就会下降我们可以通过修改参数，让 MySQL 使用另外一种算法

max_length_for_sort_data：控制排序的行数据的长度。如果单行的长度超过这个值，就换一种算法

该算法不需要将所有返回的字段放入 sort_buffer，只需排序字段和主键 id，大致的流程如下：

初始化 sort_buffer，确定放入两个字段，即 name 和 id；
从索引 city 找到第一个满足条件的主键 id
到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
从索引 city 取下一个记录的主键 id；
重复上述过程，直到 city 不满足查询条件为止
对 sort_buffer 中的数据按照字段 name 进行排序；
遍历排序结果，取前 1000 条，并按照 id 的值回表中取出其他字段

相比于全字段排序，这种排序算法多了一次回表的操作，但是需要的临时文件变少了

总结与实践

从上面的两种算法体现出 MySQL 的一个设计思想：如果内存足够，就尽量多使用内存，减少磁盘访问。rowid 排序需要进行回表操作造成磁盘读，因此不会优先考虑

怎么优雅地排序呢？

利用索引。尽量保证从索引上取出来的行是已经是有序的，避免二次排序。可以考虑覆盖索引，这样取出来的数据就是有序的来，不需要再在内存排序。

针对有多种取值的查询字段，如 city 为“广州”和“深圳”，可以将 SQL 语句拆分成两个分别查询，最后进行一次归并排序

order by 是怎么工作的 ​

全字段排序 ​

rowid 排序 ​

总结与实践 ​

order by 是怎么工作的

全字段排序

rowid 排序

总结与实践