MySQL中order by的工作原理

涉及概念：

sort_buffer、全字段排序、rowid排序、联合索引查询结果排序问题、覆盖索引

以下文章都考虑一个场景：

• 查询一张表中城市字段是“杭州”的所有人的名字，并且按照姓名排序返回前 1000 个人的姓名、年龄。

ID为主键，且在姓名字段上加普通索引，对应的sql语句如下：

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

全字段排序:

全字段排序就是一次性将数据行中被select的字段值都读取出来，本来可以直接返回到结果集，但是由于需要对结果按照某个字段排序，所以要先将所有查询的结果放到一张临时表中（内存中开辟该临时表），再对每一行的数据进行排序，将排序后的临时表取出1000行放入结果集。（ps：如果内存中放不下未排序的临时数据，就要在磁盘中建立多个文件存放这些数据，然后采用归并排序对每个文件分别排序，最终将所有文件放在一个大文件中进行整体排序，再从中取出前1000数据返回给结果集，这里访问磁盘会造成磁盘io，性能会变差）
对应流程如下：
读所有查询字段 —放入临时表（内存/磁盘）— 临时表排序 — 返回排序后的结果集

结合自己总结的一张图

rowid排序

rowid排序就是开始只读取主键ID和对应的排序字段的值，作为一行放入到临时表（还是在内存中建立临时表）中。然后对临时表进行排序，最后按照临时表中的顺序读取前1000行的ID值，再根据主键索引到主表中（回表io）查找ID对应行的所有待查字段放入结果集。（这样的话在内存中临时表只需要存放两个字段的值，尽可能的不用到磁盘中存放临时数据，减少了MySQL的磁盘io，性能可以提升，但是这里会回表查1000行数据，也需要额外的时间）
对应的流程如下：
仅读取主键ID和需要排序的字段 — 放入临时表（内存） —临时表排序 —读取临时表中前1000条记录的ID值回表查所有需要查询的字段值 —放入结果集

结合自己画的流程图

ps：如何选择使用全字段排序还是rowid排序

只要记住一个原则，MySQL优先使用内存，然后再尽量减少io，所以当内存够用时使用全字段排序，可以不产生回表io，当内存不够时使用rowid，可以不用磁盘空间。

联合索引和覆盖索引加速查询

补充一点关于查询结果排序的知识

1. 如果查询时没有走索引，或者走的是普通索引，那么结果按照主键id升序排列
2. 如果查询时走的是联合索引，那么会按照联合索引从左到右的字段顺序升序排序

比如这里用联合索引(city, name, age)，对应这条sql语句就是覆盖索引，那么根据主键ID查主表时得到的结果就是按照city、name、age排序的（相同city数据行按照name升序排，相同city且相同name的数据行，按照age字段升序排）。

对于本sql语句，第一次主表查到的数据city字段值相同，所以结果都是按照name字段升序排序的，于是就省略了order by name语句对结果进行重复排序。同时覆盖索引可以直接从第一次主表查询的节点中取到所有需要的值。

参考文章：

https://time.geekbang.org/column/article/73479