MySQL JDBC在设置rewriteBatchedStatements = true后仍然无法批处理查询。

Question

我一直在互联网和stackoverflow上阅读关于为什么jdbc批量更新速度很慢的文章。看起来正确的解决方法是在连接字符串中设置rewriteBatchedStatements = true。但是我似乎无法让它对我起作用。我正在使用springboot和spring-jdbc，我已经在我的application.properties文件中设置了rewriteBatchedStatements = true。

spring.datasource.url=jdbc:mysql://RDS_URL.us-west-2.rds.amazonaws.com/DATABASE?rewriteBatchedStatements=true

我还设置了断点来验证代码中是否反映了?rewriteBatchedStatements=true

我已经将general_log设置为true，当观察日志时，我看到插入操作没有被正确地批量处理

这是我的sql字符串代码：

private static String INSERT_USER_TO_GROUP_SQL = "INSERT INTO users (groupId, phoneNumber, accountId, source) VALUES(?, ?, ?, ?)";

日志中的行都是这样的：

45 Query INSERT INTO users (groupId, phoneNumber, accountId, source) VALUES('49', '99999999999', '123', 'web')

实现批量插入的Java代码如下：

executor.submit(() -> {
  jdbcTemplate.batchUpdate(INSERT_USER_TO_GROUP_SQL, new BatchPreparedStatementSetter() {

    @Override
    public void setValues(PreparedStatement ps, int i) throws SQLException {
      Subscriber subscriber = subscribers.get(i);
      ps.setString(1, subscriberGroup.getGroupId());
      ps.setString(2, subscriber.getPhoneNumber());
      ps.setString(3, accountId);
      ps.setString(4, subscriberGroup.getSource());
    }

    @Override
    public int getBatchSize() {
      return subscribers.size();
    }

  }); // end BatchPreparedStatementSetter lambda class
}); // end thread

这里是 batchUpdate 方法的代码片段，你可以看到它调用了 addBatch()，最后再执行 executeBatch()。

for (int i = 0; i < batchSize; i++) {
    pss.setValues(ps, i);
    if (ipss != null && ipss.isBatchExhausted(i)) {
        break;
    }
    ps.addBatch();
}
return ps.executeBatch();

这是我正在插入数据的表：

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `phoneNumber` varchar(20) DEFAULT NULL,
  `groupId` varchar(11) DEFAULT NULL,
  `source` varchar(30) DEFAULT NULL,
  `accountId` varchar(50) DEFAULT NULL,
  `deleted` int(1) DEFAULT '0',
  `timestamp` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phoneNumber` (`phoneNumber`,`groupId`,`accountId`)
) ENGINE=InnoDB AUTO_INCREMENT=21677 DEFAULT CHARSET=latin1;

我甚至尝试过不依赖于jdbc.batchUpdate()，而是手动实现。但是仍然没有成功。

    Connection connection = jdbcTemplate.getDataSource().getConnection();
    connection.setAutoCommit(false);
    PreparedStatement preparedStatement = 
    connection.prepareStatement(INSERT_USER_TO_GROUP_SQL);

    preparedStatement.setString(1, "1");
    preparedStatement.setString(2, "2");
    preparedStatement.setString(3, "3");
    preparedStatement.setString(4, "4");
    preparedStatement.addBatch();

    preparedStatement.setString(1, "11");
    preparedStatement.setString(2, "22");
    preparedStatement.setString(3, "33");
    preparedStatement.setString(4, "44");
    preparedStatement.addBatch();

    preparedStatement.executeBatch();
    connection.commit();

我也尝试排除准备语句的问题，所以我尝试直接编写查询代码。但仍然没有运气。

Connection connection = jdbcTemplate.getDataSource().getConnection();
Statement statement = connection.createStatement();
statement.addBatch("INSERT INTO users (groupId, phoneNumber, accountId, source) VALUES('1', '2', '3', '4')");
statement.addBatch("INSERT INTO users (groupId, phoneNumber, accountId, source) VALUES('11', '22', '33', '44')");
statement.executeBatch();

这是我pom文件中jdbc的版本

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
    <version>1.5.2.RELEASE</version>
</dependency>

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>4.3.6.RELEASE</version>
</dependency>

我期望这个参数能加快插入速度，并且日志中显示正确的批量插入语句。大部分SO文章都是让人们在url中设置rewritebatchedstatements = true，然后就可以了。

- qHack

---

请仔细阅读此内容，特别是有关查询性能的部分。http://meta.stackoverflow.com/a/271056/ 然后，请[编辑]您的问题以提供更多信息。同时，请确定您是否已经开启了自动提交，或者您是否在SQL中将多个INSERT语句包装在BEGIN TRANSACTION / COMMIT块中。 - O. Jones

谢谢您。我修改了标题，因为我并不真正关心特定的查询性能，这只是一个插入操作，应该很快，而且我认为这不是一个典型的“为什么我的查询很慢”的问题。我更感兴趣的是 rewritebatchedstatements=true 没有被尊重并且没有正确地对插入进行批处理。我已经添加了表结构和自动提交，并且目前已打开。 - qHack

你的代码在哪里使用了 addBatch() 和 executeBatch() 方法？重写后性能的提升来自于使用批处理进行多行插入，从而减少数据库服务器执行的隐式 COMMIT 操作的次数。你是否在调用 executeBatch() 之前多次调用了 addBatch()？https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#addBatch(java.lang.String) - O. Jones

是的，我是。我已经添加了jdbcTemplate.batchUpdate()实现的片段，这是我在第一个示例中使用的方法，该方法调用addBatch()和executeBatch()。此外，我还添加了另一个片段，展示了我尝试过的内容，在其中显式调用addBatch()和executeBatch()，而不使用内置的JdbcTemplate方法batchUpdate()。 - qHack

Answer 1

如果您遇到jdbcTemplate连接url无法支持 rewriteBatchedStatements = true 的问题，请检查您的pom.xml中的mysql-connector-java版本。

在撰写本文时，我使用的是：

<dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.9</version>
</dependency>

由于我的批处理逐个进行，因此看起来版本5.1.9不支持批量更新，并且会退回到spring文档所述的方式。

batchUpdate() -- 如果JDBC驱动程序不支持批量更新，则会退回到单个Statement上的单独更新。

将其升级到版本5.1.18后，我得到了正确的批量更新，可以在mysql general log中验证。

此外，我遇到了一个错误，可能会为其他人节省一些时间。在版本5.1.23中，当您配置db url以包含 profileSQL=true 时（我想像大多数人都这样做），驱动程序和profileSQL之间存在错误。

- qHack