Mysql慢查询日志

原创

wx62e28ac222a20 2022-08-03 09:40:01 ©著作权

文章标签 mysql java linux 数据库 python 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者wx62e28ac222a20的原创作品，请联系作者获取转载授权，否则将追究法律责任

1.查看慢查看日志

mysql> SHOW VARIABLES LIKE 'SLOW_QUERY%';
+---------------------+------------------------+
| Variable_name       | Value                  |
+---------------------+------------------------+
| slow_query_log      | OFF                    |
| slow_query_log_file | /data/mysql57-slow.log |
+---------------------+------------------------+
2 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'LONG_QUERY_TIME';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

参数说明如下：
slow_query_log：慢查询开启状态
slow_query_log_file：慢查询日志存放的位置(一般设置为 MySQL 的数据存放目录)
long_query_time：查询超过多少秒才记录

2.打开慢查询日志

[root@mysql57 ~]# cat /etc/my.cnf | grep -i slow-query-log
slow-query-log = 1
slow-query-log-file= /logs/mysql-slow.log

[root@mysql57 ~]# cat /etc/my.cnf | grep -i long_query_time
long_query_time = 3

[root@mysql57 logs]# cat /etc/my.cnf | grep -i log_queries_not_using_indexes
log_queries_not_using_indexes = ON

[root@mysql57 ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL.. SUCCESS!

查看是否生效

mysql> SHOW VARIABLES LIKE 'SLOW_QUERY%';
+---------------------+----------------------+
| Variable_name       | Value                |
+---------------------+----------------------+
| slow_query_log      | ON                   |
| slow_query_log_file | /logs/mysql-slow.log |
+---------------------+----------------------+
2 rows in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'LONG_QUERY_TIME';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
1 row in set (0.00 sec)

3.查看慢日志

# Time: 2021-11-27T12:51:03.330877Z
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 10.543047  Lock_time: 0.000152 Rows_sent: 1  Rows_examined: 17989991
use testdb;
SET timestamp=1638017463;
select count(*) from t1;

MYSQL