查询优化_排序、分组优化

1.排序case

CALL proc_drop_index("mydb","emp");
CALL proc_drop_index("mydb","dept");
以下是否能使用到索引，能否去掉using filesort
1
2
3

1.1.总结：无过滤 不索引

EXPLAIN SELECT SQL_NO_CACHE * FROM emp ORDER BY age,deptid; 

EXPLAIN SELECT SQL_NO_CACHE * FROM emp ORDER BY age,deptid LIMIT 10; 
1
2
3

创建索引

CREATE INDEX idx_age_deptid_name ON emp (age,deptid,NAME);
1

增加limit过滤条件，使用上索引了。
## 1.2.总结：顺序错，必排序

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;
CREATE INDEX idx_age_deptid_name ON emp (age,deptid,NAME)
1
2
3
4
5
6

CREATE INDEX idx_age_deptid_empno ON emp (age,deptid,empno);
1

1.3.总结：方向反 必排序

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 ;
1
2

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序

2.索引的选择

执行案例前先清除emp上的索引，只留主键

CALL proc_drop_index("mydb","emp");
1

查询 年龄为30岁的，且员工编号小于101000的用户，按用户名称排序

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME ; 
1

结论：很显然，type 是 ALL，即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。
开始优化：思路： 尽量让where的过滤条件和排序使用上索引

1.我们建一个三个字段的组合索引可否？

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

我们发现using filesort 依然存在，所以name 并没有用到索引。
原因是，因为empno是一个范围过滤，所以索引后面的字段不会再使用索引了。
所以我们建一个3值索引是没有意义的
那么我们先删掉这个索引：DROP INDEX idx_age_empno_name ON emp;
2.为了去掉filesort我们可以把索引建成

CREATE INDEX idx_age_name ON emp(age,NAME);
1

也就是说empno 和name这个两个字段我只能二选其一。
这样我们优化掉了 using filesort。性能提升了。
3.如果我们选择那个范围过滤，而放弃排序上的索引呢

DROP INDEX idx_age_name ON emp;
CREATE INDEX idx_age_empno ON emp(age,empno);
1
2

果然出现了filesort，而且type还是range光看字面其实并不美好
结果竟然有 filesort的 sql 运行速度，超过了已经优化掉 filesort的 sql，而且快了好多倍。何故？

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

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

3.理论：双路排序和单路排序

如果不在索引列上，filesort有两种算法： mysql就要启动双路排序和单路排序

3.1.双路排序（慢）

1. MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据， 读取行指针和order
   by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
2. 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。
3. 取一批数据，要对磁盘进行两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

3.2.单路排序（快）

从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间， 因为它把每一行都保存在内存中了。

3.3.结论及引申出的问题

但是用单路有问题

1. 在sort_buffer中，单路比多路要多占用很多空间，因为单路是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取sort_buffer容量大小，再排……从而多次I/O。
2. 单路本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

3.4.优化策略

1. 增大sort_buffer_size参数的设置
2. 增大max_length_for_sort_data参数的设置
3. 减少select 后面的查询的字段。 禁止使用select *

3.5.提高Order By的速度

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

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

b. 尝试提高 sort_buffer_size

不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程（connection）的 1M-8M之间调整。 MySQL5.7，InnoDB存储引擎默认值是1048576字节，1MB。

SHOW VARIABLES LIKE '%sort_buffer_size%';
1

c. 尝试提高 max_length_for_sort_data

1. 提高这个参数， 会增加用改进算法的概率。 SHOW VARIABLES LIKE ‘%max_length_for_sort_data%’; #默认1024字节
2. 但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。如果需要返回的列的总长度大于max_length_for_sort_data，使用双路算法，否则使用单路算法。1024-8192字节之间调整

4.GROUP BY关键字优化

group by 使用索引的原则几乎跟order by一致 ，唯一区别：

1. group by 先排序再分组，遵照索引建的最佳左前缀法则
2. 当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数的设置
3. where高于having,能写在where限定的条件就不要写在having中了
4. group by没有过滤条件，也可以用上索引。Order By 必须有过滤条件才能使用上索引。