在項目開發中往往需要使用到資料的導入和導出,導入就是從Excel中導入到DB中,而導出就是從DB中查詢資料然後使用POI寫到Excel上。
大資料的導入和導出,相信大家在日常的開發、面試中都會遇到。
很多問題隻要這一次解決了,總給複盤記錄,後期遇到同樣的問題就好解決了。好啦,廢話不多說開始正文!
1.傳統POI的的版本優缺點比較
其實想到資料的導入導出,理所當然的會想到apache的poi技術,以及Excel的版本問題。
- HSSFWorkbook
這個實作類是我們早期使用最多的對象,它可以操作Excel2003以前(包含2003)的所有Excel版本。在2003以前Excel的版本字尾還是.xls
- XSSFWorkbook
這個實作類現在在很多公司都可以發現還在使用,它是操作的Excel2003–Excel2007之間的版本,Excel的擴充名是.xlsx
- SXSSFWorkbook
這個實作類是POI3.8之後的版本才有的,它可以操作Excel2007以後的所有版本Excel,擴充名是.xlsx
HSSFWorkbook
它是POI版本中最常用的方式,不過:
- 它的缺點是 最多隻能導出 65535行,也就是導出的資料函數超過這個資料就會報錯;
- 它的優點是 不會報記憶體溢出。(因為資料量還不到7w是以記憶體一般都夠用,首先你得明确知道這種方式是将資料先讀取到記憶體中,然後再操作)
XSSFWorkbook
- 優點:這種形式的出現是為了突破HSSFWorkbook的65535行局限,是為了針對Excel2007版本的1048576行,16384列,最多可以導出104w條資料;
- 缺點:伴随的問題來了,雖然導出資料行數增加了好多倍,但是随之而來的記憶體溢出問題也成了噩夢。因為你所建立的book,Sheet,row,cell等在寫入到Excel之前,都是存放在記憶體中的(這還沒有算Excel的一些樣式格式等等),可想而知,記憶體不溢出就有點不科學了!!!
SXSSFWorkbook
從POI 3.8版本開始,提供了一種基于XSSF的低記憶體占用的SXSSF方式:
優點:
- 這種方式不會一般不會出現記憶體溢出(它使用了硬碟來換取記憶體空間,
- 也就是當記憶體中資料達到一定程度這些資料會被持久化到硬碟中存儲起來,而記憶體中存的都是最新的資料),
- 并且支援大型Excel檔案的建立(存儲百萬條資料綽綽有餘)。
缺點:
- 既然一部分資料持久化到了硬碟中,且不能被檢視和通路那麼就會導緻,
- 在同一時間點我們隻能通路一定數量的資料,也就是記憶體中存儲的資料;
- sheet.clone()方法将不再支援,還是因為持久化的原因;
- 不再支援對公式的求值,還是因為持久化的原因,在硬碟中的資料沒法讀取到記憶體中進行計算;
- 在使用模闆方式下載下傳資料的時候,不能改動表頭,還是因為持久化的問題,寫到了硬碟裡就不能改變了;
2.使用方式哪種看情況
經過了解也知道了這三種Workbook的優點和缺點,那麼具體使用哪種方式還是需要看情況的:
我一般會根據這樣幾種情況做分析選擇:
1、當我們經常導入導出的資料不超過7w的情況下,可以使用 HSSFWorkbook 或者 XSSFWorkbook都行;
2、當資料量查過7w并且導出的Excel中不牽扯對Excel的樣式,公式,格式等操作的情況下,推薦使用SXSSFWorkbook;
3、當資料量查過7w,并且我們需要操做Excel中的表頭,樣式,公式等,這時候我們可以使用 XSSFWorkbook 配合進行分批查詢,分批寫入Excel的方式來做;
3.百萬資料導入導出
想要解決問題我們首先要明白自己遇到的問題是什麼?
1、 我遇到的資料量超級大,使用傳統的POI方式來完成導入導出很明顯會記憶體溢出,并且效率會非常低;
2、 資料量大直接使用select * from tableName肯定不行,一下子查出來300w條資料肯定會很慢;
3、 300w 資料導出到Excel時肯定不能都寫在一個Sheet中,這樣效率會非常低;估計打開都得幾分鐘;
4、 300w資料導出到Excel中肯定不能一行一行的導出到Excel中。頻繁IO操作絕對不行;
5、 導入時300萬資料存儲到DB如果循環一條條插入也肯定不行;
6、導入時300w資料如果使用Mybatis的批量插入肯定不行,因為Mybatis的批量插入其實就是SQL的循環;一樣很慢。
解決思路:
針對1 :
其實問題所在就是記憶體溢出,我們隻要使用對上面介紹的POI方式即可,主要問題就是原生的POI解決起來相當麻煩。
經過查閱資料翻看到阿裡的一款POI封裝工具EasyExcel,上面問題等到解決;
針對2:
不能一次性查詢出全部資料,我們可以分批進行查詢,隻不過時多查詢幾次的問題,況且市面上分頁插件很多。此問題好解決。
針對3:
可以将300w條資料寫到不同的Sheet中,每一個Sheet寫一百萬即可。
針對4:
不能一行一行的寫入到Excel上,我們可以将分批查詢的資料分批寫入到Excel中。
針對5:
導入到DB時我們可以将Excel中讀取的資料存儲到集合中,到了一定數量,直接批量插入到DB中。
針對6:
不能使用Mybatis的批量插入,我們可以使用JDBC的批量插入,配合事務來完成批量插入到DB。即 Excel讀取分批+JDBC分批插入+事務。
3.1 模拟500w資料導出
需求:使用EasyExcel完成500w資料的導出。
500w資料的導出解決思路:
- 首先在查詢資料庫層面,需要分批進行查詢(比如每次查詢20w)
- 每查詢一次結束,就使用EasyExcel工具将這些資料寫入一次;
- 當一個Sheet寫滿了100w條資料,開始将查詢的資料寫入到另一個Sheet中;
- 如此循環直到資料全部導出到Excel完畢。
ps:我們需要計算Sheet個數,以及循環寫入次數。特别是最後一個Sheet的寫入次數
因為你不知道最後一個Sheet會寫入多少資料,可能是100w,也可能是25w因為我們這裡的500w隻是模拟資料,有可能導出的資料比500w多也可能少
ps:我們需要計算寫入次數,因為我們使用的分頁查詢,是以需要注意寫入的次數。
其實查詢資料庫多少次就是寫入多少次
準備工作
1.基于maven搭建springboot工程,引入easyexcel依賴,這裡我是用的時3.0版本
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
2.建立海量資料的sql腳本
CREATE TABLE dept( /*部門表*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ;
#建立表EMP雇員
CREATE TABLE emp
(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*編号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上級編号*/
hiredate DATE NOT NULL,/*入職時間*/
sal DECIMAL(7,2) NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*紅利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部門編号*/
) ;
#工資級别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2) NOT NULL,
hisal DECIMAL(17,2) NOT NULL
);
#測試資料
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
delimiter $
#建立一個函數,名字 rand_string,可以随機傳回我指定的個數字元串
create function rand_string(n INT)
returns varchar(255) #該函數會傳回一個字元串
begin
#定義了一個變量 chars_str, 類型 varchar(100)
#預設給 chars_str 初始值 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
# concat 函數 : 連接配接函數mysql函數
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end $
#這裡我們又自定了一個函數,傳回一個随機的部門号
create function rand_num( )
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $
#建立一個存儲過程, 可以添加雇員
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
#set autocommit =0 把autocommit設定成0
#autocommit = 0 含義: 不要自動送出
set autocommit = 0; #預設不送出sql語句
repeat
set i = i + 1;
#通過前面寫的函數随機産生字元串和部門編号,然後加入到emp表
insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
#commit整體送出所有sql語句,提高效率
commit;
end $
#添加8000000資料
call insert_emp(100001,8000000)$
#指令結束符,再重新設定為;
delimiter ;
3.實體類
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Emp implements Serializable {
@ExcelProperty(value = "員工編号")
private Integer empno;
@ExcelProperty(value = "員工名稱")
private String ename;
@ExcelProperty(value = "工作")
private String job;
@ExcelProperty(value = "主管編号")
private Integer mgr;
@ExcelProperty(value = "入職日期")
private Date hiredate;
@ExcelProperty(value = "薪資")
private BigDecimal sal;
@ExcelProperty(value = "獎金")
private BigDecimal comm;
@ExcelProperty(value = "所屬部門")
private Integer deptno;
}
4.vo類
@Data
public class EmpVo {
@ExcelProperty(value = "員工編号")
private Integer empno;
@ExcelProperty(value = "員工名稱")
private String ename;
@ExcelProperty(value = "工作")
private String job;
@ExcelProperty(value = "主管編号")
private Integer mgr;
@ExcelProperty(value = "入職日期")
private Date hiredate;
@ExcelProperty(value = "薪資")
private BigDecimal sal;
@ExcelProperty(value = "獎金")
private BigDecimal comm;
@ExcelProperty(value = "所屬部門")
private Integer deptno;
}
導出核心代碼
@Resource
private EmpService empService;
/**
* 分批次導出
*/
@GetMapping("/export")
public void export() throws IOException {
StopWatch stopWatch = new StopWatch();
stopWatch.start();
empService.export();
stopWatch.stop();
System.out.println("共計耗時: " + stopWatch.getTotalTimeSeconds()+"S");
}
public class ExcelConstants {
//一個sheet裝100w資料
public static final Integer PER_SHEET_ROW_COUNT = 1000000;
//每次查詢20w資料,每次寫入20w資料
public static final Integer PER_WRITE_ROW_COUNT = 200000;
}
@Override
public void export() throws IOException {
OutputStream outputStream =null;
try {
//記錄總數:實際中需要根據查詢條件進行統計即可
//LambdaQueryWrapper<Emp> lambdaQueryWrapper = new QueryWrapper<Emp>().lambda().eq(Emp::getEmpno, 1000001);
Integer totalCount = empMapper.selectCount(null);
//每一個Sheet存放100w條資料
Integer sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT;
//每次寫入的資料量20w,每頁查詢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));
ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
HttpServletResponse response = requestAttributes.getResponse();
outputStream = response.getOutputStream();
//必須放到循環外,否則會重新整理流
ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
//開始分批查詢分次寫入
for (int i = 0; i < sheetNum; i++) {
//建立Sheet
WriteSheet sheet = new WriteSheet();
sheet.setSheetName("測試Sheet1"+i);
sheet.setSheetNo(i);
//循環寫入次數: j的自增條件是當不是最後一個Sheet的時候寫入次數為正常的每個Sheet寫入的次數,如果是最後一個就需要使用計算的次數lastSheetWriteCount
for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
//分頁查詢一次20w
Page<Emp> page = empMapper.selectPage(new Page(j + 1 + oneSheetWriteCount * i, writeDataRows), null);
List<Emp> empList = page.getRecords();
List<EmpVo> empVoList = new ArrayList<>();
for (Emp emp : empList) {
EmpVo empVo = new EmpVo();
BeanUtils.copyProperties(emp, empVo);
empVoList.add(empVo);
}
WriteSheet writeSheet = EasyExcel.writerSheet(i, "員工資訊" + (i + 1)).head(EmpVo.class)
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
//寫資料
excelWriter.write(empVoList, writeSheet);
}
}
// 下載下傳EXCEL
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
// 這裡URLEncoder.encode可以防止浏覽器端導出excel檔案名中文亂碼 當然和easyexcel沒有關系
String fileName = URLEncoder.encode("員工資訊", "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
excelWriter.finish();
outputStream.flush();
} catch (IOException e) {
e.printStackTrace();
} catch (BeansException e) {
e.printStackTrace();
}finally {
if (outputStream != null) {
outputStream.close();
}
}
}
這是我電腦測試時記憶體占用和CPU使用情況,當然開了其他一些應用。
導出500w資料共計耗時,可以看到差不多400s左右,當然還要考慮業務複雜度已經電腦組態,我這裡隻是一個導出的demo并不涉及其他業務邏輯,在實際開發中可能時間會比這個更長一些
看下導出效果,我上面的腳本向插入了500w資料,100w一個sheet是以正好五個
3.2模拟500w資料導入
500W資料的導入解決思路
1、首先是分批讀取讀取Excel中的500w資料,這一點EasyExcel有自己的解決方案,我們可以參考Demo即可,隻需要把它分批的參數5000調大即可。
2、其次就是往DB裡插入,怎麼去插入這20w條資料,當然不能一條一條的循環,應該批量插入這20w條資料,同樣也不能使用Mybatis的批量插入語,因為效率也低。
3、使用JDBC+事務的批量操作将資料插入到資料庫。(分批讀取+JDBC分批插入+手動事務控制)
代碼實作
controller層測試接口
@Resource
private EmpService empService;
@GetMapping("/importData")
public void importData() {
String fileName = "C:\\Users\\asus\\Desktop\\員工資訊.xlsx";
//記錄開始讀取Excel時間,也是導入程式開始時間
long startReadTime = System.currentTimeMillis();
System.out.println("------開始讀取Excel的Sheet時間(包括導入資料過程):" + startReadTime + "ms------");
//讀取所有Sheet的資料.每次讀完一個Sheet就會調用這個方法
EasyExcel.read(fileName, new EasyExceGeneralDatalListener(empService)).doReadAll();
long endReadTime = System.currentTimeMillis();
System.out.println("------結束讀取Excel的Sheet時間(包括導入資料過程):" + endReadTime + "ms------");
System.out.println("------讀取Excel的Sheet時間(包括導入資料)共計耗時:" + (endReadTime-startReadTime) + "ms------");
}
Excel導入事件監聽
// 事件監聽
public class EasyExceGeneralDatalListener extends AnalysisEventListener<Map<Integer, String>> {
/**
* 處理業務邏輯的Service,也可以是Mapper
*/
private EmpService empService;
/**
* 用于存儲讀取的資料
*/
private List<Map<Integer, String>> dataList = new ArrayList<Map<Integer, String>>();
public EasyExceGeneralDatalListener() {
}
public EasyExceGeneralDatalListener(EmpService empService) {
this.empService = empService;
}
@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() {
empService.importData(dataList);
dataList.clear();
}
/**
* Excel中所有資料解析完畢會調用此方法
*
* @param: context
* @MethodName: doAfterAllAnalysed
* @return: void
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
saveData();
dataList.clear();
}
}
核心業務代碼
public interface EmpService {
void export() throws IOException;
void importData(List<Map<Integer, String>> dataList);
}
/*
* 測試用Excel導入超過10w條資料,經過測試發現,使用Mybatis的批量插入速度非常慢,是以這裡可以使用 資料分批+JDBC分批插入+事務來繼續插入速度會非常快
*/
@Override
public void importData(List<Map<Integer, String>> dataList) {
//結果集中資料為0時,結束方法.進行下一次調用
if (dataList.size() == 0) {
return;
}
//JDBC分批插入+事務操作完成對20w資料的插入
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 emp (`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) values";
sql += "(?,?,?,?,?,?,?,?)";
ps = conn.prepareStatement(sql);
//循環結果集:這裡循環不支援lambda表達式
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.setString(6, item.get(5));
ps.setString(7, item.get(6));
ps.setString(8, item.get(7));
//将一組參數添加到此 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");
} catch (Exception e) {
e.printStackTrace();
} finally {
//關連接配接
JDBCDruidUtils.close(conn, ps);
}
}
}
jdbc工具類
//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配置檔案
這裡我将檔案建立在類路徑下,需要注意的是連接配接mysql資料庫時需要指定rewriteBatchedStatements=true批處理才會生效,否則還是逐條插入效率較低,allowMultiQueries=true表示可以使sql語句中有多個insert或者update語句(語句之間攜帶分号),這裡可以忽略。
# druid.properties配置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/llp?autoReconnect=true&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true&rewriteBatchedStatements=true
username=root
password=root
initialSize=10
maxActive=50
maxWait=60000
測試結果
------開始讀取Excel的Sheet時間(包括導入資料過程):1674181403555ms------
200000條,開始導入到資料庫時間:1674181409740ms
2023-01-20 10:23:29.943 INFO 18580 --- [nio-8888-exec-1] com.alibaba.druid.pool.DruidDataSource : {dataSource-1} inited
200000條,結束導入到資料庫時間:1674181413252ms
200000條,導入用時:3512ms
200000條,開始導入到資料庫時間:1674181418422ms
200000條,結束導入到資料庫時間:1674181420999ms
200000條,導入用時:2577ms
.....
200000條,開始導入到資料庫時間:1674181607405ms
200000條,結束導入到資料庫時間:1674181610154ms
200000條,導入用時:2749ms
------結束讀取Excel的Sheet時間(包括導入資料過程):1674181610155ms------
------讀取Excel的Sheet時間(包括導入資料)共計耗時:206600ms------
這裡我删除裡部分日志,從列印結果可以看出,在我的電腦上導入500w資料差不多需要200多秒的時間。當然公司的業務邏輯很複雜,資料量也比較多,表的字段也比較多,導入和導出的速度會比現在測試的要慢一點。
4.總結
1.如此大批量資料的導出和導入操作,會占用大量的記憶體實際開發中還應限制操作人數。
2.在做大批量的資料導入時,可以使用jdbc手動開啟事務,批量送出。