MySQL高级--性能优化之慢日志查询

持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第11天，点击查看活动详情

👨‍🎓作者：Java学术趴

🏦仓库：Github、Gitee

✏️博客：CSDN、掘金、InfoQ、云+社区

💌公众号：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 分析步骤

0. 是否支持，查看当前版本的MySQL是否支持show profile 功能。

show variables like 'profiling';

当前版本的MySQL支持show profile 功能并且是关闭状态。

2. 开启功能，默认是关闭，使用前需要开启。

// 开启
set profiling = ON;
// 关闭
set profiling = OFF;

3. 运行SQL。

以下运行的SQL都会在show profile 中进行记录。

// 第一个SQL
select * from whale_fall;
// 第二个SQL
select * from whale_fall;
// 第三个SQL
select sleep(4);

3. 查看结果。

show profiles;

4. 诊断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;

5. 日常开发需要注意的结论。

我们可以通过上述查询出来的状态分析此时SQL出现异常的原因

• converting HEAP to MyISAM : 查询结果太大，内存都不够用了往磁盘上搬。
• Creating tmp table : 创建临时表。
• Copying to tmp table on disk : 把内存中临时表复制到磁盘，危险！！！
• locked : 锁。