mysql查询日志 【MySQL】基础实战篇(2)—慢查询日志分析

MySQL的慢查询日志，用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过 long_query_time值的SQL,则会被记录到慢查询日志中。

long_query_time的默认值为10，意思是运行10秒以上(不合10秒)的语句，认为是超出了我们的最大忍耐时间值

它的主要作用是，帮助我们发现那些执行时间特别长的SQL查询，并且有针对性地进行优化，从而提高系统的整体效率。

当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。

比如一条sq执行超过5秒钟，我们就算慢SQL,希望能收集超过5秒的sql,结合explain 进行全面分析。

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

慢查询日志支持将日志记录写入文件。

首先

先查看一下慢查询是否已经开启

mysql> show variables like '%slow_query_log';

结果如下

+----------------+-------+| Variable_name  | Value |+----------------+-------+| slow_query_log | OFF   |+----------------+-------+1 row in set (0.03 sec)

OFF 代表关闭状态

把其设置成打开状态，如下

mysql> set global slow_query_log =on;Query OK, 0 rows affected (0.13 sec)

结果如下

mysql> show variables like'%slow_query_log';+----------------+-------+| Variable_name  | Value |+----------------+-------+| slow_query_log | ON    |+----------------+-------+1 row in set (0.00 sec)

接下来设置long_query_time值

mysql> show variables like '%long_query_time%';+-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.01 sec)

由此可说，sql中long_query_time默认值为10

语句如下：

mysql > set global long_query_time=1;mysql >show global variables like '%long_query_time%';mysql >set long_query_time=1;mysql >show variables like '%long_query_time%';

设置global 的方式对当前session的long_query_time失效。对新链接的客户端有效

具体示范如下：

mysql> set global long_query_time =1;Query OK, 0 rows affected (0.01 sec)mysql> show global variables like '%long_query_time%';+-----------------+----------+| Variable_name   | Value    |+-----------------+----------+| long_query_time | 1.000000 |+-----------------+----------+1 row in set (0.03 sec)mysql> show variables like '%long_query_time%';+-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.00 sec)mysql> set long_query_time=1;Query OK, 0 rows affected (0.01 sec)mysql> show variables like '%long_query_time%';+-----------------+----------+| Variable_name   | Value    |+-----------------+----------+| long_query_time | 1.000000 |+-----------------+----------+1 row in set (0.01 sec)mysql> show global variables like '%long_query_time%';+-----------------+----------+| Variable_name   | Value    |+-----------------+----------+| long_query_time | 1.000000 |+-----------------+----------+1 row in set (0.00 sec)

此改法在服务器重启之后，会自动失效。

所以可以在配置文件中修改设置参数以用作永久设置。

[mysqld]

slow_query_log=ON #开启慢查询日志

slow_query_log_file=/var/lid/mysql/bsk-slow.log #慢查询日志的目录和文件名信息

long_query_time=3 #设置慢查询值为3秒

long_output=FILE

查看是否存在慢查询

mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';+---------------+-------+| Variable_name | Value |+---------------+-------+| Slow_queries  | 0     |+---------------+-------+1 row in set (0.12 sec)

慢查询日志分析工具

mysqldumpslow 定位到慢查询语句

查看mysqldumpslow的帮助信息 ：

[root@localhost ~]# mysqldumpslow --helpUsage: mysqldumpslow [ OPTS... ] [ LOGS... ]Parse and summarize the MySQL slow query log. Options are  --verbose    verbose  --debug      debug  --help       write this text to standard output  -v           verbose  -d           debug  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default                al: average lock time                ar: average rows sent                at: average query time                 c: count                 l: lock time                 r: rows sent                 t: query time    -r           reverse the sort order (largest last instead of first)  -t NUM       just show the top n queries  -a           don't abstract all numbers to N and strings to 'S'  -n NUM       abstract numbers with at least n digits within names  -g PATTERN   grep: only consider stmts that include this string  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),               default is '*', i.e. match all  -i NAME      name of server instance (if using mysql.server startup script)  -l           don't subtract lock time from total time[root@localhost ~]# 

首先进入文件所在目录下

[root@localhost ~]# cd /var/lib/mysql#会出现如下文件-rw-r-----. 1 mysql mysql      178 Sep  7 06:07 localhost-slow.log#然后在进行如下语句[root@localhost mysql]# mysqldumpslow -a -s t -t 5 /var/lib/mysql/localhost-slow.log Reading mysql slow query log from /var/lib/mysql/localhost-slow.logCount: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts  Died at /bin/mysqldumpslow line 162,  chunk 1.

关闭慢查询日志

方式一：永久性方式

[mysqld]#slow_query_log =OFF

方式二：临时性方式

mysql> set global slow_query_log=off;Query OK, 0 rows affected (0.00 sec)# 查看慢查询日志功能mysql> show variables like '%slow_query_log%';+---------------------+-----------------------------------+| Variable_name       | Value                             |+---------------------+-----------------------------------+| slow_query_log      | OFF                               || slow_query_log_file | /var/lib/mysql/localhost-slow.log |+---------------------+-----------------------------------+2 rows in set (0.04 sec)

删除慢查询日志文件

[root@localhost mysql]# rm localhost-slow.log