MyBatis 批量插入幾千條數(shù)據(jù)，請(qǐng)慎用foreach

點(diǎn)擊上方“碼農(nóng)突圍”，馬上關(guān)注
這里是碼農(nóng)充電第一站，回復(fù)“666”，獲取一份專屬大禮包
真愛，請(qǐng)?jiān)O(shè)置“星標(biāo)”或點(diǎn)個(gè)“在看

<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>

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

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

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.

• 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.

SqlSession?session?=?sessionFactory.openSession(ExecutorType.BATCH);
for?(Model?model?:?list)?{
????session.insert("insertStatement",?model);
}
session.flushStatements();

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.

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();
}

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

1. https://dev.mysql.com/doc/refman/5.6/en/insert-optimization.html

2. https://stackoverflow.com/questions/19682414/how-can-mysql-insert-millions-records-fast

3. https://stackoverflow.com/questions/32649759/using-foreach-to-do-batch-insert-with-mybatis/40608353

4. https://blog.csdn.net/wlwlwlwl015/article/details/50246717

5. http://blog.harawata.net/2016/04/bulk-insert-multi-row-vs-batch-using.html

6. https://www.red-gate.com/simple-talk/sql/performance/comparing-multiple-rows-insert-vs-single-row-insert-with-three-data-load-methods/

7. https://stackoverflow.com/questions/7004390/java-batch-insert-into-mysql-very-slow

8. http://www.mybatis.org/mybatis-dynamic-sql/docs/insert.html

-End-
最近有一些小伙伴，讓我?guī)兔φ乙恍?面試題?資料，于是我翻遍了收藏的 5T 資料后，匯總整理出來，可以說是程序員面試必備！所有資料都整理到網(wǎng)盤了，歡迎下載！

點(diǎn)擊??卡片，關(guān)注后回復(fù)【面試題】即可獲取