使用JPA Repository读取千万级记录时遇OutOfMemoryError怎么解决？

问题核心分析 #

你遇到的内存溢出本质是两个问题叠加：

1. private方法的事务注解不生效：Spring AOP默认只代理public方法，getWorkLogs()是private方法，@Transactional(REQUIRES_NEW)完全没起作用，导致所有分页查询共用同一个EntityManager会话，一级缓存（Session缓存）持续膨胀。
2. Hibernate一级缓存未清理：即使分页，Hibernate会把查询到的所有实体存入一级缓存，直到会话结束，1000万条数据累积直接耗尽堆内存。

具体解决方案 #

方案1：修复事务有效性+手动清理缓存

首先解决事务注解不生效的问题，同时在每页处理完成后清理EntityManager缓存：

1. 将分页处理方法改为public并抽离到独立Bean
   创建一个专门处理批量数据的组件，让Spring能代理事务：

@Component
public class WorkLogBatchHandler {
    private final WorkLogRepository logRepository;
    private final EntityManager workLogEntityManager;

    // 注入对应数据源的EntityManager
    public WorkLogBatchHandler(WorkLogRepository logRepository, 
                               @Qualifier("workLogEntityManager") EntityManager workLogEntityManager) {
        this.logRepository = logRepository;
        this.workLogEntityManager = workLogEntityManager;
    }

    @Transactional(value = "workLogTransactionManager", propagation = Propagation.REQUIRES_NEW)
    public void processPage(int index, int batchSize, Long dateStart, Long dateEnd) throws IOException {
        Pageable pageable = PageRequest.of(index, batchSize);
        List<WorkLog> logs = logRepository.findLogsByTimestmpRange(dateStart, dateEnd, pageable);
        
        // 执行日志写入文件逻辑
        saveLogsToFile(logs);
        
        // 手动清理一级缓存，释放内存
        workLogEntityManager.clear();
    }

    private void saveLogsToFile(List<WorkLog> logs) throws IOException {
        // 你的文件写入实现
    }
}

2. 在原服务中注入该组件并调用

@Service
public class LogExportService {
    private final WorkLogRepository logRepository;
    private final WorkLogBatchHandler batchHandler;
    private final Logger log = LoggerFactory.getLogger(LogExportService.class);
    private Long dateStart;
    private Long dateEnd;

    public LogExportService(WorkLogRepository logRepository, WorkLogBatchHandler batchHandler) {
        this.logRepository = logRepository;
        this.batchHandler = batchHandler;
    }

    public void exportLogs() throws IOException {
        Long countLogs = logRepository.countLogsByTimestmpRange(dateStart, dateEnd);
        int batchSize = 32760;
        long countPages = countLogs / batchSize + 1;
        
        for (int index = 0; index < countPages; index++) {
            log.info("exportLogs: page {} of {}, pageSize = {}", index + 1, countPages, batchSize);
            batchHandler.processPage(index, batchSize, dateStart, dateEnd);
        }
    }
}

方案2：优化查询提示，减少缓存压力

在Repository的查询上添加Hibernate只读提示，让Hibernate不对实体做缓存管理，进一步降低内存占用：

public interface WorkLogRepository extends JpaRepository<WorkLog, Long> {
    @Query("SELECT l FROM WorkLog l WHERE (l.timestmp BETWEEN :from AND :to) ORDER BY eventId")
    @QueryHints({
            @QueryHint(name = org.hibernate.annotations.QueryHints.READ_ONLY, value = "true"),
            @QueryHint(name = org.hibernate.annotations.QueryHints.FETCH_SIZE, value = "32760")
    })
    List<WorkLog> findLogsByTimestmpRange(@Param("from") Long from,
                                          @Param("to") Long to,
                                          Pageable pageable);

    // count方法保持不变
    @Query("SELECT count(l) FROM WorkLog l WHERE (l.timestmp BETWEEN :from AND :to)")
    Long countLogsByTimestmpRange(@Param("from") Long from,
                                  @Param("to") Long to);
}

方案3：用滚动查询替代分页（推荐处理超大量数据）

大offset的分页查询会导致数据库性能下降，用滚动查询可以更高效地遍历数据，同时严格控制内存：

@Component
public class WorkLogBatchHandler {
    private final EntityManager workLogEntityManager;
    private final Logger log = LoggerFactory.getLogger(WorkLogBatchHandler.class);

    public WorkLogBatchHandler(@Qualifier("workLogEntityManager") EntityManager workLogEntityManager) {
        this.workLogEntityManager = workLogEntityManager;
    }

    @Transactional(value = "workLogTransactionManager", readOnly = true)
    public void scrollAndExport(Long dateStart, Long dateEnd, int batchSize) throws IOException {
        org.hibernate.query.Query<WorkLog> hibernateQuery = workLogEntityManager.createQuery(
                        "SELECT l FROM WorkLog l WHERE l.timestmp BETWEEN :from AND :to ORDER BY eventId",
                        WorkLog.class)
                .setParameter("from", dateStart)
                .setParameter("to", dateEnd)
                .setHint(org.hibernate.annotations.QueryHints.READ_ONLY, true)
                .setHint(org.hibernate.annotations.QueryHints.FETCH_SIZE, batchSize)
                .unwrap(org.hibernate.query.Query.class);

        // 开启向前滚动的结果集，不缓存所有数据
        ScrollableResults results = hibernateQuery.scroll(ScrollMode.FORWARD_ONLY);
        
        List<WorkLog> batch = new ArrayList<>(batchSize);
        int totalCount = 0;
        int pageIndex = 1;

        while (results.next()) {
            batch.add((WorkLog) results.get(0));
            totalCount++;

            if (batch.size() == batchSize) {
                log.info("exportLogs: page {}, processed {} records", pageIndex++, totalCount);
                saveLogsToFile(batch);
                batch.clear();
                workLogEntityManager.clear(); // 清理缓存
            }
        }

        // 处理最后一批剩余数据
        if (!batch.isEmpty()) {
            saveLogsToFile(batch);
            workLogEntityManager.clear();
        }

        results.close();
        log.info("Export completed, total records: {}", totalCount);
    }

    private void saveLogsToFile(List<WorkLog> logs) throws IOException {
        // 你的文件写入实现
    }
}

额外建议 #

• 调整JVM堆内存参数（如-Xmx4g）作为临时缓解，但核心还是解决缓存问题。
• 避免一次性count所有数据：如果count操作本身也导致内存问题，可以用数据库的COUNT(*)优化，或者估算分批范围。

内容的提问来源于stack exchange，提问作者Aarne Avialaynen