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

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

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

创建数据表

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

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 内存表插入速度快的特点，我们先利用函数和存储过程在内存表中生成数据，然后再从内存表插入普通表中。所以这里我们先创建一个内存表

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 = MEMORY DEFAULT CHARSET = utf8mb4;

创建函数及存储过程

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

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

创建插入数据存储过程

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

调用存储过程

CALL add_vote_memory(1000000)

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

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;

从内存表插入普通表

INSERT INTO vote_record SELECT * FROM vote_record_memory

TIPS

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

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

使用临时数据表

生成临时数据表

创建临时数据表tmp_series

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

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

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

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

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

导入数据到tmp_series表

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

生成 100w 记录花了 41 秒

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

生成测试数据

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

INSERT INTO
  vote_record
  SELECT
    id,
    CONCAT('testdata', id),
    FLOOR(Rand() * 1000),
    FLOOR(Rand() * 100),
    NOW()
  FROM
    tmp_series;

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

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

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

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 速度过慢