Day6——准备sql优化的环境——批量插入数据

一. 回顾

前面学习了Day5——explain，今天来准备sql优化的环境，批量插入数据，1w条部门数据，50w条员工数据。

二. 准备环境

这里直接给出sql脚本的代码，按照注释以及代码顺序去运行即可。

#创建表
create table dept(
    id int(11) not null auto_increment,
		deptName varchar(30) DEFAULT null,
		address VARCHAR(40) DEFAULT null,
		ceo int null,
		PRIMARY key (id)
)engine=INNODB auto_increment=1 DEFAULT CHARSET=utf8;

create table emp(
    id int(11) not null auto_increment,
		empno int not null,
		name VARCHAR(20) DEFAULT null,
		age int(3) DEFAULT null,
		deptId int(11) DEFAULT null,
		PRIMARY key (id)
		#CONSTRAINT fk_dept_id foreign key (deptId) REFERENCES dept(id)
)engine=INNODB auto_increment=1 DEFAULT CHARSET=utf8;


show VARIABLES like '%log_bin%';

#开启允许创建函数
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 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i int DEFAULT 0;
while i < n do
    #concat()是拼接字串，substring()是取字串【第二个入参是从哪个位置开始截取，第三个入参是指截取1个字符】，
		#floor()是取整，rand()是取[0,1)
    set return_str = concat(return_str, SUBSTRING(chars_str, floor(1+rand()*52), 1));
		set i = i + 1;
end while;
return return_str;
end $$

#用于随机生成多少到多少的编号
delimiter $$
create function rand_num(from_num int, to_num int) returns int(11)
begin
declare i int default 0;
set i = floor(from_num + rand()*(to_num-from_num+1));
return i;
end $$

#假如要删除
drop function rand_num;

----------------------------------------------------------------------------

#创建存储过程

#插入emp数据
delimiter $$
create procedure insert_emp(start int, max_num int)
begin
declare i int default 0;
set autocommit = 0;
repeat
    set i = i + 1;
		insert into emp(empno, name, age, deptId) values((start+i), rand_string(6), rand_num(30, 50), rand_num(1, 10000));
		until i = max_num
end repeat;
commit;
end $$

#插入dept数据
delimiter $$
create procedure insert_dept(max_num int)
begin
declare i int default 0;
set autocommit = 0;
repeat
    set i = i + 1;
		insert into dept(deptName, address, ceo) values(rand_string(8), rand_string(10), rand_num(1, 500000));
		until i = max_num
end repeat;
commit;
end $$

-----------------------------------------------------------------------------------------------

#执行存储过程

#往dept表添加1万条数据
delimiter ;
call insert_dept(10000);

#往emp表添加50万条数据
call insert_emp(100000, 500000);

-------------------------------------------------------------------------------------------------


#创建批量删索引存储过程
delimiter $$
create PROCEDURE proc_drop_index(dbname VARCHAR(200), tablename VARCHAR(200))
begin
    DECLARE done int DEFAULT 0;
		DECLARE ct int DEFAULT 0;
		DECLARE _index VARCHAR(200) DEFAULT '';
		DECLARE _cur CURSOR for SELECT index_name  FROM information_schema.STATISTICS WHERE table_schema=dbname and table_name=tablename
  AND seq_in_index = 1 AND index_name <>'PRIMARY';
    DECLARE continue HANDLER for not found set done = 2;
		OPEN _cur;
		fetch _cur into _index;
		while _index <>'' do
		    set @str = concat("drop index ", _index, " on ", tablename);
				PREPARE sql_str from @str;
				EXECUTE sql_str;
				DEALLOCATE PREPARE sql_str;
				set _index = '';
				FETCH _cur into _index;
		end while;
    close _cur;
end $$

#执行存储过程

delimiter ;
call proc_drop_index("mydb","t_emp");



SELECT index_name  FROM information_schema.STATISTICS WHERE table_name='t_emp' AND table_schema='mydb'

 AND index_name <>'PRIMARY' AND seq_in_index = 1