浅谈慢查询

慢查询是什么，如何操作？

MySQL记录下查询超过指定时间的语句，超过指定时间的SQL语句查询称为“慢查询”

开启慢查询

方法一：用命令开启慢查询

查看默认慢查询的时间（10秒）
    mysql> show variables like "%long%";  
    +-----------------+-----------+  
    | Variable_name   | Value     |  
    +-----------------+-----------+  
    | long_query_time | 10.000000 |  
    +-----------------+-----------+  
    1 row in set (0.00 sec)  

设置成2秒，加上global,下次进mysql已然生效 
    mysql> set global long_query_time=2;           
    Query OK, 0 rows affected (0.00 sec)  

查看一下慢查询是不是已经开启 
    mysql> show variables like "%slow%";           
    +---------------------+---------------------------------+  
    | Variable_name       | Value                           |  
    +---------------------+---------------------------------+  
    | log_slow_queries    | OFF                             |  
    | slow_launch_time    | 2                               |  
    | slow_query_log      | OFF                             |  
    | slow_query_log_file | /usr/local/mysql/mysql-slow.log |  
    +---------------------+---------------------------------+  
    4 rows in set (0.00 sec)  

设置开启慢查询
    mysql> set slow_query_log='ON';                          
    ERROR 1229 (HY000): Variable 'slow_query_log' is a GLOBAL variable and should be set with SET GLOBAL  
    
设置开启慢查询，加上global，不然会报错的
    mysql> set global slow_query_log='ON';         	
    Query OK, 0 rows affected (0.28 sec)  

查看是否已经开启 
    mysql> show variables like "%slow%";               
    +---------------------+---------------------------------+  
    | Variable_name       | Value                           |  
    +---------------------+---------------------------------+  
    | log_slow_queries    | ON                              |  
    | slow_launch_time    | 2                               |  
    | slow_query_log      | ON                              |  
    | slow_query_log_file | /usr/local/mysql/mysql-slow.log |  
    +---------------------+---------------------------------+  
    4 rows in set (0.00 sec)  
   
开启执行sql日志
	set  global general_log='ON';  

方法二：修改mysql的配置文件my.cnfgeneral_log

在[mysqld]里面加上以下内容
    long_query_time = 2  
    log-slow-queries = /usr/local/mysql/mysql-slow.log  

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