Mysql系列-Order By

2021-11-02 本文已影响0人程序员fly

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进行排序相关操作了，这个排序操作具体如何呢，相关流程如下

全字段排序流程

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

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

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

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

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

rowId排序

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

rowid排序

初始化sort_buffer，确定放入两个字段，即age和id；
从索引city找到第一个满足city='北京’条件的主键id；
到主键id索引查找到整行，取age、id这两个字段（没有取name、city、age），存入sort_buffer中；
从索引city取下一个记录的主键id；
重复步骤3、4直到不满足city='北京’条件为止；
对sort_buffer中的数据按照字段age进行排序；
遍历排序结果，取前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