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;

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

[mysqld]slow_query_log = ONslow_query_log_file = /usr/local/mysql/data/slow.loglong_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开启慢查询设置成功！

__________________________________________________________________________________

MySQL有一个功能就是可以log下来运行的比较慢的sql语句，默认是没有这个log的，为了开启这个功能，要修改my.cnf或者在MySQL启动的时候加入一些参数。

开启mysql的慢查询

方法1，用命令开启慢查询

查看复制打印?

1. mysql> show variables like "%long%";         //查看一下默认为慢查询的时间10秒  
2. +-----------------+-----------+  
3. | Variable_name   | Value     |  
4. +-----------------+-----------+  
5. | long_query_time | 10.000000 |  
6. +-----------------+-----------+  
7. 1 row in set (0.00 sec)  
8.   
9. mysql> set global long_query_time=2;          //设置成2秒，加上global,下次进mysql已然生效  
10. Query OK, 0 rows affected (0.00 sec)  
11.   
12. mysql> show variables like "%slow%";          //查看一下慢查询是不是已经开启  
13. +---------------------+---------------------------------+  
14. | Variable_name       | Value                           |  
15. +---------------------+---------------------------------+  
16. | log_slow_queries    | OFF                             |  
17. | slow_launch_time    | 2                               |  
18. | slow_query_log      | OFF                             |  
19. | slow_query_log_file | /usr/local/mysql/mysql-slow.log |  
20. +---------------------+---------------------------------+  
21. 4 rows in set (0.00 sec)  
22.   
23. mysql> set slow_query_log='ON';                        //加上global，不然会报错的。  
24. ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL  
25. mysql> set global slow_query_log='ON';            //启用慢查询  
26. Query OK, 0 rows affected (0.28 sec)  
27.   
28. mysql> show variables like "%slow%";              //查看是否已经开启  
29. +---------------------+---------------------------------+  
30. | Variable_name       | Value                           |  
31. +---------------------+---------------------------------+  
32. | log_slow_queries    | ON                              |  
33. | slow_launch_time    | 2                               |  
34. | slow_query_log      | ON                              |  
35. | slow_query_log_file | /usr/local/mysql/mysql-slow.log |  
36. +---------------------+---------------------------------+  
37. 4 rows in set (0.00 sec)  

      set  global general_log='ON';  //开启执行sql日志

      show variables like "%log%";  //查看日志是否开启

方法2,修改mysql的配置文件my.cnf   general_log

在[mysqld]里面加上以下内容

1. long_query_time = 2  
2. log-slow-queries = /usr/local/mysql/mysql-slow.log  

5.6以后版本，开启慢查询的方法变了。
开启的语句应该这样写（修改my.cnf）：
slow-query-log=1slow-query-log-file=/usr/local/mysql/data/slow.log
保存、重启，问题解决。

重起一下
/usr/local/mysql/libexec/mysqld restart

或   service mysqld restart

三，分析工具

分析工具干什么事的呢，其实就是把mysql-slow.log里面记录下来的数据，分析一下显示出来。其实自己写一个shell脚本也是可以把要的信息取出来的。我们来看一下mysql-slow.log里面到底是什么东西

查看复制打印?

1. [root@BlackGhost mysql]# cat mysql-slow.log     //查看命令  
2. /usr/local/mysql/libexec/mysqld, Version: 5.1.26-rc-log (Source distribution). started with:  
3. Tcp port: 3306  Unix socket: /tmp/mysql.sock  
4. Time                 Id Command    Argument  
5. # Time: 100814 13:28:30  
6. # User@Host: root[root] @ localhost []  
7. # Query_time: 10.096500  Lock_time: 0.045791 Rows_sent: 1  Rows_examined: 2374192  
8. SET timestamp=1281763710;  
9. select count(distinct ad_code) as x from ad_visit_history where ad_code in (select ad_code from ad_list where media_id=15);  
10. # Time: 100814 13:37:02  
11. # User@Host: root[root] @ localhost []  
12. # Query_time: 10.394134  Lock_time: 0.000091 Rows_sent: 1  Rows_examined: 2374192  
13. SET timestamp=1281764222;  
14. select count(distinct ad_code) as x from ad_visit_history where ad_code in (select ad_code from ad_list where media_id=15);  
15. # Time: 100814 13:37:16  
16. # User@Host: root[root] @ localhost []  
17. # Query_time: 4.608920  Lock_time: 0.000078 Rows_sent: 1  Rows_examined: 1260544  
18. SET timestamp=1281764236;  
19. select count(*) as cou  from ad_visit_history where ad_code in (select ad_code from ad_list where id=41) order by id desc;  

看到了，就是记录一下sql语句的执行情况，包括执行时间，锁定时间等，所以要不要分析工具看个人情况，分析工具很多，在这儿只说一下mysql自带的慢查询分析工具mysqldumpslow的使用方法。

查看复制打印?

1. [root@BlackGhost bin]# mysqldumpslow -h  
2. Option h requires an argument  
3. ERROR: bad option  
4.   
5. Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]  
6.   
7. Parse and summarize the MySQL slow query log. Options are  
8.   
9.  --verbose    verbose  
10.  --debug      debug  
11.  --help       write this text to standard output  
12.   
13.  -v           verbose  
14.  -d           debug          //查错  
15.  -s ORDER     what to sort by (t, at, l, al, r, ar etc), 'at' is default     //排序方式query次数，时间，lock的时间和返回的记录数来排序  
16.  -r           reverse the sort order (largest last instead of first)       //倒排序  
17.  -t NUM       just show the top n queries                                       //显示前N多个  
18.  -a           don't abstract all numbers to N and strings to 'S' 
19.  -n NUM       abstract numbers with at least n digits within names   //抽象的数字，至 少有n位内的名称 
20.  -g PATTERN   grep: only consider stmts that include this string      //配置模式 
21.  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),     //mysql所以机器名或者IP 
22.  default is '*', i.e. match all 
23.  -i NAME      name of server instance (if using mysql.server startup script) 
24.  -l           don't subtract lock time from total time           //总时间中不减去锁定时间  

