Mysql如何使用order by工作

日常开发中，我们经常要进行字段的排序，但是我们大多不知道排序是如何执行的，今天我们就说说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

如果我们执行下面语句是如何进行排序的呢

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

全字段排序

之前我们说过,为了避免全表扫描,我们在city字段上加索引，现在我使用explain命令查看这个语句的执行情况

我们发现extra这个子弹中的Using filesort 表是要进行排序，Mysql为每一个线程分配一块内存用于排序，这个叫sort_buffer.

如图所示，通常情况下，这个语句的流程如下

1. 初始化sort_buffer,确定放入name,city,age这三个字段
2. 从索引中找到第一个杭州的主键id
3. 然后到主键id取出整行(name,age,city)，存入sort_buffer中，
4. 从索引字段中去下一个记录的id
5. 重复3，4步骤，直到不满足条件
6. 对sort_buffer中的name字段进行排序
7. 按照排序结果取前1000条返回给客户端

我们把上面的排序叫全字段排序，执行流程如下

图中nama的排序有可能在内存中完成，也就可能使用外部排序，这个取决于所需的内存和参数sort_buffer_size

sort_buffer_size,就是Mysql为排序开辟的内存的大小，如果排序的数据量小于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看到是否使用了临时文件，

number_of_tmp_files表示使用的临时文件数，我们可以理解为mysql在排序的时候把数据分成了12份，每一份单独排序后存在这些临时文件中，然后把12有序文件再合并一个有序的大文件。

如果number_of_tmp_files=0,说明使用的是内存进行排序，我们看到examined_rows=4000.说明参与排序的行数是4000.sort_mode里面packed_additional_fields的意思是说，排序过程对字段进行了紧凑处理，如果字段定义为varchar(16),实际上排序过程中也就是按照字段的实际长度进行排序。

注意的是这里我们设置internal_tmp_disk_storage_engin设置成MyiSAM,否则select @a-@b的结果会显示4001，这是因为如果是innodb，数据从临时表取出也会进行加1操作。

rowid排序

我们可以看到如果查询的字段很多的话，那么sort_buffer存放的字段数太多，就会使用临时文件进行排序，因此造成了很大的浪费,此时mysql任务排序的单行长度会怎么做呢，

首先我要知道如何判断单行长度太大,如下参数

SET max_length_for_sort_data = 16;

我们看到city,name,age总长度为36，远远大于16，因此我们判定单行长度过大，Mysql就会使用另外一种算法进行排序.

新的算法放入sort_buffer的字段，只需要排序的列和主键id,但是这个时候，并不能直接返回排序的结果，而是要回表查询整行。

1. 初始化sort_buffer,确定放入两个字段，即name和id
2. 从索引city中找到第一个满足的条件主键id
3. 再到主键id索引中获取整行，取出name,id两个字段，存入sort_buffer
4. 在从索引city中到下一个记录id
5. 重复3，4步骤，知道不满足条件位置
6. 对sort_buffer进行name排序
7. 遍历排序结果，取出前1000条记录， 并按照id再到原表获取city,name,age字段返回给客户端

上面的排序算法叫做rowid排序，对比之前的流程，我们发现不同于之前的是多了一次访问表T的步骤，我们可根据上图的步骤，想一下select @b-@a,结果是啥,

首先，图中examined_rows的值还是4000，表示用于排序的数据是4000行，但是select@b-@a这个语句的值变成5000.

因为这个时候除了排序过程外，在排序完成后，还要根据id取原表取值，由于语句是limit 1000,因此会多读1000行。

我们也可以发现sort_mode=sort_key,rowid，表示参与排序只要name和id两个字段.

numner_of_tmp_files=10,那是因为参与排序的行数虽然仍然是4000行，但是每一个行都变小了，因此需要排序的总数量变小了，需要的临时文件相应变少了。

全字段排序和rowid排序

如果msyql实在是担心内存太小，会影响排序效率，才会采取rowid算法，这样排序过程中一次可以排序更多行，但是需要回表取数据。

如果任务内存足够大，会优先选择全字段排序，把需要的字段放入到sort_buffer,这样就会直接从内存里面返回查询结果，不再回表查询数据，

对于innodb来说，rowid排序要求回表造成磁盘读，因此不会优先选择，

看到这里，是不是所有的order by都要进行排序操作，如果不排序就不能获取正确的数据呢，其实，并不是多有的order by 语句，都需要排序，MySQL之所以要使用临时文件排序，是因为原来的数据都是无序的，因此如果本身的从city索引获取的数据就是按照name进行排序的，是不是就可以不用再进行排序呢.

实时上，确实是这样的，我们现在来建立一个(city,name)联合索引，对应的sql语句如下

alter table add index city_user(city,name)

从上面索引看出，我们依然可以用树索引定位到第一个city='杭州'的数据，并且额外确保了,接下来按顺序去下一条记录，只要city=杭州，name就是有序的

1. 从索引(city,name)找到一个满足city=杭州条件的主键id
2. 到主键id取到整行，取name,age ,city,作为结果的一部分直接返回
3. 从索引(city,name)取下一个主键id
4. 重复2,3步骤，直达查询到1000记录，或者不满足条件循环结束

可以看到这个查询过程不需要临时表，也不需要排序，接下来，我们用explain结果验证一下

发现extra的字段中没有using filesort,也就是不用排序，而且由于(city,name)索引本身就是索引有序，所以这个查询不需要查询4000行数据，只要找到前1000条数据就可以了。

到这里，我是不是还可以进行优化呢，当然是可以的，我们可以使用覆盖索引，覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上取数据，

我们按照覆盖索引的概念，建立(city,name.age)联合索引，

alter table add index （city,name,age）

这里如果city相等，还是按照name字段进行递增进行排序的，此时查询语句也就不需要排序了，

1. 从索引(city,name,age)中找到满足city=杭州的记录，取出city,name,age这三个字段的值，作为结果集的一部分返回
2. 从索引(city,name,age)取下一个记录，同样取出三个字段的值，作为结果返回
3. 重复2步骤，直到查到1000记录，或者不满足city=杭州的条件结束循环

然后我们再看explain

可以看到Extra字段里面多了Using index ,表示使用了覆盖索引，性能上会快很多.