背景

小李今天去面试

面试官：我看了你的简历，里面提到，你做过excel表格的数据导入到数据库，也做过数据库的数据导出到excel，你能讲讲你是怎么做的吗？用到些什么技术？有没有遇到什么难点？

小李：我们使用poi组件，导入：提前定义好excel表格的模板，填充好数据后，读取excel的表格数据，然后写入到数据库。导入：从数据库读取数据，然后写入到excel，然后生成文件，返回给前端，其中没遇到什么问题，性能挺好

面试官：如果数据量比较大，比如10w、100w，你想想，会遇到什么问题？

小李：这......，吧啦吧啦说了一堆

面试官：今天就到这，后面联系

问题分析

对于excel的读取、写入，实际工作中经常使用，当数据量小的时候，一切都那么丝滑，如果随着系统运行，数据越来越多，性能问题就体现了，具体有什么问题呢？

应用的内存

数据量上来以后，大量的数据读取在内存里面操作，如果数据库字段很多，内存占用就很会大，对于Java应用来说，会产生大量的GC，影响整个系统的吞吐量，严重的内存溢出，甚至宕机

数据库

持续的对数据库操作，也会造成数据库很大的压力

数据导出： 从数据库查询数据，你是一次全部查出来？还是分页查询？分页查询随着页数的越来越深，是否有性能的问题，这些都会给数据库造成很大的压力

数据导入： 从excel读取大量数据后，对数据库的插入操作，你是单条插入，还是批量插入，批量多少合适，这些都要考虑，不同策略性能也不一样

同步&异步

导入导出，选择是同步，还是异步，对系统至关重要

同步

点击导入导出，一直等待系统给我提示成功还是失败，如果数据少的情况，效果杠杠的，没什么问题。如果数据量非常大，比如10w以上，数据处理时间过长，人为的以为系统出了问题，再次刷新点击，上个任务没完成，新的任务又来，人为造成并发，还是数据的大量读写，瞬间数据库崩掉，系统也挂掉。

异步

点击导入导出，后端插一条导出导出的任务，异步执行，提示用户，导出导入任务已经在执行，同时记录下任务，让用户去任务列表看执行的情况。这样避免长时间等待，让用户误以为失败了，持续点击，造成并发。

异步还能实现限流，每种任务类型，当前只能允许多少人使用。比如：商品导出，最多允许三个任务同时执行，用户点击导出，查询任务列表，如果有在执行的任务大于三个，就提示用户当前有任务在运行，请稍后。

技术选型

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

特点：

• 快速 快速的读取excel中的数据。
• 简洁 映射excel和实体类，让代码变的更加简洁
• 大文件 在读写大文件的时候使用磁盘做缓存，更加的节约内存

项目地址：easyexcel

场景

pom文件

js

复制代码

<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.3.3</version></dependency>

Excel数据导入到数据库

10w数据的excel，一个sheet放入1w数据，一个sheet一个任务进行处理，可以使用线程池，提交到线程池进行处理

技术点：

• 数据插入操作要批量，杜绝一条数据一次提交到数据，造成数据库的压力。
• 多线程，一个线程读取一个sheet
• 异步 防止响应过慢，用户误判系统出错，多次重复操作上传。用户点击上传，立马返回提示用户，数据在导入，请前往任务列表查看任务进度

我这里演示，就创建两个线程处理，代码如下：

java

复制代码

