【经验篇】JdbcTemplate数据批量操作性能优化

最近在项目中使用进行批量数据更新/删除时，小数据量场景下，性能较为优越。但是在进行大量数据（如10万条）的更新操作时，可能会遇到性能问题。

光阴迷客

2367人浏览 · 2024-06-14 17:38:32

光阴迷客 · 2024-06-14 17:38:32 发布

背景简介

最近在项目中使用JdbcTemplate进行批量数据更新/删除时，小数据量场景下，性能较为优越。但是在进行大量数据（如10万条）的更新操作时，可能会遇到性能问题。

优化建议

以下是一些可能的原因和相应的优化建议：

1. SQL语句和MySQL处理方式
原因： 批量更新操作的性能不仅取决于JdbcTemplate或NamedJdbcTemplate本身，更主要的是取决于执行的SQL语句和数据库的处理方式。例如，MySQL在处理批量更新时，会尝试将多个SQL语句转换为单个SQL语句，这个过程需要时间，特别是在处理大量数据时。

优化建议：

使用合适的批量更新策略，如使用PreparedStatement的批处理功能。
如果是MYSQL数据库，考虑使用MySQL的多值删除语句。

2. 数据库设计和索引
原因： 数据库的设计、索引的选择和配置也可能影响批量删除操作的性能。
优化建议：

对经常进行批量删除操作的表进行适当的设计和优化。
确保使用的索引是最合适的，并避免不必要的全表扫描。

3. 代码和SQL语句优化
原因： 代码中的某些实现或SQL语句本身可能存在性能问题。
优化建议：

对代码和SQL语句进行性能分析，找出性能瓶颈并进行优化。
使用数据库监控工具来跟踪和分析批量操作的性能。

4. 并发和锁竞争
原因： 当多个用户或应用程序同时访问数据库时，可能会发生锁竞争问题，导致批量操作的性能下降。
优化建议：

使用合适的并发控制策略，如乐观锁或悲观锁。
考虑在数据库层面进行并发控制，如使用数据库的隔离级别或行级锁等特性。

数据库服务器的硬件性能和配置也可能影响批量操作的性能，比如内存、CPU和磁盘I/O性能。

问题描述

用jdbcTemplate.batchUpdate(String[] sqls, SqlParameterSource[] batchArgs) 方法批量执行更新操作，当数据量达到10w级别时，执行时间太长，导致数据库连接超时关闭，且数据库出现锁表情况。

BatchUpdate原理

JdbcTemplate是Spring框架中处理SQL语句的核心类之一。它为开发者提供了一种更加方便简单的方式来执行SQL语句，其中的BatchUpdate也是JdbcTemplate的重要功能之一。BatchUpdate可以帮助我们简化SQL语句的处理、提高性能以及避免潜在的SQL注入等问题，简单介绍一下BatchUpdate的原理。

所谓BatchUpdate，就是批量执行SQL语句。换言之，在同一个事务里面，执行多次SQL语句的效率显然会更高。
在使用JDBC操作数据库时，我们通常使用的是Statement或者是PreparedStatement。执行单条SQL语句时，这两种方式其实都可以胜任，关键在于执行多条SQL语句时，PreparedStatement会更加高效。
JdbcTemplate中的BatchUpdate方法便是通过PreparedStatement来实现的。batchUpdate() 方法将多条 SQL 语句添加到一个BatchPreparedStatementSetter对象中，然后使用PreparedStatement.executeBatch()方法执行这个BatchPreparedStatementSetter对象。BatchPreparedStatementSetter对象会将我们提供的多条 SQL 语句批量发送到数据库执行，从而提高效率。
我们可以使用JdbcTemplate的batchUpdate方法并传入SQL语句、参数数组和参数类型。其中，参数数组中存放的是一组参数集合，而每个参数集合中包含了该语句的所有参数。而参数类型则是一个整数数组，指定了参数数组中的每个元素的类型。换句话说，我们需要传递的参数个数和类型都必须和SQL语句对应。
batchUpdate方法将使用预处理语句执行SQL语句，而我们所传递的参数信息则会在Java客户端被缓冲。这种方式可以非常有效地减少JDBC调用次数，并优化SQL执行效率。在数据需要提交时，batchUpdate会将Java客户端缓存中的参数集合全部传输至数据库端进行执行，这就是BatchUpdate最核心的原理。

需要注意的是，batchUpdate() 方法需要在一个事务中执行，以确保所有 SQL 语句都能够正确执行。如果在执行过程中出现错误，整个批处理操作会回滚，保证数据的一致性。

