MySQL慢查询日志

笔记内容：MySQL慢查询日志

笔记日期：2017-12-10

<br>

• MySQL日志文件系统的组成
• 通用查询日志
• 慢查询日志

和大多数关系型数据库一样，日志文件是MySQL数据库的重要组成部分。MySQL有几种不同的日志文件，通常包括错误日志文件，二进制日志，通用日志，慢查询日志，等等。这些日志可以帮助我们定位mysqld内部发生的事件，数据库性能故障，记录数据的变更历史，用户恢复数据库等等。

<br>

MySQL日志文件系统的组成

1.错误日志：记录启动、运行或停止mysqld时出现的问题。

2.通用查询日志：记录建立的客户端连接和执行的语句。

3.更新日志：记录更改数据的语句。该日志在MySQL 5.1中已不再使用。

4.二进制日志：记录所有更改数据的语句。还用于主从复制。

5.慢查询日志：记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。

6.Innodb日志：innodb redo log

7.中继日志：从库从主库获取到的要更新的数据的日志。

默认情况下，所有日志创建于mysqld数据目录中。 可以通过刷新日志，来强制mysqld来关闭和重新打开日志文件（或者在某些情况下切换到一个新的日志）。当你执行一个FLUSH LOGS语句或执行mysqladmin flush-logs或mysqladmin refresh时，则日志被老化。对于存在MySQL复制的情形下，从复制服务器将维护更多日志文件，被称为接替日志。

<br>

通用查询日志

在学习通用日志查询时，需要知道几个数据库中的常用命令：

1.show variables like '%version%';

这个命令，可以显示当前数据库中与版本号相关的信息。示例：

mysql> show variables like '%version%';  
+-------------------------+----------------+
| Variable_name           | Value          |
+-------------------------+----------------+
| innodb_version          | 5.7.14         |
| protocol_version        | 10             |
| slave_type_conversions  |                |
| version                 | 10.2.6-MariaDB |
| version_comment         | MariaDB Server |
| version_compile_machine | x86_64         |
| version_compile_os      | Linux          |
| version_malloc_library  | system         |
| version_ssl_library     | YaSSL 2.4.2    |
| wsrep_patch_version     | wsrep_25.19    |
+-------------------------+----------------+
10 rows in set (0.01 sec)

mysql>

以下这个命令是用于查看当前的通用日志查询是否开启，如果general_log的值为ON则为开启，为OFF则为关闭（默认情况下是关闭的）。

2.show variables like '%general%';

示例：

mysql> show variables like '%general%';
+------------------+------------+
| Variable_name    | Value      |
+------------------+------------+
| general_log      | OFF        |
| general_log_file | server.log |
+------------------+------------+
2 rows in set (0.00 sec)

mysql>

以下这个命令是用于查看当前通用查询日志输出的格式，log_output的值可以是FILE（存储在数数据库的数据文件中的hostname.log），也可以是TABLE（存储在数据库中的mysql.general_log）。

3.show variables like '%log_output%';

示例：

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

mysql> 

<br>

如何开启MySQL通用查询日志，以及使用何种方式去记录这些通用查询日志？

• 开启通用日志查询： set global general_log=on;

• 关闭通用日志查询： set global general_log=off;

• 设置将通用日志记录到数据库表格中： set globallog_output='TABLE';

• 设置将通用日志记录到本地文件中： set globallog_output='FILE';

• 设置将通用日志记录到数据库表和本地文件中：set global log_output='FILE,TABLE';

示例：

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

mysql> set global log_output='FILE,TABLE';
Query OK, 0 rows affected (0.03 sec)

mysql> select * from mysql.general_log;
+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
| event_time                 | user_host                 | thread_id | server_id | command_type | argument                        |
+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
| 2017-12-10 17:47:35.177238 | root[root] @ localhost [] |       189 |         1 | Query        | select * from mysql.general_log |
| 2017-12-10 17:47:52.608628 | root[root] @ localhost [] |       189 |         1 | Query        | select * from mysql.general_log |
| 2017-12-10 17:47:55.138903 | root[root] @ localhost [] |       189 |         1 | Query        | select * from mysql.general_log |
+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
3 rows in set (0.00 sec)

