关于java:面试官order-by-是怎样排序的怎么优化

8次阅读

共计 7976 个字符，预计需要花费 20 分钟才能阅读完成。

01 前言

刚换了新工作，用了两周工夫筹备，在 3 天之内拿了 5 个 offer，最初抉择了广州某互联网行业独角兽 offer，昨天刚入职。这几天刚好整顿下在面试中被问到有意思的问题，也借此机会跟大家分享下。

这家企业的面试官有点意思，一面是个同龄小哥，一起聊了两个小时（聊到我嘴都干了）。二面是个从阿里进去的架构师，视频面试，我做完自我介绍之后，他一收场就问我：

对 MySQL 相熟吗？

我一愣，随之意识到这是个坑。他必定想问我某方面的原理了，恰好我钻研过索引。就答复：

对索引比拟相熟。

他：

order by 是怎么实现排序的？

还好我又温习，基本上排序缓冲区、怎么优化之类的都答到点子上。明天也跟大家盘一盘 order by，我将从原理讲到最终优化，给大家聊聊 order by，心愿对你有所帮忙。

国际惯例，先上思维导图。PS：文末有福利

.png)

1.1 往期精彩

MySQL 查问语句是怎么执行的？

MySQL 索引

MySQL 日志

MySQL 事务与 MVCC

MySQL 的锁机制

MySQL 字符串怎么设计索引？

面试官：数据库自增 ID 用完了会咋样？

1.2 先举个栗子

当初有一张订单表，构造是这样的：

CREATE TABLE `order` (id INT ( 11) NOT NULL AUTO_INCREMENT COMMENT '主键',
user_code VARCHAR (16) NOT NULL COMMENT '用户编号',
goods_name VARCHAR (64) NOT NULL COMMENT '商品名称',
order_date TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单工夫',
city VARCHAR (16) DEFAULT NULL COMMENT '下单城市',
order_num INT (10) NOT NULL COMMENT '订单号数量',
PRIMARY KEY (`id`) 
) ENGINE = INNODB AUTO_INCREMENT = 100 DEFAULT CHARSET = utf8 COMMENT = '商品订单表';

造点数据：

// 第一步：创立函数
delimiter //

DROP PROCEDURE
IF
    EXISTS proc_buildata;
CREATE PROCEDURE proc_buildata (IN loop_times INT) BEGIN
DECLARE var INT DEFAULT 0;
WHILE
    var < loop_times DO
    
    SET var = var + 1;
INSERT INTO `order` (`id`, `user_code`, `goods_name`, `order_date`, `city` , `order_num`)
VALUES
    (var, var + 1, '有线耳机', '2021-06-20 16:46:00', '杭州', 1);

END WHILE;

END // delimiter;

// 第二步：调用下面生成的函数，即可插入数据，倡议大家造点随机的数据。比方改改城市和订单数量
CALL proc_buildata(4000);

我生成的数据是这样的：

现有需要：查出 618 期间，广州的小伙伴的订单数量和用户编号，并依照订单数量升序，只有 1000 条。

依据需要能够得出以下 SQL，置信小伙伴都很相熟了。

select city, order_num, user_code from `order` where city='广州' order by order_num limit 1000;

那这个语句是怎么执行的呢？有什么参数能够影响它的行为吗？

02 全字段排序

失去这个需要，我第一反馈是先给 city 字段加上索引，防止全表扫描：

ALTER TABLE `order` ADD INDEX city_index (`city`);

用 explain 看看执行状况

留神到最初一个 extra 字段的后果是：Using filesort，示意须要排序。其实 MySQL 会给每个线程调配一块内存用于排序，称为 sort_buffer。

为了更直观理解排序的执行流程，我粗略画了个 city 索引的图示：

