mysql/mariadb知识点总结(24):慢查询日志

  • A+
所属分类:mysql  数据库

这篇文章总结了mysql中慢查询日志的知识点。

 

在本博客中,"mysql"是一个系列文章,这些文章主要对mysql/mariadb的常用知识点进行了总结,每一篇博客总结的知识点有所不同,具体内容可参考mysql文章列表。

mysql文章列表直达链接:mysql知识点总结

 mysql/mariadb知识点总结(24):慢查询日志

Mysql中,日志可以按照功能分为如下几类。

错误日志

查询日志

慢查询日志

二进制日志

中继日志

innodb存储引擎级别的事务日志

上述每一类日志的相关知识点都会被总结为一篇文章,此处我们单独总结一下慢查询日志的相关知识点。

 

慢查询日志

某些sql语句执行完毕所花费的时间特别长,我们将这种响应比较慢的语句记录在慢查询日志中,不要被"慢查询日志"的名字误导,错误的以为慢查询日志只会记录执行比较慢的SELECT语句,其实不然,INSERT、DELETE、UPDATE、CALL等DML操作,只要是超过了指定的时间,都可以称之为"慢查询",被记录在慢查询日志中。聪明如你一定已经想到了,"慢查询日志"与"查询日志"的理念很像。默认情况下,慢查询日志是不被开启的,如果需要,可以手动开启,开启慢查询日志之后,默认设置下,执行超过10秒的语句才会被记录到慢查询日志中,当然,对于"慢"的定义,见仁见智,超过多长时间才是我们认为的"慢",可以自定义。

mysql/mariadb知识点总结(24):慢查询日志

先来看看跟慢查询日志相关的常用参数。

log_slow_queries :表示是否开启慢查询日志,5.6以前的版本使用此参数指定是否开启慢查询日志,5.6以后的版本使用slow_query_log取代此参数,如果你使用的mysql版本刚好是5.5,那么你可以看到这两个参数同时存在,此时我们不用同时设置它们,设置这两个参数中的任何一个,另一个也会自动保持一致。

log_output : 表示当慢查询日志开启以后,以哪种方式存放,log_output可以设置为4种值,"FILE"、"TABLE"、"FILE,TABLE"、"NONE"。此值为"FILE"表示慢查询日志存放于指定的文件中,此值为"TABLE"表示慢查询日志存放于mysql库的slow_log表中,此值为"FILE,TABLE"表示将慢查询日志同时存放于指定的文件与slow_log表中,一般不会进行这样的设置,因为这样会徒增很多IO压力,如果开启,建议设置为"table",此值为"NONE"时表示不记录查询日志,即使slow_query_log设置为ON,如果log_output设置为NONE,也不会记录慢查询日志,其实,log_output不止用于控制慢查询日志的输出,查询日志的输出也是由此参数进行控制,也就是说,log_output设置为file,就表示查询日志和慢查询日志都存放到对应的文件中,设置为table,查询日志和慢查询日志就都存放在对应的数据库表中。

slow_query_log :表示是否开启慢查询日志,此参数与log_slow_queries的作用没有区别,5.6以后的版本使用此参数替代log_slow_queries。

slow_query_log_file :当使用文件存储慢查询日志时(log_output设置为"FILE"或者"FILE,TABLE"时),指定慢查询日志存储于哪个日志文件中,默认的慢查询日志文件名为"主机名-slow.log",慢查询日志的位置为datadir参数所对应的目录位置,一般情况下为 /var/lib/mysql

long_query_time :表示"多长时间的查询"被认定为"慢查询",此值得默认值为10秒,表示超过10秒的查询被认定为慢查询。

log_queries_not_using_indexes :表示如果运行的sql语句没有使用到索引,是否也被当做慢查询语句记录到慢查询日志中,OFF表示不记录,ON表示记录。

log_throttle_queries_not_using_indexes :5.6.5版本新引入的参数,当log_queries_not_using_inde设置为ON时,没有使用索引的查询语句也会被当做慢查询语句记录到慢查询日志中,使用log_throttle_queries_not_using_indexes可以限制这种语句每分钟记录到慢查询日志中的次数,因为在生产环境中,有可能有很多没有使用索引的语句,此类语句频繁的被记录到慢查询日志中,可能会导致慢查询日志快速不断的增长,管理员可以通过此参数进行控制。

 

了解了上述关于慢查询的常用参数,我们来动手练习一下,其实有了之前实践"查询日志"的经验,再来实践"慢查询日志"还是比较容易的。

首先,开启慢查询日志功能,由于当前使用的mysql版本为5.5,所以slow_query_log与log_slow_queries两个参数同时存在,而且默认都为OFF,设置其中一个为ON即可,另一个会自动保持一致,如下设置并未保存到配置文件中,所以重启mysql服务后如下设置将会失效。

mysql/mariadb知识点总结(24):慢查询日志

