正确使用排序和分组
- order by原理
- 1)MySQL排序方式
- 2)Filesort排序详情
- Filesort的排序模式(sort_mode)
- 单路模式排序过程
- 双路模式排序过程
- max_length_for_sort_data影响排序模式
- order by优化
- 1)添加合适索引
- 排序字段添加索引
- 多字段排序优化
- 先等值查询再排序
- 2)去掉不必要的返回字段
- 3)修改参数值
- 4)几种无法利用索引排序的情况(牢记)
- 先范围查询后排序
- ASC和DESC混合使用时无法使用索引
- group by优化
- 总结
排序和分组时经常会遇到的需求,只有正确使用SQL语句才能确保较高的执行效率。
首先在数据库中创建一张表,
use test;
drop table if exists t1;
CREATE TABLE 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;
delimiter ;;
create procedure insert_t1()
begin
declare i int;
set i=1;
while(i<=10000)do
insert into t1(a,b,c) values(i,i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t1();
update t1 set a=1000 where id >9000; /* 将id大于9000的行的a字段更新为1000 */
上表中,
- 主键是字段id
- 普通索引有两个,分别是字段a和b的联合索引以及字段c的索引
- 字段d不存在索引
order by原理
1)MySQL排序方式
按照排序原理分类,MySQL的排序分为两种,
- 通过有序索引直接返回有序数据
- 通过Filesort进行排序
使用explain
对排序的SQL语句进行分析,重点关注Extra
项,
- 如果该项显示是
Using index
,表示是通过有序索引直接返回有序数据。如,
explain select id, c from t1 order by c;
返回结果如下,
MySQL的索引树有索引值及其对应的主键值,依据排序后的主键值从表中取出相应的数据行,从数据行中提取查询的字段并返回客户端。
- 如果该项显示
Using filesort
,说明该SQL是通过Filesort进行排序。如,
explain select id, d from t1 order by d;
返回结果,
2)Filesort排序详情
MySQL中Filesort不一定是在磁盘文件汇总进行排序的,也有可能是在内存中排序。内存中排序或者磁盘排序取决于数据大小和sort_buffer_size
参数的设置。
- 若
排序数据大小 < sort_buffer_size
:内存排序 - 若
排序数据大小 > sort_buffer_size
:磁盘排序
可以使用trace确定Filesort发生在磁盘还是内存中,trace的使用参考笔记。重点关注trace分析结果中的number_of_tmp_files
项。
- 如果该项为0,表示没有在排序过程中使用临时文件,说明在内存中就已经完成排序
- 如果该项大于0,表示排序过程中使用到了临时文件
以下图为例,
参数含义,
参数 | 说明 |
rows | 预计扫描行数 |
examined_rows | 参与排序的行 |
number_of_tmp_files | 使用临时文件的个数 |
sort_buffer_size | sort_buffer的大小 |
sort_mode | 排序模式 |
Filesort的排序模式(sort_mode)
Filesort下存在三种排序模式,
排序模式 | 简述 |
<sort_key, rowid>双路排序 | 先根据条件取出需要排序的数据的行ID,之后再sort buffer中进行排序,排序完成后再取回其他需要的字段 |
<sort_key, additional_fields>单路排序 | 一次性取出满足条件的所有数据,之后在sort buffer中进行排序 |
<sort_key, packed_additional_fields>打包排序 | 和单路模式相似,只是将char和varchar类型字段更加紧缩地存到sort buffer中 |
打包模式是单路模式的升级版,所以对单路和双路模式进行说明。
MySQL通过变量max_length_for_sort_data
和需要查询的字段总大小决定使用哪种排序,
- 若
max_length_for_sort_data
比查询字段的总长度大,则使用单路排序<sort_key, additional_fields> - 若
max_length_for_sort_data
比查询字段总长度小,则使用双路排序<sort_key, rowid>
查询字段长度可以理解为查询字段类型在内存中所占用的字节空间,
字段类型 | 字节 |
INT | 4 |
BIGINT | 8 |
DECIMAL(M, D) | M+2 |
DATETIME | 8 |
TIMESTAMP | 4 |
CHAR(M) | M |
VARCHAR(M) | M |
以一条SQL语句为例,对两个排序模式进行说明,
select a,c,d from t1 where a=1000 order by d;
单路模式排序过程
单路模式详细排序过程如下,
- 从字段a的索引树中找到第一个满足
a=1000
的所有记录的主键 id - 根据主键 id的值取出整行记录,从记录中抽取出字段a、c和d的值,存入sort buffer中
- 从字段a的索引树中继续寻找满足条件的记录对应的主键索引值
- 重复步骤2和3,直至字段a的索引树中不存在满足条件的索引
- 对sort buffer中的数据按照字段d的值进行排序,将排序结果返回给客户端
双路模式排序过程
双路模式详细排序过程如下,
- 从字段a的索引树中找到第一个满足
a=1000
的所有记录的主键 id - 根据主键 id的值取出整行记录,从记录中抽取出字段d和主键id的值,存入sort buffer中
- 从字段a的索引树中继续寻找满足条件的记录对应的主键索引值
- 重复步骤2和3,直至字段a的索引树中不存在满足条件的索引
- 对sort buffer中的数据按照字段d的值进行排序
- 遍历排序好的id和字段d,按照id的值返回原表中取出字段a、c和d的值返回客户端
与单路排序相比,双路查询只会将主键和需要排序的字段放入到sort buffer中进行排序,与单路排序相比双路排序存在回表的过程。
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;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
trace记录的优化器的执行过程如下,
可见,排序模式是单路模式<sort_key, additional_fields>。原因是字段a和d的长度为12,可以被设置的max_length_for_sort_data
变量容纳,直接在sort buffer中进行排序即可。
如果将该变量的值设置为小于12的值,
et 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\G
trace分析结果如下,
此时,排序模式改为双路排序。
max_length_for_sort_data
的设置主要看配置,如果配置比较好,可以适当将该变量的值设置的大一些,这样就不存在回表的过程。
order by优化
1)添加合适索引
排序字段添加索引
对存在索引的字段和不存在索引的字段进行排序,使用explain
对两个语句进行分析,
explain select d,id from t1 order by d;
explain select c,id from t1 order by c;
上面两行语句中,字段d不存在索引,而字段c存在索引。两个语句的分析结果如下,
- 语句1
- 语句2
存在索引的字段在排序的SQL语句执行过程中是使用索引进行排序的,这一点从Extra
项的值就可以看出。而不存在索引的字段排序时,Extra
项的值显示使用的是Filesort进行排序。
type
项的值显示,不存在索引时为ALL,即全表扫描。而存在索引时为index,即全索引扫描。
多字段排序优化
有时候需要使用多个字段同时对返回结果进行排序,
explain select id,a,c from t1 order by a,c;
explain select id,a,b from t1 order by a,b;
以上两个语句分别使用字段a和c、字段a和b对返回结果进行排序。其中,字段a和b存在联合索引,而a和c之间不存在联合索引。
explain
分析结果如下,
- 字段a和c
- 字段a和b
分析结果显示,使用联合字段进行排序时是使用的是索引排序;而多字段之间不存在联合索引时,使用的是Filesort。
如果多个字段进行排序,建议在多个字段上添加联合索引。值得注意的是,排序字段的顺序必须与联合索引的顺序一致。
先等值查询再排序
更多的情况是根据某个字段查出一部分数据,之后再对这部分数据进行排序。
explain select id,a,d from t1 where a=1000 order by d;
explain select id,a,b from t1 where a=1000 order by b;
上面两个查询中,先找出满足a=1000
的记录,之后分别测试使用字段d和b进行排序。其中,字段a和b之间存在联合索引。
分析的结果如下,
- 字段a和d
- 字段a和b
通过Extra
项可以看出,当存在联合索引时,能够使用索引进行排序。
对于先等值后排序的语句,可以通过在条件字段和排序字段上添加联合索引对语句进行优化。同样注意,字段的顺序与联合索引的顺序要一致。
2)去掉不必要的返回字段
有时为了省事,写SQL语句时令其返回所有的字段值,实际上并不需要全部的字段,
explain select * from t1 order by a,b; /* 根据a和b字段排序查出id,a,b,c,d字段的值 */
explain select id,a,b from t1 order by a,b; /* 根据a和b字段排序查出id,a,b字段的值 */
分析结果如下,
上面两个查询中,都使用的是字段a和b进行排序,且a和b直接存在联合索引。按照上一小节中多字段排序的讲解,应该都会走索引排序。但是语句1的排序方式是Filesort。
这种情况不使用索引排序的原因是:扫描整个索引并查找到没索引的字段的成本比扫描全表的成本更高。语句1中返回全部字段时,字段d是不存在索引的,将索引按照a和b进行排序后,再找对应的d字段的值,此时的运行消耗比全表扫描要更大。
3)修改参数值
修改与排序相关的两个参数,max_length_for_sort_data
和sort_buffer_size
的值,
-
max_length_for_sort_data
值较大时,会将所有字段放在sort buffer中进行排序,排序完成后直接返回客户端。但是不能设置过大,否则会导致CPU利用率过低或者磁盘IO过高 -
sort_buffer_size
越大,sort buffer中排序过程中产生的临时文件会少,使得数据尽可能在内存中完成排序。但是不能设置过大,否则会导致数据库服务器SWAP
4)几种无法利用索引排序的情况(牢记)
先范围查询后排序
先等值查询后排序是可以使用联合索引进行排序的,但是如果联合索引中的字段如果先使用了范围查询,则无法利用索引进行排序。
explain select id,a,b from t1 where a > 9000 order by b;
分析结果如下,
从Extra
项可以看出,该查询没有使用索引排序,原因如下,
字段a和b联合索引的索引树如下,
在树中可以看见,对于某个字段a的值,b是有序的。而对于a字段的范围查询,a字段会有多个值,取到的(a,b)的值中,b不一定有序。如上图中,
- 当
a=1
时,b的值为1,2,3。此时b的值是有序的。 - 当
a=1,a=2
时,b的值为1,2,3,1,2,3。此时b的值是无序的。
因此当a进行范围查询并要求结果对字段b进行排序,此时就需要额外对b进行排序,不如使用Filesort。
ASC和DESC混合使用时无法使用索引
联合字段对数据进行排序时,如果一个是顺序,一个是逆序,使用不了索引。
explain select id,a,b from t1 order by a asc,b desc;
分析结果如下,
group by优化
默认情况下,会对group by字段进行排序,优化方式与order by基本一致。不需要排序时,指定order by null
即可。
总结