续约首页爆炸式多Count接口优化｜1次请求12条跨表count(1)串行查询、接口卡顿DB压垮，五步极致落地优化

一、业务背景

环境：续约管理系统坐席工作台首页，需要统计当前登录坐席多项业务看板指标，包含：

业务现状：前端首页一次性聚合全部看板数据，后端串行执行12条独立 select count(1) SQL，跨4张业务数据表、无索引、无缓存、串行执行，多坐席并发访问直接打垮数据库连接池，接口响应900ms+，高峰期接口超时、服务卡顿。

private HomeDashboardSummary queryHomeDashboardSummary(String workCode,
Map<String, HomeDashboardSummary> summaryCache) {
if (workCode == null || workCode.trim().isEmpty()) {
return new HomeDashboardSummary();
}
if (summaryCache.containsKey(workCode)) {
return summaryCache.get(workCode);
}

HomeDashboardSummary summary = new HomeDashboardSummary();
try {
ServiceTaskTimeoutSummaryResponse timeoutSummary =
serviceTaskService.getTimeoutSummary(workCode);

ServiceTaskTodaySummaryResponse serviceSummary =
serviceTaskService.getTodaySummary(workCode);

WorkOrderTodaySummaryResponse workOrderSummary =
workOrderService.getTodaySummary(workCode);

CompanyInteractionTaskTodaySummaryResponse interactionSummary =
interactionService.queryTaskTodaySummary(workCode);

// 组装首页统计结果
} catch (Exception e) {
log.warn("查询首页成员统计失败，workCode={}", workCode, e);
}

summaryCache.put(workCode, summary);
return summary;
}

二、原始日志问题复盘（源码）

单次接口请求串行执行：t_system_config、t_service_task、t_service_follow_record、t_renewal_work_order、t_company_interaction_task 多张表独立Count查询；

每条SQL单独建立数据库连接、语法解析、磁盘IO、事务提交；同一个用户userId反复传参、重复过滤今日时间、删除标记；

部分SQL嵌套exists关联子查询，索引失效、全表扫描叠加；多坐席轮询请求，DB QPS暴涨。

2.1 原生代码致命问题汇总

• 问题1：同一张表6条独立count(1)，重复扫描数据表6次，浪费IO

• 问题2：多张不同业务表串行查询，总耗时 = 所有SQL耗时累加

• 问题3：无联合索引，count统计回表查询，性能极差

• 问题4：系统配置SQL每次请求都查询，无本地缓存

• 问题5：首页看板允许秒级延迟，完全无Redis缓存，高频重复查库

• 问题6：exists 嵌套子查询，行级匹配，放大查询开销

2.2 原生架构耗时模型

优化前总耗时 = SQL1+SQL2+SQL3…+SQL12 串行累加 ≈ 900ms

数据库连接消耗：单次占用12个DB连接，并发直接耗尽连接池

三、整体优化思路（由浅入深、低成本优先）

3.1. 角色权限接口和首页数据接口拆分，降低接口职责耦合

原代码：

@ApiOperation("获取首页角色权限")
@GetMapping("/home/role")
@AuthenticationCheck("/index")
public Result<HomeRoleResponse> homeRole(HttpServletRequest request) {
try {
Employee employee = EmployeeApi.getCurrentEmployee(request);
boolean supervisor = Permissions.checkSupervisorPermission(request);
boolean superSupervisor = Permissions.checkSuperSupervisorPermission(request);
HomeRoleResponse response = new HomeRoleResponse();
response.setSupervisor(supervisor);
response.setSuperSupervisor(superSupervisor);
response.setSupervisorGroups(queryHomeSupervisorGroups(employee, supervisor, superSupervisor));
response.setTeamMembers(queryHomeTeamMembers(response.getSupervisorGroups(), superSupervisor));
return Result.succ(response);
} catch (Exception e) {
log.warn("获取首页角色权限失败", e);
}
return Result.fail("获取首页角色权限失败");
}

代码将业务混在一起，一个接口干了两件事，这就是现在AI生成代码的问题，对业务不清晰

修改后的代码：将业务代码跟识别身份分了开来

