MySQL慢查询日志

注: 这篇文章是对MySQL官方文档慢查询日志https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html的翻译.

慢查询日志里的内容包括执行时间超过long_query_time 并且数据行数超过min_examined_row_limit的SQL命令. 慢查询日志能够记录执行时间很长的命令, 然后可以作为优化的对象. 尽管如此, 诊断一个慢查询也是一个很耗时的任务. 为了让这个任务简单一些, 你可以用mysqldumpslow 命令去处理慢查询日志, 然后统计分析其中的内容. 这是另外一个故事了. 参照Section 4.6.9, “mysqldumpslow — Summarize Slow Query Log Files”.

获取锁的时间不会挤入命了执行时间. mysqld会在命令执行完之后以及所有锁释放之后将慢查询命令写入日志, 所以日志的顺序可能会与实际的执行顺序不同.

慢查询日志参数

long_query_time 的最小值是0, 默认值是10. 设定值可以精确到毫秒microseconds.

默认情况下, 管理员命令和没有用到索引的命令是不计入到日志里的. 这个行为可以用设定log_slow_admin_statements和log_queries_not_using_indexes这两个参数来改变.

不指定值或者指定为1, 就会启动日志. 如果指定为0, 则关闭了日志. 要指定日志文件名称, 使用

–slow_query_log_file=file_name. 要指定日志的路径的话, 使用log_output 系统变量 (参照 Section 5.4.1, “Selecting General Query Log and Slow Query Log Output Destinations”).

Log_output可以设置将满查询存储在文件里或者是表里, 详见文档

如果你没有指定慢查询日志文件名称, 默认的名称是host_name-slow.log. MySQL服务会把文件存储到数据路径下, 如果用户没有指定另外的存储路径的话.

在运行时启用或关闭慢查询日志或者改变日志文件, 可以使用slow_query_log 和 slow_query_log_file 这两个系统变量. slow_query_log 设置为0则关闭, 设置为1则启用. 设置slow_query_log_file 修改日志文件名称. 如果日志文件已经有了, 则会关闭此文件并新生成一个日志文件.

如果设置了--log-short-format 这个参数, 服务会写入简短的慢查询日志.

如果想记录慢查询里的管理员命令, 则设置log_slow_admin_statements 这个系统变量. 管理员命令包括ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, 以及 REPAIR TABLE.

如果要把没有使用索引的慢查询命令写入日志, 需要启用log_queries_not_using_indexes 这个系统变量(需要注意的是, 启动了这个变量的情况下, 如果一张表只有一条数据, 就算没有索引, 也不会把明亮写入日志, 因为有没有索引对查询速度都没有影响)

没有用到索引的命令被记录的话, 慢查询日志会增加的很快. 我们需要设置log_throttle_queries_not_using_indexes 这个参数来做一下限制. 这个变量默认是0, 就是说没有限制. 设置成正数代表限制一分钟写入多少条没有用到索引的命令. 当60秒记录到了这个限制的慢查询之后, 剩下的慢查询会被压缩, 日志会记录统计有多少条, 以及总的耗时. 在下一个60秒会重复这个动作.

数据库服务会用下面的控制参数来决定是否写入查询到慢查询日志:

1. log_slow_admin_statements 参数决定是否写入管理员命令;
2. 查询必须大于等于long_query_time 秒, 或者 启动log_queries_not_using_indexes 写入没有用到索引的查询;
3. 查询数据量必须大于等于min_examined_row_limit 行;
4. log_throttle_queries_not_using_indexes 参数决定是否压缩查询命令.

log_timestamps 系统变量控制慢查询日志里时间的时区(以及一般的日志和错误日志). 他并不会影响到日志信息写入到日志表的时区, 但是从表里取出的数据可以通过CONVERT_TZ()命令来转换时区, 或者通过设置 time_zone 系统变量.

默认请款下, salve数据库不会往满查询日志卸乳重复数据. 要写入的话, 可以启用log_slow_slave_statements这个系统变量. 需要注意的是, 如果row-based从数据库使用的是 (binlog_format=ROW), 那么log_slow_slave_statements 这个参数是不起作用的. 查询以statement的格式写入二进制日志时才会被添加到慢查询日志, 也就是说, binlog_format=STATEMENT 设置后, 或者设置 binlog_format=MIXED 但是statement statement的格式被记录, 满查询才会被记录. 如果设置 binlog_format=ROW , 慢查询则不会被记录, 即使启用了log_slow_slave_statements .

慢查询日志内容

启用了慢查询, 服务会把输出写入到log_output 这个路径下. 如果你启动日志, 服务会打开日志文件并写入启动信息. 但是, 如果没有设置日志文件路径, 不会进一步写入慢查询. 如果日志路径设置为None, 就算启动了日志, 也不会写入慢查询. 设置日志文件名称并不会影响到这个机制.

如果启动了日志并且设置了日志文件路径, 每一个慢查询写入的时候会占用一行, 以#开头, 包括这些字段:

• Query_time

  执行的时间, 单位秒

• Lock_time

  获取锁的时间, 单位秒

• Rows_sent

  返回给客户端的数据行数

• Rows_examined

  服务层面查询出的数据行总量

启用log_slow_extra这个参数的话, 服务会忘日志里写入额外的信息, 包括Thread_id, Errno等, 这个不做详述.

番外

上面提到的系统变量(system variables)怎么查询到具体的值呢, 参照这篇文档: https://dev.mysql.com/doc/refman/8.0/en/show-variables.html

GLOBAL和SESSION变量的区别是作用域不同, GLOBAL的作用域是全局, SESSION之作用于当前连接.

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

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

mysql> show session variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.500000 |
+-----------------+----------+
1 row in set (1.22 sec)