Written by honors

慢查询日志与分析

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

相关参数配置

参数 作用 默认值
log_output 日志输出到哪儿，默认FILE，表示文件；设置成TABLE，则将日志记录到mysql.slow_log中。也可设置多种格式，比如 FILE 、TABLE FILE
long_query_time 执行时间超过这么久才记录到慢查询日志，单位秒，可使用小数表示小于秒的时间 10
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，则该参数无效 OFF
log_slow_extra 当log_output=FILE时，是否要记录额外信息（MySQL 8.0.14开始提供），对log_output=TABLE的结果无影响。 OFF

使用方式

方式一

修改配置文件my.cnf 在[mysqld]段落加上参数即可，然后重启MySQL

[mysqld]
# ...
log_output = 'FILE,TABLE';
slow_query_log = ON
long_query_time = 0.001
SET GLOBAL log_queries_not_using_indexes = 'ON';

关于long_query_time 设置后有一个问题
file
设置完成后，没有生效，需要断开连接后重新连接才能生效
file

方式二

通过全局变量设置，这种方式无需重启即可生效，但是重启后需要重新配置

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

分析慢查询日志

运行一条查询语句，因为设置的慢sql查询时间是0.001秒，所以执行的语句都会被触发

SELECT * FROM employees;

慢查询日志表

当log_output = TABLE时，可直接用如下语句分析：

select * from mysql.slow_log

file

start_time: 执行sql语句的时间
user_host: 执行sql的主机
query_time: 执行用时
lock_time: sql占有锁的时间
rows_sent:这条sql返回了多少条数据到客户端
rows_examined:这条sql语句扫描了多少行
db: 执行sql的目标数据库
last_insert_id: 最后一条自增的ID
insert_id: 插入的id
server_id: 服务器id ,配置主从数据库时的设置
sql_text: 当前执行的sql
thread_id:执行sql的线程id

慢查询日志文件

当log_output = FILE时,首先查看日志文件存放的地址

show VARIABLES LIKE "%slow_query_log_file%";

然后打开存放的文件，可以看到文件类型的日志，是每5行表示一个sql,周而复始
file
人工分析比较麻烦。所以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条执行最慢的语句，需要执行的操作是

 mysqldumpslow -s t -t 10 VM-0-4-centos-slow.log 

# 得到按照查询时间排序，并且带有left join的10条SQL
mysqldumpslow -s t -t 10 -g 'left join' /var/lib/mysql/ VM-0-4-centos-slow.log

file

发表回复