使用MyBatis向MySql数据库批量insert插入100万条数据

1 场景

有时候在创建数据库之后，有一些已经存在的数据需要插入到数据库，这就涉及到了数据的大规模批量插入了，如果循环使用insert into table values(a,b,c)插入，速度将会比较慢，应该大部分时间都在事务的提交创建上了，mysql提供了批量插入的功能，可以使用insert into table values(a1,b1,c1),(a2,b2,c2)，(a3,b3,c3)……;当然可以使用mysql的存储过程，我这里使用了mybatis进行批量插入；

2 MySQL数据库user表

+---------+------------------+--------+-------+-----------+---------+
| Field   | Type             | Null   | Key   |   Default | Extra   |
|---------+------------------+--------+-------+-----------+---------|
| id      | int(10) unsigned | NO     |       |    <null> |         |
| name    | varchar(4)       | YES    |       |    <null> |         |
| city    | varchar(5)       | YES    |       |    <null> |         |
+---------+------------------+--------+-------+-----------+---------+

3 创建实体

public class User {
    private int id;
    private String name;
    private String city;
}

当然还有get和set方法以及构造函数；

4 批量插入接口

public interface UserMapper {
    // 批量新增用户
    int batchAdd(List<User> list);
}

5 Mapper配置SQL语句

<!--批量新增-->
<insert id="batchAdd" parameterType="java.util.List">
    INSERT INTO user(id,name,city)
    VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.id},#{item.name},#{item.city})
    </foreach>
</insert>

这里使用了foreach动态sql语句的写法；

6 max_allowed_packet数据库参数设置

该参数反映了MySQL允许的最大包数，最大值是1G；
配置文件路径：/etc/mysql/mysql.conf.d/mysqld.cnf
修改max_allowed_packet = 100M
保存退出，重启mysql服务sudo service mysql restart

7 测试（根据不同的需求编写）

public void batchAddTest() {
    //读入上下文
    ApplicationContext context = new ClassPathXmlApplicationContext("spring-dao.xml");
    //获得
    UserMapperImpl2 mapper = context.getBean("userMapper", UserMapperImpl2.class);
    //分批次循环获得
    int batchSize = 125000;
    long beforeTotal = System.currentTimeMillis();
    for (int i = 0; i < 1000000; i += batchSize) {
        logger.debug("第" + (i / batchSize) + "批次");
        List<User> list = new GenerateUsers().getUsers(i, batchSize);
        long before = System.currentTimeMillis();
        mapper.batchAdd(list);
        logger.debug("插入耗时：" + (System.currentTimeMillis() - before) + "毫秒");
    }
    logger.debug("总耗时：" + (System.currentTimeMillis() - beforeTotal) + "毫秒");
}

注意直接插入100万条不是最佳的选择，和深度学习一样，应该分batchsize分批次插入，作者实验了不同的batchsize的耗时：

batchsize	耗时（秒）
50000	62.696
62500	63.944
100000	49.173
125000	32.125
200000	71.469

值得注意的是batchsize并不完全是决定因素，还受服务器的计算资源分配、网络延迟等的影响，但是可以肯定的是需要在batchsize和插入每个batchsize所花时间之间权衡。