MyBatis 別再亂用 foreach 批量插入了,5000 數據用了 14 分鐘,實力勸退。。

来源:https://www.cnblogs.com/javastack/archive/2022/08/31/16643485.html
-Advertisement-
Play Games

近日,項目中有一個耗時較長的Job存在CPU占用過高的問題,經排查發現,主要時間消耗在往MyBatis中批量插入數據。mapper configuration是用foreach迴圈做的,差不多是這樣。(由於項目保密,以下代碼均為自己手寫的demo代碼) <insert id="batchInsert ...


近日,項目中有一個耗時較長的Job存在CPU占用過高的問題,經排查發現,主要時間消耗在往MyBatis中批量插入數據。mapper configuration是用foreach迴圈做的,差不多是這樣。(由於項目保密,以下代碼均為自己手寫的demo代碼)

<insert id="batchInsert" parameterType="java.util.List">
    insert into USER (id, name) values
    <foreach collection="list" item="model" index="index" separator=","> 
        (#{model.id}, #{model.name})
    </foreach>
</insert>

這個方法提升批量插入速度的原理是,將傳統的:

INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");

轉化為:

INSERT INTO `table1` (`field1`, `field2`) 
VALUES ("data1", "data2"),
("data1", "data2"),
("data1", "data2"),
("data1", "data2"),
("data1", "data2");

在MySql Docs中也提到過這個trick,如果要優化插入速度時,可以將許多小型操作組合到一個大型操作中。理想情況下,這樣可以在單個連接中一次性發送許多新行的數據,並將所有索引更新和一致性檢查延遲到最後才進行。

乍看上去這個foreach沒有問題,但是經過項目實踐發現,當表的列數較多(20+),以及一次性插入的行數較多(5000+)時,整個插入的耗時十分漫長,達到了14分鐘,這是不能忍的。在資料中也提到了一句話:

Of course don't combine ALL of them, if the amount is HUGE. Say you have 1000 rows you need to insert, then don't do it one at a time. You shouldn't equally try to have all 1000 rows in a single query. Instead break it into smaller sizes.

它強調,當插入數量很多時,不能一次性全放在一條語句里。可是為什麼不能放在同一條語句里呢?這條語句為什麼會耗時這麼久呢?

我查閱了資料發現:

Insert inside Mybatis foreach is not batch, this is a single (could become giant) SQL statement and that brings drawbacks:

  • some database such as Oracle here does not support.
  • in relevant cases: there will be a large number of records to insert and the database configured limit (by default around 2000 parameters per statement) will be hit, and eventually possibly DB stack error if the statement itself become too large.

Iteration over the collection must not be done in the mybatis XML. Just execute a simple Insertstatement in a Java Foreach loop. The most important thing is the session Executor type.

SqlSession session = sessionFactory.openSession(ExecutorType.BATCH);
for (Model model : list) {
    session.insert("insertStatement", model);
}
session.flushStatements();

Unlike default ExecutorType.SIMPLE, the statement will be prepared once and executed for each record to insert.

從資料中可知,預設執行器類型為Simple,會為每個語句創建一個新的預處理語句,也就是創建一個PreparedStatement對象。在我們的項目中,會不停地使用批量插入這個方法,而因為MyBatis對於含有<foreach>的語句,無法採用緩存,那麼在每次調用方法時,都會重新解析sql語句。

Internally, it still generates the same single insert statement with many placeholders as the JDBC code above.

MyBatis has an ability to cache PreparedStatement, but this statement cannot be cached because it contains <foreach /> element and the statement varies depending on the parameters. As a result, MyBatis has to 1) evaluate the foreach part and 2) parse the statement string to build parameter mapping [1] on every execution of this statement.

And these steps are relatively costly process when the statement string is big and contains many placeholders.

[1] simply put, it is a mapping between placeholders and the parameters.

從上述資料可知,耗時就耗在,由於我foreach後有5000+個values,所以這個PreparedStatement特別長,包含了很多占位符,對於占位符和參數的映射尤其耗時。並且,查閱相關資料可知,values的增長與所需的解析時間,是呈指數型增長的。

所以,如果非要使用 foreach 的方式來進行批量插入的話,可以考慮減少一條 insert 語句中 values 的個數,最好能達到上面曲線的最底部的值,使速度最快。一般按經驗來說,一次性插20~50行數量是比較合適的,時間消耗也能接受。

重點來了。上面講的是,如果非要用<foreach>的方式來插入,可以提升性能的方式。而實際上,MyBatis文檔中寫批量插入的時候,是推薦使用另外一種方法。(可以看 http://www.mybatis.org/mybatis-dynamic-sql/docs/insert.html 中 Batch Insert Support 標題里的內容)

SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
    SimpleTableMapper mapper = session.getMapper(SimpleTableMapper.class);
    List<SimpleTableRecord> records = getRecordsToInsert(); // not shown
 
    BatchInsert<SimpleTableRecord> batchInsert = insert(records)
            .into(simpleTable)
            .map(id).toProperty("id")
            .map(firstName).toProperty("firstName")
            .map(lastName).toProperty("lastName")
            .map(birthDate).toProperty("birthDate")
            .map(employed).toProperty("employed")
            .map(occupation).toProperty("occupation")
            .build()
            .render(RenderingStrategy.MYBATIS3);
 
    batchInsert.insertStatements().stream().forEach(mapper::insert);
 
    session.commit();
} finally {
    session.close();
}

