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 是否生效参考命令行配置慢查询