慢查询日志(slow-query-log)
一、什么是慢查询日志?
慢查询日志是MySQL提供的一种日志记录,用来记录在MySQL中响应(执行)时间超过阀值(long_query_time,默认值:10,单位:秒)的SQL语句。
默认情况下,MySQL没有启用慢查询日志。进行MySQL调优时,则可以开启慢查询日志,调优完成后,建议关闭慢查询日志,因为开启慢查询日志会或多或少带来一定的性能影响。
登陆数据库:mysql –uroot –p
Enter password:
查看 MySQL 慢查询日志
1、查看慢查询日志是否开启:mysql> show variables like 'slow_query_log%';
slow_query_log 慢查询开启状态 OFF 未开启;ON 为开启;
mysql> show variables like 'slow_query_log%';
+---------------------+-------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /home/data/mysql/localhost-slow.log |
+---------------------+-------------------------------------+
2 rows in set (0.00 sec)
2、查看慢查询日志超时时间:mysql> show variables like 'long_query_time';
long_query_time 查询超过多少秒才记录 默认10秒,修改为1秒。
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
二、如何开启 MySQL 慢查询日志:
两种方法:
1、临时开启慢查询:不需要重启数据库,但当 MySql 重启后慢查询就失效。
mysql> set global slow_query_log=1; || mysql> set global slow_query_log=on;
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.00 sec)
2、修改配置文件 /etc/my.cnf ;找到 [mysqld] 下面添加:
slow_query_log =1
slow_query_log_file="/home/data/mysql/localhost-slow.log"
long_query_time = 1
修改后重启 MySQL:service mysqld restart ;
再次,查看慢查询日志开启状态:mysql> show variables like '%query%';
slow_query_log:ON (已开启)
slow_query_log_file:/home/data/mysql/localhost-slow.log(表示指定慢查询日志的存储路径)。
mysql> show variables like '%query%';
+------------------------------+-------------------------------------+
| Variable_name | Value |
+------------------------------+-------------------------------------+
| binlog_rows_query_log_events | OFF |
| 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 | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| slow_query_log | ON |
| slow_query_log_file | /home/data/mysql/localhost-slow.log |
+------------------------------+-------------------------------------+
13 rows in set (0.00 sec)
三、慢查询日志分析工具 -- mysqldumpslow
mysqldumpslow是MySQL自带的分析慢查询的工具。
测试:mysql> select sleep(2.0);
mysql> select sleep(2.0);
+----------+
| sleep(2.0) |
+----------+
| 0 |
+----------+
1 row in set (2.00 sec)
查看帮助命令:[root@localhost ~]# mysqldumpslow -h
[root@localhost ~]# mysqldumpslow -h
Option h requires an argument
ERROR: bad option
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
其中:
-s 表示按照何种方式排序 c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间;
-t 表示 top n,即返回前面多少条数据;
-g 表示正则匹配模式,不区分大小写。
mysqldumpslow使用举例:
1、获取前 3 个SQL语句 -t 3
[root@localhost ~]# mysqldumpslow -t 3 /home/data/mysql/localhost-slow.log
[root@localhost ~]# mysqldumpslow -t 3 /home/data/mysql/localhost-slow.log
Reading mysql slow query log from /home/data/mysql/localhost-slow.log
Count: 3 Time=2.29s (6s) Lock=0.00s (0s) Rows=1.0 (3), mmo_admin[mmo_admin]@[192.168.1.230]
select sleep(N.N)
Died at /usr/bin/mysqldumpslow line 161, <> chunk 3.
其中:
Count: 3 Time=2.29s (6s) Lock=0.00s (0s) Rows=1.0 (3), mmo_admin[mmo_admin]@[192.168.1.230]
select sleep(N.N)
表示:
Count 出现次数=3次
Time 执行最长时间=2.29秒(累计总耗费时间=6秒)
Lock 等待锁的时间=0秒
Rows 发送给客户端的行总数=1行(扫描的行总数=3行)
mmo_admin 用户=mmo_admin @ 当前登录IP=192.168.1.230
select sleep(N.N) 实际执行的SQL语句(抽象化类型SQL)
2、按照时间排序且含有'sleep'的 top 3 个SQL语句 -s t -t 3 -g "sleep"
[root@localhost ~]# mysqldumpslow -s t -t 3 -g "sleep" /home/data/mysql/localhost-slow.log | more
[root@localhost ~]# mysqldumpslow -s t -t 3 -g "sleep" /home/data/mysql/localhost-slow.log | more
Reading mysql slow query log from /home/data/mysql/localhost-slow.log
Died at /usr/bin/mysqldumpslow line 161, <> chunk 3.
Count: 3 Time=2.29s (6s) Lock=0.00s (0s) Rows=1.0 (3), mmo_admin[mmo_admin]@[192.168.1.230]
select sleep(N.N)
在命令后面加上:| more ,可以避免可能出现的刷屏的情况,便于查看。
3、获取访问次数最多的 5 个SQL语句 -s c -t 5
[root@localhost ~]# mysqldumpslow -s c -t 5 /home/data/mysql/localhost-slow.log | more
四、小结
1、默认情况下,MySQL没有启用慢查询日志;
2、开启 MySQL 慢查询日志有两种方法:mysql> set global slow_query_log=1 或者 修改配置文件 /etc/my.cnf ;
3、可以使用MySQL自带的分析工具:mysqldumpslow ,进行慢查询日志分析;
4、调优完成后,建议关闭慢查询日志。