前言
最近在优化公司的一个数据导入功能,里面涉及到比较复杂的逻辑,特别是最后一个列表更新,涉及到比较多的数据,在这里卡顿处主要为两处:
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中再来做复杂比对处理也不失为一种比较好的方案,或者某些场景下可以使用临时表等等。