正确使用排序和分组
- 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\Gtrace记录的优化器的执行过程如下，
可见，排序模式是单路模式<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\Gtrace分析结果如下，
此时，排序模式改为双路排序。
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即可。
总结
