mysqldumpslow输出错误

big_turnip

1. [root@tw-web tmp]# mysqldumpslow -t 3 -s at /var/log/mysql/slowquery.log
   
2. 
   
3. Reading mysql slow query log from /var/log/mysql/slowquery.log
   
4. Count: 2  Time=57.00s (114s)  Lock=0.00s (0s)  Rows=0.0 (0), xxxxxxxxxxxxxxxxxx
   
5.   # Thread_id: N  Schema: xxxx  Last_errno: N  Killed: N
   
6.   # Query_time: N.N  Lock_time: N.N  Rows_sent: N  Rows_examined: N  Rows_affected: N  Rows_read: N
   
7.   # Bytes_sent: N
   
8.   SET timestamp=N;
   
9.   SELECT `id`,`uname`,`alias`,`pid`,`limit` FROM `xxxxxxx`
   
10. 
    
11. Count: 1  Time=57.00s (57s)  Lock=0.00s (0s)  Rows=0.0 (0), xxxxxxxxxxxxxxxxxxxxx
    
12.   # Thread_id: N  Schema: xxxx  Last_errno: N  Killed: N
    
13.   # Query_time: N.N  Lock_time: N.N  Rows_sent: N  Rows_examined: N  Rows_affected: N  Rows_read: N
    
14.   # Bytes_sent: N
    
15.   SET timestamp=N;
    
16.   SELECT `uname`,`email`,`pwd`,`spwd` FROM `xxxxxx` WHERE ( `email` = 'S' ) AND ( `uname` = 'S' ) LIMIT N
    
17. 
    
18. Count: 1  Time=57.00s (57s)  Lock=0.00s (0s)  Rows=0.0 (0), xxxxxxxxxxxxxxxxxxxxxxx
    
19.   # Thread_id: N  Schema: xxxxx  Last_errno: N  Killed: N
    
20.   # Query_time: N.N  Lock_time: N.N  Rows_sent: N  Rows_examined: N  Rows_affected: N  Rows_read: N
    
21.   # Bytes_sent: N
    
22.   SET timestamp=N;
    
23.   SELECT * FROM `xxxxx`
    
24. 
    

复制代码

1. 
   
2. [root@tw-web-223 tmp]# grep Query_time /var/log/mysql/slowquery.log |sort -r |head -n 3
   
3. # Query_time: 0.003627  Lock_time: 0.000060  Rows_sent: 732  Rows_examined: 732  Rows_affected: 0  Rows_read: 732
   
4. # Query_time: 0.003410  Lock_time: 0.000058  Rows_sent: 732  Rows_examined: 732  Rows_affected: 0  Rows_read: 732
   
5. # Query_time: 0.003395  Lock_time: 0.000026  Rows_sent: 732  Rows_examined: 732  Rows_affected: 0  Rows_read: 732

复制代码

slowquery.log中最久的查询都没有超过1秒，为什么，用mysqldumpslow处理后的log会有那个多个57秒呢

1. mysql> show variables like 'long%';
   
2. +-----------------+----------+
   
3. | Variable_name   | Value    |
   
4. +-----------------+----------+
   
5. | long_query_time | 1.000000 |
   
6. +-----------------+----------+
   
7. 1 row in set (0.00 sec)
   

复制代码

配置中long_query_time = 1不是设定为慢查询为1s，为什么1s以下的查询也被记录到slowquery.log了

yqhenry

关于mysql slow quary log。我们一般在设置时有下面几个参数需要注意：
1、long_query_time   这个是查询的时间。
2、log_queries_not_using_indexes  如果这个选项开启，则查询时间小于long_quary_time，也会记录在slowlog里。
3、slow_query_log  slow_query_log_file  开启slowlog和slowlog的存放位置。

所以，关于你的问题，请你看下你的 log_queries_not_using_indexes 参数是否开启。