在业务中一定有需要根据指定的字段排序的场景，假设我们要从市民表中查询出所有城市字段是杭州的市民，并且按照市民的姓名进行排序，返回前1000个市民的姓名和年龄。
下面是市民表的创建语句。
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;
如果我们想实现上述的功能，我们可以这么写sql语句。
select city,name,age from t where city='杭州' order by name limit 1000
那么这个sql语句的执行过程是什么样的呢？这种带限制条数的排序类sql语句的执行过程会受什么因素影响呢？
既然业务中有这么一个语句，那么理所当然要在city字段上建立索引。
在city字段上建立索引之后，我们用expain命令来看一下这个sql语句的执行计划。
从explain执行计划中的extra字段中我们看到了”using filesort”字符串，这就说明在这个sql语句的执行过程中进行了排序，那么Mysql是在什么地方进行排序的呢？Mysql给每一个线程都分配了一块用于排序的内存，这块用于排序的内存的名字叫做sort_buffer。
现在我们已经介绍了在Mysql中用于排序的内存块的名字，那么我们就很容易来描述上面的sql语句的执行过程了。
哪些字段的值会放进sort_buffer中？只有紧跟在select关键字后面的字段的值才会放进sort_buffer中，那么哪些记录的这些字段的值会放进sort_buffer中呢？只有符合查询条件的记录的这些字段的值才会放进sort_buffer中。然后对sort_buffer中的数据行根据排序字段的值进行排序，再从排序的结果中取出前1000个数据行返回给客户端。
那么能完整的描述一下sql语句的执行过程吗？
Mysql首先会对sql语句进行解析，从sql语句中解析出在select关键字后面有哪些字段，select后面有哪些字段决定了Mysql会把哪些字段的值放进sort_buffer中，Innodb存储引擎会在city索引树上直接定位到第一个满足条件的叶子节点，取出叶子节点中的主键值，然后拿着这个主键值回到主键索引树上取出完整的数据行，再从这个完整的数据行中取出紧跟在select关键字后面的字段的值，并且把这些字段的值放到sort_buffer中，再回到city索引树上检查下一个节点是否满足sql语句中的条件，如果满足，则重复之前的步骤，如果不满足，就停止重复，再根据sql语句中的排序字段name字段进行排序，最后取出排序后的结果中的前1000条数据行返回给客户端。
由于在这个过程中把紧跟在select关键字后所有的字段的值都放到了sort_buffer中然后再根据排序字段进行排序，所以这个过程被称作全字段排序过程。
那么很容易提出一个疑问，如果sort_buffer中不足以放下所有满足条件的数据，那么Mysql会怎么做呢？如果sort_buffer中能够放下所有满足sql语句中条件的数据，那么就可以使用排序算法直接对数据行进行排序，在内存中就可以完成排序的过程，不需要借助磁盘上的空间，你可以想象现在已经把一堆数据读取到一个数组中了，现在要对这个数组中的数据进行排序。如果sort_buffer中不足以放下所有满足sql语句中条件的数据，那么就要借助磁盘临时文件来排序。借助磁盘临时文件进行排序是怎么样的一个流程？
有一个疑惑，在排序过程中不需要额外的空间吗？
那么我们是如何确定一个sql语句在执行时是使用排序算法直接对数据行进行排序，还是借助磁盘上的临时文件来进行排序的呢？
我们可以使用下面的方法，来确定一个排序语句有没有借助磁盘临时文件来排序。
/* 打开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;
首先我们要开启Mysql提供的把优化器最终选择的执行方案记录到json文件中的功能，在这个json文件中，我们能够看到如下所示的文本信息。
如果我们想知道一个排序语句有没有借助磁盘上的临时文件进行排序，就要去关注这个json文件中number_of_tmp_files选项的值，number_of_tmp_files选项的值表示的是在这个排序语句的执行过程中使用了磁盘上临时文件的数目。如果在sort_buffer中不足以放下所有满足sql语句中条件的数据，那么就只能借助磁盘上的空间来进行排序，那么借助磁盘上临时文件进行排序是怎么样的一个过程呢？
如果在sort_buffer中足以放下所有满足sql语句中条件的数据，那么number_of_tmp_files选项的值就是0，也就是说，如果我们看到了number_of_tmp_files选项的值为0，这就说明在这个sql语句的执行过程中并没有借助磁盘上临时文件进行排序。
那么为什么在这里number_of_tmp_files选项的值是12而不是别的值呢？这个数值是多少与sort_buffer的大小、还有满足条件的数据有多少有关系，在sort_buffer大小一定的情况下，满足sql语句中条件的数据越多，那么number_of_tmp_files选项的值就越大，在满足sql语句中条件的数据一定的情况下，sort_buffer越大，那么number_of_tmp_files选项的值就越小，那么它们之间具体是怎样的定量关系呢？
在记录优化器最终选择的执行方案的json文件中，我们还可以看到一个叫做examined_rows的字段，在这个例子中examined_rows选项的值是4000，examined_rows选项的值表示了有多少个数据行进行了排序，在这个json文件中还有一个叫做sort_mode的字段，在这个例子中sort_mode字段的值是packed_additional_fields，
接下来我们考虑这么一个问题，如果select关键字后面的字段有很多，那么我们就要在sort_buffer中放进很多个字段的值，放进sort_buffer中的每个数据行的长度就会很长，而sort_buffer的大小是有限的，那么就会借助磁盘上的临时文件进行排序，而借助磁盘上的临时文件进行排序的速度是远远不如在内存中进行排序的。在内存中排序指的是所有的数据都被读取到了内存中，通过调整数据在内存中的位置从而实现数据的有序化。
那么Mysql是怎么应对放进sort_buffer中的数据行的长度过长这种情况的呢？Mysql提供了一个指定排序数据最大长度的选项，对应的英文名称是max_length_for_sort_data，这个选项的值决定了Mysql采用哪一种进行排序。
如果这个选项的值大于放进sort_buffer中的数据行的长度，那么就是用上文中介绍到的全字段排序算法进行排序。如果这个选项的值小于放进sort_buffer中的数据行的长度，那么就会使用下面将要介绍的算法进行排序。
如果max_length_for_sort_data选项的值小于放进sort_buffer中的数据行的长度，那么Mysql就不会把select关键字后所有字段的值都放进sort_buffer中，这时只会向sort_buffer中放入两个字段的值，排序字段name字段和主键字段id字段。然后Innodb存储引擎会从city索引树中搜索到第一个满足查询条件的节点，取出节点中保存的主键值，根据主键值回到主键索引树中取出整行数据，然后取出数据行中的排序字段和主键字段的值放进sort_buffer中，再回到city索引树取出下一个节点中的city值，判断city的值是否满足查询条件，如果满足查询条件，那么重复之前的过程，如果city的值不满足查询条件，那么就停止重复，再对sort_buffer中的数据根据name字段的值进行排序，取出排序结果中的前1000行，然后取出前1000行中保存的主键字段id字段的值，拿着id字段的值回到主键索引树上取出紧跟在select关键字后的所有字段的值，再把更详细的排序结果返回给客户端。
在这里需要提一个Mysql具体实现的细节，Mysql并不是一次性把1000个数据行中的所有id字段的值全部取出来保存到一个数组中，也不是在1000个数据行都完成回表查询之后，再一次性的把所有的数据发送给客户端，而是每取出一个id字段的值，回到主键索引树上取出更详细的数据，然后直接把更详细的数据返回给客户端，把这个过程重复1000次。
这种排序算法被称为基于主键字段的排序算法。那么全字段排序算法和基于主键字段的排序算法有什么区别呢？相比于全字段排序算法，基于主键字段的排序算法需要多回一次表，也就是说需要多回一次主键索引树上取数据。
那么Mysql什么时候会选择使用全字段排序算法，什么时候又会选择使用基于主键字段的排序算法呢？
当Mysql认为sort_buffer的大小足够大时，足以放下紧跟在select字段后的所有字段的值，那么Mysql就会选择使用全字段排序算法，因为全字段排序算法会在排序完成后直接把内存中的排序结果返回给客户端，而不需要再次回表取数据。回表取数据意味着需要进行磁盘读取，而磁盘读取过程的耗时往往较长，会明显增加sql语句的执行时间。
如果Mysql认为sort_buffer太小了，即使借助磁盘上的临时文件进行排序也需要借助很多个临时文件，借助更多的临时文件进行排序意味着什么？这个时候Mysql就会采用基于主键字段的排序算法。因为采用基于主键字段的排序算法就可以在有限的sort_buffer上给更多的数据行进行排序，从而减少需要的临时文件的个数。
那么在执行所有的排序语句的过程中都需要对sort_buffer中的数据行进行排序吗？显然不是，我们需要对sort_buffer中的数据行进行排序是因为我们在把数据行放入sort_buffer中时没有按照顺序来放入，那么如果我们在向sort_buffer中放入数据行时总是保证先放入的数据行中的值更小或更大，也就是说放入sort_buffer中的数据行本身就是有序的，那么我们自然就不需要对数据行进行排序了。
那么我们怎么做才能保证放入sort_buffer中的数据行本身就是有序的呢？如果我们不需要再对sort_buffer中的数据行进行排序会有什么好处呢？
假如我们现在在上面的市民表上建立了一个city字段和name字段的联合索引，建立联合索引的sql语句如下。
alter table t add index city_user(city, name);
在city和name字段的联合索引树上，在定位到第一个满足sql语句中查询条件的叶子节点后，会依次取联合索引树上的下一个叶子节点，直到下一个叶子节点的值不满足sql语句中的查询条件。从city和name字段的联合索引树上某段连续的叶子节点中取出的值天然具有city字段相同，name字段有序的特征，如果我们按照city和name字段的联合索引树上的叶子节点的顺序向sort_buffer中放入数据行，那么放入sort_buffer中的数据行天然就是对于name字段有序的，也就是说不需要再对sort_buffer中的数据行进行排序。
那么在建立city和name字段的联合索引后，排序语句的执行过程变成了什么样子呢？
Mysql会先在city和name字段的联合索引树上定位到第一个符合查询条件的叶子节点，然后取出叶子节点中的主键值，拿着这个主键值回到主键索引树上取出完整的数据行，然后从这个数据行中取出要查询的字段的值，把查询到的字段的值直接发送给客户端，然后检查联合索引树上的下一个叶子结点是否满足查询条件，如果满足查询条件，那么重复之前的过程，如果下一个叶子节点不满足查询条件或者是已经重复了1000次，就停止重复。
在这个执行过程中并没有使用临时表，也没有进行排序。临时表一直都没有使用，但是这个方法确实节省了排序的过程。临时表是什么？
如果在sql语句的执行过程中进行了排序，那么在这个sql语句的explain计划中的extra字段中就会显示using filesort，那么我们来看一下在建立联合索引后，排序语句的explain执行计划中的extra字段中还有这串字符吗？
从图中我们可以看到，extra字段中没有using filesort了，也就是说在这个语句的执行过程中没有进行排序。而且这个执行过程还有一个优点就是Mysql只需要回表1000次，而不是像之前的执行过程需要回表4000次甚至8000次。
那么我们还可以怎么做来进一步简化这个排序语句的执行过程呢？
我们还可以通过覆盖索引来简化这个排序语句的执行过程，针对这个查询语句，我们可以创建一个city、name和age字段的联合索引，创建这个联合索引的sql语句如下。
alter table t add index city_user_age(city, name, age);
由于在city、name和age字段的联合索引树上的某段连续的叶子节点同样具有city字段的值相同，name字段的值有序的特征，所以我们把这个联合索引树上满足查询条件的叶子节点中的数据行依次放到sort_buffer后，sort_buffer中的数据行也是有序的，而且由于这个联合索引树上包含了我们查询的所有字段的值，所以与之前执行过程相比节省了回表操作所消耗的时间，在这个执行过程中一次回表操作都不需要进行。
那么这个执行过程具体是什么样的呢？
Mysql会先在联合索引树上定位到第一个满足查询条件的叶子节点，取出在这个叶子节点中存放的我们要查询的字段的值，并且把查询到的字段的值直接发送给客户端，检查联合索引树上的下一个叶子节点是否满足查询条件，如果满足查询条件，就重复上面的过程，如果不满足查询条件或者重复的次数达到了limit关键字的限制条件，就停止重复。由于在这个联合索引树的叶子节点中存放了所有我们查询的字段的值，所以在这个过程中并不需要进行回表操作。
那么这时这个查询语句的explain执行计划是什么样的呢？
我们可以看到在这个语句的explain执行计划的extra字段中多了using index的字符串，using index表示的是在这个语句的执行过程中使用了覆盖索引。由于在这个过程中不需要进行回表操作也不需要进行排序，而在别的执行过程中或多或少都需要进行回表，所以在这几个执行过程中此时这个查询语句的执行速度是最快的。
但是这个例子并不是想要说明我们要给所有的查询语句都建立上联合索引，从而在执行这些查询语句时能够使用覆盖索引，因为建立的索引会占用空间，而且在增删改数据时还要对索引树进行维护，对索引树进行维护既需要占用cpu的使用时间，也需要对磁盘进行读写，也就是说我们多建立一个索引，在发生增删改数据时就要多消耗一部分硬件资源，所以要权衡好建立索引的数量与对查询语句的优化。
若有收获，就点个赞吧
0 人点赞
