Statement batch

我们可以看到 Statement 和 PreparedStatement 为我们提供的批次执行 sql 操作

JDBC 引入上述 batch 功能的主要目的，是加快对客户端SQL的执行和响应速度，并进而提高数据库整体并发度，而 jdbc batch 能够提高对客户端SQL的执行和响应速度,其主要原理有：

• 减少了JDBC客户端和数据库服务器之间网络传输的开销：使用 batch 功能前，每提交一个SQL，都需要一次网络IO开销，且提交后需要等待服务端返回结果后，才能提交下一个SQL；而使用 batch 功能后，客户端的多个SQL是一起提交给服务器的，只涉及到一次网络IO开销（single database round trip），其示意图如下：

• 当batch底层使用的是静态SQL并参数化执行时(JAVA中一般是使用类java.sql.PreparedStatement 来参数化执行静态SQL)，数据库服务器可以只做一次解析：利用对参数化机制的支持，数据库服务器仅需要对 PreparedStatement 做一次解析（sql parse），即可传入不同参数执行该 batch 中所有的 SQL;

• 网上有个帖子，详细对比了不同场景下，不同数据库的插入和更新性能的差异，可以看出，ORACLE/PG/MYSQL 使用 batch 功能后，性能都有了3-5被的提高：

现在大家用的都是 PreparedStatement 了
对于 select update insert delete 来说、select 是用不上这个批次方法的
我们看到 executeBatch 返回的是 int[] 类型
不同 db 可能返回的值或者逻辑是不一样的

关于 rewriteBatchedStatements 参数

该参数是 mysql 专属的

https://stackoverflow.com/questions/2993251/jdbc-batch-insert-performance

里面有说到关于如何提高 mysql 批量 insert 的效率
useServerPrepStmts 是作用于 mysql 服务端的
rewriteBatchedStatements 是作用于mysql 客户端的、也就是我们的应用程序
那么它发挥了什么的作用？

该参数的意思就是重写用户的 sql 已提高批量 update 的速率。

可以发现是将批量执⾏的⼀组 sql Insert 语句,改写为一条 batched 语句 insert into tableA (colA,colB) values (colA-value1,colB-value1),(colA-value2,colB-value2),(colA-value3,colB-value3), 并通过一次请求发送给数据库服务器的，也就是说此时 mysql 使用了批量插入功能；
对于批量
对于增删改

• batchInsert(10 records) 会被改写为 "insert into t (…) values (…), (…), (…)” 并一次性提交；
  如果不能被改写为 “multi-values”, 则会改写为多个;分割的sql语句并一次性提交：语句 “INSERT INTO TABLE(col1) VALUES (?) ON DUPLICATE KEY UPDATE col2=?” 与变量 [1,2] 和 [2,3]，会被改写为 “INSERT INTO TABLE(col1) VALUES (1) ON DUPLICATE KEY UPDATE col2=2;INSERT INTO TABLE(col1) VALUES (3) ON DUPLICATE KEY UPDATE col2=4” 并一次性提交
• batchDelete(10 records) 会被改写为 "delete from t where id = 1; delete from t where id = 2; delete from t where id = 3;…."并一次性提交；
• batchUpdate(10 records) 会被改写为 “update t set… where id = 1; update t set… where id = 2; update t set… where id = 3…” 并一次性提交；

对于其他数据库比如 oracle 来说、默认是开启了这种批量优化的。比如现在 insert (1,1)(2,2)(3,3)… 到数据

源码

