面试官：百万数据的导入导出解决方案，怎么设计？

远景

在项目开辟中常常需要利用到数据的导入和导出，导入就是从Excel中导入到DB中,而导出就是从DB中查询数据然后利用POI写到Excel上。

写本文的布景是由于在工作中碰到了大数据的导入和导出，题目既然来了逃窜不如干掉它！！！

只要这一次处理了，前期碰到一样的题目就好处理了。

空话不多说，起头撸起来！！！

1. 传统POI的版本优弱点

实在想到数据的导入导出，理所固然的会想到apache的poi技术，以及Excel的版本题目。

既然要做导入导出，那末我们就先来大致看一下传统poi技术的版本以及优弱点对照吧！

首先我们晓得POI中我们最熟悉的莫过于WorkBook这样一个接口，我们的POI版本也在更新的同时对这个几口的实现类做了更新：

• HSSFWorkbook：

这个实现类是我们早期利用最多的工具，它可以操纵Excel2003之前（包括2003）的一切Excel版本。在2003之前Excel的版本后缀还是.xls

• XSSFWorkbook：

这个实现类现在在很多公司都可以发现还在利用，它是操纵的Excel2003--Excel2007之间的版本，Excel的扩大名是.xlsx

• SXSSFWorkbook：

这个实现类是POI3.8以后的版本才有的,它可以操纵Excel2007今后的一切版本Excel,扩大名是.xlsx

大致晓得了我们在导入导出操纵的时辰会用到这样三个实现类以及他们可以操纵的Excel版本和后缀以后,我们就要从优弱点分析他们了。

HSSFWorkbook

它是POI版本中最常用的方式，不外:

• 它的弱点是 最多只能导出 65535行，也就是导出的数据函数跨越这个数据就会报错;

• 它的优点是 不会报内存溢出。（由于数据量还不到7w所之内存一般都够用，首先你得明白晓得这类方式是将数据先读取到内存中，然后再操纵）

XSSFWorkbook

• 优点：这类形式的出现是为了冲破HSSFWorkbook的65535行范围，是为了针对Excel2007版本的1048576行，16384列，最多可以导出104w条数据；

• 弱点：陪伴的题目来了，虽然导出数据行数增加了好多倍，可是随之而来的内存溢出题目也成了噩梦。由于你所建立的book，Sheet，row，cell等在写入到Excel之前，都是寄存在内存中的（这还没有算Excel的一些款式格式等等），不可思议，内存不溢出就有点不科学了！！！

SXSSFWorkbook

从POI 3.8版本起头，供给了一种基于XSSF的低内存占用的SXSSF方式：

优点：

• 这类方式不会一般不会出现内存溢出（它利用了硬盘来调换内存空间，

• 也就是当内存中数据到达一定水平这些数据会被持久化到硬盘中存储起来，而内存中存的都是最新的数据），

• 而且支持大型Excel文件的建立（存储百万条数据绰绰不足）。

弱点：

• 既然一部分数据持久化到了硬盘中，且不能被检察和拜候那末就会致使，

• 在同一时候点我们只能拜候一定数目的数据，也就是内存中存储的数据;

• sheet.clone()方式将不再支持,还是由于持久化的缘由;

• 不再支持对公式的求值，还是由于持久化的缘由，在硬盘中的数据没法读取到内存中停止计较；

• 在利用模板方式下载数据的时辰，不能修改表头，还是由于持久化的题目，写到了硬盘里就不能改变了；

2. 利用方式

经过领会也晓得了这三种Workbook的优点和弱点，那末具体利用哪类方式还是需要看情况的：

我一般会按照这样几种情况做分析挑选：

1、当我们经常导入导出的数据不跨越7w的情况下，可以利用 HSSFWorkbook 大概 XSSFWorkbook都行；

2、当数据量查过7w而且导出的Excel中不牵扯对Excel的款式，公式，格式等操纵的情况下，保举利用SXSSFWorkbook;

3、当数据量查过7w，而且我们需要操做Excel中的表头，款式，公式等，这时辰我们可以利用 XSSFWorkbook 配合停止分批查询，分批写入Excel的方式来做；

3. 百万数据导入导出（正菜）

铺垫也做了很多，那末现在起头讲讲我在工作中碰到的超百万数据的导入导出处理计划：

想要处理题目我们首先要大白自己碰到的题目是什么？

1、 我碰到的数据量超级大，利用传统的POI方式来完成导入导出很明显会内存溢出，而且效力会很是低；

2、 数据量大间接利用select * from tableName必定不可，一会儿查出来300w条数据必定会很慢；

3、 300w 数据导出到Excel时必定不能都写在一个Sheet中，这样效力会很是低；估量翻开都得几分钟；

4、 300w数据导出到Excel中必定不能一行一行的导出到Excel中。频仍IO操纵绝对不可；

5、 导入时300万数据存储到DB假如循环一条条插入也必定不可；

6、导入时300w数据假如利用Mybatis的批量插入必定不可，由于Mybatis的批量插入实在就是SQL的循环；一样很慢。

处理思绪：

实在题目地点就是内存溢出，我们只要利用对上面先容的POI方式即可，首要题目就是原生的POI处理起来相当麻烦。

经过查阅材料翻看到阿里的一款POI封装工具EasyExcel，上面题目等到处理；

不能一次性查询出全数数据，我们可以分批停止查询，只不外期多查询几次的题目，况且市道上分页插件很多。此题目好处理。

可以将300w条数据写到分歧的Sheet中，每一个Sheet写一百万即可。别的，保举公众 号Java精选，答复java口试，获得口试材料，支持在线刷题。