可见，当初满足 sql 条件的就是 ID-3 到 ID-X 这一段数据。sql 的整个流程是这样的：

  • 1、初始化 sort_buffer，放入 city、order_num、user_code 这三个字段；
  • 2、从索引 city 找到第一个满足 city=’ 广州’条件的主键 id，也就是图中的 ID_3；
  • 3、到主键 id 索引取出整行，取 city、order_num、user_code 三个字段的值，存入 sort_buffer 中；
  • 4、从索引 city 取下一个记录的主键 id；
  • 5、反复步骤 3、4 直到 city 的值不满足查问条件为止，对应的主键 id 也就是图中的 ID_X；
  • 6、对 sort_buffer 中的数据依照字段 order_num 做疾速排序；
  • 7、依照排序后果取前 1000 行返回给客户端。

这个过程称之为全字段排序，画个图，长这样：

其中，按 order_num 排序 这个步骤，可能在内存中实现，也可能须要应用内部排序，这取决于排序所需的内存和参数 sort_buffer_size。

也就是 MySQL 为排序开拓的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中实现。但如果排序数据量太大，内存顶不住，就得磁盘临时文件辅助排序。

当然，在 MySQL5.7 以上版本能够用上面介绍的 检测办法（前面都有用到），来查看一个排序语句是否应用了临时文件。PS：这里的语句间接复制到 navicat 执行即可，要一起执行（都复制进去，点下执行）

/* 关上 optimizer_trace，只对本线程无效 */
SET optimizer_trace='enabled=on'; 

/* @a 保留 Innodb_rows_read 的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, order_num, user_code from `order` where city='广州' order by order_num limit 1000; 

/* 查看 OPTIMIZER_TRACE 输入 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`;

/* @b 保留 Innodb_rows_read 的以后值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算 Innodb_rows_read 差值 */
select @b-@a;

执行完之后，可从 OPTIMIZER_TRACE 表的 TRACE 字段失去以下后果：

其中 examined_rows 示意须要排序的行数 6883；sort_buffer_size 就是排序缓冲区的大小；sort_buffer_size 就是我 MySQL 的排序缓冲区大小 256 KB。

另外，sort_mode 的值是 packed_additional_fields，它示意排序过程对数据做了优化，也就是数据占用多少就算多少内存。举个栗子：不存在数据定义长度 16，就按这个长度算，如果数据只占 2，只会按长度 2 分配内存。

number_of_tmp_files 代表的是用了几个内部文件来辅助排序。我这里是用了两个，内存放不下时，就应用内部排序，内部排序个别应用 归并排序 算法。能够这么简略了解，MySQL 将须要排序的数据分成 2 份，每一份独自排序后存在这些临时文件中。而后把这 2 个有序文件再合并成一个有序的大文件。

最初一个查问语句，select @b-@a 的值是 6884，示意整个过程只扫描了 6883 行，为啥显示 6884？

因为查问 OPTIMIZER_TRACE 表时，需用到长期表；而 InnDB 引擎把数据从长期表取出时，Inndb_rows_read 值会加 1。

所以，把 internal_tmp_disk_storage_engine 设置为 MyISAM 可解决此问题。

03 rowid 排序

下面的全字段排序其实会有很大的问题，你可能发现了。咱们须要查问的字段都要放到 sort_buffer 中，如果查问的字段多了起来，内存占用升高，就会很容易打满 sort_buffer。

这时，就要用很多的临时文件辅助排序，导致性能升高。

那问题来了：

咱们思考的方向应该是升高排序的单行长度，哪有没有办法能做到呢？

必定是有的，MySQL 之所以走全字段排序是由 max_length_for_sort_data 管制的，它的 默认值是 1024。

show variables like 'max_length_for_sort_data';

因为本文示例中 city,order_num,user_code 长度 = 16+4+16 =36 < 1024, 所以走的是全字段排序。咱们来改下这个参数，改小一点，

SET max_length_for_sort_data = 16;

当单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。原来 city、user_code、order_num 占用的长度是 36，显然放不下全副查问字段了。这时就要换算法：sort_buffer 只存 order_num 和 id 字段。

