避免 OOM，高效匯出百萬級資料的 SpringBoot 實現方案

來源：程式設計師小富

前言

動態資料匯出是一般專案都會涉及到的功能。它的基本實現邏輯就是從mysql查詢資料，載入到記憶體，然後從記憶體建立excel或者csv，以流的形式響應給前端。

• 參考：。

SpringBoot下載excel基本都是這麼幹。

雖然這是個可行的方案，然而一旦mysql資料量太大，達到十萬級，百萬級，千萬級，大規模資料載入到記憶體必然會引起OutofMemoryError。

要考慮如何避免OOM，一般有兩個方面的思路。

一方面就是儘量不做唄，先懟產品下面幾個問題啊：

• 我們為什麼要匯出這麼多資料呢？誰傻到去看這麼大的資料啊，這個設計是不是合理的呢？
• 怎麼做好許可權控制？百萬級資料匯出你確定不會洩露商業機密？
• 如果要匯出百萬級資料，那為什麼不直接找大資料或者DBA來幹呢？然後以郵件形式傳遞不行嗎？
• 為什麼要透過後端的邏輯來實現，不考慮時間成本，流量成本嗎？
• 如果透過分頁匯出，每次點選按鈕只導2萬條，分批匯出難道不能滿足業務需求嗎？

如果產品說 “甲方是爸爸，你去和甲方說啊”，“客戶說這個做出來，才考慮付尾款！”，如果客戶的確缺根筋要讓你這樣搞， 那就只能從技術上考慮如何實現了。

從技術上講，為了避免OOM，我們一定要注意一個原則：

不能將全量資料一次性載入到記憶體之中。

全量載入不可行，那我們的目標就是如何實現資料的分批載入了。實事上，Mysql本身支援Stream查詢，我們可以透過Stream流獲取資料，然後將資料逐條刷入到檔案中，每次刷入檔案後再從記憶體中移除這條資料，從而避免OOM。

由於採用了資料逐條刷入檔案，而且資料量達到百萬級，所以檔案格式就不要採用excel了，excel2007最大才支援104萬行的資料。這裡推薦：

以csv代替excel。

考慮到當前SpringBoot持久層框架通常為JPA和mybatis，我們可以分別從這兩個框架實現百萬級資料匯出的方案。

JPA實現百萬級資料匯出

• 具體方案不妨參考：http://knes1.github.io/blog/2015/2015-10-19-streaming-mysql-results-using-java8-streams-and-spring-data.html。

實現專案對應：

核心註解如下，需要加入到具體的Repository之上。方法的返回型別定義成Stream。Integer.MIN_VALUE告訴jdbc driver逐條返回資料。

@QueryHints(value = @QueryHint(name = HINT_FETCH_SIZE, value = "" + Integer.MIN_VALUE))
@Query(value = "select t from Todo t")
Stream<Todo> streamAll();

此外還需要在Stream處理資料的方法之上新增@Transactional(readOnly = true)，保證事物是隻讀的。

同時需要注入javax.persistence.EntityManager，透過detach從記憶體中移除已經使用後的物件。

@RequestMapping(value = "/todos.csv", method = RequestMethod.GET)
@Transactional(readOnly = true)
public void exportTodosCSV(HttpServletResponse response) {
 response.addHeader("Content-Type", "application/csv");
 response.addHeader("Content-Disposition", "attachment; filename=todos.csv");
 response.setCharacterEncoding("UTF-8");
 try(Stream<Todo> todoStream = todoRepository.streamAll()) {
  PrintWriter out = response.getWriter();
  todoStream.forEach(rethrowConsumer(todo -> {
   String line = todoToCSV(todo);
   out.write(line);
   out.write("\n");
   entityManager.detach(todo);
  }));
  out.flush();
 } catch (IOException e) {
  log.info("Exception occurred " + e.getMessage(), e);
  throw new RuntimeException("Exception occurred while exporting results", e);
 }
}

MyBatis實現百萬級資料匯出

MyBatis實現逐條獲取資料，必須要自定義ResultHandler，然後在mapper.xml檔案中，對應的select語句中新增fetchSize="-2147483648"。

最後將自定義的ResultHandler傳給SqlSession來執行查詢，並將返回的結果進行處理。

