背景:
上一篇说到批量插入数据,这里详细对比foreach和PreparedStatement两者如何选择。
分别插入不同数据量对比两种方式的效率。
插入字段值30+以上,小于30个不统计。
1.foreach
controller:
@PostMapping("batchInsertOfList")
public void batchInsertOfList(){
int limit = 2000;
User user = new User();
user.setLimit(limit);
int count = 5;
List<User> result = userMapper.query(user);
Long t1 = 0L;
for (int i = 0; i < count; i++) {
long l = System.currentTimeMillis();
userMapper.batchInsertOfList(result);
long t2 = System.currentTimeMillis() - l;
System.out.println(t2);
t1 += t2;
}
System.out.println("foreach插入"+limit+"条数据"+count+"次。平均耗时->"+t1/count);
}
dao:
void batchInsertOfList(@Param("users") List<User> users);
xml:
<insert id="batchInsertOfList">
insert into user (name,age
,school_phone1
,school_phone2
,school_phone3
,school_phone4
,school_phone5
,school_phone6
,school_phone7
,school_phone8
,school_phone9
,school_phone10
,school_phone11
,school_phone12
,school_phone13
,school_phone14
,school_phone15
,school_phone16
,school_phone17
,school_phone18
,school_phone19
,school_phone20
,school_phone21
,school_phone22
,school_phone23
,school_phone24
,school_phone25
,school_phone26
,school_phone27
,school_phone28
,school_phone29
,school_phone30
,school_phone31
,school_phone32
)values
<foreach collection="users" item="item" separator="," close=";">
(#{item.name},#{item.age}
,#{item.schoolPhone1}
,#{item.schoolPhone2}
,#{item.schoolPhone3}
,#{item.schoolPhone4}
,#{item.schoolPhone5}
,#{item.schoolPhone6}
,#{item.schoolPhone7}
,#{item.schoolPhone8}
,#{item.schoolPhone9}
,#{item.schoolPhone10}
,#{item.schoolPhone11}
,#{item.schoolPhone12}
,#{item.schoolPhone13}
,#{item.schoolPhone14}
,#{item.schoolPhone15}
,#{item.schoolPhone16}
,#{item.schoolPhone17}
,#{item.schoolPhone18}
,#{item.schoolPhone19}
,#{item.schoolPhone20}
,#{item.schoolPhone21}
,#{item.schoolPhone22}
,#{item.schoolPhone23}
,#{item.schoolPhone24}
,#{item.schoolPhone25}
,#{item.schoolPhone26}
,#{item.schoolPhone27}
,#{item.schoolPhone28}
,#{item.schoolPhone29}
,#{item.schoolPhone30}
,#{item.schoolPhone31}
,#{item.schoolPhone32}
)
</foreach>
</insert>
2.PreparedStatement
controller:
@PostMapping("batchInsertDriverManage")
public void batchInsertDriverManage() throws Exception {
Long t1 = 0L;
int limit = 2000;
int count = 5;
for (int j = 0; j < count; j++) {
long l = System.currentTimeMillis();
Connection connection =
DriverManager.getConnection(mysqlUrl, mysqlUsername, mysqlPassword);
connection.setAutoCommit(false);
StringBuffer sql =
new StringBuffer("insert into user (name,age,school_phone1\n" +
" ,school_phone2\n" +
" ,school_phone3\n" +
" ,school_phone4\n" +
" ,school_phone5\n" +
" ,school_phone6\n" +
" ,school_phone7\n" +
" ,school_phone8\n" +
" ,school_phone9\n" +
" ,school_phone10\n" +
" ,school_phone11\n" +
" ,school_phone12\n" +
" ,school_phone13\n" +
" ,school_phone14\n" +
" ,school_phone15\n" +
" ,school_phone16\n" +
" ,school_phone17\n" +
" ,school_phone18\n" +
" ,school_phone19\n" +
" ,school_phone20\n" +
" ,school_phone21\n" +
" ,school_phone22\n" +
" ,school_phone23\n" +
" ,school_phone24\n" +
" ,school_phone25\n" +
" ,school_phone26\n" +
" ,school_phone27\n" +
" ,school_phone28\n" +
" ,school_phone29\n" +
" ,school_phone30\n" +
" ,school_phone31\n" +
" ,school_phone32)values(" +
"?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
PreparedStatement ps = connection.prepareStatement(sql.toString());
for (int i = 0; i < limit; i++) {
ps.setString(1,"my");
ps.setString(2,"24");
ps.setString(3,"默认值");
ps.setString(4,"默认值");
ps.setString(5,"默认值");
ps.setString(6,"默认值");
ps.setString(7,"默认值");
ps.setString(8,"默认值");
ps.setString(9,"默认值");
ps.setString(10,"默认值");
ps.setString(11,"默认值");
ps.setString(12,"默认值");
ps.setString(13,"默认值");
ps.setString(14,"默认值");
ps.setString(15,"默认值");
ps.setString(16,"默认值");
ps.setString(17,"默认值");
ps.setString(18,"默认值");
ps.setString(19,"默认值");
ps.setString(20,"默认值");
ps.setString(21,"默认值");
ps.setString(22,"默认值");
ps.setString(23,"默认值");
ps.setString(24,"默认值");
ps.setString(25,"默认值");
ps.setString(26,"默认值");
ps.setString(27,"默认值");
ps.setString(28,"默认值");
ps.setString(29,"默认值");
ps.setString(30,"默认值");
ps.setString(31,"默认值");
ps.setString(32,"默认值");
ps.setString(33,"默认值");
ps.setString(34,"默认值");
ps.addBatch();
}
ps.executeBatch();
connection.commit();
connection.close();
long t2 = System.currentTimeMillis() - l;
System.out.println(t2);
t1 += t2;
}
System.out.println("pstm插入"+limit+"数据"+count+"次。平均耗时->"+t1/count);
}
3、对比
可以很明显看到当数据量大于4W的时候pstm的效率比foreach高50%左右。
在数据量不上万的情况下两者选foreach编码简单,效率可以忽略。
foreach之所以慢是因为MyBatis在填充#{}值的时候映射耗时。