在當(dāng)今數(shù)據(jù)驅(qū)動(dòng)的時(shí)代，高效的數(shù)據(jù)處理尤其是數(shù)據(jù)插入操作對(duì)于各類應(yīng)用系統(tǒng)的性能表現(xiàn)至關(guān)重要。當(dāng)我們聚焦到 MyBatis 這個(gè)強(qiáng)大的框架時(shí)，探索其高效插入數(shù)據(jù)的方法就成為了提升系統(tǒng)效率的關(guān)鍵一環(huán)。

隨著業(yè)務(wù)的不斷發(fā)展和數(shù)據(jù)量的持續(xù)增長(zhǎng)，我們常常面臨著如何在保證數(shù)據(jù)準(zhǔn)確性的前提下，盡可能快速地將大量數(shù)據(jù)插入到數(shù)據(jù)庫(kù)中的挑戰(zhàn)。MyBatis 作為一款廣泛應(yīng)用的持久層框架，提供了多種途徑和策略來(lái)實(shí)現(xiàn)高效的插入操作。在接下來(lái)的文章中，我們將深入剖析 MyBatis 在高效插入數(shù)據(jù)方面的獨(dú)特之處，從基本原理到實(shí)際應(yīng)用技巧，逐一揭開其神秘面紗。無(wú)論是新手開發(fā)者還是經(jīng)驗(yàn)豐富的技術(shù)人員，都能從這里獲得對(duì) MyBatis 高效插入數(shù)據(jù)更深入的理解和實(shí)用的指引，從而為構(gòu)建更高效、更穩(wěn)定的系統(tǒng)奠定堅(jiān)實(shí)的基礎(chǔ)。

一、關(guān)于MySQL批量插入的一些問(wèn)題

MySQL一直是我們互聯(lián)網(wǎng)行業(yè)比較常用的數(shù)據(jù)，當(dāng)我們使用半ORM框架進(jìn)行MySQL大批量插入操作時(shí)，你是否考慮過(guò)這些問(wèn)題:

• 進(jìn)行大數(shù)據(jù)量插入時(shí)，是否需要進(jìn)行分批次插入，一次插入多少合適？有什么判斷依據(jù)？
• 使用foreach進(jìn)行大數(shù)據(jù)量的插入存在什么問(wèn)題？
• 如果插入批量插入過(guò)程中，因?yàn)榉?wù)器宕機(jī)等原因?qū)е虏迦胧∫趺崔k？

基于此類問(wèn)題，筆者以自己日常的開發(fā)手段作為依據(jù)演示一下MySQL批量插入的技巧。

二、常見的三種插入方式演示

1. 實(shí)驗(yàn)樣本數(shù)據(jù)

為了演示，這里給出一張示例表，除了id以外，有10個(gè)varchar字段，也就是說(shuō)全字段寫滿的話一條數(shù)據(jù)差不多1k左右：