MyBatis實現百萬級資料匯出的具體例項

以下是基於MyBatis Stream匯出的完整的工程樣例，我們將透過對比Stream檔案匯出和傳統方式匯出的記憶體佔用率的差異，來驗證Stream檔案匯出的有效性。

我們先定義一個工具類DownloadProcessor，它內部封裝一個HttpServletResponse物件，用來將物件寫入到csv。

public class DownloadProcessor {
    private final HttpServletResponse response;
     
    public DownloadProcessor(HttpServletResponse response) {
        this.response = response;
        String fileName = System.currentTimeMillis() + ".csv";
        this.response.addHeader("Content-Type", "application/csv");
        this.response.addHeader("Content-Disposition", "attachment; filename="+fileName);
        this.response.setCharacterEncoding("UTF-8");
    }
     
    public <E> void processData(E record) {
        try {
            response.getWriter().write(record.toString()); //如果是要寫入csv,需要重寫toString,屬性透過","分割
            response.getWriter().write("\n");
        }catch (IOException e){
            e.printStackTrace();
        }
    }
}

然後透過實現org.apache.ibatis.session.ResultHandler，自定義我們的ResultHandler，它用於獲取java物件，然後傳遞給上面的DownloadProcessor處理類進行寫檔案操作：

public class CustomResultHandler implements ResultHandler {

    private final DownloadProcessor downloadProcessor;
     
    public CustomResultHandler(
            DownloadProcessor downloadProcessor) {
        super();
        this.downloadProcessor = downloadProcessor;
    }
     
    @Override
    public void handleResult(ResultContext resultContext) {
        Authors authors = (Authors)resultContext.getResultObject();
        downloadProcessor.processData(authors);
    }
}

實體類：

public class Authors {
    private Integer id;
    private String firstName;
     
    private String lastName;
     
    private String email;
     
    private Date birthdate;
     
    private Date added;
     
    public Integer getId() {
        return id;
    }
     
    public void setId(Integer id) {
        this.id = id;
    }
     
    public String getFirstName() {
        return firstName;
    }
     
    public void setFirstName(String firstName) {
        this.firstName = firstName == null ? null : firstName.trim();
    }
     
    public String getLastName() {
        return lastName;
    }
     
    public void setLastName(String lastName) {
        this.lastName = lastName == null ? null : lastName.trim();
    }
     
    public String getEmail() {
        return email;
    }
     
    public void setEmail(String email) {
        this.email = email == null ? null : email.trim();
    }
     
    public Date getBirthdate() {
        return birthdate;
    }
     
    public void setBirthdate(Date birthdate) {
        this.birthdate = birthdate;
    }
     
    public Date getAdded() {
        return added;
    }
     
    public void setAdded(Date added) {
        this.added = added;
    }
     
    @Override
    public String toString() {
        return this.id + "," + this.firstName + "," + this.lastName + "," + this.email + "," + this.birthdate + "," + this.added;
    }
}

Mapper介面：

public interface AuthorsMapper {
   List<Authors> selectByExample(AuthorsExample example);
    
   List<Authors> streamByExample(AuthorsExample example); //以stream形式從mysql獲取資料
}

Mapper xml檔案核心片段，以下兩條select的唯一差異就是在stream獲取資料的方式中多了一條屬性：fetchSize="-2147483648"

<select id="selectByExample" parameterType="com.alphathur.mysqlstreamingexport.domain.AuthorsExample" resultMap="BaseResultMap">
    select
    <if test="distinct">
      distinct
    </if>
    'false' as QUERYID,
    <include refid="Base_Column_List" />
    from authors
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
    <if test="orderByClause != null">
      order by ${orderByClause}
    </if>
  </select>
  <select id="streamByExample" fetchSize="-2147483648" parameterType="com.alphathur.mysqlstreamingexport.domain.AuthorsExample" resultMap="BaseResultMap">
    select
    <if test="distinct">
      distinct
    </if>
    'false' as QUERYID,
    <include refid="Base_Column_List" />
    from authors
    <if test="_parameter != null">
      <include refid="Example_Where_Clause" />
    </if>
    <if test="orderByClause != null">
      order by ${orderByClause}
    </if>
  </select>