不能一行一行的写入到Excel上，我们可以将分批查询的数据分批写入到Excel中。

导入到DB时我们可以将Excel中读取的数据存储到调集合，到了一定数目，间接批量插入到DB中。

不能利用Mybatis的批量插入，我们可以利用JDBC的批量插入，配合事务来完成批量插入到DB。即 Excel读取分批+JDBC分批插入+事务。

3.1 EasyExcel 简介

附上GitHub地址：

https://github.com/alibaba/easyexcel

GitHub地址上教程和说明很具体，而且附带有读和写的demo代码，这里对它的先容我就不再具体说了。

至于EasyExcel底层怎样实现的这个还有待研讨。

3.2 300w数据导出

EasyExcel完成300w数据的导出。技术难点已经晓得了，接下来就是针对这一难点供给自己的处理思绪即可。

300w数据的导出处理思绪：

• 首先在查询数据库层面，需要分批停止查询（我利用的是每次查询20w）

• 每查询一次竣事，就利用EasyExcel工具将这些数据写入一次；

• 当一个Sheet写满了100w条数据，起头将查询的数据写入到另一个Sheet中；

• 如此循环直到数据全数导出到Excel终了。

留意：

1、我们需要计较Sheet个数，以及循环写入次数。出格是最初一个Sheet的写入次数

由于你不晓得最初一个Sheet选哟写入几大都据，能够是100w，也能够是25w由于我们这里的300w只是模拟数据，有能够导出的数据比300w多也能够少

2、我们需要计较写入次数，由于我们利用的分页查询，所以需要留意写入的次数。

实在查询数据库几多次就是写入几多次

下面是测试机设置

数据库我利用的是Oracle19C在网上查阅实在在数据量不跨越1亿的情况下，Mysql和Oracle的性能实在相差不大,跨越1亿,Oracle的各方面上风才会明显。

所以这里可以疏忽利用数据库对时候酿成的影响，利用mysql一样可以完成测试，不需要零丁安装Oracle。

此次测试在查询方面我利用的是rownum停止的模拟查询300w条数据，这类查询效力实在并不高，现实还有很多优化空间来加速查询速度，

如：明白查询具体字段，不要用星号，经常查询字段增加索引等尽能够进步查询效力，用时能够会更短。

下面是300w数据从DB导出到Excel所用时候

从上面成果可以看出，300w的数据导出时候用时2分15秒，而且这是在不适用实体作为映照的情况下，假如利用实体映照不适用循环封装的话速度会更快(固然这也是在没有设备表甲等其他表格款式的情况下)

综合来说速度还算可以。

在网上查了很多材料有一个博主测试利用EasyExcel导出102w数据用时105秒，具体可以看一下链接：

https://blog.csdn.net/u014299266/article/details/107790561

看一下导出结果：文件还是挺大的163M

经过测试EasyExcel还是挺快的，而且利用起来相当方便，作者还专门供给了关流方式，不需要我们手动去关流了，也避免了我们经常忘记关流致使的一系列题目。

导出测试就到这里，对于数据量小于300W的数据可以利用在一个Sheet中停止导出。这里就不再演示。

3.3 300w数据导入

代码不重要首先还是思绪

300W数据的导入处理思绪

1、首先是分批读取读取Excel中的300w数据，这一点EasyExcel有自己的处理计划，我们可以参考Demo即可，只需要把它分批的参数3000调大即可。我是用的20w；（一会儿代码一看就能大白）

2、其次就是往DB里插入，怎样去插入这20w条数据，固然不能一条一条的循环，应当批量插入这20w条数据，一样也不能利用Mybatis的批量插入语，由于效力也低。可以参考下面链接【Myabtis批量插入和JDBC批量插入性能对照】

3、利用JDBC+事务的批量操纵将数据插入到数据库。（分批读取+JDBC分批插入+手动事务控制）

https://www.cnblogs.com/wxw7blog/p/8706797.html

如图

下面是300w数据边读边写用时候:

大致计较一下:

从起头读取到中心分批导入再到法式竣事总共用时: (1623127964725-1623127873630)/1000=91.095秒

300w数据恰好是分15次插入综适用时:8209毫秒 也就是 8.209秒

计较可得300w数据读取时候为:91.095-8.209=82.886秒

成果不言而喻:

EasyExcel分批读取300W数据只用了 82.886秒

利用JDBC分批+事务操纵插入300w条数据综合只用时 8.209秒

看一下数据库的数据能否是真的存进去了300w

可以看到数据比导入前多了300W，测试很成功

具体我没有看网上其他人的测试情况，这工具一般也很少有人愿意测试，不外这个速度对于我那时处理公司大数据的导入和导出已经充足，固然公司的营业逻辑很复杂，数据量也比力多，表的字段也比力多，导入和导出的速度会比现在测试的要慢一点，可是也在人类能接管的范围之内。

4. 总结

此次工作中碰到的题目也给我留下了深入印象,同时也是我职业生活添彩的一笔。

最最少简历上可以写上你处置过上百万条数据的导入导出。

最初说一下公司之前怎样做的，公司之前做法是

限制了用户的下载数目每次最多只能有四小我同时下载，而且控制每个用户最大的导出数据最多只能是20w，与此同时他们也是利用的JDBC分批导入，可是并没有手动控制事务。

控制同时下载人数我可以了解，可是控制下载数据最多为20w就显得有点鸡肋了。

这也是我前期要处理的题目。

好了到此竣事，相信大神有比我做的更好的，对于EasyExcel内部究竟是怎样实现的还有待讲求（有空我再研讨研讨）。

作者：liu.kai

https://blog.csdn.net/weixin_44848900/article/details/117701981