MySQL 浅析order by 如何执行

Mysql会给每个线程分配一块内存用于排序，称为sort_buffer

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

全字段排序

对于这样的一条语句，有索引city，MySQL的order by默认是这样执行的：

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

这种方法我们暂且称之为全字段排序。

值得注意的是，sort_buffer_size参数是MySQL为排序开辟的内存大小，如果需要排序的数据量小于sort_buffer_size，就直接内存排序，否则借助磁盘临时文件进行外部排序，外部排序一般使用归并排序算法。

高枕无忧了？ 并不，可以看出来全字段排序是把需要查询的数据都干进sort_buffer，如果想要的字段过多，那么就会利用磁盘临时文件，并且会有很多个临时文件，排序的性能会很差。

RowId排序

max_length_for_sort_data 是MySQL用来控制单行长度的一个参数，如果单行数据大于这个参数，那么MySQL就会采用另外一个算法：

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

比较全字段排序和RowId排序

• 如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

• 如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

并不是所有的order by都需要排序

索引之后就能保证是有序的，如果需要查询name和city字段，city是带索引的

如果给name和city加上联合索引，那么就不需要排序了，本身就是有序的。

这其实就是覆盖索引的概念：如果索引上的信息满足查询要求，就不要再回到主键索引上去取数据了，即不需要再回表了。