@GetMapping("/home/role")
@AuthenticationCheck("/index")
public Result<HomeRoleResponse> homeRole(HttpServletRequest request) {
try {
boolean supervisor = Permissions.checkSupervisorPermission(request);
boolean superSupervisor = Permissions.checkSuperSupervisorPermission(request);
HomeRoleResponse response = new HomeRoleResponse();
if (superSupervisor) {
response.setRole("superSupervisor");
} else if (supervisor) {
response.setRole("supervisor");
} else {
response.setRole("staff");
}
return Result.succ(response);
} catch (Exception e) {
log.warn("获取首页角色权限失败", e);
}
return Result.fail("获取首页角色权限失败");
}

@ApiOperation("获取首页数据")
@GetMapping
@AuthenticationCheck("/index")
public Result<HomePageResponse> homePage(HttpServletRequest request) {
long start = System.currentTimeMillis();
try {
Employee employee = EmployeeApi.getCurrentEmployee(request);
boolean supervisor = Permissions.checkSupervisorPermission(request);
boolean superSupervisor = Permissions.checkSuperSupervisorPermission(request);
HomePageResponse response = new HomePageResponse();
response.setSupervisorGroups(homeRoleService.queryHomeSupervisorGroups(employee, supervisor, superSupervisor));
response.setTeamMembers(homeRoleService.queryHomeTeamMembers(response.getSupervisorGroups(), superSupervisor));
log.info("home.page.finished workCode={}, supervisor={}, superSupervisor={}, supervisorGroups={}, teamMembers={}, costMs={}",
employee == null ? null : employee.getWorkCode(), supervisor, superSupervisor,
response.getSupervisorGroups() == null ? 0 : response.getSupervisorGroups().size(),
response.getTeamMembers() == null ? 0 : response.getTeamMembers().size(),
System.currentTimeMillis() – start);
return Result.succ(response);
} catch (Exception e) {
log.warn("获取首页数据失败", e);
}
return Result.fail("获取首页数据失败");
}

3.2. 首页团队成员统计从按人循环查询，改为批量查询

原代码：

private List<HomeMember> buildHomeMembers(List<HomeMember> members) {
List<HomeMember> result = new ArrayList<>();

for (HomeMember member : members) {
HomeMember homeMember = copyHomeMember(member);

// 每个成员单独查一轮统计
homeMember.setSummary(queryHomeDashboardSummary(homeMember.getWorkCode()));

result.add(homeMember);
}

return result;
}

每个人都会执行：

private HomeDashboardSummary queryHomeDashboardSummary(String workCode) {
HomeDashboardSummary summary = new HomeDashboardSummary();

ServiceTaskTimeoutSummaryResponse timeoutSummary =
serviceTaskService.getTimeoutSummary(workCode);

ServiceTaskTodaySummaryResponse serviceSummary =
serviceTaskService.getTodaySummary(workCode);

WorkOrderTodaySummaryResponse workOrderSummary =
workOrderService.getTodaySummary(workCode);

CompanyInteractionTaskTodaySummaryResponse interactionSummary =
interactionService.queryTaskTodaySummary(workCode);

// 组装 summary
return summary;
}

如果团队有 20 个人，就会变成：

20 次服务超时统计

20 次服务任务统计

20 次工单统计

20 次数据交互统计

也就是典型 N+1 查询。

新代码
现在是先收集所有成员工号，再批量查：

private Map<String, HomeDashboardSummary> queryHomeDashboardSummaryMap(
List<SupervisorMemberMapping> mappings) {

List<String> workCodes = collectHomeWorkCodes(mappings);
Map<String, HomeDashboardSummary> summaryCache = new HashMap<>();

for (String workCode : workCodes) {
summaryCache.put(workCode, new HomeDashboardSummary());
}

fillServiceTaskSummaries(workCodes, summaryCache);
fillWorkOrderSummaries(workCodes, summaryCache);
fillInteractionSummaries(workCodes, summaryCache);

return summaryCache;
}
private List<HomeMember> buildHomeMembers(
List<HomeMember> members,
Map<String, HomeDashboardSummary> summaryCache) {

List<HomeMember> result = new ArrayList<>();

for (HomeMember member : members) {
HomeMember homeMember = copyHomeMember(member);

// 不再查库，只从缓存 Map 里取
homeMember.setSummary(
getCachedHomeDashboardSummary(homeMember.getWorkCode(), summaryCache)
);

result.add(homeMember);
}

return result;
}

