前言

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