order by 语句怎么优化？ - 雨点的名字

说明 当前演示的数据库版本5.7

一、一个简单使用示例

先创建一张订单表

CREATE TABLE `order_info` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `order_no` int NOT NULL COMMENT '订单号',
  `goods_id` int NOT NULL DEFAULT '0' COMMENT '商品id',
  `name` varchar(50) NOT NULL COMMENT '商品名称',
  `status` int NOT NULL DEFAULT '0' COMMENT '订单状态：1待支付，2成功支付，3支付失败，4已关闭',
  `pay_type` int NOT NULL DEFAULT '0' COMMENT '支付方式：1微信支付，2支付宝支付',
  `price` decimal(11,2) DEFAULT NULL COMMENT '订单金额',
  `pay_time` datetime DEFAULT NULL COMMENT '支付时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='订单信息表';

同时也在表里插了一些数据

现在有一个需求: 查询商品名称是耳机,订单号按照从小到大排序的前10个,查询结果只需订单号,商品名称,订单状态。

现在我们这里执行SQL语句

select order_no, name, status from order_info where name = '耳机' order by order_no limit 10

这条语句我们都会写，但我们知道它的执行的流程是怎么样的吗？

二、order by 原理分析

2.1、explain 分析

为了避免全表扫描，这里我们在name加上一个普通索引

alter table order_info add index idx_name(name)

我们再看下执行计划

explain select order_no, name, status from order_info where name = '耳机' order by order_no limit 10

• Extra 这个字段的Using index condition 表示该查询走了索引,但需要回表查询

• Extra 这个字段的 Using filesort 表示使用了内部排序

一般出现Using filesort 也是我们需要考虑优化的点。

Using filesort: 表示没有走索引排序,而是走了内部排序，这时MySQL会给每个线程分配一块内存用于排序，称为sort_buffer。

2.2 全字段排序

为了说明上面这条SQL查询语句的执行过程，我们先来看一下idx_name这个索引的示意图。

从图中可以看到，满足name='耳机'条件的行，是id = (2,3,5) 的这些记录。

整个完整流程如下图所示:

这里的执行流程:

1)、初始化sort_buffer，确定放入order_no、name、status这三个字段；

2)、从索引idx_name找到第一个满足name='耳机'条件的主键id，也就是图中的id=2；

3)、到主键索引取出整行，取order_no、name、status三个字段的值，存入sort_buffer中；

4)、从索引idx_name取下一个满足name='耳机'条件的主键id；

5)、重复步骤3、4直到name的值不满足查询条件为止；

6)、对sort_buffer中的数据按照字段name做快速排序；

7)、按照排序结果取前10行返回给客户端。

因为需要查询的字段不能够在idx_name索引中全部找到，所以才需要拿着idx_name索引中获取的主键，再到主键索引中获取其它属性。这个过程也叫回表。

回表: 就是指拿到主键再回到主键索引查询的过程。

全字段排序，就是把查询所需要的字段全部读取到sort_buffer中。

但如果查询的字段数据量很大呢，大到当前的sort_buffer放不下了，那怎么办呢？

那就不得不利用磁盘临时文件辅助排序。

实际上，sort_buffer的大小是由一个参数控制的：sort_buffer_size。

如果要排序的数据小于sort_buffer_size，排序在sort_buffer 内存中完成，如果要排序的数据大于sort_buffer_size，则借助磁盘文件来进行排序

我们可以通过下面的方法来确定一个排序语句是否使用了临时文件。

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* 执行语句 */
select order_no, name, status from order_info where name = '耳机' order by order_no limit 10;

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

number_of_tmp_files 表示使用来排序的磁盘临时文件数。

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

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

1)、从主键索引树，拿到需要的数据，并放到sort_buffer内存块中。当sort_buffer快要满时，就对sort_buffer中的数据排序，排完后，把数据临时放到磁盘一个小文件中。

2)、继续回到主键索引树取数据，继续放到sort_buffer内存中，排序后，也把这些数据写入到磁盘临时小文件中。

3)、继续循环，直到取出所有满足条件的数据。

4)、 最后把磁盘的临时排好序的小文件，合并成一个有序的大文件。

2、rowid排序

既然查询的字段数据量很大，大到当前的sort_buffer放不下了，就会使用磁盘临时文件，排序的性能会很差。

那是不是可以不把所有查询字段都放入sort_buffer中，而仅仅是放排序字段和该记录主键到sort_buffer中呢，这其实就是rowid 排序。

这里我们思考两个问题？

什么是rowid排序？

就是只把查询SQL需要用于排序的字段和主键id，放到sort_buffer中。

什么情况走全字段排序,什么情况下走rowid排序？

它们的切换通过一个参数控制的这个参数就是max_length_for_sort_data 它表示MySQL用于排序行数据的长度的一个参数，如果单行的长度超过这个值，MySQL 就认为

单行太大，就换rowid 排序。

我们可以通过命令看下这个参数取值。

show variables like 'max_length_for_sort_data';

默认是1024,。

因为本文示例中name,order_no,status长度=50+4+4 =58 < 1024, 所以走的是全字段排序。

接下来，我来修改一个参数，让MySQL采用另外一种算法。

SET max_length_for_sort_data = 16;

新的算法放入sort_buffer的字段，只有要排序的列（即order_no字段）和主键id。

但这时，排序的结果就因为少了name和status字段的值，不能直接返回了，整个执行流程就变成如下所示的样子：

1)、初始化sort_buffer，确定放入两个字段，即order_no和id；

2)、从索引idx_name找到第一个满足name='耳机’条件的主键id，也就是图中的id=2；

