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

深碍√TFBOYSˉ_ 2022-11-27 03:27 103阅读 0赞

### 文章目录 ###

*  一. 回顾
 *  二. 准备环境

# 一. 回顾 #

前面学习了[Day5——explain][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

[Day5_explain]: https://blog.csdn.net/qq_40634846/article/details/107965957