第一步
1、mysql开启慢查询
查询MySQL是否开启慢查询:
mysql> show variables like 'slow_query%';
+---------------------+----------------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /home/mysql8data/mysql/3-e184-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)
可以看到mysql是否开启、慢查询日志位置、慢查询记录阈值(秒)
(1)方法一:直接用sql语句修改全局变量,数据库服务不需要重启,但重启后变量重置,慢查询失效
mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.00 sec)
mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'slow_query%';
+---------------------+----------------------------------------+
| Variable_name | Value |
+---------------------+----------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /home/mysql8data/mysql/3-e184-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)
可以看到慢查询时间阈值还是10s,重新登录一下就好
[root@3-e184 ~]# mysql -uroot -h127.0.0.1 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 288
Server version: 8.0.20 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
mysql>
(2)方法二:修改mysql配置文件 /etc/my.cnf,添加参数后重新启动服务生效
show_query_log = 1;
show_query_log_file=/home/mysql8data/mysql/3-e184-slow.log
log_query_time=1;
一般情况下线上的数据库都不会随意重启,开启慢查询日志会损耗mysql性能,所以方法一用几天排查出问题关掉就行,如果要一直开启慢查询日志,可以方法一和方法二结合使用。
2、慢查询日志测试
开启慢查询后,可以直接在日志文件中查看到具体信息
mysql> select sleep(3);
+----------+
| sleep(3) |
+----------+
| 0 |
+----------+
1 row in set (3.00 sec)
mysql> select count(*) FROM T_File;
+----------+
| count(*) |
+----------+
| 1881997 |
+----------+
1 row in set (0.87 sec)
mysql> select * from T_File where GUID like '%fhska%';
Empty set (2.28 sec)
mysql>
可以看到1、3两次查询都超过1s,应该会记录在日志中,查看日志:
[root@3-e184 ~]# cat /home/mysql8data/mysql/3-e184-slow.log
/usr/sbin/mysqld, Version: 8.0.20 (MySQL Community Server - GPL). started with:
Tcp port: 3306 Unix socket: /home/mysql8data/mysql/mysql.sock
Time Id Command Argument
# Time: 2021-01-12T15:54:13.921353Z
# User@Host: root[root] @ localhost [127.0.0.1] Id: 288
# Query_time: 3.000221 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 1
use BeiDouSoft-FileManage;
SET timestamp=1610466850;
select sleep(3);
----------
# Time: 2021-01-12T15:54:18.029566Z
# User@Host: root[root] @ localhost [127.0.0.1] Id: 288
# Query_time: 2.279036 Lock_time: 0.000109 Rows_sent: 0 Rows_examined: 1881997
SET timestamp=1610466855;
select * from T_File where GUID like '%fhska%';
[root@3-e184 ~]#
3、mysqldumpslow使用 常用命令
mysqldumpslow -s r -t 10 /data/mysql/mysql-slow.log //得到返回记录集最多的10个SQL
mysqldumpslow -s c -t 10 /data/mysql/mysql-slow.log //得到访问次数最多的10个SQL
mysqldumpslow -s t -t 10 -g "left join" /data/mysql/mysql-slow.log //得到按照时间排序的前10条里面含有做了连接的查询SQL
mysqldumpslow -s r -t 10 /data/mysql/mysql-slow.log | more //另外建议在使用这些命令时结合|和more使用,否则有可能出现爆屏情况
常用参数
s:是表示按照何种方式排序
c:访问次数
i:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
t:即为返回前面多少条数据
g:后边搭配一个正则匹配模式,大小写不敏感
重新生成log文件
有时候log文件过大需要清空,直接删除log文件,本以为下次再有慢查询会自动生成文件并记录,其实并不会生成。当然重启mysql是可以生成的,但是生产环境重启mysql一定要慎重,不到万不得已不要重启mysql。在删除log文件之后,重新生成log文件需要运行 flush logs 命令。
mysql> flush logs;
Query OK, 0 rows affected