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）