MySql || 快速创建100w条记录

平时每个开发者都会讨论数据量大时，sql的优化问题。但是并不是每个人都会有100w的数据量可以用来实战，那么今天我们就自己动手，模拟一个100w数据量的表。

• 创建原理

其实创建的方法有很多，有快的也有慢的。本博客中写的当然不是最快的那个，但确实是比较好操作和理解的。那么我先来说明一下它的原理：它是利用mysql中的在memory引擎的特点，用于快速的插入100w的数据在内存中存放，然后再利用sql插入到目标的表当中。

• 操作步骤

1. 创建表t_user，这是用于存放数据的表。

 1 create table `t_user` (
 2   `id` int(11) not null auto_increment,
 3   `c_user_id` varchar(36) not null default '',
 4   `c_name` varchar(22) not null default '',
 5   `c_province_id` int(11) not null,
 6   `c_city_id` int(11) not null,
 7   `create_time` datetime not null,
 8   primary key (`id`) 9 ) engine=innodb  default charset=utf8mb4;

　　2.创建内存表t_user_memory，这是用于快速插入数据的表。

1 create table `t_user_memory` (
2   `id` int(11) not null auto_increment,
3   `c_user_id` varchar(36) not null default '',
4   `c_name` varchar(22) not null default '',
5   `c_province_id` int(11) not null,
6   `c_city_id` int(11) not null,
7   `create_time` datetime not null,
8   primary key (`id`)
9 ) engine=memory default charset=utf8mb4;

　　3.创建随机字符串函数randstr()，用于在给c_name赋值时更加随机。

delimiter $$
create definer=`root`@`%` 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 $$

这个函数的语法不用解释也会看得懂，但是还是有几个点需要注意的。

1. delimiter关键字，这是一个声明结束符的关键字。简单的来说就是，防止和begin和end之间的语句冲突，因为我们希望这里面的语句是连续执行的。
2. concat(str,str)链接两个字符串组成一个新的字符串。
3. floor(num)函数，向下取整。例如floor(1.23) = 1 、floor(-1.23) = -2。

 

　　4.创建随机时间函数randdatatime()，为了后期的时间更加随机。

 1 delimiter $$
 2 create definer = `root`@`%` function `randdatatime` (sd datetime, ed datetime) returns datetime deterministic
 3 begin
 4     declare
 5         sub int default 0 ; 
 6     declare
 7             ret datetime ;
 8         set sub = abs(
 9             unix_timestamp(ed) - unix_timestamp(sd)
10         ) ;
11         set ret = date_add(
12             sd,
13             interval floor(1 + rand() *(sub - 1)) second
14         ) ; 
15     return ret ; 
16 end $$

里面的需要注意的是date_add(datetime,interval  num  second)函数，第一个参数是被添加的时间，第二个参数是一个组合，表明是添加的长度，如：（interval 1  year）表示在原来的基础上添加一年的时间间隔。

　　5.创建插入数据存储过程add_t_user_memory (int)

 1 delimiter $$
 2 create definer=`root`@`%` procedure add_t_user_memory (in n int)
 3 begin
 4     declare
 5         i int default 1 ;
 6     while i < n do
 7         insert into t_user_memory (
 8             c_user_id,
 9             c_name,
10             c_province_id,
11             c_city_id,
12             create_time
13         )
14     values
15         (
16             uuid(),
17             randstr (20),
18             floor(rand() * 1000),
19             rand() * 100,
20             now()
21         );
22 set i = i + 1 ;
23 end
24 while ;
25 end $$

 

• 执行过程中的问题

在完成上面的声明之后我们就可以用call  add_t_user_memory (1000000)来创建100w的数据啦，这大概需要20分钟的时间。但是在调用的过程中却发现了一个问题如下图所示。

原来是't_user_memory' 已经满了。它是我在上面定义的用内存存放数据的表，在mysql中默认的大小是16mb。这个大小只能放4w多条数据，所以我们要想办法把它扩大。那么可以执行下面语句

1 set global max_heap_table_size = 1024 * 1024 * 1024 * 1;
2 
3 #查看当前的设置的大小
4 select @@max_heap_table_size;

注意：修改需要重新链接mysql才能更新修改。这个修改自己机器上玩玩就可以了，在生产环境这么改有什么后果我可不负责。

 

• 完善数据库

1. 执行下面语句，大概需要10s就可以插入到t_user中。

insert into t_user select * from t_user_memory;

　　2.打乱创建时间。

#更新年间隔
update t_user set create_time=date_add(create_time, interval floor(1 + (rand() * 4)) year);
#更新秒间隔
update t_user set create_time=randdatatime(now(),create_time);

• 完成

这样，我们的百万级数据库就创建完成啦！然后，我们可以将t_user_memory这个表清空，毕竟它是很占内存的，你数据有多少内存就占多少。下次再结合业务试试sql优化怎么玩。