MySQL13-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;

假设按照下面的sql查询并排序。

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

全字段排序

避免全表扫描， 我们需要在city字段加上索引。

在city字段上创建索引之后， 我们用explain命令来看看这个语句的执行情况。

Extra中"Using filesort"表示排序，mysql会给每个线程分配一个块内存（sort_buffer）用来排序。city索引示意图：

sql执行过程：

1. 初始化sort_buffer，确定放入name、city、age 这三个段；
2. 从city索引找到第一个city='杭州’的主键id，图中的ID_X；
3. 根据id去聚集索引取这三个字段，放到sort_buffer；
4. 在从city索引取下一个；
5. 重复3、4查询所有的值；
6. 在sort_buffer按name快速排序；
7. 按照排序结果取前1000行返回给客户端。

如果sort_buffer太小，内存放不下排序的数据，则需要使用外部排序，利用磁盘临时文件辅助排序。这取决于排序所需内存和参数 sort_buffer_size。

下面方法可以确定排序是否使用临时文件：

/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 
/* @a 保存 Innodb_rows_read 的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000; 
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
/* @b 保存 Innodb_rows_read 的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 计算 Innodb_rows_read 差值 */
select @b-@a;

通过查看 OPTIMIZER_TRACE，number_of_tmp_files表示排序使用的临时文件数，外部排序一般使用归并排序算法。
rows表示满足city='杭州’有4000条，examined_rows=4000表示4000行参与排序。
sort_mode packed_additional_fields表示排序过程字符串做了“紧凑”处理。name字段定义varchar(16)，排序过程中按照实际长度分配空间。
最后一个查询语句 select @b-@a返回结果是 4000，表示只扫描了4000行。

这边老师把internal_tmp_disk_storage_engine 设置成MyISAM，否则，select @b-@a结果为 4001。因为innodb把数据从临时表取出来时，会让Innodb_rows_read 的值加 1。

rowid 排序

在上面这个算法过程里面， 只对原表的数据读了一遍， 剩下的操作都是在sort_buffer和临时文件中执行的。 但这个算法有一个问题， 就是如果查询要返回的字段很多的话， 那么sort_buffer里面要放的字段数太多， 这样内存里能够同时放下的行数很少， 要分成很多个临时文件， 排序的性能会很差。如果排序的单行长度太大mysql会使用另一种算法。

那么， 如果MySQL认为排序的单行长度太大会怎么做呢？

SET max_length_for_sort_data = 16;

max_length_for_sort_data， 是MySQL中专门控制用于排序的行数据的长度的一个参数。 它的意思是， 如果单行的长度超过这个值， MySQL就认为单行太大， 要换一个算法。

city、name、age 这三个字段的定义总长度是 36 > max_length_for_sort_data，所以会使用别的算法。

新的算法放入sort_buffer的字段， 只有要排序的列（ 即name字段） 和主键id

但这时， 排序的结果就因为少了city和age字段的值， 不能直接返回了， 整个执行流程就变成如下所示的样子：

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

这个执行流程的示意图如下， 我把它称为rowid排序。

对比图3的全字段排序流程图你会发现， rowid排序多访问了一次表t的主键索引， 就是步骤7。

需要说明的是， 最后的“结果集”是一个逻辑概念， 实际上MySQL服务端从排序后的sort_buffer中依次取出id， 然后到原表查到city、 name和age这三个字段的结果， 不需要在服务端再耗费内存存储结果， 是直接返回给客户端的

我们看一下执行select @b-@a， 结果会发生什么变化？

• 首先， 图中的examined_rows的值还是4000， 表示用于排序的数据是4000行。 但是select @b-@a这个语句的值变成5000了。
• 因为这时候除了排序过程外， 在排序完成后， 还要根据id去原表取值。 由于语句是limit 1000， 因此会多读1000行。
• sort_mode变成了<sort_key, rowid>， 表示参与排序的只有name和id这两个字段。
• number_of_tmp_files变成10了， 是因为这时候参与排序的行数虽然仍然是4000行， 但是每一行都变小了， 因此需要排序的总数据量就变小了， 需要的临时文件也相应地变少了。

全字段排序与rowid排序如何选择

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

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