MySql高级-慢查询日志（五）

二、 慢查询日志

2.1 是什么

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。

具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long _query_time的默认值为10，意思是运行10秒以上的语句。

由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

2.2 怎么玩

2.2.1 说明

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件

2.2.2 查看是否开启以及如何开启
1.默认

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启
SHOW VARIABLES LIKE ‘%slow_query_log%’;

2.开启
如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此)

修改my.cnf文件，[mysqld]下增加或修改参数

slow_query_log和slow_query_log_file后，然后重启MySQL服务器。也即将如下两行配置进my.cnf文件

slow_query_log =1

slow_query_log_file=/var/lib/mysql/atguigu-slow.log

关于慢查询的参数slow_query_log_file，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件(主机)host_name-slow.log(如果没有指定参数slow_query_log_file的话)

3.开启了慢查询日志后，什么样的sql会记录到慢查询日志里呢

这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，

命令:SHOW VARIABLES LIKE ‘long_query_time%’;

可以使用命令修改，也可以在my.cnf参数里面修改。假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。

4.设置慢SQL的阈值时间为3秒

set global long_query_time = 3;

5.为什么设置后看不出变化

需要重新连接或新开一个会话才能看到修改值。

SHOW VARIABLES LIKE ‘long_query_time%’;

show global variables like ‘long_query_time’;

6.记录慢SQL，并后续分析

7.查询当前系统中有多少条慢SQL记录

show global status like ‘%Slow_queries%’;

8.配置步骤小结

#开启慢查询日志
set slow_query_log=1;

#设置记录sql的日志文件路径
set slow_query_log_file=/var/lib/mysql/atguigu-slow.log

#设置时间阀值，单位秒
set long_query_time=3;

set log_output=FILE

2.3 日志分析工具mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。

2.3.1 查看mysqldumpslow的帮助信息

s:表示按照何种方式排序
c:访问次数
1:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回锁定数
at:平均查询时间
t:返回前面多少条的数据
g:后面搭配一个正则匹配模式，大小写不敏感的。
2.3.2 工作常用参考
1.得到返回记录集最多的10个SQL：mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

2.得到访问次数最多的10个SQL：mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

3.得到按照时间排序的前10条里面含有左连接的查询语句：mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/atguigu-slow.log

4.另外建议在使用这些命令时结合|和more 使用，否则有可能出现爆屏情况：mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

三、show profile

3.1 是什么

MySQL提供的可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL调优的测量。

官网：https://dev.mysql.com/doc/refman/8.0/en/show-profile.html

默认情况下参数处于关闭状态，并保存最近15次的运行结果。

3.2 分析步骤

1.是否支持，看看当前的MySQL版本是否支持

show variables like 'profiling';
 
或者 
show variables like 'profiling%';
 
默认是关闭，使用前需要开启

2.开启功能，默认为关闭状态，使用前需开启

3.运行SQL

select * from emp group by id%10 limit 150000;
 
select * from emp group by id%20 order by 5;

4.查看结果，show profiles
5. 诊断SQL，show profile cpu,block io for query上一步前面有问题的SQL的数字号码

type:

1.ALL–显示所有的开销信息

2.BLOCK IO–显示块lO相关开销

3.CONTEXT SWVITCHES --上下文切换相关开销

4.CPU–显示CPU相关开销信息

5.IPC–显示发送和接收相关开销信息

6.MEMORY–显示内存相关开销信息

7.PAGE FAULTS–显示页面错误相关开销信息

8.SOURCE–显示和Source_function，Source_file，Source_line相关的开销信息

9.SWAPS–显示交换次数相关开销的信息

3.3 日常开发需要注意的结论

1、convering heap to MyISAM

查询结果太大，内存不够用，往磁盘中写入

2.creating tmp table(创建临时表)

拷贝数据到临时表，用完之后再删除
select * from emp group by id%20 limit 120000;
select * from emp group by id%20 order by 5

3.Copying to tmp table on disk

将内存中的临时表复制到磁盘，危险！！！

4.locked

已上锁

四、全局查询日志

4.1 配置启用

在mysql的my.cnf中，设置如下:

#开启
general_log=1

#记录日志文件的路径
general_log_file=/path/logfile

#输出格式
log_output=FILE

4.2 编码启用

命令：
set global general_log = 1;
 
set global log_output = 'TABLE';

此后，你所编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看

select * from mysql.general_log

永远不要在生产环境中开启此功能