MySQL-慢查询日志分析

慢日志查询

概述

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过 long_query_time 值的SQL，则会被记录到慢查询日志中，ong_query_time 的默认值为 10，意思是运行10S以上的语句。就会被认作是慢查询，默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

查询日志相关参数

查看是否开启慢查询日志

SHOW VARIABLES LIKE '%slow_query_log%'

开启

SET GLOBAL slow_query_log = 1;

只对当前数据库生效, 如果重启后, 则会失效，如果永久生效，必须要修改配置文件

slow_query_log = 1

slow_query_log_file = 地址

那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？

SHOW VARIABLES LIKE 'long_query_time'

SET GLOBAL long_query_time = 4;

要断开连接后, 才能生效

SHOW GLOBAL VARIABLES LIKE 'long_query_time';

SELECT sleep(4)

SHOW GLOBAL STATUS LIKE '%slow_queries%';

慢日志分析工具

参数

-s：按照那种方式排序
         c：访问计数
         l：锁定时间
         r：返回记录
         al：平均锁定时间
         ar：平均访问记录数
         at：平均查询时间
-t：是top n的意思，返回多少条数据
-g：可以跟上正则匹配模式，大小写不敏感

示例

得到返回记录集最多的10个SQL

mysqldumpslow - s r - t 10 / DATABASE / mysql / mysql06_slow.log

得到访问次数最多的10个SQL

mysqldumpslow - s c - t 10 / DATABASE / mysql / mysql06_slow.log

得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow - s t - t 10 - g “left join” / DATABASE / mysql / mysql06_slow.log

另外建议在使用这些命令时结合 | 和 more 使用，否则有可能出现刷屏的情况

mysqldumpslow - s r - t 20 / mysqldata / mysql / mysql06 - slow.log | more

Show Profile分析

概述

Show Profile 是mysql提供的可以用来分析当前会话中SQL语句执行的资源消耗情况的工具，可用于SQL调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。把一条SQL在mysql当中每一个环节耗费的时候都记录下来，默认该功能是关闭的，使用前需开启。默认保存最近15次运行的结果。

使用

1. 查看当前版本是否支持

SHOW VARIABLES LIKE 'profiling';

2. 打开 profile

SET profiling = ON

3. 查看结果

SHOW PROFILES

4. 诊断SQL

SHOW PROFILE cpu,block io FOR QUERY 88;

当出现以下选项时, 要进行优化

• converting HEAP to MyIsAM，查询结果太大，内存都不够用了，往确磁盘上存在了
• Creating tmp table，创建临时表，copy数据到临时表，用完再进行删除
• Copying to tmp table on disk，把内存中临时表复制到磁盘, 危险操作
• Locked，被锁定

全局查询日志

只允许在测试环境用, 不能在生产环境使用，在测试是可以把所有执行的SQL抓取出来查看

设置启用

SET GLOBAL general_log = 1;

SET GLOBAL log_output = 'TABLE'

此后所编写的SQL语句将会记录到mysql库里的 general_log 表中

SELECT * FROM mysql.general_log;

mysql - u root - p1234 -- DEFAULT - CHARACTER -SET = utf8