Excel 海量數據導入數據庫實操技巧
前言
在當今數據驅動的時代，處理大規模數據是眾多企業和項目面臨的常見挑戰。其中，將百萬級數據從Excel導入到數據庫是一個典型的場景，它涉及到高效的數據讀取、優化的數據庫寫入以及一系列性能和錯誤處理問題。
面臨的挑戰
內存溢出
當處理百萬級數據時，直接將整個Excel文件加載到內存中進行處理是不現實的，這極有可能導致內存溢出錯誤。傳統的Excel處理方式，如Apache POI的某些實現，在面對大數據量時，由于需要將大量數據存儲在內存中，容易出現內存不足的情況。
性能瓶頸
百萬級數據的讀取和插入操作會非常耗時。單線程處理如此龐大的數據量，效率極其低下。從Excel讀取數據的速度、數據在內存中的處理速度以及向數據庫插入數據的速度，都可能成為性能瓶頸。此外，頻繁的數據庫交互，如逐條插入數據，會大大增加數據庫的負載，進一步降低整體性能。
異常處理
在數據讀取和導入過程中，可能會遇到各種異常情況。例如，Excel數據格式錯誤、數據不一致、數據庫連接異常、數據重復等問題。如何妥善處理這些異常，保證數據的完整性和導入過程的穩定性，是實現過程中需要重點考慮的問題。
技術選型
EasyExcel
EasyExcel采用了流式讀取技術，它不會將整個Excel文件一次性加載到內存中，而是按行從磁盤逐個讀取數據并解析。這種方式大大減少了內存的占用，使得處理百萬級數據成為可能。例如，在解析一個包含百萬行數據的Excel文件時，傳統的POI方式可能會因為內存不足而失敗，而EasyExcel可以高效地逐行處理數據，避免內存問題。
多線程處理
為了提升性能，引入多線程技術是關鍵。多線程可以應用在兩個主要場景：多線程讀取Excel文件和多線程實現數據插入。通過生產者 - 消費者模式，多個線程同時讀取Excel數據，并將讀取到的數據交給多個線程進行插入操作，從而提高整體的數據處理速度。例如，我們可以創建一個固定大小的線程池，每個線程負責讀取Excel文件中的一個Sheet頁數據，或者負責將一批數據插入到數據庫中。
數據庫批量插入
在數據插入方面，利用數據庫的批量插入功能可以顯著提升速度。相比于逐條插入數據，批量插入可以減少數據庫交互次數，降低數據庫負載。不同的數據庫有不同的批量插入方式，例如在 MySQL 中，可以使用INSERT INTO ... VALUES (...) , (...)這種語法形式進行批量插入；在Oracle中，可以使用INSERT ALL語法。同時，結合MyBatis等持久層框架，也可以方便地實現批量插入操作。
實現步驟
數據讀取
通過配置ReadListener，實現對Excel數據的逐行讀取。在ReadListener的invoke方法中，對每一行數據進行處理。例如：
public class MyDataModelListener extends AnalysisEventListener<MyDataModel> {
private static final int BATCH_SIZE = 1000;
private List<MyDataModel> batch = new ArrayList<>();
private MyDataService myDataService;
public MyDataModelListener(MyDataService myDataService) {
this.myDataService = myDataService;
}
@Override
public void invoke(MyDataModel data, AnalysisContext context) {
if (validateData(data)) {
batch.add(data);
} else {
// 處理無效數據，例如記錄日志或跳過
}
if (batch.size() >= BATCH_SIZE) {
processBatch();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
if (!batch.isEmpty()) {
processBatch();
}
}
private void processBatch() {
myDataService.saveBatch(batch);
batch.clear();
}
private boolean validateData(MyDataModel data) {
// 實現數據校驗邏輯
}
}如果Excel文件包含多個Sheet頁，可以通過線程池并發讀取各個Sheet。以下是實現并發讀取多個Sheet的示例代碼：
String filePath = "/users/yian/workspace/excel/test.xlsx";
// 需要讀取的sheet數量
int numberOfSheets = 20;
// 創建一個固定大小的線程池，大小與sheet數量相同
ExecutorService executor = Executors.newFixedThreadPool(numberOfSheets);
for (int sheetNo = 0; sheetNo < numberOfSheets; sheetNo++) {
int finalSheetNo = sheetNo;
executor.submit(() -> {
EasyExcel.read(filePath, MyDataModel.class, new MyDataModelListener(myDataService))
.sheet(finalSheetNo)
.doRead();
});
}
executor.shutdown();
try {
executor.awaitTermination(Long.MAX_VALUE, TimeUnit.NANOSECONDS);
} catch (InterruptedException e) {
e.printStackTrace();
}數據插入
在數據讀取過程中，當收集到一定數量的數據（例如1000條）后，將這些數據批量插入到數據庫中。可以使用MyBatis的批量插入功能，通過在Mapper.xml文件中配置批量插入語句來實現：
<insert id="saveBatch" parameterType="list">
INSERT INTO your_table (column1, column2, column3)
VALUES
<foreach collection="list" item="item" separator=",">
(#{item.column1}, #{item.column2}, #{item.column3})
</foreach>
</insert>錯誤處理
- 數據校驗：在數據讀取階段，對每一行數據進行格式校驗和業務邏輯校驗。例如，檢查數據是否符合特定的格式要求、是否滿足業務規則等。如果數據校驗失敗，記錄錯誤日志并跳過該數據，或者將錯誤數據存儲到一個單獨的表中，以便后續處理。
- 異常捕獲與處理：在數據讀取和插入過程中，捕獲可能出現的異常，如數據庫連接異常、插入失敗等。對于數據庫插入異常，可以采用事務回滾的方式保證數據的一致性。同時，記錄詳細的異常信息，以便排查問題。例如：
@Transactional
public void saveBatch(List<MyDataModel> dataList) {
try {
// 執行批量插入操作
myMapper.saveBatch(dataList);
} catch (Exception e) {
// 記錄異常日志
log.error("數據插入失敗", e);
// 回滾事務
throw new RuntimeException("數據插入失敗", e);
}
}
