使用Redis的发布与订阅，基于EasyExcel+线程池+批量插入如何实现百万级数据导入

一、Excel文件 → EasyExcel流式解析 → Redis发布消息 → Redis订阅消费 → 线程池批量处理 → 批量插入数据库

1、数据模型定义

@Data
public class UserData {
    @ExcelProperty("用户名")
    private String username;
    
    @ExcelProperty("年龄")
    private Integer age;
    
    @ExcelProperty("邮箱")
    private String email;
    
    @ExcelProperty("手机号")
    private String phone;
}

2、Redis配置

@Configuration
public class RedisConfig {
    
    @Bean
    public RedisTemplate<String, Object> redisTemplate(RedisConnectionFactory factory) {

        RedisTemplate<String, Object> template = new RedisTemplate<>();
        template.setConnectionFactory(factory);
        
        // 使用Jackson序列化
        Jackson2JsonRedisSerializer<Object> serializer = new Jackson2JsonRedisSerializer<>(Object.class);
        ObjectMapper mapper = new ObjectMapper();
        mapper.setVisibility(PropertyAccessor.ALL, JsonAutoDetect.Visibility.ANY);
        mapper.activateDefaultTyping(LazyCollectionOption.FALSE, ObjectMapper.DefaultTyping.NON_FINAL);
        serializer.setObjectMapper(mapper);
        
        template.setKeySerializer(new StringRedisSerializer());
        template.setValueSerializer(serializer);
        template.setHashKeySerializer(new StringRedisSerializer());
        template.setHashValueSerializer(serializer);
        return template;
    }
    
	
    @Bean
    public ChannelTopic topic() {
        return new ChannelTopic("excel:import:channel");
    }
}

3、EasyExcel监听器 + Redis发布者

@Component
@Slf4j
public class ExcelImportListener implements ReadListener<UserData> {
    
    @Autowired
    private RedisTemplate<String, Object> redisTemplate;
    
    @Autowired
    private ChannelTopic topic;
    
    // 批量大小
    private static final int BATCH_SIZE = 5000;
    private List<UserData> cacheList = new ArrayList<>(BATCH_SIZE);
    
	
    // 统计信息
    private AtomicLong totalCount = new AtomicLong(0);
    private AtomicLong publishCount = new AtomicLong(0);
    
	
    @Override
    public void invoke(UserData data, AnalysisContext context) {
	
        cacheList.add(data);
		
        totalCount.incrementAndGet();
        
        // 达到批量大小，发布到Redis
        if (cacheList.size() >= BATCH_SIZE) {
            publishBatch();
        }
    }
    
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 发布剩余数据
        if (!cacheList.isEmpty()) {
            publishBatch();
        }
        log.info("Excel解析完成，总数据量：{}，发布批次：{}", 
                 totalCount.get(), publishCount.get());
    }
    
    private void publishBatch() {
	
        if (cacheList.isEmpty()) {
            return;
        }
        
        // 创建批次消息
        ImportBatchMessage message = new ImportBatchMessage();
        message.setBatchId(UUID.randomUUID().toString());
        message.setBatchNumber(publishCount.incrementAndGet());
        message.setDataList(new ArrayList<>(cacheList));
        message.setTotalCount(totalCount.get());
        message.setTimestamp(System.currentTimeMillis());
        
		
        // 发布到Redis
        redisTemplate.convertAndSend(topic.getTopic(), message);
        log.info("发布批次：{}，数据量：{}", message.getBatchNumber(), cacheList.size());
        
		
        // 清空缓存
        cacheList.clear();
    }
}

4、批次消息实体

@Data
public class ImportBatchMessage implements Serializable {

    private String batchId;
	
    private Integer batchNumber;
	
    private List<UserData> dataList;
	
    private Long totalCount;
	
    private Long timestamp;
	
}

5、Redis订阅者 + 批量处理器

@Component
@Slf4j
public class ExcelImportSubscriber implements MessageListener {
    
    @Autowired
    private UserService userService;
    
	
    @Autowired
    private ThreadPoolTaskExecutor importExecutor;
    
	
    // 使用队列暂存待处理数据
    private BlockingQueue<List<UserData>> pendingQueue = new LinkedBlockingQueue<>();
    
	
    // 批量插入大小
    private static final int INSERT_BATCH_SIZE = 1000;
    
	
    @Override
    public void onMessage(Message message, byte[] pattern) {
        try {
		
            // 反序列化消息
            String json = new String(message.getBody(), StandardCharsets.UTF_8);
			
            ImportBatchMessage batchMessage = JSON.parseObject(json, ImportBatchMessage.class);
            
			
            // 提交到线程池处理
            importExecutor.submit(() -> processBatch(batchMessage));
            
        } catch (Exception e) {
            log.error("处理Redis消息失败", e);
        }
    }
    
