如何快速在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 参考

留言列表
  • lm:
    数据的预取总是失败,求大佬指教下,实现了个ssr,但是总觉得我实现的ssr不对
      2019年03月15日 16:46 回复
    • ewr:
      eqr
        2018年09月21日 09:17 回复
      • 小王:
        你好,请问一下,官网demo 跑起来了,但是用浏览器http://localhost:8080.一直加载不出来是怎么回事呢?
          2018年09月20日 14:50 回复
        • dk:
          请教博主一点问题,目前我的ssr项目已基本完成开发,但不知如何部署到虚拟服务器centos7中,具体的问题是不懂需要将哪些代码上传到服务器并使用pm2来启动项目.希望博主有空回答下,谢谢.
          • u3xyz:
            你好,其实这个问题与“hp5”的问题有点类似,你可以看下那个回复,理论上构建生成的所有前端资源文件都会用到,都需要放到服务器
            2018年09月11日 21:47
          2018年09月11日 20:18 回复
        • hp5:
          你好,vue-ssr开发基本没什么问题,但是不知道如何部署,希望请教一下,上线部署时,需要将那些文件给后端,希望博主有时间回复一下,谢谢。
          • u3xyz:
            你好,所谓VueSSR,本质是在前后端共用一套js代码,在Node环境下直接吐出页面的过程。SSR一定是在Node环境 ,这个与Java,php等传统后端没什么关系(后端只提供数据接口),也就谈不上要给他们什么文件了,独立部署即可。
            2018年07月25日 10:36
          2018年07月24日 13:10 回复
        • lgf:
          我的项目是vue-cli 然后 实现ssr seo渲染
            2018年07月03日 19:07 回复
          • lgf:
            你好,我现在也是有SEO需求,SSR做,在浏览器打开的时候显示网页源代码可以看到详细的html,增加百度搜索更多的关键子,现在我ssr还是不太能行,用了个prerender-spa-plugin,但是这个插件问题太多,你这篇文章我看了一遍我实现有点困难,(比较菜) 有没有再详细点的教程呢 需求比较着急,所以问问您还有再详细点的demo吗
            • u3xyz:
              推荐多参考官方Demo: https://github.com/vuejs/vue-hackernews-2.0/
              2018年07月25日 10:28
            2018年07月03日 19:05 回复
          • 小白白:
            刚开始研究 我有一个问题 开发过程中我修改代码 就要重新打一次包么?怎么实现热更新啊 希望指点一下
              2018年06月29日 11:40 回复
            • 木メメ木+大:
              你好,发现你的博客的一个现象:比如在第二页点击进入详情,然后按下浏览器返回,就回到第一页,这个是博主刻意为之的吗? 另外特别想请教一下,左右目录联动的实现~
              • u3xyz:
                第一个问题:你是说在第二页的文章返回时默认到了第一页吗?这个不是刻意为之,算小bug吧 第二个问题:可以参看我的文章《如何实现markdown文章标题导航》
                2018年06月28日 12:33
              2018年06月27日 11:46 回复
            • 昌子:
              最近在做ssr,前面过程很顺利,到后面npm run build时,会爆出一个elementUI的样式错误,搞了好几天了还是无头绪,有时间帮忙看下吗?项目急上线
              • u3xyz:
                这个得你自己想办法解决哈
                2018年06月28日 12:31
              2018年06月26日 22:56 回复
            • meme:
              不太懂服务端,骨架屏倒是实现了,ssr看了好多,明白大致流程,就是对服务端不太了解,首先阿里买个云主机,然后你用sheel 连接ssh 登录 起一个www服务器,那么数据库放哪里呢,怎么就线上也能用了?还有node 服务放哪里,还有比如我是node写的接口,怎么弄呢。最近又看了 nginx 是说可以静态资源转发,不太明白其中的流程。期望大佬,写篇文章。或者私信一下。谢谢了
              • u3xyz:
                可以看看文章中的服务器部署模块。我只用了一台阿里云主机,node服务,nginx,数据库都是在这台机器上
                2018年06月28日 12:30
              2018年06月26日 21:10 回复
            • PlainHeart:
              作者,您好,我最近刚开始研究这个,按照您的文档我并不能实现功能,希望在配置上能给予一些更详细的帮助,谢谢!
              • u3xyz:
                首先,得耐心一点,我也不是一下就很顺利得做出来了,那里有问题就想办法解决。此文章也只是列出了关键步骤,并不是所有的步骤。建议多研究官方示例vue-hackernews-2.0,或者试试Nuxt.js
                2018年06月28日 12:28
              2018年06月23日 17:55 回复
            • ljc:
              你好,本人刚接触vue-ssr,开发的时候基本没什么问题,但是不知道如何上线部署,想请教一下,上线部署的时候 ,需要把哪些文件给后端呢,希望博主有时间回复一下
              • u3xyz:
                你好,回复已经单独发qq邮箱(7048***)
                2018年04月21日 00:56
              2018年04月20日 15:33 回复

            发表评论: