網(wǎng)上很多文章，都說MySQL驅(qū)動并沒有實現(xiàn)"真正的"batchUpdate，執(zhí)行的時候還是一條一條按順序?qū)QL發(fā)送到MySQL服務(wù)器，其實這是錯誤的。

先貼一段源碼(基于MySQL 5.1.40驅(qū)動)，執(zhí)行batchUpdate的時候最終執(zhí)行如下方法:executeBatchInternal

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

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

        if (this.batchedArgs == null || this.batchedArgs.size() == 0) {
            return new long[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();
        }
    }
}

為了測試網(wǎng)上文章說法的正誤，本地測試寫了一個batch批量執(zhí)行，跟蹤源碼的時候發(fā)現(xiàn)最后進入到

return executeBatchSerially(batchTimeout);

該方法的javadoc 如是說：

Executes the current batch of statements by executing them one-by-one.

executeBatchSerially核心源碼如下：

for (this.batchCommandIndex = 0; this.batchCommandIndex < nbrCommands; this.batchCommandIndex++) {
    Object arg = this.batchedArgs.get(this.batchCommandIndex);

    try {
        if (arg instanceof String) {
            updateCounts[this.batchCommandIndex] = executeUpdateInternal((String) arg, true, this.retrieveGeneratedKeys);

            // limit one generated key per OnDuplicateKey statement
            getBatchedGeneratedKeys(this.results.getFirstCharOfQuery() == 'I' && containsOnDuplicateKeyInString((String) arg) ? 1 : 0);
        } else {
            BatchParams paramArg = (BatchParams) arg;
            //核心代碼，for循環(huán)執(zhí)行每一條SQL
            updateCounts[this.batchCommandIndex] = executeUpdateInternal(paramArg.parameterStrings, paramArg.parameterStreams,
                    paramArg.isStream, paramArg.streamLengths, paramArg.isNull, true);

            // limit one generated key per OnDuplicateKey statement
            getBatchedGeneratedKeys(containsOnDuplicateKeyUpdateInSQL() ? 1 : 0);
        }
    } catch (SQLException ex) {
        updateCounts[this.batchCommandIndex] = EXECUTE_FAILED;

        if (this.continueBatchOnError && !(ex instanceof MySQLTimeoutException) && !(ex instanceof MySQLStatementCancelledException)
                && !hasDeadlockOrTimeoutRolledBackTx(ex)) {
            sqlEx = ex;
        } else {
            long[] newUpdateCounts = new long[this.batchCommandIndex];
            System.arraycopy(updateCounts, 0, newUpdateCounts, 0, this.batchCommandIndex);

            throw SQLError.createBatchUpdateException(ex, newUpdateCounts, getExceptionInterceptor());
        }
    }
}

通過代碼分析，也確實是一條一條SQL執(zhí)行，而不是把batch的SQL發(fā)送到服務(wù)器

但是

重點來了，執(zhí)行executeBatchSerially是有條件的，再次貼一下源碼：

if (!this.batchHasPlainStatements && this.connection.getRewriteBatchedStatements()) {
    ...
}

return executeBatchSerially(batchTimeout);

也就是說，如果沒做任何配置，默認(rèn)情況下if條件是進不去的，會直接執(zhí)行if塊后邊的

return executeBatchSerially(batchTimeout);

那，我們來看一下if條件是什么:

!this.batchHasPlainStatements && this.connection.getRewriteBatchedStatements()

batchHasPlainStatements默認(rèn)初始化就是false，可以不用管，重點是connection.getRewriteBatchedStatements()。這個是Connection的一個參數(shù)rewriteBatchedStatements，會在讀取jdbcUrl的時候讀取進來：jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true

我們將jdbcUrl添加一個rewriteBatchedStatements試試，即變成：jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&rewriteBatchedStatements=true

這時候就會進if塊了。進入if塊之后，再根據(jù)執(zhí)行的是insert 還是update、 delete，會走不同方法

• 如果是insert語句，滿成條件情況下，會整合成形如："insert into xxx_table values (xx),(yy),(zz)..."這樣的語句
• 如果是update\delete語句，滿成條件情況下，會整合成形如："update t set … where id = 1; update t set … where id = 2; update t set … where id = 3 ..."這樣的語句

然后分批次發(fā)送給MySQL(會有一次發(fā)送的package大小限制，所以需要拆分批次)

int maxAllowedPacket = this.connection.getMaxAllowedPacket();

if (!this.batchHasPlainStatements && this.connection.getRewriteBatchedStatements()) {
    //insert會在這里進行再次判斷
    if (canRewriteAsMultiValueInsertAtSqlLevel()) {
        return executeBatchedInserts(batchTimeout);
    }
    //update、delete會在這里進行再次判斷
    //1. mysql版本>=4.1.0
    //2. batchHasPlainStatements為false
    //3. batch的數(shù)量>3
    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);

在這里總結(jié)一下，如果想要達到MySQL真正batchUpdate效果，需要有以下幾個條件：

1. 需要在jdbcUrl后添加參數(shù)rewriteBatchedStatements=true
2. this.batchHasPlainStatements 為false
3. 如果是update \ delete 語句，還需要mysql版本>=4.1.0，并且batch的數(shù)量>3

因此，如果可能的情況下，請在jdbcUrl后添加參數(shù)rewriteBatchedStatements=true，盡可能利用上MySQL給我們提供的便利，提高性能。