    private void processBatch(ImportBatchMessage batchMessage) {
	
        List<UserData> dataList = batchMessage.getDataList();
		
        if (dataList == null || dataList.isEmpty()) {
            return;
        }
        
        log.info("开始处理批次：{}，数据量：{}", 
                 batchMessage.getBatchNumber(), dataList.size());
        
		
        // 分批插入数据库
        int totalSize = dataList.size();
        for (int i = 0; i < totalSize; i += INSERT_BATCH_SIZE) {
		
            int end = Math.min(i + INSERT_BATCH_SIZE, totalSize);
			
            List<UserData> subList = dataList.subList(i, end);
            
            try {
			
                userService.batchInsert(subList);
				
                log.info("批次：{}，已插入 {} 条数据", batchMessage.getBatchNumber(), subList.size());
            
			} catch (Exception e) {
			
                log.error("批次：{}，插入失败", batchMessage.getBatchNumber(), e);
				
                // 可以将失败数据记录到Redis或存入失败表
                handleFailedData(subList, e);
            }
        }
    }
    
    private void handleFailedData(List<UserData> failedList, Exception e) {
        // 记录失败数据，用于后续重试或人工处理
        // 可以存到Redis的List或专门的失败表
    }
}

6、线程池配置

@Configuration
public class ThreadPoolConfig {
    
    @Bean("importExecutor")
    public ThreadPoolTaskExecutor importExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(10);
        executor.setMaxPoolSize(20);
        executor.setQueueCapacity(1000);
        executor.setKeepAliveSeconds(60);
        executor.setThreadNamePrefix("import-");
        executor.setRejectedExecutionHandler(new ThreadPoolExecutor.CallerRunsPolicy());
        executor.initialize();
        return executor;
    }
}

7、服务层批量插入

@Service
@Slf4j
public class UserService {
    
    @Autowired
    private UserMapper userMapper;
    
	
    @Transactional(rollbackFor = Exception.class)
    public void batchInsert(List<UserData> dataList) {
	
        if (dataList == null || dataList.isEmpty()) {
            return;
        }
        
        // 使用MyBatis-Plus的批量插入
        long startTime = System.currentTimeMillis();
		
        boolean result = userMapper.insertBatch(dataList);
		
        log.info("批量插入 {} 条数据，耗时：{} ms", dataList.size(), System.currentTimeMillis() - startTime);
    }
}

8、MyBatis批量插入SQL

<!-- UserMapper.xml -->
<insert id="insertBatch" parameterType="list">
    INSERT INTO user (username, age, email, phone) VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.username}, #{item.age}, #{item.email}, #{item.phone})
    </foreach>
</insert>

9、导入 Controller 控制器

@RestController
@RequestMapping("/api/import")
@Slf4j
public class ImportController {

    
    @Autowired
    private ExcelImportListener importListener;
    
	
    @PostMapping("/excel")
    public R<String> importExcel(@RequestParam("file") MultipartFile file) {
        try {
		
            // 使用EasyExcel流式读取
            EasyExcel.read(file.getInputStream(), UserData.class, importListener)
                    .headRowNumber(1)
                    .sheet()
                    .doRead();
            
            return R.success("导入任务已提交，请查看进度");
			
        } catch (Exception e) {
            log.error("导入失败", e);
            return R.error("导入失败：" + e.getMessage());
        }
    }
}

关键优化点

1、内存优化

• EasyExcel流式读取，避免一次性加载全部数据

• 分批发布到 Redis，每批 5000 条

• 使用对象池或复用对象

2、性能优化参数

# application.yml
spring:
  redis:
    host: localhost
    port: 6379
    lettuce:
      pool:
        max-active: 20
        max-idle: 10
        min-idle: 5

# 自定义配置
import:
  batch:
    size: 5000          # Redis发布批次大小
    insert-size: 1000   # 数据库插入批次大小
  thread:
    core: 10            # 核心线程数
    max: 20             # 最大线程数
    queue: 1000         # 队列容量

异常处理策略

• 数据验证失败：记录到 Redis 的 List，后续单独处理

• 数据库插入失败：重试机制或记录到失败表

• Redis连接失败：本地缓存队列，恢复后重发

• 线程池满：使用CallerRunsPolicy策略，让主线程执行