12. 排序、分组优化

1. 案例

# 创建索引
create index idx_age_deptid_name on emp (age,deptid,name)

# 1. 无过滤，不索引
# 单纯的添加索引无法解决using filesort，要想使索引生效，必须要添加过滤条件，哪怕是分页
explain  select SQL_NO_CACHE * from emp order by age,deptid; 

explain  select SQL_NO_CACHE * from emp order by age,deptid limit 10; 


# 2. 顺序错，必排序
# 必须要保证order by后面字段的顺序与索引的顺序一致，否则会导致using filesort
explain  select * from emp where age=45 order by deptid;
 
explain  select * from emp where age=45 order by deptid,name; 
 
explain  select * from emp where age=45 order by deptid,empno;
 
explain  select * from emp where age=45 order by name,deptid;
 
explain select * from emp where deptid=45 order by age;

3. 方向反，必排序
# 保持排序方式一致，要么全升序，要么全降序，否则也会导致using filesort
explain select * from emp where age=45 order by  deptid desc, name desc ;
 
explain select * from emp where age=45 order by  deptid asc, name desc ;

2. 索引的选择

# 查询 年龄为30岁的，且员工编号小于101000的用户，按用户名称排序
SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME ;

结论：很显然，type是ALL，这是最坏的情况，Extra里还出现了Using filesort，这也是最坏的情况，所以必须要优化。

优化过程

1. 建立一个三个字段的组合索引是否可行

    CREATE INDEX idx_age_empno_name ON emp(age,empno,NAME);
   

   

   结果：Using filesort依然存在，所以name并没有用到索引，原因是因为empno是一个范围过滤，所以索引后面的字段不会再使用索引了。

2. 上述方法失效之后，尝试改成只有两个值的索引

   CREATE INDEX idx_age_name ON emp(age,NAME);
   

   

   结果：优化掉了Using filesort，

   执行SQL发现，速度提高了4倍

   

   ​

3. 如果在保留原来age_name的基础上，再创建一个age_no的索引，会发生什么呢

   create index idx_age_eno on emp(age,empno); 
   

   

   还是出现了Using filesort，并且type还是range

   执行SQL发现，有Using filesort的sql运行速度，竟然超过了没有Using filesort的sql

   

   

结果：MySQL会在创建的一堆索引中自动选择最优的索引使用。

原因是所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序，但实际提升性能很有限。相对的empno<101000这个条件如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上效果最好，是最优选择。

结论

当范围条件和group by或者order by的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多的，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

3. 双路排序和单路排序

双路排序

MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，

读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出，

可以理解成，从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

取一批数据，要对磁盘进行两次扫描，而I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

单路排序

从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出

它的效率更快一些，避免了第二次读取数据，并且把随机ID变成了顺序IO

但是它会使用更多的空间，因为它把每一行都保存在内存中了

结论

1. 由于单路是后出的，总体而言好过双路

2. 但是用单路还是有一些问题

   • 在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取sort_buffer容量大小，再排……从而多次I/O。

   • 本来只是想省一次I/O操作，结果却导致了大量的I/O操作，得不偿失。

优化策略

增大sort_buffer_size参数的设置

增大max_length_for_sort_data参数的设置

减少select后面的查询字段

总结

提高Order By的速度

1. Order by时select * 是一个大忌只Query需要的字段， 这点非常重要。在这里的影响是：

   • 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序， 否则用老算法——多路排序。
   • 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。

2. 尝试提高 sort_buffer_size

   • 不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个SQL的 1M-8M之间调整

3. 尝试提高 max_length_for_sort_data

   • 提高这个参数， 会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，主要表现为高的磁盘I/O活动和低的处理器使用率，建议在1024-8192之间调整

4.GROUP BY关键字优化

group by 使用索引的原则几乎跟order by一致 ，唯一区别是group by 即使没有过滤条件用到索引，也可以直接使用索引。

5. 最后的索引手段：覆盖索引

简单说覆盖索引就是，select 到 from 之间查询的列 <=使用的索引列+主键

explain select * from emp where name like '%abc';

使用覆盖索引后