慢查询定义及作用
慢查询日志，顾名思义，就是查询慢的日志，是指mysql记录所有执行超过指定参数设定的时间阈值的SQL语句的日志。该日志能为SQL语句的优化带来很好的帮助。默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能。
测试机器mysql的版本号为
mysql> show variables like '%version%';
+-------------------------+-----------------------------+
| Variable_name | Value |
+-------------------------+-----------------------------+
| innodb_version | 5.5.37 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.37-0ubuntu0.12.04.1-log |
| version_comment | (Ubuntu) |
| version_compile_machine | x86_64 |
| version_compile_os | debian-linux-gnu |
+-------------------------+-----------------------------+
开启慢查询日志
在mysql的配置文件my.cnf 中的mysqld下方添加以下参数:
[mysqld]
# slow query log
log_slow_queries=ON
long_query_time=1
slow_launch_time=1
slow_query_log=ON
slow_query_log_file=/var/lib/mysql/slow_queries.log
long_query_time=1 表示记录查询时间超过1秒的sql
slow_query_log_file 慢日志文件
slow_launch_time : 表示如果建立线程花费了比这个值更长的时间,slow_launch_threads 计数器将增加
重新启动mysql，查看配置参数如下：
mysql> show variables like '%slow%';
+---------------------+---------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------+
| log_slow_queries | ON |
| slow_launch_time | 1 |
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/slow_queries.log |
+---------------------+---------------------------------+
mysql> show variables like '%query%';
+------------------------------+---------------------------------+
| Variable_name | Value |
+------------------------------+---------------------------------+
| ft_query_expansion_limit | 20 |
| have_query_cache | YES |
| long_query_time | 1.000000 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/slow_queries.log |
+------------------------------+---------------------------------+
测试下：
mysql> select sleep(2) ;
+----------+
| sleep(2) |
+----------+
| 0 |
+----------+
1 row in set (2.00 sec)
查看日志：
root@ubuntu:~# cat /var/lib/mysql/slow_queries.log
/usr/sbin/mysqld, Version: 5.5.37-0ubuntu0.12.04.1-log ((Ubuntu)). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
/usr/sbin/mysqld, Version: 5.5.37-0ubuntu0.12.04.1-log ((Ubuntu)). started with:
Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock
Time Id Command Argument
# Time: 140816 13:03:53
# User@Host: root[root] @ localhost []
# Query_time: 2.000235 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1408165433;
select sleep(2);
