如何快速在mysql中生成大量Mock数据做性能测试？

最近做一个数据可视化项目时，需要 Mock 大量的数据（千万级）来进行接口开发。本文将记录探索实践的全部过程。

1. 如何快速 Mock 大量数据

1.1 逐条插入数据

因为博主主业是搞前端开发的，对 MySQL 其实不是特别在行了。要 Mock 数据，第一想法当然是写个程序或脚本来自动插入数据了。于是说干就干，很快一个基于 NodeJs 的 demo 就完成了。

建表 sql（为了演示方便，这里仅取4个字段，原测试 demo 有 21 个字段）：

use test;
CREATE TABLE test.data (
 `id` INT NOT NULL,
 `nt` VARCHAR(100) NOT NULL,
 `imei` VARCHAR(100) NOT NULL,
 `model` VARCHAR(50) NOT NULL,
 PRIMARY KEY (id)
)

逐条插入数据代码：

let mysql = require('mysql');
const uuidv1 = require('uuid/v1');
let connection = mysql.createConnection({
 host: 'localhost',
 port: '6666',
 user: 'root',
 password: '123456',
 database: 'test'
});
const close = () => connection.end();
function network() {
 return ['"wifi"', '"g4"', '"g3"', '"g2"'][Math.floor(Math.random() * 4)]
}
function phone_model() {
 return ['"NEX"','"x23"','"x21"','"x20"'][Math.floor(Math.random() * 4)]
}
function imei() {
 return `"${uuidv1()}"`;
}
connection.connect();
let s = Date.now();
let i = 0;
function insert() {
 connection.query(`insert into data(id, nt, imei, model) values (${i}, ${network()}, ${imei()}, ${phone_model()})`,
 function (error, results, fields) {
 if (error) throw error;
 if (i + 1 < 100000) {
 i++;
 insert();
 } else {
 console.log('done:' + (Date.now() - s));
 }
 });
}
insert();

用 10 条数据测试了下，perfect！完美工作。清空表，数量加大到1万条数据测试，勉强 perfect ，这次插入1万数据耗时48192ms，也就是48s左右，还能勉强接受。

继续扩量到10万条数据，这下就尴尬的很明显了，生成10万条数据，一共耗时618983ms，618s，也就是10分钟左右。大概心里估计了下，就算以线性递增来算，那么100万条数据大概就是100分钟，1000万数据大概是1000分钟，也就是近17个小时。

很明显，这效率太慢了，必须寻找效率更高的方式。

1.2 使用储存过程批量插入数据

在网上搜索了一会儿，果然找到了一种新方式：使用 mysql 储存过程来批量插入数据。所谓“储存过程”，个人认为就是批处理。

建表 sql，因为数据量大，这里加上了分区：

use test;
CREATE TABLE test.data (
 `id` INT NOT NULL,
 `nt` VARCHAR(100) NOT NULL,
 `imei` VARCHAR(100) NOT NULL,
 `model` VARCHAR(50) NOT NULL,
 PRIMARY KEY (id)
) ENGINE = MyISAM ROW_FORMAT = DEFAULT
 partition BY RANGE (id) (
 partition p0 VALUES LESS THAN (10000000),
 partition p1 VALUES LESS THAN (20000000),
 partition p2 VALUES LESS THAN (30000000),
 partition p3 VALUES LESS THAN (40000000),
 partition p4 VALUES LESS THAN (50000000),
 partition p5 VALUES LESS THAN (60000000),
 partition p6 VALUES LESS THAN (70000000),
 partition p7 VALUES LESS THAN (80000000),
 partition p8 VALUES LESS THAN (90000000),
 Partition p9 VALUES LESS THAN MAXVALUE
 );

接着是创建 mysql 储存过程，不过在编写储存过程代码时，遇到了一个问题，如何实现下面函数的功能，即随机从 wigi, 4g, 3g, 2g 中返回一个网络类型。

function network() {
 return ['"wifi"', '"g4"', '"g3"', '"g2"'][Math.floor(Math.random() * 4)]
}

2. 如何在储存过程中基于数组来生成随机值

