使用OpenCsv的CsvWriter导出大CSV时堆内存溢出问题排查

云上老周

2026-7-3

问题描述

需要从数据库导出大量记录生成CSV文件，此前用OpenCsv的CsvWriter逐行写入内存，数据量增大后出现Java堆内存溢出。改成数据库分页查询后，溢出错误仍出现在CsvWriter对象中，希望实现流式导出。

现有实现代码

@Transactional(rollbackFor = Exception.class)
@Override
public void getExtremelyLargeLogFile(HttpServletResponse response) throws ParseException, SQLException {
    SqlSession session = sqlSessionFactory.openSession();
    session.getConnection().setAutoCommit(false);
    
    int batch = 0; 
    
    DateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
    String fileName = "log_" + sdf.format(new Date());
    List<Log> list = // 类似 'select * from table limit 10000 offset 10000*batch;' 的查询
    String[] titleName = new String[] { "No.", "Modules", "System activities", "Event status" };

    buildCsvLineList(response, list, titleName, fileName, "dd-MM-yyyy HH:mm:ss", batch);
    while (!list.isEmpty()) {
        batch++; 
        list = // 类似 'select * from table limit 10000 offset 10000*batch;' 的查询
        buildCsvLineList(response, list, titleName, fileName, "dd-MM-yyyy HH:mm:ss", batch);
    }

    session.commit();
    session.clearCache();   
    session.close();
}

private void buildCsvLineList(HttpServletResponse response, List<Logs> logs, String[] titleName,
        String fileName, String pattern, int batch) {
    OutputStream out = null;
    CSVWriter csvWriter = null;

    try {

        out = response.getOutputStream();
        csvWriter = new CSVWriter(new OutputStreamWriter(out),
                ',',
                CSVWriter.NO_QUOTE_CHARACTER,
                '\\',
                System.lineSeparator());

        String lastFileName = fileName + ".csv";
        response.setContentType("application/msexcel;charset=UTF-8");
        response.setHeader("Content-Disposition",
                "attachment; filename=" + URLEncoder.encode(lastFileName, "UTF-8"));
        if (batch == 0) {
        csvWriter.writeNext(titleName);
        }

        for (Logs log : logs) {
             // 数据转换逻辑
            csvWriter.writeNext(line.toArray(new String[0]));
            csvWriter.flush();

        }
    } catch (Exception e) {
        log.error("Export csv exception, errorMessage:{}", e.getMessage(), e);
    } finally {

        if (out != null) {
            try {
                out.flush();
                out.close();
            } catch (IOException e) {
                log.error("Export Csv exception! errorMessage:{}", e.getMessage());
            }

        }

        if (csvWriter != null) {
            try {
                csvWriter.flush();
                csvWriter.close();
            } catch (IOException e) {
                log.error("Export Csv exception! errorMessage:{}", e.getMessage());
            }

        }
    }
}

堆栈跟踪信息

Caused by: java.lang.OutOfMemoryError: Java heap space
        at org.springframework.util.FastByteArrayOutputStream.addBuffer(FastByteArrayOutputStream.java:304)
        at org.springframework.util.FastByteArrayOutputStream.write(FastByteArrayOutputStream.java:125)
        at org.springframework.web.util.ContentCachingResponseWrapper$ResponseServletOutputStream.write(ContentCachingResponseWrapper.java:230)
        at java.base/sun.nio.cs.StreamEncoder.writeBytes(StreamEncoder.java:234)
        at java.base/sun.nio.cs.StreamEncoder.implWrite(StreamEncoder.java:304)
        at java.base/sun.nio.cs.StreamEncoder.implWrite(StreamEncoder.java:282)
        at java.base/sun.nio.cs.StreamEncoder.write(StreamEncoder.java:132)
        at java.base/sun.nio.cs.StreamEncoder.write(StreamEncoder.java:142)
        at java.base/java.io.OutputStreamWriter.write(OutputStreamWriter.java:223)
        at java.base/java.io.Writer.write(Writer.java:249)
        at com.opencsv.CSVWriter.writeNext(CSVWriter.java:91)
        at com.opencsv.AbstractCSVWriter.writeNext(AbstractCSVWriter.java:76)
        at com.opencsv.ICSVWriter.writeNext(ICSVWriter.java:162)

