MySQL开启慢查询

MySQL慢查询

开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能

参数说明

• slow_query_log：慢查询开启状态
• slow_query_log_file：慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）
• long_query_time：查询超过多少秒才记录

命令行配置慢查询

# 1. 查看慢查询相关参数
MariaDB [(none)]> show variables like 'slow_query%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /opt/bitnami/mariadb/logs/mysqld.log |
+---------------------+--------------------------------------+
2 rows in set (0.001 sec)

MariaDB [(none)]> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.001 sec)

# 2. 全局变量设置,开启慢查询，超过1s就为慢查询
MariaDB [(none)]> set global slow_query_log_file='/bitnami/mariadb/data/mysql_slow.log';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> set global slow_query_log='ON';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> set global long_query_time=1;
Query OK, 0 rows affected (0.000 sec)


# 3. 执行一条慢查询SQL语句
MariaDB [(none)]> select sleep(10);
+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.000 sec)

# 4. 查看是否生成慢查询日志
# 4.1 查询慢查询日志文件中的总记录条数
MariaDB [(none)]> show global status like '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 1508  |
+---------------+-------+
1 row in set (0.001 sec)

# 4.2 查看慢日志内容
Tcp port: 3306  Unix socket: /opt/bitnami/mariadb/tmp/mysql.sock
Time                Id Command  Argument
/opt/bitnami/mariadb/sbin/mysqld, Version: 10.6.10-MariaDB-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /opt/bitnami/mariadb/tmp/mysql.sock
Time                Id Command  Argument
# Time: 240318  0:12:10
# User@Host: root[root] @ localhost []		
# Thread_id: 743443  Schema:   QC_hit: No
# Query_time: 10.000166  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0
# Rows_affected: 0  Bytes_sent: 66
SET timestamp=1710691930;
select sleep(10);

配置文件修改

# 1. 修改配置文件my.cnf，在[mysqld]下的下方加入
[mysqld]
slow_query_log = ON
slow_query_log_file = /bitnami/mariadb/data/mysql_slow.log
long_query_time = 1


# 2. 重启MySQL服务

# 3 是否生效参考命令行配置慢查询