HeapDump性能社区

性能工具
登录注册
性能文章>【译】MySQL rewriteBatchedStatements 的属性设置>

【译】MySQL rewriteBatchedStatements 的属性设置转载

willberthos
1年前
340645

介绍

在本文中，我们将了解 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

原文链接：https://vladmihalcea.com/mysql-rewritebatchedstatements/#more-24661

点赞收藏
willberthos

keep foolish！

请先登录，查看4条精彩评论吧
快去登录吧，你将获得
  • 浏览更多精彩评论
  • 和开发者讨论交流，共同进步
5
4
Lv3
willberthos

徽章

keep foolish！

推荐阅读

聊聊数据库连接池 Druid得物一面：Redis大key有什么危害？如何排查和处理？数据库系列：事务的4种隔离级别数据库系列：RR和RC下，快照读的区别数据库系列：前缀索引和索引长度的取舍数据库系列：使用高区分度索引列提升性能
站内搜索
锁
mysql
故障排查
实战案例
IO
oom
java
jvm
gc
linux
内存泄漏
内核

专题推荐

数据库最容易成为整个系统的性能瓶颈，本期专题向大家介绍日常工作中常见的数据库性能问题，干货+性能调优实践帮你攻克数据库性能瓶颈！
8篇文章18258阅读量

本月精选性能专题

本月精选线上案例

本月精选原创好文

HeapDump

有性能问题，上HeapDump性能社区

Copyright © 2019-2024 heapdump.cn 版权所有 浙ICP备17054444号

联系我们

HeapDump性能社区

网媒渠道

堆堆酱

扫码加堆堆酱好友

友情链接

PerfMa官网SegmentFault思否TesterHomeXPocket开源社区DCloud 问答社区稀土掘金博客园W3CTech