Mybatis批量插入大量數(shù)據(jù)的方法有哪些

本文小編為大家詳細(xì)介紹“Mybatis批量插入大量數(shù)據(jù)的方法有哪些”，內(nèi)容詳細(xì)，步驟清晰，細(xì)節(jié)處理妥當(dāng)，希望這篇“Mybatis批量插入大量數(shù)據(jù)的方法有哪些”文章能幫助大家解決疑惑，下面跟著小編的思路慢慢深入，一起來學(xué)習(xí)新知識(shí)吧。

Mybatis批量插入的方式有三種

1. 普通插入

2. foreach 優(yōu)化插入

3. ExecutorType.BATCH插入

下面對(duì)這三種分別進(jìn)行比較:

1.普通插入

默認(rèn)的插入方式是遍歷insert語句，單條執(zhí)行，效率肯定低下，如果成堆插入，更是性能有問題。

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");

sql log如下:

2022-08-30 05:26:02 [1125b8ff-dfa3-478e-bbee-29173babe5a7] [http-nio-3005-exec-2] [com.btn.common.config.MybatisSqlLoggerInterceptor]-[INFO] 攔截的sql ==>: com.btn.mapper.patient.PatientLabelDetailMapper.insert:INSERT INTO t_patient_label_detail ( patient_id, doctor_id, tag_id, patient_name, gender, age, create_by, create_time ) VALUES ( 337, 178, 251, '劉梅好', 2, 29, '178',  )
2022-08-30 05:26:02 [1125b8ff-dfa3-478e-bbee-29173babe5a7] [http-nio-3005-exec-2] [com.btn.mapper.patient.PatientLabelDetailMapper.insert]-[DEBUG] ==>  Preparing: INSERT INTO t_patient_label_detail ( patient_id, doctor_id, tag_id, patient_name, gender, age, create_by, create_time ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? ) 

2022-08-30 05:34:40 [215b2b99-b0c9-41f6-93b2-545c8d6ff0fb] [http-nio-3005-exec-2] [com.btn.common.config.MybatisSqlLoggerInterceptor]-[INFO] 攔截的sql ==>: com.btn.mapper.patient.PatientLabelDetailMapper.insert:INSERT INTO t_patient_label_detail ( patient_id, doctor_id, tag_id, patient_name, gender, age, create_by, create_time ) VALUES ( 256, 178, 253, '????啊～吃西瓜', 0, 0, '178',  )
2022-08-30 05:34:40 [215b2b99-b0c9-41f6-93b2-545c8d6ff0fb] [http-nio-3005-exec-2] [com.btn.mapper.patient.PatientLabelDetailMapper.insert]-[DEBUG] ==>  Preparing: INSERT INTO t_patient_label_detail ( patient_id, doctor_id, tag_id, patient_name, gender, age, create_by, create_time ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? ) 

可以看到每個(gè)語句的執(zhí)行創(chuàng)建一個(gè)新的預(yù)處理語句，單條提交sql,性能低下.

2.foreach 優(yōu)化插入

如果要優(yōu)化插入速度時(shí)，可以將許多小型操作組合到一個(gè)大型操作中。理想情況下，這樣可以在單個(gè)連接中一次性發(fā)送許多新行的數(shù)據(jù)，并將所有索引更新和一致性檢查延遲到最后才進(jìn)行。

<insert id="batchInsert" parameterType="java.util.List">
    insert into table1 (field1, field2) values
    <foreach collection="list" item="t" index="index" separator=","> 
        (#{t.field1}, #{t.field2})
    </foreach>
</insert>

翻譯成sql語句也就是

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

乍看上去這個(gè)foreach沒有問題，但是經(jīng)過項(xiàng)目實(shí)踐發(fā)現(xiàn)，當(dāng)表的列數(shù)較多（20+），以及一次性插入的行數(shù)較多（5000+）時(shí)，整個(gè)插入的耗時(shí)十分漫長(zhǎng)，達(dá)到了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.

它強(qiáng)調(diào)，當(dāng)插入數(shù)量很多時(shí)，不能一次性全放在一條語句里。可是為什么不能放在同一條語句里呢？這條語句為什么會(huì)耗時(shí)這么久呢？我查閱了資料發(fā)現(xiàn)：

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.

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

從資料中可知，默認(rèn)執(zhí)行器類型為Simple，會(huì)為每個(gè)語句創(chuàng)建一個(gè)新的預(yù)處理語句，也就是創(chuàng)建一個(gè)PreparedStatement對(duì)象。在我們的項(xiàng)目中，會(huì)不停地使用批量插入這個(gè)方法，而因?yàn)镸yBatis對(duì)于含有的語句，無法采用緩存，那么在每次調(diào)用方法時(shí)，都會(huì)重新解析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 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.

從上述資料可知，耗時(shí)就耗在，由于我foreach后有5000+個(gè)values，所以這個(gè)PreparedStatement特別長(zhǎng)，包含了很多占位符，對(duì)于占位符和參數(shù)的映射尤其耗時(shí)。并且，查閱相關(guān)資料可知，values的增長(zhǎng)與所需的解析時(shí)間，是呈指數(shù)型增長(zhǎng)的。

