JDBC操作MySQL(2)—批量操作

时间:2021-5-25 作者:qvyue

在数据迁移、批量等业务场景中,经常会需要批量insert、update与delete,如果逐条操作,在数据量较大时性能往往不能满足要求,熟悉JDBC工程师首先会想到batch接口,使用方法如下:

    public static  void batchInsert() throws SQLException{
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
        connection.setAutoCommit(false);
        PreparedStatement statement = connection.prepareStatement("insert into test(id,name) values(?,?)");

        long begin = System.currentTimeMillis();
        for(int i=0;i

不过这段代码在操作MySQL时其实并没有真正生效。查看MySQL服务器通用日志:

 1 Connect  root@localhost on test
            1 Query /* mysql-connector-java-5.1.46 ( Revision: 9cc87a48e75c2d2e87c1a293b2862ce651cb256e ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
            1 Query SET NAMES latin1
            1 Query SET character_set_results = NULL
            1 Query SET autocommit=1
            1 Query SET autocommit=0
            1 Query select @@session.tx_read_only
            1 Query insert into test(id,name) values(0,'hello')
            1 Query insert into test(id,name) values(1,'hello')
            1 Query insert into test(id,name) values(2,'hello')
            1 Query insert into test(id,name) values(3,'hello')
            1 Query insert into test(id,name) values(4,'hello')
            1 Query commit

可以看到MySQL其实仍然还是一条条顺序执行的insert,与非batch方式是一样的。那么具体问题出在哪儿呢?老办法,直接翻MySQL驱动中com.mysql.jdbc.PreparedStatment的源码。

public int[] executeBatch() throws SQLException {
        synchronized (checkClosed().getConnectionMutex()) {
        
            if (this.connection.isReadOnly()) {
                throw new SQLException(Messages.getString("PreparedStatement.25") //$NON-NLS-1$
                        + Messages.getString("PreparedStatement.26"), //$NON-NLS-1$
                        SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
            }

            if (this.batchedArgs == null || this.batchedArgs.size() == 0) {
                return new int[0];
            }

            // we timeout the entire batch, not individual statements
            int batchTimeout = this.timeoutInMillis;
            this.timeoutInMillis = 0;
        
            resetCancelledState();
            
            try {
                statementBegins();
                
                clearWarnings();

                if (!this.batchHasPlainStatements
                        && this.connection.getRewriteBatchedStatements()) {
                    
                    
                    if (canRewriteAsMultiValueInsertAtSqlLevel()) {
                        return executeBatchedInserts(batchTimeout);
                    }
                    
                    if (this.connection.versionMeetsMinimum(4, 1, 0) 
                            && !this.batchHasPlainStatements
                            && this.batchedArgs != null 
                            && this.batchedArgs.size() > 3 /* cost of option setting rt-wise */) {
                        return executePreparedBatchAsMultiStatement(batchTimeout);
                    }
                }

                return executeBatchSerially(batchTimeout);
            } finally {
                this.statementExecuting.set(false);
                
                clearBatch();
            }
        }
    }
Insert操作

由上代码可以看出,MySQL驱动默认是不支持批量操作,但如果this.connection.getRewriteBatchedStatements()为true时(通过设置参数rewriteBatchedStatements=true),则会进入批量提交逻辑分支。
我们增加该参数,然后再执行,发现执行速度大大提高

