UserInfo.java
@Data
public class UserInfo implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 数据库字段信息:name_str VARCHAR(20)
*/
@NotEmpty(message = "NAME_NULL")
private String nameStr;
/**
* 数据库字段信息:age INT(10)
*/
@NotNull(message = "AGE_NULL")
private Long age;
private BigDecimal salary;
private String address;
}
UserMapper.java
public int insertBatch(@Param("list") List<UserInfo> userInfoList);
UserMapper.xml
<--useGeneratedKeys="true" 表示是否使用自增，true表示使用，false表示不使用
keyProperty="id" 表示id这个字段使用自增
-->
<insert id="insertBatch" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
insert into user_info(id,name_str,age)
values
<trim prefix="" suffixOverrides="," >
<--collection="list" 中的list对应mapper.java中的@Param("list") 中的list
item表示list中的对象
-->
<foreach item="model" index="index" collection="list">
(#{model.id,jdbcType=INTEGER},#{model.nameStr,jdbcType=VARCHAR},#{model.age,jdbcType=INTEGER} ),
</foreach>
</trim>
</insert>
UserInfoService.java
public int insertBatch(List<UserInfo> userInfoList){
return userMapper.insertBatch(userInfoList);
}
UserProviderApplicationTests.java
@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserProviderApplicationTests {
}
ServiceTest.java:
@Slf4j
public class ServiceTest extends UserProviderApplicationTests {
@Autowired
private UserInfoService userInfoService;
@Test
public void testInsertData(){
List<UserInfo> userInfoList = new ArrayList<>(10000);
for (long i = 0L; i < 10000L; i++) {
UserInfo userInfo = new UserInfo();
userInfo.setNameStr("张三"+i);
userInfo.setAge(i);
userInfo.setSalary(new BigDecimal(i));
userInfo.setAddress("深圳宝安");
userInfoList.add(userInfo);
}
List<List<UserInfo>> lists100 = splitList(userInfoList, 100);
long start100 = System.currentTimeMillis();
lists100.stream().forEach(o->{
long insertStart100 = System.currentTimeMillis();
userInfoService.insertBatch(o);
log.info("insertStart100 the time cost is {}",(System.currentTimeMillis()-insertStart100));
});
log.info("the split list to 100 the time cost is {}",(System.currentTimeMillis()-start100));
List<UserInfo> userInfoList500 = new ArrayList<>(10000);
for (long i = 0L; i < 10000L; i++) {
UserInfo userInfo = new UserInfo();
userInfo.setNameStr("张三"+i);
userInfo.setAge(i);
userInfo.setSalary(new BigDecimal(i));
userInfo.setAddress("深圳宝安");
userInfoList500.add(userInfo);
}
List<List<UserInfo>> lists500 = splitList(userInfoList500, 500);
long start500 = System.currentTimeMillis();
lists500.stream().forEach(o->{
long insertStart500 = System.currentTimeMillis();
userInfoService.insertBatch(o);
log.info("insertStart500 the time cost is {}",(System.currentTimeMillis()-insertStart500));
});
log.info("the split list to 500 the time cost is {}",(System.currentTimeMillis()-start500));
List<UserInfo> userInfoList1000 = new ArrayList<>(10000);
for (long i = 0L; i < 10000L; i++) {
UserInfo userInfo = new UserInfo();
userInfo.setNameStr("张三"+i);
userInfo.setAge(i);
userInfo.setSalary(new BigDecimal(i));
userInfo.setAddress("深圳宝安");
userInfoList1000.add(userInfo);
}
List<List<UserInfo>> lists1000 = splitList(userInfoList1000, 1000);
long start1000 = System.currentTimeMillis();
lists1000.stream().forEach(o->{
long insertStart1000 = System.currentTimeMillis();
userInfoService.insertBatch(o);
log.info("insertStart1000 the time cost is {}",(System.currentTimeMillis()-insertStart1000));
});
log.info("the split list to 1000 the time cost is {}",(System.currentTimeMillis()-start1000));
List<UserInfo> userInfoList2000 = new ArrayList<>(10000);
for (long i = 0L; i < 10000L; i++) {
UserInfo userInfo = new UserInfo();
userInfo.setNameStr("张三"+i);
userInfo.setAge(i);
userInfo.setSalary(new BigDecimal(i));
userInfo.setAddress("深圳宝安");
userInfoList2000.add(userInfo);
}
List<List<UserInfo>> lists2000 = splitList(userInfoList2000, 2000);
long start2000 = System.currentTimeMillis();
lists2000.stream().forEach(o->{
long insertStart2000 = System.currentTimeMillis();
userInfoService.insertBatch(o);
log.info("insertStart2000 the time cost is {}",(System.currentTimeMillis()-insertStart2000));
});
log.info("the split list to 2000 the time cost is {}",(System.currentTimeMillis()-start2000));
List<UserInfo> userInfoList3000 = new ArrayList<>(10000);
for (long i = 0L; i < 10000L; i++) {
UserInfo userInfo = new UserInfo();
userInfo.setNameStr("张三"+i);
userInfo.setAge(i);
userInfo.setSalary(new BigDecimal(i));
userInfo.setAddress("深圳宝安");
userInfoList3000.add(userInfo);
}
List<List<UserInfo>> lists3000 = splitList(userInfoList3000, 3000);
long start3000 = System.currentTimeMillis();
lists3000.stream().forEach(o->{
long insertStart3000 = System.currentTimeMillis();
userInfoService.insertBatch(o);
log.info("insertStart3000 the time cost is {}",(System.currentTimeMillis()-insertStart3000));
});
log.info("the split list to 3000 the time cost is {}",(System.currentTimeMillis()-start3000));
List<UserInfo> userInfoList4000 = new ArrayList<>(10000);
for (long i = 0L; i < 10000L; i++) {
UserInfo userInfo = new UserInfo();
userInfo.setNameStr("张三"+i);
userInfo.setAge(i);
userInfo.setSalary(new BigDecimal(i));
userInfo.setAddress("深圳宝安");
userInfoList4000.add(userInfo);
}
List<List<UserInfo>> lists4000 = splitList(userInfoList4000, 4000);
long start4000 = System.currentTimeMillis();
lists4000.stream().forEach(o->{
long insertStart4000 = System.currentTimeMillis();
userInfoService.insertBatch(o);
log.info("insertStart4000 the time cost is {}",(System.currentTimeMillis()-insertStart4000));
});
log.info("the split list to 4000 the time cost is {}",(System.currentTimeMillis()-start4000));
List<UserInfo> userInfoList5000 = new ArrayList<>(10000);
for (long i = 0L; i < 10000L; i++) {
UserInfo userInfo = new UserInfo();
userInfo.setNameStr("张三"+i);
userInfo.setAge(i);
userInfo.setSalary(new BigDecimal(i));
userInfo.setAddress("深圳宝安");
userInfoList5000.add(userInfo);
}
List<List<UserInfo>> lists5000 = splitList(userInfoList5000, 5000);
long start5000 = System.currentTimeMillis();
lists5000.stream().forEach(o->{
long insertStart5000 = System.currentTimeMillis();
userInfoService.insertBatch(o);
log.info("insertStart5000 the time cost is {}",(System.currentTimeMillis()-insertStart5000));
});
log.info("the split list to 5000 the time cost is {}",(System.currentTimeMillis()-start5000));
}
public static <T> List<List<T>> splitList(List<T> list, int groupSize) {
int length = list.size();
// 计算可以分成多少组
int num = (length + groupSize - 1) / groupSize;
List<List<T>> newList = new ArrayList<>(num);
for (int i = 0; i < num; i++) {
// 开始位置
int fromIndex = i * groupSize;
// 结束位置
int toIndex = (i + 1) * groupSize < length ? (i + 1) * groupSize : length;
newList.add(list.subList(fromIndex, toIndex));
}
return newList;
}
}
测试结果：一次1000，性能比较好，从结果中可以看出1000，2000，3000，4000，5000 所用时间相差不大，但一次1000时插入表中时间比较短，即锁表时间也是很短的
insertStart100 the time cost is 515
insertStart100 the time cost is 22
insertStart100 the time cost is 23
insertStart100 the time cost is 19
insertStart100 the time cost is 31
insertStart100 the time cost is 23
insertStart100 the time cost is 19
insertStart100 the time cost is 20
insertStart100 the time cost is 30
insertStart100 the time cost is 21
insertStart100 the time cost is 24
insertStart100 the time cost is 22
insertStart100 the time cost is 21
insertStart100 the time cost is 24
insertStart100 the time cost is 32
insertStart100 the time cost is 19
insertStart100 the time cost is 17
insertStart100 the time cost is 14
insertStart100 the time cost is 14
insertStart100 the time cost is 14
insertStart100 the time cost is 14
insertStart100 the time cost is 18
insertStart100 the time cost is 14
insertStart100 the time cost is 12
insertStart100 the time cost is 14
insertStart100 the time cost is 16
insertStart100 the time cost is 12
insertStart100 the time cost is 12
insertStart100 the time cost is 14
insertStart100 the time cost is 13
insertStart100 the time cost is 12
insertStart100 the time cost is 14
insertStart100 the time cost is 12
insertStart100 the time cost is 12
insertStart100 the time cost is 15
insertStart100 the time cost is 14
insertStart100 the time cost is 20
insertStart100 the time cost is 18
insertStart100 the time cost is 26
insertStart100 the time cost is 27
insertStart100 the time cost is 14
insertStart100 the time cost is 13
insertStart100 the time cost is 10
insertStart100 the time cost is 11
insertStart100 the time cost is 10
insertStart100 the time cost is 9
insertStart100 the time cost is 11
insertStart100 the time cost is 11
insertStart100 the time cost is 13
insertStart100 the time cost is 18
insertStart100 the time cost is 12
insertStart100 the time cost is 15
insertStart100 the time cost is 17
insertStart100 the time cost is 11
insertStart100 the time cost is 10
insertStart100 the time cost is 11
insertStart100 the time cost is 12
insertStart100 the time cost is 12
insertStart100 the time cost is 14
insertStart100 the time cost is 10
insertStart100 the time cost is 10
insertStart100 the time cost is 13
insertStart100 the time cost is 12
insertStart100 the time cost is 12
insertStart100 the time cost is 9
insertStart100 the time cost is 9
insertStart100 the time cost is 17
insertStart100 the time cost is 10
insertStart100 the time cost is 10
insertStart100 the time cost is 10
insertStart100 the time cost is 11
insertStart100 the time cost is 18
insertStart100 the time cost is 14
insertStart100 the time cost is 9
insertStart100 the time cost is 8
insertStart100 the time cost is 9
insertStart100 the time cost is 9
insertStart100 the time cost is 10
insertStart100 the time cost is 9
insertStart100 the time cost is 9
insertStart100 the time cost is 11
insertStart100 the time cost is 10
insertStart100 the time cost is 12
insertStart100 the time cost is 10
insertStart100 the time cost is 10
insertStart100 the time cost is 12
insertStart100 the time cost is 21
insertStart100 the time cost is 11
insertStart100 the time cost is 11
insertStart100 the time cost is 12
insertStart100 the time cost is 11
insertStart100 the time cost is 11
insertStart100 the time cost is 10
insertStart100 the time cost is 10
insertStart100 the time cost is 12
insertStart100 the time cost is 10
insertStart100 the time cost is 10
insertStart100 the time cost is 13
insertStart100 the time cost is 12
insertStart100 the time cost is 9
the split list to 100 the time cost is 1946
insertStart500 the time cost is 35
insertStart500 the time cost is 35
insertStart500 the time cost is 60
insertStart500 the time cost is 24
insertStart500 the time cost is 31
insertStart500 the time cost is 25
insertStart500 the time cost is 33
insertStart500 the time cost is 27
insertStart500 the time cost is 36
insertStart500 the time cost is 26
insertStart500 the time cost is 24
insertStart500 the time cost is 24
insertStart500 the time cost is 24
insertStart500 the time cost is 24
insertStart500 the time cost is 24
insertStart500 the time cost is 32
insertStart500 the time cost is 24
insertStart500 the time cost is 27
insertStart500 the time cost is 23
insertStart500 the time cost is 23
the split list to 500 the time cost is 585
insertStart1000 the time cost is 44
insertStart1000 the time cost is 49
insertStart1000 the time cost is 49
insertStart1000 the time cost is 43
insertStart1000 the time cost is 44
insertStart1000 the time cost is 46
insertStart1000 the time cost is 42
insertStart1000 the time cost is 45
insertStart1000 the time cost is 46
insertStart1000 the time cost is 43
the split list to 1000 the time cost is 454
insertStart2000 the time cost is 91
insertStart2000 the time cost is 97
insertStart2000 the time cost is 87
insertStart2000 the time cost is 94
insertStart2000 the time cost is 85
the split list to 2000 the time cost is 454
insertStart3000 the time cost is 127
insertStart3000 the time cost is 125
insertStart3000 the time cost is 141
insertStart3000 the time cost is 68
the split list to 3000 the time cost is 461
insertStart4000 the time cost is 184
insertStart4000 the time cost is 171
insertStart4000 the time cost is 88
the split list to 4000 the time cost is 445
insertStart5000 the time cost is 215
insertStart5000 the time cost is 216
the split list to 5000 the time cost is 432
