JPA/JPQL千万级记录DISTINCT查DTO触发OOM优化方案

核心问题原因：当前写法会一次性将50万条去重结果全量加载到JVM堆内存，叠加PostgreSQL JDBC驱动默认全量拉取客户端结果、JPA一级缓存的内存开销，很容易超出堆内存上限触发OOM。你当前使用DTO投影而不是实体类接收结果的思路是对的，只需要调整结果加载方式、配合数据库优化即可解决问题，可落地的方案按优先级排序如下：

• 方案1：流式查询+分批拉取（优先推荐，改动最小）
  Spring Data JPA原生支持Stream类型返回值，配合JDBC抓取大小设置、只读事务，可以实现数据库结果分批加载到JVM，单条处理完就会被GC回收，内存占用稳定在几十MB级别，不会出现OOM。
  注意PostgreSQL JDBC驱动的特殊要求：必须关闭自动提交（即开启事务），fetchSize参数才会生效，否则驱动还是会默认把所有结果拉到本地内存。
  代码实现参考：

  import org.springframework.data.jpa.repository.JpaRepository
  import org.springframework.data.jpa.repository.Query
  import org.springframework.data.jpa.repository.QueryHints
  import javax.persistence.QueryHint
  import java.util.stream.Stream
  import org.springframework.transaction.annotation.Transactional
  
  // Repository层定义
  interface MyClazzRepository : JpaRepository<MyClazz, Long> {
      @QueryHints(
          // 开启只读模式，关闭Hibernate脏检查，减少不必要的性能开销
          QueryHint(name = org.hibernate.annotations.QueryHints.READ_ONLY, value = "true"),
          // 设置每次从数据库拉取1000条结果到内存，不要设太大也不要太小
          QueryHint(name = org.hibernate.annotations.QueryHints.FETCH_SIZE, value = "1000")
      )
      @Query("SELECT DISTINCT new MyClazzDAO(var1, var2) FROM MyClazz")
      @Transactional(readOnly = true) // 必须加，否则PostgreSQL端fetchSize不生效
      fun findDistinctModelsStream(): Stream<MyClazzDAO>
  }
  
  // 业务层调用，use是Kotlin的扩展函数，会自动关闭流、释放数据库连接
  @Service
  class MyClazzService(private val myClazzRepository: MyClazzRepository) {
      fun handleDistinctData() {
          myClazzRepository.findDistinctModelsStream().use { dataStream ->
              dataStream.forEach { daoItem ->
                  // 这里写单条数据的业务逻辑，比如批量入库、做聚合计算
                  // 禁止在这里把所有数据重新收集到全局List/Map中，否则会再次触发OOM
              }
          }
      }
  }
  

• 方案2：键集分页查询（兼容性最好，无事务依赖）
  如果不想使用Stream，或者需要把结果分批返回给上层，可以用键集（Seek）分页代替传统的offset分页，基于去重字段做排序和游标过滤，避免大offset的性能问题，也不需要持有长事务连接。
  核心逻辑是每次查询固定条数（比如1000条），第一次查询时lastVar1传入空字符串、lastVar2传入Int.MIN_VALUE，之后将上一批次最后一条的var1、var2值作为下一次查询的过滤条件，循环直到查询结果长度小于每页条数即可。
  参考查询逻辑：

  // Repository层方法
  @Query("""
      SELECT DISTINCT new MyClazzDAO(var1, var2) FROM MyClazz
      WHERE var1 > :lastVar1 OR (var1 = :lastVar1 AND var2 > :lastVar2)
      ORDER BY var1, var2
      LIMIT :pageSize
  """)
  fun findNextPage(
      @Param("lastVar1") lastVar1: String,
      @Param("lastVar2") lastVar2: Int,
      @Param("pageSize") pageSize: Int = 1000
  ): List<MyClazzDAO>
  

• 配套数据库优化（必做，大幅提升查询速度）
  你的去重逻辑只依赖var1、var2两个字段，直接给这两个字段建联合覆盖索引，数据库不需要回表查询，直接扫描索引就能完成去重，查询速度可以提升数倍：

  建索引SQL：CREATE INDEX IF NOT EXISTS idx_myclazz_var1_var2 ON my_clazz (var1, var2);

常见踩坑点：

1. 不要在拿到Stream结果后调用toList()、toSet()等全量收集方法，否则所有结果还是会加载到内存，优化完全失效。
2. 不要把fetchSize设得过大（比如超过10000），否则单次拉取的数据量太大还是会占用过多内存；也不要设得太小（比如小于100），会增加和数据库的交互次数，拖慢处理速度。
3. 有条件可以升级到Spring Boot 3.x版本，自带的Hibernate 6对DISTINCT DTO投影做了大量性能优化，内存占用和查询速度都比Hibernate 5好很多。

内容的提问来源于stack exchange，提问作者Pablo Mosby