乐趣区

关于java:后端程序员必备攻克order-by

jiezi

前言

日常开发中，咱们常常会应用到 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;

这条语句的逻辑很分明，然而它的 底层执行流程 是怎么的呢？

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 之后，整体的执行流程就是：

  1. MySQL 为对应的线程初始化sort_buffer，放入须要查问的 name、age、city 字段；
  2. 从 索引树 idx_city，找到第一个满足 city=’ 深圳’条件的主键 id，也就是图中的 id=9；
  3. 到 主键 id 索引树 拿到 id= 9 的这一行数据，取 name、age、city 三个字段的值，存到 sort_buffer；
  4. 从 索引树 idx_city 拿到下一个记录的主键 id，即图中的 id=13；
  5. 反复步骤 3、4 直到 city 的值不等于深圳 为止；
  6. 后面 5 步曾经查找到了所有 city 为深圳 的数据，在 sort_buffer 中，将所有数据依据 age 进行排序；
  7. 依照排序后果取前 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，则示意应用了磁盘文件来进行排序。

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

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

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 执行流程又是怎么的呢？

  1. MySQL 为对应的线程初始化sort_buffer，放入须要排序的 age 字段，以及主键 id；
  2. 从 索引树 idx_city，找到第一个满足 city=’ 深圳’条件的主键 id，也就是图中的 id=9；
  3. 到 主键 id 索引树 拿到 id= 9 的这一行数据，取 age 和主键 id 的值，存到 sort_buffer；
  4. 从 索引树 idx_city 拿到下一个记录的主键 id，即图中的 id=13；
  5. 反复步骤 3、4 直到 city 的值不等于深圳 为止；
  6. 后面 5 步曾经查找到了所有 city 为深圳的数据，在 sort_buffer中，将所有数据依据 age 进行排序；
  7. 遍历排序后果，取前 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;

能够发现，加上 idx_city_age 联结索引，就不须要 Using filesort 排序了。为什么呢？因为 索引自身是有序的 ，咱们能够看下idx_city_age 联结索引示意图，如下：

整个 SQL 执行流程变成酱紫：

  1. 从索引 idx_city_age 找到满足city=’ 深圳’ 的主键 id
  2. 到 主键 id 索引 取出整行，拿到 name、city、age 三个字段的值，作为后果集的一部分间接返回
  3. 从索引 idx_city_age 取下一个记录主键 id
  4. 反复步骤 2、3，直到查到 第 10 条 记录，或者是 不满足 city=’ 深圳’ 条件时循环完结。

流程示意图如下：

从示意图看来，还是有一次回表操作。针对本次示例，有没有更高效的计划呢？有的，能够应用 笼罩索引：

笼罩索引：在查问的数据列外面，不须要回表去查，间接从索引列就能取到想要的后果。换句话说，你 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 参数值，咱们间接采纳默认值就能够了。

应用 order by 的一些留神点

没有 where 条件，order by 字段须要加索引吗

日常开发过程中，咱们可能会遇到没有 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;
  • 无条件查问, 如果 m 值较小, 是能够走索引的. 因为 MySQL 优化器认为，依据索引有序性去回表查数据, 而后失去 m 条数据, 就能够终止循环, 那么老本比全表扫描小, 则抉择走二级索引。

分页 limit 过大时，会导致大量排序怎么办?

假如 SQL 如下：

select * from A order by a limit 100000,10
  • 能够记录上一页最初的 id，下一页查问时，查问条件带上 id，如：where id > 上一页最初 id limit 10。
  • 也能够在业务容许的状况下，限度页数。

索引存储程序与 order by 不统一，如何优化？

假如有联结索引 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='员工表';

应用了 in 条件多个属性时，SQL 执行是否有排序过程

如果咱们有 联结索引 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。

最初

如果感觉本文对你有帮忙，记得点赞 + 珍藏 + 转发！！！

退出移动版