CREATE TABLE `batch_insert_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `fileid_1` varchar(100) DEFAULT NULL,
  `fileid_2` varchar(100) DEFAULT NULL,
  `fileid_3` varchar(100) DEFAULT NULL,
  `fileid_4` varchar(100) DEFAULT NULL,
  `fileid_5` varchar(100) DEFAULT NULL,
  `fileid_6` varchar(100) DEFAULT NULL,
  `fileid_7` varchar(100) DEFAULT NULL,
  `fileid_8` varchar(100) DEFAULT NULL,
  `fileid_9` varchar(100) DEFAULT NULL,
  `fileid_10` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COMMENT='測(cè)試批量插入，一行數(shù)據(jù)1k左右';

2. 使用逐行插入

我們首先采用逐行插入方式分別插入3000、10w條的數(shù)據(jù)，這里為了保證實(shí)驗(yàn)的準(zhǔn)確性，提前進(jìn)行代碼預(yù)熱，先插入5條數(shù)據(jù)，然后在進(jìn)行大批量的插入:

/**
     * 逐行插入
     */
    @Test
    void rowByRowInsert() {
        //預(yù)熱先插入5條數(shù)據(jù)
        performCodeWarmUp(5);

        //生成10w條數(shù)據(jù)
        List<BatchInsertTest> testList = generateBatchInsertTestData();


        long start = System.currentTimeMillis();

        for (BatchInsertTest test : testList) {
            batchInsertTestMapper.insert(test);
        }

        long end = System.currentTimeMillis();
        log.info("逐行插入{}條數(shù)據(jù)耗時(shí):{}", BATCH_INSERT_SIZE, end - start);

    }

輸出結(jié)果如下，可以看到當(dāng)進(jìn)行3000條數(shù)據(jù)的逐條插入時(shí)耗時(shí)在3s左右:

逐行插入3000條數(shù)據(jù)耗時(shí):3492

而逐行插入10w條的耗時(shí)將其2min，插入表現(xiàn)可以說(shuō)是非常差勁：

05.988 INFO  c.s.w.WebTemplateApplicationTests:55   main                    逐行插入100000條數(shù)據(jù)耗時(shí):119678

3. 使用foreach語(yǔ)法實(shí)現(xiàn)批量插入

Mybatis為我們提供了foreach語(yǔ)法實(shí)現(xiàn)數(shù)據(jù)批量插入，從語(yǔ)法上不難看出，它會(huì)遍歷我們傳入的集合，生成一條批量插入語(yǔ)句，其語(yǔ)法格式大抵如下所示:

 insert into batch_insert_test (id, fileid_1, fileid_2, fileid_3, fileid_4, fileid_5, fileid_6, fileid_7, fileid_8, fileid_9, fileid_10) 
 values (1, '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),
 (2, '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),
 (3, '1', '2', '3', '4', '5', '6', '7', '8', '9', '10');

批量插入代碼如下所示：

 /**
     * foreach插入
     */
    @Test
    void forEachInsert() {
        /**
         * 代碼預(yù)熱
         */
        performCodeWarmUp(5);


        List<BatchInsertTest> testList = generateBatchInsertTestData();

        long start = System.currentTimeMillis();
        batchInsertTestMapper.batchInsertTest(testList);
        long end = System.currentTimeMillis();

        log.info("foreach{}條數(shù)據(jù)耗時(shí):{}", BATCH_INSERT_SIZE, end - start);

    }

對(duì)應(yīng)xml配置如下：

<!-- 插入數(shù)據(jù) -->
  <insert id="batchInsertTest" parameterType="java.util.List">
    INSERT INTO batch_insert_test (fileid_1, fileid_2, fileid_3, fileid_4, fileid_5, fileid_6, fileid_7, fileid_8, fileid_9, fileid_10)
    VALUES
    <foreach collection="list" item="item" separator=",">
      (#{item.fileid1}, #{item.fileid2}, #{item.fileid3}, #{item.fileid4}, #{item.fileid5},
      #{item.fileid6}, #{item.fileid7}, #{item.fileid8}, #{item.fileid9}, #{item.fileid10})
    </foreach>
  </insert>

實(shí)驗(yàn)結(jié)果如下，使用foreach進(jìn)行插入3000條的數(shù)據(jù)耗時(shí)不到1s：

10.496 INFO  c.s.w.WebTemplateApplicationTests:79   main                    foreach3000條數(shù)據(jù)耗時(shí):403

當(dāng)我們進(jìn)行10w條的數(shù)據(jù)插入時(shí)，受限于max_allowed_packet配置的大小，max_allowed_packet定義了服務(wù)器和客戶端之間傳輸?shù)淖畲髷?shù)據(jù)包大小。該參數(shù)用于限制單個(gè)查詢或語(yǔ)句可以傳輸?shù)淖畲髷?shù)據(jù)量，我們通過(guò)show VARIABLES like '%max_allowed_packet%';默認(rèn)情況下為67108864大約6M左右，所以這也最終導(dǎo)致了這10w條數(shù)據(jù)的插入直接失敗了。

Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (106,100,142 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.

4. 使用批處理完成插入

再來(lái)看看筆者最推薦的一種插入方式——批處理插入，批處理的工作原理是在一次SQL連接通信提交多條SQL執(zhí)行語(yǔ)句，通過(guò)減少網(wǎng)絡(luò)往返的開銷來(lái)提升SQL執(zhí)行效率的一種手段，需要注意使用批處理的時(shí)候需要注意以下幾點(diǎn)：

• 批處理會(huì)以批次為單位提交SQL執(zhí)行語(yǔ)句，如果涉及大批量的批處理大查詢操作，SQL服務(wù)器內(nèi)存資源存在被這批次查詢耗盡的風(fēng)險(xiǎn)。
• 批處理提交或者查詢的數(shù)據(jù)過(guò)大時(shí)會(huì)導(dǎo)致傳輸包過(guò)大，也可能導(dǎo)致網(wǎng)絡(luò)傳輸耗時(shí)長(zhǎng)的問(wèn)題。

在正式介紹這種插入方式前，讀者先確認(rèn)自己的鏈接配置是否添加了這條配置語(yǔ)句，只有在MySQL連接參數(shù)后面增加這一項(xiàng)配置才會(huì)使得MySQL5.1.13以上版本的驅(qū)動(dòng)批量提交你的插入語(yǔ)句。

rewriteBatchedStatements=true

完成連接配置后，我們還需要對(duì)于批量插入的編碼進(jìn)行一定調(diào)整，Mybatis默認(rèn)情況下執(zhí)行器為Simple，這種執(zhí)行器每次執(zhí)行創(chuàng)建的都是一個(gè)全新的語(yǔ)句，也就是創(chuàng)建一個(gè)全新的PreparedStatement對(duì)象，這也就意味著每次提交的SQL語(yǔ)句的插入請(qǐng)求都無(wú)法緩存，每次調(diào)用時(shí)都需要重新解析SQL語(yǔ)句。 而我們的批處理則是將ExecutorType改為BATCH，執(zhí)行時(shí)Mybatis會(huì)先將插入語(yǔ)句進(jìn)行一次預(yù)編譯生成PreparedStatement對(duì)象，發(fā)送一個(gè)網(wǎng)絡(luò)請(qǐng)求進(jìn)行數(shù)據(jù)解析和優(yōu)化，因?yàn)镋xecutorType改為BATCH，所以這次預(yù)編譯之后，后續(xù)的插入的SQL到DBMS時(shí)，就無(wú)需在進(jìn)行預(yù)編譯，可直接一次網(wǎng)絡(luò)IO將批量插入的語(yǔ)句提交到MySQL上執(zhí)行。

@Autowired
    private SqlSessionFactory sqlSessionFactory;

/**
     * session插入
     */
    @Test
    void batchInsert() {
        /**
         * 代碼預(yù)熱
         */
        performCodeWarmUp(5);


        List<BatchInsertTest> testList = generateBatchInsertTestData();

        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        BatchInsertTestMapper sqlSessionMapper = sqlSession.getMapper(BatchInsertTestMapper.class);

        long start = System.currentTimeMillis();

        for (BatchInsertTest batchInsertTest : testList) {
            sqlSessionMapper.insert(batchInsertTest);
        }
        sqlSession.commit();
        long end = System.currentTimeMillis();
        log.info("批處理插入{}條數(shù)據(jù)耗時(shí):{}", BATCH_INSERT_SIZE, end - start);

    }

可以看到進(jìn)行3000條數(shù)據(jù)插入時(shí)，耗時(shí)也只需只需179ms左右：

05.226 INFO  c.s.w.WebTemplateApplicationTests:108  main                    批處理插入3000條數(shù)據(jù)耗時(shí):179

而進(jìn)行10w條數(shù)據(jù)批處理插入的時(shí)機(jī)只需4s左右，效率非?？捎^。

04.771 INFO  c.s.w.WebTemplateApplicationTests:108  main                    批處理插入100000條數(shù)據(jù)耗時(shí):4635

三、更高效的插入方式

因?yàn)镸ybatis對(duì)于原生批處理操作做了很多的封裝，其中涉及很多校驗(yàn)檢查和解析等繁瑣的流程，所以通過(guò)使用原生JDBC Batch來(lái)避免這些繁瑣的解析、動(dòng)態(tài)攔截等操作，對(duì)于MySQL批量插入也會(huì)有顯著的提升。感興趣的讀者可以自行嘗試，筆者這里就不多做演示了。

四、詳解批處理高效的原因

針對(duì)上述三種方式，筆者來(lái)解釋一下為什么在能夠確保不出錯(cuò)的情況下，批處理插入的效率最高，我們都知道MySQL進(jìn)行插入操作時(shí)整體的耗時(shí)比例如下：

鏈接耗時(shí) （30%）
發(fā)送query到服務(wù)器 （20%）
解析query （20%）
插入操作 （10% * 詞條數(shù)目）
插入index （10% * Index的數(shù)目）
關(guān)閉鏈接 （10%）

由此可知，進(jìn)行SQL插入操作時(shí)，最耗時(shí)的操作是網(wǎng)絡(luò)連接，這也就是為什么在進(jìn)行3000條數(shù)據(jù)插入時(shí)，foreach和批處理插入的性能的性能表現(xiàn)最出色。因?yàn)橹鹦胁迦胩峤粫r(shí)，每一條插入操作都會(huì)進(jìn)行至少兩次的網(wǎng)絡(luò)返回(如果生成的是stament對(duì)象則是兩次，PreparedStatement則還要加上預(yù)編譯的網(wǎng)絡(luò)往返)，在大量的插入情況下，所有的語(yǔ)句都需要經(jīng)歷一次最耗時(shí)的鏈接操作，性能自然是下降了不少。

這里筆者給出逐條插入的時(shí)的執(zhí)行調(diào)試日志，可以看到每條插入都會(huì)進(jìn)行一次預(yù)編譯：

相比之下批處理和foreach一次預(yù)編譯加上一次網(wǎng)絡(luò)往返即可完成SQL執(zhí)行，效率自然是上去的：

對(duì)應(yīng)我們也給出批處理和foreach插入的執(zhí)行日志印證這一點(diǎn)：

我們?cè)賮?lái)說(shuō)說(shuō)為什么批處理比f(wàn)oreach高效的原因，明明同樣是3000條語(yǔ)句的插入，foreach傳輸?shù)臄?shù)據(jù)包大小也小于批處理，為什么批處理的性能卻要好于foreach插入操作呢？

foreach插入進(jìn)行預(yù)編譯之后，存在一個(gè)字符串解析拼接的操作，這就意味著如果本次插入的數(shù)據(jù)鍋大就會(huì)存在一個(gè)漫長(zhǎng)的SQL拼接耗時(shí)，結(jié)合mybatis官網(wǎng)給出的壓測(cè)報(bào)告來(lái)看，在20~50行左右的插入性能表現(xiàn)最好，超過(guò)這個(gè)數(shù)字之后表現(xiàn)就會(huì)逐漸變差：

對(duì)此我們也給出mybatis的foreach語(yǔ)法底層的字符拼接的實(shí)現(xiàn)，即FilteredDynamicContext 下的appendSql方法：

  private static class FilteredDynamicContext extends DynamicContext {
    private DynamicContext delegate;
    //對(duì)應(yīng)集合項(xiàng)在集合的索引位置
    private int index;
    // item的索引
    private String itemIndex;
    // item的值
    private String item;
    //.............
    // 解析 #{item}
    @Override
    public void appendSql(String sql) {
      GenericTokenParser parser = new GenericTokenParser("#{", "}", new TokenHandler() {
        @Override
        public String handleToken(String content) {
          // 把 #{itm} 轉(zhuǎn)換為 #{__frch_item_1} 之類的
          String newContent = content.replaceFirst("^\\s*" + item + "(?![^.,:\\s])", itemizeItem(item, index));
           // 把 #{itmIndex} 轉(zhuǎn)換為 #{__frch_itemIndex_1} 之類的
          if (itemIndex != null && newContent.equals(content)) {
            newContent = content.replaceFirst("^\\s*" + itemIndex + "(?![^.,:\\s])", itemizeItem(itemIndex, index));
          }
          // 再返回 #{__frch_item_1} 或 #{__frch_itemIndex_1}
          return new StringBuilder("#{").append(newContent).append("}").toString();
        }
      });
      // 拼接SQL
      delegate.appendSql(parser.parse(sql));
    }
  private static String itemizeItem(String item, int i) {
    return new StringBuilder("__frch_").append(item).append("_").append(i).toString();
  }
}

五、一次插入多少數(shù)據(jù)量合適

明確要使用批處理進(jìn)行批量插入之后，我們?cè)賮?lái)了解下一個(gè)問(wèn)題，一次性批量插入多少條SQL語(yǔ)句比較合適？

對(duì)此我們基于100w的數(shù)據(jù)，分別按照每次10、500、1000、20000、80000條壓測(cè)，最終實(shí)驗(yàn)結(jié)果如下：

80000的數(shù)據(jù)，每次插入10條，耗時(shí):14555
80000的數(shù)據(jù)，每次插入500條，耗時(shí):5001
80000的數(shù)據(jù)，每次插入1000條，耗時(shí):3960
80000的數(shù)據(jù)，每次插入2000條，耗時(shí):3788
80000的數(shù)據(jù)，每次插入3000條，耗時(shí):3993
80000的數(shù)據(jù)，每次插入4000條，耗時(shí):3847

在經(jīng)過(guò)筆者的壓測(cè)實(shí)驗(yàn)時(shí)發(fā)現(xiàn)，在2000條差不多2M大小的情況下插入時(shí)的性能最出色。這一點(diǎn)筆者也在網(wǎng)上看到一篇文章提到MySQL的全局變量max_allowed_packet，它限制了每條SQL語(yǔ)句的大小，默認(rèn)情況下為4M，而這位作者的實(shí)驗(yàn)則是插入數(shù)據(jù)的大小在max_allowed_packet的一半情況下性能最佳。

show variables like 'max_allowed_packet%';  

當(dāng)然并不一定只有上述條件影響批量插入的性能，影響批量插入的性能原因還有:

插入緩存：對(duì)于innodb存儲(chǔ)引擎來(lái)說(shuō)，插入是需要耗費(fèi)緩沖池內(nèi)存的，如果在寫密集的情況下，插入緩存會(huì)占用過(guò)多的緩沖池內(nèi)存，若插入操作占用大小超過(guò)緩沖池的一半，則會(huì)影響操其他的操作。

關(guān)于緩沖池的大小，可以通過(guò)下面這條SQL查看，默認(rèn)情況下為134M:

show variables like 'innodb_buffer_pool_size';

索引的維護(hù)：這點(diǎn)相信讀者比較熟悉，如果每次插入涉及大量無(wú)序且多個(gè)索引的維護(hù)，導(dǎo)致B+tree進(jìn)行節(jié)點(diǎn)分裂合并等處理，則會(huì)消耗大量的計(jì)算資源，從而間接影響插入效率。

六、使用批處理的注意事項(xiàng)

批處理就是將一批操作提交至MySQL服務(wù)器一次性操作，但無(wú)法保證事務(wù)的原子性，所以讀者在使用批處理操作時(shí)，若需要保證操作原子性則需要考慮一下事務(wù)問(wèn)題。

七、小結(jié)

整篇文章的篇幅不算很大，可以看到筆者針對(duì)此類問(wèn)題常見的做法是:

• 明確問(wèn)題和要解決的問(wèn)題，以批量插入為例，首要問(wèn)題就是現(xiàn)有方案中可以有幾種插入方式和如何提高這些插入技術(shù)的性能。
• 將問(wèn)題切割成無(wú)數(shù)個(gè)子問(wèn)題，筆者將批量插入按步驟分為:如何插入和插入多少的子問(wèn)題。
• 搜索常見的解決方案，即筆者上述的的逐條插入、foreach、批處理3種插入方式。
• 基于現(xiàn)成方案采用不同量級(jí)的樣本進(jìn)行求證，為避免偶然性，筆者將插入的量級(jí)設(shè)置為幾千甚至幾萬(wàn)不等。
• 基于實(shí)驗(yàn)樣本復(fù)盤總結(jié)，在明確批量插入技術(shù)之后，繼續(xù)查閱資料尋找插入量級(jí)，并繼續(xù)實(shí)驗(yàn)從而得出最終研究成果。
• 進(jìn)階，對(duì)于上述成果繼續(xù)加以求證了解工作原理，并對(duì)后續(xù)可能存在的問(wèn)題查閱更多資料進(jìn)行兜底。