Mybatis批量插入的方式有三種

1. 普通插入

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

3. ExecutorType.BATCH插入

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

1.普通插入

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

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è)語(yǔ)句的執(zhí)行創(chuàng)建一個(gè)新的預(yù)處理語(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語(yǔ)句也就是

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

乍看上去這個(gè)foreach沒(méi)有問(wèn)題，但是經(jīng)過(guò)項(xiàng)目實(shí)踐發(fā)現(xiàn)，當(dāng)表的列數(shù)較多（20+），以及一次性插入的行數(shù)較多（5000+）時(shí)，整個(gè)插入的耗時(shí)十分漫長(zhǎng)，達(dá)到了14分鐘，這是不能忍的。在資料中也提到了一句話(huà)：

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í)，不能一次性全放在一條語(yǔ)句里?？墒菫槭裁床荒芊旁谕粭l語(yǔ)句里呢？這條語(yǔ)句為什么會(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í)行器類(lèi)型為Simple，會(huì)為每個(gè)語(yǔ)句創(chuàng)建一個(gè)新的預(yù)處理語(yǔ)句，也就是創(chuàng)建一個(gè)PreparedStatement對(duì)象。在我們的項(xiàng)目中，會(huì)不停地使用批量插入這個(gè)方法，而因?yàn)镸yBatis對(duì)于含有的語(yǔ)句，無(wú)法采用緩存，那么在每次調(diào)用方法時(shí)，都會(huì)重新解析sql語(yǔ)句。

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ù)較多（超過(guò)20），以及一次性插入的行數(shù)較多（上萬(wàn)條）時(shí)，插入性能非常差，通常需要20分鐘以上

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

此外Mysql 對(duì)執(zhí)行的SQL語(yǔ)句大小進(jìn)行限制，相當(dāng)于對(duì)字符串進(jìn)行限制。默認(rèn)允許最大SQL是 4M 。
超過(guò)限制就會(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框架無(wú)關(guān), Mybatis 解析動(dòng)態(tài)SQL的源碼如下:

 
// 開(kāi)始解析
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 語(yǔ)句
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)建語(yǔ)句
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;
}

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

3.ExecutorType.BATCH插入

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

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

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

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

  //如果自動(dòng)提交設(shè)置為true,將無(wú)法控制提交的條數(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();

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

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(),所以只需寫(xiě)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語(yǔ)句發(fā)個(gè)數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)預(yù)編譯好，數(shù)據(jù)庫(kù)等待需要運(yùn)行的參數(shù)，接收到參數(shù)后一次運(yùn)行，ExecutorType.BATCH只打印一次SQL語(yǔ)句，預(yù)編譯一次sql,多次設(shè)置參數(shù)步驟.

總結(jié):

經(jīng)過(guò)以上三種方式分析,在插入大數(shù)據(jù)量時(shí)優(yōu)先選擇第三種方式,

ExecutorType.BATCH插入

到此這篇關(guān)于Mybatis批量插入大量數(shù)據(jù)的最優(yōu)方式的文章就介紹到這了,更多相關(guān)Mybatis批量插入大量數(shù)據(jù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！