JDBCBatchItemWriter批量更新缓慢问题排查及chunk大小影响疑问

问题描述 #

使用JDBCBatchItemWriter批量更新10万条记录时速度极慢，每2分钟仅完成1000条；但将Writer中的语句改为插入后，10万条记录可在40-45秒内完成。测试过程中chunk大小保持为1000，且Reader和Processor未做改动。

代码实现 #

Step配置 #

@Bean
public Step processEodBatchUpdateActualTableStep() {
    log.debug("[processEodBatchJob] Start Update Process for Actual Table");
    return stepBuilderFactory.get(JobConfigurationConstants.PROCESS_EOD_FILE_UPDATE_STEP_NAME)
            .<ExtensionQRMerchantTrxHistEntity, TransactionHistoryExtEntity>chunk(1000)
            .reader(updateItemReader())
            .processor(new ExtensionToTrxnHistExtConverter(mapper))
            .writer(new UpdateActualTable(dataSource).updateActualTable())
            .build();
}

Reader实现 #

@Bean
public JdbcCursorItemReader<ExtensionQRMerchantTrxHistEntity> updateItemReader(){
    log.info("[UPDATE Reader] Read all records from temp table");
    JdbcCursorItemReader<ExtensionQRMerchantTrxHistEntity> reader = new JdbcCursorItemReader<>();
    reader.setSql("SELECT * FROM ext_qr_merchant_trx_hist eqmth " +
                "WHERE EXISTS " +
                "(SELECT 1 FROM t_trxn_detail_ext ttde WHERE eqmth.trx_ref_no = ttde.ref_no AND eqmth.trx_amt = ttde.amount " +
                "AND eqmth.trx_dt = ttde.trxn_date);");
    reader.setDataSource(dataSource);
    reader.setFetchSize(10);
    reader.setRowMapper(new RowMapper<ExtensionQRMerchantTrxHistEntity>() {
            @Override
            public ExtensionQRMerchantTrxHistEntity mapRow(@NonNull ResultSet rs, int rowNum) throws SQLException {
                ExtensionQRMerchantTrxHistEntity entity = new ExtensionQRMerchantTrxHistEntity();
                entity.setTransactionDate(rs.getTimestamp(1));
                entity.setTransactionRefNo(rs.getString(2));
                entity.setTransactionAmount(rs.getBigDecimal(3));
                entity.setQrString(rs.getString(4));
                return entity;
            }
        });
    return reader;
}

Processor实现 #

@Slf4j
@RequiredArgsConstructor
public class ExtensionToTrxnHistExtConverter implements ItemProcessor<ExtensionQRMerchantTrxHistEntity, TransactionHistoryExtEntity> {

    private final DuitNowRppDTOMapper mapper;

    @Override
    public TransactionHistoryExtEntity process(@NonNull ExtensionQRMerchantTrxHistEntity entity) throws Exception {
        log.info("[Processor] Setting ExtensionQRMerchantTrxHistEntity to TransactionHistoryExtEntity");
        return setTransactionHistory(entity);
    }

    private TransactionHistoryExtEntity setTransactionHistory(ExtensionQRMerchantTrxHistEntity tempEntity){
        //Set output
        TransactionHistoryExtEntity outputEntity =new TransactionHistoryExtEntity();
        //Parse QR String
        DuitNowRppDTO dto = mapper.mapFromQRDestination(tempEntity.getQrString());
        //Set current date
        Date now = new Date();

        //Set field for Insert new record
        UUID uuid = UUID.randomUUID();
        outputEntity.setId(uuid);
        outputEntity.setCreateDate(now);
        outputEntity.setCreateBy(Constants.SYSTEM);

        //Set field for updating record
        outputEntity.setUpdateDate(now);
        outputEntity.setUpdateBy(Constants.SYSTEM);


        //replace field from temp table
        outputEntity.setCurrencyCode(dto.getTransactionCurrencyCode());
        outputEntity.setTransactionDate(tempEntity.getTransactionDate());
        outputEntity.setReferenceNumber(tempEntity.getTransactionRefNo());
        outputEntity.setAmount(tempEntity.getTransactionAmount());
        return outputEntity;
    }

}