例子：

[root@BlackGhost bin]# ./mysqldumpslow -s r -t 20 /usr/local/mysql/mysql-slow.log

[root@BlackGhost bin]# ./mysqldumpslow -s r -t 20 -g 'count' /usr/local/mysql/mysql-slow.log

如果在my.cnf里面修改，需增加如下几行

long_query_time = 1
log-slow-queries = log的路径
log-queries-not-using-indexes

5.6以后版本，开启慢查询的方法变了。
开启的语句应该这样写（修改my.cnf）：
slow-query-log=1slow-query-log-file=/usr/local/mysql/data/slow.log
保存、重启，问题解决。

long_query_time 是指执行超过多久的sql会被log下来，这里是1秒。
log-slow-queries 设置把日志写在那里，可以为空，系统会给一个缺省的文件log-queries-not-using-indexes 就是纪录没使用索引的sql
分析：
mysqldumpslow –help以下，主要用的是
-s ORDER what to sort by (t, at, l, al, r, ar etc), ‘at’ is default
-t NUM just show the top n queries
-g PATTERN grep: only consider stmts that include this string

-s，是order的顺序，说明写的不够详细，俺用下来，包括看了代码，主要有
c,t,l,r和ac,at,al,ar，分别是按照query次数，时间，lock的时间和返回的记录数来排序，前面加了a的时倒叙
-t，是top n的意思，即为返回前面多少条的数据
-g，后边可以写一个正则匹配模式，大小写不敏感的

mysqldumpslow -s c -t 20 host-slow.log
mysqldumpslow -s r -t 20 host-slow.log

上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。
mysqldumpslow -t 10 -s t -g “left join” host-slow.log
这个是按照时间返回前10条里面含有左连接的sql语句。

Time: 060908 22:17:43
# Query_time: 12 Lock_time: 0 Rows_sent: 86345 Rows_examined: 580963
Q:这个是慢查的日志,都是些什么意思?

A:查询用了12妙，返回86345行，一共查了580963行

在web开发中，我们经常会写出一些SQL语句，一条糟糕的SQL语句可能让你的整个程序都非常慢，超过10秒一般用户就会选择关闭网页，如何优化SQL语句将那些运行时间 比较长的SQL语句找出呢？MySQL给我们提供了一个很好的功能，那就是慢查询！所谓的慢查询就是通过设置来记录超过一定时间的SQL语句！那么如何应用慢查询呢？

1.开启MySQL的慢查询日志功能
默认情况下，MySQL是不会记录超过一定执行时间的SQL语句的。要开启这个功能，我们需要修改MySQL的配置文件，windows下修改my.ini,Linux下修改my.cnf文件,在[mysqld]最后增加如下命令：

复制代码代码如下:

slow_query_log
long_query_time = 1

2.测试慢查询日志功能
(1)进入MySql控制台，执行如下语句:

复制代码代码如下:

select sleep(2);

mysql> select sleep(2);
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (2.12 sec)
(2)查看慢查询日志文件think-slow.log，在文件最后发现：

复制代码代码如下:

# Time: 121120 20:06:23
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 2.104120  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1353413183;
select sleep(2);

3.解释：
(1)slow_query_log 这句是开启记录慢查询功能，slow_query_log=0关闭；slow_query_log=1开启（这个1可以不写）

(2)long_query_time = 1 这句是记录超过1秒的SQL执行语句

(3)那么这个日志文件存放在什么地方呢？
默认是放在mysql的data目录，并且文件名为host_name-slow.log即 主机名-slow.log,比如在笔者的开发机上就是THINK-slow.log(因为偶用的Thinkpad，呵呵)

(4)如果日志文件不想放在data目录，我们可以通过如下配置指定存放的目录及日志文件名：
slow_query_log_file=file_name
其中file_name就是你的存放日志的目录和文件名，在这里注意有的资料上可能是log-slow-queries=file_name,这个在mysql5.5版已经过时！

4.如何记录低于1s的慢查询记录呢？
MySQL5.21版以前long_query_time 参数的单位是秒，默认值是10。这相当于说最低只能记录执行时间超过 1 秒的查询，怎么记录查询时间超过100毫秒的SQL语句记录呢？在mysql5.21+后版本支持毫秒记录
(1)进入MySql控制台，运行如下sql语句：

复制代码代码如下:

set global long_query_time=0.1

该句是设置记录慢查询超过时间100ms的SQL，记住要重启mysql才能生效！
(2)测试
进入mysql控制台，执行如下sql语句：

复制代码代码如下:

select sleep(0.5);

查看慢查询日志文件，我们看到最后添加的新信息：

复制代码代码如下:

# Time: 121120 20:42:06
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 0.500028  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1353415326;
select sleep(0.5);

5.6以后版本，开启慢查询的方法变了。
开启的语句应该这样写（修改my.cnf）：
slow-query-log=1slow-query-log-file=/usr/local/mysql/data/slow.log
保存、重启，问题解决。