foreach 遇到數(shù)量大，性能瓶頸

項(xiàng)目實(shí)踐發(fā)現(xiàn)，當(dāng)表的列數(shù)較多（超過20），以及一次性插入的行數(shù)較多（上萬條）時(shí)，插入性能非常差，通常需要20分鐘以上

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

此外Mysql 對(duì)執(zhí)行的SQL語句大小進(jìn)行限制，相當(dāng)于對(duì)字符串進(jìn)行限制。默認(rèn)允許最大SQL是 4M 。
超過限制就會(huì)拋錯(cuò):

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (8346602 > 4194304). You can change this value on the server by setting the max_allowed_packet’ variable.

這個(gè)錯(cuò)誤是 Mysql 的JDBC包拋出的,跟Mybatis框架無關(guān), Mybatis 解析動(dòng)態(tài)SQL的源碼如下:

 // 開始解析
public void parse() {
    if (!configuration.isResourceLoaded(resource)) {
        configurationElement(parser.evalNode("/mapper"));
        configuration.addLoadedResource(resource);
        bindMapperForNamespace();
    }
 
    parsePendingResultMaps();
    parsePendingChacheRefs();
    parsePendingStatements();
}
// 解析mapper
private void configurationElement(XNode context) {
    try {
        String namespace = context.getStringAttribute("namespace");
        if (namespace.equals("")) {
            throw new BuilderException("Mapper's namespace cannot be empty");
        }
        builderAssistant.setCurrentNamespace(namespace);
        cacheRefElement(context.evalNode("cache-ref"));
        cacheElement(context.evalNode("cache"));
        parameterMapElement(context.evalNodes("/mapper/parameterMap"));
        resultMapElements(context.evalNodes("/mapper/resultMap"));
        sqlElement(context.evalNodes("/mapper/sql"));
        buildStatementFromContext(context.evalNodes("select|insert|update|delete"));
    } catch (Exception e) {
        throw new BuilderException("Error parsing Mapper XML. Cause: " + e, e);
    }
}
 
// 創(chuàng)建 select|insert|update|delete 語句
private void buildStatementFromContext(List<XNode> list, String requiredDatabaseId) {
    for (XNode context : list) {
        final XMLStatementBuilder statementParser = new XMLStatementBuilder(configuration, builderAssistant, context, requiredDatabaseId);
        try {
            statementParser.parseStatementNode();
        } catch (IncompleteElementException e) {
            configuration.addIncompleteStatement(statementParser);
        }
    }
}

// 填充參數(shù)，創(chuàng)建語句
public BoundSql getBoundSql(Object parameterObject) {
    DynamicContext context = new DynamicContext(configuration, parameterObject);
    rootSqlNode.apply(context);
    SqlSourceBuilder sqlSourceParser = new SqlSourceBuilder(configuration);
    Class<?> parameterType = parameterObject == null ? Object.class : parameterObject.getClass();
    SqlSource sqlSource = sqlSourceParser.parse(context.getSql(), parameterType, context.getBindings());
    BoundSql boundSql = sqlSource.getBoundSql(parameterObject);
    for (Map.Entry<String, Object> entry : context.getBindings().entrySet()) {
        boundSql.setAdditionalParameter(entry.getKey(), entry.getValue());
    }
    return boundSql;
}

從開始到結(jié)束， Mybatis 都沒有對(duì)填充的條數(shù)和參數(shù)的數(shù)量做限制,是Mysql 對(duì)語句的長(zhǎng)度有限制，默認(rèn)是 4M。

3.ExecutorType.BATCH插入

Mybatis內(nèi)置的ExecutorType有3種，SIMPLE、REUSE、BATCH; 默認(rèn)的是simple，該模式下它為每個(gè)語句的執(zhí)行創(chuàng)建一個(gè)新的預(yù)處理語句，單條提交sql；而batch模式重復(fù)使用已經(jīng)預(yù)處理的語句，并且批量執(zhí)行所有更新語句，顯然batch性能將更優(yōu)；但batch模式也有自己的問題，比如在Insert操作時(shí)，在事務(wù)沒有提交之前，是沒有辦法獲取到自增的id，這在某型情形下是不符合業(yè)務(wù)要求的.

JDBC 在執(zhí)行 SQL 語句時(shí)，會(huì)將 SQL 語句以及實(shí)參通過網(wǎng)絡(luò)請(qǐng)求的方式發(fā)送到數(shù)據(jù)庫，一次執(zhí)行一條 SQL 語句，一方面會(huì)減小請(qǐng)求包的有效負(fù)載，另一個(gè)方面會(huì)增加耗費(fèi)在網(wǎng)絡(luò)通信上的時(shí)間。通過批處理的方式，我們就可以在 JDBC 客戶端緩存多條 SQL 語句，然后在 flush 或緩存滿的時(shí)候，將多條 SQL 語句打包發(fā)送到數(shù)據(jù)庫執(zhí)行，這樣就可以有效地降低上述兩方面的損耗，從而提高系統(tǒng)性能。進(jìn)行jdbc批處理時(shí)需在JDBC的url中加入rewriteBatchedStatements=true

