共计 6308 个字符,预计需要花费 16 分钟才能阅读完成。
导读 | 日常开发中,我们经常会使用到 order by,亲爱的小伙伴,你是否知道 order by 的工作原理呢?order by 的优化思路是怎样的呢?使用 order by 有哪些注意的问题呢?本文将跟大家一起来学习,攻克 order by~。 |
日常开发中,我们经常会使用到 order by,亲爱的小伙伴,你是否知道 order by 的工作原理呢?order by 的优化思路是怎样的呢?使用 order by 有哪些注意的问题呢?本文将跟大家一起来学习,攻克 order by~
假设用一张员工表,表结构如下:
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;
这条语句的逻辑很清楚,但是它的底层执行流程是怎样的呢?
我们先用 Explain 关键字查看一下执行计划
我们可以发现,这条 SQL 使用到了索引,并且也用到排序。那么它是怎么排序的呢?
MySQL 会给每个查询线程分配一块小内存,用于排序的,称为 sort_buffer。什么时候把字段放进去排序呢,其实是通过 idx_city 索引找到对应的数据,才把数据放进去啦。
我们回顾下索引是怎么找到匹配的数据的,现在先把索引树画出来吧,idx_city 索引树如下:
idx_city 索引树,叶子节点存储的是主键 id。还有一棵 id 主键聚族索引树,我们再画出聚族索引树图吧:
我们的查询语句是怎么找到匹配数据的呢?先通过 idx_city 索引树,找到对应的主键 id,然后再通过拿到的主键 id,搜索 id 主键索引树,找到对应的行数据。
加上 order by 之后,整体的执行流程就是:
执行示意图如下:
将查询所需的字段全部读取到 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,则表示使用了磁盘文件来进行排序。
使用了磁盘临时文件,整个排序过程又是怎样的呢?
TPS: 借助磁盘临时小文件排序,实际上使用的是归并排序算法。
小伙伴们可能会有个疑问,既然 sort_buffer 放不下,就需要用到临时磁盘文件,这会影响排序效率。那为什么还要把排序不相关的字段(name,city)放到 sort_buffer 中呢?只放排序相关的 age 字段,它不香吗?可以了解下 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 ## 修改排序数据最大单行长度为 32
set max_length_for_sort_data = 32;
## 执行查询 SQL
select name,age,city from staff where city = ‘ 深圳 ’ order by age limit 10;
使用 rowid 排序的话,整个 SQL 执行流程又是怎样的呢?
执行示意图如下:
对比一下全字段排序的流程,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 排序对比
一般情况下,对于 InnoDB 存储引擎,会优先使用全字段排序。可以发现 max_length_for_sort_data 参数设置为 1024,这个数比较大的。一般情况下,排序字段不会超过这个值,也就是都会走全字段排序。
我们如何优化 order by 语句呢?
再回顾下示例 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;
可以发现,加上 idx_city_age 联合索引,就不需要 Using filesort 排序了。为什么呢?因为索引本身是有序的,我们可以看下 idx_city_age 联合索引示意图,如下:
整个 SQL 执行流程变成酱紫:
流程示意图如下:
从示意图看来,还是有一次回表操作。针对本次示例,有没有更高效的方案呢?有的,可以使用覆盖索引:
覆盖索引:在查询的数据列里面,不需要回表去查,直接从索引列就能取到想要的结果。换句话说,你 SQL 用到的索引列数据,覆盖了查询结果的列,就算上覆盖索引了。”
我们给 city,name,age 组成一个联合索引,即可用到了覆盖索引,这时候 SQL 执行时,连回表操作都可以省去啦。
我们还可以通过调整参数,去优化 order by 的执行。比如可以调整 sort_buffer_size 的值。因为 sort_buffer 值太小,数据量大的话,会借助磁盘临时文件排序。如果 MySQL 服务器配置高的话,可以使用稍微调整大点。
我们还可以调整 max_length_for_sort_data 的值,这个值太小的话,order by 会走 rowid 排序,会回表,降低查询性能。所以 max_length_for_sort_data 可以适当大一点。
当然,很多时候,这些 MySQL 参数值,我们直接采用默认值就可以了。
日常开发过程中,我们可能会遇到没有 where 条件的 order by,那么,这时候 order by 后面的字段是否需要加索引呢。如有这么一个 SQL,create_time 是否需要加索引:
select * from A order by create_time;
无条件查询的话,即使 create_time 上有索引, 也不会使用到。因为 MySQL 优化器认为走普通二级索引,再去回表成本比全表扫描排序更高。所以选择走全表扫描, 然后根据全字段排序或者 rowid 排序来进行。
如果查询 SQL 修改一下:
select * from A order by create_time limit m;
假设 SQL 如下:
select * from A order by a limit 100000,10
假设有联合索引 idx_age_name, 我们需求修改为这样:查询前 10 个员工的姓名、年龄,并且按照年龄小到大排序,如果年龄相同,则按姓名降序排。对应的 SQL 语句就可以这么写:
select name,age from staff order by age ,name desc limit 10;
我们看下执行计划,发现使用到 Using filesort
这是因为,idx_age_name 索引树中,age 从小到大排序,如果 age 相同,再按 name 从小到大排序。而 order by 中,是按 age 从小到大排序,如果 age 相同,再按 name 从大到小排序。也就是说,索引存储顺序与 order by 不一致。
我们怎么优化呢?如果 MySQL 是 8.0 版本,支持 Descending Indexes,可以这样修改索引:
CREATE TABLE `staff` (`id` bigint(11) NOT NULL 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`),
KEY `idx_age_name` (`age`,`name` desc) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='员工表';
如果我们有联合索引 idx_city_name,执行这个 SQL 的话,是不会走排序过程的,如下:
select * from staff where city in ('深圳') order by age limit 10;
但是,如果使用 in 条件,并且有多个条件时,就会有排序过程。
explain select * from staff where city in ('深圳','上海') order by age limit 10;
这是因为:in 有两个条件,在满足深圳时,age 是排好序的,但是把满足上海的 age 也加进来,就不能保证满足所有的 age 都是排好序的。因此需要 Using filesort。
![post-qrcode](https://www.xgss.net/wp-content/uploads/2023/07/二维码-微信公众号256x256.jpg)