【JAVA】一篇聊透百万级数据导入导出场景问题、大数据处理策略及优化方案、EasyExcel 和 EasyPOI的玩法详解

更多相关内容可查看

玩之前先了解几个关键名词（大佬可省略）：Apache POI、EasyExcel、EasyPOI

名词介绍

简单了解，详情可以扒拉官方文档

Apache POI：

官方文档：https://www.w3ccoo.com/apache_poi/apache_poi_overview.html

Apache POI是一个用于在Java程序中读写Microsoft Office文档（如Excel、Word和PowerPoint）的开源库

EasyExcel：

官方文档：https://easyexcel.opensource.alibaba.com/

EasyExcel是一个Java库，用于快速、高效地读写Excel文件。它特别适合处理大数据量的Excel文件，相比其他库，EasyExcel在处理大文件时更节省内存，也更简单易用。

EasyPOI

源码地址：https://github.com/jueyue/easypoi

处理Excel文件的Java库，设计得比较简单易用，使得操作Excel文件的工作变得不那么复杂

---

场景问题分析解决

百万级或者千万级数据量导入导出的场景面临的一些问题，拆开解决：

导入问题解决方案

内存溢出（分批导入）

问题：传统的Apache POI在读取Excel文件时会创建大量的Java对象来表示文件中的每一个单元格和行，当数据量超级大，使用传统的POI方式来完成导入会内存溢出，并且效率会非常低；

解决：EasyExcel通过流式读取和写入数据，只在内存中处理当前的数据块，避免了一次性加载整个文件，从而有效降低了内存消耗。分批读取读取Excel中的百万级的数据，这一点EasyExcel只需要把它分批的参数3000调大即可。我是用的20w；

EasyExcel底层采用了什么技术解决的这个问题：

• 基于流的API：EasyExcel使用了流式API来处理Excel文件。它使用了InputStream和OutputStream来逐步读取和写入数据，而不是一次性将整个文件加载到内存中。这样可以处理数据块，逐步读取和写入文件。
• 事件驱动模型：EasyExcel采用了事件驱动的方式，特别是在读取时，库会触发事件（如行读取事件），开发者可以在这些事件发生时处理数据。这样，只有当前正在处理的行会被加载到内存中，其他数据仍然保留在文件中。
• 按需加载：在读取过程中，EasyExcel只会加载当前需要处理的数据，而不是整个文件。它会在读取数据时动态地从磁盘加载数据块，然后处理完这些数据块后将其从内存中清除。
• 低级别的文件操作：EasyExcel使用了底层的文件操作技术，如BufferedInputStream，来高效地读取文件内容，减少内存占用和提高读取速度。

DB插入（分批插入）

问题：其次就是往DB里插入，怎么去插入这20w条数据，当然不能一条一条的循环，应该批量插入这20w条数据，同样也不能使用Mybatis的批量插入，因为效率也低。可以参考下面链接【Myabtis批量插入和JDBC批量插入性能对比】

解决：使用JDBC+事务的批量操作将数据插入到数据库。（分批读取+JDBC分批插入+手动事务控制）

核心代码：

