mysql没记录slow日志_MySQL中的慢查询日志(Slow Query Log)

MySQL中的慢查询日志(Slow Query Log)

1. 以什么形式来记录？

log_output系统变量来决定的，可选值 TABLE, FILE, 或者 NONE，默认值是FILE，可以同时选择TABLE和FILE，中间用逗号隔开。

2. 如何开启？

设置 slow_query_log 系统变量，设置为ON,OFF

3. 具体写到哪里？

如果是写到文件里，由slow_query_log_file 指定，如果要看默认值，可以使用show variables like 'slow_query_log_file'。如果写到表里，则写到mysql.slow_log

4. 何时开启？

可以在启动MySQL server的时候开启，指定--log_output，--slow_query_log和--slow_query_log_file选项；也可以在运行时开启，设置环境变量log_output，slow_query_log和slow_query_log_file

5. 记录什么内容？

The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined.

6. 多慢算慢？

非administrative 语句，执行时间比long_query_time长的，并且返回的行数不少于min_examined_row_limit的，都算慢

测试：

下面的测试中，设置long_query_time为4s，即如果SQL执行时间长于4s钟，就记录到慢日志中：

mysql>

set global slow_query_log=1;

mysql>

set global long_query_time=4;

mysql>

show variables like 'slow_query_log_file';

+---------------------+------------------------------+

| Variable_name | Value |

+---------------------+------------------------------+

| slow_query_log_file | /var/lib/mysql/db2a-slow.log |

+---------------------+------------------------------+

1 row in set (0.00 sec)

mysql>

show variables like 'long_query_time';

+-----------------+----------+

| Variable_name | Value |

+-----------------+----------+

| long_query_time | 4.000000 |

+-----------------+----------+

1 row in set (0.01 sec)

mysql>

show variables like 'min_examined_row_limit';

+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| min_examined_row_limit | 0 |

+------------------------+-------+

1 row in set (0.00 sec)

mysql>

select * from t1 where id < 1000000 order by word desc limit 1;

+--------+--------------------------------------+----------------------------+

| id | word | curtime |

+--------+--------------------------------------+----------------------------+

| 484424 | zZzzAs9yRCb7Jwcdskb95dZR2GSVO85Hbg== | 2017-09-13 22:08:41.277430 |

+--------+--------------------------------------+----------------------------+

1 row in set (

4.80 sec)

mysql>

select * from test1 limit 1;

+------+

| year |

+------+

| 2010 |

+------+

1 row in set (

0.05 sec)

mysql>

create table t2 like t1;

Query OK, 0 rows affected (0.27 sec)

mysql>

insert into t2 select * from t1 where id < 100000;

Query OK, 99999 rows affected (

10.40 sec)

Records: 99999 Duplicates: 0 Warnings: 0

mysql>

select * from t2 where id < 1000000 order by word desc limit 1;

+-------+--------------------------------------+----------------------------+

| id | word | curtime |

+-------+--------------------------------------+----------------------------+

| 61356 | zZzqYVLg7cleB2ku281C2TfwAUc51P52mg== | 2017-09-13 20:59:03.136307 |

+-------+--------------------------------------+----------------------------+

1 row in set (

0.06 sec)

mysql>

set global slow_query_log=0;

下面来看下慢日志的内容： root@db2a:~#

cat /var/lib/mysql/db2a-slow.log

mysqld, Version: 5.7.18-log (MySQL Community Server (GPL)). started with:

Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock

Time Id Command Argument

# Time: 2017-09-18T13:04:29.577414Z

# User@Host: root[root] @ localhost [] Id: 16

# Query_time: 4.795787 Lock_time: 0.000232 Rows_sent: 1 Rows_examined: 10000001

use sample;

SET timestamp=1505739869;

select * from t1 where id < 1000000 order by word desc limit 1;

# Time: 2017-09-18T13:05:15.201835Z

# User@Host: root[root] @ localhost [] Id: 16

# Query_time: 10.399761 Lock_time: 0.000464 Rows_sent: 0 Rows_examined: 10000000

SET timestamp=1505739915;

insert into t2 select * from t1 where id < 100000;

mysqld, Version: 5.7.18-log (MySQL Community Server (GPL)). started with:

Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock

Time Id Command Argument

可以看到，有两条符合结果的记录，而且详细记录了Query_time、Lock_time、Rows_examined。

参考链接：

https://dev.mysql.com/doc/refman/5.7/en/log-destinations.html

https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html