查了大量资料，发现 mysql 储存过程不支持数组操作。一时间，似乎走到了死胡同。最后发现了这篇文章：MySQL函数和存储过程生成电话号码。作者生成电话号码的思路给了我启发，于是我参照他的思路，实现了随机生成网络类型的功能。

----------------------------
-- 生成网络类型的函数
----------------------------
DELIMITER $$
create function network() returns char(4)
begin 
 declare networks varchar(100) default "wifi4g 3g 2g "; -- 1,5,9,13
 declare idx int;
 declare ret char(4);
 set idx = 1+floor(rand()*4)*4;
 set ret = trim(substring(networks,idx,4));
 return ret;
end $$
DELIMITER ;

大概思路就是：

• 找出数组中最长的字符串项，比如 wifi,4g,3g,2g 中最长的项是 'wifi'，长度为4
• 将数组所有项用空格填充，让其与最长项长度一样，即wifi4g 3g 2g
• 随机生成固定的字符截取起始点。这里的随机，固定可能会不太好理解。看这个表达式1+floor(rand()*4)*4就清楚了，此表达式总是返回 1，5，9，13中某个值
• 截取字符串，同时去掉填充的空格，trim(substring(networks,idx,4))，就得到随机值了

解决随机生成值的问题后，储存过程的代码也就出来了：

----------------------------
-- 生成网络类型的函数
----------------------------
DELIMITER $$
create function network() returns char(4)
begin 
 declare networks varchar(100) default "wifi4g 3g 2g "; -- 1,5,9,13
 declare idx int;
 declare ret char(4);
 set idx = 1+floor(rand()*4)*4;
 set ret = trim(substring(networks,idx,4));
 return ret;
end $$
DELIMITER ;
----------------------------
-- 生成机型的函数
----------------------------
DELIMITER $$
create function phone_model() returns char(10)
begin 
 declare phone_types varchar(100) default "NEX x23 x21 x20 x9 x7 x6 x5 Z1 Z2 Z3 Y97 Y91 Y85 Y83 Y81 Y79 ";
 declare idx int;
 declare ret char(10);
 set idx = 1+floor(rand()*17)*4;
 set ret = trim(substring(phone_types,idx,4));
 return ret;
end $$
DELIMITER ;
----------------------------
-- 生成IMEI的函数
----------------------------
DELIMITER $$
create function randchar() returns char(5)
begin
 declare ret char(5); 
 set ret = substring("ABCDEFGHIJKLMNOPQRSTUVWXYZ",floor(1+26*rand()),1);
 return ret;
end $$
DELIMITER ;
DELIMITER $$
create function imei() returns char(50)
begin
 declare ret char(50) default ""; 
 declare imeiLen int default 11;
 DECLARE idx INT default 0;
 WHILE idx < imeiLen DO
 SET idx = idx + 1;
 SET ret = CONCAT(ret, randchar());
 END WHILE;
 return ret;
end $$
DELIMITER ;
----------------------------
-- 创建储存过程
----------------------------
use test;
DROP PROCEDURE IF EXISTS test.BatchInsertCustomer;
delimiter //
CREATE PROCEDURE BatchInsertCustomer(IN start INT,IN loop_time INT)
 BEGIN
 DECLARE Var INT;
 DECLARE ID INT;
 SET Var = 0;
 SET ID= start;
 WHILE Var < loop_time DO
 insert into data(`id`, `nt`, `imei`, `model`) 
 values (ID, network(), imei(), phone_model());
 SET Var = Var + 1;
 SET ID = ID + 1;
 END WHILE;
 END;
 //
delimiter ;

调用储存过程：

-- 调用
ALTER TABLE test DISABLE KEYS;
CALL BatchInsertCustomer(1, 10);
ALTER TABLE test ENABLE KEYS;

在测试时，使用储存过程生成1000万数据大概是140分钟，不到2个半小时。相比逐条插入的17个小时，快了8，9倍，效率提升不少。

3. 小结

使用 mysql 储存过程可以快速地生成 Mock 数据。同时本文还提供了一种“如何在储存过程中基于数组来生成随机值“的思路，希望对大家有些帮助。

3.1 参考

• MySQL批量插入大量数据方法
• MySQL函数和存储过程生成电话号码