mysql>

记录到mysql.general_log表中的数据如下：

默认情况下记录在本地文件中的通用查询日志文件名称的前缀为主机名，后缀为.log，可以使用find命令寻找，示例：

[root@server ~]# find / -name "server.log"
/data/mariadb/server.log
[root@server ~]# tail /data/mariadb/server.log
Time                 Id Command    Argument
171210 17:46:39   189 Query set global log_output='FILE,TABLE'
171210 17:47:35   189 Query select * from mysql.general_log
171210 17:47:52   189 Query select * from mysql.general_log
171210 17:47:55   189 Query select * from mysql.general_log
171210 17:50:57   189 Quit  
171210 17:52:01   190 Connect   root@localhost as anonymous on 
          190 Query select @@version_comment limit 1
171210 17:52:09   190 Query select * from mysql.general_log
171210 17:52:59   190 Quit  
[root@server ~]#

注意：上述命令只是临时生效，当MySQL重启后则会失效，如果要永久生效，需要配置my.cnf文件

my.cnf文件需要配置的内容如下：

general_log=1   #为1表示开启通用日志查询，值为0表示关闭通用日志查询
log_output=FILE,TABLE   #设置通用日志的输出格式为文件和表

如果没有开启通用日志查询的话，general_log表是空的：

mysql> select * from mysql.general_log;
Empty set (0.00 sec)

mysql>

<br>

慢查询日志

  MySQL的慢查询日志是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL语句，就会被记录到慢查询日志中（日志可以写入文件或者数据库表，如果对性能要求高的话，建议写文件）。所以慢查询日志就是记录mysql服务器中影响数据库性能的相关SQL语句的，通过对这些特殊的SQL语句分析，改进以达到提高数据库性能的目的。默认情况下，MySQL数据库是不开启慢查询日志的，long_query_time的默认值为10（即10秒，通常设置为1秒），即运行10秒以上的语句是慢查询语句。
  一般情况下，慢查询发生在数据库比较大的表格中（比如：一个表的数据量有几百万），且查询条件的字段没有建立索引，此时，要匹配查询条件的字段会进行全表扫描，查询耗时超过long_query_time所定义的阈值（预设值），则为慢查询语句，这些慢查询语句就会记录到慢查询日志中。

使用以下命令可以查看当前慢查询日志的开启情况：

show variables like '%quer%';

示例：

mysql> show variables like '%quer%';
+---------------------------------+-----------------+
| Variable_name                   | Value           |
+---------------------------------+-----------------+
| expensive_subquery_limit        | 100             |
| ft_query_expansion_limit        | 20              |
| have_query_cache                | YES             |
| log_queries_not_using_indexes   | OFF             |
| long_query_time                 | 10.000000       |
| query_alloc_block_size          | 16384           |
| query_cache_limit               | 1048576         |
| query_cache_min_res_unit        | 4096            |
| query_cache_size                | 1048576         |
| query_cache_strip_comments      | OFF             |
| query_cache_type                | OFF             |
| query_cache_wlock_invalidate    | OFF             |
| query_prealloc_size             | 24576           |
| slow_query_log                  | OFF             |
| slow_query_log_file             | server-slow.log |
| wsrep_sst_donor_rejects_queries | OFF             |
+---------------------------------+-----------------+
16 rows in set (0.00 sec)

mysql> 

