MySql慢查询日志

慢查询日志

• mysql提供一种日志记录，它用来记录在mysql中响应时间超过阈值的语句，具体指运行时间超过long_query_time 会被记录到慢查询日志中；long_query_time 默认值为10s，运行时间超过10S的语句会被记录（不包含10S）
• 由它来查看哪些sql语句超出我们的最大忍耐时间值，对于慢sql我们用explain分析

怎么设置

• 默认情况下，mysql数据库没有开启慢查询日志，需要我们手动来设置这个参数
• 如果没有调优需求的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定性能影响。慢查询日志支持将日志记录写入文件

-- 查看慢查询日志是否开启，慢查询日志保存路径
show variables like '%slow_query_log%';

-- 开启慢查询日志（临时），永久修改设置my.ini文件
set global slow_query_log=1;


-- 查看阈值是多少（默认10）
show variables like 'long_query_time%';

-- 设置阈值
set global long_query_time=3;
set global slow_query_log_file=D:/phpStudy/PHPTutorial/MySQL/slow_log.txt

-- 测试
select sleep(5);

--在slow_log.txt可以看到这条语句，查询的时间及语句

慢查询日志工具

• s:表示按照何种方式排序
• c:访问次数
• l:锁定时间
• r:返回记录
• t:查询时间
• al:平均锁定时间
• ar:平均返回记录数
• t:即为返回前面多少条的数据

用法

在linux中执行

-- 查看mysqldumpslow有什么功能
mysqldumpslow --help

-- 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10  D:/phpStudy/PHPTutorial/MySQL/slow_log.txt

-- 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 D:/phpStudy/PHPTutorial/MySQL/slow_log.txt

批量插入数据

函数和存储过程

-- 部门表
create table dept(
    id int primary key auto_increment,
    deptno mediumint not null,
    dname varchar(20) not null,
    loc varchar(13) not null
)engine=innodb default charset=utf8;

-- 员工表
create table emp(
    id int primary key auto_increment,
    empno mediumint not null,
    ename varchar(20) not null,
    job varchar(9) not null,
    mgr mediumint not null,
    hiredate date not null,
    sal decimal(7,2) not null,
    comm decimal(7,2) not null,
    deptno mediumint not null
)engine=innodb default charset=utf8;

创建函数

如果报错:this function has none of DETERMINISTIC…查看参数

mysql中各种函数，参考：https://www.runoob.com/mysql/mysql-functions.html

show variables like 'log_bin_trust_function_creators%';

-- 如果是off，打开
set global log_bin_trust_function_creators=1;

-- 随机产生字符串
DELIMITER $$ --分隔符
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*48),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$

-- concat用于字符串的拼接
mysql> select concat('my','sql');
+--------------------+
| concat('my','sql') |
+--------------------+
| mysql              |
+--------------------+
1 row in set (0.00 sec)

-- substring用于截取字符串
substring(string,position,length);
-- string 要截取的字符串，position 起始字符（从1开始），length截取长度

-- 随机产生部门编号
delimiter $$
create function rand_num() returns int(5)
begin
declare i int default 0;
set i = floor(100+rand()*10);
return i;
end $$

创建存储过程

存储过程(stored procedure)是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程就是数据库sql语言层面的代码封装与重用。

可参考：https://www.runoob.com/w3cnote/mysql-stored-procedure.html

-- 插入数据
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'saleman',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$

-- procedure 就是存储过程
-- repeat 表示循环开始
-- curdate() 返回当前日期(年月日，不带时间)

-- 插入数据
delimiter $$
create procedure insert_dept(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into dept(deptno,dname,loc) values ((start+1),rand_string(10),rand_string(8));
until i = max_num
end repeat;
commit;
end $$

关于mysql中的循环可参考:https://www.cnblogs.com/weibanggang/p/9678312.html

调用存储过程

delimiter ;
call insert_dept(100,10);
call insert_emp(100001,500000);