MySQL：创建函数与创建存储过程+show profile分析

 

 

一、MySQL批量数据脚本总体流程如下：目标是往表里插入1千万条记录

1、建表

-- 1 新建部门表Wind_Dept
create table Wind_Dept(
id int unsigned not null auto_increment,
deptno mediumint unsigned not null default 0, /*部门编号*/
dname varchar(20) not null default "",
loc varchar(13) not null default "",
PRIMARY KEY (`id`)
)engine=innodb default charset=utf8mb4 comment='部门表';

-- 2 新建员工表Wind_Emp
create table Wind_Emp(
id int unsigned not null auto_increment,
empno mediumint unsigned not null default 0, /*编号*/
ename varchar(20) not null default "", /*姓名*/
job varchar(9) not null default "", /*工作*/
mgr mediumint unsigned not null default 0, /*上级编号*/
hiredate date not null, /*入职时间*/
sal decimal(7,2) not null, /*薪水*/
comm decimal(7,2) not null, /*红利*/
deptno mediumint unsigned not null default 0, /*部门编号*/
PRIMARY KEY (`id`)
)engine=innodb default charset=utf8mb4 comment='员工表';

drop table Wind_Dept;
drop table Wind_Emp;
select * from Wind_Dept;
select * from Wind_Emp;

2、设置参数log_bin_trust_function_creators：开启可以创建存储函数的权限

/**
简单介绍一下，当二进制日志启用后，这个变量就会启用。
它控制是否可以信任存储函数创建者，不会创建写入二进制日志引起不安全事件的存储函数。
如果设置为0（默认值），用户不得创建或修改存储函数，除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。
设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。 
如果变量设置为1，MySQL不会对创建存储函数实施这些限制。 此变量也适用于触发器的创建。
那么为什么MySQL有这样的限制呢？ 
因为二进制日志的一个重要功能是用于主从复制，而存储函数有可能导致主从的数据不一致。
所以当开启二进制日志后，参数log_bin_trust_function_creators就会生效，限制存储函数的创建、修改、调用。那么此时如何解决这个问题呢？set global log_bin_trust_function_creators=1;
**/
-- 开启可以创建存储函数的权限
show variables like "%log_bin_trust_function_creators%";
set global log_bin_trust_function_creators=0;
set global log_bin_trust_function_creators=1;

3、创建函数，保证每条数据都不同

3.1 随机产生字符串

-- （1）创建一个函数：随机产生一个字符串
delimiter $$
create function ran_string(n int) returns varchar(255)
begin
	declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz';
	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()*52),1));
		set i=i+1;
	end while;
	return return_str;
end $$

3.2 随机产生部门编号 

-- （2）创建一个函数：随机产生部门编号 100-110
delimiter $$
create function rand_num() returns int(5)
begin
	declare i int default 0;
	set i=floor(100+rand()*10);
	return i;
end $$

4、创建存储过程

4.1 创建往Wind_Emp表中插入数据的存储过程

-- （3.1）创建往Wind_Emp表中插入数据的存储过程
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
-- 把MySQL默认的自动提交关闭
set autocommit = 0;
repeat
set i = i+1;
insert into Wind_Emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),ran_string(6),'salesman',0001,curdate(),2000,400,rand_num());
until i=max_num
end repeat;
commit;
end $$

4.2 创建往Wind_Demp表中插入数据的存储过程

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 Wind_Dept(deptno,dname,loc) values((start+i),ran_string(10),ran_string(8));
until i=max_num
end repeat;
commit;
end $$

5、调用存储过程

5.1 插入部门表

-- （5.1）插入部门表
call insert_dept(100,10);
select * from Wind_Dept;

5.2 插入员工表

-- （5.2）插入员工表
call insert_emp(100001,500000);
select * from Wind_Emp;

6、结果

    

 

二、show profile分析

0、show profile总体概览

（1）Show Profile实战：https://www.cnblogs.com/116970u/p/11004431.html

1、show profile是什么

是mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量。

2、官网在哪里

http://dev.mysql.com/doc/refman/5.7/en/show-profile.html

3、参数是什么，开启了么

默认情况下，参数处于关闭状态，并保存最近15次的运行结果

4、分析步骤

4.1 查看当前的mysql版本是否开启show profile关键字分析SQL：

Show variables like 'profiling';

发现：show profile 功能默认是关闭的，使用前需要手动开启。

4.2 开启功能，默认是关闭，使用前需要开启：

　　show variables like 'profiling';
　　set profiling=1;

4.3 先运行SQL，然后通过show profiles查看问题SQL的编号：

select * from Wind_Dept;
select * from Wind_Emp;
select * from Wind_Emp e left join Wind_Dept d on e.deptno=d.id;
select * from Wind_Emp e right join Wind_Dept d on e.deptno=d.id;
select * from Wind_Emp e join Wind_Dept d on e.deptno=d.id;
select * from Wind_Emp group by id%10 limit 150000; -- 需要设置mode
select * from Wind_Emp group by id%20  order by 5; -- 需要设置mode

show profiles;

4.4 在拿到问题SQL的编号后，再使用 show profile 诊断问题SQL：

show profiles;
show profile cpu, block io for query n; -- 其中，n为上一步查询出来的问题SQL的数字号码

show profile cpu, block io for query 4;

show profile 后面的参数备注：

4.5 小结：日常开发需要注意的结论，观察status中出现的参数：

（1）converting HEAP to MyISAM ：查询结果太大，内存都不够用了，开始往磁盘上搬了。

（2）Creating tmp table ：创建临时表（group by 很容易创建临时表：先排序再分组。）

          A：新建临时表 B：拷贝数据到临时表 C：用完后再删除临时表。

（3）Copying to tmp table on disk ：把内存中的临时表复制到磁盘，非常危险！

（4）locked ：出现锁了。

4.6 全局日志查询（最好不要使用）

-- 全局日志查询是否开启，默认是关闭的（最好不要代开使用！）。
-- 日志文件 general_log_file：/usr/local/mysql/data/cmmMacPro.log
show variables like "%general_log%";
set global general_log=1;
set global general_log=0;
set global log_output="TABLE";
-- 执行SQL在线下复现慢查询场景
select * from Wind_Dept;
-- MySQL本身自带的数据库mysql，自带的表general_log（偷偷的把查询SQL记录一下）
select * from mysql.general_log;

 

 

99、番外篇

（1）mysql高级课程的笔记博客：https://www.cnblogs.com/116970u/category/1475957.html

（2）mysql高级课程的B站视频：尚硅谷周阳：https://www.bilibili.com/video/BV1KW411u7vy?p=51

 

本文地址：https://blog.csdn.net/cmm0401/article/details/107295136