在数据迁移、批量等业务场景中，经常会需要批量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<COUNT;i++){
statement.setInt(1, i);
statement.setString(2, "hello");
statement.addBatch();
}
statement.executeBatch();
connection.commit();
long end = System.currentTimeMillis();
System.out.println("batchInsert span time="+(end-begin)+ "ms");
不过这段代码在操作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<COUNT;i++){
statement.setInt(1, i);
statement.setString(2, "hello");
statement.addBatch();
}
statement.executeBatch();
connection.commit();
long end = System.currentTimeMillis();
System.out.println("batchInsertWithRewriteBatchedStatements span time="+(end-begin)+ "ms");
}
通过我们看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 id="insertBatch" parameterType="java.util.List">
insert into test(id, name) values
<foreach collection="list" item="item" index="index" separator=",">
(#{item.id},
#{item.name})
</foreach>
</insert>
但这种方式有个问题，如果插入的条数较多，可能会导致报文超过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 < numBatches - 1; i++) {
newStatementSql.append(';');
newStatementSql.append(this.originalSql);
}
return newStatementSql.toString();
}
}
写代码亲测:
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<COUNT;i++){
statement.setString(1, "hello");
statement.setInt(2, i);
statement.execute();
}
connection.commit();
long end = System.currentTimeMillis();
System.out.println("batchUpdate span time="+(end-begin)+ "ms");
}
public static void batchUpdateWithRewriteBatchedStatements() throws SQLException{
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true", "root", "123456");
connection.setAutoCommit(false);
PreparedStatement statement = connection.prepareStatement("update test set name= ? where id =?");
long begin = System.currentTimeMillis();
for(int i=0;i<COUNT;i++){
statement.setString(1, "hello");
statement.setInt(2, i);
statement.addBatch();
}
statement.executeBatch();
connection.commit();
long end = System.currentTimeMillis();
System.out.println("batchUpdateWithRewriteBatchedStatements span time="+(end-begin)+ "ms");
}
对应的后台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批量操作则无太大提升。
