三分钟搞定向MySQL插入百万级数据

目录

  1.新建转换

  1.1 配置“生成记录”插件

  1.2 配置“表输出”插件

1.2.1 数据库连接

1.2.2 目标表

1.2.3 数据库字段

   2、利用主键自增插入百万数据

---

  在平时的数据库测试中，经常需要用到测试数据，而在验证索引的效率时，需要庞大的数据量才能更好地验证索引的效率，今天来分享一个实用的工具——kettle，能在几分钟内实现向MySQL插入百万级数据。

  kettle的安装使用很简单，下载、解压，双击打开即可使用，此步骤忽略。

  1.新建转换

  步骤：文件-新建-转换

  建好的转换流程如下：

    “生成记录”作为输入插件，“表输出”作为输出插件，在执行转换流程之前，我们需要提前建好需要输出的表结构，创建表结构如下：

CREATE TABLE `tb_user_1`  (
  `id` int(11) NOT NULL DEFAULT 0 AUTO_INCREMENT COMMENT '主键',
  `username` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '用户名',
  `phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '手机号',
  `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '邮箱',
  `profession` varchar(11) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '专业',
  `age` int(3) UNSIGNED NULL DEFAULT NULL COMMENT '年龄',
  `gender` int(1) NULL DEFAULT NULL COMMENT '性别 , 1: 男, 2: 女',
  `status` int(1) NULL DEFAULT NULL COMMENT '状态',
  `createtime` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_unicode_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

  创建表之后，开始配置输入和输出插件：

  1.1 配置“生成记录”插件

从上往下看箭头所指代表的意思：  

限制：这里的数字是指要往MySQL插入数据的条数，这里设置1，是因为主键是唯一的，数值大于1，违反了主键的唯一性，就会报错。

字段：名称和类型要和创建表时的对应；值为自定义；空窜默认否。

  1.2 配置“表输出”插件

先来看主界面：

1.2.1 数据库连接

 选择MySQL并填好连接信息，这里略过。

 除了配置连接信息，还需配置字符集，否则字段值有中文时会乱码。

 分别在“高级”和“选项”里配置，如下：

高级配置：

 选项配置 ：

1.2.2 目标表

 目标表填之前创建好的表名tb_user_1。 

1.2.3 数据库字段

 先选中“指定数据库字段”，再点击右侧的“获取字段”即可将表的字段同步过来，点击确定。

到这里已经完成了转换流程的配置，此时左上角会出现“有变动”字样，需要ctrl+s保存，否则配置不会生效。

点击面板上的三角形运行，运行结果如下：

回到数据库看下是否成功插入：

到这里已经成功向数据库插入了一条数据，回到主题， 如何快速插入百万级别的数据呢？

回到“生成记录”的插件：

那么是否可以在“限制”框里直接输入数字1000000呢？ 

答案是NO，因为在创建的tb_user_1表中，id作为主键具有唯一性，插入相同的值就会报错：

   2、利用主键自增插入百万数据

那么如何解决呢？在这里有个小细节，在创建表时设置了id自增，所以在“生成记录”的插件里不需要再定义字段id。只需定义除了id以外的其他字段，因为插入了其他字段的值，id会随着记录数逐行递增。

首先分别右键删除两个插件中的字段id：

删除后的“生成记录”插件如下：

再往表里插入一条名字为“吕布1”的记录，运行，看表记录：

可以看到插入了一条username为吕布1的记录，那么同样的道理，我们往表里插入100万条记录也是如此。

修改“生成记录”插件，将限制数改为100万。

运行：

插入100万条记录总耗时4分1秒，查看表记录：

加上之前的两条记录，一共1000002条记录，收工！ 

推荐阅读：

 MySQL查询性能优化——SQL优化(四)

 MySQL查询性能优化——索引的使用(三)

 MySQL查询性能优化——索引分类(二)

 MySQL查询性能优化——索引原理(一)