Mybatis批量插入大量數據的最優方式總結
Mybatis批量插入的方式有三種
1. 普通插入
2. foreach 優化插入
3. ExecutorType.BATCH插入
下面對這三種分別進行比較:
1.普通插入
默認的插入方式是遍歷insert語句，單條執行，效率肯定低下，如果成堆插入，更是性能有問題。
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 ( ?, ?, ?, ?, ?, ?, ?, ? )
可以看到每個語句的執行創建一個新的預處理語句，單條提交sql,性能低下.
2.foreach 優化插入
如果要優化插入速度時，可以將許多小型操作組合到一個大型操作中。理想情況下，這樣可以在單個連接中一次性發送許多新行的數據，並將所有索引更新和一致性檢查延遲到最後才進行。
<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沒有問題，但是經過項目實踐發現，當表的列數較多（20+），以及一次性插入的行數較多（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.
它強調，當插入數量很多時，不能一次性全放在一條語句裡。可是為什麼不能放在同一條語句裡呢？這條語句為什麼會耗時這麼久呢？我查閱瞭資料發現：
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.
從資料中可知，默認執行器類型為Simple，會為每個語句創建一個新的預處理語句，也就是創建一個PreparedStatement對象。在我們的項目中，會不停地使用批量插入這個方法，而因為MyBatis對於含有的語句，無法采用緩存，那麼在每次調用方法時，都會重新解析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特別長，包含瞭很多占位符，對於占位符和參數的映射尤其耗時。並且，查閱相關資料可知，values的增長與所需的解析時間，是呈指數型增長的。
foreach 遇到數量大，性能瓶頸
項目實踐發現，當表的列數較多（超過20），以及一次性插入的行數較多（上萬條）時，插入性能非常差，通常需要20分鐘以上
所以，如果非要使用 foreach 的方式來進行批量插入的話，可以考慮減少一條 insert 語句中 values 的個數，最好能達到上面曲線的最底部的值，使速度最快。一般按經驗來說，一次性插20~50行數量是比較合適的，時間消耗也能接受。
此外Mysql 對執行的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 解析動態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);
}
}
// 創建 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);
}
}
}
// 填充參數，創建語句
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 都沒有對填充的條數和參數的數量做限制,是Mysql 對語句的長度有限制，默認是 4M。
3.ExecutorType.BATCH插入
Mybatis內置的ExecutorType有3種，SIMPLE、REUSE、BATCH; 默認的是simple，該模式下它為每個語句的執行創建一個新的預處理語句，單條提交sql；而batch模式重復使用已經預處理的語句，並且批量執行所有更新語句，顯然batch性能將更優；但batch模式也有自己的問題，比如在Insert操作時，在事務沒有提交之前，是沒有辦法獲取到自增的id，這在某型情形下是不符合業務要求的.
JDBC 在執行 SQL 語句時，會將 SQL 語句以及實參通過網絡請求的方式發送到數據庫，一次執行一條 SQL 語句，一方面會減小請求包的有效負載，另一個方面會增加耗費在網絡通信上的時間。通過批處理的方式，我們就可以在 JDBC 客戶端緩存多條 SQL 語句，然後在 flush 或緩存滿的時候，將多條 SQL 語句打包發送到數據庫執行，這樣就可以有效地降低上述兩方面的損耗，從而提高系統性能。進行jdbc批處理時需在JDBC的url中加入rewriteBatchedStatements=true
不過，有一點需要特別註意：每次向數據庫發送的 SQL 語句的條數是有上限的，如果批量執行的時候超過這個上限值，數據庫就會拋出異常，拒絕執行這一批 SQL 語句，所以我們需要控制批量發送 SQL 語句的條數和頻率.
使用Batch批量處理數據庫，當需要向數據庫發送一批SQL語句執行時，應避免向數據庫一條條的發送執行，而應采用JDBC的批處理機制，以提升執行效率
//如果自動提交設置為true,將無法控制提交的條數，改為最後統一提交
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後需要調用sqlSession .clearCache()和sqlSession .flushStatements();,用以刷新緩存和提交到數據庫,通過閱讀源碼,這兩行大可不必寫,源碼解析如下:
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()方法已經調用瞭clearLocalCache()和flushStatements(),
而clearCache()方法也是調用瞭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語句發個數據庫，數據庫預編譯好，數據庫等待需要運行的參數，接收到參數後一次運行，ExecutorType.BATCH隻打印一次SQL語句，預編譯一次sql,多次設置參數步驟.
總結:
經過以上三種方式分析,在插入大數據量時優先選擇第三種方式,
ExecutorType.BATCH插入
到此這篇關於Mybatis批量插入大量數據的最優方式的文章就介紹到這瞭,更多相關Mybatis批量插入大量數據內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！
推薦閱讀：
- MyBatis批量插入大量數據(1w以上)
- Java經典面試題匯總:Mybatis
- MyBatis批量插入的幾種方式效率比較
- Java面試題沖刺第九天–MyBatis2
- Mybatis 一級緩存和二級緩存原理區別