excel导入导出百万级数据优化

背景

在我前年找实习的时候，遇到了面试官问我：mysql从excel导出百万级数据，该怎么做？我听到的第一反应是：我*，我哪去接触百万级的数据，你们导出的数据是什么？我还是一个才找实习工作的大学生啊。后来也有各种各样的八股文，介绍这种导入导出的优化，然而我拒绝囫囵吞枣式学习，背八股文的方式学习。shigen也在这里实测了，在此先感谢蜗牛，为我提供了高质量的代码参考和分析案例。

分析

百万级数据导出Excel

循环导出

新手和没做过这方面的程序员们别不好意思，我知道你们怎么想的。不就是查询数据写到excel里边吗，看我的。先一条条的读取数据放在一个list里边，然后用Apache的POI写入excel，完了提供下载就可以了。

好坏喔在这里不做评论哈，自己心里肯定过意不去的。百万数据，我得运行多久！

批量查询导出

这种想法的技术就知道sql这一部分可以优化了，我们分批查询分批写入，然后汇总成一个Excel文件，直接下载。shigen就写一点伪代码吧。

Execel  excel = new Excel();
for (int i=0;i< page;i++) {
  List<data> data = getFromDB(i, pagesize);
  excel.write(data);
}
excel.close();

线程池走起

知道循环了，知道了循环里的方法都是一样，参数不一样了。那我就获得了一个消息；我可以用线程池了。但是，我excel的最终写入完成是需要知道的，CompletableFuture这就派上了用场。只有全部的任务完成之后，才会刷新流，标志着excel的写入完成。在此，看看shigen的代码设计吧。

线程池异步导出

循环导出

为什么还要提到这个呢，在批量查询导出中不是不建议循环，然后读取写入数据吗？是的，shigen确实是这样讲的。但是，如果你有以下的两种情况，也许这种方式是你的首选，也是最优解。

不会用异步任务，不会线程池
导出的数据主键ID是连续的

第一种情况就不多说了，首选，也是人思考解决问题的本能。我只说第二种。涉及到了sql的优化了。

select * from user limit 10, 1000;
select * from user where id>=10 limit 1000;

两种sql，你猜猜哪种效率会更高呢？shigen直接揭晓答案，知道原因的也欢迎在评论区交流。第二种效率更高。

那我第二种方式写的代码是这样的。

循环分页导出

那这两种方式我测试了一下，执行的时间分别是：271ms 125ms。也明显的感觉到第二种代码更简单对吧。

百万级数据导入Excel

这个也依旧的麻烦，有人说不就是把之前的操作反过来的吗？是的，但是性能处理不好，要么花费很长的时间，要么直接OOM了。

以下是shigen的分析：

从excel导入100万数据到mysql

首先是easyExcel分批读取Excel中的100w数据 EasyExcelGeneralDataListener按照sheet页一行行的数据读取
其次就是往DB里插入，怎么去插入这20w条数据，批量插入同样也不能使用Mybatis的批量插入，会读取数据到内存中，事务整体提交
使用JDBC+事务的批量操作将数据插入到数据库（分批读取+JDBC分批插入+手动事务控制）

分析的过程就是这样，那怎么实现呢？展示一下shigen写的代码：

    @GetMapping("/importExcel")
    public void importExcel(@RequestParam("file") MultipartFile file) throws IOException {
        if (file == null || file.isEmpty()) {
            throw new RuntimeException("file为空");
        }
        InputStream inputStream = file.getInputStream();
        // 记录开始读取Excel时间,也是导入程序开始时间
        long startReadTime = System.currentTimeMillis();
        log.info("------开始读取Excel的Sheet时间(包括导入数据过程):" + startReadTime + "ms------");
        // 读取所有Sheet的数据.每次读完一个Sheet就会调用这个方法
        EasyExcel.read(inputStream, new EasyExcelGeneralDataListener(userService)).doReadAll();
        long endReadTime = System.currentTimeMillis();
        log.info("------结束读取耗时" + (endReadTime - startReadTime) + "ms------");
    }