Java: 写入100万数据到MySQL

数据量大的测试

忘了之前在哪遇到过这个问题,就是插入100万的数据到MySQL.

想了好几个办法,但发现还是用Java比较简单.

这里使用了Spring Data JdbcTemplate和Spring Boot,最低Java版本是Java 8.

解决方案1

第一个办法是直接用Spring Data JdbcTemplate的批量插入功能.完成100万数据插入到MySQL,大约需要5分钟.速度太慢了.

思路是:生成100万条随机数据,用batchUpdate批量插入进去.

代码如下:

public void run1(){
        System.out.println("开始.............................");
        beginTransaction();
        System.out.println(LocalDateTime.now().toString());
        int size=1000000;
        List<int[]> listArr = IntStream.rangeClosed(1,size).parallel().boxed().map(x->new int[]{x}).collect(Collectors.toList());
        System.out.println("data generator success.......................");
        jdbcTemplate.batchUpdate(" INSERT INTO `user_gender` VALUES (?,?,0) ",new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                int[] args = listArr.get(i);
                ps.setInt(1,args[0]);
                ps.setString(2, "student_"+args[0]);
            }

            @Override
            public int getBatchSize() {
                return size;
            }
        });
        endTransaction();
        System.out.println(LocalDateTime.now().toString());
}

解决方案2

经历第一种慢之后,思考了一下.似乎有一种更好的方法:

方案2插入100万数据,只需要约40秒.没错,就是40秒!

该方案部分参考了: https://www.cnblogs.com/cxxjohnson/p/9155411.html

方案2的思路是:

一下生成100万条数据,然后使用insert into ‘user_gender’ VALUES 的方式,一起加入.但会遇到个问题,在插入数据的时候可能会遇到问题:

om.mysql.jdbc.PacketTooBigException: Packet for query is too large (4232009 > 4194304). You can change this value on the server by setting the max_allowed_packet’ variable.

出现上面的错误是因为数据库表的 max_allowed_packet这个配置没配置足够大，因为默认的为4M的，后来我调为100M就没报错了

set global max_allowed_packet = 100*1024*1024;

记住，设置好后重新登录数据库才能看的设置后的值

show VARIABLES like ‘%max_allowed_packet%’;

代码如下:

public void run2(){
        System.out.println("start.............................");
        beginTransaction();
        System.out.println(LocalDateTime.now().toString());
        int size=1000000;
        String listArr = IntStream.rangeClosed(1,size).parallel().boxed().map(x->" (\""+x+"\",\"student_"+x+"\",0) ").collect(Collectors.joining(","));
        StringBuilder sql = new StringBuilder();
        sql.append("  INSERT INTO `user_gender` VALUES  ");
        sql.append(listArr);
        System.out.println("data generator success.......................");
        System.out.println(LocalDateTime.now().toString());
        System.out.println("start insert.................................");
        jdbcTemplate.batchUpdate(sql.toString());
        endTransaction();
        System.out.println(LocalDateTime.now().toString());
}

这样的速度,基本也就达到目标了.

完整源码: Gitee仓库地址