当前位置：首页 > news >正文

MySQL驱动Add Batch优化实现

news 2025/7/18 10:42:00

MySQL 驱动 Add Batch 优化实现

MySQL 驱动会在 JDBC URL 添加 rewriteBatchedStatements 参数时，对 batch 操作进行优化。本文测试各种参数组合的行为，并结合驱动代码简单分析。

batch参数组合行为

useServerPrepStmts 参数

PreparedStatement psmt = connection.prepareStatement("DELETE FROM t_order WHERE  `order_id` = ?");
psmt.setObject(1, 1);
psmt.execute();

开启:
使用服务端预编译，先发送 prepared 语句，再发送 excute 语句

不开启:
mysql 驱动会将占位符填充后，明文下发sql.

比如 DELETE FROM t_order WHERE order_id = ?; 语句

MySQL 驱动会下发 DELETE FROM t_order WHERE order_id = 1;

allowMultiQueries 参数

Statement statement = connection.createStatement();
statement.execute("DELETE FROM t_order WHERE `order_id` = 1;DELETE FROM t_order WHERE `order_id` = 2;");

不开启:
服务端不支持 DELETE FROM t_order WHERE order_id = 1;DELETE FROM t_order WHERE order_id = 2; 这样的批量delete语句

开启:
支持多语句，比如： DELETE FROM t_order WHERE order_id = 1;DELETE FROM t_order WHERE order_id = 2;

rewriteBatchedStatements 参数

PreparedStatement psmt = connection.prepareStatement("DELETE FROM t_order WHERE  `order_id` = ?");
for (int i = 1; i <= 500; i++) {psmt.setObject(1, i);psmt.addBatch();
}
psmt.executeBatch();

不开启:
batch 操作，在 addBatch 时一条条下发参数值。

开启:
在执行 executeBatch 时,将 batch 操作改写后批量下发；改写后的 SQL 比如 DELETE FROM t_order WHERE order_id = 1;DELETE FROM t_order WHERE order_id = 2;…;DELETE FROM t_order WHERE order_id = 500;

useServerPrepStmts+allowMultiQueries 参数

PreparedStatement psmt = connection.prepareStatement("DELETE FROM t_order WHERE  `order_id` = ?;DELETE FROM t_order WHERE  `order_id` = ?;");
psmt.execute();

虽然开启了服务端预编译参数 useServerPrepStmts，但是 MySQL JDBC 驱动会判断预编译 SQL 不支持 allowMultiQueries，会直接转换成客户端预编译，也就时会将占位符赋值后下发到 mysql。

比如对于以下 SQL: DELETE FROM t_order WHERE order_id = ?;DELETE FROM t_order WHERE order_id = ?;

客户端会将占位符填充后发送: DELETE FROM t_order WHERE order_id = 1;DELETE FROM t_order WHERE order_id = 2;

useServerPrepStmts+rewriteBatchedStatements 参数

PreparedStatement psmt = connection.prepareStatement("DELETE FROM t_order WHERE  `order_id` = ?");
for (int i = 1; i < 500; i++) {psmt.setObject(1, i);psmt.addBatch();
}
psmt.executeBatch();

对于 DELETE FROM t_order WHERE order_id = ? 的 batch 语句，

会转换成 DELETE FROM t_order WHERE order_id = ?;DELETE FROM t_order WHERE order_id = ?;多语句下发，但是服务端返回不支持。

然后客户端再使用客户端预编译尝试发送(会先发送 set multi option on 包,执行完再关闭该标识)，然后下发多语句 DELETE FROM t_order WHERE order_id = 1;DELETE FROM t_order WHERE order_id = 2;…;DELETE FROM t_order WHERE order_id = 500;

useServerPrepStmts+allowMultiQueries+rewriteBatchedStatements 参数

PreparedStatement psmt = connection.prepareStatement("DELETE FROM t_order WHERE  `order_id` = ?");
for (int i = 1; i < 500; i++) {psmt.setObject(1, i);psmt.addBatch();
}
psmt.executeBatch();

