MYSQL 慢查询日志 2

3.2.2   慢查询日志（2）

如果我们想得到锁定时间最长的10条SQL语句，可以运行：

 
 
[root@nh119-141 data]# /usr/local/mysql/bin
/mysqldumpslow -s al -n 10 david.log   
Reading mysql slow query log from david.log  
Count: 5  Time=0.00s (0s)  Lock=0.20s (1s) 
Rows=4.4 (22), Audition [Audition]@[192.168.30.108]  
  SELECT OtherSN, State FROM wait_friend_info WHERE UserSN = N  
 
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  
Rows=1.0 (1), audition-kr[audition-kr]@[192.168.30.105]  
  SELECT COUNT(N) FROM famverifycode WHERE 
UserSN=N AND verifycode='S' 
...... 

MySQL 5.1开始可以将慢查询的日志记录放入一张表中，这使我们的查询更加直观。慢查询表在mysql架构下，名为slow_log。其表结构定义如下：

 
 
mysql> show create table mysql.slow_log;  
*************************** 1. row ***************************  
       Table: slow_log  
Create Table: 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(11) NOT NULL,  
  'sql_text' mediumtext NOT NULL  
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log' 
1 row in set (0.00 sec) 

参数log_output指定了慢查询输出的格式，默认为FILE，你可以将它设为TABLE，然后就可以查询mysql架构下的slow_log表了，如：

 
 
mysql> show variables like 'log_output';  
+---------------+---------+  
| Variable_name | Value |  
+---------------+---------+  
| log_output    | FILE |  
+---------------+---------+  
1 row in set (0.00 sec)  
 
mysql> set global log_output='TABLE';  
Query OK, 0 rows affected (0.00 sec)  
 
mysql> show variables like 'log_output';  
+---------------+---------+  
| Variable_name | Value |  
+---------------+---------+  
| log_output  | TABLE |  
+---------------+---------+  
1 row in set (0.00 sec)  
 
mysql> select sleep(10);  
+-----------+  
| sleep(10)|  
+-----------+  
|       0 |  
+-----------+  
1 row in set (10.01 sec)  
mysql> select * from mysql.slow_log\G;  
*************************** 1. row ***************************  
    start_time: 2009-09-25 13:44:29  
     user_host: david[david] @ localhost []  
    query_time: 00:00:09  
     lock_time: 00:00:00  
     rows_sent: 1  
 rows_examined: 0  
            db: mysql  
last_insert_id: 0  
     insert_id: 0  
     server_id: 0  
      sql_text: select sleep(10)  
1 row in set (0.00 sec) 

参数log_output是动态的，并且是全局的。我们可以在线进行修改。在上表中我设置了睡眠（sleep）10秒，那么这句SQL语句就会被记录到slow_log表了。

查看slow_log表的定义会发现，该表使用的是CSV引擎，对大数据量下的查询效率可能不高。我们可以把slow_log表的引擎转换到MyISAM，用来进一步提高查询的效率。但是，如果已经启动了慢查询，将会提示错误：

 
 
mysql> alter table mysql.slow_log engine=myisam;  
ERROR 1580 (HY000): You cannot 'ALTER'
a log table if logging is enabled  
 
mysql> set global slow_query_log=off;  
Query OK, 0 rows affected (0.00 sec)  
 
mysql> alter table mysql.slow_log engine=myisam;  
Query OK, 1 row affected (0.00 sec)  
Records: 1  Duplicates: 0  Warnings: 0 

不能忽视的是，将slow_log表的存储引擎更改为MyISAM后，对数据库还是会造成额外的开销。不过好在很多关于慢查询的参数都是动态的，我们可以方便地在线进行设置或者修改。