order by 是怎样工作的

一图胜千言

rowid排序

排序的时候，只将 order by 后面排序字段以及记录主键放入到 sort buffer 中。比如 select a，b，c from t order by a desc，

只将字段a，表主键id 放入 sort buffer 中

全字段排序

排序的时候，将select sql中所有字段都放入到 sort buffer 中一起参与排序，无需根据rowid 再去主键索引上获取记录。

比如 select a，b，c from t order by a desc，只将 a，b，c 三个字段让如sort buffer中，如果表 t 还有字段 d，字段 d 是不会被放入 sort buffer 中

sort_buffer

属于 server层，MySQL 会给每个线程（也就是每一个 session）分配一块内存用于排序，称为 sort_buffer。可以使用 sort_buffer_size 进行设置。

max_length_for_sort_data

全字段排序的最大行数据，默认值是 1024 字节。

怎么做比较的，需要排序的数据字段的定义大小的和大于max_length_for_sort_data，就使用 “rowid排序”，反之使用“全字段排序”。

例如：select a，b，c from t order by a desc，a，b，c 数据类型都是 varchar(8) max_length_for_sort_data 配置的是 16

a，b，c 字段定义之和是24 大于 16 就使用“rowid排序”

需要说明的是，如果是 rowid 排序，在排序完成后需根据排好序的 rowid，去主键索引获取记录，也就是需要回表操作

举例一条 order by sql的执行流程

CREATE TABLE `t2` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_a` (`a`) ) ENGINE=InnoDB;

在表 t2 中插入数据 1000 条数据

for(int i=1; i<=1000; i++) { insert into t2(id, a, b) values(i, i%100, i); }

sql select a, b from t2 where a = 10 order by b;

这里假设使用的是“全字段排序”，并且需要排序的数据都可以放入 sort buffer 中。

执行流程如下

1. 初始化 sort_buffer；
2. 从索引 a 找到第一个满足 a=10 条件的主键 id；
3. 到主键 id 索引取出整行，取 a, b 两个字段的值，存入 sort_buffer 中；
4. 从索引 a 取下一个记录的主键 id；
5. 重复步骤 3、4 直到 a 的值不满足查询条件为止；
6. 对 sort_buffer 中的数据按照字段 b 做快速排序；
7. 返回数据给客户端

有个疑问，从执行流程看，扫描行数应该是10行，为什么是 10行，因为sql执行的时候会踩中字段a的索引，符合a=10的记录只有10行，所以扫描行数是 10 行。但是通过观察慢日志，实际扫描行数却是 20 行，为什么？？一直没搞明白。

如何验证 order by 是否使用了临时表

通过以sql命令，验证是否使用了临时表

/* 打开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 表示的是，排序过程中使用的临时文件数。内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。

可以这么简单理解，MySQL 将需要排序的数据分成 312 份，每一份单独排序后存在这些临时文件中。然后把这 312 个有序文件再合并成一个有序的大文件。

如果 sort_buffer_size 超过了需要排序的数据量的大小，number_of_tmp_files 就是 0，表示排序可以直接在内存中完成。

否则就需要放在临时文件中排序。sort_buffer_size 越小，需要分成的份数越多，number_of_tmp_files 的值就越大。

filesort_priority_queue_optimization 这个部分的 chosen=true，就表示使用了优先队列排序算法

优先队列算法简单介绍

假设表 t 有 一万条记录，执行sql select a, b, c from t order by a asc limit 3 假设sql执行的时候选择了，rowid排序，并且使用的是优先队列排序算法

优先队列算法，就可以精确地只得到 3 个最小值，执行流程如下：

1：对于这 10000 个准备排序的 (R,rowid)，先取前三行，构造成一个堆；

（对数据结构印象模糊的同学，可以先设想成这是一个由三个元素组成的数组）

2：取下一个行 (R’,rowid’)，跟当前堆里面最大的 R 比较，如果 R’小于 R，把这个 (R,rowid) 从堆中去掉，换成 (R’,rowid’)；

3：重复第 2 步，直到第 10000 个 (R’,rowid’) 完成比较。

通过优先队列排序找到最小的三个 R 值的行的过程。整个排序过程中，为了最快地拿到当前堆的最大值，总是保持最大值在堆顶，因此这是一个最大堆。

内存表排序

在内存表上进行排序，使用的是 rowid 排序。为什么不选择全字段排序，而选择 rowid 排序

如果使用 rowid 排序，执行流程是这样的

• rowid排序的时候，将需要排序的字段 和 数据行的位置（数组中的下标）放入sort buffer 中。
• 回表过程对于内存表来说就是根据 数据行的位置（数组中的下标），直接访问内存得到数据，根本不会导致多访问磁盘。
• rowid 排序，可以一次性放入sort buffer中的数据就更多，避免创建临时文件或者减少临时文件的数量，

综上所述，内存临时表上进行排序会选择 rowid排序，参数 max_length_for_sort_data 对内存表的排序是起不到作用的

数组下标：memory引擎的表，数据是保存在数组中的，数据在数组中的排列顺序是数据的插入顺序