当执行 batch delete 语句时：

虽然开启了 useServerPrepStmts 预编译参数，但是 MySQL JDBC 驱动会判断预编译 SQL 不支持 allowMultiQueries，会直接转换成客户端预编译，也就将占位符赋值后下发到服务端。

也就是发送 DELETE FROM t_order WHERE order_id = 1;DELETE FROM t_order WHERE order_id = 2;…;DELETE FROM t_order WHERE order_id = 500;

MySQL 驱动代码分析

下面结合驱动代码简单分析两组参数的流程:

1.useServerPrepStmts+rewriteBatchedStatements+allowMultiQueries 参数

// 测试 demo
// jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useSSL=false&allowPublicKeyRetrieval=true&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=512&prepStmtCacheSqlLimit=8000&rewriteBatchedStatements=true&allowMultiQueries=true
PreparedStatement psmt = connection.prepareStatement("DELETE FROM t_order WHERE  `order_id` = ?");
for (int i = 1; i < 500; i++) {psmt.setObject(1, i);psmt.addBatch();
}
psmt.executeBatch();

MySQL驱动处理逻辑：

1.prepareStatement

当执行 prepareStatement() 时，因为开启了 useServerPrepStmts 参数，所以会下发预编译 sql 给 server 端。

PreparedStatement psmt = connection.prepareStatement(sql);

2.addBatch

添加参数即可，不和服务端交互。

3.executeBatch

调用栈:

com.mysql.cj.jdbc.StatementImpl#executeBatchcom.mysql.cj.jdbc.ClientPreparedStatement#executeBatchInternalcom.mysql.cj.jdbc.StatementImpl#statementBeginscom.mysql.cj.ServerPreparedQuery#statementBeginscom.mysql.cj.AbstractQuery#statementBeginscom.mysql.cj.AbstractPreparedQuery#getParseInfocom.mysql.cj.ParseInfo#canRewriteAsMultiValueInsertAtSqlLevel 是否支持insert values改写优化com.mysql.cj.jdbc.ClientPreparedStatement#executePreparedBatchAsMultiStatement 多语句执行优化com.mysql.cj.AbstractQuery#getBatchedArgscom.mysql.cj.AbstractPreparedQuery#computeBatchSize 计算批次大小com.mysql.cj.ServerPreparedQuery#computeMaxParameterSetSizeAndBatchSizecom.mysql.cj.jdbc.ClientPreparedStatement#generateMultiStatementForBatchcom.mysql.cj.jdbc.ConnectionImpl#prepareStatementcom.mysql.cj.jdbc.ConnectionImpl#prepareStatementcom.mysql.cj.jdbc.ServerPreparedStatement#setOneBatchedParameterSetcom.mysql.cj.jdbc.ClientPreparedStatement#executecom.mysql.cj.jdbc.ClientPreparedStatement#checkReadOnlySafeStatementcom.mysql.cj.protocol.a.NativeMessageBuilder#buildComQuerycom.mysql.cj.NativeSession#sendCommand

由于开启了 rewriteBatchedStatements 参数，并且 delelte batch size 大于 3,会通过 executePreparedBatchAsMultiStatement 优化成批量 delete.

// 开启 rewriteBatchedStatements 参数
if (!this.batchHasPlainStatements && this.rewriteBatchedStatements.getValue()) {// batch size > 3,则将 sql 转成 multi deleteif (!this.batchHasPlainStatements && this.query.getBatchedArgs() != null&& this.query.getBatchedArgs().size() > 3 /* cost of option setting rt-wise */) {return executePreparedBatchAsMultiStatement(batchTimeout);}
}

executePreparedBatchAsMultiStatement 方法

计算需要分几个批次下发。正常500条sql一个批次即可。

