前言
最近项目中遇到个获取消息的接口查询特别慢，花费近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 次查询和插入，多次的查询插入，不仅耗时巨大，还会占据更多的内存；
-- 第一段SQL
INSERT INTO gw_doctor_messages (num, injectionNumber,typed,userid,createtime,status,patid)
select count(1),-1 injectionNumber,'18' typed,user_id,NOW(),1,pat_id
from test_pat_file tpf
where filetype = 1 and patid = pat_id and status = 1
union all
select count(1),-1 injectionNumber,'19' typed,user_id,NOW(),1,pat_id
from 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_id
from test_pat_gw_eval tple
where patid = pat_id and evalid = 'Tab0002' and status =1 and reportnumber = -1
union all
select count(1),-1 injectionNumber,'28' typed,user_id,NOW(),1,pat_id
from test_pat_gw_eval tple
where patid = pat_id and evalid = 'Tab0003' and status =1 and reportnumber = -1
union all
select count(1),-1 injectionNumber,'31' typed,user_id,NOW(),1,pat_id
from test_pat_gw_eval tple
where patid = pat_id and evalid = 'Tab0004' and status =1 and reportnumber = -1
union all
select count(1),-1 injectionNumber,'35' typed,user_id,NOW(),1,pat_id
from test_pat_economics_medical tpem
where status = 1 and patid = pat_id and reportnumber = -1;
-- 第二段SQL
FOR i IN 1..month_floor LOOP
INSERT INTO gw_doctor_messages (num, injectionNumber,typed,userid,createtime,status,patid)
select count(1),i injectionNumber, '20' typed,user_id,NOW(),1,pat_id
from test_pat_gw_eval tple
where patid = pat_id and evalid = 'Tab0001' and status = 1 and reportnumber = i
union all
select count(1),i injectionNumber, '26' typed,user_id,NOW(),1,pat_id
from test_pat_gw_eval tple
where patid = pat_id and evalid = 'Tab0002' and status = 1 and reportnumber = i
union all
select count(1),i injectionNumber, '29' typed,user_id,NOW(),1,pat_id
from test_pat_gw_eval tple
where patid = pat_id and evalid = 'Tab0003' and status = 1 and reportnumber = i
union all
select count(1) ,i injectionNumber, '32' typed,user_id,NOW(),1,pat_id
from test_pat_gw_eval tple
where patid = pat_id and evalid = 'Tab0004' and status =1 and reportnumber = i
union all
select count(1) ,i injectionNumber, '36' typed,user_id,NOW(),1,pat_id
from test_pat_economics_medical tpem
where status = 1 and patid = pat_id and reportnumber = i
union all
select count(1) ,i injectionNumber, '34' typed,user_id,NOW(),1,pat_id
from 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_id
from (
-- 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 = -1
union all
-- 28 患者自我病耻感量表 入组前
select '28' typed from test_pat_gw_eval tple
where patid = pat_id and evalid = 'Tab0003' and status =1 and reportnumber = -1
union all
-- 31 Zarit照顾负担量表 入组前
select '31' typed from test_pat_gw_eval tple
where patid = pat_id and evalid = 'Tab0004' and status =1 and reportnumber = -1
union 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 patid
FROM 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.month
GROUP BY g.month
UNION ALL
-- 生活质量量表 入组后 GW
SELECT COALESCE(COUNT(e.*), 0) num, g.month injectionNumber, '26' typed, user_id userid, 1 status, pat_id patid
FROM 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.month
GROUP BY g.month
UNION ALL
-- 患者自我病耻感量表 入组后 GW
SELECT COALESCE(COUNT(e.*), 0) num, g.month injectionNumber, '29' typed, user_id userid, 1 status, pat_id patid
FROM 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.month
GROUP BY g.month
UNION ALL
-- Zarit照顾负担量表 入组后 GW
SELECT COALESCE(COUNT(e.*), 0) num, g.month injectionNumber, '32' typed, user_id userid, 1 status, pat_id patid
FROM 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.month
GROUP BY g.month
UNION ALL
-- 患者家庭经济、医疗费用评价 入组后 GW
SELECT COALESCE(COUNT(e.*), 0) num, g.month injectionNumber, '36' typed, user_id userid, 1 status, pat_id patid
FROM 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.month
GROUP BY g.month
UNION ALL
-- 严重精神疾病患者危险性评估
SELECT COALESCE(COUNT(e.*), 0) num, g.month injectionNumber, '34' typed, user_id, 1 status, pat_id patid
FROM 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.month
GROUP 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 为状态；