在我前年找实习的时候,遇到了面试官问我:mysql从excel导出百万级数据,该怎么做?我听到的第一反应是:我*,我哪去接触百万级的数据,你们导出的数据是什么?我还是一个才找实习工作的大学生啊。后来也有各种各样的八股文,介绍这种导入导出的优化,然而我拒绝囫囵吞枣式学习,背八股文的方式学习。shigen
也在这里实测了,在此先感谢蜗牛,为我提供了高质量的代码参考和分析案例。
新手和没做过这方面的程序员们别不好意思,我知道你们怎么想的。不就是查询数据写到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
。也明显的感觉到第二种代码更简单对吧。
这个也依旧的麻烦,有人说不就是把之前的操作反过来的吗?是的,但是性能处理不好,要么花费很长的时间,要么直接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------");
}
那么重点就在EasyExcelGeneralDataListener
里边:关于它的使用可以参考博客使用easyexcel读excel(实现通用listener)。我直接上shigen
的代码了。
以上就是Excel导入导出百万级数据的优化思路了。可以作为案例参考和代码模板的使用,代码地址在这里。也欢迎大家的评论交流。觉得文章不错的话,记得点赞、在看、转发、关注哈
。
与shigen
一起,每天不一样!
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。