MySQL学习（4）
MySQL学习（4） 让order by、group by查询更快
1 order by 原理
在优化 order by 语句之前,需要先了解 MySQL 中排序的相关知识点和原理,为了方便讲解过程举例说明,首先创建一张测试表,建表及数据写入语句如下:
use muke; /* 使用muke这个database */ drop table if exists t1; /* 如果表t1存在则删除表t1 */ CREATE TABLE `t1` ( /* 创建表t1 */ `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(20) DEFAULT NULL, `b` int(20) DEFAULT NULL, `c` int(20) DEFAULT NULL, `d` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_a_b` (`a`,`b`), KEY `idx_c` (`c`) ) ENGINE=InnoDB CHARSET=utf8mb4 ; drop procedure if exists insert_t1; /* 如果存在存储过程insert_t1，则删除 */ delimiter ;; create procedure insert_t1() /* 创建存储过程insert_t1 */ begin declare i int; /* 声明变量i */ set i=1; /* 设置i的初始值为1 */ while(i<=10000)do /* 对满足i<=10000的值进行while循环 */ insert into t1(a,b,c) values(i,i,i); /* 写入表t1中a、b两个字段，值都为i当前的值 */ set i=i+1; /* 将i加1 */ end while; end;; delimiter ; call insert_t1(); /* 运行存储过程insert_t1 */ update t1 set a=1000 where id >9000; /* 将id大于9000的行的a字段更新为1000 */下面一起来研究下 MySQL 的排序原理:
1.1 MySQL 的排序方式
- 通过有序索引直接返回有序数据
- 通过 Filesort 进行的排序
1.2 Filesort 是在内存中还是在磁盘中完成排序的?
MySQL 中的 Filesort 并不一定是在磁盘文件中进行排序的,也有可能在内存中排序,内存排序还是磁盘排序取决于排序的数据大小和 sort_buffer_size 配置的大小。
- 如果 “排序的数据大小” < sort_buffer_size: 内存排序。
- 如果 “排序的数据大小” > sort_buffer_size: 磁盘排序。
那么你是怎么确定使用 Filesort 排序的 SQL 是在内存还是在磁盘中进行的排序操作？ 可以使用trace进行分析查看（MySQL优化：学会使用show profile和trace分析慢查询）重点关注：number_of_tmp_files这个参数。 如果=0，表示排序过程中没有使用临时文件，是在内存中完成排序的，如果>0，则表示排序过程中使用了临时文件，是在磁盘文件中进行排序的。如下图,因为 number_of_tmp_files 等于 0,表示未使用临时文件进行排序,所以是内
下面我们看看这些字段的含义：
- rows：预计扫描的行数。
- examined_rows：参与排序的行
- number_of_tmp_files：使用临时文件的个数
- sort_buffer_size：sort_buffer 的大小
- sort_mode：排序模式
再看一个用到临时文件的例子，如下图，因为 number_of_tmp_files 等于 7，所以表示使用的是磁盘排序。对于 number_of_tmp_files 等于 7 表示该 SQL 将需要排序的数据分为 7 份，然后每份单独排序，再存放在 7 个临时文件中，最后把 7 个临时文件合并成一个大的有序文件。
下面重点介绍sort_mode。
1.3 Filesort 下的排序模式
Filesort模式下存在三种排序模式，具体介绍一下：
- < sort_key, rowid >双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；
- < sort_key, additional_fields >单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；
- < sort_key, packed_additional_fields >打包数据排序模式：打包数据排序模式是单路排序的一种升级模式，与单路排序相似，区别是将 char 和 varchar 字段存到 sort buffer 中时，更加紧缩。
- 如果 max_length_for_sort_data 比查询字段的总长度大，那么使用 < sort_key, additional_fields >排序模式；
- 如果 max_length_for_sort_data 比查询字段的总长度小，那么使用 <sort_key, rowid> 排序模式。
下面一起来通过实验验证参数 max_length_for_sort_data 对排序模式的影响:
set session optimizer_trace="enabled=on",end_markers_in_json=on; SET max_length_for_sort_data = 20; select a,d from t1 order by d; /* 查询表t1的id、a、d三个字段的值，按照字段d进行排序 */ SELECT * FROM information_schema.OPTIMIZER_TRACE\GOPTIMIZER_TRACE 结果中排序信息如下图:
发现使用的排序模式是 < sort_key, additional_fields >
怎么让这条 SQL 的排序模式变成 <sort_key, rowid> 呢?下面我们来试验下:
因为 a、d 两个字段的总长度为 12,可以尝试把 max_length_for_sort_data 改为小于12的值,看排序模式是否有改变。
set session optimizer_trace="enabled=on",end_markers_in_json=on; set max_length_for_sort_data = 4; select a,d from t1 order by d; SELECT * FROM information_schema.OPTIMIZER_TRACE\GOPTIMIZER_TRACE 结果中排序信息如下图:
发现使用的排序模式确实变成了 <sort_key, rowid>。
可能讲到这里,你会有个疑问,为什么要添加 max_length_for_sort_data 这个参数让排序使用不同的排序模式呢?限定只用一种排序模式不行吗?
我们一起分析下 max_length_for_sort_data 的重要性。比如下面这条 SQL:
select a,c,d from t1 where a=1000 order by d;来看一下单路排序的执行流程：
- 从索引 a 找到第一个满足 a = 1000 条件的主键 id
- 根据主键 id 取出整行，取出 a、c、d 三个字段的值，存入 sort_buffer 中
- 从索引 a 找到下一个满足 a = 1000 条件的主键 id
- 重复步骤 2、3 直到不满足 a = 1000
- 对 sort_buffer 中的数据按照字段 d 进行排序
- 返回结果给客户端
- 从索引 a 找到第一个满足 a = 1000 的主键 id根据主键 id
- 取出整行，把排序字段 d 和主键 id 这两个字段放到 sort buffer 中从
- 索引 a 取下一个满足 a = 1000 记录的主键 id
- 重复 3、4 直到不满足 a = 1000
- 对 sort_buffer 中的字段 d 和主键 id 按照字段 d 进行排序
- 遍历排序好的 id 和字段 d，按照 id 的值回到原表中取出 a、c、d 三个字段的值返回给客户端
其实对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。
如果 MySQL 排序内存配置的比较小并且没有条件继续增加了，可以适当把 max_length_for_sort_data 配置小点，让优化器选择使用 rowid 排序算法，可以在 sort_buffer 中一次排序更多的行，只是需要再根据主键回到原表取数据。
所以 MySQL 通过 max_length_for_sort_data 这个参数来控制排序，在不同场景使用不同的排序模式，从而提升排序效率。
2 order by优化
上面我们分析了 order by 的原理，小伙伴们应该会有些优化 order by 的思路了，下面我们就一起来总结 order by 的一些优化技巧
2.1 添加合适索引
2.1.1 排序字段添加索引
首先我们看下对 d 字段（没有索引）进行排序的执行计划：
explain select d,id from t1 order by d;
再看些对 c 字段（有索引）进行排序的执行计划：
explain select c,id from t1 order by c;
可以看到，根据有索引的字段排序，在 Extra 中显示的就为 Using index，表示使用的是索引排序，对应本节内容 1.1。如果数据量比较大，显然通过有序索引直接返回有序数据效率更高。
因此可以在排序字段上添加索引来优化排序语句。
2.1.2 多个字段排序优化
有时面对的需求是要对多个字段进行排序，而这种情况应该怎么优化或者设计索引呢？首先看下面例子：
对 a、c 两个字段进行排序的执行计划：
explain select id,a,c from t1 order by a,c;
观察 Extra 字段，发现使用的是 filesort。
再看对 a、b（a、b 两个字段有联合索引）两个字段进行排序：
explain select id,a,b from t1 order by a,b;
发现使用的是索引排序。
多个字段排序的情况，如果要通过添加索引优化，得注意排序字段的顺序与联合索引中列的顺序要一致。
因此，如果多个字段排序，可以在多个排序字段上添加联合索引来优化排序语句。
2.1.3 先等值查询再排序的优化
我们更多的情况是会先根据某个字段条件查出一部分数据，然后再排序，而这类 SQL 应该如果优化呢？看下面的实验：
表 t1中，根据 a=1000 过滤数据再根据 d 字段排序的执行计划如下：
explain select id,a,d from t1 where a=1000 order by d;
可以在 Extra 字段中看到 “Using filesort”，说明使用的是 filesort 排序。
再看下根据 a=1000 过滤数据在根据 b 字段排序的执行计划（a、b 两个字段有联合索引）：
explain select id,a,b from t1 where a=1000 order by b;可以在 Extra 字段中看到“Using index”，说明使用的是索引排序。
因此，对于先等值查询再排序的语句，可以通过在条件字段和排序字段添加联合索引来优化此类排序语句。
上海稀宇极智科技有限公司公司福利 2人发布
美的集团一面85人在聊
查看3道真题和解析
你想占我便宜我就占你便宜，谁也拿我没办法，主打一个双向奔赴