持续创作,加速成长!这是我参与「掘金日新计划 · 10 月更文挑战」的第11天,点击查看活动详情
👨🎓作者:Java学术趴
💌公众号:Java学术趴
🚫特别声明:原创不易,未经授权不得转载或抄袭,如需转载可联系小编授权。
🙏版权声明:文章里的部分文字或者图片来自于互联网以及百度百科,如有侵权请尽快联系小编。微信搜索公众号Java学术趴联系小编。
☠️每日毒鸡汤:一件事你犹豫去不去做,那就是该立即动身做的。
1 慢查询日志
1.1 慢查询日志定义
- MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阈值的语句,具体指运行时间超过 long_query_time 值的SQL,则会被记录到慢查询日志中。
- 具体指运行时间超过 long_query_time值的SQL,则会记录到慢查询日志中。 long_query_time的默认值为10,意思是运行10秒以上的语句。
- 由他来查看哪些SQL超出了我们的最大忍耐时间值,比如一条SQL执行超过5秒,我们就算慢SQL,希望能收集超过5秒的SQL,集合之前explain进行全面分析。
1.2 慢查询
- 默认情况下,MySQL数据库是没有开启慢查询日志, 需要我们手动来设置这个参数。
- 如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。
查看慢查询的状态
show variables like '%slow_query_log%';
默认情况下 slow_query_name = OFF,表示慢查询日志是禁用的。
默认情况下会给一个缺省的文件host_name-slow.log,当我们指定路径之后就会覆盖这个默认的缺省文件路径
开启/关闭慢查询日志
// 开启
set global slow_query_log = 1;
// 关闭
set global slow_query_log = 0;
slow_query_log = NO,此时代表慢查询日志开启。
以上这种配置方式只是临时生效,如果想要永久生效,就必须修改配置文件 my.cnf(其他系统变量也是如此)
修改my.cnf文件,在[mysqld]下增加或修改参数
slow_query_log = 1
slow_query_log_file = 慢查询日志文件存储地址
查看等待时间阈值
show variables like '%long_query_time%'l
这个是由参数long_query_time控制的,默认情况下long_query_time的值为10秒。
假设运行时间正好等于 long_query_time 的情况,并不会被记录下来。也就是说:在MySQL源码里是 判断大于 long_query_time,而非大于等于。
设置等待时间阈值
set global long_query_time = 3;
- 即使我们修改了阈值之后,再次查询发现阈值没有发生变化,其实已经变化了并且已经生效了,此时需要重启MySQL服务。
- 修改完等待阈值,如果不想重启服务器查询等待阈值,可以使用全局命令查看。
show global variables like '%long_query_time%';
4.6 Show Profile
4.6.1 show profile定义
- show profile提供可以用来分析当前会话中语句执行的资源消耗情况,可以用于SQL的调优测量。
- 默认情况下,参数处于关闭状态,并保存最近15次的运行结果。
4.6.2 show profile 分析步骤
- 是否支持,查看当前版本的MySQL是否支持show profile 功能。
show variables like 'profiling';
当前版本的MySQL支持show profile 功能并且是关闭状态。
- 开启功能,默认是关闭,使用前需要开启。
// 开启
set profiling = ON;
// 关闭
set profiling = OFF;
- 运行SQL。
以下运行的SQL都会在show profile 中进行记录。
// 第一个SQL
select * from whale_fall;
// 第二个SQL
select * from whale_fall;
// 第三个SQL
select sleep(4);
- 查看结果。
show profiles;
- 诊断SQL。
查询不同的性能参数
- ALL :显示所有的开销信息。
- BLOCK IO : 显示块IO相关开销。
- CONTEXT SWITCHES : 上下文切换相关开销。
- CPU : 显示CPU相关开销信息。
- IPC : 显示发送和接收相关开销信息。
- MEMORY : 显示内存相关开销信息。
- PAGE FAULTS : 显示页面错误相关开销信息。
- SOURCE:显示和Source_function,Source_file,Souce_line相关的开销信息。
- SWAPS:显示交换次数相关开销信息。
show profile cpu,block io for query 3;
- 日常开发需要注意的结论。
我们可以通过上述查询出来的状态分析此时SQL出现异常的原因
- converting HEAP to MyISAM : 查询结果太大,内存都不够用了往磁盘上搬。
- Creating tmp table : 创建临时表。
- Copying to tmp table on disk : 把内存中临时表复制到磁盘,危险!!!
- locked : 锁。