3)、到主键索引取出整行，取order_no、id这两个字段，存入sort_buffer中；

4)、从索引idx_name取下一个满足name='耳机’条件的主键id；

5)、重复步骤3、4直到不满足name='耳机’条件为止；

6)、对sort_buffer中的数据按照字段order_no进行排序；

7)、 遍历排序结果，取前10行，并按照id的值回到主键索引中取出order_no、name和status三个字段返回给客户端。

这个执行流程的示意图如下，我把它称为rowid排序。

3、全字段排序 VS rowid排序

• 全字段排序：如果sort_buffer内存足够，那效率是最高的，但如果sort_buffer内存不够的话，就需要用到磁盘临时文件，排序的性能会很差。
• rowid排序：虽然sort_buffer可以放更多数据了，相对于全字段排序而言,rowid排序会多一次回表查询。

如果MySQL认为内存足够大，会优先选择全字段排序，把需要的字段都放到sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

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

三、排序字段添加索引

上面的案例中，我们仅仅是在查询字段添加索引，那如果我们在查询条件和排序字段添加组合索引呢，那整个排序的流程又是怎么样的？

这里添加一条组合索引

drop index idx_name  on order_info;

alter table order_info add index idx_name_order_no(name,order_no);

我们再来看下执行计划

explain select order_no, name, status from order_info where name = '耳机' order by order_no limit 10

• Extra 这个字段的Using index condition 表示该查询走了索引

我们发现已经没有上面的Using filesort，说明这个查询过程不需要内部排序。

那我们再看下它的执行流程是怎么样的？

我们发现这里流程里已经不需要排序，但还需要走一次回表，那是因为我们查询字段有个status,在idx_name_order_no索引中并没有。

所以需要回表查询这个值。

那如果我们将所以改成如下呢？

 -- 删除索引
 drop index idx_name_order_no on order_info;
 
 -- 新增索引
 alter table order_info add index idx_name_order_no_status(name,order_no,status);

我们再来看下执行计划

explain select order_no, name, status from order_info where name = '耳机' order by order_no limit 10  

• Extra 这个字段的Using index 表示走了索引覆盖，也就是说都不需要回表查询主键索引了。

索引覆盖: 只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

我们再来看下当前的执行流程

这样整个查询语句的执行流程就变成了：

1. 从索引(name,order_no,status)找到第一个满足name='耳机'条件的记录，取出其中的ame,order_no,status这三个字段的值，作为结果集的一部分直接返回；

2. 从索引(name,order_no,status)取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；

3. 重复执行步骤2，直到查到第10条记录，或者是不满足name='耳机'条件时循环结束。

使用了覆盖索引，性能上会快很多。

当然，这里并不是说要为了每个查询能用上覆盖索引，就要把语句中涉及的字段都建上联合索引，毕竟索引还是有维护代价的。这是一个需要权衡的决定。

四、order by如何优化？

这里总结4点优化经验

1)、排序字段加索引

2)、只select需要的字段

3)、尝试提高 sort_buffer_size

4)、尝试提高 max_length_for_sort_data

4.1 排序字段加索引

尽量使用索引排序，如果这里使用ID排序的话，因为ID是索引字段，天生就具备有序的特性，所以这种情况都不需要放到sort buffer中去额外进行排序操作。

4.2 只select需要的字段

避免非必要的字段查询,只select需要的字段， 这点非常重要。

在这里的影响是：

1)、因为查询的字段较多可能导致数据会超出sort_buffer的容量，超出之后就需要用到磁盘临时文件，排序的性能会很差。

2)、 当select的字段大小总和>max_length_for_sort_data，排序算法会将 全字段排序 改为 rowid排序 增加一次回表查询。

4.3 尝试提高 sort_buffer_size

不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。

4.4 尝试提高 max_length_for_sort_data

提高这个参数， 会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，超出之后就需要用到磁盘临时文件，排序的性能会很差。

五、一个有意思的思考题

在上面示例中如果我们在添加 idx_name_order_no 索引，同时执行下面sql

select order_no, name, status from order_info where name = '耳机' order by order_no limit 10 

在分析explain的时候，是不会产生 Using filesort 的,因为会走idx_name_order_no索引排序。

那如果这里sql改成

select order_no, name, status from order_info where name in ('耳机', '短裤') order by order_no limit 10

1）那么这个语句执行的时候会走内部排序吗，为什么？

虽然有(name,order_no)联合索引，对于单个name内部，order_no是递增的。

但这条SQL语句不是要单独地查一个name的值，而是同时查了耳机和短裤 两个名称，因此所有满足条件的order_no就不是递增的了。也就是说，这条SQL语句需要排序。

所以答案是依然会产生 Using filesort。

2）针对上面的sql有没有什么方式来实现走idx_name_order_no索引排序？

这里，我们要用到(name,order_no)联合索引的特性，把这一条语句拆成两条语句，执行流程如下：

--  这个语句是不需要排序的，客户端用一个长度为10的内存数组A保存结果。
select order_no, name, status from order_info where name = '耳机' order by order_no limit 10

--  用相同的方法，假设结果被存进了内存数组B。
select order_no, name, status from order_info where name = '短裤' order by order_no limit 10

现在A和B是两个有序数组，然后你可以用归并排序的思想，得到order_no最小的前10值，就是我们需要的结果了。

声明: 公众号如需转载该篇文章,发表文章的头部一定要 告知是转至公众号: 后端元宇宙。同时也可以问本人要markdown原稿和原图片。其它情况一律禁止转载！