补充：日志过滤器代码

import jakarta.servlet.FilterChain; 
import jakarta.servlet.ServletException; 
import jakarta.servlet.http.HttpServletRequest; 
import jakarta.servlet.http.HttpServletResponse; 
import lombok.extern.slf4j.Slf4j; 
import org.jetbrains.annotations.NotNull; 
import org.springframework.stereotype.Component; 
import org.springframework.web.filter.OncePerRequestFilter; 
import org.springframework.web.util.ContentCachingRequestWrapper; 
import org.springframework.web.util.ContentCachingResponseWrapper;

import java.io.IOException; 
import java.io.UnsupportedEncodingException;

@Slf4j 
@Component 

public class LogFilter extends OncePerRequestFilter {

private static final int MAX_PAYLOAD_LENGTH = 2000;

@Override
protected void doFilterInternal(@NotNull HttpServletRequest request, @NotNull HttpServletResponse response, FilterChain filterChain)
        throws ServletException, IOException {

    long startTime = System.currentTimeMillis();
    if (request.getRequestURI().matches("(?i).*csv.*") ) {
        filterChain.doFilter(request, response);
        return;
    }

    ContentCachingRequestWrapper requestWrapper = new ContentCachingRequestWrapper(request);
    ContentCachingResponseWrapper responseWrapper = new ContentCachingResponseWrapper(response);
    filterChain.doFilter(requestWrapper, responseWrapper);
    long timeTaken = System.currentTimeMillis() - startTime;

    String requestBody = getStringValue(requestWrapper.getContentAsByteArray(),
            request.getCharacterEncoding());
    String responseBody = null;

    if (!request.getRequestURI().matches("(?i).*csv.*") || response.getStatus() / 100 != 2) {
        responseBody = getStringValue(responseWrapper.getContentAsByteArray(),
                response.getCharacterEncoding());
    }

        log.info("Client IP: " + clientIp);
        log.info("Finished processing request: {}  {}", request.getMethod(), request.getRequestURI());
        log.info("Query String: " + request.getQueryString());

        // 记录请求头
        log.info("Headers: ");
        request.getHeaderNames().asIterator()
                .forEachRemaining(headerName -> log.info("    {}: {}", headerName, request.getHeader(headerName)));

        log.info("Response Code: " + response.getStatus());


        // 如果请求体超过1000字符，只打印前1000并省略后续
        if (requestBody.length() > MAX_PAYLOAD_LENGTH) {
            requestBody = requestBody.substring(0, MAX_PAYLOAD_LENGTH) + "...[省略 " + (requestBody.length() - MAX_PAYLOAD_LENGTH) + " 个字符]";
        }
        log.info("Request: " + requestBody);


        // 处理响应体日志
        log.info("耗时: {}ms", timeTaken);
        log.info("***************************************************");
    }
    responseWrapper.copyBodyToResponse();
}

@Override
protected boolean shouldNotFilter(HttpServletRequest request) throws ServletException {         
    return request.getRequestURI().matches("csv");      }

}

问题分析与解决方案

核心问题原因

从堆栈信息能看到，溢出发生在ContentCachingResponseWrapper的FastByteArrayOutputStream里，这说明你的响应被缓存到内存中了，根本没做到流式输出。

看过滤器代码，问题出在shouldNotFilter方法：

@Override
protected boolean shouldNotFilter(HttpServletRequest request) throws ServletException {         
    return request.getRequestURI().matches("csv");      }

这个正则只会匹配URI完全等于csv的请求，但导出接口的URI应该是类似/export/logs.csv这种带路径的，所以过滤器并没有跳过CSV请求，反而给CSV响应套了ContentCachingResponseWrapper，把所有输出都缓存到内存里，数据量大了自然堆溢出。

另外业务代码还有两个问题：

每次调用buildCsvLineList都会重新创建CSVWriter并关闭输出流，第一次循环后输出流就关了，后续写入会报错。
分页用offset效率极低，数据量越大，数据库需要扫描的行数越多，性能会急剧下降。

