【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;
    }

    @Override
    public 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
     */
    @Override
    public 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.OracleDriver
    url=jdbc:oracle:thin:@localhost:1521:ORCL
        username=mrkay
        password=******
        initialSize=10
        maxActive=50
        maxWait=60000
        // Service中具体业务逻辑

        /**
 * 测试用Excel导入超过10w条数据,经过测试发现,使用Mybatis的批量插入速度非常慢,所以这里可以使用 数据分批+JDBC分批插入+事务来继续插入速度会非常快
 *
 * @param
 * @MethodName: import2DBFromExcel10w
 * @return: java.util.Map<java.lang.String, java.lang.Object>
 */
        @Override
        public 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");

            //title
            Table 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);

            //模拟统计查询的数据数量这里模拟100w
            int count = 3000001;
            //记录总数:实际中需要根据查询条件进行统计即可
            Integer totalCount = actResultLogMapper.findActResultLogByCondations(count);
            //每一个Sheet存放100w条数据
            Integer sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT;
            //每次写入的数据量20w
            Integer 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++) {
                //创建Sheet
                Sheet sheet = new Sheet(i, 0);
                sheet.setSheetName("测试Sheet1" + i);
                //循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount
                for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                    //集合复用,便于GC清理
                    dataList.clear();
                    //分页查询一次20w
                    PageHelper.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);
                }
            }

            // 下载EXCEL
            response.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();
           }
       }
   }
   

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