Mybatis批量插入的方式有三種

1. 普通插入

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

3. ExecutorType.BATCH插入

下面對這三種分別進行比較:

1.普通插入

默認的插入方式是遍歷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 ( ?, ?, ?, ?, ?, ?, ?, ? ) 

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

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

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

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

乍看上去這個foreach沒有問題，但是經(jīng)過項目實踐發(fā)現(xiàn)，當表的列數(shù)較多（20+），以及一次性插入的行數(shù)較多（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.

它強調(diào)，當插入數(shù)量很多時，不能一次性全放在一條語句里?？墒菫槭裁床荒芊旁谕粭l語句里呢？這條語句為什么會耗時這么久呢？我查閱了資料發(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.

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

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

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

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

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

此外Mysql 對執(zhí)行的SQL語句大小進行限制，相當于對字符串進行限制。默認允許最大SQL是 4M 。
超過限制就會拋錯:

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.

這個錯誤是 Mysql 的JDBC包拋出的,跟Mybatis框架無關, Mybatis 解析動態(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;
}

從開始到結束， Mybatis 都沒有對填充的條數(shù)和參數(shù)的數(shù)量做限制,是Mysql 對語句的長度有限制，默認是 4M。

3.ExecutorType.BATCH插入

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

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

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

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

  //如果自動提交設置為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耗時 ==>: 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ā)個數(shù)據(jù)庫，數(shù)據(jù)庫預編譯好，數(shù)據(jù)庫等待需要運行的參數(shù)，接收到參數(shù)后一次運行，ExecutorType.BATCH只打印一次SQL語句，預編譯一次sql,多次設置參數(shù)步驟.

總結:

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

ExecutorType.BATCH插入

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

最新評論