mysql 排序后下一条记录_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这个子弹中的Usingfilesort表是要进行排序，Mysql为每一个线程分配一块内存用于排序，这个叫sort_buffer.

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

初始化sort_buffer,确定放入name,city,age这三个字段

从索引中找到第一个杭州的主键id

然后到主键id取出整行(name,age,city)，存入sort_buffer中，

从索引字段中去下一个记录的id

重复3，4步骤，直到不满足条件

对sort_buffer中的name字段进行排序

按照排序结果取前1000条返回给客户端

我们把上面的排序叫全字段排序，执行流程如下图中nama的排序有可能在内存中完成，也就可能使用外部排序，这个取决于所需的内存和参数sort_buffer_sizesort_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,但是这个时候，并不能直接返回排序的结果，而是要回表查询整行。

初始化sort_buffer,确定放入两个字段，即name和id

从索引city中找到第一个满足的条件主键id

再到主键id索引中获取整行，取出name,id两个字段，存入sort_buffer

在从索引city中到下一个记录id

重复3，4步骤，知道不满足条件位置

对sort_buffer进行name排序

遍历排序结果，取出前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语句如下

altertableaddindex city_user(city,name)

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

从索引(city,name)找到一个满足city=杭州条件的主键id

到主键id取到整行，取name,age,city,作为结果的一部分直接返回

从索引(city,name)取下一个主键id

重复2,3步骤，直达查询到1000记录，或者不满足条件循环结束

可以看到这个查询过程不需要临时表，也不需要排序，接下来，我们用explain结果验证一下发现extra的字段中没有using filesort,也就是不用排序，而且由于(city,name)索引本身就是索引有序，所以这个查询不需要查询4000行数据，只要找到前1000条数据就可以了。

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

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

altertableaddindex(city,name,age)

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