查询优化
- 观察,至少跑一天,看看生产的慢SQL情况
- 开启慢查询日志,设置阈值,比如超过5秒钟的就是慢SQL,并将它抓取出来
- explain+慢SQL分析
- show profile
- 运维经理 or DBA,进行数据库服务器参数的调优
===总结
- 慢查询的开启并捕获
- explain+慢SQL分析
- show profile查询SQL在mysql服务器里面执行细节和生命周期情况
- SQL数据库服务器的参数调优
优化原则:小表驱动大表
####################原理(RBO)#######################
select * from A where id in (select id from B)
#等价于
for select id from B
for select * from A where A.id = B.id
#当B表的数据集必须小于A表的数据集时,用in优于exist
select * from A where id exists(select 1 from B where B.id = A.id)
#等价于
for select * from A
for select * from B where B.id = A.id
#当A表的数据集必须小于B表的数据集时,用exists优于in
注意查询顺序问题
SELECT __ FROM table WHERE EXISTS(subquery)
**可以理解为:**将主查询的数据,放到子查询中做条件验证,根据验证结果(TRUE or FALSE)来决定著查询的数据结果是否保留
EXISTS(subquery)只返回TRUE or FALSE,因此子查询中的SELECT*也可以时SELECT 1或select ‘X’,官方的说法是实际执行时会忽略SELECT清单,因此没有区别。
EXISTS子查询执行过程可能经过了优化而不是我们理解上的逐条对比,如果担忧效率问题,可进行实际检验以确定是否有效率问题。
EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代,何种最优需要具体情况具体分析。
-
尽量使用Index方式排序,避免使用filesort方式
满足两种情况会使用Index排序
- ORDER BY 语句使用引索最前列
- 使用where子句与Order BY子句条件列组合满足最左前列
#using index explain SELECT * FROM tblA WHERE age > 20 ORDER BY age; #using index,using filesort explain SELECT * FROM tblA WHERE age > 20 ORDER BY birth; #using index,using filesort explain SELECT * FROM tblA WHERE age > 20 ORDER BY birth,age; #using index explain SELECT * FROM tblA WHERE birth > '2016-01-28 00:00:00' ORDER BY birth; #using filesort 引索默认升序 explain SELECT * FROM tblA ORDER BY age ASC,birth DESC;
-
尽可能在索引列上完成排序操作,遵照索引最佳左前缀
-
如果不在索引列上,filesort有两种算法,双路排序和单路排序
- 双路(4,1之前):两次扫描磁盘 ==> 读取行指针和orderby列,对他们进行排序,然后扫描已经排好的列表,按照表中的值重新从列表中读取对应数据输出,从磁盘中取排序字段,在buffer进行排序,再从磁盘读取其他字段。
- 单路排序:从磁盘读取查询所需要的所有列,按照ORDER BY在buffer对它进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间,因为它把每一行都保存在了内存里。
有可能取出的数据总大小超出了sort_buffer 的总容量,导致每次只能取sort_buffer容量大小的数据,进行排序排完再取,从而进行多次I/O,反而导致大量I/O操作,反而得不偿失。
调优:
1.order by时不要加select *
1.1 当Query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BOLB类型时,会用改进后的算法,否则使用—多路排序
1.2 两种算法的数据都有可能超出sort_buffer的容量时,超出之后,会创建tmp文件进行合并排序,导致多次I/O,用单路排序的风险更大
2.尝试提高sort_buffer_size
3.尝试提高max_length_for_sort_data
如果设得太大,总容量超出sort_buffer_sizes的概率就增大,明显症状就是高的I/O活动和低的处理器使用率。