Writer实现 #

@Slf4j
@RequiredArgsConstructor
public class UpdateActualTable {
    private final DataSource dataSource;

    public JdbcBatchItemWriter<TransactionHistoryExtEntity> updateActualTable() {
        log.info("[Update] Using Batch Item Writer to UPDATE to Actual Table");
        JdbcBatchItemWriter<TransactionHistoryExtEntity> itemWriter = new JdbcBatchItemWriter<>();
        itemWriter.setDataSource(dataSource);
        itemWriter.setSql("UPDATE t_trxn_detail_ext " +
                "SET " +
                "update_by = ?, update_dt = ? " +
                "WHERE ref_no = ? AND amount = ? AND trxn_date = ?");
        itemWriter.setItemPreparedStatementSetter((entity, preparedStatement) -> {
            // insert
            preparedStatement.setString(1, entity.getUpdateBy());
            preparedStatement.setString(2, entity.getUpdateDate().toString());
            //where
            preparedStatement.setString(3, entity.getReferenceNumber());
            preparedStatement.setBigDecimal(4, entity.getAmount());
            preparedStatement.setString(5, entity.getTransactionDate().toString());
        });
        return itemWriter;
    }
}

疑问 #

1. 导致更新性能远低于插入的原因是什么？
2. 当前设置的chunk大小（1000）是否会影响更新性能？

解答 #

一、更新性能远低于插入的核心原因 #

1. WHERE条件无复合索引，引发全表扫描
   更新语句的WHERE子句依赖ref_no、amount、trxn_date三个字段匹配数据，如果t_trxn_detail_ext表没有针对这三个字段的复合索引，数据库每次执行更新都要全表扫描查找匹配行，这是性能骤降的主要原因。而插入操作无需查找，直接写入磁盘页，自然速度快很多。

2. 日期类型字符串化导致索引失效
   代码中将updateDate和transactionDate转成字符串传入PreparedStatement，数据库会对日期字段做隐式类型转换，即使有索引也无法触发，进一步加剧了全表扫描的开销。

3. Reader FetchSize过小，增加数据库交互开销
   Reader设置的fetchSize=10意味着每次从数据库仅拉取10条数据，频繁的请求-响应交互会累积额外开销，拖慢整个批处理流程。

二、Chunk大小的影响 #

当前设置的chunk大小1000是合理的批量操作值，一般不会成为更新慢的瓶颈。批量更新的性能瓶颈主要集中在SQL执行效率（索引、类型转换）而非批次大小，只要chunk大小不超过数据库和JDBC驱动的限制，就不会有问题。

优化建议 #

• 添加复合索引：给t_trxn_detail_ext表创建(ref_no, amount, trxn_date)的复合索引（字段顺序可根据实际数据选择性调整，选择性高的字段放前面）。
• 修正日期参数传递：使用setTimestamp而非setString传递日期类型参数，避免隐式转换：

  preparedStatement.setString(1, entity.getUpdateBy());
  preparedStatement.setTimestamp(2, new Timestamp(entity.getUpdateDate().getTime()));
  preparedStatement.setString(3, entity.getReferenceNumber());
  preparedStatement.setBigDecimal(4, entity.getAmount());
  preparedStatement.setTimestamp(5, entity.getTransactionDate());
  

• 调整Reader FetchSize：将fetchSize设置为与chunk大小一致（如1000），减少数据库交互次数。
• 验证JDBC批量更新配置：部分数据库驱动（如MySQL）需要开启rewriteBatchedStatements=true才能真正启用批量更新优化，确保驱动配置正确。

内容的提问来源于stack exchange，提问作者Teck Chuan