MySql PreparedStatement executeBatch过慢问题

   在近期工作中，数据库使用到了MySql引擎，在一次数据导入几万条数据，如果一条一条插入，必然性能不佳，顾使用到了JDBC中PreparedStatement的executeBatch方法来批量提交数据，以此提高性能，但结果让我们大吃一惊，性能不升反降，由于服务器在异地，2万条左右的数据，整整提交了20分钟，简直破我的三观，于是就开启了排查之路。

   过程中一直怀疑代码有问题，线程死锁（用到了多线程提交数据），数据库事务过长等等问题，后面才发现rewriteBatchedStatements参数未开启，开启参数后，均值8秒提交完成，最优值可以到5秒（与网络有关），最高值10秒。

    到底为什么这个参数会产生这么大的性能差距？于是引起了我对这个参数的一摊究竟。

   开始书写最简单的测试代码，环境如下：

    OS：MAC 8G内存 256GSSD

    MySql server：本机，version：5.7.11

   JDBC 驱动：5.1.31

   数据量：总共提交100万条数据

   数据列：单列

   执行sql如下：

insert into t_student(name) values(?)

   测试程序如下：

public static void main(String[] args){
        String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&rewriteBatchedStatements=true&useServerPrepStmts=true";
        int batchSize = 0;
        Long l = System.currentTimeMillis();
        executeBatch(url,(batchSize<=0 || batchSize > 60000) ? 60000 : batchSize);
        System.out.println("useServerPrepStmts and rewriteBatchedStatements time:"+(System.currentTimeMillis()-l)+"ms");

        url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&rewriteBatchedStatements=true";
        l = System.currentTimeMillis();
        executeBatch(url,batchSize);
        System.out.println(" rewriteBatchedStatements time:"+(System.currentTimeMillis()-l)+"ms");

        url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true";
        l = System.currentTimeMillis();
        executeBatch(url,batchSize);
        System.out.println("  time:"+(System.currentTimeMillis()-l)+"ms");
    }

    public static void executeBatch(String url,int batchSize){
        Connection connection = null;
        try {
            String sql = "insert into t_student(name) values(?)";
            connection = getConnection(url);
            connection.setAutoCommit(false);
            PreparedStatement pstm = connection.prepareStatement(sql);
            for(int  i = 0 ; i < 1000000 ; i++){
                pstm.setString(1,"li.guohui"+i);
                pstm.addBatch();
                if(batchSize > 0) {
                    if (i % batchSize == 0) {
                        pstm.executeBatch();
                    }
                }
            }
            pstm.executeBatch();
            connection.commit();
        }catch (Exception e){
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            close(connection);
        }

    }

    public static Connection getConnection(String url) throws ClassNotFoundException, SQLException {
        String username = "root";
        String password = "root";
        Class.forName("com.mysql.jdbc.Driver");
        return DriverManager.getConnection(url,username,password);
    }

    public static void close(Connection connection){
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

执行结果如下：

useServerPrepStmts and rewriteBatchedStatements time:  8506ms
rewriteBatchedStatements time:                         5213ms
mysql time:                                            161732ms

从结果一看，一目了然，url中仅开启参数rewriteBatchedStatements时，性能最佳，5秒多些就能处理结束；

WHY？？？？？？

通过tcpdump网络抓包，指令格式（sudo tcpdump -i 10 -vv -nn port 3306 -X）详细说明请看博客，我们可以看到三个参数的不同处理方式：

1、同时开启useServerPrepStmts and rewriteBatchedStatements：

mysql的jdbc连接器采用预处理的方式提交数据，将预处理数据线发送到数据库服务器，然后再发送一个一个的参数数据，一个操作需要发送两次。

2、仅开启rewriteBatchedStatements参数，抓包结果如下：

mysql的jdbc连接器将需要插入的值拼接在插入语句中，多值一次发送：“insert into table(colName) values('a'),('b').....”形式；这样数据只需要一次提交数据到数据库服务器；

3、未开启useServerPrepStmts and rewriteBatchedStatements参数，抓包结果如下：

从抓包结果，可以看出，mysql的jdbc连接器，将100万条数分100万次提交到数据库服务器，这可想而知，这是多么的慢，不仅仅数据提交，tcp连接等等等都会消耗一定时间。

至此，我们大概明白了为什么executeBatch如此慢的原因，总结如下：

a、开启rewriteBatchedStatements而未开启useServerPrepStmts参数，mysql连接器以字符数值拼接多值sql，一次发送到数据库服务器；

b、同时rewriteBatchedStatements和useServerPrepStmts参数，MySQL连接器以预编译的方式将sql和值分开发送，这种方式需要注意占位符过多的情况，一次MySQL最多的占位符为65535个，即每次执行executeBatch时，数据个数不能超过65535个（与数据条数、提交的表字段个数有关），否则会出现以下错误：

java.sql.SQLException: Prepared statement contains too many placeholders
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1094)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4226)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
	at com.mysql.jdbc.ServerPreparedStatement.serverPrepare(ServerPreparedStatement.java:1623)
	at com.mysql.jdbc.ServerPreparedStatement.<init>(ServerPreparedStatement.java:402)
	at com.mysql.jdbc.ServerPreparedStatement.prepareBatchedInsertSQL(ServerPreparedStatement.java:3037)
	at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1665)
	at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1377)

c、什么都不开启，MySQL连接器默认一条一条提交数据，多次与数据库服务器交互。

**&&性能比较（在多条数据提交时）： 开启rewriteBatchedStatements  > 同时开启rewriteBatchedStatements和useServerPrepStmts > 未开启

接下来，我们再来看看rewriteBatchedStatements参数的源码处理过程：

addBatch方法都做了啥？

往批处理维护的缓存数组中加入需要插入数据库的值：

执行executeBatch方法时，就开始将batchedArgs里的数据通过连接socket tcp方式发送到数据库服务器：

但是从源码中注意到，如果一次提交的数据少于3条，那么批量提交是不生效的，该连接器生效是从4.1.0版本以上支持。

MySQL其它连接器参数说明：

请看官方文档：https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html