在项目开发中,经常面临数据的批量导入以及接口数据的批量插入或更新操作的需求。利用项目间隙,整理了下找到的解决方案,并对每个方案的性能进行了简单测试。代码地址:github。
项目中使用了postgresql数据库。
目录
一、批量插入
批量插入有四种方案。代码如下:
1. service层
/**
* 使用mybatisplus 批量插入
* @param list 用户集合
* @return @link com.ufc.dream.web_start.entity.SysUser
*/
int saveBatchMybatisPlus(List<SysUser> list);
/**
* 使用 循环插入
* @param list
* @return
*/
int saveForeach(List<SysUser> list);
/**
* 使用sqlSession批量插入
* @param list
* @return
*/
int saveSession(List<SysUser> list);
/**
* 拼装sql批量插入
* @param list
* @return
*/
int saveSql(List<SysUser> list);
service实现
@Override
@Transactional(rollbackFor = Exception.class)
public int saveBatchMybatisPlus(List<SysUser> list) {
saveBatch(list);
return list.size();
}
@Override
@Transactional(rollbackFor = Exception.class)
public int saveForeach(List<SysUser> list) {
for (SysUser user : list) {
getBaseMapper().insertEntity(user);
}
return list.size();
}
@Override
public int saveSession(List<SysUser> list) {
SqlSession batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
// 获取批量操作的新的map
SysUserMapper mapper = batchSqlSession.getMapper(SysUserMapper.class);
for (SysUser user : list) {
mapper.insertEntity(user);
}
// session 提交
batchSqlSession.commit();
}catch (Exception e){
e.printStackTrace();
batchSqlSession.rollback();
} finally {
// session 关闭
batchSqlSession.close();
}
return list.size();
}
// 超过数据库长度限制,分批插入
@Override
@Transactional(rollbackFor = Exception.class)
public int saveSql(List<SysUser> list) {
int size = list.size();
int num = size/1000;
if (size%1000!=0){
num += 1;
}
for (int i = 0; i < num; i++) {
if (i == num - 1) {
getBaseMapper().insertEntitySql(list.subList(i*1000, list.size()));
}
getBaseMapper().insertEntitySql(list.subList(i*1000, (i+1) * 1000));
}
return list.size();
}
因为mybatisPlus自带的saveBatch方法上有@Transactional注解,影响事务管理器的选择,所以我重写了saveBatch方法。
public class BaseServiceImpl <M extends BaseMapper<T>, T > extends ServiceImpl<M, T> implements IBaseService<T> {
@Override
public boolean saveBatch(Collection<T> entityList) {
String sqlStatement = this.sqlStatement(SqlMethod.INSERT_ONE);
return this.executeBatch(entityList, 1000, (sqlSession, entity) -> {
sqlSession.insert(sqlStatement, entity);
});
}
}
2.Mapper层
mapper接口:
int insertEntity(SysUser user);
int insertEntitySql(List<SysUser> list);
mapper.xml
<insert id="insertEntity">
insert into sys_user (user_no, weixin, phone, user_name, password)
values (#{userNo}, #{weixin}, #{phone}, #{userName}, #{password})
</insert>
<insert id="insertEntitySql">
insert into sys_user (user_no, weixin, phone, user_name, password)
values
<foreach collection="list" item="item" separator=",">
(#{item.userNo}, #{item.weixin}, #{item.phone}, #{item.userName}, #{item.password})
</foreach>
</insert>
3.实体类
bean定义
@TableName(value ="sys_user")
@Data
public class SysUser implements Serializable {
/**
*
*/
@TableId(value = "id", type = IdType.AUTO)
private Long id;
/**
*
*/
private String userNo;
/**
*
*/
private String weixin;
/**
*
*/
private String phone;
/**
*
*/
private String userName;
/**
*
*/
private String password;
}
4.测试类以及测试结果
测试类
/**
* 插入10000条耗时
* Foreach: 2452
* Session: 277
* Sql: 602
* mybatisPlus: 436
*/
@Test
public void testSave(){
List<SysUser> list = new ArrayList<SysUser>();
SysUser user = null;
for (int i = 0; i < 10000; i++) {
user = new SysUser();
user.setUserNo("1");
user.setUserName("username");
list.add(user);
}
long start = System.currentTimeMillis();
sysUserServiceImpl.saveForeach(list);
long foreachEnd = System.currentTimeMillis();
sysUserServiceImpl.saveSession(list);
long sessionEnd = System.currentTimeMillis();
sysUserServiceImpl.saveSql(list);
long sqlEnd = System.currentTimeMillis();
sysUserServiceImpl.saveBatchMybatisPlus(list);
long mybatisPlusEnd = System.currentTimeMillis();
System.out.println("Foreach: " + (foreachEnd - start));
System.out.println("Session: " + (sessionEnd - foreachEnd));
System.out.println("Sql: " + (sqlEnd - sessionEnd));
System.out.println("mybatisPlus: " + (mybatisPlusEnd - sqlEnd));
}
测试结果:
插入1000条的耗时:
插入10000条耗时:
其中foreach循环是最慢的,使用SqlSession的批量操作是最快的。mybatisplus的saveBatch的实现也是使用了SqlSession但逻辑更复杂稍慢。
二、批量更新
测试使用的根据主键来更新数据。主要的方案有循环更新、mybatisPlus批量更新、sqlsession批量处理,使用case拼装sql、还有直接拼装sql。
更新service层
/**
* 根据 id 使用mybatisplus 批量插入
* @param list
* @return
*/
int updateBatchWithId(List<SysUser> list);
/**
* 使用 case when 批量更新
* @param list
* @return
*/
int updateCase(List<SysUser> list);
/**
* 使用 拼装sql批量更新
* @param list
* @return
*/
int updateSql(List<SysUser> list);
/**
* 使用 sqlsession 批量更新
* @param list
* @return
*/
int updateSession(List<SysUser> list);
/**
* 使用foreach 批量更新
* @param list
* @return
*/
int updateForeach(List<SysUser> list);
service实现
@Override
@Transactional(rollbackFor = Exception.class)
public int updateBatchWithId(List<SysUser> list) {
updateBatchById(list);
return list.size();
}
@Override
@Transactional(rollbackFor = Exception.class)
public int updateCase(List<SysUser> list) {
getBaseMapper().updateUserNameByIdSql(list);
return list.size();
}
@Override
@Transactional(rollbackFor = Exception.class)
public int updateSql(List<SysUser> list) {
getBaseMapper().updateUserNameByIdSql(list);
return list.size();
}
@Override
public int updateSession(List<SysUser> list) {
SqlSession batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
// 获取批量操作的新的map
SysUserMapper mapper = batchSqlSession.getMapper(SysUserMapper.class);
for (SysUser user : list) {
mapper.updateUserNameById(user);
}
// session 提交
batchSqlSession.commit();
}catch (Exception e){
e.printStackTrace();
batchSqlSession.rollback();
} finally {
// session 关闭
batchSqlSession.close();
}
return list.size();
}
@Override
@Transactional(rollbackFor = Exception.class)
public int updateForeach(List<SysUser> list) {
for (SysUser user : list) {
getBaseMapper().updateUserNameById(user);
}
return list.size();
}
mapper层
mapper接口
int updateUserNameById(SysUser user);
int updateUserNameByIdCase(List<SysUser> list);
int updateUserNameByIdSql(List<SysUser> list);
mapper的xml文件
<update id="updateUserNameById">
update sys_user set user_name = #{userName}
where id = #{id}
</update>
<update id="updateUserNameByIdCase" parameterType="list">
update sys_user set user_name =
<foreach collection="list" item="item" separator=" " open="case id" close="end">
when #{item.id} then #{item.userName}
</foreach>
where id in
<foreach collection="list" item="item" separator="," open="(" close=")">
#{item}
</foreach>
</update>
<update id="updateUserNameByIdSql">
<foreach collection="list" item="item" separator=";">
update sys_user set user_name = #{item.userName}
where id = #{item.id}
</foreach>
</update>
测试类以及测试结果
/**
* 更新10000 条耗时
* Foreach: 1570
* Session: 344
* Sql: 1283
* Case: 1076
* mybatisPlus: 510
*/
@Test
public void testUpdate(){
List<SysUser> listFor = sysUserServiceImpl.queryTestList(1,10000);
List<SysUser> listSes = sysUserServiceImpl.queryTestList(10000,10000);
List<SysUser> listSql = sysUserServiceImpl.queryTestList(20000,10000);
List<SysUser> listCase= sysUserServiceImpl.queryTestList(30000,10000);
List<SysUser> listPlus = sysUserServiceImpl.queryTestList(40000,10000);
long start = System.currentTimeMillis();
sysUserServiceImpl.updateForeach(listFor);
long foreachEnd = System.currentTimeMillis();
sysUserServiceImpl.updateSession(listSes);
long sessionEnd = System.currentTimeMillis();
sysUserServiceImpl.updateSql(listSql);
long sqlEnd = System.currentTimeMillis();
sysUserServiceImpl.updateCase(listCase);
long caseEnd = System.currentTimeMillis();
sysUserServiceImpl.updateBatchWithId(listPlus);
long plusEnd = System.currentTimeMillis();
System.out.println("Foreach: " + (foreachEnd - start));
System.out.println("Session: " + (sessionEnd - foreachEnd));
System.out.println("Sql: " + (sqlEnd - sessionEnd));
System.out.println("Case: " + (caseEnd - sqlEnd));
System.out.println("mybatisPlus: " + (plusEnd - caseEnd));
}
测试更新1000条耗时:
测试更新10000条耗时:
更新的耗时结果和插入的耗时结果相一致。都是使用sqlsession的耗时是最短的,mybatisplus的批量操作耗时和它最接近。foreach插入效率是最低的。