四招:
1.观察,至少跑一天,看看生产的慢SQL情况
2.开启慢查询日志,设置阙值,比如超过5秒钟的就是慢SQL
3.explain+慢查询分析
4.show profile
5.数据库服务器参数优化---运维的事
一、查询优化
1、小表驱动大表(小的数据集驱动大的数据集)
注:若大表驱动小表,则会额外增加跟数
据库连接
的资源消耗
2、exists和in
2.1exists
#语法:
select ...from tableA where exists (select 1 from tableB where tableB.key=tableA.key)
#理解:
将主查询的数据,放到子查询中做条件验证,根据验证结果(true or false)来决定主查询的数据结果是否得以保留。
#提示:
#in/exists 使用对比
3、order by 关键字优化
3.1 order by 子句,尽量使用index方式排序(按索引列的顺序),避免使用fileSort方式排序
3.1.1、 表结构
3.1.2、 案例分析
where条件中用的是age范围查询(index),此时我们只看order by中的列(同样要满足:带头大哥不死,中间兄弟不断的原则)
where条件中未使用范围查询
3.1.3 结论:
最后一个升序、降序的场景中也出现了filesort,因为 索引排序默认的是
升序
3.1.4 order by满足以下两种情况会使用index排序:
- order by 语句使用索引最左前列
- 使用where子句与order by子句条件列组合满足索引最左前列
3.2 尽可能在索引列上完成排序操作,遵照索引建的最佳左前缀
3.3 如果不在索引列上,filesort有两种算法:双路排序和单路排序
3.3.1 双路排序(两次读取硬盘,两次I/)
从磁盘取排序字段,在buffer中进行排序,排序后再从磁盘中取其它字段
3.3.2 单路排序(一次I/O)
从磁盘读取查询需要的所有列,按照order by列在buffer中对它们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据,并且把随机IO变成了顺序IO,但是它会使用更多的空间,因为它把每一行都保存在内存中了。
结论:单路优于双路,但是会出现如下问题
3.4 优化策略
为排序使用索引
4、group by 关键字优化
二、慢查询日志
1、是什么
2、怎么玩??
2.1 说明
2.2 查看是否开启?如何开启?
#查看:默认日志是关闭的状态
#开启:使用
set global slow_query_log=1
注:set global slow_query_log=1开启慢查询日志只是当前数据库生效,并非永久生效,mysql重启后则会失效。
#那么永久生效如何设置?
#慢查询日志文件存放路径
2.3 开启后哪些SQL会被记录到慢查询日志中?
由long_query_time参数控制,默认为10秒钟(
大于
10秒
才会被记录),可修改默认参数配置
2.4 案例
#查看当前多少阙值:show variables like 'long_query_time%'
#设置慢的阙值时间:set global long_query_time=3
#为什么设置后看不出变化?
#记录慢SQL并后续分析
查看慢查询日志文件
#查询当前系统中有多少条慢查询记录
2.5 配置
#永久生效,要在mysql的配置文件中配置
3、日志分析工具 mysqldumpslow
3.1 查看mysqldumpslow帮助信息
3.2 工作常用参考
三