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 |
查询与设定
- 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
- 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
- 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
- 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
- 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
如果您觉得有帮助,欢迎点赞哦 ~ 谢谢!!