Spring Boot下JPQL实现十万级数据批量更新的性能优化问题

以下优化方案适配你当前使用的Spring Boot 1.5.2 + PostgreSQL + Hibernate技术栈，可按改造成本和性能需求选择：

前置基础配置（所有方案都需要先加） #

先在application.properties中添加如下配置，开启JDBC批量处理能力，避免Hibernate单条发送SQL：

# 设置Hibernate批量处理大小，和你要的100条批次匹配
spring.jpa.properties.hibernate.jdbc.batch_size=100
# 开启批量版本化数据更新
spring.jpa.properties.hibernate.jdbc.batch_versioned_data=true
# 按SQL类型排序更新语句，优化批次合并效率
spring.jpa.properties.hibernate.order_updates=true
# PostgreSQL JDBC驱动专属配置，开启更新语句批量重写，否则批量配置不生效
spring.datasource.hikari.data-source-properties.reWriteBatchedUpdates=true

另外注意你当前CoverageRepository的泛型配置存在错误：你的主键是long类型，但继承CrudRepository时指定的主键类型是String，需要改成CrudRepository<Coverage, Long>，避免后续出现主键映射异常。

方案1：PostgreSQL原生UNNEST批量更新（性能最优，推荐） #

利用PostgreSQL原生UNNEST函数将数组转为临时表，关联更新，10万条数据可在几秒内完成，改造成本低：

1. 在CoverageRepository中新增批量更新方法：

@Modifying
@Query(value = "UPDATE coverage c SET coverage_id = t.new_id " +
        "FROM UNNEST(:ids::bigint[], :newCoverageIds::varchar[]) AS t(id, new_id) " +
        "WHERE c.id = t.id", nativeQuery = true)
void batchUpdateCoverageIds(@Param("ids") long[] ids, @Param("newCoverageIds") String[] newCoverageIds);

2. 改造业务层代码，按100条拆分批次执行：

@Autowired
CoverageRepository coverageRepository;
@PersistenceContext
EntityManager entityManager;

@Transactional
public void saveAllCoverages(List<Coverage> coverages) {
    int batchSize = 100;
    // 按批次拆分数据
    for (int i = 0; i < coverages.size(); i += batchSize) {
        int endIdx = Math.min(i + batchSize, coverages.size());
        List<Coverage> currentBatch = coverages.subList(i, endIdx);
        // 提取当前批次的id和新coverageId数组
        long[] ids = new long[currentBatch.size()];
        String[] newCoverageIds = new String[currentBatch.size()];
        for (int j = 0; j < currentBatch.size(); j++) {
            ids[j] = currentBatch.get(j).getId();
            newCoverageIds[j] = currentBatch.get(j).getCoverageId();
        }
        // 执行批量更新
        coverageRepository.batchUpdateCoverageIds(ids, newCoverageIds);
        // 清空持久上下文，避免内存溢出
        entityManager.flush();
        entityManager.clear();
    }
}

方案2：JPQL CASE WHEN批量更新（跨数据库兼容） #

如果你需要兼容不同数据库，可以用CASE WHEN + IN的JPQL语句实现单SQL批量更新：

1. 在CoverageRepository中新增批量更新方法：

@Modifying
@Query("<script>UPDATE Coverage SET coverageId = CASE id " +
        "<foreach collection='batch' item='c'> WHEN :#{#c.id} THEN :#{#c.coverageId} </foreach>" +
        "END WHERE id IN <foreach collection='batch' item='c' open='(' separator=',' close=')'> :#{#c.id} </foreach>" +
        "</script>")
void batchUpdateWithCaseWhen(@Param("batch") List<Coverage> batch);

2. 业务层同样按100条拆分批次，直接传入每批次的Coverage列表即可，性能略低于UNNEST方案，但跨数据库兼容性更好。

方案3：最小改造成本方案 #

如果不想修改Repository代码，仅调整现有逻辑配合前置配置也可提升性能：
将业务层代码拆分批次，每100条执行一次flush和clear，配合前置的批量配置，Hibernate会自动将100条更新语句合并为一个批次发送到数据库：

@Transactional
public void saveAllCoverages(List<Coverage> coverages) {
    int batchSize = 100;
    for (int i = 0; i < coverages.size(); i++) {
        Coverage coverage = coverages.get(i);
        coverageRepository.saveCoverage(coverage.getId(), coverage.getCoverageId());
        // 每100条执行一次批量提交并清空缓存
        if (i % batchSize == 0 && i > 0) {
            entityManager.flush();
            entityManager.clear();
        }
    }
    // 处理剩余不足100条的数据
    entityManager.flush();
    entityManager.clear();
}

该方案改造成本最低，但性能比前两个方案差30%~50%左右。

内容的提问来源于stack exchange，提问作者Bằng Rikimaru