后端程序员必备：攻克order by，揭秘

最新推荐文章于 2022-11-03 19:32:17 发布

Android学习小分队

最新推荐文章于 2022-11-03 19:32:17 发布

阅读量144

点赞数

分类专栏：程序员文章标签：后端 java 面试

本文链接：https://blog.csdn.net/m0_56144365/article/details/119523476

版权

程序员专栏收录该内容

937 篇文章 3 订阅

订阅专栏

本文详细解析了MySQL中涉及查询排序的执行流程，包括全字段排序和rowid排序的工作机制，以及如何利用索引优化orderby操作。通过案例分析了sort_buffer_size和max_length_for_sort_data参数的影响，并提供了优化orderby语句的策略，如建立联合索引和调整系统参数。

摘要由CSDN通过智能技术生成

假设用一张员工表，表结构如下：


CREATE TABLE `staff` (

`id` BIGINT ( 11 ) AUTO_INCREMENT COMMENT '主键id',

`id_card` VARCHAR ( 20 ) NOT NULL COMMENT '身份证号码',

`name` VARCHAR ( 64 ) NOT NULL COMMENT '姓名',

`age` INT ( 4 ) NOT NULL COMMENT '年龄',

`city` VARCHAR ( 64 ) NOT NULL COMMENT '城市',

PRIMARY KEY ( `id`),

INDEX idx_city ( `city` )

) ENGINE = INNODB COMMENT '员工表';

表数据如下：

我们现在有这么一个需求：查询前10个，来自深圳员工的姓名、年龄、城市，并且按照年龄小到大排序。对应的 SQL 语句就可以这么写：


select name,age,city from staff where city = '深圳' order by age limit 10;

这条语句的逻辑很清楚，但是它的底层执行流程是怎样的呢？

order by 工作原理

explain 执行计划

我们先用Explain关键字查看一下执行计划

执行计划的key这个字段，表示使用到索引idx_city
Extra 这个字段的 Using index condition 表示索引条件
Extra 这个字段的 Using filesort表示用到排序

我们可以发现，这条SQL使用到了索引，并且也用到排序。那么它是怎么排序的呢？

全字段排序

MySQL 会给每个查询线程分配一块小内存，用于排序的，称为 sort_buffer。什么时候把字段放进去排序呢，其实是通过idx_city索引找到对应的数据，才把数据放进去啦。

我们回顾下索引是怎么找到匹配的数据的，现在先把索引树画出来吧，idx_city索引树如下：

idx_city索引树，叶子节点存储的是主键id。还有一棵id主键聚族索引树，我们再画出聚族索引树图吧：

我们的查询语句是怎么找到匹配数据的呢？先通过idx_city索引树，找到对应的主键id，然后再通过拿到的主键id，搜索id主键索引树，找到对应的行数据。

加上order by之后，整体的执行流程就是：

MySQL 为对应的线程初始化sort_buffer，放入需要查询的name、age、city字段；
从索引树idx_city，找到第一个满足 city='深圳’条件的主键 id，也就是图中的id=9；
到主键 id 索引树拿到id=9的这一行数据，取name、age、city三个字段的值，存到sort_buffer；
从索引树idx_city 拿到下一个记录的主键 id，即图中的id=13；
重复步骤 3、4 直到city的值不等于深圳为止；
前面5步已经查找到了所有city为深圳的数据，在 sort_buffer中，将所有数据根据age进行排序；
按照排序结果取前10行返回给客户端。

执行示意图如下：

将查询所需的字段全部读取到sort_buffer中，就是全字段排序。这里面，有些小伙伴可能会有个疑问,把查询的所有字段都放到sort_buffer，而sort_buffer是一块内存来的，如果数据量太大，sort_buffer放不下怎么办呢？

磁盘临时文件辅助排序

实际上，sort_buffer的大小是由一个参数控制的：sort_buffer_size。如果要排序的数据小于sort_buffer_size，排序在sort_buffer 内存中完成，如果要排序的数据大于sort_buffer_size，则借助磁盘文件来进行排序

如何确定是否使用了磁盘文件来进行排序呢？可以使用以下这几个命令


## 打开optimizer_trace，开启统计

set optimizer_trace = "enabled=on";

## 执行SQL语句

select name,age,city from staff where city = '深圳' order by age limit 10;

## 查询输出的统计信息

select * from information_schema.optimizer_trace

可以从 number_of_tmp_files 中看出，是否使用了临时文件。

number_of_tmp_files 表示使用来排序的磁盘临时文件数。如果number_of_tmp_files>0，则表示使用了磁盘文件来进行排序。

使用了磁盘临时文件，整个排序过程又是怎样的呢？

从主键Id索引树，拿到需要的数据，并放到sort_buffer内存块中。当sort_buffer快要满时，就对sort_buffer中的数据排序，排完后，把数据临时放到磁盘一个小文件中。
继续回到主键 id 索引树取数据，继续放到sort_buffer内存中，排序后，也把这些数据写入到磁盘临时小文件中。
继续循环，直到取出所有满足条件的数据。最后把磁盘的临时排好序的小文件，合并成一个有序的大文件。

