Excel导入30万条数据和导出50万条数据方案

背景：最近在做一个功能改造，老系统Excel导入导出大批量数据，要求支持一次性导入30万条数据，一次性导出50万条数据，还需要对数据进行校验，另外对性能有要求，不能比老系统的性能差。和业务了解了老系统导入30万的数据耗时为10分钟左右，导出50万数据耗时1分钟左右。

        说实话刚开始了解需求的时候有点慌，之前没有做过这么大批量数据的导入导出，因为这是跳槽进公司的第一个需求，怕做不好得滚蛋。

-------------------------------------------分割线-------------------------------------------

项目技术栈：Springboot + Mybatis + Oracle + Redis + Apollo

家里的电脑配置，以下部分测试是使用家里的电脑进行：CPU：I5-7500 RAM：8G 硬盘：固态硬盘

一、导入Excel问题汇总及解决方案：

1、内存溢出、读取文件性能太差（和电脑的配置有关）

        a) 使用POI工具的常规读取Excel文件方式导致内存溢出

        POI读取Excel文件代码：

        FileInputStream in = new FileInputStream(file);
        Workbook wk = new XSSFWorkbook(in);

        测试的Excel文件大小为：12M，53万条数据

        代码测试结果：

        CPU和RAM资源被占用90%以上

        Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded

2、解决方案

        使用别人封装的工具

        
            com.monitorjbl
            xlsx-streamer
            2.0.0
        

        读取Excel文件代码：

        FileInputStream in = new FileInputStream(file);
        Workbook wk = StreamingReader.builder()
                .rowCacheSize(500)
                .bufferSize(2048)
                .open(in);

代码测试结果：

CPU和RAM资源占用分别为 40%、70%

2022-05-05 23:30:25.067[1651764625067] | INFO  | main | ExcelImportTask  - Excel文件大小12M,共530328条数据，解析Excel文件耗时13秒

3、校验数据是否已经存在数据库中

        主要校验导入文件中的三个唯一主键字段在数据库中是否存在，刚开始以为是主键的原因，查数据库校验会很快，所以使用循环遍历和组装in条件去和数据库查重，但是几十万的数据量就需要和数据库交互上万甚至上几十万次，因此性能是非常之差。

不可行的方案：

        3.1、循环遍历查重

        3.2、每1000条组装一次in条件查询（in条件Oracle最多支持1000个）

        3.3、使用多线程的方式查重（没试过，但是觉得不行，就算是多线程成也需要与数据库上万次交互）

        3.4、使用redis，表数据个月都会新增几十上百万的数据，所以使用缓存的方式也不可行

解决方案：临时表

使用临时表，创建一张临时表，把导入的数据先插入临时表，然后再使用主表和临时表进行查重，查重结束后删除临时表数据。

测试结果（使用公司电脑测试）：

        1、导入30万数据，存在100条重复数据，查重校验耗时是1秒左右

        2、同一批30万的数据，导入两次，重复数据为30万条，查重校验耗时大约30秒

4、大批量数据插入数据库性能

mybatis-plus有批量插入的API:saveBatch,每1000条插入一次数据库，经过测试插入30万条数据耗时大约再180-200秒左右，觉得性能太差，改用了原生JDBC的批量插入，经过测试插入30万条数据大约3-4秒

总结：

1、在导入数据量太大的情况下不能使用普通POI读取方式，因为会导致内存溢出，服务器资源被大量占用，读取性能巨差

2、需要与数据库查重不可频繁查询数据库，可使用临时表方案

3、批量插入可以使用原生JDBC批量插入

4、经过一系列优化，导入30万条数据整个任务耗时大约60秒，比老系统的导入性能提升了10倍

二、导出问题汇总及解决方案：

导出数据主要的性能瓶颈主要是在查询数据库的步骤，刚开始使用mybatis的方式分页查询，但是查询50万条数据需要耗时180秒左右，比老系统性能差3倍，不可行。

最后使用原生JDBC的方式查询，查询50万条数据大约耗时30-40秒左右

整个导出任务耗时大约60秒。

读取Excel大文件参考博客：poi 上传Excel如何处理大文件，避免内存溢出_小何开发的博客-CSDN博客_poi大文件导入内存溢出

结语：导入30万数据经过一些列优化，耗时约60秒，比老系统提升了10倍，导出耗时约60秒，和老系统持平。