mysql开启慢查询

1. 查询慢查询是否开启命令

# off 关闭状态，on：开启状态
show variables  like 'slow_query_log';

 

2.  开启慢查询命令

#on 开启，off关闭
set global slow_query_log = on;

3. 设置慢日志路径

#日志路径自行设置
set global slow_query_log_file ='D:\\log\\mysql\\slow.log';

 

4. 查询慢sql的时间默认10秒

show variables like "long_query_time";

5. 设置慢查询的时间

#0.1秒
set global long_query_time=0.1;

 

6. 以下新建表来测试慢查询

6.1 在test库建表

use test;
DROP TABLE IF EXISTS `t_slow_log_test`;
CREATE TABLE `t_slow_log_test`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

6.2 用存储过程批量写入数据

if EXISTS fetchInsert DROP fetchInsert;
create PROCEDURE fetchInsert()
begin
DECLARE i int;
DECLARE j DOUBLE;
DECLARE t_name varchar(20);
 
set i =0;
while i<140000 DO
insert into t_slow_log_test(name) values(t_name);
set i = i+1;
set t_name =  i + "-name";
end while;
end;

6.3 执行上述存储过程

#执行存储过程
CALL fetchInsert();

6.4 在执行存储过程fetchInsert()中会话费比较长的时间，在慢查询的日志中会有体现，如下：

进入刚才设置慢查询目录下会有体现：

#慢查询的日志目录查询slow.log
D:\develop\mysql\bin\mysqld, Version: 5.5.27 (MySQL Community Server (GPL)). started with:
TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
# Time: 191217 14:01:06
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 94.975587  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0

 

注：Linux 同样操作