百万数据的导入导出解决方案

素颜马尾好姑娘i 2024-04-03 13:25 43阅读 0赞

#### 百万数据的导入导出解决方案 ####

*  前言
 *  一、传统POI的的版本优缺点比较？
 *   *  HSSFWorkbook
     *  XSSFWorkbook
     *  SXSSFWorkbook
 *  二、正菜
 *   *  1.想要解决问题我们首先要明白自己遇到的问题是什么？
     *   *  300w数据的导出解决思路
         *  300w数据导入
 *  总结

--------------------

## 前言 ##

因为在工作中遇到了大数据的导入和导出，问题既然来了逃跑不如干掉它！！！

只要这一次解决了，后期遇到同样的问题就好解决了。

废话不多说，开始撸起来！！！

--------------------

## 一、传统POI的的版本优缺点比较？ ##

### 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()方法将不再支持,还是因为持久化的原因;
 *  不再支持对公式的求值，还是因为持久化的原因，在硬盘中的数据没法读取到内存中进行计算；
 *  在使用模板方式下载数据的时候，不能改动表头，还是因为持久化的问题，写到了硬盘里就不能改变了

；

## 二、正菜 ##

### 1.想要解决问题我们首先要明白自己遇到的问题是什么？ ###

1.  我遇到的数据量超级大，使用传统的POI方式来完成导入导出很明显会内存溢出，并且效率会非常低；
2.  数据量大直接使用select \* from tableName肯定不行，一下子查出来300w条数据肯定会很慢；
3.  300w 数据导出到Excel时肯定不能都写在一个Sheet中，这样效率会非常低；估计打开都得几分钟；
4.  300w数据导出到Excel中肯定不能一行一行的导出到Excel中。频繁IO操作绝对不行；
5.  导入时300万数据存储到DB如果循环一条条插入也肯定不行
6.  导入时300w数据如果使用Mybatis的批量插入肯定不行，因为Mybatis的批量插入其实就是SQL的循环；一样很慢。

**解决思路**

*  针对1 ：其实问题所在就是内存溢出，我们只要使用对上面介绍的POI方式即可，主要问题就是原生的POI解决起来相当麻烦。  
    经过查阅资料翻看到阿里的一款POI封装工具EasyExcel，上面问题等到解决；
 *  针对2：不能一次性查询出全部数据，我们可以分批进行查询，只不过时多查询几次的问题，况且市面上分页插件很多。此问题好解决。
 *  针对3：可以将300w条数据写到不同的Sheet中，每一个Sheet写一百万即可。
 *  针对4：不能一行一行的写入到Excel上，我们可以将分批查询的数据分批写入到Excel中。
 *  针对5：导入到DB时我们可以将Excel中读取的数据存储到集合中，到了一定数量，直接批量插入到DB中。
 *  针对6：不能使用Mybatis的批量插入，我们可以使用JDBC的批量插入，配合事务来完成批量插入到DB。即  
    Excel读取分批+JDBC分批插入+事务。

#### 300w数据的导出解决思路 ####

代码如下（示例）：

*  首先在查询数据库层面，需要分批进行查询（我使用的是每次查询20w）
 *  每查询一次结束，就使用EasyExcel工具将这些数据写入一次；
 *  当一个Sheet写满了100w条数据，开始将查询的数据写入到另一个Sheet中；
 *  如此循环直到数据全部导出到Excel完毕。

**300w的数据导出时间用时大概：2分15秒**

#### 300w数据导入 ####

代码不重要首先还是思路

1.  首先是分批读取读取Excel中的300w数据，这一点EasyExcel有自己的解决方案，我们可以参考Demo即可，只需要把它分批的参数3000调大即可。我是用的20w；（一会儿代码一看就能明白）
2.  其次就是往DB里插入，怎么去插入这20w条数据，当然不能一条一条的循环，应该批量插入这20w条数据，同样也不能使用Mybatis的批量插入语，因为效率也低。可以参考下面链接【Myabtis批量插入和JDBC批量插入性能对比】
3.  使用JDBC+事务的批量操作将数据插入到数据库。（分批读取+JDBC分批插入+手动事务控制）

EasyExcel分批读取300W数据只用了 82.886秒  
使用JDBC分批+事务操作插入300w条数据综合只用时 8.209秒

--------------------

## 总结 ##

这次工作中遇到的问题也给我留下了深刻印象,同时也是我职业生涯添彩的一笔。

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