这时的流程应该是这样的：

  • 1、初始化 sort_buffer，确定放入两个字段，即 order_num 和 id；
  • 2、从索引 city 找到第一个满足 city=’ 广州’条件的主键 id，也就是图中的 ID_3；
  • 3、回表，取 order_num、id 这两个字段，存入 sort_buffer 中；
  • 4、从索引 city 取下一个记录的主键 id；
  • 5、反复步骤 3、4 直到不满足 city=’ 广州’条件为止，也就是图中的 ID_X；
  • 6、对 sort_buffer 中的数据依照字段 order_num 进行排序；
  • 7、遍历排序后果，取前 1000 行，再次回表取出 city、order_num 和 user_code 三个字段返回给客户端。

图示：由图可见，这种形式其实多了一次回表操作、但 sort_buffer_size 占用却变小了。

此时，执行下面的检测办法，能够发现 OPTIMIZER_TRACE 表中的信息变了。

  • sort_mode 变成了 <sort_key, rowid>，示意参加排序的只有 order_num 和 id 这两个字段。
  • number_of_tmp_files 变成 0 了，是因为这时参加排序的行数尽管依然是 6883 行，然而每一行都变小了，因而须要排序的总数据量就变小了，sort_buffer_size 能满足排序用的内存，所以临时文件就不须要了。

examined_rows 的值还是 6883，示意用于排序的数据是 6883 行。然而 select @b-@a 这个语句的值变成 7884 了。因为这时候除了排序过程外，在排序实现后，还要回表一次。因为语句是 limit 1000，所以会多读 1000 行。

3.1 做个小结

rowid 排序中，排序过程一次能够排序更多行，然而须要回表取数据。

如果内存足够大，MySQL 会优先选择全字段排序，把须要的字段都放到 sort_buffer 中，这样排序后就会间接从内存返回查问后果了，不必回表。

这也就体现了 MySQL 的一个设计思维：如果内存够，就要多利用内存，尽量减少磁盘拜访。

对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因而不会被优先选择。

这两种都是因为数据自身是无序的，才要放到 sort_buffer 并生成临时文件能力做排序。

哪有没有方法，让数据自身就有序呢？回忆下，咱们学过的索引就是有序的。

04 索引优化

这时，要是我把 city、order_num 建一个组合索引，得出的数据是不是就是人造有序的了？比方：

alter table `order` add index city_order_num_index(city, order_num);

此时，order 表的索引长这样：

文章结尾的 sql 执行语句。执行流程长这样：

  • 1、从索引 (city,order_num) 找到第一个满足 city=’ 广州’条件的主键 id；
  • 2、回表，取 city、order_num、user_code 三个字段的值，作为后果集的一部分间接返回；
  • 3、从索引 (city,order_num) 取下一个记录主键 id；
  • 4、反复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city=’ 广州’条件时循环完结。

用 explain 看下，这个过程不须要排序，更不须要长期表。只须要一次回表：

从图中能够看到，Extra 字段中没有 Using filesort 了，也就是不须要排序了。而且因为 (city,order_num) 这个联结索引自身有序，只有找到满足条件的前 1000 条记录就能够退出了，再回表一次。也就是说，只须要扫描 2000 次。

问题来了，还有没有更优解呢？

05 终极优化

下面的办法，还是有一次回表，次要是因为索引中不包含 user_code。回顾下咱们之前学过的 sql 优化，是怎么防止回表的？

查问字段，加到组合索引中呀，对应到这张表，就是把 user_code 也加到组合索引中：

alter table `order` add index city_order_num_user_code_index(city, order_num, user_code);

此时的流程长这样，间接取数据就完事了：

explain 看下执行状况：

从图中可知，Extra 字段中多了 Using index 了，也就是应用了索引笼罩。连回表都不须要了，只需扫描 1000 次。

完满~

5.1 参数调优

除此以外，还能够通过调整参数优化 order by 的执行。比方调整 sort_buffer_size 尽量大点，因为 sort_buffer 太小，排序数据量大的话，会借助磁盘临时文件排序。如果 MySQL 服务器配置高的话，能够略微调大点。

再 比方把 max_length_for_sort_data 的值调大点。如果该值过小，则会减少回表次数、升高查问性能。

06 order by 常见面试题

1、查问语句有 in 多个属性时，SQL 执行是否有排序过程？

假如当初有联结索引 (city,order_num,user_code)，执行以下 SQL 语句：

