MySQL 重写声明配置属性

原创 X丶 2022-10-24

524

介绍

在本文中，我们将看到MySQL重写巴奇语句在使用JDBC，JPA或休眠时如何工作。

当我编写高性能Java持久性书籍的批处理章节时，我第一次研究了这个MySQL配置属性，当时，我发现此设置允许通过重写发送到数据库的SQL字符串来进行批处理。Statement

但是，MySQL 6 连接器/J 文档提到：

对于预准备语句，服务器端预准备语句当前无法利用此重写选项

因此，在很长一段时间里，我错误地认为此功能不适用于批处理 JDBC 预准备语句。

当我阅读MySQL 8.0.30连接器/ J发行说明时，我意识到该文档一直在误导我们：

连接属性rewriteBatchedStatements的描述已被纠正，消除了服务器端准备语句不能利用重写选项的限制。(错误# 34022110)

显然，他们正在使用JDBC，因此，我决定测试此功能并在本文中写下我的发现。

将重写缓冲语句与 JDBC 语句批处理结合使用

大多数 Java 开发人员在必须执行插入、更新和删除语句时使用接口的执行更新方法。Statement

但是，从 Java 1.2 开始，该接口一直提供可用于批处理多个语句的，以便在调用该方法时按单个请求发送它们，如以下示例所示：

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;
 
        ...
    }
}
复制

每个 INSERT 语句都将使用方法调用单独执行。
默认情况下，MySQL 在使用普通 JDBC 对象时仍会单独执行 INSERT 语句。
但是，如果我们启用 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);
复制

并调试方法执行，您将看到，现在，调用了

if (this.rewriteBatchedStatements.getValue() && nbrCommands > 4) {
    return executeBatchUsingMultiQueries(
        multiQueriesEnabled,
        nbrCommands,
        individualStatementTimeout
    );
}
复制

executeBatchUsingMultiQueries方法将单个INSERT语句连接到StringBuilder中，并运行单个执行调用：

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
        );
    }
 
    ...
}
复制

因此，对于普通JDBC语句批处理，MySQL重写BatchedStatements配置属性将附加当前批处理的语句，并在单个数据库往返中执行它们。

在JDBC PreparedStatement批处理中使用rewriteBatchedStatements

当使用JPA和Hibernate时，所有的SQL语句都将使用JDBC PreparedStatement执行，这是有很好的理由的:

预处理语句允许您增加语句缓存的可能性
prepare语句允许您避免SQL注入攻击，因为您绑定了参数值，而不是像我们对前面的String所做的那样将它们注入。格式的电话。

然而，由于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，如数据源代理日志条目所示：

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)
]
复制

因此，使用默认的MySQLJDBCDriver设置，一条语句被发送到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
复制

因此，在启用重写BatchedStatements 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语句都是使用JDBC PreparedStatement接口完成的。

因此，当运行一个测试，使用100的批处理大小在60秒的时间内插入5000篇文章记录时，我们得到以下结果:

下面是两种场景的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
复制

显然，MySQL的rewriteBatchedStatements设置提供了一个优势，当激活这个属性时，总批处理执行时间要短得多。

正如MySQL文档中所解释的，有一些注意事项你应该注意:

只有当重写的语句只包含INSERT或REPLACE语句时，statement . getgeneratedkeys()才有效。当使用JPA和Hibernate时，这不是真正的问题，因为在刷新期间只有INSERT会被批处理。

重写插入……ON DUPLICATE KEY UPDATE语句可能不会像预期的那样工作，但是，这对JPA和Hibernate来说不是问题，因为默认的INSERT不使用ON DUPLICATE KEY UPDATE子句。

结论

虽然MySQL JDBC驱动程序提供rewriteBatchedStatements设置已经很长时间了，因为文档很容易误导人，所以不清楚这个属性是否适用于PreparedStatement批处理。

因此，如果您的批处理任务运行在MySQL上，启用rewriteBatchedStatements设置可能会提供更好的性能。

原文标题：MySQL rewriteBatchedStatements configuration property
原文作者：vlad mihalcea
原文地址：https://vladmihalcea.com/mysql-rewritebatchedstatements/

mysql 墨力翻译

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

MySQL 重写声明配置属性

介绍

将重写缓冲语句与 JDBC 语句批处理结合使用

在JDBC PreparedStatement批处理中使用rewriteBatchedStatements

测试时间

结论

评论

相关阅读