查询优化_排序、分组优化
查询优化_排序、分组优化
- 1.排序case
- 1.1.总结:无过滤 不索引
- 1.3.总结:方向反 必排序
- 2.索引的选择
- 3.理论:双路排序和单路排序
- 3.1.双路排序(慢)
- 3.2.单路排序(快)
- 3.3.结论及引申出的问题
- 3.4.优化策略
- 3.5.提高Order By的速度
- 4.GROUP BY关键字优化
1.排序case
CALL proc_drop_index("mydb","emp");
CALL proc_drop_index("mydb","dept");
以下是否能使用到索引,能否去掉using filesort
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;
创建索引
CREATE INDEX idx_age_deptid_name ON emp (age,deptid,NAME);
增加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)
CREATE INDEX idx_age_deptid_empno ON emp (age,deptid,empno);
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 ;
ORDER BY子句,尽量使用Index方式排序,避免使用FileSort方式排序
2.索引的选择
执行案例前先清除emp上的索引,只留主键
CALL proc_drop_index("mydb","emp");
查询 年龄为30岁的,且员工编号小于101000的用户,按用户名称排序
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME ;
结论:很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。
开始优化:思路: 尽量让where的过滤条件和排序使用上索引
1.我们建一个三个字段的组合索引可否?
CREATE INDEX idx_age_empno_name ON emp (age,empno,NAME);
我们发现using filesort 依然存在,所以name 并没有用到索引。
原因是,因为empno是一个范围过滤,所以索引后面的字段不会再使用索引了。
所以我们建一个3值索引是没有意义的
那么我们先删掉这个索引:DROP INDEX idx_age_empno_name ON emp;
2.为了去掉filesort我们可以把索引建成
CREATE INDEX idx_age_name ON emp(age,NAME);
也就是说empno 和name这个两个字段我只能二选其一。
这样我们优化掉了 using filesort。性能提升了。
3.如果我们选择那个范围过滤,而放弃排序上的索引呢
DROP INDEX idx_age_name ON emp;
CREATE INDEX idx_age_empno ON emp(age,empno);
果然出现了filesort,而且type还是range光看字面其实并不美好
结果竟然有 filesort的 sql 运行速度,超过了已经优化掉 filesort的 sql,而且快了好多倍。何故?
4.原因
所有的排序都是在条件过滤之后才执行的,所以,如果条件过滤掉大部分数据的话,剩下几百几千条数据进行排序其实并不是很消耗性能,即使索引优化了排序,但实际提升性能很有限。
相对的 empno<101000 这个条件,如果没有用到索引的话,要对几万条的数据进行扫描,这是非常消耗性能的,所以索引放在这个字段上性价比最高,是最优选择。
5.结论:
当【范围条件】和【group by 或者 order by】的字段出现二选一时,优先观察条件字段的过滤数量,如果过滤的数据足够多,而需要排序的数据并不多时,优先把索引放在范围字段上。反之,亦然。
3.理论:双路排序和单路排序
如果不在索引列上,filesort有两种算法: mysql就要启动双路排序和单路排序
3.1.双路排序(慢)
- MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据, 读取行指针和order
by列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出- 从磁盘取排序字段,在buffer进行排序,再从磁盘取其他字段。
- 取一批数据,要对磁盘进行两次扫描,众所周知,I\O是很耗时的,所以在mysql4.1之后,出现了第二种改进的算法,就是单路排序。
3.2.单路排序(快)
从磁盘读取查询需要的所有列,按照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间, 因为它把每一行都保存在内存中了。
3.3.结论及引申出的问题
但是用单路有问题
- 在sort_buffer中,单路比多路要多占用很多空间,因为单路是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量,导致每次只能取sort_buffer容量大小的数据,进行排序(创建tmp文件,多路合并),排完再取sort_buffer容量大小,再排……从而多次I/O。
- 单路本来想省一次I/O操作,反而导致了大量的I/O操作,反而得不偿失。
3.4.优化策略
- 增大sort_buffer_size参数的设置
- 增大max_length_for_sort_data参数的设置
- 减少select 后面的查询的字段。 禁止使用select *
3.5.提高Order By的速度
a. Order by时select * 是一个大忌。只Query需要的字段, 这点非常重要。在这里的影响是:
- 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时,会用改进后的算法——单路排序, 否则用老算法——多路排序。
- 两种算法的数据都有可能超出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%';
c. 尝试提高 max_length_for_sort_data
- 提高这个参数, 会增加用改进算法的概率。 SHOW VARIABLES LIKE ‘%max_length_for_sort_data%’; #默认1024字节
- 但是如果设的太高,数据总容量超出sort_buffer_size的概率就增大,明显症状是高的磁盘I/O活动和低的处理器使用率。如果需要返回的列的总长度大于max_length_for_sort_data,使用双路算法,否则使用单路算法。1024-8192字节之间调整
4.GROUP BY关键字优化
group by 使用索引的原则几乎跟order by一致 ,唯一区别:
- group by 先排序再分组,遵照索引建的最佳左前缀法则
- 当无法使用索引列,增大max_length_for_sort_data和sort_buffer_size参数的设置
- where高于having,能写在where限定的条件就不要写在having中了
- group by没有过滤条件,也可以用上索引。Order By 必须有过滤条件才能使用上索引。