目录

 

 

一、场景

二、问题原因

---

一、场景

最近在清洗老数据，为了提高效率，使用了mysql的batch特性：

设置关闭自动提交功能：autoCommit = false

示例代码：

List<Person> persons = ... // get a list of Person objects from somewhere.


String sql = "update people set firstname=? , lastname=? where id=?";

PreparedStatement preparedStatement = null;
try{
    preparedStatement =
            connection.prepareStatement(sql);

    for(Person person : persons) {
        preparedStatement.setString(1, person.getFirstName());
        preparedStatement.setString(2, person.getLastName());
        preparedStatement.setLong  (3, person.getId());

        preparedStatement.addBatch();
    }

    int[] affectedRecords = preparedStatement.executeBatch();
    connection.commit();

}finally {
    if(preparedStatement != null) {
        preparedStatement.close();
    }
}

线上的代码改动了批量提交数目设置：

if (sum % batchSum == 0) {
      prepareStatement.executeBatch();
      connection.commit();
}

 

原本以为是真正的批量更新，观察每次更新条数很慢，怀疑是一条条执行的更新，根据日志观察，并非批量更新。

 

二、问题原因

mysql-connector-java 版本8.0.19

com.mysql.cj.jdbc.ClientPreparedStatement#executeBatchInternal

 @Override
    protected long[] executeBatchInternal() throws SQLException {
        synchronized (checkClosed().getConnectionMutex()) {

            if (this.connection.isReadOnly()) {
                throw new SQLException(Messages.getString("PreparedStatement.25") + Messages.getString("PreparedStatement.26"),
                        MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT);
            }

            if (this.query.getBatchedArgs() == null || this.query.getBatchedArgs().size() == 0) {
                return new long[0];
            }

            // we timeout the entire batch, not individual statements
            int batchTimeout = getTimeoutInMillis();
            setTimeoutInMillis(0);

            resetCancelledState();

            try {
                statementBegins();

                clearWarnings();

                if (!this.batchHasPlainStatements && this.rewriteBatchedStatements.getValue()) {

                    if (((PreparedQuery<?>) this.query).getParseInfo().canRewriteAsMultiValueInsertAtSqlLevel()) {
                        return executeBatchedInserts(batchTimeout);
                    }

                    if (!this.batchHasPlainStatements && this.query.getBatchedArgs() != null
                            && this.query.getBatchedArgs().size() > 3 /* cost of option setting rt-wise */) {
                        return executePreparedBatchAsMultiStatement(batchTimeout);
                    }
                }

                return executeBatchSerially(batchTimeout);
            } finally {
                this.query.getStatementExecuting().set(false);

                clearBatch();
            }
        }
    }

 

mysql代码在执行批量更新的时候，是有条件的，条件不满自就会串行执行。

 

    if (!this.batchHasPlainStatements && this.rewriteBatchedStatements.getValue()) {

                    if (((PreparedQuery<?>) this.query).getParseInfo().canRewriteAsMultiValueInsertAtSqlLevel()) {
                        return executeBatchedInserts(batchTimeout);
                    }

                    if (!this.batchHasPlainStatements && this.query.getBatchedArgs() != null
                            && this.query.getBatchedArgs().size() > 3 /* cost of option setting rt-wise */) {
                        return executePreparedBatchAsMultiStatement(batchTimeout);
                    }
                }

                return executeBatchSerially(batchTimeout);

 

 

 /**
     * Does the batch (if any) contain "plain" statements added by
     * Statement.addBatch(String)?
     * 
     * If so, we can't re-write it to use multi-value or multi-queries.
     */
    protected boolean batchHasPlainStatements = false;

默认this.batchHasPlainStatements = false，rewriteBatchedStatements=false，所以我们必须在jdbc url中添加参数：

&rewriteBatchedStatements=true

而且还发现批量更新还必须符合两个参数相关的条件：

&& this.query.getBatchedArgs() != null
                            && this.query.getBatchedArgs().size() > 3 

所以，mysql默认情况的批量更新并就是真正的批量更新，与参数rewriteBatchedStatements相关，而且mybatis  ExecutorType.BATCH 类型，你在使用的时候，也会碰到类似的问题。