MySQL如何快速生成百万级数据量？

改自it老哥

需求情景

• 在数据库中生成大量测试数据的情景，由于mysql有这种内存表插入快特点，先利用函数和存储过程在内存表生成数据，再将内存表中数据落到普通数据表中。

实现思路

1. 创建内存表和普通表（engine=innodb）

• 内存表的表数据在mysql实例重启后就会自动删除

2. 创建函数和存储过程

• 创建生成随机数字的函数

• 创建生成号码函数

• 创建生成随机字符串函数

• 创建插入内存表数据的存储过程

• 创建内存表数据插入普通表的存储过程

3. 调用存储过程插入数据

• 修改mysql内存表存储大小的值

• 调用存储过程 add_test_user_memory_to_outside

具体过程

1.创建内存表、普通表结构

• 内存表

create table `test_user_memory` (
`id` int(11) not null auto_increment comment '主键id',
`user_id` varchar(36) not null  comment '用户id',
`user_name` varchar(30) not null comment '用户名称',
`phone_num` varchar(20) not null comment '手机号码',
`lan_id` int(9) not null comment '本地',
`region_id` int(9) not null comment '区域',
`create_time` datetime not null comment '创建时间',
primary key (`id`),
key `idx_user_id` (`user_id`)
) engine=memory default charset=utf8mb4;
复制

• 普通表

create table `test_user` (
 `id` int(11) not null auto_increment comment '主键id',
`user_id` varchar(36) not null  comment '用户id',
`user_name` varchar(30) not null comment '用户名称',
`phone` varchar(20) not null comment '手机号码',
`lan_id` int(9) not null comment '本地',
`region_id` int(9) not null comment '区域',
`create_time` datetime not null comment '创建时间',
primary key (`id`),
key `idx_user_id` (`user_id`)
) engine=innodb default charset=utf8mb4;
复制

2.创建函数

• 函数1:生成n个随机数字（生成手机号时用）

delimiter //
create function  randnum(n int) returns varchar(255)
begin
declare chars_str varchar(20) default '0123456789';
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()*10 ),1));
set i = i +1;
end while;
return return_str;
end //
delimiter ;
复制

注意：
这里可能报错：
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
解决方法：

SET GLOBAL log_bin_trust_function_creators = 1;
复制

• 函数2:生成号码的函数

    生成随机手机号
    定义常见的手机前3位： 130 131 132 133 134 135 136 137 138 139 186 187 189 151 157 
    set starts = 1 + floor(rand()*15)*4; 截取字符串的开始是从 1、5、9、13 ... 开始的,每隔4取。floor(rand()*15)的取值范围是0~14
    set head = substring(bodys,starts,3);在字符串bodys中从starts位置截取三位
  复制

delimiter //
create function generatephone() returns varchar(20)
begin
declare head char(3);
declare phone varchar(20);
declare bodys varchar(100) default "130 131 132 133 134 135 136 137 138 139 186 187 189 151 157";
declare starts int;
set starts = 1+floor(rand()*15)*4;  
set head = trim(substring(bodys,starts,3));  
set phone = trim(concat(head,randnum(8)));
return phone;
end //
delimiter ;
复制

• 函数3:创建随机字符串函数

delimiter //
create function `randstr`(n int) returns varchar(255) charset utf8mb4
deterministic
begin
declare chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
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() * 62), 1));
set i = i + 1;
end while;
return return_str;
end //
delimiter ;
复制

• 存储过程1:创建插入内存表数据的存储过程

delimiter //
create procedure `add_test_user_memory`(in n int)
 begin
declare i int default 1;
while (i <= n) do
insert into test_user_memory (user_id, user_name, phone_num, lan_id,region_id, create_time) values (uuid(), randstr(20), generatephone(), floor(rand() * 1000), floor(rand() * 100), now());
set i = i + 1;
end while;
end //
delimiter ;
复制

• 存储过程2:创建内存表数据插入普通表存储过程

    此处利用对内存表的循环插入和删除来实现批量生成数据，这样可以不需要更改mysql默认的max_heap_table_size值也照样可以生成百万或者千万的数据。
    max_heap_table_size默认值是16M。
    max_heap_table_size的作用是配置用户创建内存临时表的大小，配置的值越大，能存进内存表的数据就越多。
    
    循环从内存表获取数据插入普通表
    参数描述 n表示循环调用几次；count表示每次插入内存表和普通表的数据量
  复制

delimiter //
create procedure `add_test_user_memory_to_outside`(in n int, in count int)
begin
declare i int default 1;
while (i <= n) do
call add_test_user_memory(count);
insert into test_user select * from test_user_memory;
delete from test_user_memory;
set i = i + 1;
end while;
end //
delimiter ;
复制

3.调用存储过程插入数据

step1:先调用存储过程往内存表插入一万条数据，然后再把内存表的一万条数据插入普通表

call add_test_user_memory(10000);
复制

step2:一次性把内存表的数据插入到普通表，这个过程是很快的

insert into test_user select * from test_user_memory;
复制

step3:清空内存表数据

delete from  test_user_memory;
复制

4.插入十万或者百万数据

查看内存表大小，默认值为16777216

show variables like "%table_size%";
复制

方法一：调整内存表大小
方法二：调取另一个存储过程：add_test_user_memory_to_outside
这个存储过程就是通过不断循环插入内存表，再从内存表获取数据插入普通表，最后删除内存表，以此循环直至循环结束。

call add_test_user_memory_to_outside(100,10000);
复制