MySQL日志——慢查询日志

        一、慢查询日志介绍

        所谓的慢查询日志，就是会记录哪些执行时间比较长的查询语句。当查询超过一定的时间没有返回结果的时候就会记录慢查询日志。慢查询日志可以帮助DBA找出执行效率缓慢的SQL语句，为数据库优化工作提供帮助。

        慢查询日志默认是不开启的，也没有必要一直开启，在需要采样的时候再开启即可。

        二、慢查询日志的开启和关闭

        可以登录mysql后使用SET命令进行设置（推荐的方式）也可以在my.cnf文件中进行配置。

      （1）在my.cnf中配置使用慢查询日志

        在my.cnf中配置开启或关闭要生效都要重启mysql服务，因此实际生产环境是不能使用的，但是这里介绍下如何配置：

                slow_query_log=on|off                  #是否开启慢查询日志

                slow_query_log_file=filePathAndName;  #指定慢查询日志文件的路径和文件名，默认在data目录

                long_query_time=5         #指定多少秒没有返回结果的语句记录日志，即超过该时间就被认为是慢查询

                long_queries-not-using-indexes          #记录所有没有使用到索引的查询语句

                min_examined_row_limit=1000           #记录由于查询了多于1000次而导致的慢查询

                log-slow-admin-statements    #记录慢的那些OPTIMIZE TABLE、ANALZE TABLE和ALTER TABLE语句。因为这些语句本身内部会执行查询，如alert table会先查询出来再进行修改。

                log-slow-slave-statements   #记录由slave所产生的慢查询

        例如long_queries-not-using-indexes和min_examined_row_limit配合可以更加精细的控制慢查询日志。对于那些有数据量小而没有建立索引但又频繁查询的表可以使用这种方式设置后者比较大的值来过渡掉这些查询，因为这些确实不需要记录。例如设置后者比较大的值来发现那些耗时递归的子查询，因为对于递归查询的每个次查询可能执行时间非常短，但是整体的子查询执行时间可能会比较长。

        （2）登录mysql的设置，这种方式不用重启mysql服务，因此推荐使用。

                  set @@global.slow_query_log=1;   #开启慢查询日志，其中@@global.和写global 空格 都可以

                  set  @@global.long_query_time=3;  #超过3秒认为是慢查询，则记录慢查询日志。

            

            更多的参数设置可以使用如下命令查看：SHOW  VARIABLES  LIKE  ‘%slow%’;例如：

            

        更多的如 long_query_time，使用 SHOW VARIABLES  LIKE  ‘%long%‘；来查看。在my.cnf中可以配置的参数都可以通过set命令来直接在mysql中设置，这样就不用重启mysql服务了。

        注意： set global xxx =yy 和set  xxx =yyy是不同的，前者设置的全局的，而后者设置的是当前会话的，因此前者需要退出重新登录才可以生效，而后者可以立即生效。对于slow_query_log是必须使用gloable的，而对于long_query_time可以不使用global，那么设置的是当前会话。

        无论那种方式，如果不指定慢查询文件的位置和文件名，默认为数据文件目录的 {hostname}-slow.log 。在mysql的配置文件中，对于下划线和中画线是没有区别的，因此slow-query和slow_query是一样的。

        三、慢查询的测试

        在开启了慢查询日志之后，可以使用select  sleep(seconds); 来模拟出一个慢查询。其实慢查询主要的配置就是两个，一个是开启，另一个是查询执行时间。

        慢查询日子中包含了查询执行时间、返回结果数、sql执行开始时间等，例如： localhost-slow.log

       

       

        四、慢查询日志的分析

        慢查询日志使用为普通文本工具查看是不合适的，因为采样时间一般比较长因此内容比较多，而且每个记录的记录的字段比较多，因此不便于直接查看，而通常使用慢查询分析工具来进行分析。

        慢查询日志分析工具这里介绍三种：

       

        1．mysqldumpslow

        该慢查询日志分析工具是mysql自带的。这个工具就比较方便使用了，因为mysql自带而不用安装第三方的工具。该工具使用也很简单，只要将慢查询日志指定给它即可：

        mysqldumpslow  localhost-slow.log

        该工具不需要登录mysql执行，而是mysql命令目录中的一个可执行文件，例如：

       

        这里显示了其中一个慢查询语句的统计结果，也即是该慢查询语句多次执行也是对应一个结果，这个结果中统计了这个语句多次执行的统计信息。例如Count表示这个语句一共执行了几次，Time表示每次执行该语句的平局时间，（4S）表示执行Count次的总时间；Rows1.0(1)表示每次返回行数和总共返回的行数；root[root]@localhost表示由哪个mysql用户执行的该语句。Lock表示对数据加锁的时间。

        但是mysqldumpslow这个工具的分析结果还是比较简陋的，不是很方便于阅读。下面介绍的mysqlsla和pt-query-digest会更加便于阅读，尤其是慢查询日志量比较大的时候。例如msqlsla会以统计报表来展示结果。

        2．mysqlsla

        该工具是hackmysql.com开发的一款慢查询日志分析工具，通过一下地址就可以下载到或直接在Linux中使用 wget  http://hackmysql.com/scripts/mysqlsla  就可以下载到。但是现在貌似已经无法访问了。

        3．percona-toolkit包中的pt-query-digest

        percona是mysql的一个分支版本，也推出了一些mysql的相关工具。percona-toolkit中就带有多个mysql运维相关的工具。其中的pt-query-digest就是一个很好的慢查询日志分析工具。

         mysqlsla和pt-query-digest工具的使用方式和mysqldumpslow一样，但是前面两个需要到各自的安装目录中去执行，因为没有配置环境变量。然后将慢查询日志指定给它们即可，如：msyqlsld  host-slow.log

        对于mysqlsla和pt-query-digest这里就不记录了。

        五、删除慢查询日志

        无论那种日志，关闭了日志的记录后日志文件并不会消失。例如关闭了通用查询日志或慢查询日志都不会导致日志文件消失，只是停止了向其中继续写入数据而已。

慢查询日志也是会不断增长的，因此采样后要即使关闭慢查询日志，然后才是对日志的分析。当分析结束后也需要将日志删除，以免下次采样时混入了上次的结果！

        删除时首先要先关闭慢查询日志的记录，然后才是删除或清空日志文件。如果是在my.cnf中配置的慢查询日志，那么取消后需要重启mysql服务才能生效。

 

        【重点说明】如果有些操作要永久生效但是有不能停止数据库，那么可以先使用set命令开启或关闭相应的设置，并将其配置到my.cnf中。这样本次由于set是直接生效的，并且配置了my.cnf后下次重启mysql服务时由于set的失效但是my.cnf生效，那么配置最终也是生效的。这样就可以平滑的过渡了。