3.3. 服务任务、工单、数据交互统计统一按人员列表批量聚合

三类统计都改成同一种模式：传入人员列表，一次 SQL 按人员分组聚合返回：查张三服务任务统计

查张三工单统计

查张三数据交互统计

查李四服务任务统计

查李四工单统计

查李四数据交互统计

查王五服务任务统计

查王五工单统计

查王五数据交互统计

服务任务统计
按 follower 批量聚合：

<select id="selectHomeSummaryByFollowers">
select s.follower,
IFNULL(sum(s.assignTimeoutCount), 0) as assignTimeoutCount,
IFNULL(sum(s.appointmentTimeoutCount), 0) as appointmentTimeoutCount,
IFNULL(sum(s.assignCompletedCount), 0) as assignCompletedCount,
IFNULL(sum(s.assignUncompletedCount), 0) as assignUncompletedCount,
IFNULL(sum(s.appointmentCompletedCount), 0) as appointmentCompletedCount,
IFNULL(sum(s.appointmentUncompletedCount), 0) as appointmentUncompletedCount
from (
…
) s
group by s.follower
</select>

批量条件：

and t.follower in
<foreach collection="followers" item="follower" open="(" separator="," close=")">
#{follower}
</foreach>

工单统计
按 current_node_assignee_no 批量聚合

<select id="selectTodaySummaryByHandlers">
select s.handlerNo,
IFNULL(sum(s.completedCount), 0) as completedCount,
IFNULL(sum(s.uncompletedCount), 0) as uncompletedCount
from (
…
) s
group by s.handlerNo
</select>

批量条件：

where current_node_assignee_no in
<foreach collection="handlerNos" item="handlerNo" open="(" separator="," close=")">
#{handlerNo,jdbcType=VARCHAR}
</foreach>

数据交互统计
按 handle_user_no 批量聚合：

<select id="selectTodaySummaryByHandleUsers">
select s.handleUserNo,
IFNULL(sum(s.completedCount), 0) as completedCount,
IFNULL(sum(s.uncompletedCount), 0) as uncompletedCount
from (
…
) s
group by s.handleUserNo
</select>

批量条件：

where t.handle_user_no in
<foreach collection="handleUserNos" item="handleUserNo" open="(" separator="," close=")">
#{handleUserNo, jdbcType=VARCHAR}
</foreach>

Java 层统一回填
三类统计都查完后，按工号放回首页成员的 summary：

Map<String, HomeDashboardSummary> summaryCache = new HashMap<>();

fillServiceTaskSummaries(workCodes, summaryCache);
fillWorkOrderSummaries(workCodes, summaryCache);
fillInteractionSummaries(workCodes, summaryCache);

3.4. 批量 SQL 从 CASE WHEN + OR 优化为 UNION ALL 分段聚合

原来的批量统计虽然已经避免了按人循环查，但 SQL 里还是把“已完成、未完成、超时”等统计放在一个查询里，通过：

SUM(CASE WHEN 条件 THEN 1 ELSE 0 END)

再配合多个 OR 条件一起判断。

这种写法的问题是：不同统计字段依赖的筛选条件不一样，比如创建时间、预约时间、完成时间、处理状态都不同，数据库很难稳定命中合适索引，容易退化成扫描较多数据后再逐行计算 CASE WHEN。

优化后改成：

SELECT user_no,
SUM(completed) completed,
SUM(uncompleted) uncompleted,
SUM(timeout) timeout
FROM (
SELECT follower user_no, COUNT(1) completed, 0 uncompleted, 0 timeout
FROM t_service_task
WHERE deleted = 0
AND follower IN (…)
AND create_time >= CURDATE()
AND create_time < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
AND follow_status IN (1, 2)
GROUP BY follower

UNION ALL

SELECT follower user_no, 0 completed, COUNT(1) uncompleted, 0 timeout
FROM t_service_task
WHERE deleted = 0
AND follower IN (…)
AND create_time >= CURDATE()
AND create_time < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
AND follow_status = 1
GROUP BY follower
) t
GROUP BY user_no;

