前言
最近在优化公司的一个数据导入功能，里面涉及到比较复杂的逻辑，特别是最后一个列表更新，涉及到比较多的数据，在这里卡顿处主要为两处：
1.使用大量的查询条件去做修改删除操作
2.在一个for循环里面调用数据库做修改操作；
当数据量大的时候，服务非常的卡顿，在这里记录一下优化过程
现状
代码一: sql中有大量查询条件
Service 类
// 前方省略
// 执行list
List<Map<String, Object>> deleteDeformationList = new ArrayList<>();
List<Map<String, Object>> deleteDynamicsList = new ArrayList<>();
List<DeformationData> addDeformationList = new ArrayList<>();
List<DynamicsData> addDynamicsList = new ArrayList<>();
List<WarnRecord> addWarnRecordList = new ArrayList<>();
List<MonitorPoint> updatePointList = new ArrayList<>();
List<NewestRecord> newestRecords = new LinkedList<>();
for(int i = 0; i < dataList.size(); i++){
uploadData = dataList.get(i);
dataId = dataIds.get(i);
currView = pointViewMap.get(uploadData.getPointCode());
if(currView == null){
continue;
}
observedDate = uploadData.getObservedTime().substring(0, 10);
deleteMap = new HashMap<>();
deleteMap.put("pointCode", uploadData.getPointCode());
deleteMap.put("observedTime", observedDate);
deleteMap.put("proSiteId", currView.getProSiteId());
deleteMap.put("monProjectId", currView.getMonProjectId());
deleteMap.put("sourceId",sourceId);
currWarn = warnMap.get(currView.getPointId());
if(currView.getMonType() == MonitorConstant.PROJECTTYPE_DEFORMATION){
// 变形监测
deleteDeformationList.add(deleteMap);
speedValue = getSpeedValue(uploadData, sourceId, currView.getMonProjectId());
addDeformationList.add(createAddDeformation(sourceId, dataId, userId, currView, uploadData, speedValue));
addWarnRecordList.addAll(createAddDeformationWarn(sourceId, dataId, userId, currView, currWarn, uploadData, speedValue,newestRecords));
} else {
// 力学监测
deleteDynamicsList.add(deleteMap);
addDynamicsList.add(createAddDynamics(sourceId, dataId, userId, currView, uploadData));
addWarnRecordList.addAll(createAddDynamicsWarn(sourceId, dataId, userId, currView, currWarn, uploadData,newestRecords));
}
// 设置修改事件
updatePoint = new MonitorPoint();
updatePoint.setPointId(currView.getPointId());
updatePoint.setUpdateUser(userId);
updatePoint.setUpdateTime(LocalDateTime.now());
updatePointList.add(updatePoint);
}
// 处理集合
if(deleteDeformationList.size() > 0){
warnRecordMapper.batchDeleteWarnRecord(deleteDeformationList);
deformationDataMapper.batchDeleteData(deleteDeformationList);
}
if(deleteDynamicsList.size() > 0){
warnRecordMapper.batchDeleteWarnRecord(deleteDynamicsList);
dynamicsDataMapper.batchDeleteData(deleteDynamicsList);
}
// 后方省略mapper 中 sql 实现
<delete id="batchDeleteWarnRecord">
DELETE FROM WarnRecord WHERE
<foreach collection="lists" item="list" separator=" or " index="index">
( sourceId = #{list.sourceId,jdbcType=BIGINT}
AND monProjectId = #{list.monProjectId,jdbcType=BIGINT}
AND DATE_FORMAT(recordTime,"%Y/%m/%d") = #{list.observedTime,jdbcType=VARCHAR}
AND pointCode = #{list.pointCode,jdbcType=VARCHAR} )
</foreach>
</delete>在 where 查询条件后面，跟了太多的条件，导致查询起来非常慢
代码二: for循环中操作数据库
Service 类
@Override
public void updateNewestRecord(List<NewestRecord> records) {
for (int i = 0 ; i <records.size() ; i++){
NewestRecord record = records.get(i);
baseMapper.updateNewestRecord(record);
}
}mapper 中 sql 实现
<update id="updateNewestRecord">
update NewestRecord
set
warnStatus = if(warnStatus=1,1,if(#{record.warnStatus} = 1,if((select count(1) from MonitorSwitchRecord msr where msr.sourceId = #{record.sourceId} and msr.pointId=#{record.pointId} and date_format(msr.closeTime,'%Y-%m-%d %H:%i') > date_format(#{record.updateTime},'%Y-%m-%d %H:%i')) > 0,0,#{record.warnStatus}),#{record.warnStatus})),
recordValue = #{record.recordValue},
pointCode = #{record.pointCode},
updateTime = #{record.updateTime}
<if test="record.newestWarnLevel != null and record.newestRecordTime != null">
,
oldWarnLevel = IFNULL(newestWarnLevel,0),
newestWarnLevel = if(warnStatus=0,#{record.newestWarnLevel},if(#{record.newestWarnLevel} > IFNULL(newestWarnLevel,0),#{record.newestWarnLevel},IFNULL(newestWarnLevel,0))),
oldRecordTime = newestRecordTime,
newestRecordTime =#{record.newestRecordTime},
warnValue = #{record.warnValue}
</if>
where sourceId = #{record.sourceId} and skewId = #{record.skewId} and monProjectId = #{record.monProjectId} and pointId = #{record.pointId} and warnItem = #{record.warnItem} and date_format(updateTime,'%Y-%m-%d') <![CDATA[ <= ]]> date_format(#{record.updateTime},'%Y-%m-%d')
</update>之前用户导入少量数据时，可以正常使用；现在用户导入将近两千条数据，循环起来，不停的访问链接数据库，导致非常的卡顿，会导致前端超时。并且数据库服务器cpu报警
修改方案
代码一: 查出原始数据 java 中修改后在批量保存
针对代码一，先在数据库中查询满足条件的所有数据，然后在java代码中循环设置数据，再批量保存
Service 类
// warnRecordMapper.batchDeleteWarnRecord(deleteDynamicsList);
List<Long> recordIdList = new ArrayList<>();
List<WarnRecord> warnRecords = warnRecordMapper.selectListByObservedDate(projectId, sourceId, observedDate);
HashMap<String,WarnRecord> warnRecordHashMap = new HashMap<>();
for (WarnRecord warnRecord : warnRecords) {
warnRecordHashMap.put(warnRecord.getProjectSiteId()+ "_" +warnRecord.getMonProjectId() + "_" + warnRecord.getPointCode() ,warnRecord);
}
for (Map<String, Object> stringObjectMap : deleteDynamicsList) {
String proSiteId1 = stringObjectMap.get("proSiteId").toString();
String monProjectId1 = stringObjectMap.get("monProjectId").toString();
String pointCode1 = stringObjectMap.get("pointCode").toString();
WarnRecord warnRecord1 = warnRecordHashMap.get(proSiteId1 + "_" + monProjectId1 + "_" + pointCode1);
if (warnRecord1 != null){
recordIdList.add(warnRecord1.getRecordId());
}
}
if (!recordIdList.isEmpty()){
warnRecordMapper.deleteBatchIds(recordIdList);
}mapper 中 sql 实现
<select id="selectListByObservedDate" resultType="com.pip.monitor.entry.WarnRecord">
select * FROM WarnRecord WHERE
projectId = #{projectId,jdbcType=BIGINT}
AND sourceId = #{sourceId,jdbcType=BIGINT}
AND DATE_FORMAT(recordTime,"%Y/%m/%d") = #{observedDate,jdbcType=VARCHAR}
</select>代码二: 手动使用 MyBatis 的 BATCH 方式执行批量修改
使用 SqlSessionFactory 获取 SqlSession ,进行批量提交
@Autowired
private SqlSessionFactory sqlSessionFactory;
@Override
public void updateNewestRecord(List<NewestRecord> records) {
// 使用 MyBatis 的 BATCH 方式执行批量修改 By chengqichuan On 2023/2/13 14:17
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
NewestRecordMapper mapper = sqlSession.getMapper(NewestRecordMapper.class);
for (int i = 0 ; i <records.size() ; i++){
NewestRecord record = records.get(i);
mapper.updateNewestRecord(record);
//每 1000 条提交一次
if((i + 1) % 1000 == 0){
sqlSession.flushStatements();
}
}
sqlSession.flushStatements();
}在查找方案的时候看到网上还有一种 case函数语法 ,即把入参和条件 使用 case when 的语法组合起来，实现批量复杂数据修改，个人觉得不太清晰，
update tab_student set
classroom = case id
when 1 then "one"
when 2 then "tow"
when 3 then "three"
end
where id in (1,2,3)总结
在日常开发中，一定要重视这种循环调用数据库的操作，数据库连接的开启和关闭对数据库来说是巨大的消耗；还有大量数据的多个条件的复杂查询。
在数据量可控的情况下，查询有限数据在java中再来做复杂比对处理也不失为一种比较好的方案，或者某些场景下可以使用临时表等等。