MySQL 轻松创建千万级测试数据

在进行查询操作的性能测试时,我们往往需要测试大数据量模式下的查询功能的性能,这时就需要我们去创造一些测试数据来填充数据库,来模拟真实环境。
制造数据的方式有很多种。可以使用 LoadRunner,JMeter 等压测工具压一些数据进去,但这样做效率不高。也可以用程序生成一个数据文件,再用 load data 加载,这样虽然快了但是又不够灵活。

本文介绍两种快速生成大量测试数据的方法,分别使用存储过程和临时数据表。

创建数据表

无论我们使用哪种方式,都需要创建一个数据表

1
2
3
4
5
6
7
8
9
CREATE TABLE `vote_record` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` varchar(20) NOT NULL,
`vote_id` int(11) NOT NULL,
`group_id` int(11) NOT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `index_user_id` (`user_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

使用存储过程

创建内存表

利用 MySQL 内存表插入速度快的特点,我们先利用函数和存储过程在内存表中生成数据,然后再从内存表插入普通表中。所以这里我们先创建一个内存表

1
2
3
4
5
6
7
8
9
CREATE TABLE `vote_record_memory` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` varchar(20) NOT NULL,
`vote_id` int(11) NOT NULL,
`group_id` int(11) NOT NULL,
`create_time` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `index_user_id` (`user_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

创建函数及存储过程

创建随机字符串,参数为字符串的长度

1
2
3
4
5
6
7
8
9
10
11
12
CREATE DEFINER=`root`@`%` FUNCTION `rand_string`(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
2
3
4
5
6
7
8
CREATE DEFINER=`root`@`%` PROCEDURE `add_vote_memory`(IN n int)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i <= n) DO
INSERT INTO vote_record_memory (user_id, vote_id, group_id, create_time) VALUES (rand_string(20), FLOOR(RAND() * 1000), FLOOR(RAND() * 100), NOW());
SET i = i + 1;
END WHILE;
END

调用存储过程

1
CALL add_vote_memory(1000000)

如果报错内存满了,可以通过修改max_heap_table_size这个参数调整。另外也可以将innodb_flush_log_at_trx_commit这个参数调整为0以提高 insert 的性能。关于这两个参数可以参考这里
这里再补充一个有用的函数:生成指定时间段内的随机时间

1
2
3
4
5
6
7
8
9
10
11
12
CREATE DEFINER=`root`@`%` FUNCTION `rand_datetime`(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

-- 测试
SELECT rand_datetime(DATE_FORMAT('2017-1-1 00:00:00','%Y-%m-%d %H:%i:%s'),DATE_FORMAT('2017-12-31 23:59:59','%Y-%m-%d %H:%i:%s')) AS t;

从内存表插入普通表

1
INSERT INTO vote_record SELECT * FROM vote_record_memory

TIPS

不采用内存表,直接用存储过程往普通表里插入数据也是可以的,只是效率上会差一些。我实验了用以上存储过程往内存表和普通表里分别插入1w条记录,耗时分别是 22.40s 和 30.70s,如果要插入的数据量在百万甚至千万级别的情况下,差别还是挺明显的。

另外,使用了上边创建随机字符串的rand_string()函数,也对执行效率有影响。如果对数据的要求不高,可以考虑使用CONCAT('testdata', id)的方式来代替。

使用临时数据表

生成临时数据表

创建临时数据表tmp_series

1
CREATE TABLE tmp_series (id INT, PRIMARY KEY (id));

用 python 生成 100w 记录的数据文件

1
python -c "for i in range(1, 1+1000000): print(i)" > 100w.txt

也可以直接用 bash 做,但 bash 的方式要比 python 慢得多

1
i=1; while [ $i -le 1000000 ]; do echo $i; let i+=1; done  > 100w.txt

导入数据到tmp_series

1
load data infile '/Users/yibo/100w.txt' replace into table tmp_series;

生成 100w 记录花了 41 秒

1
2
Query OK, 1000000 rows affected (41.05 sec)
Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 0

生成测试数据

通过tmp_series表生成并插入测试数据,测试数据的计算方法可以自由发挥。

1
2
3
4
5
6
7
8
9
10
INSERT INTO
vote_record
SELECT
id,
CONCAT('testdata', id),
FLOOR(Rand() * 1000),
FLOOR(Rand() * 100),
NOW()
FROM
tmp_series;

生成 100w 条数据耗时 25 秒,速度还挺不错

1
2
Query OK, 1000000 rows affected (24.82 sec)
Records: 1000000 Duplicates: 0 Warnings: 0

如果只想生成小的数据集,比如 1w 条记录,可以使用 limit。

1
2
3
4
5
6
7
8
9
10
11
12
INSERT INTO
vote_record
SELECT
id,
CONCAT('testdata', id),
FLOOR(RAND() * 1000),
FLOOR(RAND() * 100),
NOW()
FROM
tmp_series
ORDER BY id
LIMIT 10000;

总结

本文我们了解了两种创建大量测试数据方法——使用存储过程和使用临时数据表。使用存储过程的方式比较灵活也更简单便捷,但效率不高。使用临时数据表,我们需要创建临时表并生成一些临时数据导入其中,然后再利用临时数据表生成测试数据,虽然步骤稍多却效率可观。我们可以根据实际情况酌情选择适合的方法。

参考

快速为MySQL创建大量测试数据
mysql 快速生成百万条测试数据
创建千万级测试数据 · Mysql进阶
MySQL存储过程 - MySQL教程™
MySQL insert 速度过慢