MySQL如何快速的创建千万级测试数据

备注：

此文章的数据量在100w，如果想要千万级，调大数量即可,但是不要大量使用rand() 或者uuid() 会导致性能下降

背景

在进行查询操作的性能测试或者sql优化时，我们经常需要在线下环境构建大量的基础数据供我们测试，模拟线上的真实环境。

废话，总不能让我去线上去测试吧，会被dba砍死的

创建测试数据的方式

    1. 编写代码，通过代码批量插库（本人使用过，步骤太繁琐，性能不高，不推荐）

    2. 编写存储过程和函数执行（本文实现方式1）

    3. 临时数据表方式执行 （本文实现方式2，强烈推荐该方式，非常简单，数据插入快速，100w，只需几秒）

    4. 一行一行手动插入，（wtf，去死吧）

创建基础表结构

不管用何种方式，我要插在那张表总要创建的吧

create table `t_user` (
 `id` int(11) not null auto_increment,
 `c_user_id` varchar(36) not null default '',
 `c_name` varchar(22) not null default '',
 `c_province_id` int(11) not null,
 `c_city_id` int(11) not null,
 `create_time` datetime not null,
 primary key (`id`),
 key `idx_user_id` (`c_user_id`)
) engine=innodb default charset=utf8mb4;

方式1： 采用存储过程和内存表

创建内存表

利用 mysql 内存表插入速度快的特点，我们先利用函数和存储过程在内存表中生成数据，然后再从内存表插入普通表中

create table `t_user_memory` (
 `id` int(11) not null auto_increment,
 `c_user_id` varchar(36) not null default '',
 `c_name` varchar(22) not null default '',
 `c_province_id` int(11) not null,
 `c_city_id` int(11) not null,
 `create_time` datetime not null,
 primary key (`id`),
 key `idx_user_id` (`c_user_id`)
) engine=memory default charset=utf8mb4;

创建函数和存储过程

# 创建随机字符串和随机时间的函数
mysql> delimiter $$
mysql> 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$$
query ok, 0 rows affected (0.00 sec)

mysql> create definer=`root`@`%` function `randdatatime`(sd datetime,ed datetime) returns datetime
 -> deterministic
 -> begin
 -> declare sub int default 0;
 -> declare ret datetime;
 -> set sub = abs(unix_timestamp(ed)-unix_timestamp(sd));
 -> set ret = date_add(sd,interval floor(1+rand()*(sub-1)) second);
 -> return ret;
 -> end $$

mysql> delimiter ;

# 创建插入数据存储过程
mysql> create definer=`root`@`%` procedure `add_t_user_memory`(in n int)
 -> begin
 -> declare i int default 1;
 -> while (i <= n) do
 ->  insert into t_user_memory (c_user_id, c_name, c_province_id,c_city_id, create_time) values (uuid(), randstr(20), floor(rand() * 1000), floor(rand() * 100), now());
 ->  set i = i + 1;
 -> end while;
 -> end
 -> $$
query ok, 0 rows affected (0.01 sec)

调用存储过程

mysql> call add_t_user_memory(1000000);
error 1114 (hy000): the table 't_user_memory' is full

出现内存已满时，修改 max_heap_table_size 参数的大小，我使用64m内存，插入了22w数据，看情况改，不过这个值不要太大，默认32m或者64m就好，生产环境不要乱尝试

从内存表插入普通表

mysql> insert into t_user select * from t_user_memory;
query ok, 218953 rows affected (1.70 sec)
records: 218953 duplicates: 0 warnings: 0

方式2： 采用临时表

创建临时数据表tmp_table

create table tmp_table (
	id int,
	primary key (id)
);

用 python或者bash 生成 100w 记录的数据文件（python瞬间就会生成完）

python(推荐): python -c "for i in range(1, 1+1000000): print(i)" > base.txt
bash(不推荐，会比较慢): bash i=1; while [ $i -le 1000000 ]; do echo $i; let i+=1; done  > base.txt

导入数据到临时表tmp_table中

mysql> load data infile '/users/ljtjintao/temp/base.txt' replace into table tmp_table;
query ok, 1000000 rows affected (2.55 sec)
records: 1000000 deleted: 0 skipped: 0 warnings: 0

千万级数据 20秒插入完成

注意： 导入数据时有可能会报错，原因是mysql默认没有开secure_file_priv（ 这个参数用来限制数据导入和导出操作的效果，例如执行load data、select … into outfile语句和load_file()函数。这些操作需要用户具有file权限。 ）