BatchUpdate源码分析

public int[] batchUpdate(String sql, SqlParameterSource[] batchArgs) {
    ......
    
    return getJdbcOperations().batchUpdate(
        pscf.getSql(),
        new BatchPreparedStatementSetter(){
                    @Override //用来为PreparedStatement设值。
                    public void setValues(PreparedStatement ps, int i) throws SQLException {
                        Object[] values = NamedParameterUtils.buildValueArray(parsedSql, batchArgs[i], null);
                        pscf.newPreparedStatementSetter(values).setValues(ps);
                    }
                    @Override //用来返回批次的大小
                    public int getBatchSize() {
                        return batchArgs.length;
                    }
                });
}

BatchPreparedStatementSetter的getBatchSize需要返回值原因是JdbcTemplate的batchUpdate在执行前，会先调用getBatchSize获取值，做后续判断。源码如下：

public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss) throws DataAccessException {
  if (this.logger.isDebugEnabled()) {
      this.logger.debug("Executing SQL batch update [" + sql + "]");
  }
  int[] result = (int[])this.execute(sql, (ps) -> {
    try {
        int batchSize = pss.getBatchSize();
        InterruptibleBatchPreparedStatementSetter ipss = pss instanceof InterruptibleBatchPreparedStatementSetter ? (InterruptibleBatchPreparedStatementSetter)pss : null;
        if (!JdbcUtils.supportsBatchUpdates(ps.getConnection())) {
            List<Integer> rowsAffected = new ArrayList();
            int ix = 0;
            while(true) {
                if (ix < batchSize) {
                    pss.setValues(ps, ix);
                    if (ipss == null || !ipss.isBatchExhausted(ix)) {
                        rowsAffected.add(ps.executeUpdate());
                        ++ix;
                        continue;
                    }
                }
                int[] rowsAffectedArray = new int[rowsAffected.size()];
  
                for(int ixx = 0; ixx < rowsAffectedArray.length; ++ixx) {
                    rowsAffectedArray[ixx] = (Integer)rowsAffected.get(ixx);
                }
                int[] var13 = rowsAffectedArray;
                return var13;
            }
        } else {
            int i = 0;
            while(true) {
                if (i < batchSize) {
                    pss.setValues(ps, i);
                    if (ipss == null || !ipss.isBatchExhausted(i)) {
                        ps.addBatch();
                        ++i;
                        continue;
                    }
                }
  
                int[] var10 = ps.executeBatch();
                return var10;
            }
        }
    } finally {
        if (pss instanceof ParameterDisposer) {
            ((ParameterDisposer)pss).cleanupParameters();
        }
    }
  });
  Assert.state(result != null, "No result array");
  return result;
}

BatchSize不宜过大，也不宜过小，它是指批次大小。

解决方法

方法一：手工分批

还使用jdbcTemplate.batchUpdate(String[] sqls, SqlParameterSource[] batchArgs) 方法，但是分批调用，例如：

String sql = "update emp set sal = :sql where empno = :empno";
SqlParameterSource[] batchArgs = new SqlParameterSource[] { ..10w.. };

// 手动分批
int batchSize = 5000;
// 总数据行数
int totalSize = batchArgs.length;
for (int startIndex = 0; startIndex < totalSize; startIndex += batchSize) {
    int endIndex = Math.min(startIndex + batchSize, totalSize);
    // 参数分批
    SqlParameterSource[] chunk = Arrays.copyOfRange(batchArgs, startIndex, endIndex);
    namedJdbcTemplate.batchUpdate(sql, chunk);
}

方法二：自动分批

使用BatchSqlUpdate进行自动分批，例如：

//
SqlParameterSource[] batchArgs = new SqlParameterSource[] { ..10w.. };
// 获取数据源
DataSource ds = namedJdbcTemplate.getJdbcTemplate().getDataSource();
// 构造BatchSqlUpdate对象
BatchSqlUpdate bsu = new BatchSqlUpdate(ds, "update emp set sal=? where empno = ?");
// 设置BatchSize
bsu.setBatchSize(5000);
// 设置参数类型
bsu.setTypes(new int[]{Types.FLOAT, Types.INTEGER});
// 设置参数队列
for(SqlParameterSource sqlParameterSource : batchArgs){
    bsu.update(new Object[]{sqlParameterSource.get("sal"), sqlParameterSource.get("empno")});
}
bsu.flush();