獲取資料的核心service如下，由於只做個簡單演示，就懶得寫成介面了。其中 streamDownload 方法即為stream取資料寫檔案的實現，它將以很低的記憶體佔用從MySQL獲取資料；此外還提供traditionDownload方法，它是一種傳統的下載方式，批次獲取全部資料，然後將每個物件寫入檔案。

@Service
public class AuthorsService {
    private final SqlSessionTemplate sqlSessionTemplate;
    private final AuthorsMapper authorsMapper;

    public AuthorsService(SqlSessionTemplate sqlSessionTemplate, AuthorsMapper authorsMapper) {
        this.sqlSessionTemplate = sqlSessionTemplate;
        this.authorsMapper = authorsMapper;
    }

    /**
     * stream讀資料寫檔案方式
     * @param httpServletResponse
     * @throws IOException
     */
    public void streamDownload(HttpServletResponse httpServletResponse)
            throws IOException {
        AuthorsExample authorsExample = new AuthorsExample();
        authorsExample.createCriteria();
        HashMap<String, Object> param = new HashMap<>();
        param.put("oredCriteria", authorsExample.getOredCriteria());
        param.put("orderByClause", authorsExample.getOrderByClause());
        CustomResultHandler customResultHandler = new CustomResultHandler(new DownloadProcessor (httpServletResponse));
        sqlSessionTemplate.select(
                "com.alphathur.mysqlstreamingexport.mapper.AuthorsMapper.streamByExample", param, customResultHandler);
        httpServletResponse.getWriter().flush();
        httpServletResponse.getWriter().close();
    }

    /**
     * 傳統下載方式
     * @param httpServletResponse
     * @throws IOException
     */
    public void traditionDownload(HttpServletResponse httpServletResponse)
            throws IOException {
        AuthorsExample authorsExample = new AuthorsExample();
        authorsExample.createCriteria();
        List<Authors> authors = authorsMapper.selectByExample (authorsExample);
        DownloadProcessor downloadProcessor = new DownloadProcessor (httpServletResponse);
        authors.forEach (downloadProcessor::processData);
        httpServletResponse.getWriter().flush();
        httpServletResponse.getWriter().close();
    }
}

下載的入口controller：

@RestController
@RequestMapping("download")
public class HelloController {
    private final AuthorsService authorsService;

    public HelloController(AuthorsService authorsService) {
        this.authorsService = authorsService;
    }

    @GetMapping("streamDownload")
    public void streamDownload(HttpServletResponse response)
            throws IOException {
        authorsService.streamDownload(response);
    }

    @GetMapping("traditionDownload")
    public void traditionDownload(HttpServletResponse response)
            throws IOException {
        authorsService.traditionDownload (response);
    }
}   

實體類對應的表結構建立語句：

CREATE TABLE `authors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `birthdate` date NOT NULL,
  `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10095 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

這裡有個問題：如何短時間內建立大批次測試資料到MySQL呢？一種方式是使用儲存過程 + 大殺器 select insert 語句！不太懂？

沒關係，且看我另一篇文章 MySQL如何生成大批次測試資料 你就會明白了。如果你懶得看，我這裡已經將生成的270多萬條測試資料上傳到網盤，你直接下載然後透過navicat匯入就好了。

• 連結：
• 提取碼：nrp0

有了測試資料，我們就可以直接測試了。先啟動專案，然後開啟jdk bin目錄下的 jconsole.exe

首先我們測試傳統方式下載檔案的記憶體佔用，直接瀏覽器訪問：。

可以看出，下載開始前記憶體佔用大概為幾十M，下載開始後記憶體佔用急速上升，峰值達到接近2.5G，即使是下載完成，堆記憶體也維持一個較高的佔用，這實在是太可怕了，如果生產環境敢這麼搞，不出意外肯定記憶體溢位。

接著我們測試stream方式檔案下載的記憶體佔用，瀏覽器訪問：，當下載開始後，記憶體佔用也會有一個明顯的上升，但是峰值才到500M。對比於上面的方式，記憶體佔用率足足降低了80%！怎麼樣，興奮了嗎！

我們再透過記事本開啟下載後的兩個檔案，發現內容沒有缺斤少兩，都是2727127行，完美！