SQL表定义为
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;
查询城市是'杭州'的所有人的名字,并按照姓名排序返回前1000个人的姓名,年龄
select city,name,age from t where city ='杭州' order by name limit 1000
全字段排序
Extra中的字段 'Using filesort' 表示的就是需要排序, MySQL 会给每个线程分配一块内存用于排序,称为sort_buffer,
city索引示意图
通常情况下,上面SQL语句的执行流程如下:
1.初始化sort_buffer,确定放入name,city,age这三个字段;
2.从索引city找到第一个满足city = '杭州' 条件的主键id,也就是图中的ID_X
3.到主键id索引取出整行,取name,city,age三个字段的值,存入sort_buffer中;
4.从索引city取出下一个记录的主键id;
5,重复步骤3,4直到city的值不满足查询条件为止,对应的之间id也就是图中的ID_Y;
6.对sort_buffer中的数据按照字段name做快速排序;
7,按照排序结果取前1000行返回给客户端.
图中,"按name排序"这个动作,可能在内存中完成,也可能需要使用外部排序,这取决于排序所需的内存和参数sort_buffer_size.
sort_buffer_size,就是MySQL为排序开辟的内存(sort_buffer)的大小,如果要排序的数据量小于sort_buffer_size,排序就在内存中完成.但如果排序数据量太大,内存放不下,则不得不利用磁盘临时文件辅助排序.
外部排序一般使用归并排序算法,可以这么简单理解,MySQL将需要排序的数据分成12份,每一份单独排序后存在这些临时文件中.然后把这12个有序文件再合并成一个有序的大文件
如果sort_buffer_size超过了需要排序的数据量的大小,number_of_tmp_files就是0,表示排序可以直接在内存中完成.
否则就需要放在临时文件中排序.sort_buffer_size越小,需要分成的份数越多,number_of_tmp_files的值就越大.
sort_mode 里面的packed_additional_fields的意思是,排序过程对字符串做了"紧凑"处理,即使name字段定义是varchar(16),在排序过程中还是要按照实际长度来分配空间的.
rowid排序
在上面这个算法过程中,只对原表的数据读了一遍,剩下的操作都是再sort_buffer和临时文件中执行的,但是如果查询要返回的字段很多的话,那么sort_buffer里面要放的字段数太多,这样内存里能够同时放下的行数很少,需要分成很多临时文件,排序的性能会很差,
这个时候MySQL认为如果单行长度很大会换一个算法即rowid排序
新的算法放入sort_buffer的字段,只有要排序的列(即name字段)和主键id
但这时,排序的结果就因为少了city和age字段的值,不能直接返回了,整个执行流程就会变成如下的样子:
1 初始化sort_buffer,确定放入两个字段,即Name和id;
2 从索引city找到第一个满足city = '杭州'条件的主键id,也就是图中的id_x;
3 到主键id索引取出整行,取name,age这两个字段, 存入sort_buffer中;
4 从索引city取下一个记录的主键Id;
5重复步骤3,4直到不满足city='杭州'条件为止,也就是图中的id_y;
6 对sort_buffer中的字段安装name进行排序;
7 遍历排序结果,取1000行,并按照Id的值回到原表中取出city,name和age三个字段返回给客户端.
这个执行流程中的示意图如下 我们称它为rowid排序
