Mysql慢查询日志

概念

• Mysql的慢查询是一种日志, 主要用于记录 Mysql中响应时间超过设定的阀值的语句.

常用参数

类型	说明	默认值
slow_query_log	是否开启慢查询日志	OFF
slow_query_log_file	配置慢查询日志存储路径(版本5.6及以上版本)	host_name-slow.log
long_query_time	慢查询阈值,当查询时间大于设定的阈值时,记录日志	10.000000(秒)
log_queries_not_using_indexes	未使用索引的查询也被记录到慢查询日志中	OFF
log_output	日志存储方式	FILE

查询与设定

1. slow_query_log

• 开启: 1/ON, 关闭: 0/OFF

mysql> SHOW VARIABLES LIKE 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set

mysql> SET GLOBAL slow_query_log=1;
Query OK, 0 rows affected

2. slow_query_log_file

mysql> SHOW VARIABLES LIKE 'slow_query_log_file';
+---------------------+--------------------------+
| Variable_name       | Value                    |
+---------------------+--------------------------+
| slow_query_log_file | DESKTOP-HCBV7NE-slow.log |
+---------------------+--------------------------+
1 row in set

mysql> SET GLOBAL slow_query_log_file="/tmp/mysql_slow.log";
Query OK, 0 rows affected

3. long_query_time

mysql> SHOW VARIABLES LIKE 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set

mysql> SET GLOBAL long_query_time=5.555555;
Query OK, 0 rows affected

4. log_queries_not_using_indexes

mysql> SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set

mysql> SET GLOBAL log_queries_not_using_indexes=ON;
Query OK, 0 rows affected

5. log_output

• FILE表示将日志存入到文件
• TABLE表示将日志存入到 mysql.slow_log表中
• FILE,TABLE同时支持两种日志存储

mysql> SHOW VARIABLES LIKE 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set

mysql> SET GLOBAL log_output='FILE,TABLE';
Query OK, 0 rows affected

注: 通过 SET GLOBAL设定的值,查询时需重新连接会话再 SHOW VARIABLES LIKE才会显示更改后的状态,或使用 SHOW GLOBAL VARIABLES LIKE查询的话无需重新连接
注: 通过 SET GLOBAL设定的值,一旦数据库重启将会失效.如果需要永久生效,就必须配置到 my.cnf

查询慢查询记录数

mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 111   |
+---------------+-------+
1 row in set

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           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, ae, c, l, r, e, t), 'at' is default
                al: average lock time 平均锁定时间
                ar: average rows sent 平均访问记录数
                at: average query time 平均查询时间
                aa: average rows affected
                 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

• 得到返回记录最多的20个查询语句
  mysqldumpslow -s r -t 20 sqlslow.log
• 得到访问次数最多的10个查询语句
  mysqldumpslow -s c -t 10 sqlslow.log
• 得到按照时间排序的前10条里面含有左连接的查询语句
  mysqldumpslow -s t -t 10 -g “left join” sqlslow.log
• 建议结合 | more使用, 避免出现刷屏的情况
  mysqldumpslow -s r -t 10 sqlslow.log | more

如果您觉得有帮助，欢迎点赞哦 ~ 谢谢!!