临时开始MySQL慢查询日志

问题

最近在了解一下MySQL课程，其中有用到MySQL的慢查询日志的使用，这里记录一下。

MySQL8

检查慢查询日志配置

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

这里两个变量可知，慢查询是关闭的，以及慢查询日志的存放位置。

开启慢查询日志

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

将slow_query_log设置为1，这样就临时打开慢查询日志了。
再次验证一下：

mysql> show variables  like '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | ON                                   |
| slow_query_log_file | /var/lib/mysql/0a24a50d6986-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)

使用慢查询日志

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

mysql> explain select * from t where a between 10000 and 20000;
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | t     | NULL       | range | a             | a    | 5       | NULL | 10001 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t force index(a) where a between 10000 and 20000;
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | t     | NULL       | range | a             | a    | 5       | NULL | 10001 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

其中，set long_query_time=0;设置慢日志触发查询时间阀值为0，这样任何查询都会写进去。
查看慢查询日志文件：

apt-get upate
apt-get install vim
vim /var/lib/mysql/0a24a50d6986-slow.log

这里使用的docker中安装的mysql8，需要手动安装一下vim。这样就可以临时查看慢查询日志了。

参考：

• 慢查询日志概念
• 5.4.5 The Slow Query Log
• mysql