MySQL慢查日志

MySQL慢查日志的开启方式和存储格式

show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | ON    |
+----------------+-------+
 set global slow_query_log=on;
//需要权限
 set global log_queries_not_using_indexes=on;

查看慢查询设定时间

show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 10.000000 |
+-----------------+----------+

set global long_query_time = 0.5;//修改慢查询时间，重新连接sql会话后生效

慢查询日志记录位置
slow_query_log_file

mysql> show variables like 'slow%';
+---------------------+--------------------------+
| Variable_name       | Value                    |
+---------------------+--------------------------+
| slow_launch_time    | 2                        |
| slow_query_log      | ON                       |
| slow_query_log_file | PC-20120726BXZJ-slow.log |
+---------------------+--------------------------+

修改日志位置

set global slow_query_log_file = 'D:\slow.log';

慢查询日志包含的内容

• #User@Host:执行SQL的主机信息
• Query_time：SQL的执行信息
• Time: SQL的执行日期

# User@Host: root[root] @ localhost [127.0.0.1]  Id:    24
# Query_time: 0.001000  Lock_time: 0.001000 Rows_sent: 4  Rows_examined: 4
SET timestamp=1548387939;
SELECT * FROM `information_schema`.`columns` WHERE TABLE_NAME='notice' AND TABLE_SCHEMA='dx';
# Time: 2019-01-25T03:45:39.963597Z

慢查询日志分析工具
Window下安装perl
http://strawberryperl.com/
D:\Strawberry\perl\bin加入环境变量path
非windows环境以下命令去掉 .pl

//帮助
mysqldumpslow.pl -h
//查询执行最慢的三条
mysqldumpslow.pl -t 3 D:\slow.log |more