一、寫在開頭
我們在上一篇文章中提到了透過EasyExcel處理Mysql百萬資料的匯入功能（一鍵看原文），當時我們經過測試資料的反覆測驗，100萬條放在excel中的資料，4個欄位的情況下，匯入資料庫，平均耗時500秒，這對於我們來說肯定難以接受，今天我們就來做一次效能最佳化。
二、效能瓶頸分析
一般的大資料量excel入庫的場景中，耗時大概在如下幾點裡：
- 耗時1： 百萬資料讀取，欄位數量，sheet頁個數，檔案體積；針對這種情況，我們要選擇分片讀取，選擇合適的集合儲存。
- 耗時2： 百萬資料的校驗，逐行分欄位校驗；這種情況的耗時會隨著欄位個數逐漸增加，目前我們的案例中不設計，暫不展開。
- 耗時3： 百萬資料的寫入；選擇合適的寫入方式，如Mybatis-plus的分批插入，採用多執行緒處理等。
三、針對耗時1進行最佳化
耗時2的場景我們在案例中並未用到，耗時1中針對百萬級資料的讀取，我們必然要選擇分片讀取，分片處理，這在我們上一篇文章中就已經採用了該方案，這裡透過實現EasyExcel的ReadListener頁面讀取監聽器，實現其invoke方法，在方法中我們增加BATCH_COUNT（單次讀取條數）配置，來進行分片讀取。讀取完後，我們一定要選擇合適的集合容器存放臨時資料，不同集合之間的增加資料效能存在差異這裡我們選擇ArrayList。
【最佳化前程式碼片段】
@Slf4j
@Service
public class EasyExcelImportHandler implements ReadListener<User> {
/*成功資料*/
private final CopyOnWriteArrayList<User> successList = new CopyOnWriteArrayList<>();
/*單次處理條數*/
private final static int BATCH_COUNT = 20000;
@Resource
private ThreadPoolExecutor threadPoolExecutor;
@Resource
private UserMapper userMapper;
@Override
public void invoke(User user, AnalysisContext analysisContext) {
if(StringUtils.isNotBlank(user.getName())){
successList.add(user);
return;
}
if(successList.size() >= BATCH_COUNT){
log.info("讀取資料：{}", successList.size());
saveData();
}
}
///
///
}
【最佳化後程式碼片段】
@Slf4j
@Service
public class EasyExcelImportHandler implements ReadListener<User> {
/*成功資料*/
// private final CopyOnWriteArrayList<User> successList = new CopyOnWriteArrayList<>();
private final List<User> successList = new ArrayList<>();
/*單次處理條數，有原來2萬變為10萬*/
private final static int BATCH_COUNT = 100000;
@Resource
private ThreadPoolExecutor threadPoolExecutor;
@Resource
private UserMapper userMapper;
@Override
public void invoke(User user, AnalysisContext analysisContext) {
if (StringUtils.isNotBlank(user.getName())) {
successList.add(user);
return;
}
//size是否為100000條:這裡其實就是分批.當資料等於10w的時候執行一次插入
if (successList.size() >= BATCH_COUNT) {
log.info("讀取資料：{}", successList.size());
saveData();
//清理集合便於GC回收
successList.clear();
}
}
///
///
}
這裡面我們主要做了2點最佳化，1）將原來的執行緒安全的CopyOnWriteArrayList換為ArrayList，前者雖然可保執行緒安全，但儲存資料效能很差；2）將原來單批次2000調整為100000，這個引數是因電腦而異的，並沒有最佳數值。
【注】：本文中的程式碼僅針對最佳化點貼出，完整程式碼參考文首中的上一篇文章連線哈！
四、針對耗時3進行最佳化
針對耗時3的處理方案，我們這裡準備了2個：JDBC分批插入+手動事務控制、多執行緒+Mybatis-Plus批次插入。
4.1 JDBC分批插入+手動事務控制
很多博文中都說mybatis批次插入效能低，有人建議使用原生的JDBC進行處理，那咱們就採用這種方案來測試一下。
首先我們既然要透過jdbc連線資料庫進行操作，那就先準備一個連線工具類吧
public class JdbcConnectUtil {
private static String driver;
private static String url;
private static String name;
private static String password;
/**
* 建立資料Properties集合物件載入載入配置檔案
*/
static {
Properties properties = new Properties();
try {
properties.load(JdbcConnectUtil.class.getClassLoader().getResourceAsStream("generator.properties"));
driver = properties.getProperty("jdbc.driverClass");
url = properties.getProperty("jdbc.connectionURL");
name = properties.getProperty("jdbc.userId");
password = properties.getProperty("jdbc.password");
Class.forName(driver);
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 獲取資料庫連線物件
* @return
* @throws Exception
*/
public static Connection getConnect() throws Exception {
return DriverManager.getConnection(url, name, password);
}
/**
* 關閉資料庫相關資源
* @param conn
* @param ps
* @param rs
*/
public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
try {
if (conn != null) conn.close();
if (ps != null) ps.close();
if (rs != null) rs.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public static void close(Connection conn, PreparedStatement ps) {
close(conn, ps, null);
}
public static void close(Connection conn, ResultSet rs) {
close(conn, null, rs);
}
}
有了工具類後，我們就可以在EasyExcelImportHandler類中進行JDBC匯入邏輯的實現啦。
/**
* jdbc+事務處理
*/
public void import4Jdbc(){
//分批讀取+JDBC分批插入+手動事務控制
Connection conn = null;
//JDBC儲存過程
PreparedStatement ps = null;
try {
//建立jdbc資料庫連線
conn = JdbcConnectUtil.getConnect();
//關閉事務預設提交
conn.setAutoCommit(false);
String sql = "insert into user (id,name, phone_num, address) values";
sql += "(?,?,?,?)";
ps = conn.prepareStatement(sql);
for (int i = 0; i < successList.size(); i++) {
User user = new User();
ps.setInt(1,successList.get(i).getId());
ps.setString(2,successList.get(i).getName());
ps.setString(3,successList.get(i).getPhoneNum());
ps.setString(4,successList.get(i).getAddress());
//將一組引數新增到此 PreparedStatement 物件的批處理命令中。
ps.addBatch();
}
//執行批處理
ps.executeBatch();
//手動提交事務
conn.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
//記得關閉連線
JdbcConnectUtil.close(conn,ps);
}
}
這裡我們透過PreparedStatement的addBatch()和executeBatch()實現JDBC的分批插入，然後用import4Jdbc()替換原來的savaData()即可。
經過多次匯入測試，這種方案的平均耗時為140秒。相比之前的500秒確實有了大幅度提升，但是2分多鐘仍然感覺有點慢。
4.2 多執行緒+Mybatis-Plus批次插入
我們知道Mybatis-Plus的IService中提供了saveBatch的批次插入方法，但經過檢視日誌發現Mybatis-Plus的saveBatch在最後還是迴圈呼叫的INSERT INTO語句！
這種情況下，測試多執行緒速度和單執行緒相差不大，所以需要實現真正的批次插入語句，兩種方式，一種是透過給Mybatis-Plus注入器，增強批次插入，一種是在xml檔案中自己拼接SQL語句，我們在這裡選用後一種，因為我們只做一個表，直接手寫xml很方便，如果是在企業開發時建議使用sql注入器實現（自定義SQL隱碼攻擊器實現DefaultSqlInjector，新增InsertBatchSomeColumn方法，透過使用InsertBatchSomeColumn方法批次插入。）。
【XML中手動批次插入】
<insert id="insertSelective" parameterType="java.util.List">
insert into user
(id,name, phone_num, address
)
values
<foreach collection="list" item="item" separator=",">
(#{item.id},#{item.name},#{item.phoneNum},#{item.address})
</foreach>
</insert>
在在EasyExcelImportHandler類中的saveData()方法中實現多執行緒批次插入。
/**
* 採用多執行緒讀取資料
*/
private void saveData() {
List<List<User>> lists = ListUtil.split(successList, 1000);
CountDownLatch countDownLatch = new CountDownLatch(lists.size());
for (List<User> list : lists) {
threadPoolExecutor.execute(() -> {
try {
userMapper.insertSelective(list.stream().map(o -> {
User user = new User();
user.setName(o.getName());
user.setId(o.getId());
user.setPhoneNum(o.getPhoneNum());
user.setAddress(o.getAddress());
return user;
}).collect(Collectors.toList()));
} catch (Exception e) {
log.error("啟動執行緒失敗,e:{}", e.getMessage(), e);
} finally {
//執行完一個執行緒減1,直到執行完
countDownLatch.countDown();
}
});
}
// 等待所有執行緒執行完
try {
countDownLatch.await();
} catch (Exception e) {
log.error("等待所有執行緒執行完異常,e:{}", e.getMessage(), e);
}
// 提前將不再使用的集合清空，釋放資源
successList.clear();
lists.clear();
}
經過多次匯入測試，100萬資料量匯入耗時平均在20秒，這就是一個很客觀且友好使用者的匯入功能啦，畢竟100萬的xlsx檔案，開啟都需要七八秒呢！
五、總結
OK！以上就是SpringBoot專案下，透過阿里開源的EasyExcel技術進行百萬級資料的匯入功能的最佳化步驟啦，由原來的500秒最佳化到20秒！
六、結尾彩蛋
如果本篇部落格對您有一定的幫助，大家記得留言+點贊+收藏呀。原創不易，轉載請聯絡Build哥！
如果您想與Build哥的關係更近一步，還可以關注“JavaBuild888”，在這裡除了看到《Java成長計劃》系列博文，還有提升工作效率的小筆記、讀書心得、大廠面經、人生感悟等等，歡迎您的加入！
