数据库监控与调优【三】—— 慢查询日志与分析

慢查询日志与分析

TIPS

本文基于MySQL 8.0，理论支持MySQL 5.0及更高版本。

慢查询日志

慢查询日志是MySQL内置的一项功能，可以记录执行超过指定时间的SQL语句

发现慢SQL的工具

• Skywalking
• VisualVM
• JavaMelody

慢查询日志相关参数与默认值

• log_output: 指定把慢查询日志输出到哪里，默认为 FILE, 表示文件; 设置成 TABLE, 则将日志记录到mysql.slow_log表中, 也可以设置多种格式, 比如 FILE, TABLE，表示会同时把慢查询日志记录到文件和mysql.slow_log表中
• long_query_time: 执行时间超过这么久才记录到慢查询日志, 单位秒, 可使用小数表示小于秒的时间。默认值: 10, 生产环境一般设置为500毫秒
• log_queries_not_using_indexes: 是否要将未使用的索引的SQL记录到慢查询日志中, 此配置会无视 long_query_time的配置. 生产环境建议关闭; 开发环境建议开启。默认值: OFF
• log_throttle_queries_not_using_indexes: 和log_queries_not_using_indexes 配合使用, 如果 log_queries_not_using_indexes 打开, 则改参数将限制每分钟写入的, 未使用索引的SQL数量。默认值: 0
• min_examined_row_limit: 扫描行数至少达到这么多才记录到慢查询日志。默认值: 0
• log_slow_admin_statements: 是否要记录管理语句, 默认关闭. 管理语句包括 ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE. 默认值: OFF
• slow_query_log_file: 指定慢查询日志文件路径。默认值: /var 路径
• log_slow_slave_statements: 该参数在从库上设置, 决定是否记录在复制过程中超过long_query_time的SQL. 如果binlog格式是 row, 则该参数无效. 只有当binlog格式是 statement才有效。默认值: OFF
• log_slow_extra: 当log_output = FILE时, 是否要记录格外信息(MySQL 8.0.14 开始提供), 对log_output=TABLE的结果无影响. 默认值: OFF

使用方式

方式一：修改配置文件 my.cnf, 在[mysqld] 段落中添加如上参数即可

[mysqld]
...

log_output = 'FILE,TABLE' ;
slow_query_log = ON
long_query_time = 0.001

然后重启MySQL即可

service mysqld restart

方式二：通过全局变量设置

这种方式无需重启即可生效，但是mysql一旦重启，配置就会失效。

首先执行全局设置

set global slow_query_log = 'ON';
set global log_output = 'FILE,TABLE';
set global long_query_time = 0.001;

这样设置之后, 就会将慢查询日志同时记录到文件以及mysql.slow_log表中。

## 设置完直接查询可能还是原来的10.000000，需要关闭当前会话重新创建一个会话即可
show variables like '%long_query_time%';

## 记录没有使用索引的查询
set global log_queries_not_using_indexes = 'ON'; 

## 执行一条sql语句测试
select * from employees; 

## 使用表的方式打开sql日志，然后可以针对query_time字段做筛选，针对SQL再进行调优
select * from `mysql`.slow_log;  

## 使用文件的方式打开sql日志，可以看到文件路径
show variables like '%slow_query_log_file%';  

分析慢查询日志文件工具

当log_output = FILE 时，可以使用mysqldumpslow工具分析，这是mysql内置的工具。

mysqldumpslow参数

➜ mysqldumpslow --help
Usage: 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           展示更详细的信息
  -d           debug
  -s ORDER     以哪种方式排序，默认at
                al: 平均锁定时间
                ar: 平均返回记录数
                at: 平均查询时间
                 c: 访问计数
                 l: 锁定时间
                 r: 返回记录
                 t: 查询时间
  -r           将-s的排序倒序
  -t NUM       top n的意思，展示最前面的几条
  -a           不去将数字展示成N，将字符串展示成'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   后边可以写一个正则，只有符合正则的行会展示
  -h HOSTNAME  慢查询日志以 主机名-slow.log的格式命名，-h可指定读取指定主机名的慢查询日志，默认情况下是*，读取所有的慢查询日志
  -i NAME      MySQL Server的实例名称（如果使用了mysql.server startup脚本的话）
  -l           不将锁定时间从总时间中减去

返回10条最慢的sql查询

mysqldumpslow -s t -t 10 日志文件路径

此时, 字符串都被替换成了’S’, 数字都替换成了’N’

若不想让替换，则在命令上加个-a

mysqldumpslow -s t -t 10 -a 日志文件路径

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

mysqldumpslow -s r -t 10 日志文件路径

得到按照查询时间排序，并且带有left join的10条SQL

mysqldumpslow -s t -t 10 -g "left join" 日志文件路径 

第三方工具（pt-query-digest）

除MySQL自带的mysqldumpslow工具外, 也可以使用pt_query_digest分析慢查询日志文件。pt-query-digest是Percona 公司开发的工具。是Percona Toolkit工具套件中的工具之一。后面会详细介绍。

参考文档

The Slow Query Log