需要关注以下的几个参数：
  1.slow_query_log： 的值为ON为开启慢查询日志，OFF则为关闭慢查询日志。

  2.slow_query_log_file： 的值是记录的慢查询日志到文件中（注意：默认名为主机名.log，慢查询日志是否写入指定文件中，需要指定慢查询的输出日志格式为文件，相关命令为：show variables like ‘%log_output%’；去查看输出的格式）。

  3.long_query_time： 指定了慢查询的阈值，即如果执行语句的时间超过该阈值则为慢查询语句，默认值为10秒。

  4.log_queries_not_using_indexes： 如果值设置为ON，则会记录所有没有利用索引的查询（注意：如果只是将log_queries_not_using_indexes设置为ON，而将slow_query_log设置为OFF，此时该设置也不会生效，即该设置生效的前提是slow_query_log的值设置为ON），一般在性能调优的时候会暂时开启。

  5.min_examined_row_limit： 查询检查返回少于该参数指定行的SQL不被记录到慢查询日志

  6.log_slow_queries： 指定是否开启慢查询日志(该参数要被slow_query_log取代，做兼容性保留)

<br>

和通用查询日志一样，慢查询日志也是使用 show variables like '%log_output%'; 语句来查看日志的记录方式：

mysql> show variables like '%log_output%';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| log_output    | FILE,TABLE |
+---------------+------------+
1 row in set (0.00 sec)

mysql>

<br>

设置记录日志的方式也是和之前的一样：

• 设置将通用日志记录到数据库表格中： set globallog_output='TABLE';

• 设置将通用日志记录到本地文件中： set globallog_output='FILE';

• 设置将通用日志记录到数据库表和本地文件中：set global log_output='FILE,TABLE';

<br>

开启慢查询日志：

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

mysql> set global long_query_time=1;  # 将阀值设置为1秒
Query OK, 0 rows affected (0.00 sec)

mysql> set session long_query_time=1; # 将session级别的阀值也设置为1秒
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%long_query_time%';  # 查看阀值，默认为10秒
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> show variables like '%slow_query_log%';  # 查看慢查询日志状态，ON为开启状态，默认为OFF
+---------------------+-----------------+
| Variable_name       | Value           |
+---------------------+-----------------+
| slow_query_log      | ON              |
| slow_query_log_file | server-slow.log |
+---------------------+-----------------+
2 rows in set (0.01 sec)

mysql> show global status like '%slow%';  # 查看慢查询的记录数量
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 0     |
+---------------------+-------+
2 rows in set (0.06 sec)

mysql> show variables like 'log_queries_not_using_indexes';   # 查看log_queries_not_using_indexes状态
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql> 

如果出现修改之后依旧显示为默认值的情况，重新登录mysql就好了，或者在show后面加上global关键字也可以。

<br>

关于慢查询日志的表中的数据个文本中的数据格式分析：

慢查询的日志记录myql.slow_log表中，格式如下：

查询语句：

select * from mysql.slow_log;

此时再查看一下慢查询的记录数量：

mysql> show global status like '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 374   |
+---------------------+-------+
2 rows in set (0.00 sec)

mysql>

慢查询日志和通用查询日志都是使用的同一个本地文件：

[root@server ~]# find / -name 'server.log'
/data/mariadb/server.log
[root@server ~]# tail /data/mariadb/server.log
          197 Field List    INNODB_SYS_INDEXES 
          197 Field List    INNODB_SYS_VIRTUAL 
          197 Field List    INNODB_MUTEXES 
          197 Field List    INNODB_SYS_SEMAPHORE_WAITS 
171210 21:43:19   197 Query show tables
171210 21:43:36   197 Query select * from ALL_PLUGINS
171210 21:44:09   197 Query select * from myql.slow_log
171210 21:44:15   197 Query select * from mysql.slow_log
171210 21:44:23   197 Query select * from mysql.slow_log
171210 22:00:12   197 Quit  
[root@server ~]#

可以看到，不管是表还是文件，都具体记录了：是哪条sql语句导致慢查询（sql_text），该慢查询语句的查询时间（query_time），锁表时间（Lock_time），以及扫描过的行数（rows_examined）等信息。

<br>

如何手动产生慢查询语句

实际在学习过程中，如何得知设置的慢查询是有效的？很简单，我们可以手动产生一条慢查询语句，比如，如果我们的慢查询log_query_time的值设置为1，则执行以下这条语句就可以了：

selectsleep(1);

该条语句即是慢查询语句，之后，便可以在相应的日志输出文件或表中去查看是否有该条语句。