SpringBoot导出Oracle 5万条CSV数据超时优化咨询

我在SpringBoot应用中通过Repository原生查询导出Oracle数据库的5万条记录到CSV文件，当前接口响应耗时近2分钟，触发了504网关超时。以下是Controller和Service层代码，求缩短响应时间的方法。

Controller代码 #

@PostMapping(value = Constants.DOWNLOAD)
@ApiOperation("download file for approvals")
public ResponseEntity<String> downloadFile(
    @RequestBody @ApiParam(value = "Payload Body", required = true) 
ApiDownload apiDownload) throws Exception {
    try {
        return mergeDebtService.downloadFile(apiDownload);
    } catch (Exception e) {
        e.printStackTrace();
        throw new InvalidInputException(e.getMessage());
    }
}

Service代码 #

public ResponseEntity<String> downloadFile(ApiDownload apiDownload) throws Exception {
    List<Integer> listIds = new ArrayList<Integer>();
    List<Object[]> listObj = new ArrayList<>();
    boolean blnAll=false;

    if(UserMap.getRole()!=null && UserMap.getRole().equals("ADMIN")) {
        listObj = ddmApprovalsRepository.findAllData(ddmUserMap.getProfileType());
    } else {
        listObj = ddmApprovalsRepository.findData(apiDownload.getLoginName());
    }

    File directory = new File("/tmp/uploads");
    directory.mkdir();
    Files.createDirectories(Paths.get("/tmp/uploads"));

    try {            
        if(directory.exists()) {
            FileUtils.deleteDirectory(directory);
            directory.mkdir();
            Files.createDirectories(Paths.get("/tmp/uploads"));
        }
    } catch (Exception e) {
        // if the file is being accessed then it can throw error. 
    }

    FILE_EXISTS=true;
    ExcelFileGenerator fileGenerator = new ExcelFileGenerator();
    String fileName = fileGenerator.generateCSVFile(listObj, 
    apiApprovalsDownload.getLoginName(),"/tmp/uploads",cbControlParams,blnAll);
    
    File file = new File("/tmp/uploads/"+fileName);
    return ResponseEntity.status(200).header("filename", fileName).body("downloaded successfully");
}

优化方案 #

1. 流式分页查询，避免全量加载内存 #

当前一次性把5万条数据加载到List<Object[]>，既占内存又拖慢查询速度。改用流式或分批分页读取：

• Repository层返回Stream类型，边读边处理：

@Query(value = "SELECT column1, column2 FROM your_table WHERE ...", nativeQuery = true)
Stream<Object[]> findAllDataStream(String profileType);

• Service层用try-with-resources包裹流，避免内存泄漏：

try (Stream<Object[]> dataStream = ddmApprovalsRepository.findAllDataStream(profileType)) {
    // 直接将流数据写入CSV，无需缓存到List
}

2. 跳过本地文件，直接流式输出到HTTP响应 #

当前流程多了本地磁盘IO的开销，直接把CSV内容写入响应流：

• 修改Controller返回StreamingResponseBody，实时输出数据：

@PostMapping(value = Constants.DOWNLOAD)
public ResponseEntity<StreamingResponseBody> downloadFile(@RequestBody ApiDownload apiDownload) {
    StreamingResponseBody responseBody = outputStream -> {
        CSVWriter writer = new CSVWriter(new OutputStreamWriter(outputStream, StandardCharsets.UTF_8));
        // 分批查询数据，逐行写入writer
        writer.writeNext(new String[]{"列1", "列2"}); // 写入表头
        // 循环分页查询并写入数据
        writer.close();
    };
    return ResponseEntity.ok()
            .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=approvals.csv")
            .contentType(MediaType.APPLICATION_OCTET_STREAM)
            .body(responseBody);
}

3. 简化本地文件操作逻辑 #

当前代码反复删除创建目录，存在冗余IO：

• 生成唯一文件名（比如UUID.randomUUID().toString() + ".csv"），避免文件覆盖，跳过删除目录的步骤；
• 文件生成后异步清理，不要阻塞接口流程。

4. 优化数据库查询性能 #

• 给查询条件字段（如login_name、profile_type）添加索引，减少数据库扫描行数；
• 避免SELECT *，只查询需要导出的字段，降低数据传输量；
• 给Oracle查询添加/*+ ALL_ROWS */提示，优化大结果集的执行计划。

5. 异步导出+通知机制 #

如果必须生成本地文件，把导出任务异步化：

• 用@Async标记导出方法，接口立即返回“导出任务已启动”；
• 任务完成后通过邮件、前端消息通知用户下载，避免用户等待超时。

6. 内存细节优化 #

• 初始化ArrayList时指定初始容量（比如new ArrayList<>(50000)），避免频繁扩容；
• 把Object[]映射为实体类，减少类型转换开销，代码可读性也更高。

内容的提问来源于stack exchange，提问作者Ashish kumar