demo:
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 ;
全字段排序
使用explain命令查看语句执行情况,在Extra字段中的"Using filesort"表示的就是需要排序,Mysql会给每个线程分配一块内存用户排序,称为sort_buffer
通常情况下,这个语句执行流程如下:
1.初始化sort_buffer,确定放入name,city,age这三个字段;
2.从索引city找到第一个满足city='杭州'条件的主键id;
3.到主键id索引取出整行,取name,city,age三个字段的值,存入sort_buffer中;
4.从索引city取下一个记录的主键id;
5.重复步骤3,4知道city的值不满足查询条件为止,对应的主键id也就是图中的ID_Y;
6.对sort_buffer中的数据按照字段name做快速排序;
7.按照排序结果取前1000行返回给客户端.
根据索引找到主键,主键索引去除记录,存入sort_buffer(此时无序),按照name排序,返回结果集
按name进行排序这个动作,可能在内存中完成,也可能需要使用外部排序,这取决于排序所需的内存和参数sort_buffer_size
sort_buffer_size,就是Mysql为排序开辟的内存(sort_buffer)的大小.如果要排序的数据量小于sort_buffer_size,排序就在内存中完成.否则利用磁盘临时文件辅助排序.
外部排序一般使用归并排序算法.Mysql将需要排序的数据分成n份,每一份单独排序后存在这些临时文件中,然后把这n个有序文件在合并成一个有序的大文件.
rowid排序
在上面这个算法过程里面,只对原表的数据读了一遍,剩下的操作都是在sort_buffer和临时文件中处理.但是全字段排序有一个问题,就是如果查询要返回的字段很多的话,那么sort_buffer里面要放的字段数太多,这样内存里能够同时放下的行数就很少,导致分成多个临时文件,排序的性能就很会大打折扣.所以如果单行很大,全字段排序效率不够好.
***max_length_for_sort_data，是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。
这个算法放入sort_buffer得字段,只有要排序得列(name)和主键id.
但是因为结果少了city和age字段得值,不能直接返回,整个执行流程如下:
1.初始化sort_buffer,确定放入两个字段,既name和id
2.从索引city找到第一个满足city='杭州'条件得主键id
3.到主键id索引取出整行,取name,id这两个字段,存入sort_buffer中
4.从索引city取下一个记录得主键id
5.重复3,4直到不满足city='杭州'条件为止
6.对sort_buffer中得数据按照字段name进行排序
7.遍历排序结果,取前1000行,并按照id得值回到原表中取出city,name和age三个字段返回给客户端
***最后的结果集是个逻辑概念,实际上Mysql服务端从排序后得sort_buffer中依次取出id,然后到原表查询到所需字段结果,不需耗费服务端内存存储结果,是直接返回客户端的.
全字段排序 vs rowid排序
如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。
如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。
这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。
对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。
其实并不是所有的order by都需要排序操作的.MySQL 之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。
如果能保证从city这个索引上取出来的行,天然就是按照name递增排序的话,就不用排序操作了
创建一个city和name的联合索引,对应的sql:
alter table t add index city_user(city, name);
在这个索引里面，我们依然可以用树搜索的方式定位到第一个满足 city='杭州’的记录，并且额外确保了，接下来按顺序取“下一条记录”的遍历过程中，只要 city 的值是杭州，name 的值就一定是有序的。
流程如下:
1.从索引(city,name)找到第一个满足city='杭州'条件的主键id
2.到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回
3.从索引 (city,name) 取下一个记录主键 id
4.重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city='杭州’条件时循环结束
因为联合索引本身有序,所以不需要把4000行都查一遍,只要找到满足条件的前1000条记录就可以退出了.
