MyBatis批量插入幾千條數(shù)據(jù)，請慎用foreach?。。?/h1>
點擊上方[全棧開發(fā)者社區(qū)]→右上角[...]→[設(shè)為星標(biāo)?
點擊領(lǐng)取全棧資料：全棧資料
近日，項目中有一個耗時較長的Job存在CPU占用過高的問題，經(jīng)排查發(fā)現(xiàn)，主要時間消耗在往MyBatis中批量插入數(shù)據(jù)。mapper configuration是用foreach循環(huán)做的，差不多是這樣。（由于項目保密，以下代碼均為自己手寫的demo代碼）
<insert?id="batchInsert"?parameterType="java.util.List">
????insert?into?USER?(id,?name)?values
????<foreach?collection="list"?item="model"?index="index"?separator=",">?
????????(#{model.id},?#{model.name})
????foreach>
insert>
這個方法提升批量插入速度的原理是，將傳統(tǒng)的：
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");
轉(zhuǎn)化為：
INSERT?INTO?`table1`?(`field1`,?`field2`)?
VALUES?("data1",?"data2"),
("data1",?"data2"),
("data1",?"data2"),
("data1",?"data2"),
("data1",?"data2");
在MySql Docs中也提到過這個trick，如果要優(yōu)化插入速度時，可以將許多小型操作組合到一個大型操作中。理想情況下，這樣可以在單個連接中一次性發(fā)送許多新行的數(shù)據(jù)，并將所有索引更新和一致性檢查延遲到最后才進(jìn)行。
乍看上去這個foreach沒有問題，但是經(jīng)過項目實踐發(fā)現(xiàn)，當(dāng)表的列數(shù)較多（20+），以及一次性插入的行數(shù)較多（5000+）時，整個插入的耗時十分漫長，達(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ù)量很多時，不能一次性全放在一條語句里。可是為什么不能放在同一條語句里呢？這條語句為什么會耗時這么久呢？我查閱了資料發(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.
SqlSession?session?=?sessionFactory.openSession(ExecutorType.BATCH);
for?(Model?model?:?list)?{
????session.insert("insertStatement",?model);
}
session.flushStatements();
Unlike default ExecutorType.SIMPLE, the statement will be prepared once and executed for each record to insert.
從資料中可知，默認(rèn)執(zhí)行器類型為Simple，會為每個語句創(chuàng)建一個新的預(yù)處理語句，也就是創(chuàng)建一個PreparedStatement對象。在我們的項目中，會不停地使用批量插入這個方法，而因為MyBatis對于含有
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
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ù)的映射尤其耗時。并且，查閱相關(guān)資料可知，values的增長與所需的解析時間，是呈指數(shù)型增長的。
所以，如果非要使用 foreach 的方式來進(jìn)行批量插入的話，可以考慮減少一條 insert 語句中 values 的個數(shù)，最好能達(dá)到上面曲線的最底部的值，使速度最快。一般按經(jīng)驗來說，一次性插20~50行數(shù)量是比較合適的，時間消耗也能接受。
重點來了。上面講的是，如果非要用
SqlSession?session?=?sqlSessionFactory.openSession(ExecutorType.BATCH);
try?{
????SimpleTableMapper?mapper?=?session.getMapper(SimpleTableMapper.class);
????List?records?=?getRecordsToInsert();?//?not?shown
?
????BatchInsert?batchInsert?=?insert(records)
????????????.into(simpleTable)
????????????.map(id).toProperty("id")
????????????.map(firstName).toProperty("firstName")
????????????.map(lastName).toProperty("lastName")
????????????.map(birthDate).toProperty("birthDate")
????????????.map(employed).toProperty("employed")
????????????.map(occupation).toProperty("occupation")
????????????.build()
????????????.render(RenderingStrategy.MYBATIS3);
?
????batchInsert.insertStatements().stream().forEach(mapper::insert);
?
????session.commit();
}?finally?{
????session.close();
}
即基本思想是將 MyBatis session 的 executor type 設(shè)為 Batch ，然后多次執(zhí)行插入語句。就類似于JDBC的下面語句一樣。
Connection?connection?=?DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?useUnicode=true&characterEncoding=UTF-8&useServerPrepStmts=false&rewriteBatchedStatements=true","root","root");
connection.setAutoCommit(false);
PreparedStatement?ps?=?connection.prepareStatement(
????????"insert?into?tb_user?(name)?values(?)");
for?(int?i?=?0;?i?????ps.setString(1,name);
????ps.addBatch();
}
ps.executeBatch();
connection.commit();
connection.close();
經(jīng)過試驗，使用了 ExecutorType.BATCH 的插入方式，性能顯著提升，不到 2s 便能全部插入完成。
總結(jié)一下，如果MyBatis需要進(jìn)行批量插入，推薦使用 ExecutorType.BATCH 的插入方式，如果非要使用
覺得本文對你有幫助？請分享給更多人
關(guān)注「全棧開發(fā)者社區(qū)」加星標(biāo)，提升全棧技能
本公眾號會不定期給大家發(fā)福利，包括送書、學(xué)習(xí)資源等，敬請期待吧！
如果感覺推送內(nèi)容不錯，不妨右下角點個在看轉(zhuǎn)發(fā)朋友圈或收藏，感謝支持。
好文章，留言、點贊、在看和分享一條龍吧??
點擊上方[全棧開發(fā)者社區(qū)]→右上角[...]→[設(shè)為星標(biāo)?
點擊領(lǐng)取全棧資料：全棧資料
近日，項目中有一個耗時較長的Job存在CPU占用過高的問題，經(jīng)排查發(fā)現(xiàn)，主要時間消耗在往MyBatis中批量插入數(shù)據(jù)。mapper configuration是用foreach循環(huán)做的，差不多是這樣。（由于項目保密，以下代碼均為自己手寫的demo代碼）
<insert?id="batchInsert"?parameterType="java.util.List">
????insert?into?USER?(id,?name)?values
????<foreach?collection="list"?item="model"?index="index"?separator=",">?
????????(#{model.id},?#{model.name})
????foreach>
insert>
這個方法提升批量插入速度的原理是，將傳統(tǒng)的：
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");
轉(zhuǎn)化為：
INSERT?INTO?`table1`?(`field1`,?`field2`)?
VALUES?("data1",?"data2"),
("data1",?"data2"),
("data1",?"data2"),
("data1",?"data2"),
("data1",?"data2");
在MySql Docs中也提到過這個trick，如果要優(yōu)化插入速度時，可以將許多小型操作組合到一個大型操作中。理想情況下，這樣可以在單個連接中一次性發(fā)送許多新行的數(shù)據(jù)，并將所有索引更新和一致性檢查延遲到最后才進(jìn)行。
乍看上去這個foreach沒有問題，但是經(jīng)過項目實踐發(fā)現(xiàn)，當(dāng)表的列數(shù)較多（20+），以及一次性插入的行數(shù)較多（5000+）時，整個插入的耗時十分漫長，達(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ù)量很多時，不能一次性全放在一條語句里。可是為什么不能放在同一條語句里呢？這條語句為什么會耗時這么久呢？我查閱了資料發(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.
SqlSession?session?=?sessionFactory.openSession(ExecutorType.BATCH);
for?(Model?model?:?list)?{
????session.insert("insertStatement",?model);
}
session.flushStatements();
Unlike default ExecutorType.SIMPLE, the statement will be prepared once and executed for each record to insert.
從資料中可知，默認(rèn)執(zhí)行器類型為Simple，會為每個語句創(chuàng)建一個新的預(yù)處理語句，也就是創(chuàng)建一個PreparedStatement對象。在我們的項目中，會不停地使用批量插入這個方法，而因為MyBatis對于含有
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ù)的映射尤其耗時。并且，查閱相關(guān)資料可知，values的增長與所需的解析時間，是呈指數(shù)型增長的。
所以，如果非要使用 foreach 的方式來進(jìn)行批量插入的話，可以考慮減少一條 insert 語句中 values 的個數(shù)，最好能達(dá)到上面曲線的最底部的值，使速度最快。一般按經(jīng)驗來說，一次性插20~50行數(shù)量是比較合適的，時間消耗也能接受。
重點來了。上面講的是，如果非要用
SqlSession?session?=?sqlSessionFactory.openSession(ExecutorType.BATCH);
try?{
????SimpleTableMapper?mapper?=?session.getMapper(SimpleTableMapper.class);
????List?records?=?getRecordsToInsert();?//?not?shown
?
????BatchInsert?batchInsert?=?insert(records)
????????????.into(simpleTable)
????????????.map(id).toProperty("id")
????????????.map(firstName).toProperty("firstName")
????????????.map(lastName).toProperty("lastName")
????????????.map(birthDate).toProperty("birthDate")
????????????.map(employed).toProperty("employed")
????????????.map(occupation).toProperty("occupation")
????????????.build()
????????????.render(RenderingStrategy.MYBATIS3);
?
????batchInsert.insertStatements().stream().forEach(mapper::insert);
?
????session.commit();
}?finally?{
????session.close();
}
即基本思想是將 MyBatis session 的 executor type 設(shè)為 Batch ，然后多次執(zhí)行插入語句。就類似于JDBC的下面語句一樣。
Connection?connection?=?DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?useUnicode=true&characterEncoding=UTF-8&useServerPrepStmts=false&rewriteBatchedStatements=true","root","root");
connection.setAutoCommit(false);
PreparedStatement?ps?=?connection.prepareStatement(
????????"insert?into?tb_user?(name)?values(?)");
for?(int?i?=?0;?i?????ps.setString(1,name);
????ps.addBatch();
}
ps.executeBatch();
connection.commit();
connection.close();
經(jīng)過試驗，使用了 ExecutorType.BATCH 的插入方式，性能顯著提升，不到 2s 便能全部插入完成。
總結(jié)一下，如果MyBatis需要進(jìn)行批量插入，推薦使用 ExecutorType.BATCH 的插入方式，如果非要使用
覺得本文對你有幫助？請分享給更多人
關(guān)注「全棧開發(fā)者社區(qū)」加星標(biāo)，提升全棧技能
本公眾號會不定期給大家發(fā)福利，包括送書、學(xué)習(xí)資源等，敬請期待吧！
如果感覺推送內(nèi)容不錯，不妨右下角點個在看轉(zhuǎn)發(fā)朋友圈或收藏，感謝支持。
好文章，留言、點贊、在看和分享一條龍吧??