慢查询开启状态
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)