用html做卖珠宝的网站全网品牌推广公司
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);
相关调用栈如下：
com.mysql.cj.jdbc.ConnectionImpl#prepareStatementcom.mysql.cj.jdbc.ConnectionImpl#canHandleAsServerPreparedStatement1.判断当前sql是否支持服务端预编译com.mysql.cj.jdbc.ServerPreparedStatement#getInstancecom.mysql.cj.jdbc.ServerPreparedStatement#serverPrepare2.下发预编译sqlcom.mysql.cj.ServerPreparedQuery#serverPreparecom.mysql.cj.protocol.a.NativeMessageBuilder#buildComStmtPreparecom.mysql.cj.NativeSession#sendCommand发包com.mysql.cj.protocol.a.NativeProtocol#readcom.mysql.cj.protocol.a.ColumnDefinitionReader#unpackField
1.开启 useServerPrepStmts 参数(emulateUnsupportedPstmts 参数默认就为true)，会再根据 canHandleAsServerPreparedStatement() 判断当前 sql 是否支持服务端预编译。
![![[img-20240325200018-1.jpg]]](https://img-blog.csdnimg.cn/direct/63234cc329ff482baaaa2976aa2556ff.png)
2.canHandleAsServerPreparedStatement()方法会通过 StringUtils._canHandleAsServerPreparedStatementNoCache() 方法检测 sql 是否可以支持服务端预编译。
com.mysql.cj.jdbc.ServerPreparedStatement#getInstance发送预编译 sql
![![[img-20240325200019-3.jpg]]](https://img-blog.csdnimg.cn/direct/23b9a287bcc04e1b84f35495c4d613ee.png)
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]]](https://img-blog.csdnimg.cn/direct/2d5259c9d9c14263b093511716315ef0.png)
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 重试
4.client Prepare statement 重新批量delete 语句