优化点是：

• 每个统计口径单独一段 SQL，只保留当前口径需要的条件。
• 避免多个复杂 OR 混在一起影响索引选择。
• 每段 SQL 可以按自己的条件命中对应索引，例如：
  • 服务任务按 follower + create_time + follow_status
  • 工单按 current_node_assignee_no + status + wo_end_time
  • 数据交互按 handle_user_no + handle_status + handle_end_time

最外层再按人员编号统一 GROUP BY 汇总结果。

3.5. 增加接口耗时日志，方便定位慢点

• long serviceTaskStart = System.currentTimeMillis();
  fillServiceTaskSummaries(workCodes, summaryCache);
  long serviceTaskMs = System.currentTimeMillis() – serviceTaskStart;

  long workOrderStart = System.currentTimeMillis();
  fillWorkOrderSummaries(workCodes, summaryCache);
  long workOrderMs = System.currentTimeMillis() – workOrderStart;

  long interactionStart = System.currentTimeMillis();
  fillInteractionSummaries(workCodes, summaryCache);
  long interactionMs = System.currentTimeMillis() – interactionStart;

  log.info("home.page.summary.batch workCodeCount={}, serviceTaskMs={}, workOrderMs={}, interactionMs={}, totalMs={}",
  workCodes.size(), serviceTaskMs, workOrderMs, interactionMs, totalMs);

  线上再出现慢查询时，可以直接从日志判断：

• serviceTaskMs 高：重点看 t_service_task、t_service_follow_record
• workOrderMs 高：重点看 t_renewal_work_order
• interactionMs 高：重点看 t_company_interaction_task 

3.6.Redis缓存+本地缓存双层优化

• 新增首页指标缓存服务：
  • 缓存粒度按人员工号：renew:dashboard:stat:{workCode}。
  • 本地缓存 TTL 30s，Redis TTL 60s。
  • 查询顺序：本地缓存命中直接返回；本地未命中查 Redis；Redis 未命中后按人员列表批量查库并回填本地+Redis。
  • 缓存内容只存 HomeDashboardSummary，不缓存整页响应，避免主管关系变化导致整页缓存失效复杂。
• 改造 HomeRoleService：
  • queryHomeDashboardSummaryMap 先走缓存服务。
  • 只对缓存未命中的人员执行现有批量 SQL。
  • 保留主管汇总、超级主管分组、普通主管过滤逻辑不变。

由于以下原因：暂不进行缓存优化：

一、业务层面：数据强实时性要求，缓存会产生脏数据

二、数据特征：无缓存收益，缓存反而增加开销

• 若缓存：每个主管生成一条独立 Redis Key，团队扩张后 Key 数量海量，内存占用极高；
• 无复用：A 主管的缓存数据，其他主管完全无法共用，缓存命中率无限趋近 0。

三、技术一致性与复杂度问题

• 方案 1（过期缓存）：数据滞后；
• 方案 2（更新 DB 同步删缓存）：高并发下会出现「DB 更新成功、缓存删除失败」，永久脏数据；
• 方案 3（双写 DB+Redis）：分布式事务复杂，增加开发 bug 风险。

四、运维与成本层面

四、优化前后生产数据对比

优化阶段

SQL数量

接口响应耗时

DB连接占用

五、开发踩坑避坑总结

六、总结

本次线上续约首页接口属于后端开发经典案例：直接业务指标拆分、逐个写count查询，代码极简、性能灾难。

• 接口拆分
  /user/home/role 只返回角色；/home/page 只返回首页业务数据，避免角色接口承载统计逻辑。

• 查询优化
  首页团队统计从“按人循环查”改成“人员列表批量聚合”，服务任务、工单、数据交互分别按人员列表一次性查询。

• SQL 优化
  批量统计 SQL 从复杂 CASE WHEN + OR 改成 UNION ALL 分段聚合，让每段 SQL 更容易命中索引。

总结后续开发规范：首页统计类接口禁止超过2条count SQL、禁止串行统计查询。