解决办法：在mysql的配置文件中（my.ini 或者 my.conf）中添加 secure_file_priv = /users/ljtjintao/temp/` ,  然后重启mysql 解决

以临时表为基础数据，插入数据到t_user中，100w数据插入需要10.37s

mysql> insert into t_user
 -> select
 -> id,
 -> uuid(),
 -> concat('usernickname', id),
 -> floor(rand() * 1000),
 -> floor(rand() * 100),
 -> now()
 -> from
 -> tmp_table;
query ok, 1000000 rows affected (10.37 sec)
records: 1000000 duplicates: 0 warnings: 0

更新创建时间字段让插入的数据的创建时间更加随机

update t_user set create_time=date_add(create_time, interval floor(1 + (rand() * 7)) year);

query ok, 1000000 rows affected (5.21 sec)
rows matched: 1000000 changed: 1000000 warnings: 0

mysql> update t_user set create_time=date_add(create_time, interval floor(1 + (rand() * 7)) year);


query ok, 1000000 rows affected (4.77 sec)
rows matched: 1000000 changed: 1000000 warnings: 0

mysql> select * from t_user limit 30;
+----+--------------------------------------+----------------+---------------+-----------+---------------------+
| id | c_user_id    | c_name  | c_province_id | c_city_id | create_time  |
+----+--------------------------------------+----------------+---------------+-----------+---------------------+
| 1 | bf5e227a-7b84-11e9-9d6e-751d319e85c2 | usernickname1 |  84 | 64 | 2015-11-13 21:13:19 |
| 2 | bf5e26f8-7b84-11e9-9d6e-751d319e85c2 | usernickname2 |  967 | 90 | 2019-11-13 20:19:33 |
| 3 | bf5e2810-7b84-11e9-9d6e-751d319e85c2 | usernickname3 |  623 | 40 | 2014-11-13 20:57:46 |
| 4 | bf5e2888-7b84-11e9-9d6e-751d319e85c2 | usernickname4 |  140 | 49 | 2016-11-13 20:50:11 |
| 5 | bf5e28f6-7b84-11e9-9d6e-751d319e85c2 | usernickname5 |  47 | 75 | 2016-11-13 21:17:38 |
| 6 | bf5e295a-7b84-11e9-9d6e-751d319e85c2 | usernickname6 |  642 | 94 | 2015-11-13 20:57:36 |
| 7 | bf5e29be-7b84-11e9-9d6e-751d319e85c2 | usernickname7 |  780 |  7 | 2015-11-13 20:55:07 |
| 8 | bf5e2a4a-7b84-11e9-9d6e-751d319e85c2 | usernickname8 |  39 | 96 | 2017-11-13 21:42:46 |
| 9 | bf5e2b58-7b84-11e9-9d6e-751d319e85c2 | usernickname9 |  731 | 74 | 2015-11-13 22:48:30 |
| 10 | bf5e2bb2-7b84-11e9-9d6e-751d319e85c2 | usernickname10 |  534 | 43 | 2016-11-13 22:54:10 |
| 11 | bf5e2c16-7b84-11e9-9d6e-751d319e85c2 | usernickname11 |  572 | 55 | 2018-11-13 20:05:19 |
| 12 | bf5e2c70-7b84-11e9-9d6e-751d319e85c2 | usernickname12 |  71 | 68 | 2014-11-13 20:44:04 |
| 13 | bf5e2cca-7b84-11e9-9d6e-751d319e85c2 | usernickname13 |  204 | 97 | 2019-11-13 20:24:23 |
| 14 | bf5e2d2e-7b84-11e9-9d6e-751d319e85c2 | usernickname14 |  249 | 32 | 2019-11-13 22:49:43 |
| 15 | bf5e2d88-7b84-11e9-9d6e-751d319e85c2 | usernickname15 |  900 | 51 | 2019-11-13 20:55:26 |
| 16 | bf5e2dec-7b84-11e9-9d6e-751d319e85c2 | usernickname16 |  854 | 74 | 2018-11-13 22:07:58 |
| 17 | bf5e2e50-7b84-11e9-9d6e-751d319e85c2 | usernickname17 |  136 | 46 | 2013-11-13 21:53:34 |
| 18 | bf5e2eb4-7b84-11e9-9d6e-751d319e85c2 | usernickname18 |  897 | 10 | 2018-11-13 20:03:55 |
| 19 | bf5e2f0e-7b84-11e9-9d6e-751d319e85c2 | usernickname19 |  829 | 83 | 2013-11-13 20:38:54 |
| 20 | bf5e2f68-7b84-11e9-9d6e-751d319e85c2 | usernickname20 |  683 | 91 | 2019-11-13 20:02:42 |
| 21 | bf5e2fcc-7b84-11e9-9d6e-751d319e85c2 | usernickname21 |  511 | 81 | 2013-11-13 21:16:48 |
| 22 | bf5e3026-7b84-11e9-9d6e-751d319e85c2 | usernickname22 |  562 | 35 | 2019-11-13 20:15:52 |
| 23 | bf5e3080-7b84-11e9-9d6e-751d319e85c2 | usernickname23 |  91 | 39 | 2016-11-13 20:28:59 |
| 24 | bf5e30da-7b84-11e9-9d6e-751d319e85c2 | usernickname24 |  677 | 21 | 2016-11-13 21:37:15 |
| 25 | bf5e3134-7b84-11e9-9d6e-751d319e85c2 | usernickname25 |  50 | 60 | 2018-11-13 20:39:20 |
| 26 | bf5e318e-7b84-11e9-9d6e-751d319e85c2 | usernickname26 |  856 | 47 | 2018-11-13 21:24:53 |
| 27 | bf5e31e8-7b84-11e9-9d6e-751d319e85c2 | usernickname27 |  816 | 65 | 2014-11-13 22:06:26 |
| 28 | bf5e324c-7b84-11e9-9d6e-751d319e85c2 | usernickname28 |  806 |  7 | 2019-11-13 20:17:30 |
| 29 | bf5e32a6-7b84-11e9-9d6e-751d319e85c2 | usernickname29 |  973 | 63 | 2014-11-13 21:08:09 |
| 30 | bf5e3300-7b84-11e9-9d6e-751d319e85c2 | usernickname30 |  237 | 29 | 2018-11-13 21:48:17 |
+----+--------------------------------------+----------------+---------------+-----------+---------------------+
30 rows in set (0.01 sec)

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对的支持。