// EasyExcel的读取Excel数据的API
@Test
public void import2DBFromExcel10wTest() {String fileName = "D:\\StudyWorkspace\\JavaWorkspace\\java_project_workspace\\idea_projects\\SpringBootProjects\\easyexcel\\exportFile\\excel300w.xlsx";//记录开始读取Excel时间,也是导入程序开始时间long startReadTime = System.currentTimeMillis();System.out.println("------开始读取Excel的Sheet时间(包括导入数据过程):" + startReadTime + "ms------");//读取所有Sheet的数据.每次读完一个Sheet就会调用这个方法EasyExcel.read(fileName, new EasyExceGeneralDatalListener(actResultLogService2)).doReadAll();long endReadTime = System.currentTimeMillis();System.out.println("------结束读取Excel的Sheet时间(包括导入数据过程):" + endReadTime + "ms------");
}
// 事件监听
public class EasyExceGeneralDatalListener extends AnalysisEventListener<Map<Integer, String>> {/*** 处理业务逻辑的Service,也可以是Mapper*/private ActResultLogService2 actResultLogService2;/*** 用于存储读取的数据*/private List<Map<Integer, String>> dataList = new ArrayList<Map<Integer, String>>();public EasyExceGeneralDatalListener() {}public EasyExceGeneralDatalListener(ActResultLogService2 actResultLogService2) {this.actResultLogService2 = actResultLogService2;}@Overridepublic void invoke(Map<Integer, String> data, AnalysisContext context) {//数据add进入集合dataList.add(data);//size是否为100000条:这里其实就是分批.当数据等于10w的时候执行一次插入if (dataList.size() >= ExcelConstants.GENERAL_ONCE_SAVE_TO_DB_ROWS) {//存入数据库:数据小于1w条使用Mybatis的批量插入即可;saveData();//清理集合便于GC回收dataList.clear();}}/*** 保存数据到DB** @param* @MethodName: saveData* @return: void*/private void saveData() {actResultLogService2.import2DBFromExcel10w(dataList);dataList.clear();}/*** Excel中所有数据解析完毕会调用此方法** @param: context* @MethodName: doAfterAllAnalysed* @return: void*/@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {saveData();dataList.clear();}
}
//JDBC工具类
public class JDBCDruidUtils {private static DataSource dataSource;/*创建数据Properties集合对象加载加载配置文件*/static {Properties pro = new Properties();//加载数据库连接池对象try {//获取数据库连接池对象pro.load(JDBCDruidUtils.class.getClassLoader().getResourceAsStream("druid.properties"));dataSource = DruidDataSourceFactory.createDataSource(pro);} catch (Exception e) {e.printStackTrace();}}/*获取连接*/public static Connection getConnection() throws SQLException {return dataSource.getConnection();}/*** 关闭conn,和 statement独对象资源** @param connection* @param statement* @MethodName: close* @return: void*/public static void close(Connection connection, Statement statement) {if (connection != null) {try {connection.close();} catch (SQLException e) {e.printStackTrace();}}if (statement != null) {try {statement.close();} catch (SQLException e) {e.printStackTrace();}}}/*** 关闭 conn , statement 和resultset三个对象资源** @param connection* @param statement* @param resultSet* @MethodName: close* @return: void*/public static void close(Connection connection, Statement statement, ResultSet resultSet) {close(connection, statement);if (resultSet != null) {try {resultSet.close();} catch (SQLException e) {e.printStackTrace();}}}/*获取连接池对象*/public static DataSource getDataSource() {return dataSource;}}
# druid.properties配置driverClassName=oracle.jdbc.driver.OracleDriverurl=jdbc:oracle:thin:@localhost:1521:ORCLusername=mrkaypassword=******initialSize=10maxActive=50maxWait=60000// Service中具体业务逻辑/*** 测试用Excel导入超过10w条数据,经过测试发现,使用Mybatis的批量插入速度非常慢,所以这里可以使用 数据分批+JDBC分批插入+事务来继续插入速度会非常快** @param* @MethodName: import2DBFromExcel10w* @return: java.util.Map<java.lang.String, java.lang.Object>*/@Overridepublic Map<String, Object> import2DBFromExcel10w(List<Map<Integer, String>> dataList) {HashMap<String, Object> result = new HashMap<>();//结果集中数据为0时,结束方法.进行下一次调用if (dataList.size() == 0) {result.put("empty", "0000");return result;}//JDBC分批插入+事务操作完成对10w数据的插入Connection conn = null;PreparedStatement ps = null;try {long startTime = System.currentTimeMillis();System.out.println(dataList.size() + "条,开始导入到数据库时间:" + startTime + "ms");conn = JDBCDruidUtils.getConnection();//控制事务:默认不提交conn.setAutoCommit(false);String sql = "insert into ACT_RESULT_LOG (onlineseqid,businessid,becifno,ivisresult,createdby,createddate,updateby,updateddate,risklevel) values";sql += "(?,?,?,?,?,?,?,?,?)";ps = conn.prepareStatement(sql);//循环结果集:这里循环不支持"烂布袋"表达式for (int i = 0; i < dataList.size(); i++) {Map<Integer, String> item = dataList.get(i);ps.setString(1, item.get(0));ps.setString(2, item.get(1));ps.setString(3, item.get(2));ps.setString(4, item.get(3));ps.setString(5, item.get(4));ps.setTimestamp(6, new Timestamp(System.currentTimeMillis()));ps.setString(7, item.get(6));ps.setTimestamp(8, new Timestamp(System.currentTimeMillis()));ps.setString(9, item.get(8));//将一组参数添加到此 PreparedStatement 对象的批处理命令中。ps.addBatch();}//执行批处理ps.executeBatch();//手动提交事务conn.commit();long endTime = System.currentTimeMillis();System.out.println(dataList.size() + "条,结束导入到数据库时间:" + endTime + "ms");System.out.println(dataList.size() + "条,导入用时:" + (endTime - startTime) + "ms");result.put("success", "1111");} catch (Exception e) {result.put("exception", "0000");e.printStackTrace();} finally {//关连接JDBCDruidUtils.close(conn, ps);}return result;}

---

导出问题解决方案

问题：如果一次性查询数据库百万条数据会很慢

解决：

• 首先在查询数据库层面，需要分批进行查询（我使用的是每次查询20w）
• 每查询一次结束，就使用EasyExcel工具将这些数据写入一次
• 当一个Sheet写满了100w条数据，开始将查询的数据写入到另一个Sheet中
• 如此循环直到数据全部导出到Excel完毕

注意：

1、我们需要计算Sheet个数，以及循环写入次数。特别是最后一个Sheet的写入次数

因为你不知道最后一个Sheet选哟写入多少数据，可能是100w，也可能是25w因为我们这里的300w只是模拟数据，有可能导出的数据比300w多也可能少

2、我们需要计算写入次数，因为我们使用的分页查询，所以需要注意写入的次数。

其实查询数据库多少次就是写入多少次

核心代码：

//导出逻辑代码
public void dataExport300w(HttpServletResponse response) {{OutputStream outputStream = null;try {long startTime = System.currentTimeMillis();System.out.println("导出开始时间:" + startTime);outputStream = response.getOutputStream();ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX);String fileName = new String(("excel100w").getBytes(), "UTF-8");//titleTable table = new Table(1);List<List<String>> titles = new ArrayList<List<String>>();titles.add(Arrays.asList("onlineseqid"));titles.add(Arrays.asList("businessid"));titles.add(Arrays.asList("becifno"));titles.add(Arrays.asList("ivisresult"));titles.add(Arrays.asList("createdby"));titles.add(Arrays.asList("createddate"));titles.add(Arrays.asList("updateby"));titles.add(Arrays.asList("updateddate"));titles.add(Arrays.asList("risklevel"));table.setHead(titles);//模拟统计查询的数据数量这里模拟100wint count = 3000001;//记录总数:实际中需要根据查询条件进行统计即可Integer totalCount = actResultLogMapper.findActResultLogByCondations(count);//每一个Sheet存放100w条数据Integer sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT;//每次写入的数据量20wInteger writeDataRows = ExcelConstants.PER_WRITE_ROW_COUNT;//计算需要的Sheet数量Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);//计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)Integer oneSheetWriteCount = sheetDataRows / writeDataRows;//计算最后一个sheet需要写入的次数Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));//开始分批查询分次写入//注意这次的循环就需要进行嵌套循环了,外层循环是Sheet数目,内层循环是写入次数List<List<String>> dataList = new ArrayList<>();for (int i = 0; i < sheetNum; i++) {//创建SheetSheet sheet = new Sheet(i, 0);sheet.setSheetName("测试Sheet1" + i);//循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCountfor (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {//集合复用,便于GC清理dataList.clear();//分页查询一次20wPageHelper.startPage(j + 1 + oneSheetWriteCount * i, writeDataRows);List<ActResultLog> reslultList = actResultLogMapper.findByPage100w();if (!CollectionUtils.isEmpty(reslultList)) {reslultList.forEach(item -> {dataList.add(Arrays.asList(item.getOnlineseqid(), item.getBusinessid(), item.getBecifno(), item.getIvisresult(), item.getCreatedby(), Calendar.getInstance().getTime().toString(), item.getUpdateby(), Calendar.getInstance().getTime().toString(), item.getRisklevel()));});}//写数据writer.write0(dataList, sheet, table);}}// 下载EXCELresponse.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes("gb2312"), "ISO-8859-1") + ".xlsx");response.setContentType("multipart/form-data");response.setCharacterEncoding("utf-8");writer.finish();outputStream.flush();//导出时间结束long endTime = System.currentTimeMillis();System.out.println("导出结束时间:" + endTime + "ms");System.out.println("导出所用时间:" + (endTime - startTime) / 1000 + "秒");} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} finally {if (outputStream != null) {try {outputStream.close();} catch (Exception e) {e.printStackTrace();}}}}
}

---

导入导出的策略

以下几点建议，不管在面试的角度还是架构设计的角度都尽量要考虑到

定时导入导出

1. 批量处理：定时任务通常处理大量数据，因此可以设计批量处理流程。例如，使用定时任务（如Cron）安排在低流量时段进行数据处理。

2. 增量更新：避免每次全量导入或导出，使用增量更新策略，仅处理自上次操作以来发生变化的数据。

3. 数据分片：将数据分成小块进行处理和存储，减少每次操作的负载。例如，按日期或记录ID范围分片处理。

4. 异常处理和恢复：实现任务失败后的重试机制，并记录失败日志，确保任务能够从失败点继续执行。

5. 性能优化：优化数据库查询和写入操作，例如使用批量插入或更新，提高导入导出的效率。

实时导入导出

1. 流式处理：实时数据处理需要即刻反应，因此使用流式处理技术（如Kafka、RabbitMQ）来处理数据流，确保数据能即时处理。

2. 数据缓存：使用缓存机制（如Redis）减少重复操作，提高响应速度，并减轻数据库负担。

3. 实时数据同步：使用数据同步工具或技术（如CDC—Change Data Capture）来实现实时数据更新，将数据及时同步到目标系统。

4. 负载均衡：实现系统负载均衡，确保在高流量情况下系统能够平稳运行，避免单点故障。

5. 监控和报警：实时监控数据流动和处理状态，设立报警机制，及时响应系统异常或数据处理问题。

在数据量不超过1亿的情况下，Mysql和Oracle的性能其实相差不大,超过1亿,Oracle的各方面优势才会明显

---

EasyPOI

最后浅聊一下EasyPOI的一些内容，他最大的特点就是用起来简单

1. 引入依赖：
   将 EasyPOI 依赖添加到项目的 pom.xml 中（对于 Maven 项目）：

   <dependency><groupId>cn.afterturn</groupId><artifactId>easypoi</artifactId><version>5.0.1</version>
   </dependency>
   

2. 定义实体类：
   创建一个 Java 类来表示 Excel 中的数据结构，并使用 EasyPOI 注解进行配置。例如：

   import cn.afterturn.easypoi.excel.annotation.Excel;public class Person {@Excel(name = "Name")private String name;@Excel(name = "Age")private int age;// Getters and setters
   }
   

3. 导出 Excel 文件：
   使用 EasyPOI 导出 Excel 文件：

   import cn.afterturn.easypoi.excel.ExcelExportUtil;
   import cn.afterturn.easypoi.excel.entity.ExportParams;
   import org.apache.poi.ss.usermodel.Workbook;public class ExportExample {public static void main(String[] args) {List<Person> list = Arrays.asList(new Person("John", 30), new Person("Jane", 25));Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("Person List", "Sheet1"), Person.class, list);try (FileOutputStream fos = new FileOutputStream("person.xlsx")) {workbook.write(fos);} catch (IOException e) {e.printStackTrace();}}
   }
   

4. 导入 Excel 文件：
   使用 EasyPOI 导入 Excel 文件：

   import cn.afterturn.easypoi.excel.ExcelImportUtil;
   import cn.afterturn.easypoi.excel.entity.ImportParams;
   import java.io.FileInputStream;
   import java.io.IOException;
   import java.util.List;public class ImportExample {public static void main(String[] args) {ImportParams params = new ImportParams();params.setHeadRows(1);try (FileInputStream fis = new FileInputStream("person.xlsx")) {List<Person> list = ExcelImportUtil.importExcel(fis, Person.class, params);for (Person person : list) {System.out.println(person.getName() + ": " + person.getAge());}} catch (Exception e) {e.printStackTrace();}}
   }
   

小数据量的导入导出就可以酌情怎么方便怎么来