读取excel大数据量详解

需求：导入大数据量excel文件到数据库（测试11MB，40w行数据）

首先说结论：都是大概时间，且其中有两个参数需要调，这里统一下参数大小。

监听器中的缓存list一次性存100000（测试过100000比30000快）
并行工具类中的size，一个线程一次性处理的数据量1000条数据（底层mabatis在连接sql时到1000，也会自动插入）（测试的1000比2000和100更快）

采用方法	耗时
2.x版本+单线程+saveBatch	74s
2.x版本+16线程+saveBatch（采用多线程）	17s
2.x版本+16线程+手写sql（优化sql）	14s
3.2.1+版本+16线程+手写sql（采用新版本）	10s

注：这是在本地数据库，如果是远程数据库（耗时会长一点，我测试的远程慢了一倍）

解决方案：

使用库：EasyExcel，相比poi、jxl更快。

官网介绍：EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。他能让你在不用考虑性能、内存的等因素的情况下，快速完成Excel的读、写等功能。（来自官网）

速度快原因：Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。
easyexcel重写了poi对07版Excel的解析，一个3M的excel用POI sax解析依然需要100M左右内存，改用easyexcel可以降低到几M，并且再大的excel也不会出现内存溢出；03版依赖POI的sax模式，在上层做了模型转换的封装，让使用者更加简单方便。（来自官网）

EasyExcel官方网址：https://easyexcel.opensource.alibaba.com/

优化：

代码就不在这里写了，可以直接参考官网，将一些优化手段和踩的坑说一下

示例表(student)：

字段名	字段类型
id	int
name	string
age	int

优化方案一：采用多线程

使用：在导入数据时，通过Java8中的stream流中的并行流来对数据进行多线程导入，工具类如下，使用方法代码如下
踩坑或疑问 ：本人电脑是16线程，我将线程参数设置为2，当我一次性用list接收30000时，一个线程插入10000数据时，确实是使用两个线程，但是当我把一个线程一次性插入1000时，线程参数不管设置为多少（大于1）时，16个线程全跑起来了，关于在运行时查看线程可以使用stream流中的peek()，已写入下面的工具类代码中。

// 工具类使用方法：第一个参数为数据集合，第二个为插入数据的方法，可自定义
InsertConsumerUtils.insertData(Student, studentService::saveBatch);

// 工具类代码
public class InsertConsumerUtils {
    /**
     * 每个长 SQL 插入的行数，可以根据数据库性能调整
     */
    private final static int SIZE = 1000;

    // 如果需要调整并发数目，修改下面方法的第二个参数即可
    static {
        System.setProperty("java.util.concurrent.ForkJoinPool.common.parallelism"
        					, "4");
    }

    /**
     * 插入方法
     *
     * @param list     插入数据集合
     * @param consumer 消费型方法，直接使用 mapper::method 方法引用的方式
     * @param <T>      插入的数据类型
     */
    public static <T> void insertData(List<T> list, Consumer<List<T>> consumer) {
        if (list == null || list.size() < 1) {
            return;
        }
        List<List<T>> streamList = new ArrayList<>();

        for (int i = 0; i < list.size(); i += SIZE) {
            int j = Math.min((i + SIZE), list.size());
            List<T> subList = list.subList(i, j);
            streamList.add(subList);
        }
        // 并行流使用的并发数是 CPU 核心数，不能局部更改。全局更改影响较大，斟酌
        streamList.parallelStream()
                .peek(ts -> System.out.println(Thread.currentThread().getName()))
                .forEach(consumer);
    }
}

优化方案二：sql优化，批量插入

刚开始使用的是MP中的saveBatch以为是批量插入，后面查看资料和源码发现，MP中的saveBatch是将一句一句的sql进行插入，也就是如下示例代码

// 举例说明，不用在意字段是什么
insert into table values (1, "张三", 18)
insert into table values (2, "李四", 19)
insert into table values (3, "王五", 10)

于是我们需要将sql变成真正意义上的批量插入，代码示例如下

// 举例说明，不用在意字段是什么
insert into student values (1, "张三“, 18),(2, "李四", 19),(3, "王五", 10)

说到这里，肯定有小伙伴有疑问，不知道写这个sql怎么办，mybatis中的代码如下：

<insert id="{写你的函数名}" useGeneratedKeys="true" keyProperty="unid" 
			parameterType="{写你entity类全路径}">
        insert into student(name,age)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.name}, #{item.age})
        </foreach>
    </insert>

正常来说代码就是这样，但是有个问题，就是excel导入的数据一般没有id，如果数据库id没有设置自动增长，就需要我们自己插入id，代码如下

<insert id="{写你的函数名}" useGeneratedKeys="true" keyProperty="unid" 
			parameterType="{写你的entity类全路径}">
		<selectKey keyProperty="id" resultType="{写你entity类全路径}" order="BEFORE">
            select if(max(id) is null,1, max(id) + 1 ) as id from student
        </selectKey>
        insert into student(id,name) values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.id}, #{item.name})
        </foreach>
    </insert>

但是这样又带来了一个问题，使用了多线程，导致生成的id会重复，所以在多线程下，需要手动生成id，并且手动设置，于是我们又需要一个工具类，利用雪花算法生成的19位id（大部分人用的这种id策略）
由于导致篇幅过长，将生成雪花id工具类查看下面一篇博客，链接如下：
雪花id生成工具类

使用方法如下：

student.setId(snowflakeIdWorker.nextId())

优化方案三：采用新版本

在官网上我们可以看到不同版本的速度描述：可以看到最新的版本在空间和时间上综合是最优的，一代版本一代神嘛，下面是整理官网给出的数据。

版本	速度
2.x	64M内存1分钟内读取75M(46W行25列)的Excel
3.0.2+版本	64M内存20秒读取75M(46W行25列)的Excel
3.2.1+版本	16M内存23秒读取75M(46W行25列)的Excel

刚开始由于公司项目中很多依赖和3.x版本有冲突，于是采用了2.x版本，后面学到了一个处理依赖冲突的方法（又get一个新技能），就是下载插件 Maven Helper，如何操作网上有很多文章，就不细说了，处理完依赖冲突后，就可以使用了。

问题：在2.x版本中，监听器是继承AnalysisEventListener，3.x中变成了实现ReadListener，刚开始没有跑成功，如果使用3.x版本，出错时可以考虑还是采用2.x版本中的继承AnalysisEventListener，网上关于ReadListener的博客也非常少，猜测可能是使用的人比较少吧。

// 2.x 监听器
public class DemoDataListener extends AnalysisEventListener<DemoData>

// 3.x 监听器
public class DemoDataListener implements ReadListener<DemoData>

其他：

1、官网有提到一个急速模式，经测试没有效果
开启急速模式：急速模式可以自己开启后测试下内存的占用，如果感觉符合预期可以直接开启。如果最大文件条数也就十几二十万，然后excel也就是十几二十M，而且不会有很高的并发，并且内存也较大。这种情况下可以考虑开启极速模式。

EasyExcel.read().readCache(new MapCache());

2、监听器中的缓存list和并行工具类中的size，一个是缓存一次性存多少，一个是每个线程插入的数量，这两个参数的改变会影响全局的速度，网上有一些说批量插入速度最快为20，我测试了发现也不是，网上很多文章也不是很准确，有兴趣的小伙伴可以自行去调整这两个参数，不同的场景，不同服务器每个人最合适的参数应该是不一样的。

注：本人也是刚刚接触这个easyExcel，如果有误欢迎指正，另外还有疑问的小伙伴可留言