MySQL重写BatchStatements
想象一下，有一个工具可以自动检测JPA和Hibernate的性能问题。这难道不是很好吗？
好吧，Hypersistence Optimizer就是这样的工具!而且它可以与Spring Boot、Spring Framework、Jakarta EE、Java EE、Quarkus或Play Framework一起使用。
因此，请享受将你的时间花在你喜欢的事情上，而不是在周六晚上修复你的生产系统的性能问题!
简介
在这篇文章中，我们将看到在使用JDBC、JPA或Hibernate时，MySQL rewriteBatchedStatements如何工作。
我第一次研究这个MySQL配置属性是在我写[高性能Java持久性]一书的批处理章节时，那时，我发现这个设置允许通过重写发送到数据库的SQL字符串来批处理普通Statement 。
然而，[MySQL 6 Connector/J的文档]中提到，
对于准备好的语句，服务器端准备好的语句目前不能利用这个重写选项
所以，在很长一段时间里，我错误地认为这个功能不是为了批量处理JDBC准备好的语句。
当我读到MySQL 8.0.30 Connector/J发布说明时，我才意识到文档一直在误导我们。
连接属性
rewriteBatchedStatements的描述已被纠正，删除了服务器端准备好的语句不能利用重写选项的限制。(Bug #34022110)
所以，很明显，rewriteBatchedStatements 是与JDBCPreparedStatements一起工作的，为此，我决定测试这个功能，并将我的发现写在这篇文章中。
在JDBC语句批处理中使用rewriteBatchedStatements
大多数Java开发人员在需要执行INSERT、UPDATE和DELETE语句时，会使用 executeUpdate当需要执行INSERT、UPDATE和DELETE语句时，Statement 接口的方法。
然而，从Java 1.2开始，Statement 接口就提供了addBatch ，我们可以用它来批处理多个语句，以便在调用executeBatch 方法时在一个请求中发送这些语句，如下例所示。
String INSERT = "insert into post (id, title) values (%1$d, 'Post no. %1$d')";
try(Statement statement = connection.createStatement()) {
for (long id = 1; id <= 10; id++) {
statement.addBatch(
String.format(INSERT, id)
);
}
statement.executeBatch();
}
现在，你会认为上面的例子会在一次数据库往返中执行INSERT语句，但如果你通过MySQL JDBC驱动进行调试，你会发现下面的代码块。
if (this.rewriteBatchedStatements.getValue() && nbrCommands > 4) {
return executeBatchUsingMultiQueries(
multiQueriesEnabled,
nbrCommands,
individualStatementTimeout
);
}
updateCounts = new long[nbrCommands];
for (int i = 0; i < nbrCommands; i++) {
updateCounts[i] = -3;
}
int commandIndex = 0;
for (commandIndex = 0; commandIndex < nbrCommands; commandIndex++) {
try {
String sql = (String) batchedArgs.get(commandIndex);
updateCounts[commandIndex] = executeUpdateInternal(sql, true, true);
...
} catch (SQLException ex) {
updateCounts[commandIndex] = EXECUTE_FAILED;
...
}
}
false因为rewriteBatchedStatements ，每个INSERT语句将使用executeUpdateInternal 方法调用单独执行。
因此，即使我们使用了addBatch 和executeBatch ，在默认情况下，MySQL在使用普通的JDBCStatement 对象时仍然会单独执行INSERT语句。
然而，如果我们启用rewriteBatchedStatements JDBC配置属性。
MysqlDataSource dataSource = new MysqlDataSource();
String url = "jdbc:mysql://localhost/high_performance_java_persistence?useSSL=false";
dataSource.setURL(url);
dataSource.setUser(username());
dataSource.setPassword(password());
dataSource.setRewriteBatchedStatements(true);
并调试executeBatch 方法的执行，你会看到，现在，executeBatchUsingMultiQueries 被调用。
if (this.rewriteBatchedStatements.getValue() && nbrCommands > 4) {
return executeBatchUsingMultiQueries(
multiQueriesEnabled,
nbrCommands,
individualStatementTimeout
);
}
而executeBatchUsingMultiQueries 方法将把各个INSERT语句串联成一个StringBuilder ，并运行一个execute 的调用。
StringBuilder queryBuf = new StringBuilder();
batchStmt = locallyScopedConn.createStatement();
JdbcStatement jdbcBatchedStmt = (JdbcStatement) batchStmt;
...
int argumentSetsInBatchSoFar = 0;
for (commandIndex = 0; commandIndex < nbrCommands; commandIndex++) {
String nextQuery = (String) this.query.getBatchedArgs().get(commandIndex);
...
queryBuf.append(nextQuery);
queryBuf.append(";");
argumentSetsInBatchSoFar++;
}
if (queryBuf.length() > 0) {
try {
batchStmt.execute(queryBuf.toString(), java.sql.Statement.RETURN_GENERATED_KEYS);
} catch (SQLException ex) {
sqlEx = handleExceptionForBatch(
commandIndex - 1, argumentSetsInBatchSoFar, updateCounts, ex
);
}
...
}
因此，对于普通的JDBCStatement 批处理，MySQLrewriteBatchedStatements 配置属性将附加当前批处理的语句并在单个数据库往返中执行它们。
在JDBC预处理语句批处理中使用rewriteBatchedStatements
当使用JPA和Hibernate时，你所有的SQL语句都将使用JDBCPreparedStatement 来执行，这是很好的理由。
- 预备语句允许你增加语句缓存的可能性
- 准备好的语句允许你避免SQL注入攻击，因为你绑定了参数值，而不是像我们在之前的
String.format调用中那样注入它们。
然而，由于Hibernate默认不启用JDBC批处理，我们需要提供以下配置属性来激活自动批处理机制。
spring.jpa.properties.hibernate.jdbc.batch_size=10;
spring.jpa.properties.hibernate.order_inserts=true;
spring.jpa.properties.hibernate.order_updates=true;
因此，当持久化10个Post 实体时。
for (long i = 1; i <= 10; i++) {
entityManager.persist(
new Post()
.setId(i)
.setTitle(String.format("Post no. %d", i))
);
}
Hibernate要执行一个JDBC INSERT，正如datasource-proxy的日志条目所说明的。
Type:Prepared, Batch:True, QuerySize:1, BatchSize:10,
Query:["
insert into post (title, id) values (?, ?)
"],
Params:[
(Post no. 1, 1), (Post no. 2, 2), (Post no. 3, 3),
(Post no. 4, 4), (Post no. 5, 5), (Post no. 6, 6),
(Post no. 7, 7), (Post no. 8, 8), (Post no. 9, 9),
(Post no. 10, 10)
]
如果你使用的是
IDENTITY实体标识符策略，Hibernate将无法自动批处理插入语句。
因此，使用默认的MySQL JDBC驱动设置，一条语句被发送到了MySQL数据库服务器。然而，如果你检查数据库服务器的日志，我们可以看到，在语句到达后，MySQL执行每条语句，好像它们是在for-loop中运行的。
Query insert into post (title, id) values ('Post no. 1', 1)
Query insert into post (title, id) values ('Post no. 2', 2)
Query insert into post (title, id) values ('Post no. 3', 3)
Query insert into post (title, id) values ('Post no. 4', 4)
Query insert into post (title, id) values ('Post no. 5', 5)
Query insert into post (title, id) values ('Post no. 6', 6)
Query insert into post (title, id) values ('Post no. 7', 7)
Query insert into post (title, id) values ('Post no. 8', 8)
Query insert into post (title, id) values ('Post no. 9', 9)
Query insert into post (title, id) values ('Post no. 10', 10)
Query commit
因此，在启用rewriteBatchedStatements MySQL JDBC驱动设置后。
dataSource.setRewriteBatchedStatements(true);
当我们重新运行之前插入10个Post 实体的测试案例时，我们可以看到在数据库端执行了以下INSERT语句。
Query insert into post (title, id)
values ('Post no. 1', 1),('Post no. 2', 2),('Post no. 3', 3),
('Post no. 4', 4),('Post no. 5', 5),('Post no. 6', 6),
('Post no. 7', 7),('Post no. 8', 8),('Post no. 9', 9),
('Post no. 10', 10)
Query commit
语句发生变化的原因是，MySQL JDBC驱动程序现在调用了executeBatchWithMultiValuesClause 方法，将成批的INSERT语句改写为单个多值INSERT。
if (!this.batchHasPlainStatements &&
this.rewriteBatchedStatements.getValue()) {
if (getQueryInfo().isRewritableWithMultiValuesClause()) {
return executeBatchWithMultiValuesClause(batchTimeout);
}
...
}
测试时间
对于普通语句，没有必要测试rewriteBatchedStatements ，因为你将使用JDBC、JPA、Hibernate或jOOQ执行的大多数SQL语句都是使用JDBCPreparedStatement 接口完成的。
因此，当运行一个使用批处理大小为100 ，持续时间为60秒，执行5000条post 的测试时，我们得到以下结果。
下面是两种情况下的Dropwizard指标。
Test MySQL batch insert with rewriteBatchedStatements=false
type=TIMER, name=batchInsertTimer, count=55, min=909.9544999999999, max=1743.0735, mean=1072.3787996947426, stddev=128.4560649360703, median=1049.4146, p75=1106.231, p95=1224.2176, p98=1649.8706, p99=1743.0735, p999=1743.0735, mean_rate=0.8612772397894758, m1=0.6330960191792878, m5=0.3192705968508436, m15=0.24209506781664528, rate_unit=events/second, duration_unit=milliseconds
Test MySQL batch insert with rewriteBatchedStatements=true
type=TIMER, name=batchInsertTimer, count=441, min=80.09599999999999, max=565.4343, mean=112.20623474996226, stddev=29.01211110828766, median=103.52319999999999, p75=120.9807, p95=161.3664, p98=173.9123, p99=182.2464, p999=565.4343, mean_rate=7.263224298238385, m1=6.872524588278418, m5=6.547662085190082, m15=6.453339001683109, rate_unit=events/second, duration_unit=milliseconds
显然，MySQLrewriteBatchedStatements 设置提供了一个优势，因为激活这个属性时，总的批执行时间要短得多。
正如MySQL文档中所解释的，有一些注意事项你应该注意到。
Statement.getGeneratedKeys()只有当重写的语句只包括INSERT或REPLACE语句时才有效。在使用JPA和Hibernate时，这并不是一个真正的问题，因为在刷新过程中只有INSERT会被分批处理。- 重写
INSERT ... ON DUPLICATE KEY UPDATE语句可能不会像预期的那样工作，但是，同样，这对JPA和Hibernate来说不是一个问题，因为默认的INSERT不使用ON DUPLICATE KEY UPDATE子句。
结论
虽然MySQL JDBC驱动程序提供rewriteBatchedStatements 的设置已经有很长时间了，但由于文档相当具有误导性，人们并不清楚该属性适用于PreparedStatement 批处理。
因此，如果你的批处理任务是在MySQL上运行，启用rewriteBatchedStatements 设置可能会提供更好的性能。