public static  void batchInsertWithRewriteBatchedStatements() throws SQLException{
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true", "root", "123456");
        connection.setAutoCommit(false);
        PreparedStatement statement = connection.prepareStatement("insert into test(id,name) values(?,?)");

        long begin = System.currentTimeMillis();
        for(int i=0;i

通过我们看MySQL服务器端日志:

            2 Connect   root@localhost on test
            2 Query /* mysql-connector-java-5.1.46 ( Revision: 9cc87a48e75c2d2e87c1a293b2862ce651cb256e ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
            2 Query SET NAMES latin1
            2 Query SET character_set_results = NULL
            2 Query SET autocommit=1
            2 Query SET autocommit=0
            2 Query select @@session.tx_read_only
            2 Query insert into test(id,name) values(0,'hello'),(1,'hello'),(2,'hello'),(3,'hello'),(4,'hello')
            2 Query commit

可见MySQL驱动其实只把insert转变成了insert values(),(),()形式,然后一次性发送到MySQL服务器执行。

我们继续看canRewriteAsMultiValueInsertAtSqlLevel()方法,该方法真正的实现在canRewrite方法中,其代码如下:

protected static boolean canRewrite(String sql, boolean isOnDuplicateKeyUpdate, int locationOfOnDuplicateKeyUpdate, int statementStartPos) {
       // Needs to be INSERT, can't have INSERT ... SELECT or
       // INSERT ... ON DUPLICATE KEY UPDATE with an id=LAST_INSERT_ID(...)

       boolean rewritableOdku = true;

       if (isOnDuplicateKeyUpdate) {
           int updateClausePos = StringUtils.indexOfIgnoreCase(
                   locationOfOnDuplicateKeyUpdate, sql, " UPDATE ");

           if (updateClausePos != -1) {
               rewritableOdku = StringUtils
                       .indexOfIgnoreCaseRespectMarker(updateClausePos,
                               sql, "LAST_INSERT_ID", ""'`", ""'`",
                               false) == -1;
           }
       }

       return StringUtils
               .startsWithIgnoreCaseAndWs(sql, "INSERT",
                       statementStartPos)
               && StringUtils.indexOfIgnoreCaseRespectMarker(
                       statementStartPos, sql, "SELECT", ""'`",
                       ""'`", false) == -1 && rewritableOdku;
   }

由此方法可以看出,并不是所有insert都可以进行多值插入批量优化的,INSERT … SELECT 以及INSERT … ON DUPLICATE就不支持。

执行效率对比

插入1w条数据,顺序插入与批量插入时间对比

batchInsert span time=1879ms
batchInsertWithRewriteBatchedStatements span time=199ms

结论:

可见使用批量方式insert,可以大大提高执行效率,MySQL对此做了真正的优化,本地测试可以提高10倍左右。
对于批量插入,网上很多推荐直接使用insert into table values(),()…,尤其是使用MyBatis时,建议使用foreach进行SQL拼接,例如:

    
        insert into test(id, name) values
        
            (#{item.id},
             #{item.name})
        

但这种方式有个问题,如果插入的条数较多,可能会导致报文超过MySQL最大允许报文长度时,MySQL驱动会直接抛出异常

    Exception in thread "main" com.mysql.jdbc.PacketTooBigException: Packet for query is too large (16888926 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3681)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2512)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
    at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)
    at com.cmbc.dap.dao.test.MysqlBatchTest.batchInsertWithMultiValues(MysqlBatchTest.java:125)
    at com.cmbc.dap.dao.test.MysqlBatchTest.main(MysqlBatchTest.java:18)

MySQL报文允许最大长度可以通过以下命令查看
mysql> show VARIABLES like ‘%max_allowed_packet%’;
+——————–+———–+
| Variable_name | Value |
+——————–+———–+
| max_allowed_packet | 167772160 |
+——————–+———–+
1 row in set (0.00 sec)

因此不建议开发时通过这种方式进行批量操作,正确安全的方式还是通过JDBC batch接口进行操作,MySQL驱动会根据报文最大长度自动拆分包。

如果使用Mybatis,可指定ExcutorType为Batch模式来完成批量操作。

Update、Delete操作

对于update、delete类型SQL,当SQL数目大于3时,通过多条语句用分号;相隔,然后一起发送MySQL服务器的方式实现批量执行。具体代码可见generateMultiStatementForBatch方法:

    private String generateMultiStatementForBatch(int numBatches) throws SQLException {
        synchronized (checkClosed().getConnectionMutex()) {
            StringBuffer newStatementSql = new StringBuffer((this.originalSql
                    .length() + 1) * numBatches);
                    
            newStatementSql.append(this.originalSql);
    
            for (int i = 0; i 

写代码亲测:

public static  void batchUpdate() throws SQLException{
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
        connection.setAutoCommit(false);
        
        PreparedStatement statement = connection.prepareStatement("update test set name= ? where id =?");
        long begin = System.currentTimeMillis();
        for(int i=0;i

对应的后台MySQL服务器端通用日志:

            190104 13:12:24     1 Connect   root@localhost on test
            1 Query /* mysql-connector-java-5.1.46 ( Revision: 9cc87a48e75c2d2e87c1a293b2862ce651cb256e ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
            1 Query SET NAMES latin1
            1 Query SET character_set_results = NULL
            1 Query SET autocommit=1
            1 Query SET autocommit=0
190104 13:12:25     1 Query select @@session.tx_read_only
            1 Query update test set name= 'hello' where id =0
190104 13:12:26     1 Query select @@session.tx_read_only
            1 Query update test set name= 'hello' where id =1
            1 Query select @@session.tx_read_only
            1 Query update test set name= 'hello' where id =2
190104 13:12:27     1 Query select @@session.tx_read_only
            1 Query update test set name= 'hello' where id =3
190104 13:12:28     1 Query select @@session.tx_read_only
            1 Query update test set name= 'hello' where id =4
            1 Query commit
            2 Connect   root@localhost on test
            2 Query /* mysql-connector-java-5.1.46 ( Revision: 9cc87a48e75c2d2e87c1a293b2862ce651cb256e ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@tx_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
            2 Query SET NAMES latin1
            2 Query SET character_set_results = NULL
            2 Query SET autocommit=1
            2 Query SET autocommit=0
            2 Query select @@session.tx_read_only
            2 Query select @@session.tx_read_only
            2 Query update test set name= 'hello' where id =0;
190104 13:12:29     2 Query update test set name= 'hello' where id =1;
190104 13:12:30     2 Query update test set name= 'hello' where id =2;
            2 Query update test set name= 'hello' where id =3;
190104 13:12:31     2 Query update test set name= 'hello' where id =4
190104 13:12:32     2 Query commit

由上可见,这种多条SQL一起发送到MySQL服务器后,依然是一条条执行,相比客户端顺序执行,仅仅是节省了多次的网络开销。

执行效率对比

插入1w条数据,顺序插入与批量插入时间对比

batchUpdate span time=63192ms
batchUpdateWithRewriteBatchedStatements span time=60946ms

结论:

可见,对于update、delete,MySQL的批量操作其实是一种”伪”批量,性能有提升,但非常有限。

综上,可看出MySQL对批量insert做了很多优化,所以对于批量插入,强烈建议通过JDBC Batch进行操作,但update与delete批量操作则无太大提升。

声明:本文内容由互联网用户自发贡献自行上传,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任。如果您发现有涉嫌版权的内容,欢迎发送邮件至:qvyue@qq.com 进行举报,并提供相关证据,工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。