SpringBoot 實現(xiàn) MySQL 百萬級數(shù)據(jù)量導出并避免 OOM 的解決方案

點擊關注公眾號，Java干貨 及時送達 ??

動態(tài)數(shù)據(jù)導出是一般項目都會涉及到的功能。它的基本實現(xiàn)邏輯就是從mysql查詢數(shù)據(jù)，加載到內存，然后從內存創(chuàng)建excel或者csv，以流的形式響應給前端。

• 參考：https://grokonez.com/spring-framework/spring-boot/excel-file-download-from-springboot-restapi-apache-poi-mysql。

SpringBoot下載excel基本都是這么干。

雖然這是個可行的方案，然而一旦mysql數(shù)據(jù)量太大，達到十萬級，百萬級，千萬級，大規(guī)模數(shù)據(jù)加載到內存必然會引起OutofMemoryError。

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

一方面就是盡量不做唄，先懟產品下面幾個問題?。?/p>

• 我們?yōu)槭裁匆獙С鲞@么多數(shù)據(jù)呢？誰傻到去看這么大的數(shù)據(jù)啊，這個設計是不是合理的呢？
• 怎么做好權限控制？百萬級數(shù)據(jù)導出你確定不會泄露商業(yè)機密？
• 如果要導出百萬級數(shù)據(jù)，那為什么不直接找大數(shù)據(jù)或者DBA來干呢？然后以郵件形式傳遞不行嗎？
• 為什么要通過后端的邏輯來實現(xiàn)，不考慮時間成本，流量成本嗎？
• 如果通過分頁導出，每次點擊按鈕只導2萬條，分批導出難道不能滿足業(yè)務需求嗎？

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

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

不能將全量數(shù)據(jù)一次性加載到內存之中。

全量加載不可行，那我們的目標就是如何實現(xiàn)數(shù)據(jù)的分批加載了。實事上，Mysql本身支持Stream查詢，我們可以通過Stream流獲取數(shù)據(jù)，然后將數(shù)據(jù)逐條刷入到文件中，每次刷入文件后再從內存中移除這條數(shù)據(jù)，從而避免OOM。

由于采用了數(shù)據(jù)逐條刷入文件，而且數(shù)據(jù)量達到百萬級，所以文件格式就不要采用excel了，excel2007最大才支持104萬行的數(shù)據(jù)。這里推薦：

以csv代替excel。

考慮到當前SpringBoot持久層框架通常為JPA和mybatis，我們可以分別從這兩個框架實現(xiàn)百萬級數(shù)據(jù)導出的方案。

JPA實現(xiàn)百萬級數(shù)據(jù)導出

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

實現(xiàn)項目對應：

• https://github.com/knes1/todo

核心注解如下，需要加入到具體的Repository之上。方法的返回類型定義成Stream。Integer.MIN_VALUE告訴jdbc driver逐條返回數(shù)據(jù)。

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

    

此外還需要在Stream處理數(shù)據(jù)的方法之上添加@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實現(xiàn)百萬級數(shù)據(jù)導出

MyBatis實現(xiàn)逐條獲取數(shù)據(jù)，必須要自定義ResultHandler，然后在mapper.xml文件中，對應的select語句中添加fetchSize="-2147483648"。

最后將自定義的ResultHandler傳給SqlSession來執(zhí)行查詢，并將返回的結果進行處理。

MyBatis實現(xiàn)百萬級數(shù)據(jù)導出的具體實例

以下是基于MyBatis Stream導出的完整的工程樣例，我們將通過對比Stream文件導出和傳統(tǒng)方式導出的內存占用率的差異，來驗證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();
????????}
????}
}

    

然后通過實現(xiàn)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獲取數(shù)據(jù)
}

    

Mapper xml文件核心片段，以下兩條select的唯一差異就是在stream獲取數(shù)據(jù)的方式中多了一條屬性：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>

    

獲取數(shù)據(jù)的核心service如下，由于只做個簡單演示，就懶得寫成接口了。其中 streamDownload 方法即為stream取數(shù)據(jù)寫文件的實現(xiàn)，它將以很低的內存占用從MySQL獲取數(shù)據(jù)；此外還提供traditionDownload方法，它是一種傳統(tǒng)的下載方式，批量獲取全部數(shù)據(jù)，然后將每個對象寫入文件。

      
      @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讀數(shù)據(jù)寫文件方式
?????*?@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();
????}

????/**
?????*?傳統(tǒng)下載方式
?????*?@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);
????}
}???

    

實體類對應的表結構創(chuàng)建語句：

      
      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;

    

這里有個問題：如何短時間內創(chuàng)建大批量測試數(shù)據(jù)到MySQL呢？一種方式是使用存儲過程 + 大殺器 select insert 語句！不太懂？

沒關系，且看我另一篇文章 MySQL如何生成大批量測試數(shù)據(jù) 你就會明白了。如果你懶得看，我這里已經將生成的270多萬條測試數(shù)據(jù)上傳到網(wǎng)盤，你直接下載然后通過navicat導入就好了。

• 鏈接：https://pan.baidu.com/s/1hqnWU2JKlL4Tb9nWtJl4sw
• 提取碼：nrp0

有了測試數(shù)據(jù)，我們就可以直接測試了。先啟動項目，然后打開jdk bin目錄下的 jconsole.exe

首先我們測試傳統(tǒng)方式下載文件的內存占用，直接瀏覽器訪問：http://localhost:8080/download/traditionDownload。

可以看出，下載開始前內存占用大概為幾十M，下載開始后內存占用急速上升，峰值達到接近2.5G，即使是下載完成，堆內存也維持一個較高的占用，這實在是太可怕了，如果生產環(huán)境敢這么搞，不出意外肯定內存溢出。

接著我們測試stream方式文件下載的內存占用，瀏覽器訪問：http://localhost:8080/download/streamDownload，當下載開始后，內存占用也會有一個明顯的上升，但是峰值才到500M。對比于上面的方式，內存占用率足足降低了80%！怎么樣，興奮了嗎！

我們再通過記事本打開下載后的兩個文件，發(fā)現(xiàn)內容沒有缺斤少兩，都是2727127行，完美！

感謝閱讀，希望對你有所幫助?:)? 來源：

blog.csdn.net/haohao_ding/article/details/123164771

1.?如何搭建一個永久運行的個人百度網(wǎng)盤？

2.?三分鐘教你如何用 Github 快速找到優(yōu)秀的開源項目 !

3.?別再分庫分表了，試試TiDB！

4.?這才是企業(yè)級的 oss-spring-boot-starter，真心牛x！

最近面試BAT，整理一份面試資料 《Java面試BATJ通關手冊》 ，覆蓋了Java核心技術、JVM、Java并發(fā)、SSM、微服務、數(shù)據(jù)庫、數(shù)據(jù)結構等等。

獲取方式：點“ 在看 ”，關注公眾號并回復? Java ?領取，更多內容陸續(xù)奉上。

PS：因公眾號平臺更改了推送規(guī)則，如果不想錯過內容，記得讀完點一下 “在看” ，加個 “星標” ，這樣每次新文章推送才會第一時間出現(xiàn)在你的訂閱列表里。

點“在看”支持小哈呀，謝謝啦