order by原理分析

目录

一、前言

二、order by简单的例子

三、order by工作原理

3.1、explain执行计划分析

3.2、全字段排序

3.3、磁盘排序

3.4 rowid排序

3.5全字段排序与rowid排序对比

四、如何优化order by

4.1 联合索引优化

4.2 索引覆盖优化

4.3 调整参数优化

五、order by注意事项

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

5.2 深分页问题

---

一、前言

日常开发中，我们经常会使用到order by，那你是否知道order by 的工作原理呢？order by的优化思路是怎样的呢？使用order by有哪些注意的问题呢？

二、order by简单的例子

我们现在创建一张表，SQL如下：

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的工作原理流程。

三、order by工作原理

3.1、explain执行计划分析

首先我们来分析一下上述sql的执行计划如下：

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

• 我们看执行计划key列，说明用到了idx_city索引。
• 执行计划Extra列的Using filesort说明用到了排序。

我们看到这个SQL既使用到了索引，同时也进行了排序。我们现在来看看他的排序是怎么样的一个原理。

3.2、全字段排序

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

现在我们来看下，是怎么通过idx_city索引查询数据的呢？如图idx_city索引树结构：

idx_city索引树叶子节点的数据为主键id数据，主键id的索引树结构如下：

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

我们来分析一下这个SQL语句执行的一个流程：

1. 首先MySQL为这次查询分配一个sort_buffer，将需要查询name，age，city字段放入sort_buffer中。
2. 通过idx_city索引树拿到city='深圳'的第一个值，也就是id=9。
3. 从主键索引树查询id=9的数据，获取到name，age，city的值放入sort_buffer。
4. 获取下一个city='深圳'的主键值，也就是id=13；
5. 重复3,4步骤，知道找到值不等于深圳的停止；
6. 现在已经找到了所有city=‘深圳’的数据，在sort_buffer按照age排序；
7. 排序好的数据前10条返回给客户端；

如图：

我们将要返回的数据都放在sort_buffer中，就是全字段排序。那么假如数据量很大，sort_buffer放不下怎么办呢？就会利用到磁盘排序，下面我们来认识一下磁盘排序。

3.3、磁盘排序

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

那我们怎么知道是否使用了磁盘排序呢，我们设置如下sql命令：

## 打开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 

执行上面SQL，我们来看输出的信息如下：

num_initial_chunks_spilled_to_disk = 0，说明没有用到磁盘排序。如果num_initial_chunks_spilled_to_disk > 0 说明使用到了磁盘排序。

我们来看看使用了磁盘排序的一个流程：

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

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

3. 继续循环，直到取出所有满足条件的数据。最后把磁盘的临时排好序的小文件，合并成一个有序的大文件。

那我我们是不是会有疑惑？既然如果sort_buffer内存会不够，那我们为什么一定要把所有需要的字段都放入内存中呢？我们是否可以就放需要排序的列在内存中？是可以的，其实rowid排序就是这样的。

3.4 rowid排序

rowid排序其实就是把我们需要排序的字段age和主键id放入sort_buffer内存中。那么什么情况下会使用rowid排序呢？其实是有个变量max_length_for_sort_data变量控制的。这个参数其实是MySQL用于控制排序行长度，如果超过了这个数据，就是使用rowid排序。我们可以通过命令看下这个参数取值。

show variables like 'max_length_for_sort_data';

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

# 修改排序数据最大单行长度为32
set max_length_for_sort_data = 32;
## 执行查询SQL
select name,age,city from staff where city = '深圳' order by age limit 10;

现在我们来分析一下rowid排序流程：

1. 将age和id字段放入sort_buffer内存中
2. 从city索引树获取city为深圳的第一条数据，通过叶子节点获取主键id=9
3. 通过主键id=9从主键索引树获取数据，拿到age数据放入sort_buffer中
4. 从city索引树，获取下一条city=深圳的数据，即id=13
5. 重复3,4操作，直到city不等于深圳为止
6. 将sort_buffer数据按照age排序

7. 遍历排序结果，取前10行，并按照 id 的值回到原表中，取出city、name 和 age 三个字段返回给客户端。

如果所示：

通过对比全字段排序的流程，我们发现多了一次回表的操作。

执行下面SQL查询一下统计信息：

## 打开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 

3.5全字段排序与rowid排序对比

• 全字段排序：sort_buffer内存不够的话，就需要用到磁盘临时文件，造成磁盘访问。

• rowid排序：sort_buffer可以放更多数据，但是需要再回到原表去取数据，比全字段排序多一次回表。

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

四、如何优化order by

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

• 我们还可以通过调整max_length_for_sort_data等参数优化；

4.1 联合索引优化

我们给city和name加上联合索引，来看下查询计划：

alter table staff add  index idx_city_age(city,age);
explain select name,age,city from staff where city = '深圳' order by age limit 10;

我们看extra列已经没有Using filesort了。为什么加上联合索引就没有用到排序了呢？因为索引本身是有序的，我们可以看下idx_city_age联合索引示意图，如下：

我们来分析一下使用联合索引的一个逻辑：

1. 从idx_city_age联合索引获取到满足条件的第一个主键id=9；
2. 通过主键id去主键索引树查询到数据 name、city、age返回给客户端；

3. 从索引idx_city_age取下一个记录主键id；

4. 重复2,3操作，直至不满足city='深圳'，循环结束；

流程如下：

通过上面流程，我们得知还是需要进行一次回表操作，那么有没有更好的优化方案呢？那就是索引覆盖，我前面文章也有提过索引覆盖的概念，有不理解的同学可以先看下之前的文章。

4.2 索引覆盖优化

我们创建联合索引 idx_city_name_age，SQL如下：

alter table staff add  index idx_city_name_age(city,name,age);

索引覆盖：在查询的数据列里面，不需要回表去查，直接从索引列就能取到想要的结果。换句话说，你SQL用到的索引列数据，覆盖了查询结果的列，就算上覆盖索引了。

4.3 调整参数优化

• 比如我们可以通过调整sort_buffer_size参数大小，因为如果sort_buffer_size较小，我们就会使用到磁盘排序，磁盘读取的性能是很差的。
• 我们可以通过调整max_length_for_sort_data大小，因为这个参数较小的话，会使用到rowid排序。rowid排序会再一次回表查询，性能很差。所以max_length_for_sort_data可以适当大一点。

五、order by注意事项

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

如图我们在city和age上有一个联合索引：

我们执行如下SQL语句：

explain select name,age,city from staff order by age limit 10;

可以看到，没有where条件。没有使用到索引，通过Extra列出现了排序。

5.2 深分页问题

什么事深分页问题，就是limit过大导致的，我们看下这样的SQL：

select id,name,balance from account where update_time> '2020-09-19' limit 100000,10;

优化方案：

• 可以记录上一页最后的id，下一页查询时，查询条件带上id，如：where id > 上一页最后id limit 10。

• 把条件转移到主键索引树如图下SQL

select id,name,balance FROM account where id >= (select a.id from account a where a.update_time >= '2020-09-19' limit 100000, 1) LIMIT 10;

参考文章：

看一遍就理解：order by详解