SpringBoot 實現(xiàn) MySQL 百萬級數(shù)據(jù)量導出并避免 OOM 的解決方案
前言
JPA實現(xiàn)百萬級數(shù)據(jù)導出
MyBatis實現(xiàn)百萬級數(shù)據(jù)導出
MyBatis實現(xiàn)百萬級數(shù)據(jù)導出的具體實例
前言
動態(tài)數(shù)據(jù)導出是一般項目都會涉及到的功能。它的基本實現(xiàn)邏輯就是從mysql查詢數(shù)據(jù)，加載到內(nèi)存，然后從內(nèi)存創(chuàng)建excel或者csv，以流的形式響應(yīng)給前端。
參考：
https://grokonez.com/spring-framework/spring-boot/excel-file-download-from-springboot-restapi-apache-poi-mysql。
SpringBoot下載excel基本都是這么干。
雖然這是個可行的方案，然而一旦mysql數(shù)據(jù)量太大，達到十萬級，百萬級，千萬級，大規(guī)模數(shù)據(jù)加載到內(nèi)存必然會引起OutofMemoryError。
要考慮如何避免OOM，一般有兩個方面的思路。
一方面就是盡量不做唄，先懟產(chǎn)品下面幾個問題?。?/p>
我們?yōu)槭裁匆獙С鲞@么多數(shù)據(jù)呢？誰傻到去看這么大的數(shù)據(jù)啊，這個設(shè)計是不是合理的呢？
怎么做好權(quán)限控制？百萬級數(shù)據(jù)導出你確定不會泄露商業(yè)機密？
如果要導出百萬級數(shù)據(jù)，那為什么不直接找大數(shù)據(jù)或者DBA來干呢？然后以郵件形式傳遞不行嗎？
為什么要通過后端的邏輯來實現(xiàn)，不考慮時間成本，流量成本嗎？
如果通過分頁導出，每次點擊按鈕只導2萬條，分批導出難道不能滿足業(yè)務(wù)需求嗎？
如果產(chǎn)品說 “甲方是爸爸，你去和甲方說啊”，“客戶說這個做出來，才考慮付尾款！”，如果客戶的確缺根筋要讓你這樣搞， 那就只能從技術(shù)上考慮如何實現(xiàn)了。
從技術(shù)上講，為了避免OOM，我們一定要注意一個原則：
不能將全量數(shù)據(jù)一次性加載到內(nèi)存之中。
全量加載不可行，那我們的目標就是如何實現(xiàn)數(shù)據(jù)的分批加載了。實事上，Mysql本身支持Stream查詢，我們可以通過Stream流獲取數(shù)據(jù)，然后將數(shù)據(jù)逐條刷入到文件中，每次刷入文件后再從內(nèi)存中移除這條數(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)項目對應(yīng)：
https://github.com/knes1/todo
核心注解如下，需要加入到具體的Repository之上。方法的返回類型定義成Stream。Integer.MIN_VALUE告訴jdbc driver逐條返回數(shù)據(jù)。
此外還需要在Stream處理數(shù)據(jù)的方法之上添加@Transactional(readOnly = true)，保證事物是只讀的。
同時需要注入
javax.persistence.EntityManager，通過detach從內(nèi)存中移除已經(jīng)使用后的對象。
MyBatis實現(xiàn)百萬級數(shù)據(jù)導出
MyBatis實現(xiàn)逐條獲取數(shù)據(jù)，必須要自定義ResultHandler，然后在mapper.xml文件中，對應(yīng)的select語句中添加fetchSize="-2147483648"。
最后將自定義的ResultHandler傳給SqlSession來執(zhí)行查詢，并將返回的結(jié)果進行處理。
MyBatis實現(xiàn)百萬級數(shù)據(jù)導出的具體實例
以下是基于MyBatis Stream導出的完整的工程樣例，我們將通過對比Stream文件導出和傳統(tǒng)方式導出的內(nèi)存占用率的差異，來驗證Stream文件導出的有效性。
我們先定義一個工具類DownloadProcessor，它內(nèi)部封裝一個HttpServletResponse對象，用來將對象寫入到csv。
然后通過實現(xiàn)
org.apache.ibatis.session.ResultHandler，自定義我們的ResultHandler，它用于獲取java對象，然后傳遞給上面的DownloadProcessor處理類進行寫文件操作：
實體類：
Mapper接口：
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)，它將以很低的內(nèi)存占用從MySQL獲取數(shù)據(jù)；此外還提供traditionDownload方法，它是一種傳統(tǒng)的下載方式，批量獲取全部數(shù)據(jù)，然后將每個對象寫入文件。
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：
("download")public class HelloController { ? ?private final AuthorsService authorsService; ? ?public HelloController(AuthorsService authorsService) { ? ? ? ?this.authorsService = authorsService;
? ?} ? ?("streamDownload") ? ?public void streamDownload(HttpServletResponse response)
? ? ? ? ? ?throws IOException {
? ? ? ?authorsService.streamDownload(response);
? ?} ? ?("traditionDownload") ? ?public void traditionDownload(HttpServletResponse response)
? ? ? ? ? ?throws IOException {
? ? ? ?authorsService.traditionDownload (response);
? ?}
} ?
實體類對應(yīng)的表結(jié)構(gòu)創(chuàng)建語句：
這里有個問題：如何短時間內(nèi)創(chuàng)建大批量測試數(shù)據(jù)到MySQL呢？一種方式是使用存儲過程 + 大殺器 select insert 語句！不太懂？
沒關(guān)系，且看我另一篇文章 MySQL如何生成大批量測試數(shù)據(jù) 你就會明白了。如果你懶得看，我這里已經(jīng)將生成的270多萬條測試數(shù)據(jù)上傳到網(wǎng)盤，你直接下載然后通過navicat導入就好了。
鏈接：
https://pan.baidu.com/s/1hqnWU2JKlL4Tb9nWtJl4sw提取碼：nrp0
有了測試數(shù)據(jù)，我們就可以直接測試了。先啟動項目，然后打開jdk bin目錄下的jconsole.exe
首先我們測試傳統(tǒng)方式下載文件的內(nèi)存占用，直接瀏覽器訪問：
http://localhost:8080/download/traditionDownload。
可以看出，下載開始前內(nèi)存占用大概為幾十M，下載開始后內(nèi)存占用急速上升，峰值達到接近2.5G，即使是下載完成，堆內(nèi)存也維持一個較高的占用，這實在是太可怕了，如果生產(chǎn)環(huán)境敢這么搞，不出意外肯定內(nèi)存溢出。
接著我們測試stream方式文件下載的內(nèi)存占用，瀏覽器訪問：
http://localhost:8080/download/streamDownload，當下載開始后，內(nèi)存占用也會有一個明顯的上升，但是峰值才到500M。對比于上面的方式，內(nèi)存占用率足足降低了80%！怎么樣，興奮了嗎！
我們再通過記事本打開下載后的兩個文件，發(fā)現(xiàn)內(nèi)容沒有缺斤少兩，都是2727127行，完美！
