Mysql系列-Order By

2021/11/2 19:11:26

本文主要是介绍Mysql系列-Order By,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

Mysql系列-Order By

前言

面试过程中对于排序方面的考察,一般就集中在order by和group by上,今天我们一起研究下order by相关的知识点,对于order By我们回答的点都有哪些,相关面试题如下,小伙伴试着回答一下吧

  • MySQL中的group by,order by 如何进行优化的
  • group by和order by的区别
  • 排序时候order by执行起来比较慢,怎么进行优化

概念

我们在查询的时候,如果需要对查询中读取的数据进行排休,我们就需要使用order by 字段进行排序,排序的语法是

SELECT field1, field2,...fieldN FROM table_name ORDER BY field1 desc/ASC 

今天我们一起研究下 这段语句执行到数据库的时候,Mysql 的底层流程是怎么样的

排序算法

我们通过一个具体的案例来具体分析一下相关流程,建表语句如下所示

CREATE TABLE user (
  id int(11) AUTO_INCREMENT COMMENT '主键id',
  city varchar(16) NOT NULL COMMENT '城市',
  name varchar(16) NOT NULL COMMENT '名称', 
  age int(11) NOT NULL COMMENT '年龄',
  PRIMARY KEY (id),
  KEY city (city)
) ENGINE=InnoDB;
insert into `user` (city,name,age) VALUES ('北京','程序员fly',20);
insert into `user` (city,name,age) VALUES ('北京','小红',22);
insert into `user` (city,name,age) VALUES ('上海','小飞',22);
insert into `user` (city,name,age) VALUES ('苏州','大飞',30);
insert into `user` (city,name,age) VALUES ('杭州','程序员fly',26);
insert into `user` (city,name,age) VALUES ('广州','程序员fly',21);
insert into `user` (city,name,age) VALUES ('长春','程序员fly',20);

全字段排序

现在有这么个需求,按照年龄从小到大,查询前三个城市在北京的用户相关信息,我们写sql应该会这样写,这条sql执行简单,接下来我们通过explain关键字来看一下SQL的执行计划

select * from `user` where `city`='北京' order by age limit 3;

执行计划
执行计划中,我们可以看到,using fileSort进行排序相关操作了,这个排序操作具体如何呢,相关流程如下
全字段排序流程

如图所示相关流程描述如下

  1. 初始化sort_buffer,确定放入id,city,name、age这四个字段
  2. 从索引city找到第一个满足city='北京’条件的主键id
  3. 到主键id索引取出整行,取id,city,name、age这四个字段,存入sort_buffer中
  4. 从索引city取下一个记录的主键id
  5. 重复步骤3、4直到city的值不满足查询条件为止
  6. 对sort_buffer中的数据按照字段age做快速排序
  7. 按照排序结果取前3行返回给客户端

Mysql会为每一个查询的线程专门分配一块内存(sort_buffer)去干排序这件事,内存大小有sort_buffer_size控制,上面流程中我们将回表取的id,city,name、age全部放到sort_buffer中,我们称为上面流程所用的算法为全字段排序

这里小伙伴们是否有这样的疑问,如果我们查的数据sort_buffer放不下该怎么办,mysql其实这个时候会借用磁盘临时文件辅助排序,这里会用到一个归并算法,具体流程如下

  1. 首先从索引city找到第一个满足city='北京’条件的主键id进行回表操作,查找相关的数据
  2. 将查询的数据放入到sort_buffer中,当sort_buffer快满的时候,就在sort_buffer里面先对这部分数据进行排序,排序好的记录临时放入磁盘小文件中,继续往sort_buffer读取数据
  3. 经过第2步就会得到很多有序的小文件,利用归并排序合并成一个大文件,完成整个排序过程

rowId排序

​ 全字段排序是将id,city,name、age全部放入到sort_buffer里面进行排序,我们sql里面写的排序规则是order by age按照年龄排序,如果我们只需要将age放入sort_buffer中,这样不就能装下更多的值(因为sort_buffer里面放入字段越多,存放的条数就会越少,就很有可能利用磁盘临时文件进行排序,磁盘肯定没内存快嘛)所以Mysql又提供了一种排序算法rowid排序算法,具体流程如下

rowid排序

  1. 初始化sort_buffer,确定放入两个字段,即age和id;
  2. 从索引city找到第一个满足city='北京’条件的主键id;
  3. 到主键id索引查找到整行,取age、id这两个字段(没有取name、city、age),存入sort_buffer中;
  4. 从索引city取下一个记录的主键id;
  5. 重复步骤3、4直到不满足city='北京’条件为止;
  6. 对sort_buffer中的数据按照字段age进行排序;
  7. 遍历排序结果,取前3行,因为sort只要age,id这两个字段,但是我们需要是的id,name,city,age相关的信息,这里需要回表去主键索引树上根据id读相关信息返回(多了一步回表操作

优化思路

调整sort_buffer的大小

​ 内存肯定比磁盘快的,Mysql有个思想,如果内存够的话就多用内存,尽量减少磁盘访问,也因此我们尽量把sort_buffer调大一点

避免Rowid排序

对于InnoDB表来说,rowid排序会多一次回表操作,会增加磁盘读,我们可以适当调整下这个配置参数 max_length_for_sort_data:参数含义如下:如果放入sort_buffer中的字段长度大于这个值,Mysql就会使用rowid排序,Mysql默认为为1KB(开发过程中尽量别写select *)

使用联合索引

排序是因为数据是乱序的所以需要排序,如果能够保证从city这个索引取出的行,天然的按照age排序,这样不仅不需要排序了,我们上面阶段学习过程中我们知道Mysql的B+树是有序的,我们就可以建立(city,age)的联合索引,当city相同的时候,age是天然有序的,避免排序,B+树存储结构如图所示

联合索引explain

select * from `user` where `city`='北京' order by age limit 3; //order by age ,age有序的,不再需要

通过explain关键字我们看到,当建立联合索引(city,age的时候,执行计划Extra中不再出现using fileSort,说明没用fileSort排序相关操作。

联合索引#### 闲谈

感觉有帮助的同学还请点赞关注,这将对我是很大的鼓励~,公众号有自己开始总结的一系列文章,需要的小伙伴还请关注下个人公众号程序员fly,希望能一起成长。
扫码关注一下呗

参考链接

https://www.cnblogs.com/Chenjiabing/p/12696879.html

https://juejin.cn/post/6844904144713547783

https://time.geekbang.org/column/intro/100020801



这篇关于Mysql系列-Order By的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程