order by的工作流程

发布于:2024-04-15 ⋅ 阅读:(80) ⋅ 点赞:(0)

在日常的业务开发中,使用到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技术和记录生活的博主。

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


网站公告

今日签到

点亮在社区的每一天
去签到