mysql慢日志详解和分析

本文主要是介绍mysql慢日志详解和分析，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

慢查询开启状态

show variables like '%slow_query_log%';

+---------------------+----------------------------------------+
| Variable_name       | Value                                  |
+---------------------+----------------------------------------+
| slow_query_log      | ON                                     |
| slow_query_log_file | /var/lib/mysql/VM-0-14-ubuntu-slow.log |
+---------------------+----------------------------------------+
2 rows in set (0.00 sec)

开启慢查询日志

set global slow_query_log=on;

设置慢查询时间

set global long_query_time=1;

查看慢查询时间

show variables like '%long_query_time%';

+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.01 sec)

查看慢查询记录数

show global status like '%slow_queries%';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 2     |
+---------------+-------+
1 row in set (0.00 sec)

show variables like '%slow_query_log_file%';

+---------------------+----------------------------------------+
| Variable_name       | Value                                  |
+---------------------+----------------------------------------+
| slow_query_log_file | /var/lib/mysql/VM-0-14-ubuntu-slow.log |
+---------------------+----------------------------------------+
1 row in set (0.00 sec)

查看日志

sudo cat /var/lib/mysql/VM-0-14-ubuntu-slow.log;

mysqldumpslow工具分析慢查询日志

耗时最多的5个sql语句

sudo mysqldumpslow -s t -t 5 /var/lib/mysql/VM-0-14-ubuntu-slow.log

访问次数最多的5个sql语句

mysqldumpslow -s c -t 5 /var/lib/mysql/VM-0-14-ubuntu-slow.log

返回记录集最多的5个sql语句

mysqldumpslow -s r -t 5 /var/lib/mysql/VM-0-14-ubuntu-slow.log

按照时间返回前5条里面含有左连接的sql语句

mysqldumpslow -t 5 -s t -g "left join" /var/lib/mysql/VM-0-14-ubuntu-slow.log

参数说明

-s：按照何种方式排序
c 记录次数
t 时间
l 查询时间
r 返回的记录数
ac at al ar 表示相应的倒序
-t：top n的意思
-g：后面可以写一个正则匹配模式，大小写不敏感

```angular2
Reading mysql slow query log from /var/lib/mysql/VM-0-14-ubuntu-slow.log
Count: 1  Time=590.46s (590s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  call lock_table()

Count: 1  Time=287.75s (287s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  call update_myisam()

Count: 2  Time=124.50s (248s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  update lock_myisam set `status`=N where `id`=N

Count: 1  Time=12.38s (12s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select count(*) from student left join class on class.id=student.class_id where class.id<N

Count: 2  Time=2.91s (5s)  Lock=0.00s (0s)  Rows=1.0 (2), root[root]@localhost
  select count(*) from student left join class on class.id=student.class_id  where student.age in(N,N,N)

这篇关于mysql慢日志详解和分析的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！