记录接口查询缓慢优化，SQL生成数据由O(n)→O(1)过程

前言

        最近项目中遇到个获取消息的接口查询特别慢，花费近10多秒，就因为查询的时间特别久，当前端频繁的切换在请求接口时，还会造成生成数据的重复；

一 问题排查

1.代码排查

       经排查，后端业务生成数据时，都会先删除一下旧数据，再生成新数据，但是在生成这里花费的时间特别久，多个请求都进入了这里写数据，造成了重复数据生成

systemMessageDao.deleteInjectionRecordFinishedByGWDoctor(Long.valueOf(user.getUserId()));
testPats.forEach(item -> // 这里处理数据部分，花费的时间特别久handlerGWDoctorMessage(item, user, res, messageValueMap)
);

2. SQL 排查

       数据生成的SQL是花费时间最久的，细看，还是有很多的问题的，第一段生成入组前的数据，6个独立的SELECT COUNT(1)…UNION ALL查询，就会执行6次查询 + 6次插入，第二段，跟入组时间有月份差值的，那就是差值的月份 x 6 次查询和插入，多次的查询插入，不仅耗时巨大，还会占据更多的内存；

		-- 第一段SQLINSERT INTO gw_doctor_messages (num, injectionNumber,typed,userid,createtime,status,patid)select count(1),-1 injectionNumber,'18' typed,user_id,NOW(),1,pat_idfrom test_pat_file tpf where filetype = 1 and patid = pat_id and status = 1 union allselect count(1),-1 injectionNumber,'19' typed,user_id,NOW(),1,pat_idfrom test_pat_file tpf where filetype = 2 and patid = pat_id and status = 1 union all select count(1),-1 injectionNumber,'25' typed,user_id,NOW(),1,pat_idfrom test_pat_gw_eval tple where patid = pat_id and evalid = 'Tab0002' and status =1 and reportnumber = -1union all select count(1),-1 injectionNumber,'28' typed,user_id,NOW(),1,pat_idfrom test_pat_gw_eval tple where patid = pat_id and evalid = 'Tab0003' and status =1 and reportnumber = -1union all select count(1),-1 injectionNumber,'31' typed,user_id,NOW(),1,pat_idfrom test_pat_gw_eval tple where patid = pat_id and evalid = 'Tab0004' and status =1 and reportnumber = -1union all select count(1),-1 injectionNumber,'35' typed,user_id,NOW(),1,pat_idfrom test_pat_economics_medical tpem where status = 1 and patid = pat_id and reportnumber = -1;-- 第二段SQLFOR i IN 1..month_floor LOOPINSERT INTO gw_doctor_messages (num, injectionNumber,typed,userid,createtime,status,patid)select count(1),i injectionNumber, '20' typed,user_id,NOW(),1,pat_idfrom test_pat_gw_eval tple where patid = pat_id and evalid = 'Tab0001' and status = 1 and reportnumber = iunion allselect count(1),i injectionNumber, '26' typed,user_id,NOW(),1,pat_idfrom test_pat_gw_eval tple where patid = pat_id and evalid = 'Tab0002' and status = 1 and reportnumber = iunion allselect count(1),i injectionNumber, '29' typed,user_id,NOW(),1,pat_idfrom test_pat_gw_eval tple where patid = pat_id and evalid = 'Tab0003' and status = 1 and reportnumber = iunion all select count(1) ,i injectionNumber, '32' typed,user_id,NOW(),1,pat_idfrom test_pat_gw_eval tple where patid = pat_id and evalid = 'Tab0004' and status =1 and reportnumber = iunion allselect count(1) ,i injectionNumber, '36' typed,user_id,NOW(),1,pat_idfrom test_pat_economics_medical tpem where status = 1 and patid = pat_id and reportnumber = iunion allselect count(1) ,i injectionNumber, '34' typed,user_id,NOW(),1,pat_idfrom test_pat_risk tpr where status =1 and patid = pat_id and reportmonth = i;END LOOP;

二 问题解决

1.代码处处理

       生成数据部分用redis 锁 锁住接口，没有拿到锁的就先返回，同时将生成的数据放至缓存中，设置过期时间，避免频繁的与数据库交付；

String lockKey = "GW_DOCTOR_LOCK:" + user.getUserId();
String requestId = UUID.randomUUID().toString();
boolean locked = false;for (int i = 0; i < 5; i++) { // 最多尝试5次locked = redisTemplate.opsForValue().setIfAbsent(lockKey, requestId, 30, TimeUnit.SECONDS);if (locked) break;try {Thread.sleep(200); // 等待200ms再试} catch (InterruptedException e) {Thread.currentThread().interrupt();}
}if (!locked) {LOGGER.warn("未获取到锁，跳过执行");return;
}try {systemMessageDao.deleteInjectionRecordFinishedByGWDoctor(Long.valueOf(user.getUserId()));List<MessageVo> testPats = systemMessageDao.getInputGroupPatByGW(user.getUserId());testPats.forEach(item -> handlerGWDoctorMessage(item, user, res, messageValueMap));
} finally {if (requestId.equals(redisTemplate.opsForValue().get(lockKey))) {redisTemplate.delete(lockKey);}
}// 结果缓存
String cacheKey = "GW_RESULT:" + user.getUserId();
String cachedResult = redisTemplate.opsForValue().get(cacheKey);
if (cachedResult != null) {return JSON.parseObject(cachedResult, Result.class);
}Result result = Result.success(...);
redisTemplate.opsForValue().set(cacheKey, JSON.toJSONString(result), 5, TimeUnit.MINUTES);
return result;