/** * @BelongsProject: demo4 * @BelongsPackage: com.example.demo * @Author: kb * @CreateTime: 2023-12-06 17:45 * @Description: TODO * @Version: 1.0 */package com.example.demo;import com.alibaba.excel.EasyExcel;import com.alibaba.excel.context.AnalysisContext;import com.alibaba.excel.read.listener.ReadListener;import com.alibaba.excel.util.ListUtils;import com.alibaba.fastjson.JSON;import lombok.extern.slf4j.Slf4j;import java.util.List;/** * @ClassName DataDeal * @Description TODO * @Author Jiangnan Cui * @Date 2023/12/6 17:45 * @Version 1.0 */@Slf4jpublicclassDataDealImportimplementsRunnable{private String sheet;private String fileName; DataDealImport(String fileName,String sheet){this.sheet = sheet;this.fileName = fileName; }@Overridepublicvoidrun() { log.info("{},开始执行任务",sheet);// 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭 EasyExcel.read(fileName, DemoData.class, newReadListener<DemoData>() {/** * 单次缓存的数据量 */publicstaticfinalintBATCH_COUNT=100;/** *临时存储 */private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);@Overridepublicvoidinvoke(DemoData data, AnalysisContext context) { log.info("读取到一条数据{}", JSON.toJSONString(data));/** * 校验数据通过，添加到缓存里面，如果不符合的，可以日志记录,或者写新建一个excel，添加一列，失败的原因 * */ cachedDataList.add(data);if (cachedDataList.size() >= BATCH_COUNT) { saveData();// 存储完成清理 list cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT); } }@OverridepublicvoiddoAfterAllAnalysed(AnalysisContext context) { saveData(); }/** * 加上存储数据库 */privatevoidsaveData() { log.info("{}条数据，开始存储数据库！",cachedDataList.size());//批量插入到数据库 log.info("存储数据库成功！"); } }).sheet(sheet).doRead(); }publicstaticvoidmain(String[] args) {DataDealImportdataDeal=newDataDealImport("demo.xlsx","sheet1");Threadt1=newThread(dataDeal); t1.start();DataDealImportdataDeal2=newDataDealImport("demo.xlsx","sheet2"); Thread t2= newThread(dataDeal2); t2.start(); }}

导出数据库数据到Excel

技术点

• 异步 防止响应过慢，用户误判系统出错，多次点击导出。用户点击导出，立马返回提示用户，数据在导出中，请前往任务列表查看任务进度，并且下载 这个真的太重要了，我们系统因为这个，时不时就被干崩掉
• 数据库的深度分页优化 在偏移量非常大的时候，也就是翻页到很靠后的页面时，查询速度会变得越来越慢。

mysql深度分页优化

一般分页

在系统中需要进行分页操作时，我们通常会使用 LIMIT 加上偏移量的方式实现，语法格式如下

js

复制代码

SELECT ... FROM ... WHERE ... ORDERBY ... LIMIT ...

在有对应索引的情况下，这种方式一般效率还不错。但它存在一个让人头疼的问题，在偏移量非常大的时候，也就是翻页到很靠后的页面时，查询速度会变得越来越慢。

原因：MySQL 并不是跳过 OFFSET 行，而是取 OFFSET+N 行，然后放弃前 OFFSET 行，最后返回 N 行，当 OFFSET 特别大的时候，效率就非常的低下

解决方案

记录主键id位置，避免使用offset，导致深度分页效率低下 先获取第一页数据，记录id的位置

js

复制代码

select * from T limit 10

假如id是连续不中断的，那查询第一页后，当前id的位置为10

js

复制代码

第二页查询select * from T where id >10 limit 10第三页查询select * from T where id >20 limit 10第四页查询select * from T where id >30 limit 10第五页查询...............

以此类推，无论查询到多少页，性能都会很好

代码示例：

java

复制代码

publicstaticvoidmain(String[] args) {SpringApplicationapp=newSpringApplication(WebApplication.class);Environmentenv= app.run(args).getEnvironment(); logger.info("启动成功！！"); logger.info("地址: \thttp://127.0.0.1:{}", env.getProperty("server.port"));TUserMapperuserMapper= SpringUtil.getBean(TUserMapper.class);//计算总的数据量intcount= (int) userMapper.countByExample(null);//获取分页总数intqueryCount=50_0000;intpageCount= count % queryCount == 0 ? count / queryCount : count / queryCount + 1;//设置导出的文件名StringfileName="result.xlsx";//设置excel的sheet号码intsheetNo=1;//设置第一个sheet的名字StringsheetName="sheet-" + sheetNo;longstart= System.currentTimeMillis();// 创建writeSheetWriteSheetwriteSheet= EasyExcel.writerSheet(sheetNo, sheetName).build();//记录每次分页查询的最大值LongmaxId=null;//指定文件try (ExcelWriterexcelWriter= EasyExcel.write(fileName, TUser.class).build()) {//写入每一页分页查询的数据for (inti=1; i <= pageCount; i++) {// 分页去数据库查询数据 这里可以去数据库查询每一页的数据longqueryStart= System.currentTimeMillis();TUserExampleuserExample=newTUserExample();//如果是第一次则直接进行分页查询，反之基于上一次分页查询的分页定位实际偏移量，筛选前n条数据以达到分页效果if (i == 1) { PageHelper.startPage(i, queryCount, false); } elseif (maxId != null) { userExample.createCriteria().andIdGreaterThan(maxId); PageHelper.startPage(0, queryCount, false); } List<TUser> userList = userMapper.selectByExample(userExample);//更新下一次分页查询用的idif (CollUtil.isNotEmpty(userList)) { maxId = userList.get(userList.size() - 1).getId(); }longqueryEnd= System.currentTimeMillis(); logger.info("数据大小:{},写入sheet位置:{}，耗时:{}", userList.size(), sheetName, queryEnd - queryStart);longwriteStart= System.currentTimeMillis(); excelWriter.write(userList, writeSheet);longwriteEnd= System.currentTimeMillis(); logger.info("本次写入耗时:{}", writeEnd - writeStart);//如果% 2 == 0，则说明一个sheet写入了50*2即100w的数据，需要创建新的sheet进行写入if (i % 2 == 0) { sheetName = "sheet-" + (++sheetNo); writeSheet = EasyExcel.writerSheet(sheetNo, sheetName).build(); logger.info("写满一个sheet,切换到下一个sheet:{}", sheetName); } } }longtotal= System.currentTimeMillis() - start; logger.info("导出结束，总耗时:{}", total); }

总结

• 导入导出，经量异步处理
• 数据库，分页的优化，一定要
• 多线程加速，注意下：easyexcel的写入，不支持多线程，读是支持的

参考文章

基于EasyExcel实现百万级别数据导出

写作不易，刚好你看到，刚好对你有帮助，麻烦点点赞，有问题的留言讨论。