旅游网站开发的流程,手机app软件,制作网页的要素,深圳有做网站的公司660元MySQL 驱动 Add Batch 优化实现
MySQL 驱动会在 JDBC URL 添加 rewriteBatchedStatements 参数时#xff0c;对 batch 操作进行优化。本文测试各种参数组合的行为#xff0c;并结合驱动代码简单分析。
batch参数组合行为
useServerPrepStmts 参数
PreparedStatement psmt…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;
useServerPrepStmtsallowMultiQueries 参数
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;
useServerPrepStmtsrewriteBatchedStatements 参数
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;
useServerPrepStmtsallowMultiQueriesrewriteBatchedStatements 参数
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.useServerPrepStmtsrewriteBatchedStatementsallowMultiQueries 参数
// 测试 demo
// jdbc:mysql://localhost:3306/test?serverTimezoneUTCuseSSLfalseallowPublicKeyRetrievaltrueuseServerPrepStmtstruecachePrepStmtstrueprepStmtCacheSize512prepStmtCacheSqlLimit8000rewriteBatchedStatementstrueallowMultiQueriestrue
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#unpackField1.开启 useServerPrepStmts 参数(emulateUnsupportedPstmts 参数默认就为true)会再根据 canHandleAsServerPreparedStatement() 判断当前 sql 是否支持服务端预编译。 2.canHandleAsServerPreparedStatement()方法会通过 StringUtils._canHandleAsServerPreparedStatementNoCache() 方法检测 sql 是否可以支持服务端预编译。
com.mysql.cj.jdbc.ServerPreparedStatement#getInstance 发送预编译 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.useServerPrepStmtsrewriteBatchedStatements 参数
1.下发预编译sql 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 语句