TPS: 借助磁盘临时小文件排序，实际上使用的是归并排序算法。

小伙伴们可能会有个疑问，既然sort_buffer放不下，就需要用到临时磁盘文件，这会影响排序效率。那为什么还要把排序不相关的字段（name，city）放到sort_buffer中呢？只放排序相关的age字段，它不香吗？可以了解下rowid 排序。

rowid 排序

rowid 排序就是，只把查询SQL需要用于排序的字段和主键id，放到sort_buffer中。那怎么确定走的是全字段排序还是rowid 排序排序呢？

实际上有个参数控制的。这个参数就是max_length_for_sort_data，它表示MySQL用于排序行数据的长度的一个参数，如果单行的长度超过这个值，MySQL 就认为单行太大，就换rowid 排序。我们可以通过命令看下这个参数取值。


show variables like 'max_length_for_sort_data';

max_length_for_sort_data 默认值是1024。因为本文示例中name,age,city长度=64+4+64 =132 < 1024, 所以走的是全字段排序。我们来改下这个参数，改小一点，


## 修改排序数据最大单行长度为32

set max_length_for_sort_data = 32;

## 执行查询SQL

select name,age,city from staff where city = '深圳' order by age limit 10;

使用rowid 排序的话，整个SQL执行流程又是怎样的呢？

MySQL 为对应的线程初始化sort_buffer，放入需要排序的age字段，以及主键id；
从索引树idx_city，找到第一个满足 city='深圳’条件的主键 id，也就是图中的id=9；
到主键 id 索引树拿到id=9的这一行数据，取age和主键id的值，存到sort_buffer；
从索引树idx_city 拿到下一个记录的主键 id，即图中的id=13；
重复步骤 3、4 直到city的值不等于深圳为止；
前面5步已经查找到了所有city为深圳的数据，在 sort_buffer中，将所有数据根据age进行排序；
遍历排序结果，取前10行，并按照 id 的值回到原表中，取出city、name 和 age 三个字段返回给客户端。

执行示意图如下：

对比一下全字段排序的流程，rowid 排序多了一次回表。

什么是回表？拿到主键再回到主键索引查询的过程，就叫做回表

我们通过optimizer_trace，可以看到是否使用了rowid排序的：


## 打开optimizer_trace，开启统计

set optimizer_trace = "enabled=on";

## 执行SQL语句

select name,age,city from staff where city = '深圳' order by age limit 10;

## 查询输出的统计信息

select * from information_schema.optimizer_trace

全字段排序与rowid排序对比

全字段排序： sort_buffer内存不够的话，就需要用到磁盘临时文件，造成磁盘访问。
rowid排序： sort_buffer可以放更多数据，但是需要再回到原表去取数据，比全字段排序多一次回表。

一般情况下，对于InnoDB存储引擎，会优先使用全字段排序。可以发现 max_length_for_sort_data 参数设置为1024，这个数比较大的。一般情况下，排序字段不会超过这个值，也就是都会走全字段排序。

order by的一些优化思路

我们如何优化order by语句呢？

因为数据是无序的，所以就需要排序。如果数据本身是有序的，那就不用排了。而索引数据本身是有序的，我们通过建立联合索引，优化order by 语句。
我们还可以通过调整max_length_for_sort_data等参数优化；

联合索引优化

再回顾下示例SQL的查询计划


explain select name,age,city from staff where city = '深圳' order by age limit 10;

我们给查询条件city和排序字段age，加个联合索引idx_city_age。再去查看执行计划


alter table staff add  index idx_city_age(city,age);

explain select name,age,city from staff where city = '深圳' order by age limit 10;

文末java面试题，进阶技术大纲，架构资料分享

我将这三次阿里面试的题目全部分专题整理出来，并附带上详细的答案解析，生成了一份PDF文档，有兴趣的朋友们可以点击这里即可免费领取

第一个要分享给大家的就是算法和数据结构

网易严选Java开发三面面经：HashMap+JVM+索引+消息队列

第二个就是数据库的高频知识点与性能优化

网易严选Java开发三面面经：HashMap+JVM+索引+消息队列

第三个则是并发编程（72个知识点学习）

网易严选Java开发三面面经：HashMap+JVM+索引+消息队列

最后一个是各大JAVA架构专题的面试点+解析+我的一些学习的书籍资料

网易严选Java开发三面面经：HashMap+JVM+索引+消息队列

详细的答案解析，生成了一份PDF文档，有兴趣的朋友们可以点击这里即可免费领取

第一个要分享给大家的就是算法和数据结构

[外链图片转存中…(img-pCLVFNEG-1628437584909)]

第二个就是数据库的高频知识点与性能优化

[外链图片转存中…(img-mGM7j947-1628437584910)]

第三个则是并发编程（72个知识点学习）

[外链图片转存中…(img-CnFzqToN-1628437584913)]

最后一个是各大JAVA架构专题的面试点+解析+我的一些学习的书籍资料

[外链图片转存中…(img-znOs4nDr-1628437584915)]

还有更多的Redis、MySQL、JVM、Kafka、微服务、Spring全家桶等学习笔记这里就不一一列举出来