MySQL 慢查询日志解析

前言

在之前的文章中，我们介绍了如何进行慢SQL优化《浅谈MySQL SQL优化》，如何编写合理索引《写会MySQL索引》，如何查看SQL执行计划《读懂MySQL执行计划》。现在回过头来看，这几篇文章均属于解决慢SQL问题的办法。而在实际应用中，如何定义慢SQL ? 如何发现慢SQL？也是值得探讨的问题。

慢查询

慢查询，到底多慢才叫慢？有没有统一的标准？其实呀，这并没有统一的标准，每个公司，甚至同一公司不同场景(数据库)都会有不同标准。例如：像OLTP(联机事务处理), OLAP (联机分析处理) 这两者对慢查询的标准就不一样，OLAP 实时性则没那么高，对慢查询容错性也会更高些。而OLTP(联机事务处理)属于事务处理型，实时性要求高，响应时间快，对慢查询几乎零容忍。在线上系统中，有很多系统事故往往就发生在慢查询上。一个成熟系统中，有监控系统实时监控线上查询运行状态，提前将慢查询筛选出来，也是避免生产事故，降低风险的有效措施。有些数据库中也内置慢查询监控，如：MySQL慢查询日志就是其一。

开启慢查询日志

在 MySQL中，提供了慢查询查询日志，基于性能方面的考虑，该配置默认为OFF(关闭) 状态。那么如何开启慢日志查询呢？其步骤如下：

在 MySQL 中，慢查询日志默认为OFF状态，通过如下命令进行查看：

mysql> show variables like "slow_query_log";

+---------------------+----------------------------------------------+

| Variable_name | Value |

+---------------------+----------------------------------------------+

| slow_query_log | OFF |

+---------------------+----------------------------------------------+

2 rows in set (0.01 sec)

通过如下命令进行设置为 ON 状态：

set global slow_query_log = "ON";

其中slow_query_log_file属性，表示慢查询日志存储位置，其日志默认名称为 host 名称。

如下所示：

mysql> show variables like "slow_query_log_file";

+---------------------+----------------------------------------------+

| Variable_name | Value |

+---------------------+----------------------------------------------+ |

| slow_query_log_file | /usr/local/mysql/data/hostname.log |

+---------------------+----------------------------------------------+

2 rows in set (0.01 sec)

也可使用 以下命令进行修改：

set global slow_query_log_file = ${path}/${filename}.log;

其中: path 表示路径， filename 表示文件名，如果不指定，其默认filename 为hostname。

慢查询 查询时间，当SQL执行时间超过该值时，则会记录在slow_query_log_file 文件中，其默认为 10 ，最小值为 0，(单位：

秒)。

mysql> show variables like "long_query_time";

+-----------------+-----------+

| Variable_name | Value |

+-----------------+-----------+

| long_query_time | 10.000000 |

+-----------------+-----------+

1 row in set (0.00 sec)

可通过以下命令进行修改：

mysql> set global long_query_time = 5;

当设置值小于0时，默认为 0。

通过上述设置后，退出当前会话或者开启一个新的会话，执行如下命令：

select sleep(11);

备注: 这里的 11 并不是固定值，仅仅为了展示，其值只需要符合以下条件即可：

该值大于等于long_query_time 值即可。

该 SQL 则会进入慢查询日志中。通过cat 命令查看后如下所示：

# Time: 200310 13:30:57

# User@Host: root[root] @ localhost [] Id: 21528

# Query_time: 6.000164 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0

SET timestamp=1583818257;

select sleep(6);慢查询日志文件

初看上面的慢查询日志，甚是有些陌生。不急，现在就来好好认识下：

慢查询日志以#作为起始符。

User@Host：表示用户 和 慢查询查询的ip地址。

如上所述，表示 root用户 localhost地址。

Query_time: 表示SQL查询持续时间， 单位 (秒)。

Lock_time: 表示获取锁的时间， 单位(秒)。

Rows_sent: 表示发送给客户端的行数。

Rows_examined: 表示：服务器层检查的行数。

set timestamp ：表示 慢SQL 记录时的时间戳。

其中 select sleep(6) 则表示慢SQL语句。

注意事项

在 MySQL 中，慢查询日志中默认不记录管理语句，如：

alter table, analyze table，check table等。

不过可通过以下属性进行设置：

mysql> set global log_slow_admin_statements = "ON";

Query OK, 0 rows affected (0.00 sec)

在 MySQL 中，还可以设置将未走索引的SQL语句记录在慢日志查询文件中(默认为关闭状态)。通过下述属性即可进行设置：

mysql> set global log_queries_not_using_indexes = "ON";

Query OK, 0 rows affected (0.00 sec)

在MySQL中，日志输出格式有支持：FILE(默认)，TABLE 两种，可进行组合使用。如下所示:

set global log_output = "FILE,TABLE";

这样设置会同时在 FILE, mysql库中的slow_log表中同时写入。

mysql数据库中的 slow_log 表中数据如下：

mysql> select * from slow_log;

+---------------------+---------------------------+------------+-----------+-----------+---------------+-------+----------------+-----------+-----------+-----------------+-----------+

| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |

+---------------------+---------------------------+------------+-----------+-----------+---------------+-------+----------------+-----------+-----------+-----------------+-----------+

| 2020-03-10 22:30:37 | root[root] @ localhost [] | 00:00:06 | 00:00:00 | 1 | 0 | mysql | 0 | 0 | 0 | select sleep(6) | 21735 |

+---------------------+---------------------------+------------+-----------+-----------+---------------+-------+----------------+-----------+-----------+-----------------+-----------+

1 row in set (0.00 sec)

需要特别注意的是：

1. 设置该属性后，只要SQL未走索引，即使查询时间小于long_query_time值，也会记录在慢SQL日志文件中。

2. 该设置会导致慢日志快速增长，开启前建议检查慢查询日志文件所在磁盘空间是否充足。

3. 在生产环境中，不建议开启该参数。

小结

以上是 MySQL慢查询相关的知识。我们可以通过该功能对慢查询进行预警，提前介入以避免导致生产事故。当然，MySQL 中还提供了一个比较好的工具 mysqldumpslow 来分析慢查询日志文件。

相关阅读:

《Java 基本功 之 AQS》

《软件设计实践》

《JDK SPI 源码解读》

《Java 代码中几类典型的 "坏味道"》