MySQL 开启慢日志优化

工欲善其事，必先利其器 -- mysql 开启慢日志优化

慢查询日志

顾名思义就是记录查询比较慢得日志。

测试环境

• mysql-server 5.6
• CentOS 6.5
• VMware Fushion 8 pro

首先，第一部查看是否已经开启慢查询日志：

mysql> show variables like '%slow%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| log_slow_queries    | OFF                             |
| slow_launch_time    | 2                               |
| slow_query_log      | OFF                             |
| slow_query_log_file | /var/run/mysqld/mysqld-slow.log |
+---------------------+---------------------------------+
4 rows in set (0.00 sec)

mysql>

打开慢查询日志。修改 MySQL 的配置文件 my.cnf 一般是在/etc 目录下面，加上下面三行配置后重启 MySQL。

slow_query_log = ON
slow_launch_time = 2
slow_query_log_file = /usr/local/mysql/data/slow_query.log

slow_launch_time 只能精确到秒，如果需要更精确可以使用一些第三方的工具比如后面介绍的 pt-query-digest

mysql> show variables like '%slow%';
+---------------------------+--------------------------------------+
| Variable_name             | Value                                |
+---------------------------+--------------------------------------+
| log_slow_admin_statements | OFF                                  |
| log_slow_slave_statements | OFF                                  |
| slow_launch_time          | 2                                    |
| slow_query_log            | ON                                   |
| slow_query_log_file       | /usr/local/mysql/data/slow_query.log |
+---------------------------+--------------------------------------+
5 rows in set (0.00 sec)

mysql>

Tips: 我这里用的MySQL版本是5.6，不同版本的MySQL开启慢查询的配置是不同的，比如5.6之前的某些版本是long_query_time, long_query_time和log-slow-queries。可以先在终端执行show variables like '%slow%';查看下当前版本具体配置是什么。

也可以在终端中通过设置全局变量来打开慢查询日志：

set @@global.slow_query_log = ON;

保持慢查询日志到表中

MySQL 支持将慢查询日志保存到 mysql.slow_log 这张表中。通过@@global.log_output 可以设置默认为 TABLE，FILE 和 TABLE 只能同时使用一个。

mysql> select @@global.log_output;
+---------------------+
| @@global.log_output |
+---------------------+
| FILE                |
+---------------------+
1 row in set (0.00 sec)

mysql>

set @@global.log_output='TABLE';

mysql> show create table mysql.slow_log;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| slow_log | CREATE TABLE `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

mysql>

我们可以用下列语句用来模仿慢查询；

select sleep(10);

mysql> select * from mysql.slow_Log
    -> ;
ERROR 1146 (42S02): Table 'mysql.slow_Log' doesn't exist
mysql> select *  from mysql.slow_log;
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+------------------+-----------+
| start_time          | user_host                 | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text         | thread_id |
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+------------------+-----------+
| 2016-03-21 23:07:15 | root[root] @ localhost [] | 00:00:10   | 00:00:00  |         1 |             0 |    |              0 |         0 |         0 | select sleep(10) |         2 |
+---------------------+---------------------------+------------+-----------+-----------+---------------+----+----------------+-----------+-----------+------------------+-----------+
1 row in set (0.01 sec)

mysql>

慢查询日志分析

• 可以使用 MySQL 自带的 mysqldumpslow 工具。使用很简单，可以跟-help 来查看具体的用法。

# -s：排序方式。c , t , l , r 表示记录次数、时间、查询时间的多少、返回的记录数排序；
# ac , at , al , ar 表示相应的倒叙； # -t：返回前面多少条的数据；
# -g：包含什么，大小写不敏感的； mysqldumpslow -s r -t 10 /slowquery.log
#slow记录最多的10个语句 mysqldumpslow -s t -t 10 -g "left join" /slowquery.log
#按照时间排序前10中含有"left join"的

• 可以导到 mysql.slow_query 表中，然后通过 sql 语句进行分析。
• 使用第三方工具，下面会有介绍。

pt-query-digest

pt-query-digest 可以从普通 MySQL 日志，慢查询日志以及二进制日志中分析查询，甚至可以从 SHOW PROCESSLIST 和 MySQL 协议的 tcpdump 中进行分析，如果没有指定文件，它从标准输入流（STDIN）中读取数据。 最简单地用法如下：

pt-query-digest slow.logs