Order By底层原理

Order By 普通执行步骤

现在有一张表tb_user表，表里有五个字段id、name、age、create_time、city。

表结构如下：

sql
 CREATE TABLE `tb_user` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `name` varchar(20) NOT NULL COMMENT '姓名',
   `age` int(11) DEFAULT NULL COMMENT '年龄',
   `city` varchar(20) DEFAULT NULL COMMENT '所在城市',
   `create_time` datetime DEFAULT NULL COMMENT '创建时间',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

假设有如下查询语句

sql
 select * from user where age =18 order by crate_time desc;

执行过程如下

1、根据SQL条件过滤数据，这里会把age=18之外的数据先过滤掉。

2、把符合条件的数据放到sort buffer里（sort buffer是在内存的）。

3、在sort buffer里根据create_time对数据进行排序。

4、返回客户端排序完成的数据。

Sort buffer空间不够怎么办？

上面我们已经知道了需要进行排序的数据全部会放到sort buffer里进行排序，sort buffer是在内存中的一块空间，我们可以通过sort_buffer_size 参数来设置sort buffer的大小。但我们设置的空间始终是有限的，如果需要排序的数据量大于sort buffer的空间这时怎么办。

如果当sort buffer空间无法容纳我们需要排序的数据时，这时会采用另外一种临时文件的方式进行排序，临时文件排序采用归并排序的算法，首先会把需要排序的数据拆分到多个临时文件里同步进行排序操作，然后把多个排好序的文件合并成一个结果集返回给客户端，不过在临时文件里排序相对于在sort buffer里排序来说，性能会慢很多，因为毕竟一个是在内存里操作，而一个是需要在磁盘文件里操作。

Mysql如何避免临时文件排序

当数据需要生成临时文件来进行排序时，那么效率就必定慢了下来，访问磁盘数据和内存数据的速度是完全不同的，所以Mysql会尽量避免使用功能临时文件排序，这里Mysql根据单行数据的长度是否大于max_length_for_sort_data参数设置的值来判断是否可能会用到文件排序，当行数据长度大于max_length_for_sort_data时，它会进行优化，这里它的优化思路是尽量不把非必要的字段放到sort buffer中去。

什么是非必要的数据呢，那以上面的案例来说，我们要对create_time字段进行排序，那么除了create_time字段外，其它的数据其实都可不必放到sort buffer中去，我们是不是可以先把create_time列的数据放到sort buffer里面去排好序，然后再查询出其它的关联字段返回给客户端。

因为排好序之后还要关联查询出其它列的数据，所以除了create_time之外，我们还需要有id字段，所以ID字段我们也是必须要查询到sort buffer里面的。这样的话执行流程大致如下：

1、把符合条件create_time、id列查询出来放到sort buffer里。

2、在sort buffer里根据create_time字段对数据进行排序。

3、把排好序的数据根据id再拿到city、name等其他字段。

4、把结果返回给客户端。

Mysql的优化方式的确避免了临时文件的排序，但是也是有相应损失的，这就是会在排序完之后不可避免的要查询一下原始数据把其它字段值读取出来，相对于sort buffer来说性能自然差一点，但相对于文件排序来说这种方式显然性能更佳，虽然多读一次表，但操作还是在内存里进行的。

总结

1、Mysql在默认情况下排序是先进行数据过滤，然后把符合条件的数据放到sort buffer中进行排序再返回结果。

2、当sort buffer空间不足时，会采用临时文件进行排序。

3、mysql为了尽量避免使用临时文件排序，所以会进行一下优化，它会避免非必要的字段放到sort buffer中进行排序，而是排序完之后再查询原始数据表来把相关字段的值读取出来，但是这里会不可避免的造成多读一次表的操作。所以这种排序方式相对于所有数据直接放在sort buffer中排完序返回效率要慢一些。

全字段排序

全字段排序在内存中进行，不涉及磁盘IO、不需要回表操作，但数据量受内存大小影响，有局限性。语句执行流程：

初始化sort_buffer，将需要查询的字段放入sort_buffer。
逐行或从索引中找到满足where条件的主键id。
将主键id索引取出整行，取查询出的字段值放入sort_buffer。
取下一个满足where条件的主键id。
重复步骤3、4，直到不满足where条件。
对sort_buffer中的数据按order by条件快速排序。
按照排序结果取数据返回。

rowid排序

rowid排序涉及磁盘IO，需要一次回表操作，不受内存大小限制。当排序的字段较多时，内存可放下的行数很少，需要分成很多个临时文件，排序性能很差，即MySQL认为排序的单行长度太大会使用rowid排序。语句执行流程：

初始化sort_buffer，将需要查询的字段放入sort_buffer。
逐行或从索引中找到满足where条件的主键id。
到主键id索引中取出整行，将查询的字段数据放入sort_buffer中。
取出下一个记录的主键id。
重复步骤3、4直到不满足where条件为止。
对sort_buffer中的数据按order by的条件进行排序。
遍历排序结果，取数据返回。控制用于排序的行数据的长度，单行长度超过该值，MySQL更换排序算法 SET max_length_for_sort_data = 16;

使用索引排序

语句执行流程：

从索引找到第一个满足where条件的主键id
到主键id索引取出整行，找出需要返回的字段，作为结果集直接返回
从索引取下一个记录主键的id
重复步骤2、3，直到不满足where条件时循环结束。
当索引上的字段信息满足查询的结果集时（即覆盖索引），则不需要再回到主键索引上取数据。步骤2就变成了从索引上取出数据，作为结果集直接返回，节省一步回表操作。

使用覆盖索引时，explain语句的Extra字段会多了个"Using index"，表示使用索引覆盖。

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

根据order by的原理我们可以得到一些SQL优化思路。

1、可以适当的调大一些sort_buffer_size 。

2、避免非必要的字段查询，因为这些字段越多，所需要的空间越大，就很有可能导致sort buffer空间不够，转而使用其他效率低的排序策略。

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

修改sort_buffer_size

修改/etc/my.conf配置文件，重启mysql生效


[mysqld]
# 固定Mysql sort_buffer_size参数
sort_buffer_size=1M
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\mysql-8.0.25-winx64  # 切记此处一定要用双斜杠\\，单斜杠我这里会出错
# 设置mysql数据库的数据的存放目录
datadir=D:\mysql-8.0.25-winx64\\data   # data目录之后初始化会自动创建
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4

不重启，会话生效


show variables like 'sort_buffer_size'; -- 查询sort_buffer_size大小，默认是256k

SET GLOBAL sort_buffer_size = 1024*1024; -- 设置全局级别的sort_buffer_size
SET session sort_buffer_size = 2*1024*1024; -- 设置会话级别的sort_buffer_size