六. “order by”是怎么工作的_order by如何工作的-CSDN博客

本文链接：https://blog.csdn.net/ty13572053785/article/details/120917720

假设需要查询所在城市是“杭州的所有人名字，并且按照姓名排序返回前1000个人的姓名，年龄。

表的定义是这样的：

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;

这条语句的执行流程是什么样的呢，我们接下来看看。

全字段排序

在这里插入图片描述

Extra字段中的 "Using filesort"表示的就是需要排序。（MySQL会给每个线程分配一块内存用于排序，称为 sort_buffer）

查询语句的执行流程是这样的：

初始化sort_buffer，放入city，name，age这三个字段。
从索引city找到第一个满足city=‘杭州’条件的主键id，然后到主键id索引取出整行，取city，name，age三个字段的值，存入sort_buffer中。
从索引city中取一下记录的主键id，重复步骤2直到city的值不满足查询条件为止。
对sort_buffer中的数据按照字段name做排序，按照排序结果取前1000行返回给客户端。

整个流程的示意图如下：
在这里插入图片描述

上图中，“按name排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size。如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

rowid排序

上面的算法，排序操作都是在sort_buffer和临时文件中执行的，如果查询要返回的字段很多的话，那么sort_buffer里面放了太多字段，内存里能放下的行数就很少了，要分成多个临时文件，性能会比较差。如果单行很大，这个方法效率不够好。对这种场景，MySQL引入了优化。

set max_length_for_sort_data=16;

max_length_for_sort_data是MySQL中专门用于控制用于排序的行数据长度的一个参数，如果单行的长度超过了这个值，MySQL就认为单行太大，就使用rowid排序。

rowid排序的流程是这样的：

初始化sort_buffer，放入id和排序的字段name。
从索引city中找到第一个满足条件city=‘杭州’的主键id，然后到主键id索引取出整行，取name，id这两个字段，存入sort_buffer中。
从索引city中取下一个记录的主键id，重复步骤2直到不满足查询条件为止。
对sort_buffer中的数据按照字段name进行排序。
遍历排序结果，取前1000行，并按照id的值回到原表中取出city，name和age三个字段返回给客户端。

全字段排序 VS rowid排序

如果MySQL认为内存足够大，会优先选择全字段排序，把需要的字段都放到sort_buffer中，这样就可以直接返回，不用再回到原表去取数据；如果MySQL担心排序内存太小，会影响排序效率，才会使用rowid排序算法。这样排序过程中一次可以排序很多行，但是需要再回到原表中去取数据。MySQL的设计思想是：如果内存够，就要多利用内存，尽量减少磁盘访问。