2 开启mysql慢查询-优快云博客

本文链接：https://blog.youkuaiyun.com/luslin1711/article/details/109217897

本文详细介绍了如何在MySQL中开启慢查询，包括设置查询时间阈值、验证设置并记录实际日志。通过实例展示了如何观察和调整log_queries_not_using_indexes参数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 开启慢查询

a. 查询慢查询相关设置

mysql> show variables like 'slow_query%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | OFF                           |   // 慢查询开启状态
| slow_query_log_file | /var/lib/mysql/local-slow.log |   // 慢查询日志存放的位置
+---------------------+-------------------------------+
2 rows in set (0.04 sec)
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |  // 查询超过多少秒才记录
+-----------------+-----------+
1 row in set (0.00 sec)

b. 开启慢查询，设置记录时间

mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.07 sec)
mysql>  set global long_query_time=1;
Query OK, 0 rows affected (0.02 sec)

c. 查看修改后的参数（慢查询时间修改后需要开启新的链接查看）

mysql> show variables like 'slow_query%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | ON                            |
| slow_query_log_file | /var/lib/mysql/local-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

d. 测试是否能够记录慢查询日志

mysql> select sleep(2);
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (2.00 sec)

e.在终端查看

luslin@local:/var/lib$ sudo cat /var/lib/mysql/local-slow.log
/usr/sbin/mysqld, Version: 8.0.22 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 2020-10-22T03:13:08.301400Z
# User@Host: root[root] @ localhost []  Id:    31
# Query_time: 11.000270  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
use mysql;
SET timestamp=1603336377;
select sleep(11);
# Time: 2020-10-22T03:20:46.505885Z
# User@Host: root[root] @ localhost []  Id:    41
# Query_time: 2.000241  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1603336844;
select sleep(2);
# Time: 2020-10-22T03:23:06.579032Z
# User@Host: root[root] @ localhost []  Id:    41
# Query_time: 2.000303  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1603336984;
select sleep(2);

f. 为了方便测试，将全部记录都加入慢查询中

mysql> show variables like'%log_queries_not_using_indexes%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.01 sec)

mysql> set global log_queries_not_using_indexes=on;
Query OK, 0 rows affected (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)