背景
业务需求涉及将数据库中的数据导出为Excel格式的报表。需实现大规模数据的直接导出。目标是使该报表系统能够高效地处理并导出含有百万条记录级别的数据集。
现状
现阶段，系统采用单线程模式完成整个流程：首先读取数据库中的信息，然后对获取的数据进行必要的处理，最后将结果写入到Excel文件中。随着处理数据量的增长，尤其是在面对大量数据时，出现了显著的性能瓶颈——即所谓的“深分页”问题，这导致了查询及后续的数据写入操作变得异常缓慢。
优化方案
为了解决上述性能问题，用一种基于多线程技术的解决方案。具体来说，通过同时启动多个（例如三个）独立的工作线程来并行执行数据检索任务，从而在一定程度上缓解了单一请求带来的压力，并实现了用额外计算资源换取时间效率的目标。根据初步测试结果，在处理十万级别的数据集时，这种多线程方法相比传统单线程方式可以节省大约50%的时间成本。
这里写入文件的时候只能使用单线程。(Excel文件在被打开时会被锁定，其他进程可能无法同时写入该文件。)
- 定义线程池
//借鉴的初始化线程池
public class TaskThreadPool {
/*
* 并发比例
* */
public static final int concurrentRate = 3;
/*
* 核心线程数
* */
private static final int ASYNC_CORE_THREADS = 3, CONCURRENT_CORE_THREADS = ASYNC_CORE_THREADS * concurrentRate;
/*
* 最大线程数
* */
private static final int ASYNC_MAX_THREADS = ASYNC_CORE_THREADS + 1, CONCURRENT_MAX_THREADS = ASYNC_MAX_THREADS * concurrentRate;
/*
* 队列大小
* */
private static final int ASYNC_QUEUE_SIZE = 2000, CONCURRENT_QUEUE_SIZE = 20000;
/*
* 线程池的线程前缀
* */
public static final String ASYNC_THREAD_PREFIX = "excel-async-pool-", CONCURRENT_THREAD_PREFIX = "excel-concurrent-pool-";
/*
* 空闲线程的存活时间（单位秒），三分钟
* */
private static final int KEEP_ALIVE_SECONDS = 60 * 3;
/*
* 拒绝策略：如果队列、线程数已满，本次提交的任务返回给线程自己执行
* */
public static final ThreadPoolExecutor.AbortPolicy ASYNC_REJECTED_HANDLER =
new ThreadPoolExecutor.AbortPolicy();
public static final ThreadPoolExecutor.CallerRunsPolicy CONCURRENT_REJECTED_HANDLER =
new ThreadPoolExecutor.CallerRunsPolicy();
/*
* 异步线程池
* */
private volatile static ThreadPoolTaskExecutor asyncThreadPool, concurrentThreadPool;
/*
* DCL单例式懒加载：获取异步线程池
* */
public static ThreadPoolTaskExecutor getAsyncThreadPool() {
if (asyncThreadPool == null) {
synchronized (TaskThreadPool.class) {
if (asyncThreadPool == null) {
asyncThreadPool = new ThreadPoolTaskExecutor();
asyncThreadPool.setCorePoolSize(ASYNC_CORE_THREADS);
asyncThreadPool.setMaxPoolSize(ASYNC_MAX_THREADS);
asyncThreadPool.setQueueCapacity(ASYNC_QUEUE_SIZE);
asyncThreadPool.setKeepAliveSeconds(KEEP_ALIVE_SECONDS);
asyncThreadPool.setThreadNamePrefix(ASYNC_THREAD_PREFIX);
asyncThreadPool.setWaitForTasksToCompleteOnShutdown(true);
asyncThreadPool.setRejectedExecutionHandler(ASYNC_REJECTED_HANDLER);
asyncThreadPool.initialize();
return asyncThreadPool;
}
}
}
return asyncThreadPool;
}
/*
* DCL单例式懒加载：获取并发线程池
* */
public static ThreadPoolTaskExecutor getConcurrentThreadPool() {
if (concurrentThreadPool == null) {
synchronized (TaskThreadPool.class) {
if (concurrentThreadPool == null) {
concurrentThreadPool = new ThreadPoolTaskExecutor();
concurrentThreadPool.setCorePoolSize(CONCURRENT_CORE_THREADS);
concurrentThreadPool.setMaxPoolSize(CONCURRENT_MAX_THREADS);
concurrentThreadPool.setKeepAliveSeconds(KEEP_ALIVE_SECONDS);
concurrentThreadPool.setQueueCapacity(CONCURRENT_QUEUE_SIZE);
concurrentThreadPool.setThreadNamePrefix(CONCURRENT_THREAD_PREFIX);
concurrentThreadPool.setWaitForTasksToCompleteOnShutdown(true);
concurrentThreadPool.setRejectedExecutionHandler(CONCURRENT_REJECTED_HANDLER);
concurrentThreadPool.initialize();
return concurrentThreadPool;
}
}
}
return concurrentThreadPool;
}
}
2. 主要流程
try (SXSSFWorkbook workbook = new SXSSFWorkbook(100);
FileOutputStream fileOut = new FileOutputStream(filePath)) {
String[] reportHeaderArray = reportHeader.split(",");
String[] reportContentArray = reportContent.split(",");
StringBuilder sqlStr = buildSqlQuery(reportContentArray);
log.info("开始构建 Excel，总计数据行数: " + total);
int sheetNum = (total + SHEET_SIZE - 1) / SHEET_SIZE;
log.info("总 Sheet 数: " + sheetNum);
for (int i = 1; i <= sheetNum; i++) {
log.info("开始构建第 " + i + " 个sheet");
processSheet(workbook, dataSource, reportHeaderArray, sqlStr.toString(), queryText, dbName, i,uuId,total);
}
workbook.write(fileOut);
uploadFileToMinioAndSetProgress(filePath, reportName + formattedDate + ".xlsx", uuId);
} catch (Exception e) {
log.error("导出本地临时文件写流数据异常:", e);
throw new RuntimeException("导出本地临时文件异常", e);
} finally {
cleanUpTempFiles(uuId);
}
3. 核心逻辑
/**
* workbook: Excel工作簿对象。
* dataSource: 数据源对象，用于连接数据库。
* reportHeaderArray: 报告的列头数组。
* sqlStr: SQL查询字符串。
* queryText: 查询文本。
* dbName: 数据库名。
* sheetIndex: 当前工作表的索引。
* uuId: 唯一标识符，用于跟踪进度。
* total: 总记录数
*/
private void processSheet(SXSSFWorkbook workbook, DruidDataSource dataSource, String[] reportHeaderArray,
String sqlStr, String queryText, String dbName, int sheetIndex, String uuId,int total) throws InterruptedException, JSONException {
ThreadPoolTaskExecutor concurrentPool = TaskThreadPool.getConcurrentThreadPool();
// 创建一个新的工作表并写入列头。
Sheet sheet = workbook.createSheet("页码" + sheetIndex);
writeHeaderRow(sheet, reportHeaderArray);
// 参数初始化
// rowsPerFetch: 每次查询的行数。
// fetchCount: 每个工作表需要查询的次数。
// concurrentRate: 并发执行的任务数。
int sheetRow = 1;
int rowsPerFetch = 500; // 每次查询500条
int fetchCount = SHEET_SIZE / rowsPerFetch; // 每个Sheet页需要多少次查询
int concurrentRate = TaskThreadPool.concurrentRate; // 并发数
// 循环次数 = 总次数 / 并发数，确保每次并发执行
// 根据 fetchCount 和 concurrentRate 计算需要执行的轮数。
int rounds = (fetchCount + concurrentRate - 1) / concurrentRate; // 向上取整
// 遍历多轮数据获取与处理
for (int round = 0; round < rounds; round++) {
// 初始化计数器，用于同步线程
CountDownLatch countDownLatch = new CountDownLatch(concurrentRate);
// 用一个有序的Map来存储每轮的数据，key为fetchIndex，确保顺序
ConcurrentHashMap<Integer, JSONArray> jsonArrayMap = new ConcurrentHashMap<>();
// 提交concurrentRate个任务到线程池
for (int j = 0; j < concurrentRate; j++) {
// 计算当前任务的索引
int fetchIndex = round * concurrentRate + j;
// 如果索引超过需获取数据的总数，减少计数并继续
if (fetchIndex >= fetchCount) {
countDownLatch.countDown();
continue; // 超过fetchCount时，跳过不再提交任务
}
// 计算当前任务的起始ID
final int startId = fetchIndex * rowsPerFetch + (sheetIndex - 1) * SHEET_SIZE;
// 提交异步任务到线程池
concurrentPool.submit(() -> {
try (Connection con = dataSource.getConnection(30000)) {
// 执行数据获取和解析
JSONArray resultData = fetchAndParseData(sqlStr, queryText, startId, con, dbName);
if (resultData != null) {
// 将结果放入Map中，key为fetchIndex，保证按顺序存储
jsonArrayMap.put(fetchIndex, resultData);
}
} catch (SQLException | JSONException e) {
// 记录错误并抛出异常
log.error("获取数据时出错", e);
throw new RuntimeException(e);
} finally {
// 任务完成，计数减一
countDownLatch.countDown();
}
});
}
// 等待本轮所有任务完成
countDownLatch.await();
boolean allEmpty = jsonArrayMap.values().stream().allMatch(array -> array.length() == 0);
if(allEmpty){
break;
}
// 按fetchIndex的顺序写入数据到Sheet页 按照读取顺序写入
for (int index = round * concurrentRate; index < (round + 1) * concurrentRate && index < fetchCount; index++) {
JSONArray resultArray = jsonArrayMap.get(index);
if (resultArray != null) {
sheetRow = writeDataToSheet(sheet, resultArray, sheetRow); // 写入顺序保持一致
}
}
jsonArrayMap.clear();
// 更新进度
int offset = round * concurrentRate * rowsPerFetch + sheetIndex * SHEET_SIZE; // 当前的进度偏移量
updateProgress(offset, total, uuId); // 调用更新进度的方法 可以实时查看进度
}
}
主要流程
-
初始化
CountDownLatch和jsonArrayMap:CountDownLatch用于同步线程。jsonArrayMap用于存储每轮查询的结果。
-
提交任务到线程池:
- 对于每一轮的每个任务，计算起始ID并提交到线程池。
- 每个任务负责从数据库获取数据并解析成
JSONArray。
-
等待所有任务完成:
countDownLatch.await()等待所有子任务完成。
-
检查结果是否为空:
- 如果所有结果都为空，则跳出循环。
-
按顺序写入数据到Sheet页:
- 按照
fetchIndex顺序写入结果数据。
- 按照
-
更新进度:
- 更新当前进度并调用
updateProgress方法。
- 更新当前进度并调用
辅助方法
writeHeaderRow: 写入工作表的列头。writeDataToSheet: 将数据写入工作表。fetchAndParseData: 从数据库查询并解析数据。updateProgress: 更新进度信息。
三个线程导出数据库表测试的数据结果：
| 线程模式 | 数据量 (万) | 耗时 |
|---|---|---|
| 单线程 | 31 | 80秒 |
| 多线程 | 31 | 39秒 |
| 单线程 | 160 | 28分钟 |
| 多线程 | 160 | 10分钟 |
总结：
大文件的导出问题主要涉及以下几个关键方面：
-
处理效率：
- 内存管理：处理大文件时，一次性加载整个文件到内存中可能会导致内存溢出。因此，需要分块处理文件，每次只加载一部分数据。
-
及时响应：
- 进度反馈：定期向用户提供进度反馈，让用户知道操作正在进行并且预计还需要多久完成。
-
资源控制：
- 并发控制：避免过多的任务同时运行导致资源耗尽。可以使用线程池来控制并发任务的数量。
-
错误处理：
- 恢复机制：提供恢复机制，允许用户在出现问题时能够重新开始或从中断处继续。
-
性能考虑：
- 并行处理：利用多核处理器的优势，将任务分解为多个子任务并行处理。
这里主要优化了处理效率，对内存进行了管理，对导出后的临时文件清除，实现即时反馈，缺少相关的恢复机制。
恢复机制：通过定时器来扫描等待恢复的任务，通过启动器来恢复宕机丢失的任务。
