MYSQL慢查询（一）：开启慢查询

一、简介

开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

二、参数说明

slow_query_log 慢查询开启状态
slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）
long_query_time 查询超过多少秒才记录

三、设置步骤

1.查看慢查询相关参数

mysql> show variables like 'slow_query%';
+---------------------------+----------------------------------+
| Variable_name             | Value                            |
+---------------------------+----------------------------------+
| slow_query_log            | OFF                              |
| slow_query_log_file       | /mysql/data/localhost-slow.log   |
+---------------------------+----------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

2.设置方法
方法一：全局变量设置
将 slow_query_log 全局变量设置为“ON”状态

mysql> set global slow_query_log='ON'; 

设置慢查询日志存放的位置

mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log';

查询超过1秒就记录

mysql> set global long_query_time=1;

3，慢查询相关的 参数log_queries_not_using_indexes

如果log_queries_not_using_indexes为ON的话，当执行一个sql语句的时候，如果一个表没有索引就会把这个信息记录在慢查询文件中

mysql> show variables like "%log_queries_not%"
   -> ;
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)
我们来吧log_queries_not_using_indexes设置为ON的时候来试试吧

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

mysql> show variables like "%log_queries_not%";
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
1 row in set (0.01 sec)

mysql> show index from t;   表t没有索引
Empty set (0.00 sec)

mysql> select * from t where TABLE_NAME="xxxxxxxxxxx";
Empty set (0.11 sec)

这个时候慢日志文件记录下来了这个sql语句

mysql> system  tail /tmp/mysqlslow.log;
# User@Host: root[root] @ localhost []
# Query_time: 1.150157  Lock_time: 0.000205 Rows_sent: 0  Rows_examined: 13568
use test;
SET timestamp=1378205188;
insert into t  select *  from t;
# Time: 130903 18:58:17
# User@Host: root[root] @ localhost []
# Query_time: 0.100749  Lock_time: 0.057377 Rows_sent: 0  Rows_examined: 27136
SET timestamp=1378205897;
select * from t where TABLE_NAME="xxxxxxxxxxx";

方法二：配置文件设置
修改配置文件my.cnf，在[mysqld]下的下方加入

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1

3.重启MySQL服务

service mysqld restart

4.查看设置后的参数

mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /usr/local/mysql/data/slow.log |
+---------------------+--------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

四、测试

1.执行一条慢查询SQL语句

mysql> select sleep(2);

2.查看是否生成慢查询日志

ls /usr/local/mysql/data/slow.log

如果日志存在，MySQL开启慢查询设置成功！

五、慢查询日志工具:mysqldumpslow , pt-query-digest

mysqldumpslow命令

• -s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
• -t, 是top n的意思，即为返回前面多少条的数据；
• -g, 后边可以写一个正则匹配模式，大小写不敏感的

(1)按时间排序，匹配left join语句的前10条

代码:mysqldumpslow -s t -t 10 -g 'left join' /usr/local/mysql/var/slow.log

 pt-query-digest 命令： 

(1)代码:pt-query-digest/usr/local/mysql/var/slow.log

具体请自行google:各工具用法简介（详细内容：https://www.percona.com/doc/percona-toolkit/2.2/index.html）