如何实现百万级数据从Excel导入到数据库

1.背景

  我们要知道 导入功能数据量一旦大起来的话, 批量插入数据库, 就会出现很多问题,

        1. 比如说 excel文件会很大, 如果一次加载到内存中, 如果jvm设置的内存不足以支撑, 就会导致内存溢出

         2.百万级数据读取时, 我们进行业务操作, 导入到数据库这一操作, 插入的会导致很慢, 同时也需要考虑性能问题。

        3.在导入前置, 可能我们会要进行比较复杂的业务操作, 比如判断某个字段信息, 是否属于这个用户, 可能会遇到各种各样的问题, 我们需要妥善处理, 看是否进行回滚或者进行其他的操作, 保证业务的可靠以及严谨

2.内存溢出的问题

        日常使用的POI, 是一次性将所有的数据都加载到内存中, 肯定是不现实的，那么好的办法就是基于流式读取的方式进行分批处理。

        所以我们选用市面上比较成熟的方案, 比如easyexcel, 他相比于poi最大的优点就是不会一次性将所有的数据加载到内存中, 而是从磁盘上一行一行的读取数据, 然后再逐渐解析 放入对象中

 3.性能

        在百万级数据插入时, 如果用单线程的话肯定是很慢的，想要提升性能，那么就需要使用多线程。

        多线程的使用上涉及到两个场景，一个是用多线程进行文件的读取，另一个是用多线程实现数据的插入。这里就涉及到一个生产者-消费者的模式了，多个线程读取，然后多个线程插入，这样可以最大限度的提升整体的性能。

        而数据的插入，我们除了借助多线程之外，还可以同时使用数据库的批量插入的功能，这样就能更加的提升插入速度。

4.其他的逻辑处理

        在读取数据的过程中, 可能会遇到重复数据的问题, 数据的不一致, 或者日期时间等字段的格式错误等, 这些都是需要考虑的

         第一步就是对数据点准确性进行校验, 检查数据准备落库前一步的数据的校验, 进行异常的处理(比如说返回错误数据的那一行的数据, 填充到一个新的excel文件, 并且提示一个错误信息, 指导用户进行更正, 下次即可将错误的数据再次导入尝试)

5.具体的实现

        首先添加pom依赖

<dependencies>
    <!-- EasyExcel -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>最新的版本号</version>
    </dependency>

    <!-- 数据库连接和线程池 -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
</dependencies>

 实现多个sheet的代码实现

@Service
public class ExcelImporterService {

    @Autowired
    private MyDataService myDataService;
    
    public void doImport() {
        // Excel文件的路径
        String filePath = "test.xlsx";

        // 需要读取的sheet数量
        int numberOfSheets = 20;

        // 创建一个固定大小的线程池，大小与sheet数量相同
        ExecutorService executor = Executors.newFixedThreadPool(numberOfSheets);

        // 遍历所有sheets
        for (int sheetNo = 0; sheetNo < numberOfSheets; sheetNo++) {
            // 在Java lambda表达式中使用的变量需要是final
            int finalSheetNo = sheetNo;

            // 向线程池提交一个任务
            executor.submit(() -> {
                // 使用EasyExcel读取指定的sheet
                EasyExcel.read(filePath, MyDataModel.class, new MyDataModelListener(myDataService))
                         .sheet(finalSheetNo) // 指定sheet号
                         .doRead(); // 开始读取操作
            });
        }

        // 启动线程池的关闭序列
		executor.shutdown();

        // 等待所有任务完成，或者在等待超时前被中断
        try {
            executor.awaitTermination(Long.MAX_VALUE, TimeUnit.NANOSECONDS);
        } catch (InterruptedException e) {
            // 如果等待过程中线程被中断，打印异常信息
            e.printStackTrace();
        }
    }
}

实现 ReadListener

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import org.springframework.transaction.annotation.Transactional;
import java.util.ArrayList;
import java.util.List;

// 自定义的ReadListener，用于处理从Excel读取的数据
public class MyDataModelListener implements ReadListener<MyDataModel> {
    // 设置批量处理的数据大小
    private static final int BATCH_SIZE = 1000;
    // 用于暂存读取的数据，直到达到批量大小
    private List<MyDataModel> batch = new ArrayList<>();

    
    private MyDataService myDataService;

    // 构造函数，注入MyBatis的Mapper
    public MyDataModelListener(MyDataService myDataService) {
        this.myDataService = myDataService;
    }

    // 每读取一行数据都会调用此方法
    @Override
    public void invoke(MyDataModel data, AnalysisContext context) {
        //检查数据的合法性及有效性
        if (validateData(data)) {
            //有效数据添加到list中
            batch.add(data);
        } else {
            // 处理无效数据，例如记录日志或跳过
        }
        
        // 当达到批量大小时，处理这批数据
        if (batch.size() >= BATCH_SIZE) {
            processBatch();
        }
    }

    
    private boolean validateData(MyDataModel data) {
        // 调用mapper方法来检查数据库中是否已存在该数据
        int count = myDataService.countByColumn1(data.getColumn1());
        // 如果count为0，表示数据不存在，返回true；否则返回false
        if(count == 0){
        	return true;
        }
        
        // 在这里实现数据验证逻辑
        return false;
    }


    // 所有数据读取完成后调用此方法
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 如果还有未处理的数据，进行处理
        if (!batch.isEmpty()) {
            processBatch();
        }
    }

    // 处理一批数据的方法
    private void processBatch() {
        int retryCount = 0;
        // 重试逻辑
        while (retryCount < 3) {
            try {
                // 尝试批量插入
                myDataService.batchInsert(batch);
                // 清空批量数据，以便下一次批量处理
                batch.clear();
                break;
            } catch (Exception e) {
                // 重试计数增加
                retryCount++;
                // 如果重试3次都失败，记录错误日志
                if (retryCount >= 3) {
                    logError(e, batch);
                }
            }
        }
    }

   

    // 记录错误日志的方法
    private void logError(Exception e, List<MyDataModel> failedBatch) {
        // 在这里实现错误日志记录逻辑
        // 可以记录异常信息和导致失败的数据
    }
}

@Service
public class MyDataService{
    // MyBatis的Mapper，用于数据库操作
    @Autowired
    private MyDataMapper myDataMapper;
    
 	// 使用Spring的事务管理进行批量插入
    @Transactional(rollbackFor = Exception.class)
    public void batchInsert(List<MyDataModel> batch) {
        // 使用MyBatis Mapper进行批量插入
        myDataMapper.batchInsert(batch);
    }

    public int countByColumn1(String column1){
        return myDataMapper.countByColumn1(column1);
    }
    
}

剩下的批量插入的话 建议用首先mapper 不用mp中自带的batchinsert

<insert id="batchInsert" parameterType="list">
    INSERT INTO test_table_name (column1, column2, ...)
    VALUES 
    <foreach collection="list" item="item" index="index" separator=",">
        (#{item.column1}, #{item.column2}, ...)
    </foreach>
</insert>

<select id="countByColumn1" resultType="int">
    SELECT COUNT(*) FROM your_table WHERE column1 = #{column1}
</select>