MySQL order by 执行流程

在工作中使用order by再平常不过了，但是对于mysql order 的执行流程有了解多少？

举个栗子🌰，假设我们有一张表

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命令看看这个语句的执行情况

image-20200825215034723

Extra这个字段中的“Using filesort”表示的就是需要排序,“Using filesort”表示使用索引。MySQL会给每一个线程一个叫“sort_buffer”的内存用来排序。

就上面的sql语句执行流程简单概括就是

1. 初始化 sort_buffer，确定select的字段。
2. 从索引中找到所有满足条件的列，在主键中获取整行，取出select的字段，放入sort buffer。
3. 对sort buffer中的内容进行排序（上面的sql是根据name排序，这个排序动作是在内存中完成的）。
4. 取出limit的行数返回给前端。

⚠️注意 这里是把所有满足条件的值放进sort buffer中。

由于是把全部的值放进sort buffer中，sort buffer是内存所以不一定放的下，所以就存在sort buffer放不下的情况。sort_buffer_size可以设置sort buffer的大小，如果sort buffer中放不下的话，MySQL就需要借助临时文件来处理，可以通过如下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;

这个方法是通过查看 OPTIMIZER_TRACE 的结果来确认的，你可以从 number_of_tmp_files中看到是否使用了临时文件。

image-20200825222615671

number_of_tmp_files表示的是，排序过程中使用的临时文件数。MySQL将需要排序的数据分成12份，每一份单独排序 后存在这些临时文件中。然后把这12个有序文件再合并成一个有序的大文件。 number_of_tmp_files就是0，表示排序可以直接在内存中完成。

rowid排序

在上面的全字段扫描过程中，只对原表的数据读了一遍，剩下的操作都是在sort_buffer和临时文件中执行的。但是如果select的字段非常的多的话非常占空间，sort buffer的空间又是有限的，如果就要尽量把少的字段放sort buffer，所以只有要排序的列(即name字段)和主键id，排序的结果就少了其他字段。整个执行流程如下：

1. 初始化sort_buffer，放入需要排序的字段（name）和主键id。
2. 找到满足where条件的列的id，在聚簇索引中拿到需要排序的字段，放入sort buffer中。
3. 对sort buffer中的内容进行排序。
4. 对排序后的列取出limit的行数。
5. 根据id去聚簇索引中取出其他字段。返回给前端。

数据的排序是一个成本很高的一件事，如果这个数据本身就是有序的话那就不需要排序操作了，那怎么样才能保证本身就是有序的呢？那上面我们执行的sqlselect city,name,age from t where city='杭州' order by name limit 1000;来说的话，创建一个联合索引就可以了alter table t add index city_user(city, name); 为什么创建一个联合索引就可以？自己回忆一下联合索引的数据结构。

image-20200825224949143

可以用树搜索的方式定位到第一个满足city='杭州’的记录，并且额外确保了，接下来按顺序取“下一条记录”的遍历过程中，只要city的值是杭州，name的值就一定是有序的。执行流程就是如下：

1. 根据索引（city,name）找到满足where条件的列
2. 去聚簇索引取出需要的字段，返回给前端（重复limit中值的次数）

一个小思考：如果创建的是name在左city在右的联合索引一样的查询条件还能生效吗？

上面的流程如果索引中已经有了需要返回的字段就不需要去聚簇索引中取列，对于我们上面说的查询的情况我们如果创建(city,name,age)的联合索引的话就不需要再聚簇索引中获取数据了。