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
,然后就可以了。
INSERT
语句包装在BEGIN TRANSACTION
/COMMIT
块中。 - O. JonesaddBatch()
和executeBatch()
方法?重写后性能的提升来自于使用批处理进行多行插入,从而减少数据库服务器执行的隐式COMMIT
操作的次数。你是否在调用executeBatch()
之前多次调用了addBatch()
?https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#addBatch(java.lang.String) - O. Jones