MySql || 快速创建100w条记录
平时每个开发者都会讨论数据量大时,sql的优化问题。但是并不是每个人都会有100w的数据量可以用来实战,那么今天我们就自己动手,模拟一个100w数据量的表。
- 创建原理
其实创建的方法有很多,有快的也有慢的。本博客中写的当然不是最快的那个,但确实是比较好操作和理解的。那么我先来说明一下它的原理:它是利用mysql中的在memory引擎的特点,用于快速的插入100w的数据在内存中存放,然后再利用sql插入到目标的表当中。
- 操作步骤
- 创建表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 $$
这个函数的语法不用解释也会看得懂,但是还是有几个点需要注意的。
- delimiter关键字,这是一个声明结束符的关键字。简单的来说就是,防止和begin和end之间的语句冲突,因为我们希望这里面的语句是连续执行的。
- concat(str,str)链接两个字符串组成一个新的字符串。
- 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才能更新修改。这个修改自己机器上玩玩就可以了,在生产环境这么改有什么后果我可不负责。
- 完善数据库
- 执行下面语句,大概需要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优化怎么玩。
上一篇: linux下实现VPS免费备份脚本
下一篇: 陕西排名前十的大学:陕西最有实力的大学