mysql插入千万条随机数据

因为想测试sql在千万级数据下的优化，准备在自己的阿里云数据库插入一千万条数据，在网上搜了全是些乱七八糟的，于是就自己动手，同时也希望和大家分享，不足之处还请各位指教。

一、建表

1.创建部分表

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;

    为了使插入数据为正数，我是用来unsigned关键字修饰，字段分别为编号，部分编号，部门名称，位置

2.创建员工表

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;

    员工表的各字段分别为编号，员工编号，员工姓名，工作，上级，雇佣时间，薪水，奖金，从属部门编号

二、开启二进制函数功能

    我们的数据内容是随机的，所以要使用函数来产生随机的字符串和部门编号。mysql的二进制函数功能默认是关闭的，我们可以通过以下命令查询：

show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+

然后通过
set global log_bin_trust_function_creators=1;
将二进制函数功能开启
我们再次查询，结果如下所示

show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+

这样二进制函数功能就正常开启了，这个功能是本次有效，不过没必要一直开启，接下来我们就可以开始编写我们的函数了。

三、编写随机函数

1.随机产生字符串

delimiter $$
create function rand_string(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ';
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 
$$

    在这里说明下，为了避免;就导致命令提交，我用delimiter将提交的结束符改成$$

2.随机产生部门编号

delimiter $$
create function rand_num() returns int(5)
begin
declare i int default 0;
set i = floor(100+rand()*10);
return i;
end 
$$

四、创建存储过程

1.部门表插入存储过程

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 $$

2.员工表存储过程

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 
$$

五、调用储存过程

首先将提交符恢复为；
delimiter ;
1.插入10条部门数据
call insert_dept(100,10);
2.插入员工数据
这里我先测试插入50万条数据
call insert_emp (100001,500000);
结果：Query OK, 0 rows affected (34.40 sec)，用了34.4秒
再次从员工编号5000001开始插入10000000条数据，
call insert_emp (500001,10000000);
结果：Query OK, 0 rows affected (11 min 25.66 sec)

分析：随机数据的插入还是比较耗费性能的，耗时较长，通过比对两次插入员工表的时间可以看出，第二次插入每条的平均时间还小于第一次，以为两次都只进行了一次事务提交，所以平均下来，第二次插入的每条时间会比第一次少一点。好了，数据插入好之后，我们就可以开始我们的sql调优了