java 三种批量插入效率比较

批量插入

1. 传统循环插入
2. Mybatis foreach插入
3. jdbc批量插入

1、传统循环插入，代码如下

		long time = System.currentTimeMillis();
        for (int i = 0; i < 200000; i++) {
            studentMapper.batchInsert("ORDER_NO:" + i);
        }
        System.out.println("批量插入20万数据，耗费了" + (System.currentTimeMillis() - time) + "ms");

mapper接口

public void batchInsert(@Param("item") String item);

xml

<insert id="batchInsert">
    insert into t_order(id,order_no,create_time,status) values (null,#{item},'2020-1-19','0')
</insert>

执行耗费时间：

换算成秒：5998976/1000=5998.976秒，约等于99.9829335分钟

2、Mybatis foreach插入，代码如下

  		long time = System.currentTimeMillis();
        List<String> list = new ArrayList<>();
        for (int i = 0; i < 200000; i++) {
            list.add("ORDER_NO:"+i);
        }
        studentMapper.batchInsert(list);
        System.out.println("批量插入20万数据，耗费了" + (System.currentTimeMillis() - time) + "ms");

mapper接口

public void batchInsert(List list);

xml

<insert id="batchInsert">
    insert into t_order3(id,order_no,create_time,status) values
    <foreach collection="list" item="item" separator=",">
        (null,#{item},'2020-1-19','0')
    </foreach>
</insert>

执行耗费时间：

换算成秒，13295/1000=13.295秒

3、jdbc批量插入，代码如下

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class BatchInsertTest {

    private static String url = "jdbc:mysql://localhost:3306/ml?rewriteBatchedStatements=true";
    private static String user = "root";
    private static String password = "123456";


    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement ps=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(url, user, password);
            String sql = "insert into t_order(id,order_no,create_time,status) values (null,?,'2020-1-19','0')";
            ps = con.prepareStatement(sql);
            long time = System.currentTimeMillis();
            for(int i=0;i<200000;i++){
                ps.setString(1,"ORDER_NO:"+i);
                ps.addBatch();
            }
            ps.executeBatch();
            System.out.println("批量入20万数据，耗费了"+(System.currentTimeMillis()-time)+"ms");
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (ps!=null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (con!=null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

切记要修改连接配置

执行耗费时间：

换算成秒：4988/1000=4.988秒

三种批量插入效率比较

批量插入方式	数据量	效率（秒）
传统循环插入	20万	5998.976秒
Mybatis foreach插入	20万	13.295秒
jdbc批量插入	20万	4.988秒

结论：

由此可见，在百万数据批量插入时，建议用jdbc批量插入方式，效率最高

常见异常：

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (11288953 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3592) ~[mysql-connector-java-5.1.34.jar:5.1.34]
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2417) ~[mysql-connector-java-5.1.34.jar:5.1.34]
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) ~[mysql-connector-java-5.1.34.jar:5.1.34]
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530) ~[mysql-connector-java-5.1.34.jar:5.1.34]
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907) ~[mysql-connector-java-5.1.34.jar:5.1.34]
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1199) ~[mysql-connector-java-5.1.34.jar:5.1.34]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-3.2.0.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-3.2.0.jar:na]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_131]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_131]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_131]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_131]
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) ~[mybatis-3.4.6.jar:3.4.6]
	at com.sun.proxy.$Proxy123.execute(Unknown Source) ~[na:na]
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46) ~[mybatis-3.4.6.jar:3.4.6]
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[mybatis-3.4.6.jar:3.4.6]

这是因为最大连接包太小，修改mysql的max_allowed_packet即可

修改步骤如下：

1. show VARIABLES like '%max_allowed_packet%'; 执行语句查询最大包长度

2. set global max_allowed_packet = 300*1024*1024;执行语句设置最大包长度，改为300M

再次查询，会发现长度变了

max_allowed_packet   314572800

如果修改不成功，在mysql文件下找到my.ini文件，在【mysqlId】下添加一个配置：

max_allowed_packet = 300M

最后重启mysql，再次查询，长度发生变化了，再次执行批量插入，就不会报错了。

参考链接：
https://www.jianshu.com/p/6b3a26d4320e