MySQL驱动Add Batch优化实现

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();

不开启: