order by、group by 效率分析
前提：数据准备
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 0,
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 */
1.1mysql排序方式：
按照排序原理分，MySQL 排序方式分两种：
- 通过有序索引直接返回有序数据
- 通过 Filesort 进行的排序
怎么确定某条排序的 SQL 所使用的排序方式？
explain select id,c from t1 order by c;
如果该字段里显示是 Using index，则表示是通过有序索引直接返回有序数据
explain select id,d from t1 order by d;
如果该字段里显示是 Using filesort，则表示该 SQL 是通过 Filesort 进行的排序
1.2 Filesort 是在内存中还是在磁盘中完成排序的？
MySQL 中的 Filesort 并不一定是在磁盘文件中进行排序的，也有可能在内存中排序，内存排序还是磁盘排序取决于排序的数据大小和 sort_buffer_size 配置的大小。
- 如果 “排序的数据大小” < sort_buffer_size: 内存排序
- 如果 “排序的数据大小” > sort_buffer_size: 磁盘排序
1.3 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> 排序模式。
2 order by 优化
2.1排序字段添加索引：
首先我们看下对 d 字段（没有索引）进行排序的执行计划：
再看些对 c 字段（有索引）进行排序的执行计划：
explain select c,id from t1 order by c;
可以看到，根据有索引的字段排序，在 Extra 中显示的就为 Using index，表示使用的是索引排序。如果数据量比较大，显然通过有序索引直接返回有序数据效率更高。
2.2多个字段排序优化
对 a、c 两个字段进行排序的执行计划：
explain select id,a,c from t1 order by a,c;
再看对 a、b（a、b 两个字段有联合索引）两个字段进行排序：
发现使用的是索引排序。
多个字段排序的情况，如果要通过添加索引优化，得注意排序字段的顺序与联合索引中列的顺序要一致。
因此，如果多个字段排序，可以在多个排序字段上添加联合索引来优化排序语句
2.3 先等值查询再排序的优化
我们更多的情况是会先根据某个字段条件查出一部分数据，然后再排序，而这类 SQL 应该如果优化呢？
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;
因此，对于先等值查询再排序的语句，可以通过在条件字段和排序字段添加联合索引来优化此类排序语句。
2.4 去掉不必要的返回字段
有时，我们其实并不需要查询出所有字段，但是可能因为习惯问题，就写成查所有字段的数据了。我们看下下面两条 SQL 的执行计划：
select * from t1 order by a,b;
explain select id,a,b from t1 order by a,b;
根据执行计划的结果，可以看到，查询所有字段的这条 SQL 是 filesort 排序，而只查 id、a、b 三个字段的 SQL 是 index 排序，为什么查询所有字段会不走索引？
这个例子中，查询所有字段不走索引的原因是：扫描整个索引并查找到没索引的行的成本比扫描全表的成本更高，所以优化器放弃使用索引。
2.5修改参数
- max_length_for_sort_data：如果觉得排序效率比较低，可以适当加大 max_length_for_sort_data 的值，让优化器优先选择全字段排序。当然不能设置过大，可能会导致 CPU 利用率过低或者磁盘 I/O 过高；
- sort_buffer_size：适当加大 sort_buffer_size 的值，尽可能让排序在内存中完成。但不能设置过大，可能导致数据库服务器 SWAP。
3.几种无法利用索引排序的情况
3.1ASC 和 DESC 混合使用将无法使用索引
explain select id,a,b from t1 order by a asc,b desc;
3.2 查询范围索引问题
在前面讲过：对于先等值过滤再排序的语句，可以通过在条件字段和排序字段添加联合索引来优化；但是如果联合索引中前面的字段使用了范围查询，对后面的字段排序是否能用到索引排序呢？
explain select id,a,b from t1 where a>9000 order by b;
这里对上面执行计划做下解释：首先条件 a>9000 使用了索引（关注 key 字段对应的值为 idx_a_b）；在 Extra 中，看到了“Using filesort”，表示使用了 filesort 排序，并没有使用索引排序。所以联合索引中前面的字段使用了范围查询，对后面的字段排序使用不了索引排序。
3.3 最后说到 group by 语句的优化，如果只要分组，没有排序需求的话，可以加 order by null 禁止排序。
