大家好，我是磊哥。
最近在项目中遇到了一个耗时较长的Job，其CPU占用率过高，经排查发现，主要时间消耗在通过MyBatis进行批量数据插入。mapper配置文件中使用了foreach循环进行批量插入，大致如下所示。（由于项目保密，以下代码均为自己手写的demo代码）
<insert id="batchInsert" parameterType="java.util.List">
insert into USER (id, name) values
<foreach collection="list" index="index" item="model" 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");在MySQL文档中也提到过这个技巧，如果要优化插入速度，可以将多个小操作合并成一个大操作。理想情况下，这样可以在单个连接中一次性发送许多新行的数据，并将所有索引更新和一致性检查推迟到最后进行。
乍一看，这个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.
SqlSession session = sessionFactory.openSession(ExecutorType.BATCH);
for (Model model : list) {
session.insert("insertStatement", model);
}
session.flushStatements();与默认的ExecutorType.SIMPLE不同，BATCH类型的执行器会在每次插入记录时准备一次语句并执行。
安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录，或需要将该能力封装成可公开复用的 Codex skill。
从资料中可以得知，默认的执行器类型为Simple，会为每个语句创建一个新的预处理语句，即创建一个PreparedStatement对象。在我们的项目中，会不断地使用这个批量插入方法，而由于MyBatis无法对包含<foreach></foreach>的语句进行缓存，所以每次调用方法时，都会重新解析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
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的方式进行批量插入，可以考虑减少一条insert语句中values的个数，最好能达到上述曲线的最低点，使速度最快。一般根据经验，一次性插入20~50行的数量是比较合适的，时间消耗也能接受。
重点来了。上面讲的是，如果非要使用<foreach></foreach>的方式进行插入，可以提升性能的方法。而实际上，MyBatis文档中推荐的批量插入方法是另一种方式。（可以参考http://www.mybatis.org/mybatis-dynamic-sql/docs/insert.html中的Batch Insert Support部分）
SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
SimpleTableMapper mapper = session.getMapper(SimpleTableMapper.class);
List<SimpleTableRecord> records = getRecordsToInsert(); // not shown
BatchInsert<SimpleTableRecord> 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设置为Batch，然后多次执行插入语句。这类似于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 < 100000; i++) {
ps.setString(1, "name_" + i);
ps.addBatch();
}
ps.executeBatch();
connection.commit();经过试验，使用ExecutorType.BATCH的插入方式，性能显著提升，不到2秒便能全部插入完成。
总结一下，如果MyBatis需要进行批量插入，推荐使用ExecutorType.BATCH的插入方式，如果非要使用<foreach></foreach>的插入方式，需要将每次插入的记录控制在20~50左右。