select city, order_num, user_code from `order` where city in ('广州') order by order_num limit 1000

in 单个条件，毫无疑问是不须要排序的。explain 一下：

然而，in 多个条件时；就会有排序过程，比方执行以下语句

select city, order_num, user_code from `order` where city in ('广州','深圳') order by order_num limit 1000

explain 以下，看到最初有 Using filesort 就阐明有排序过程。这是为啥呢？

因为 order_num 原本就是组合索引，满足 “city= 广州 ” 只有一个条件时，它是有序的。满足 “city= 深圳 ” 时，它也是有序的。然而两者加到一起就不能保障 order_num 还是有序的了。

2、分页 limit 过大，导致大量排序。咋办？

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

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

假如有联结索引 (age,name)，咱们需要批改为这样：查问前 10 个学生的姓名、年龄，并且依照年龄小到大排序，如果年龄雷同，则按姓名降序排。对应的 SQL 语句应该是：

select name, age from student order by age, name desc limit 10;

explain 一下，extra 的值是 Using filesort，走了排序过程：

这是因为，(age,name) 索引树中，age 从小到大排序，如果 age 雷同，再按 name 从小到大排序。而 order by 中，是按 age 从小到大排序，如果 age 雷同，再按 name 从大到小排序。也就是说，索引存储程序与 order by 不统一。

咱们怎么优化呢？如果 MySQL 是 8.0 版本，反对 Descending Indexes，能够这样批改索引：

CREATE TABLE `student` (`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
  `student_id` 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='学生表';

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

日常开发中，可能会遇到没有 where 条件的 order by，这时候 order by 前面的字段是否须要加索引呢。如有这么一个 SQL，create_time 是否须要加索引：

select * from student order by create_time;

无条件查问的话，即便 create_time 上有索引，也不会应用到。因为 MySQL 优化器认为走一般二级索引，再去回表老本比全表扫描排序更高。所以抉择走全表扫描，而后依据全字段排序或者 rowid 排序来进行。

如果查问 SQL 批改一下：

select * from student order by create_time limit m;

无条件查问，如果 m 值较小，是能够走索引的。因为 MySQL 优化器认为，依据索引有序性去回表查数据，而后失去 m 条数据，就能够终止循环，那么老本比全表扫描小，则抉择走二级索引。

07 总结

这篇文章跟你聊了聊 order by 的执行流程，以及全字段排序和 rowid 排序的区别，从而得悉，MySQL 更违心用内存去换取性能上的晋升。

与此同时，通过组合索引的索引笼罩小技巧，咱们还能够缩小回表的次数。当前设计索引的时候如果业务有波及排序的字段，尽量加到索引中，并且把业务中其余的查问字段（比方文中的 city、user_code）加到组合索引中，更好地实现索引笼罩。

当然，索引也有毛病。它占空间，有保护的代价。所以大家设计的时候还是须要依据本人的理论业务去思考。

最初，我还跟你探讨了对于 order by 的四个经典面试题，心愿对你有帮忙。

7.1 参考

  • blog.csdn.net/weixin_28917279/article/details/113424610
  • https://time.geekbang.org/col…
  • https://zhuanlan.zhihu.com/p/…

08 idea 激活

jetbrains 全家桶激活

09 大厂面试题 & 电子书

如果看到这里，喜爱这篇文章的话，请帮点个 难看。

初次见面，也不晓得送你们啥。罗唆就送 几百本电子书 和2021 最新面试材料 吧。微信搜寻 JavaFish 回复 电子书 送你 1000+ 本编程电子书；回复 面试 送点面试题；回复 1024 送你一套残缺的 java 视频教程。

面试题都是有答案的，具体如下所示：有须要的就来拿吧，相对收费，无套路获取。

正文完
java
发表至： java
2021-07-16
 0
关于java:基于Spring-Cloud的全自动化微信公众号消息采集系统
如何提升JavaScript的任务效率？学会后教给你同事
关于java:Java并发编程的艺术笔记Java线程到底有几种状态
关于java:Lock底层原理ReentrantLockAQSCondition