order by的工作流程

在日常的业务开发中，使用到MySQL的order by对数据进行排序是一个很正常的行为，那么你知道order by是如何工作的嘛？

一、全字段排序

先创建一张user表，字段name，age，address，插入随机数据100w条记录，由于按照name查询，所以给name字段添加索引：
alter table user add index idx_name (name);
我们看一下这条SQL的Explain的执行计划：
EXPLAIN SELECT name,age,address FROM user WHERE name = 'Olivia Wilson' ORDER BY age

从执行计划中可以看出，Extra字段中'Using fliesort'表示的就是用到了排序，MySQL会给每一个线程分配一块内存用于排序，称为sort_buffer

通常情况下，以上排序的SQL执行流程如下：

1. 初始化内存sort_buffer,确定放入字段：name,age,address
2. 从name索引树上找到所有满足name='Olivia Wilson'的主键ID
3. 到主键id索引树上取出整行，取name，age，address三个字段的值，放入sort_buffer字段中
4. 对sort_buffer中字段按照age做快速排序

我们暂且把这个过程称之为全字段排序，因为把所有需要查询的字段都放入到了sort_buffer中进行排序了，以上步骤4中按'age排序'这个动作，可能在内存中完成，也可能需要使用外部排序，这就取决于排序所需要的内存和参数sort_buffer_size

sort_buffer_size: 就是MySQL为排序开辟的一块内存（sort_buffer）的大小。如果排序的数量小于sort_buffer_size，排序就在内存中完成，如果排序数量太大内存放不下，就利用磁盘临时文件辅助排序

二、rowid排序

在全字段排序中，只对原表的数据读了一遍，剩下的操作都是在sort_buffer和临时文件中执行的，但是这个排序算法有个问题，如果查询要返回的字段很多的话，那么sort_buffer里面要放的字段很多，这样的内存里能同时存放下的行数就很少，要分成很多个临时文件，排序性能就会变得很差

那么MySQL如果认为排序的单行长度太大会怎么做呢？

-- 控制用于排序的行数据的长度的一个参数
SETmax_length_for_sort_data = 16;

如果单行的长度超过这个值，MySQL就会认为单行太大，要换一种排序算法
新的算法放入sort_buffer字段只有需要排序的字段（age）和主键id
但这时候，排序的结果就因为少了name和address的值，就不能直接返回，此时的执行的流程就成了以下的样子：

1. 初始化sort_buffer，确定放入字段age和id
2. 从name索引树找到满足的name='Olivia Wilson'的主键id
3. 到主键id索引取出整行，取出name和id两个字段放入sort_buffer
4. 对sort_buffer中的数据按照字段name进行排序
5. 遍历排序结果，并按照id的值回到原表中取出name，age，address三个字段返回给客户端

以上的执行流程，我们称之为rowid排序，相对于全字段排序，rowid排序多了一次访问主键索引的过程，对于InnoDB表来说，rowid排序会要求回表多造成磁盘读，因此不会被优先选择

三、优化

从以上的两种排序，可以看出MySQL做排序是一个成本比较高操作。那是不是所有的order by都需要排序操作呢？如果不排序就能得到正确的结果，那么效率就会提高很多

其实从上面分析的过程来看，之所以MySQL需要做排序操作，原因就是原来的数据都是无序的，如果在name索引上取出来的行，天然就是按照age递增排序的话，是不是就可以不用排序了呢？结果当然是是的了

针对以上疑问，创建联合索引：（name，age）
alter table user add index idx_name_age (name,age) ;
再次查看一下Explain执行计划:
EXPLAIN SELECT name,age,address FROM user WHERE name = 'Olivia Wilson' ORDER BY age

可以看到，这个过程不需要使用'Using fliesort',也就是不需要排序了
执行流程如下：

1. 从索引树（name,age）找到满足name='Olivia Wilson'的主键id
2. 到主键id索引取出整行数据，取name，age，address字段，作为结果集的一部分直接返回

从以上执行流程中可以看到，步骤2还是进行了回表，那么我们是不是可以优化这个回表呢？覆盖索引？覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上取数据
按照覆盖索引的概念，再创建一个联合索引：（name，age，address）
alter table user add index idx_name_age_address (name,age,address);
我们再看一下执行计划：
EXPLAIN SELECT name,age,address FROM user WHERE name = 'Olivia Wilson' ORDER BY age

可以看到Extra字段里多了'Using index'，表示使用了覆盖索引，性能上会快很多，当然并不是说为了每个查询都能使用上覆盖索引，毕竟索引维护也是需要代价的，需要做一个权衡的决定

---

我是一零贰肆，一个关注Java技术和记录生活的博主。

欢迎扫码关注“一零贰肆”的公众号，一起学习，共同进步，多看路，少踩坑。