一、Order by与Group by优化
Case1:
分析: 利用最左前缀
法则:中间字段不能断,因此查询用到了name索引,从key_len=74也能看出,age索引列用在排序过程中,因为Extra字段里没有using filesort
注意:
order by age 虽然用到了索引,但是不会在key_len列体现
Case 2:
分析: 从explain的执行结果来看:key_len=74,查询使用了name索引,由于用了position进行排序,跳过了age,出现了Using filesort。
注意:
这里跳过了age,这里position是无序的,所以不会走索引
Case 3:
分析: 查找只用到索引name,age和position用于排序,无Using filesort。
Case 4:
分析: 和Case 3中explain的执行结果一样,但是出现了Using filesort,因为索引的创建顺序为name,age,position,但是排序的时候age和position颠倒位置了。
重点注意:
这边颠倒age和position,mysql不会像前面提到的where后面一样优化最左前缀
Case 5:
分析: 与Case 4对比,在Extra中并未出现Using filesort,因为age为 常量
,在排序中被优化,所以索引未颠倒,不会出现Using filesort。
Case 6:
分析: 虽然排序的字段列与索引顺序一样,且order by默认升序,这里position desc变成了降序,导致与索引的排序方式不同,从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。
Case 7:
分析: 对于排序来说,多个相等条件也是范围查询
Case 8:
可以用覆盖索引优化
二、Using filesort 文件排序原理详解
filesort文件排序方式
在使用explain分析查询的时候,利用有序索引获取有序数据显示Using index。如果MySQL在排序的时候没有使用到索引那么就会输出using filesort,即使用文件排序。
文件排序是通过相应的排序算法,将取得的数据在内存中进行排序:
- MySQL需要将数据在内存中进行排序,所使用的内存区域也就是我们通过sort_buffer_size系统变量所设置的sort buffer(排序区)。
- 这个sort buffer是每个Thread独享的,所以说可能在同一时刻在MySQL中可能存在多个sort buffer内存区域。
1. 双路排序(又叫回表排序模式)
- 首先根据相应的条件取出相应的
排序字段
和可以直接定位行数据的行 ID
- 然后在 sort buffer (内存排序)中进行排序,排序完后需要再次取回其它需要的字段;
- 用trace工具可以看到sort_mode信息里显示< sort_key, rowid >
第一遍扫描出需要排序的字段,然后进行排序后,根据排序结果,第二遍再扫描一下需要select的列数据。这样会引起大量的随机IO,效率不高,但是节约内存。排序使用quick sort,但是如果内存不够则会按照 block
进行排序,将排序结果写入磁盘文件,然后再将结果合并。
2. 单路排序
- 一次性取出满足条件行的
所有字段
,然后在sort buffer
内存中进行排序; - 用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >
- 不需要回表获取其他字段效率高,但将所有字段取出,在sort buffer中排序,占用内存
如何选择文件排序方式
MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。
- 如果 字段的总长度小于max_length_for_sort_data ,那么使用 单路排序模式
- 如果 字段的总长度大于max_length_for_sort_data ,那么使用 双路排序模式
示例验证下各种排序方式:
查看下这条sql对应trace结果如下(只展示排序部分):
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; --开启trace
mysql> select * from employees where name = 'zhuge' order by position;
mysql> select * from information_schema.OPTIMIZER_TRACE;
trace排序部分结果:
"join_execution": { --Sql执行阶段
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`employees`",
"field": "position"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": { --文件排序信息
"rows": 10000, --预计扫描行数
"examined_rows": 10000, --参与排序的行
"number_of_tmp_files": 3, --使用临时文件的个数,这个值如果为0代表全部使用的sort_buffer内存排序,否则使用的磁盘文件排序
"sort_buffer_size": 262056, --排序缓存的大小,单位Byte
"sort_mode": "<sort_key, packed_additional_fields>" --排序方式,这里用的单路排序
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
mysql> set max_length_for_sort_data = 10; --employees表所有字段长度总和肯定大于10字节
mysql> select * from employees where name = 'zhuge' order by position;
mysql> select * from information_schema.OPTIMIZER_TRACE;
trace排序部分结果:
"join_execution": {
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`employees`",
"field": "position"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": {
"rows": 10000,
"examined_rows": 10000,
"number_of_tmp_files": 2,
"sort_buffer_size": 262136,
"sort_mode": "<sort_key, rowid>" --排序方式,这里用的双路排序
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
mysql> set session optimizer_trace="enabled=off"; --关闭trace
我们先看单路排序的详细过程:
- 从索引name找到第一个满足 name = ‘zhuge’ 条件的主键 id
- 根据主键 id 取出整行,取出所有字段的值,存入 sort_buffer 中
- 从索引name找到下一个满足 name = ‘zhuge’ 条件的主键 id
- 重复步骤 2、3 直到不满足 name = ‘zhuge’
- 对 sort_buffer 中的数据按照字段 position 进行排序
- 返回结果给客户端
我们再看下双路排序的详细过程:
- 从索引 name 找到第一个满足 name = ‘zhuge’ 的主键id
- 根据主键 id 取出整行,把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
- 从索引 name 取下一个满足 name = ‘zhuge’ 记录的主键 id
- 重复 3、4 直到不满足 name = ‘zhuge’
- 对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序
- 遍历排序好的 id 和字段 position,按照 id 的值回到原表中取出 所有字段的值返回给客户端
三、总结
-
其实对比两个排序模式,单路排序会把所有需要查询的字段都放到 sort buffer 中,而双路排序只会把主键和需要排序的字段放到 sort buffer 中进行排序,然后再通过主键回到原表查询需要的字段。
-
如果 MySQL 排序内存 sort_buffer 配置的比较小并且没有条件继续增加了,可以适当把 max_length_for_sort_data 配置小点,让优化器选择使用双路排序算法,可以在sort_buffer 中一次排序更多的行,只是需要再根据主键回到原表取数据。
-
如果 MySQL 排序内存有条件可以配置比较大,可以适当增大 max_length_for_sort_data 的值,让优化器优先选择全字段排序(单路排序),把需要的字段放到 sort_buffer 中,这样排序后就会直接从内存里返回查询结果了。
-
所以,MySQL通过 max_length_for_sort_data 这个参数来控制排序,在不同场景使用不同的排序模式,从而提升排序效率。
注意:
如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序,但不能因为这个就随便增大sort_buffer(默认1M),mysql很多参数设置都是做过优化的,不要轻易调整。
版权声明:本文为博主作者:Java学习者柯十一原创文章,版权归属原作者,如果侵权,请联系我们删除!
原文链接:https://blog.csdn.net/qq_42285585/article/details/131949756