目录
MySQL慢查询日志,是MySQL提供的一种日志记录。所有运行时间超过long_query_time值的SQL语句,将会被记录到慢查询日志中。
MySQL中的日志包括:错误日志、二进制日志、通用查询日志、慢查询日志等等。这里主要介绍下比较常用的两个功能:通用查询日志和慢查询日志。
1)通用查询日志:记录建立的客户端连接和执行的语句。
2)慢查询日志:记录所有执行时间超过long_query_time秒的所有查询或者不使用索引的查询
一、通用查询日志
在学习通用日志查询时,需要知道两个数据库中的常用命令:
1)查看数据库中和version相关的变量
show variables like '%version%';
效果图如下:
上述命令,显示当前数据库中与版本号相关的变量。
2)查看数据库中和general相关的变量
show variables like '%general%';
可以查看,当前的通用日志查询是否开启,如果general_log的值为ON则为开启,为OFF则为关闭(默认情况下是关闭的)。
3)查看数据库中和log_output相关的变量
show variables like '%log_output%';
查看当前慢查询日志输出的格式,可以是FILE(存储在数数据库的数据文件中的hostname.log),也可以是TABLE(存储在数据库中的mysql.slow_log)。
4) 开启和配置通用查询日志
开启通用日志查询: set global general_log = on;
关闭通用日志查询: set global general_log = off;
设置通用日志输出为表方式: set globallog_output = ’TABLE’;
设置通用日志输出为文件方式: set globallog_output = ’FILE’;
设置通用日志输出为表和文件方式:set global log_output = ’FILE,TABLE’;
(注意:上述命令只对当前生效,当MySQL重启失效,如果要永久生效,需要配置my.cnf)
日志输出的效果图如下:
记录到 mysql.general_log 表中的数据如下:
记录到本地中的.log中的格式如下:
my.cnf文件的配置如下:
general_log=1 #为1表示开启通用日志查询,值为0表示关闭通用日志查询
log_output=FILE,TABLE #设置通用日志的输出格式为文件和表
二、慢查询日志
默认情况下,Mysql数据库并不启动慢查询日志,需要我们手动来设置这个参数。long_query_time的默认值为10,意思是慢查询的执行时间阈值为10S,通常设置为1秒。
当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表。
1)查看当前慢查询日志是否开启
show variables like '%quer%';
主要掌握以下的几个参数:
(1)slow_query_log 的值:ON为开启慢查询日志,OFF为关闭慢查询日志。
(2)slow_query_log_file 的值:指定了慢查询日志记录到哪个文件中(注意:默认名为主机名.log,慢查询日志是否写入指定文件中,需要指定慢查询的输出日志格式为文件,相关命令为:show variables like ‘%log_output%’;去查看输出的格式)。
(3)long_query_time 的值:指定了慢查询的阈值,即如果执行语句的时间超过该阈值则为慢查询语句,默认值为10秒。
(4)log_queries_not_using_indexes 的值:ON为会记录所有没有利用索引的查询(前提是 slow_query_log 也设置了ON),一般在性能调优的时候会暂时开启。
2)配置慢查询日志输出格式
show variables like '%log_output%';
通过log_output的值可以查看到输出的格式,上面的值为TABLE。当然,我们也可以设置输出的格式为文本,或者同时记录文本和数据库表中,设置的命令如下:
#慢查询日志输出到表中(即mysql.slow_log)
set globallog_output=’TABLE’;
#慢查询日志仅输出到文本中(即:slow_query_log_file指定的文件)
set global log_output=’FILE’;
#慢查询日志同时输出到文本和表中
set global log_output=’FILE,TABLE’;
3)慢查询日志数据分析
慢查询的日志记录myql.slow_log表中,格式如下:
慢查询的日志记录到hostname.log文件中,格式如下:
可以看到,不管是表还是文件,都具体记录了:是那条语句导致慢查询(sql_text),该慢查询语句的查询时间(query_time),锁表时间(Lock_time),以及扫描过的行数(rows_examined)等信息。
4)查看慢查询记录数
show global status like '%slow%';
(注意:上述所有命令,如果都是通过MySQL的shell将参数设置进去,如果重启MySQL,所有设置好的参数将失效,如果想要永久的生效,需要将配置参数写入my.cnf文件中)。
5) 利用mysqldumpslow命令工具分析日志文件
可参考:windows下使用MYSQL的mysqldumpslow进行慢日志分析 和 mysqldumpslow使用总结
6)慢查询检验
很简单,我们可以手动产生一条慢查询语句。比如,如果我们的慢查询log_query_time的值设置为1,则我们可以执行如下语句:
selectsleep(1);
该条语句即是慢查询语句,执行后,便可以在相应的日志输出文件或表中去查看是否有该条语句。