慢查询日志

数据库优化 学习笔记

一、慢查询日志

1.1、MySQL 的慢查询日志

• MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阙值的语句，具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。

• 具体指运行时间超过 long_query_time 值得 SQL，则会被记录到慢查询日志中。 long_query_time 的默认值为10，意思是运行10秒以上（大于十秒）的语句。

• 由他来查看那些 SQL 超出了我们的最大忍耐时间值，比如一条 SQL 执行超过5秒钟，我们就算是慢 SQL，希望能收集超过5秒的 SQL，结合之前 explain 进行全面分析。
  
  

1.2、怎么玩（优化时, 在测试环境使用）

• 默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数。

• 当然如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

	-- 查看是否开启慢查询日志, 有两个参数
	-- slow_query_log   		默认是关闭 OFF
	-- slow_query_log_file		文件的位置（默认运行时间超过10s的 sql 语句才会背保存在这, 可以改）
	show variables like "%slow_query_log%";

	-- 开启（但这只是临时的修改, 要永久修改需改文件）
	set global slow_query_log=1;
	
	-- 可手动添加这两个参数进行修改
	slow_query_log = 1
	slow_query_log_file = e:/mysql/mysql5.6.17/slow_log.txt

	-- 修改sql语句的超时阈值
	-- 新开一个cmd连接数据库再查看（我这里是用cmd连接的, 开着的不用关）可以看到以修改
	set global long_query_time=3;
	show variables like "%long_query_time%";

	-- 在两个窗口分别执行下列语句, 并查看文件变化
	select sleep(4);

1.3、慢查询日志工具（一般是在 Linux 中使用, 简单了解一下）

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

	-- 得到返回记录集最多的10个SQL
	mysqldumpslow -s r -t 10 e:/mysql/mysql5.6.17/slow_log.txt

	-- 得到访问次数最多的10个SQL
	mysqldumpslow -s c -t 10 e:/mysql/mysql5.6.17/slow_log.txt

---

二、批量插入数据

2.1、函数和存储过程

• 部门表

	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=gbk;

• 员工表

	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=gbk;

2.2、创建函数

        创建函数，假如报错：this function has none of DETERMINISTIC… 查看参数

	set global log_bin_trust_function_creators=1;

2.3、随机产生字符串

        执行完后，数据库中会出现一个函数

	DELIMITER $$			-- 声明分隔开始, 到结束为止的内容是一个整体
	CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)		-- 创建一个函数
	BEGIN
	-- declare 定义变量及类型
	DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
	DECLARE return_str VARCHAR(255) DEFAULT '';
	DECLARE i INT DEFAULT 0;
	
	-- concat(a,b) 把 a,b 连接成一个字符串
	-- substring(a,b) 从 a 取 b 个字符串
	-- floor() 取整,  rand() 随机
	WHILE i < n DO
	SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
	SET i = i + 1;
	END WHILE;
	
	RETURN return_str;		-- 返回拼接后的字符串
	END $$					-- 声明分隔结束

2.4、随机产生部门编号

	DELIMITER $$
	CREATE FUNCTION rand_num() RETURNS INT(5)
	BEGIN
	DECLARE i INT DEFAULT 0;
	SET i = FLOOR(100+RAND()*10);
	RETURN i;
	END $$

2.5、创建存储过程插入数据

• 向 emp 插入数据

	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),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
	UNTIL i = max_num					-- 插入结束条件
	END REPEAT;
	COMMIT;
	END $$

• 向 dept 插入数据

	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 + i),rand_string(10),rand_string(8));
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
	END $$

• 调用存储过程

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

注意： 若要在 cmd 里执行创建 函数 和 存储过程

• 先执行 DELIMITER $$ ，把语句的结束符改为 $$ ，此时 ; 才不会结束
• 然后执行 创建的语句
• 再执行 END $$
• 重复这三个步骤创建 函数 和 存储过程
• 创建完成后，执行 delimiter ; ，把语句的结束符改为 ;