修复步骤

1. 修复过滤器，确保CSV请求跳过缓存

把shouldNotFilter的正则改成和doFilterInternal里一致的匹配规则：

@Override
protected boolean shouldNotFilter(HttpServletRequest request) throws ServletException {         
    return request.getRequestURI().matches("(?i).*csv.*");      
}

这样所有包含csv的URI都会跳过ContentCachingResponseWrapper，响应直接流式输出到客户端，不会缓存到内存。

2. 重构CSV导出代码，实现真正的流式输出

不要每次分页都重新创建CSVWriter和关闭流，应该在整个导出过程中复用同一个CSVWriter，写完所有数据再关闭：

@Transactional(rollbackFor = Exception.class)
@Override
public void getExtremelyLargeLogFile(HttpServletResponse response) throws ParseException, SQLException {
    SqlSession session = sqlSessionFactory.openSession();
    try {
        session.getConnection().setAutoCommit(false);
        
        DateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String fileName = "log_" + sdf.format(new Date()) + ".csv";
        
        // 设置响应头，只做一次
        response.setContentType("text/csv;charset=UTF-8"); // 用正确的CSV MIME类型，不是application/msexcel
        response.setHeader("Content-Disposition",
                "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
        
        // 初始化CSVWriter，整个导出过程复用
        try (OutputStream out = response.getOutputStream();
             OutputStreamWriter osw = new OutputStreamWriter(out, StandardCharsets.UTF_8);
             CSVWriter csvWriter = new CSVWriter(osw,
                     ',',
                     CSVWriter.NO_QUOTE_CHARACTER,
                     '\\',
                     System.lineSeparator())) {
            
            // 写入表头
            String[] titleName = new String[] { "No.", "Modules", "System activities", "Event status" };
            csvWriter.writeNext(titleName);
            csvWriter.flush();
            
            int batch = 0;
            final int PAGE_SIZE = 10000;
            List<Logs> list = queryLogsByPage(batch, PAGE_SIZE); // 封装分页查询方法
            
            while (!list.isEmpty()) {
                for (Logs log : list) {
                    // 数据转换逻辑，生成line数组
                    csvWriter.writeNext(line.toArray(new String[0]));
                }
                // 每批数据写完后刷新，避免内存堆积
                csvWriter.flush();
                
                batch++;
                list = queryLogsByPage(batch, PAGE_SIZE);
            }
        } catch (IOException e) {
            log.error("导出CSV失败", e);
            throw new RuntimeException(e);
        }
        
        session.commit();
    } finally {
        session.clearCache();   
        session.close();
    }
}

// 封装分页查询，用游标或主键分页替代offset
private List<Logs> queryLogsByPage(int batch, int pageSize) {
    // 建议用主键分页，比如：select * from table where id > lastId limit pageSize
    // 避免offset的性能问题，尤其是数据量极大时
    // 这里替换成你的查询逻辑
    return session.selectList("queryLogs", batch * pageSize, pageSize);
}

要点：

使用try-with-resources自动管理流和CSVWriter，避免手动关闭的错误。
响应头只设置一次，不要重复设置。
每批数据写完后刷新CSVWriter，确保数据及时输出到客户端，不堆积在内存。
替换offset分页为主键分页（或游标分页），提升数据库查询性能，避免大数据量下的扫描开销。

3. 额外优化建议

关闭MyBatis的一级缓存：流式导出不需要缓存查询结果，可在查询时设置useCache=false。
适当调整JVM参数：比如-Xmx，避免其他潜在内存问题。
加入进度日志：方便监控导出过程，排查异常。

内容的提问来源于stack exchange，提问作者Jia Yi Chow

使用OpenCsv的CsvWriter导出大CSV时堆内存溢出问题排查

现有实现代码 #

堆栈跟踪信息 #

补充：日志过滤器代码 #

核心问题原因 #

修复步骤 #

1. 修复过滤器，确保CSV请求跳过缓存 #

2. 重构CSV导出代码，实现真正的流式输出 #

3. 额外优化建议 #