MySQL8慢查询

开启慢查询日志

默认情况，MySQL的慢查询日志是关闭的

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

开启MySQL的慢查询日志功能

mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.09 sec)

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

设置慢查询超时时间

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

long_query_time 默认时间为 10s。修改慢查询超时时间

# 执行完后需要将当前窗口关闭重新连接才会生效 如果只想让本窗口有效果的话，不用加global关键字
set global long_query_time=2;

delimiter ;;
create procedure idata2()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i,i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata2();

测试慢查询

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

查看慢查询

cat /var/lib/mysql/VM-20-11-centos-slow.log
# Time: 2022-04-23T04:32:54.796190Z
# User@Host: root[root] @ localhost []  Id: 10864
# Query_time: 3.000236  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1650688371;
select sleep(3);

慢日志分析工具

mysqldumpslow

mysqldumpslow经常使用的参数：

-s 按照那种方式排序
    c：访问计数
    l：锁定时间
    r:返回记录
    a:查询时间
    al：平均锁定时间
    ar：平均访问记录数
    at：平均查询时间
-t 是top n的意思，返回多少条数据。
-g 可以跟上正则匹配模式，大小写不敏感。

例如:

mysqldumpslow -t 3 -s t -g 'left join' /var/lib/mysql/VM-20-11-centos-slow.log

mysqldumpslow -t 3 -s t  /var/lib/mysql/VM-20-11-centos-slow.log

执行结果分析，例如

Count: 4  Time=3.75s (15s)  Lock=0.00s (0s)  Rows=1.0 (4), root[root]@localhost
  select sleep(N)

Count 表示该类型的语句执行次数

**Time ** 表示该类型的语句执行的平均时间（总计时间）

Lock 锁时间0s

Rows 单次返回的结果数是1条记录，4次总共返回4条记录

MySQL重启失效

[root@VM-20-11-centos ~]# systemctl restart mysqld
[root@VM-20-11-centos ~]# mysql -uroot -proot123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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 'slow_query%';
+---------------------+-----------------------------------------+
| Variable_name       | Value                                   |
+---------------------+-----------------------------------------+
| slow_query_log      | OFF                                     |
| slow_query_log_file | /var/lib/mysql/VM-20-11-centos-slow.log |
+---------------------+-----------------------------------------+
2 rows in set (0.01 sec)

通过变量方式启动MySQL相关功能，重启后恢复到默认配置。如果需要永久有效，则通过修改 MySQL 的配置文件 my.cnf。

# 打开配置文件
vim /etc/my.cnf

在[mysqld]后添加

slow-query-log = on
long_query_time = 2

然后重启服务

[root@VM-20-11-centos ~]# systemctl restart mysqld
[root@VM-20-11-centos ~]# mysql -uroot -proot123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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 'slow_query%';
+---------------------+-----------------------------------------+
| Variable_name       | Value                                   |
+---------------------+-----------------------------------------+
| slow_query_log      | ON                                      |
| slow_query_log_file | /var/lib/mysql/VM-20-11-centos-slow.log |
+---------------------+-----------------------------------------+
2 rows in set (0.00 sec)