厲害了！12秒將百萬資料透過EasyExcel匯入MySQL資料庫中

JavaBuild發表於2024-05-12
ExcelMySql資料庫

一、寫在開頭

我們在上一篇文章中提到了透過EasyExcel處理Mysql百萬資料的匯入功能（一鍵看原文），當時我們經過測試資料的反覆測驗，100萬條放在excel中的資料，4個欄位的情況下，匯入資料庫，平均耗時500秒，這對於我們來說肯定難以接受，今天我們就來做一次效能最佳化。

image

二、效能瓶頸分析

一般的大資料量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分多鐘仍然感覺有點慢。

image

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檔案，開啟都需要七八秒呢！
image

五、總結

OK！以上就是SpringBoot專案下，透過阿里開源的EasyExcel技術進行百萬級資料的匯入功能的最佳化步驟啦，由原來的500秒最佳化到20秒！

六、結尾彩蛋

如果本篇部落格對您有一定的幫助，大家記得留言+點贊+收藏呀。原創不易，轉載請聯絡Build哥！

image

如果您想與Build哥的關係更近一步，還可以關注“JavaBuild888”，在這裡除了看到《Java成長計劃》系列博文，還有提升工作效率的小筆記、讀書心得、大廠面經、人生感悟等等，歡迎您的加入！

image

相關文章