不過，有一點(diǎn)需要特別注意：每次向數(shù)據(jù)庫發(fā)送的 SQL 語句的條數(shù)是有上限的，如果批量執(zhí)行的時(shí)候超過這個(gè)上限值，數(shù)據(jù)庫就會(huì)拋出異常，拒絕執(zhí)行這一批 SQL 語句，所以我們需要控制批量發(fā)送 SQL 語句的條數(shù)和頻率.

使用Batch批量處理數(shù)據(jù)庫，當(dāng)需要向數(shù)據(jù)庫發(fā)送一批SQL語句執(zhí)行時(shí)，應(yīng)避免向數(shù)據(jù)庫一條條的發(fā)送執(zhí)行，而應(yīng)采用JDBC的批處理機(jī)制，以提升執(zhí)行效率

  //如果自動(dòng)提交設(shè)置為true,將無法控制提交的條數(shù)，改為最后統(tǒng)一提交
  SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false);
  PatientLabelDetailMapper patientLabelDetailMapper = sqlSession.getMapper(PatientLabelDetailMapper.class);
  private int BATCH = 1000;
  for (int index = 0; index < data.size(); index++) {
       patientLabelDetailMapper.insert(data.get(i))
        if (index != 0 && index % BATCH == 0) {
          sqlSession .commit();
        }
      }
  sqlSession.commit();

需要說明的是,很多博客文章都說在commit后需要調(diào)用sqlSession .clearCache()和sqlSession .flushStatements();,用以刷新緩存和提交到數(shù)據(jù)庫,通過閱讀源碼,這兩行大可不必寫,源碼解析如下:

public void commit(boolean required) throws SQLException {
    if (this.closed) {
        throw new ExecutorException("Cannot commit, transaction is already closed");
    } else {
        this.clearLocalCache();
        this.flushStatements();
        if (required) {
            this.transaction.commit();
        }

    }
}

 public void clearCache() {
    this.executor.clearLocalCache();
}

源碼commit()方法已經(jīng)調(diào)用了clearLocalCache()和flushStatements(),

而clearCache()方法也是調(diào)用了clearLocalCache(),所以只需寫commit()即可.

sql log日志分析如下:

2022-08-30 05:31:27 [0ed35173-ae5f-4ea5-a937-f771d33ae4bd] [http-nio-3005-exec-1] [com.btn.common.config.MybatisSqlLoggerInterceptor]-[INFO] 攔截的sql ==>: com.btn.mapper.patient.PatientLabelDetailMapper.insert:INSERT INTO t_patient_label_detail ( patient_id, doctor_id, tag_id, patient_name, gender, age, create_by, create_time ) VALUES ( 337, 178, 252, '劉梅好', 2, 29, '178',  )
2022-08-30 05:31:27 [0ed35173-ae5f-4ea5-a937-f771d33ae4bd] [http-nio-3005-exec-1] [com.btn.mapper.patient.PatientLabelDetailMapper.insert]-[DEBUG] ==>  Preparing: INSERT INTO t_patient_label_detail ( patient_id, doctor_id, tag_id, patient_name, gender, age, create_by, create_time ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ? ) 
2022-08-30 05:31:27 [0ed35173-ae5f-4ea5-a937-f771d33ae4bd] [http-nio-3005-exec-1] [com.btn.common.config.MybatisSqlLoggerInterceptor]-[INFO] sql耗時(shí) ==>: 2
2022-08-30 05:31:27 [0ed35173-ae5f-4ea5-a937-f771d33ae4bd] [http-nio-3005-exec-1] [com.btn.mapper.patient.PatientLabelDetailMapper.insert]-[DEBUG] ==> Parameters: 337(Long), 178(Long), 252(Long), 劉梅好(String), 2(Integer), 29(Integer), 178(String), null
2022-08-30 05:31:27 [0ed35173-ae5f-4ea5-a937-f771d33ae4bd] [http-nio-3005-exec-1] [com.btn.mapper.patient.PatientLabelDetailMapper.insert]-[DEBUG] ==> Parameters: 256(Long), 178(Long), 252(Long), ????啊～吃西瓜(String), 0(Integer), 0(Integer), 178(String), null

ExecutorType.BATCH原理：把SQL語句發(fā)個(gè)數(shù)據(jù)庫，數(shù)據(jù)庫預(yù)編譯好，數(shù)據(jù)庫等待需要運(yùn)行的參數(shù)，接收到參數(shù)后一次運(yùn)行，ExecutorType.BATCH只打印一次SQL語句，預(yù)編譯一次sql,多次設(shè)置參數(shù)步驟.

讀到這里，這篇“Mybatis批量插入大量數(shù)據(jù)的方法有哪些”文章已經(jīng)介紹完畢，想要掌握這篇文章的知識(shí)點(diǎn)還需要大家自己動(dòng)手實(shí)踐使用過才能領(lǐng)會(huì)，如果想了解更多相關(guān)內(nèi)容的文章，歡迎關(guān)注億速云行業(yè)資訊頻道。