> For the complete documentation index, see [llms.txt](https://moluo.gitbook.io/notes/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://moluo.gitbook.io/notes/bian-cheng-xue-xi/java/xing-neng-tiao-you/2.6.mysql-man-cha-ri-zhi-she-zhi-ji-ce-shi.md).

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

## 检查慢查日志是否开启

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

## 检查慢日志路径

```bash
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)
```

## 开启慢日志:

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

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

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

## 慢日志测试

```bash
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)
```

## 检查慢日志记录情况

```bash
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);
```

## 更改慢日志的判断时间

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

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

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

创建测试数据

```bash
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万数据

```bash
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();
```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://moluo.gitbook.io/notes/bian-cheng-xue-xi/java/xing-neng-tiao-you/2.6.mysql-man-cha-ri-zhi-she-zhi-ji-ce-shi.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.