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

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

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

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

常見的3種插入方式演示

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

為了演示，這里給出一張示例表，除了id以外，有10個varchar字段，也就是說全字段寫滿的話一條數(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='測試批量插入，一行數(shù)據(jù)1k左右';

使用逐行插入

我們首先采用逐行插入方式分別插入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ù)耗時:{}", BATCH_INSERT_SIZE, end - start);

    }

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

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

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

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

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

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

 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ù)耗時:{}", BATCH_INSERT_SIZE, end - start);

    }

對應(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ù)耗時不到1s：

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

當(dāng)我們進(jìn)行10w條的數(shù)據(jù)插入時，受限于max_allowed_packet配置的大小，max_allowed_packet定義了服務(wù)器和客戶端之間傳輸?shù)淖畲髷?shù)據(jù)包大小。該參數(shù)用于限制單個查詢或語句可以傳輸?shù)淖畲髷?shù)據(jù)量，默認(rèn)情況下為4M左右，所以這也最終導(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.

使用批處理完成插入

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

rewriteBatchedStatements=true

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

    }

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

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

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

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

原因分析

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

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

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

我們再來說說為什么批處理比foreach高效的原因，明明同樣是3000條語句的插入，foreach傳輸?shù)臄?shù)據(jù)包大小也小于批處理，為什么批處理的性能卻要好于foreach插入操作呢？

我們在上文講批處理的時候提到，Mybatis在默認(rèn)情況下，執(zhí)行器是為SIMPLE，這就意味每次提交的插入操作的SQL語句都是相當(dāng)于全新的PreparedStatement，都是需要進(jìn)行預(yù)編譯的，所以一條插入的SQL則是需要經(jīng)歷預(yù)編譯和執(zhí)行兩次的網(wǎng)絡(luò)往返，對應(yīng)的代碼也相當(dāng)于下面這段JDBC代碼：

		  // 創(chuàng)建Statement對象
            PreparedStatement statement = connection.createStatement();

            // 批量插入的數(shù)據(jù)
            String[] names = {"John Doe", "Jane Smith", "Mike Johnson"};
            int[] ages = {30, 25, 35};
            String[] cities = {"New York", "London", "Paris"};

            // 構(gòu)建批量插入的SQL語句
            StringBuilder insertQuery = new StringBuilder("INSERT INTO mytable (name, age, city) VALUES ");
            for (int i = 0; i < names.length; i++) {
                insertQuery.append("('").append(names[i]).append("', ").append(ages[i]).append(", '").append(cities[i]).append("')");
                if (i < names.length - 1) {
                    insertQuery.append(", ");
                }
            }

            // 執(zhí)行批量插入操作
            statement.executeUpdate(insertQuery.toString());

            // 關(guān)閉連接和Statement
            statement.close();
            connection.close();

可以看到在每一次使用foreach進(jìn)行插入操作時，都需要重新創(chuàng)建一個PreparedStatement構(gòu)建出一個SQL語句，每次提交時MySQL都需要進(jìn)行一次預(yù)編譯，這意味著用戶每次使用foreach插入時，都需要進(jìn)行一次預(yù)編譯的網(wǎng)絡(luò)IO，也正是這個原因使得其性能相較于批處理會遜色一些。

而批處理則不同，在我們的代碼中，我們手動將ExecutorType改為BATCH，這樣一來，每次進(jìn)行批量插入時，Mybatis會先拿著我們的SQL語句創(chuàng)建成一個PreparedStatement提交到MySQL上進(jìn)行預(yù)編譯，這樣一來本次會話所有相同的SQL語句直接提交時，就無需經(jīng)過編譯檢查的操作，后續(xù)批量插入效率顯著提升。

更高效的插入方式

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

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

明確要使用批處理進(jìn)行批量插入之后，我們再來了解下一個問題，一次性批量插入多少條SQL語句比較合適？

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

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

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

show variables like 'max_allowed_packet%';  

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

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

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

show variables like 'innodb_buffer_pool_size';

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

小結(jié)

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

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

到此這篇關(guān)于你一定用的上的MySQL批量插入技巧分享的文章就介紹到這了,更多相關(guān)MySQL批量插入內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論