mysql45讲学习总结（五）—排序

本篇记录MySQL在执行order by语句时系统的具体执行流程，以及在具备索引的情况下，索引的选择情况。

全字段排序

select name, age, address from t where name = ‘xxx’ order by age limit 1000 ;

使用explain分析该语句：

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	bid_confirm_project		ALL					4033	10	Using where; Using filesort

Using filesort：表示需要排序。MySQL会给每个线程分配一块sort_buffer内存空间用于排序。

假设在name上有索引排序流程：

初始化sort_buffer空间，确定将用于存入字段name、age、address的值。
从索引name上找到第一个满足xxx条件的id。
根据这个id通过主键索引获取整行数据，将name、age、address的值存入到sort_buffer中。
从索引name取下一个记录的主键。
重复3,4步骤，直到从索引name取到的值不满足查询条件为止。
对sort_buffer空间的数据按照age进行排序。
返回结果集数据。

步骤6中按照age进行排序：这个可能在内存中完成，也有可能需要使用到外部排序。这取决于排序所需要的内存空间大小和参数sort_buffer_size。如果需要排序的数据量小于sort_buffer_size则使用内存空间进行排序，如果需要排序的数量大于sort_buffer_size则需要使用磁盘临时文件辅助排序。

仅仅使用explain只能分析改语句是否需要排序，至于排序是在内存中排序还是在磁盘中排序就无法得知，需要使用其他手段。MySQL版本需要到5.6以上。

-- 打开 optimizer_trace，只对本线程有效
SET optimizer_trace='enabled=on'; 

-- 执行语句
select * from xxx ;

-- 查看 OPTIMIZER_TRACE 输出 
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`

查询的结果是一个json结果：(json结果比较大，截取一段)

"filesort_summary": {
  "rows": 4250,  
  "examined_rows": 4291, // 排序的行数
  "number_of_tmp_files": 11,   // 排序过程中使用的临时文件数，如果在内存中排序，则这个值为0
  "sort_buffer_size": 261696,  // 就是上面说的排序的空间大小，这个是可以通过命令调整
  "sort_mode": "<sort_key, rowid>"
}

rowid排序

假设查询语句变为：

select * from t where name = ‘xxx’ order by age limit 1000 ;

对比可知，仅仅是将查询结果的字段修改为了*，这时在步骤1中就需要为很多无需排序的字段开辟空间，那么就会造成sort_buffer_size空间中单行的数据比较长，那么MySQL会怎么做？

实际上当MySQL判断单行数据过长时，它会修改sort_buffer_size存放值的策略，之前全字段排序时sort_buffer存放的字段为name、age、address，而如果单行数据过长时，sort_buffer存储的字段就变成id、age，排序完成之后，通过主键id索引进行一次回表，也就是说比全字段排序多了一个步骤，在排序完成之后需要进行一次回表。

同时在刚刚的optimizer_trace的json中的sort_mode项中会标识出是否采用了rowid算法。通过参数max_length_for_sort_data控制单行数据长度。

全字段排序对比rowid排序

如果MySQL认为内存足够大，会优先选择全字段排序，如果认为排序内存太小，影响排序效率，则会采用rowid排序算法，这样排序过程中一次可以排多行，但是需要再回表取相关字段数据。

排序对于MySQL来说是一个成本比较高的操作，并不是所有的order by都需要排序操作，之所以需要排序操作，是因为查到的数据是无序的，所有才需要进行排序操作，但是在MySQL中有一种方式是天然排序的，那就是索引，可以通过创建对应索引，使得查询到的数据已经是有序，那么就无需在经过排序操作。这种情况下explain得到的结果中就没有filesort。

临时表

内存临时表

CREATE TABLE words (
id int(11) NOT NULL AUTO_INCREMENT,
word varchar(64) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
declare i int;
set i=0;
while i<10000 do
insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))));
set i=i+1;
end while;
end;;
delimiter ;

call idata();

然后执行以下语句用于获取随机的前三个数据，这里的order by rand()，会对每一行的数据都生成一个随机值，然后根据这个随机值进行排序，最后取得前三行数据。

select word from words order by rand() limit 3;

使用explain命令执行结果如下：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	words	ALL					10304	Using temporary; Using filesort

Using temporary表示需要使用到临时表，这里是因为需要为每一行生成一个随机值进行排序，所以需要一个临时表存储生成的这个随机值。

对于内存临时表来说，会选用那种算法存放数据？是全字段索引的算法？还是rowid算法？。
答案是使用rowid算法，因为对内存临时表来说，回表只是简单的根据数据行的位置直接访问到数据，不会导致访问磁盘，所以这时采用的是rowid排序。

创建一个临时表。这个临时表使用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，为了后面描述方便，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引。
从 words 表中，按主键顺序取出所有的 word 值。对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中，到此，扫描行数是 10000。
现在临时表有 10000 行数据了，接下来你要在这个没有索引的内存临时表上，按照字段 R 排序。
初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型。
从内存临时表中一行一行地取出 R 值和位置信息（我后面会和你解释这里为什么是“位置信息”），分别存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加 10000，变成了 20000。
在 sort_buffer 中根据 R 的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。
排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出 word 值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003。

order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。

磁盘临时表

当需要到临时表的空间比较大时，超过了tmp_table_size的值，那么内存临时表会转换为磁盘临时表。
在磁盘临时表中还有一种优化算法：优先队列排序算法。对于这个查询语句，实际上只需要取值最小的3个值，但是如果使用归并排序的话，是将所有数据都排序了，所以实际上浪费了很大的计算量。所以这里MySQL提供了优先队列排序算法：
先取3行数据构建一个堆，再取下一行数据，与这个堆的最大值进行比较，如果大则丢弃，如果小则替换，依次执行，知道扫描完整个表。

如果需要的空间大于sort_buffer_size则采用磁盘临时表，通过磁盘临时表进行排序。

xiaocainiaoya's blog

mysql45讲学习总结（五）---排序

mysql45讲学习总结（五）—排序

全字段排序

rowid排序

临时表

内存临时表

磁盘临时表