2. SQL 处理

       避免多次的循环查询与插入，调整为一次查询（结果聚合）一次插入，由O(n) →O(1)

第一段SQL调整：

		insert into gw_doctor_messages (num, injectionNumber,typed,userid,createtime,status,patid)select count(1),-1,typed,user_id,NOW(),1,pat_idfrom (-- 18 附件上传-申请表 select '18' typed from test_pat_file tpf where filetype = 1 and patid = pat_id and status = 1 union all-- 19 附件上传-知情同意书select '19' typed from test_pat_file tpf where filetype = 2 and patid = pat_id and status = 1 union all-- 25 生活质量量表 入组前select '25' typed from test_pat_gw_eval tple where patid = pat_id and evalid = 'Tab0002' and status =1 and reportnumber = -1union all-- 28 患者自我病耻感量表 入组前select '28' typed from test_pat_gw_eval tple where patid = pat_id and evalid = 'Tab0003' and status =1 and reportnumber = -1union all-- 31 Zarit照顾负担量表 入组前select '31' typed from test_pat_gw_eval tple where patid = pat_id and evalid = 'Tab0004' and status =1 and reportnumber = -1union all-- 35 患者家庭经济、医疗费用评价 入组前select '35' typed from test_pat_economics_medical tpem where status = 1 and patid = pat_id and reportnumber = -1) as combined group by typed;

第二段SQL调整：

		INSERT INTO gw_doctor_messages (num, injectionNumber, typed, userid, createtime, status, patid)SELECT num,injectionNumber,typed,userid,NOW( ),status,patid FROM(-- 长效针剂治疗满意度调查问卷SELECT COALESCE(COUNT(e.*), 0) num, g.month injectionNumber, '20' typed, user_id userid, 1 status, pat_id patidFROM generate_series(1, month_floor) AS g(month)LEFT JOIN test_pat_gw_eval e ON e.patid = pat_id AND e.evalid = 'Tab0001' AND e.status = 1 AND e.reportnumber = g.monthGROUP BY g.monthUNION ALL-- 生活质量量表 入组后 GWSELECT COALESCE(COUNT(e.*), 0) num, g.month injectionNumber, '26' typed, user_id userid, 1 status, pat_id patidFROM generate_series(1, month_floor) AS g(month)LEFT JOIN test_pat_gw_eval e ON e.patid = pat_id AND e.evalid = 'Tab0002' AND e.status = 1 AND e.reportnumber = g.monthGROUP BY g.monthUNION ALL-- 患者自我病耻感量表 入组后 GWSELECT COALESCE(COUNT(e.*), 0) num, g.month injectionNumber, '29' typed, user_id userid, 1 status, pat_id patidFROM generate_series(1, month_floor) AS g(month)LEFT JOIN test_pat_gw_eval e ON e.patid = pat_id AND e.evalid = 'Tab0003' AND e.status = 1 AND e.reportnumber = g.monthGROUP BY g.monthUNION ALL-- Zarit照顾负担量表 入组后 GWSELECT  COALESCE(COUNT(e.*), 0) num, g.month injectionNumber, '32' typed, user_id userid, 1 status, pat_id patidFROM generate_series(1, month_floor) AS g(month)LEFT JOIN test_pat_gw_eval e ON e.patid = pat_id AND e.evalid = 'Tab0004' AND e.status = 1 AND e.reportnumber = g.monthGROUP BY g.monthUNION ALL-- 患者家庭经济、医疗费用评价 入组后 GWSELECT  COALESCE(COUNT(e.*), 0) num, g.month injectionNumber, '36' typed, user_id userid, 1 status, pat_id patidFROM generate_series(1, month_floor) AS g(month)LEFT JOIN test_pat_economics_medical e ON e.patid = pat_id AND e.status = 1 AND e.reportnumber = g.monthGROUP BY g.monthUNION ALL-- 严重精神疾病患者危险性评估SELECT COALESCE(COUNT(e.*), 0) num, g.month injectionNumber, '34' typed, user_id, 1 status, pat_id patidFROM generate_series(1, month_floor) AS g(month)LEFT JOIN test_pat_risk e ON e.patid = pat_id AND e.status = 1 AND e.reportmonth = g.monthGROUP BY g.month) subquery;

关键点说明：
       1.generate_series(1, month_floor)：生成 1 到 N 月份；

       2.使用 LEFT JOIN 替代 JOIN，确保即使没有数据也能保留生成的月份行；

       3.使用 COALESCE(COUNT(e.), 0)：若无匹配数据，COUNT() 仍为 0，符合你“无数据月也插入”的要求；

       4.每个 SELECT 保持 typed 区分不同类型；

       5.插入时统一 NOW() 为时间，1 为状态；