此处用于演示,所以同时将慢查询日志写入日志文件与mysql.slow_log表中,但是在实际使用中,推荐将log_output设置为TABLE

开启慢查询日志以后,将慢查询的时间界限设置为3秒,如下图所示,但是需要注意,在当前会话中查询设置是否生效时,需要加上global关键字,或者在一个新的数据库连接中进行查询,否则可能无法查看到最新的更改,在当前会话中,虽然全局变量已经发生改变,但是当前会话的变量值仍然没有被改变。所以,如果在当前会话中继续试验,慢查询的时间并不为我们所设置的时间,如下图所示。

mysql/mariadb知识点总结(24):慢查询日志

综上所述,我们创建一个新的数据库连接,再次查询慢查询的时间临界值,已经全部为最新设置的3秒。

mysql/mariadb知识点总结(24):慢查询日志

 

此处我们故意使语句的执行时间超过3秒,然后查看慢查询日志中是否会记录这些语句。

mysql/mariadb知识点总结(24):慢查询日志

 

查看对应的慢查询日志,已经记录了最新的慢查询语句。

mysql/mariadb知识点总结(24):慢查询日志

当然,我们的设置是将慢查询日志同时存放到日志文件与表中,所以,查看mysql.slow_log表,如下图所示,对应日志已经被记录了。

mysql/mariadb知识点总结(24):慢查询日志

从上表中可以看出,目前已经记录了两条慢查询语句,我们也可以使用如下语句,查看从mysql服务启动以后到现在,一共记录了多少条慢查询语句

show global status like '%slow_queries%';

但是需要注意,下图中的值只是本次mysql服务启动后到当前时间点的慢查询次数统计,当mysql重启以后,如下值将清零后重新计算,而慢查询日志与slow_log表中的慢查询日志则不会被清除。

mysql/mariadb知识点总结(24):慢查询日志

 

mysqldumpslow命令

mysql自带了对慢查询日志的统计分析工具,mysqldumpslow,通过mysqldumpslow命令我们可以更加方便的从不同的维度对慢日志进行排序、查找、统计。但是mysqldumpslow只能作用于慢查询日志文件,而不会帮我们统计slow_log表,换句话说,如果我们将log_output的值设置为"TABLE",mysqldumpslow则无用武之地了,当我们将log_output的值设置为"FILE"或者"FILE,TABLE"时,我们可以通过mysqldumpslow查看对应的慢查询日志文件,即可更加方便快捷的得到我们想要的信息。

 

那么我们先来看看mysqldumpslow的一些最简单的用法,使用mysqldumpslow命令直接指定对应的慢查询日志文件即可,如下图所示

mysql/mariadb知识点总结(24):慢查询日志

为了方便测试,我们已经将log_queries_not_using_indexes设置为ON,所以会从上图中看到执行0秒仍被当做慢查询的语句。

从上图可以看出,mysqldumpslow会自动根据指定的慢查询日志进行统计,统计信息大概如下

select sleep(N)这条语句一共执行了19次,平均时间3.63秒一次,合计时间69秒,返回记录共19条。

select * from a这条语句一共执行了14次,平均时间0秒

select id from a这条语句一共执行了16次

 
 

如果我们想要对这些统计信息进行排序,可以使用-s选项,比如说根据语句的执行次数进行排序,使用-s c即可,如下图所示。

mysql/mariadb知识点总结(24):慢查询日志

 

如果想要根据语句的返回记录数量进行排序,使用-s r即可,如下图所示。

mysql/mariadb知识点总结(24):慢查询日志

 

聪明如你一定已经发现了,-s选项代表排序,根据什么排序取决于-s选项后添加的参数,此处总结出各个参数的含义。

c: 执行计数

l: 锁定时间

r: 返回记录

t: 执行时间

al:平均锁定时间

ar:平均返回记录数

at:平均执行时间

 

还可以使用-t选项,指明只查看多少条统计信息,比如只查看排序后的前两条统计信息,示例如下

mysql/mariadb知识点总结(24):慢查询日志

 

还能配合-g选项,通过正则表达式匹配我们想要查看的内容,示例如下,不过我们也可以通过grep去过滤,更符合我个人的操作习惯。

mysql/mariadb知识点总结(24):慢查询日志

mysqldumpslow 还有一些其他选项,可以使用mysqldumpslow --help查看。

  

weinxin
我的微信公众号
关注"实用运维笔记"微信公众号,当博客中有新文章时,可第一时间得知哦~
朱双印

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

目前评论:3   其中:访客  2   博主  1

    • avatar yong 3

      log_throttle_queries_not_using_indexes这个参数在生产环境中怎样设置?
      之前我采用的默认设置,慢日志文件非常大;之后设定为1,慢日志文件确实很小了,但记录的全是如下的内容:
      throttle: 2766 ‘index not used’ warning(s) suppressed.;
      那么它的实际SQL语句什么,怎么获取?

      • avatar echo 5

        留名支持