@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 statementsint batchTimeout = getTimeoutInMillis();setTimeoutInMillis(0);resetCancelledState();try {statementBegins();clearWarnings();if (!this.batchHasPlainStatements && this.rewriteBatchedStatements.getValue()) {if (getQueryInfo().isRewritableWithMultiValuesClause()) {return executeBatchWithMultiValuesClause(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();}}
}

看到 executeBatchSerially 方法

for (batchCommandIndex = 0; batchCommandIndex < nbrCommands; batchCommandIndex++) {((PreparedQuery<?>) this.query).setBatchCommandIndex(batchCommandIndex);Object arg = this.query.getBatchedArgs().get(batchCommandIndex);try {if (arg instanceof String) {updateCounts[batchCommandIndex] = executeUpdateInternal((String) arg, true, this.retrieveGeneratedKeys);// limit one generated key per OnDuplicateKey statementgetBatchedGeneratedKeys(this.results.getFirstCharOfQuery() == 'I' && containsOnDuplicateKeyInString((String) arg) ? 1 : 0);} else {QueryBindings<?> queryBindings = (QueryBindings<?>) arg;updateCounts[batchCommandIndex] = executeUpdateInternal(queryBindings, true);// limit one generated key per OnDuplicateKey statementgetBatchedGeneratedKeys(containsOnDuplicateKeyUpdateInSQL() ? 1 : 0);}} catch (SQLException ex) {updateCounts[batchCommandIndex] = EXECUTE_FAILED;if (this.continueBatchOnError && !(ex instanceof MySQLTimeoutException) && !(ex instanceof MySQLStatementCancelledException)&& !hasDeadlockOrTimeoutRolledBackTx(ex)) {sqlEx = ex;} else {long[] newUpdateCounts = new long[batchCommandIndex];System.arraycopy(updateCounts, 0, newUpdateCounts, 0, batchCommandIndex);throw SQLError.createBatchUpdateException(ex, newUpdateCounts, this.exceptionInterceptor);}}
}

确实是一条条插入到数据库中的

但是我们认真看看、调用这个函数之前、还有其他出口

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

this.rewriteBatchedStatements.getValue() 这个我们说过的参数
batchHasPlainStatements 默认就是 false 不用管

executeBatchWithMultiValuesClause 做的就是 insert 的时候多 values
executePreparedBatchAsMultiStatement 做的就是 ; 分割的多个 sql 语句、比如 delete 、update 或者是 insert 无法多个 valuse 的

Mysql 这里还涉及到另外的一个参数 max_allowed_packet 代表一次的网络包最大是多少

看到在 mysql 8.0 这边的大小是 32M 左右

Spring JdbcTemplate

直接 JDBC

//获取要设置的Arp基准的List后,插入Arp基准表中    public boolean insertArpStandardList(List<ArpTable> list) {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;//MySql的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入。//优化插入性能，用JDBC的addBatch方法，但是注意在连接字符串加上面写的参数。//例如： String connectionUrl="jdbc:mysql://192.168.1.100:3306/test?rewriteBatchedStatements=true" ;String sql = "insert into arp_standard(guid, devicebrand, devicename, deviceip, ipaddress, " +"macaddress, createtime) values(?,?,?,?,?,?,?)";try{conn = DBConnection.getConnection();ps = conn.prepareStatement(sql);//优化插入第一步设置手动提交  conn.setAutoCommit(false); int len = list.size();for(int i=0; i<len; i++) {ps.setString(1, list.get(i).getGuid());ps.setString(2, list.get(i).getDeviceBrand());ps.setString(3, list.get(i).getDeviceName());ps.setString(4, list.get(i).getDeviceIp());ps.setString(5, list.get(i).getIpAddress());ps.setString(6, list.get(i).getMacAddress());ps.setString(7, list.get(i).getCreateTime());//if(ps.executeUpdate() != 1) r = false;    优化后，不用传统的插入方法了。//优化插入第二步插入代码打包，等一定量后再一起插入。ps.addBatch(); //if(ps.executeUpdate() != 1)result = false;//每200次提交一次 if((i!=0 && i%200==0) || i==len-1){//可以设置不同的大小；如50，100，200，500，1000等等  ps.executeBatch();  //优化插入第三步提交，批量插入数据库中。conn.commit();  ps.clearBatch();//提交后，Batch清空。}}} catch (Exception e) {System.out.println("MibTaskPack->getArpInfoList() error:" + e.getMessage());return false;   //出错才报false} finally {DBConnection.closeConection(conn, ps, rs);}return true;}

https://mdnice.com/writing/fc6d8a16525d447bbcae4c5be34215a0
https://stackoverflow.com/questions/26307760/mysql-and-jdbc-with-rewritebatchedstatements-true
https://letcoding.com/2024/01/03/%E5%A5%BD%E5%A5%BD%E7%A0%81%E4%BB%A3%E7%A0%81%E5%90%96/JAVA/MyBatis/JDBC%E8%BF%9E%E6%8E%A5%E5%8F%82%E6%95%B0%E3%80%90rewriteBatchedStatements%E3%80%91%E8%AF%A6%E8%A7%A3/
https://www.cnblogs.com/lizm166/p/7890168.html