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

👨‍🎓作者：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 = 1slow_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 分析步骤

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

show variables like 'profiling';

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

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

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

3. 运行 SQL。

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

// 第一个SQLselect * from whale_fall;// 第二个SQLselect * from whale_fall;// 第三个SQLselect 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 : 锁。