前言动态数据导出是一般项目都会涉及到的功能。它的基本实现逻辑就是从mysql查询数据加载到内存然后从内存创建excel或者csv以流的形式响应给前端。参考https://grokonez.com/spring-framework/spring-boot/excel-file-download-from-springboot-restapi-apache-poi-mysql。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。实现项目对应https://github.com/knes1/todo核心注解如下需要加入到具体的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) StreamTodo 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; filenametodos.csv); response.setCharacterEncoding(UTF-8); try(StreamTodo 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; filenamefileName); 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 { ListAuthors selectByExample(AuthorsExample example); ListAuthors streamByExample(AuthorsExample example); //以stream形式从mysql获取数据 }Mapper xml文件核心片段以下两条select的唯一差异就是在stream获取数据的方式中多了一条属性fetchSize-2147483648select idselectByExample parameterTypecom.alphathur.mysqlstreamingexport.domain.AuthorsExample resultMapBaseResultMap select if testdistinct distinct /if false as QUERYID, include refidBase_Column_List / from authors if test_parameter ! null include refidExample_Where_Clause / /if if testorderByClause ! null order by ${orderByClause} /if /select select idstreamByExample fetchSize-2147483648 parameterTypecom.alphathur.mysqlstreamingexport.domain.AuthorsExample resultMapBaseResultMap select if testdistinct distinct /if false as QUERYID, include refidBase_Column_List / from authors if test_parameter ! null include refidExample_Where_Clause / /if if testorderByClause ! 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(); HashMapString, 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(); ListAuthors authors authorsMapper.selectByExample (authorsExample); DownloadProcessor downloadProcessor new DownloadProcessor (httpServletResponse); authors.forEach (downloadProcessor::processData); httpServletResponse.getWriter().flush(); httpServletResponse.getWriter().close(); } }下载的入口controllerRestController 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) ) ENGINEInnoDB AUTO_INCREMENT10095 DEFAULT CHARSETutf8 COLLATEutf8_unicode_ci;这里有个问题如何短时间内创建大批量测试数据到MySQL呢一种方式是使用存储过程 大杀器 select insert 语句不太懂没关系且看我另一篇文章 MySQL如何生成大批量测试数据 你就会明白了。如果你懒得看我这里已经将生成的270多万条测试数据上传到网盘你直接下载然后通过navicat导入就好了。链接https://pan.baidu.com/s/1hqnWU2JKlL4Tb9nWtJl4sw提取码nrp0有了测试数据我们就可以直接测试了。先启动项目然后打开jdk bin目录下的jconsole.exe首先我们测试传统方式下载文件的内存占用直接浏览器访问http://localhost:8080/download/traditionDownload。可以看出下载开始前内存占用大概为几十M下载开始后内存占用急速上升峰值达到接近2.5G即使是下载完成堆内存也维持一个较高的占用这实在是太可怕了如果生产环境敢这么搞不出意外肯定内存溢出。接着我们测试stream方式文件下载的内存占用浏览器访问http://localhost:8080/download/streamDownload当下载开始后内存占用也会有一个明显的上升但是峰值才到500M。对比于上面的方式内存占用率足足降低了80%怎么样兴奋了吗我们再通过记事本打开下载后的两个文件发现内容没有缺斤少两都是2727127行完美