redis 中缓存百万级别表的查询数据堕落：Query execution was interrupte - IT评测·应用市场-qidao123.com技术社区

@Component
@Slf4j
public class FirstInspectionPassRateTask {
@Autowired
private CheckResultMapper checkResultMapper;
@Autowired
private InspectionStationInfoMapper inspectionStationInfoMapper;
@Autowired
private RedisTemplate redisTemplate;
@Scheduled(cron = "0 32 13 * * ?")
public void calculateFirstInspectionPassRate(){
log.info("####### 定时任务开启......");
ValueOperations<String ,String> operations = redisTemplate.opsForValue();
NumberFormat numberFormat = NumberFormat.getPercentInstance();
numberFormat.setMinimumFractionDigits(2);
List<String> unitIdList = inspectionStationInfoMapper.selectUnitId();//单位id 集合
unitIdList = Optional.ofNullable(unitIdList).orElse(new ArrayList<>());
//这里只给2024表中的统计数据，因为首检合格率需要跨表，目前我也想不出如何编写 TODO
List<String> monthList = new ArrayList<>(Arrays.asList("03"));
unitIdList.forEach(unitId -> {
monthList.forEach(month -> {
List<String> bgList = new ArrayList<>();
//查询指定机构的首检报告列表
bgList = checkResultMapper.getFirstInspectionReportCount(unitId, "2024", month);
bgList = Optional.ofNullable(bgList).orElse(new ArrayList<>());
//计算指定机构的首检报告数量
Long bgSum = (long) bgList.size();
//首检合格率
String rate = "0.00%";
if(bgSum != 0){
//计算异常的首检报告数量
Long warnBgSum = checkResultMapper.getWarnFirstInspectionReportCount(unitId,"2024",month,bgList);
rate = numberFormat.format((bgSum-warnBgSum)/bgSum.doubleValue());
}
rate = rate.substring(0,rate.length() -1 );//去除最后的 % 方便前端处理
String key = String.format("SZJDC:CHECKRESULT:BGBH:%s",unitId+"-2024-"+month);
operations.set(key,rate,20, TimeUnit.DAYS);
});
});
}
}

复制代码

Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6eaf527b]
10:45:18.655 [pool-2-thread-1] ERROR o.s.s.s.TaskUtils$LoggingErrorHandler - Unexpected error occurred in scheduled task
org.springframework.dao.QueryTimeoutException:
### Error querying database. Cause: java.sql.SQLTimeoutException: Query execution was interrupted
### The error may exist in file [D:\workspace\IdeaProjects\szjdc-service\sz-warning-platform\target\classes\mappers\CheckResultMapper.xml]
### The error may involve com.iflytek.warning.mapper.CheckResultMapper.getFirstInspectionReportCount
### The error occurred while handling results
### SQL: select BGBH from check_result_2024 cr WHERE year(CHECK_DATE) = ? and month(CHECK_DATE) = ? and UNIT_ID = ? and NOT EXISTS ( select CHECK_ID from check_result_2024 pcr WHERE pcr.CHECK_DATE between DATE_SUB(cr.CHECK_DATE, INTERVAL 4 MONTH) and DATE_SUB(cr.CHECK_DATE , INTERVAL 1 DAY) and cr.VIN = pcr.VIN and UNIT_ID = ? )
### Cause: java.sql.SQLTimeoutException: Query execution was interrupted

复制代码