// 根据上面得到的batch sql的长度，确定分几个批次下发 multi sql;
/*** Computes the optimum number of batched parameter lists to send* without overflowing max_allowed_packet.* * @param numBatchedArgs*            original batch size* @return computed batch size*/
public int computeBatchSize(int numBatchedArgs) {long[] combinedValues = computeMaxParameterSetSizeAndBatchSize(numBatchedArgs);long maxSizeOfParameterSet = combinedValues[0];long sizeOfEntireBatch = combinedValues[1];// 整个 batch sql 的长度不能超长，正常走到这个分支里if (sizeOfEntireBatch < this.maxAllowedPacket.getValue() - this.originalSql.length()) {return numBatchedArgs;}return (int) Math.max(1, (this.maxAllowedPacket.getValue() - this.originalSql.length()) / maxSizeOfParameterSet);
}

计算方法如下：

// 计算 maxSizeOfParameterSet: 每个参数中最大的长度
// sizeOfEntireBatch: batch所有参数长度相加
/*** Computes the maximum parameter set size and the size of the entire batch given* the number of arguments in the batch.*/
@Override
protected long[] computeMaxParameterSetSizeAndBatchSize(int numBatchedArgs) {long sizeOfEntireBatch = 1 + /* com_execute */+4 /* stmt id */ + 1 /* flags */ + 4 /* batch count padding */;long maxSizeOfParameterSet = 0;for (int i = 0; i < numBatchedArgs; i++) {ServerPreparedQueryBindValue[] paramArg = ((ServerPreparedQueryBindings) this.batchedArgs.get(i)).getBindValues();long sizeOfParameterSet = (this.parameterCount + 7) / 8; // for isNullsizeOfParameterSet += this.parameterCount * 2; // have to send typesServerPreparedQueryBindValue[] parameterBindings = this.queryBindings.getBindValues();for (int j = 0; j < parameterBindings.length; j++) {if (!paramArg[j].isNull()) {long size = paramArg[j].getBoundLength();if (paramArg[j].isStream()) {if (size != -1) {sizeOfParameterSet += size;}} else {sizeOfParameterSet += size;}}}sizeOfEntireBatch += sizeOfParameterSet;if (sizeOfParameterSet > maxSizeOfParameterSet) {maxSizeOfParameterSet = sizeOfParameterSet;}}return new long[] { maxSizeOfParameterSet, sizeOfEntireBatch };
}

拼接预编译sql,此时还有?号占位符

((Wrapper) locallyScopedConn.prepareStatement(generateMultiStatementForBatch(numValuesPerBatch))).unwrap(java.sql.PreparedStatement.class);

在这里插入图片描述

得到sql如下：

DELETE FROM t_order WHERE  `order_id` = ?;DELETE FROM t_order WHERE  `order_id` = ?;......

执行 prepareStatement，将上面的批量delete语句执行预编译。

这里逻辑和第一步里的 prepareStatement 类似，也需要判断当前批量delete sql是否支持服务端预编译。

这里可以看到，开启 allowMultiQueries 参数之后，驱动会查找 sql 里是否包含 ; 号，如果包含，不支持服务端预编译。后面会走客户端预编译流程。

在这里插入图片描述

客户端预编译执行批量delete

在这里插入图片描述

设置参数

// 给?号占位符塞值
batchedParamIndex = setOneBatchedParameterSet(batchedStatement, batchedParamIndex, this.query.getBatchedArgs().get(batchCounter++));

执行批量delete语句

在这里插入图片描述

2.useServerPrepStmts+rewriteBatchedStatements 参数

1.下发预编译sql

![[img-20240325200021-9.jpg]]

2.设置 multi statement on

在这里插入图片描述

3.多语句预编译sql,执行返回失败

在这里插入图片描述

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELETE FROM t_order WHERE order_id = ?;DELETE FROM t_order WHERE order_id ’ at line 1

代码就是服务端prepared返回失败，会再用 client Prepare statement 重试

在这里插入图片描述