MySQL基础优化(4)：正确使用order by和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 */

上表中，

1. 主键是字段id
2. 普通索引有两个，分别是字段a和b的联合索引以及字段c的索引
3. 字段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;

单路模式排序过程

单路模式详细排序过程如下，

1. 从字段a的索引树中找到第一个满足a=1000的所有记录的主键 id
2. 根据主键 id的值取出整行记录，从记录中抽取出字段a、c和d的值，存入sort buffer中
3. 从字段a的索引树中继续寻找满足条件的记录对应的主键索引值
4. 重复步骤2和3，直至字段a的索引树中不存在满足条件的索引
5. 对sort buffer中的数据按照字段d的值进行排序，将排序结果返回给客户端

双路模式排序过程

双路模式详细排序过程如下，

1. 从字段a的索引树中找到第一个满足a=1000的所有记录的主键 id
2. 根据主键 id的值取出整行记录，从记录中抽取出字段d和主键id的值，存入sort buffer中
3. 从字段a的索引树中继续寻找满足条件的记录对应的主键索引值
4. 重复步骤2和3，直至字段a的索引树中不存在满足条件的索引
5. 对sort buffer中的数据按照字段d的值进行排序
6. 遍历排序好的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即可。

总结