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

发现慢sql的工具

  • Skywalking
  • VisualVM
  • JavaMelody

1、相关参数及其默认值

参数 作用 默认值
slow_query_log 是否开启慢查询日志 OFF
log_output 日志存储方式。FILE表示将日志存入文件，TABLE表示将日志存入数据库mysql.slow_log表中，log_output=’FILE,TABLE’表示同时配置两种 FILE
long_query_time 慢查询阈值，当查询时间多于设定的阈值时，记录日志，单位秒。开发环境建议开启，开发环境建议关闭 10
log_queries_not_using_indexes 是否将未使用索引的查询也被记录到慢查询日志中，此配置会无视long_query_time，开发环境建议开启，开发环境建议关闭 OFF
log_throttle_queries_not_usig_indexes 和log_queries_not_using_indexes配合使用，如果log_queries_not_using_indexes打开则该参数会限制每分钟写入的，未使用索引的sql数量 0
min_examined_row_limit 扫描行数达到这么多才记到慢查询日志 0
log_slow_admin_statement 是否要记录管理语句，管理语句包括ALTER TABLE,ANALYZE TABLE,CHECK TABLE,CREATE TABLE,CREATE INDEX,DROP INDEX,OPTIMIZE TABLE, REPAIR TABLE OFF
slow_query_log_file MySQL数据库慢查询日志存储路径
log_slow_slave_statements 该参数在从库上设置，决定是否记录在复制过程中超过long_query_time的sql。如果binlog的格式是row，则该参数无效 OFF
log_slow_rxtra 当log_output为file时，是否要记录额外信息（mysql 8.0.14开始提供），对log_output=TABLE的结果无影响 OFF

2、参数使用方式

show global variables like '%log%'

（1）方式1：修改配置文件my.cnf

在mysqlId段落中加上如上参数，然后重启mysql

  1. [mysqld]
  2. # ...
  3. log_output = 'FILE,TABLE'

（2）方式2：设置全局变量

无需重启即可生效，但是重启后就会失效

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

3、分析慢查询日志

当log_output = ‘TABLE’时可以通过select * frommysql.slow_log 按条件进行各种查询统计分析。

当log_output = ‘FILE’时，通过show global variables like '%slow_query_log_file%',查询慢日志文件路径

use employees;
SET timestamp=1613223390;
select * from employees;
# Time: 2021-02-13T13:36:30.436841Z
# User@Host: root[root] @ localhost [::1]  Id:    13
# Query_time: 0.000331  Lock_time: 0.000104 Rows_sent: 4  Rows_examined: 79
SET timestamp=1613223390;

在实际生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow，mysqldumpslow —help

Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]  -- 后跟参数以及log文件的绝对地址;

  -s            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

常见用法：

mysqldumpslow -s c -t 10 /var/run/mysqld/mysqld-slow.log # 取出使用最多的10条慢查询

mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log # 取出查询时间最慢的3条慢查询

mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log # 得到按照时间排序的前10条里面含有左连接的查询语句

 mysqldumpslow -s r -t 10 -g 'left join' /var/run/mysqld/mysqld-slow.log # 按照扫描行数最多的