2.6.mysql慢查日志设置及测试

检查慢查日志是否开启

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

检查慢日志路径

mysql> show variables like '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/8f31619d9800-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.01 sec)

开启慢日志:

mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.04 sec)

慢日志判断标准（默认查询时间大于10s的sql语句):

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

慢日志测试

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

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

检查慢日志记录情况

root@8f31619d9800:/var/lib/mysql# cat 8f31619d9800-slow.log 
/usr/sbin/mysqld, Version: 8.0.18 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument

root@8f31619d9800:/var/lib/mysql# tail -f 8f31619d9800-slow.log 
/usr/sbin/mysqld, Version: 8.0.18 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 2021-02-28T17:19:06.533244Z
# User@Host: root[root] @ localhost []  Id:     8
# Query_time: 11.000830  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1614532735;
select sleep(11);

更改慢日志的判断时间

mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

使用exit退出mysql，重新登录mysql。使用如下命令查看变更结果

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

创建测试数据

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> use test;
Database changed

mysql> create table t1(id int, name varchar(25));
Query OK, 0 rows affected (0.04 sec)

生成100万数据

DROP PROCEDURE IF EXISTS pro_t1;
delimiter $$
create procedure pro_t1()
begin
declare i int;
set i=0;
while i<100000 do
    insert into t1(id,name) values(i, CONCAT('smartan',i));
set i=i+1;
end while;
end
$$
delimiter ;
call pro_t1();