【译】MySQL rewriteBatchedStatements 的属性设置转载
介绍
在本文中，我们将了解 MySQL rewriteBatchedStatements 在使用 JDBC、JPA 或 Hibernate 时是如何工作的。
我在编写《高性能 Java 持久性》一书的批处理章节时首先研究了这个 MySQL 配置属性，然后，我发现这个设置允许Statement通过重写发送到数据库的 SQL 字符串来进行纯批处理。
但是，MySQL 6 Connector/J 文档提到：
对于prepared statements，服务器端prepared statements目前无法利用此重写选项
因此，很长一段时间以来，我错误地认为此功能不适用于批处理 JDBC 准备语句。
当我阅读MySQL 8.0.30 Connector/J 发行说明时，我意识到文档误导了我们：
已更正连接属性的描述
rewriteBatchedStatements，消除了服务器端准备好的语句无法利用重写选项的限制。（错误号 34022110）
因此，显然，它rewriteBatchedStatements正在使用 JDBC PreparedStatement，因此，我决定测试此功能并在本文中写下我的发现。
将 rewriteBatchedStatements 与 JDBC 语句批处理一起使用
大多数 Java 开发人员在必须执行 INSERT、UPDATE 和 DELETE 语句时使用接口的executeUpdate方法。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;
...
}
}
因为rewriteBatchedStatementsis ，每个 INSERT 语句将使用方法调用false单独执行。executeUpdateInternal
因此，即使我们使用addBatchand ，默认情况下，MySQL 在使用普通 JDBC对象executeBatch时仍会单独执行 INSERT 语句。Statement
但是，如果我们启用rewriteBatchedStatementsJDBC 配置属性：
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 语句连接到 aStringBuilder并运行单个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配置属性将附加当前批处理的语句并在单个数据库往返中执行它们。
将 rewriteBatchedStatements 与 JDBC PreparedStatement 批处理一起使用
使用 JPA 和 Hibernate 时，你的所有 SQL 语句都将使用 JDBC 执行PreparedStatement，这是有充分理由的：
- 准备好的语句允许你增加语句缓存的可能性
- 准备好的语句允许你避免 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 循环中运行一样：
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
因此，启用rewriteBatchedStatementsMySQL JDBC Driver 设置后：
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接口完成的。
post因此，当使用 60 秒的批处理大小运行插入 5000 条记录的测试时100，我们得到以下结果：
以下是这两种情况的 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子句。
原文作者：Vlad Mihalcea