即基本思想是將 MyBatis session 的 executor type 設為 Batch ,然後多次執行插入語句。就類似於JDBC的下麵語句一樣。

Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?useUnicode=true&characterEncoding=UTF-8&useServerPrepStmts=false&rewriteBatchedStatements=true","root","root");
connection.setAutoCommit(false);
PreparedStatement ps = connection.prepareStatement(
        "insert into tb_user (name) values(?)");
for (int i = 0; i < stuNum; i++) {
    ps.setString(1,name);
    ps.addBatch();
}
ps.executeBatch();
connection.commit();
connection.close();

經過試驗,使用了 ExecutorType.BATCH 的插入方式,性能顯著提升,不到 2s 便能全部插入完成。

總結一下,如果MyBatis需要進行批量插入,推薦使用 ExecutorType.BATCH 的插入方式,如果非要使用 <foreach>的插入的話,需要將每次插入的記錄控制在 20~50 左右。

參考資料

  1. https://dev.mysql.com/doc/refman/5.6/en/insert-optimization.html
  2. https://stackoverflow.com/questions/19682414/how-can-mysql-insert-millions-records-fast
  3. https://stackoverflow.com/questions/32649759/using-foreach-to-do-batch-insert-with-mybatis/40608353
  4. https://blog.csdn.net/wlwlwlwl015/article/details/50246717
  5. http://blog.harawata.net/2016/04/bulk-insert-multi-row-vs-batch-using.html
  6. https://www.red-gate.com/simple-talk/sql/performance/comparing-multiple-rows-insert-vs-single-row-insert-with-three-data-load-methods/
  7. https://stackoverflow.com/questions/7004390/java-batch-insert-into-mysql-very-slow
  8. http://www.mybatis.org/mybatis-dynamic-sql/docs/insert.html

原文鏈接:https://blog.csdn.net/huanghanqian/article/details/83177178

版權聲明:本文為CSDN博主「huanghanqian」的原創文章,遵循CC 4.0 BY-SA版權協議,轉載請附上原文出處鏈接及本聲明。

近期熱文推薦:

1.1,000+ 道 Java面試題及答案整理(2022最新版)

2.勁爆!Java 協程要來了。。。

3.Spring Boot 2.x 教程,太全了!

4.別再寫滿屏的爆爆爆炸類了,試試裝飾器模式,這才是優雅的方式!!

5.《Java開發手冊(嵩山版)》最新發佈,速速下載!

覺得不錯,別忘了隨手點贊+轉發哦!


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 《Python極客項目編程 》中文PDF完整版免費下載地址 ↑ ↑ ↑ ↑ ↑ ↑ ↑ 點擊即可下載 內容簡介 · · · · · · Python 是一種強大的編程語言,容易學習而且充滿樂趣。但掌握了基本知識後,接下來做什麼? 本書通過14個有趣的項目,幫助和鼓勵讀者探索Python編程的世界。 ...
  • 序言 哈嘍兄弟們,今天咱們來瞭解一下 fileinput 。 說到fileinput,可能90%的碼農表示沒用過,甚至沒有聽說過。 這不奇怪,因為在python界,既然open可以走天下,何必要fileinput呢? 但是,今天我還是要介紹fileinput這個方法,因為太奈斯了。 不止是香。是真香 ...
  • 函數是組織代碼的非常有效的方式,有了函數,我們就可以編寫大規模的項目。可以說,函數是組織代碼的最小單元。 Python函數的定義 函數是代碼封裝的一種手段,函數中包含一段可以重覆執行的代碼,在需要用到這些代碼時,只需要調用函數,就會運行函數中的代碼。 python 函數這麼定義: def 函數名稱( ...
  • 創作不易,感謝支持! fopen函數 頭文件:stdio.h 功能是打開一個文件,其聲明格式是: FILE *fopen(const char *filename, const char *mode); 文件指針名 = fopen(文件名,使用文件方式) “文件名”是被打開文件的文件名,類型是C風格 ...
  • GUI:Graphical User Interface(圖形用戶介面) 用圖形的方式,用來顯示電腦操作的界面 Java為GUI提供的API都存在java.awt和javax.Swing兩個包中 java.awt 包: awt是這三個單詞首字母的縮寫,翻譯過來是抽象視窗工具包,只不過這個包的API ...
  • (這裡寫自定義目錄標題)Java開發入門 博客內容是本人自學java過程,所以具體工具的下載步驟會省略。其中的部分下載和安裝步驟,引用了其他博主的相關文章。 Java語言 Java是目前世界上最流行的電腦編程語言,是一種可以編寫跨平臺應用軟體的面向對象的程式設計語言,也是當今使用率最高的編程語言。 ...
  • 前兩天從網上採集到一條短視頻數據(刷短視頻),發現六公主連排5部劉亦菲主演的電影!甚是震驚,太有牌面了,看了一下日子是8月25號,嗷,原來當天是劉亦菲的生日。巧了,正好也是我家柴犬旺財的3歲生日😀。 言歸正傳,我們看到這條數據的 標題:#劉亦菲35歲生日獲央視獨寵# 神仙姐姐生日快樂! 為了分析數 ...
  • 首先,進行springboot2.7之後,官方不推薦使用/META-INF/spring.factories,轉成和SPI比較類似的/META-INF/spring/org.springframework.boot.autoconfigure.AutoConfiguration.imports文件, ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...