mysql批量插入数据脚本

当mysql逐行插入大量数据时速度很慢，可将插入数据的格式改为插入多行格式。

修改前：
insert into time_slot values ( 'A', 'M', 8, 0, 8, 50);
insert into time_slot values ( 'A', 'W', 8, 0, 8, 50);
insert into time_slot values ( 'A', 'F', 8, 0, 8, 50);
...

修改后：
insert into time_slot values
( 'A', 'M', 8, 0, 8, 50),
( 'A', 'W', 8, 0, 8, 50),
( 'A', 'F', 8, 0, 8, 50),
...
;

批量数据脚本及查询优化分析

 step1:建表

# 新建库
create database bigData;
use bigData;
 
 
#1 建表dept
CREATE TABLE dept(  
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,   
dname VARCHAR(20) NOT NULL DEFAULT "",  
loc VARCHAR(13) NOT NULL DEFAULT ""  
) ENGINE=INNODB DEFAULT CHARSET=UTF8 ;  
 
 
#2 建表emp
CREATE TABLE emp  
(  
id INT UNSIGNED PRIMARY KEY 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 /*部门编号*/  
)ENGINE=INNODB DEFAULT CHARSET=UTF8 ; 

 

 step2:设置参数log_bin_trust_function_creators

创建函数，假如报错：This function has none of DETERMINISTIC......
# 由于开启过慢查询日志，因为我们开启了 bin-log, 我们就必须为我们的function指定一个参数。

show variables like 'log_bin_trust_function_creators';

set global log_bin_trust_function_creators=1;

# 这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法：

windows下my.ini[mysqld]加上
log_bin_trust_function_creators=1

linux下 /etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators=1

 

 step3:创建函数,保证每条数据都不同

随机产生字符串

USE `bigdata`;
DROP function IF EXISTS `rand_string`;

USE `bigdata`;
DROP function IF EXISTS `bigdata`.`rand_string`;
;

DELIMITER $$
USE `bigdata`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `rand_string`(n int) RETURNS varchar(255) CHARSET utf8mb4
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
 ##声明一个 字符窜长度为 100 的变量 chars_str ,默认值 
 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));
##concat 连接函数  ，substring(a,index,length) 从index处开始截取
 SET i = i + 1;
 END WHILE;
 RETURN return_str;

END$$

DELIMITER ;
;

 

随机产生部门编号

USE `bigdata`;
DROP function IF EXISTS `rand_num`;

USE `bigdata`;
DROP function IF EXISTS `bigdata`.`rand_num`;
;

DELIMITER $$
USE `bigdata`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `rand_num`() RETURNS int
BEGIN
 DECLARE i INTEGER DEFAULT 0;  
 SET i = FLOOR(100+RAND()*10) ;  
RETURN i;
END$$

DELIMITER ;
;

 

 step4:创建存储过程

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

USE `bigdata`;
DROP procedure IF EXISTS `insert_emp`;

USE `bigdata`;
DROP procedure IF EXISTS `bigdata`.`insert_emp`;
;

DELIMITER $$
USE `bigdata`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_emp`(start int(10),max_num int(10))
BEGIN
DECLARE i INT DEFAULT 0;   
#set autocommit =0 把autocommit设置成0  ；提高执行效率
 SET autocommit = 0;    
 REPEAT  ##重复
 SET i = i + 1;
 if i%10000 = 0 then commit;
 end if;
 INSERT INTO emp (empno, ename ,job ,mgr ,hiredate ,sal ,comm ,deptno ) VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),FLOOR(1+RAND()*20000),FLOOR(1+RAND()*1000),rand_num());  
 UNTIL i = max_num   ##直到  上面也是一个循环
 END REPEAT;  ##满足条件后结束循环
 COMMIT;   ##执行完成后一起提交
END$$

DELIMITER ;
;

 

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

USE `bigdata`;
DROP procedure IF EXISTS `insert_dept`;

USE `bigdata`;
DROP procedure IF EXISTS `bigdata`.`insert_dept`;
;

DELIMITER $$
USE `bigdata`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_dept`(start int(10),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 ;
;

 

 step5:调用存储过程

-- 往dept表中插入数据
CALL insert_dept(100,10); 

-- 往emp表中插入数据
call insert_emp(100000,3000000);

 

 step6: 大量数据案例分析

 

 

EXPLAIN SELECT * FROM emp WHERE deptno =101 AND empno <101000 ORDER BY ename ;

SELECT * FROM emp WHERE deptno =101 AND empno <101000 ORDER BY ename ;
耗时3.71sec

 #结论：很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。

#开始优化：
思路： 尽量让where的过滤条件和排序使用上索引
但是一共两个字段(deptno,empno)上有过滤条件，一个字段(ename)有索引
1、我们建一个三个字段的组合索引可否？

create index idx_dno_eno_ena on emp(deptno,empno,ename);

EXPLAIN SELECT * FROM emp WHERE deptno =101 AND empno <101000 ORDER BY ename ;

SELECT * FROM emp WHERE deptno =101 AND empno <101000 ORDER BY ename ;
执行花费了0.011sec

我们发现using filesort 依然存在，所以ename 并没有用到索引。

原因是因为empno是一个范围过滤，所以索引后面的字段不会再使用索引了。

2、接着，创建一个deptno和ename的复合索引
drop index idx_dno_eno_ena on emp;
但是我们可以把索引建成
create index idx_dno_ena on emp(deptno,ename);

explain SELECT * FROM emp WHERE deptno =101 AND empno <101000 ORDER BY ename;

 

 也就是说empno 和ename这个两个字段我只能二选其一。这样我们优化掉了 using filesort。

SELECT * FROM emp WHERE deptno =101 AND empno <101000 ORDER BY ename;
查询卡住了，（原因调查中。。。）初步分析耗时发费在对empno筛选

3、建立deptno,empno字段复合索引

create index idx_dno_eno on emp(deptno,empno);

explain果然出现了filesort，意味着排序没有用到索引，我们来执行以下sql。

explain SELECT * FROM emp WHERE deptno =101 AND empno <101000 ORDER BY ename;

 

SELECT * FROM emp WHERE deptno =101 AND empno <101000 ORDER BY ename ;

查询效果 104 rows in set, 1 warning (0.12 sec)

结果竟然有 filesort的 sql 运行速度，超过了已经优化掉 filesort的 sql ，而且快了近10倍。何故？

原因是所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序但实际提升性能很有限。 相对的 empno<100100 这个条件如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。

结论： 当范围条件和group by 或者 order by 的字段出现二选一时 ，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。