SQL性能优化之压测404的根因追查与解决方案_MsSqlserver

今天聊一聊sql优化的一则案例分析。

适用数据库：达梦（dm8）/ mysql

场景：任务列表查询接口 work_hour_task 压力测试

一、背景

测试人员对任务列表查询接口进行并发压测时，出现大量 404 响应错误。经初步排查，这些 404 并非业务逻辑主动返回，而是接口响应超时后由网关/nginx 抛出的超时错误。

触发链路如下：

sql 慢查询（全表扫描 + n+1 子查询 + filesort）
→ 接口响应时间飙升
→ 压测并发请求大量堆积
→ 网关请求排队超时
→ 返回 404

二、问题 sql

原始 sql（mysql 版本）

explain select
    t.id, t.tenant_id, t.task_name, t.is_project_task, t.team_code, t.project_code,
    t.task_type, t.task_sub_type, t.responsible_person_code, t.work_hours, t.estimate_work_hours,
    t.task_desc, t.process, t.plan_start_date, t.plan_end_date, t.task_source,
    t.actual_start_time, t.actual_end_time, t.related_team_code, t.rel_id,
    t.status, t.ext_info, t.version, t.deleted,
    t.created_by, t.created_time, t.updated_by, t.updated_time
from work_hour_task t
where t.deleted = 0
  and (
    t.team_code in ('webdev')
    or exists (
      select 1 from work_hour_task_related_team tr
      where tr.task_rel_id = t.rel_id
        and tr.deleted = 0
        and tr.team_code in ('webdev')
        and tr.tenant_id = 'hkbank'
    )
  )
  and t.tenant_id = 'hkbank'
order by t.created_time desc
limit 15

达梦版本结构基本一致，where 条件中额外使用了 find_in_set 函数代替 exists 子查询。

三、执行计划解读

mysql 版本执行计划

id	select_type	type	key	rows	extra
1	primary	all	null	1233	using where; using filesort
2	dependent subquery	eq_ref	uk_task_team_del	1	using index condition; using where

达梦版本执行计划

节点	类型	描述
nset2 → prjt2 → sort3	结果集 → 投影 → 排序	存在额外排序开销
union for or2	or 条件拆成两路扫描	两路各自回表，代价翻倍
blkup2 × 2	bookmark lookup	两路均存在回表
ssek2 × 2	二级索引 seek	其中一路因 find_in_set 索引失效

四、三大核心问题

问题一：全表扫描（type = all）

主表 t 有 3 个候选索引（idx_task_team、idx_task_page_core、idx_task_count_core），但优化器最终 key = null，全部放弃，被迫全表扫描 1233 行。

根本原因： where 条件中对列使用了函数（find_in_set）或存在关联子查询（exists），优化器无法利用索引进行范围扫描。

问题二：dependent subquery（n+1 问题）

exists 子查询的 select_type 为 dependent subquery，意味着它依赖外层主表的每一行逐行触发执行。

主表扫描 1233 行 × 子查询执行 1233 次 = i/o 实际放大 1233 倍

子查询虽然单次走了唯一索引（eq_ref，rows=1），但积累后总代价极高，这是典型的 n+1 问题。

问题三：using filesort（额外排序）

order by t.created_time desc 无法利用现有索引完成排序，数据库须在内存或磁盘中对全量结果集做额外排序。高并发压测时，排序操作大量占用 cpu 和内存，进一步拖慢响应时间。

五、优化方案

改写思路：两段式查询

将原来「一条复杂 sql 承包所有逻辑」的写法，拆分为两步：

第一步（id 收集层）：轻量查询先收集符合条件的 task id 列表，用 union all 分别处理两种匹配条件，各自加 order by + limit，合并后取 top n。
第二步（明细查询层）：用主键 in (id1, id2, ...) 查询完整字段，走主键索引，无子查询，无 filesort。

这种「分步查询」模式是处理 or + 子查询 + 排序分页 组合场景的标准实践，彻底解耦了「找哪些记录」和「取这些记录的字段」两个问题。

优化后 sql（mysql 版本）

-- 第二步：用主键 in 查询明细，消除子查询和 filesort
select
    id, tenant_id, task_name, is_project_task, team_code, project_code,
    task_type, task_sub_type, responsible_person_code, work_hours, estimate_work_hours,
    task_desc, process, plan_start_date, plan_end_date, task_source,
    actual_start_time, actual_end_time, related_team_code, rel_id,
    status, ext_info, version, deleted,
    created_by, created_time, updated_by, updated_time
from work_hour_task
where id in (1942, 1941, 1940, 1939, 1938, 1937, 1936, 1935,
             1934, 1933, 1932, 1931, 1930, 1929, 1928)
  and deleted = 0
  and tenant_id = 'hkbank'

优化后执行计划

id	select_type	type	key	rows	extra
1	simple	range	primary	15	using where

达梦版本额外改写

达梦原 sql 中 find_in_set 将函数施加于列上导致索引失效，需在应用层将参数预先拆分：

-- 改前（索引失效）
or find_in_set(?, related_team_code) > 0
-- 改后（应用层拆分参数后传入，索引可正常使用）
or t.related_team_code in (?, ?, ?)

六、优化前后对比

指标	优化前	优化后
select_type	primary + dependent subquery	simple
type（扫描类型）	all（全表扫描）❌	range（索引范围扫描）✅
key（命中索引）	null（全部放弃）❌	primary（主键）✅
rows（扫描行数）	1233 行 × 子查询 1233 次 ❌	15 行 ✅
extra	using filesort ❌	using where ✅
行数降幅	—	下降 98.8%

优化前执行路径：全表扫描 1233 行 → 逐行触发子查询（× 1233 次）→ filesort 排序 → 取 top 15

优化后执行路径：主键 range 扫描 15 行 → 直接返回（无子查询，无排序）

七、经验总结与规范建议

本次问题根因清单

#	根因	影响	解决方式
1	find_in_set / exists 对列施函数	索引全部失效，退化为全表扫描	改为 `in (?)` 或应用层预处理
2	dependent subquery（n+1）	子查询随主表每行触发，i/o 放大 n 倍	分步查询或改写为 join
3	using filesort	全量结果集额外排序，高并发时 cpu 飙升	建包含 order by 字段的覆盖索引
4	缺少覆盖索引	索引命中后仍大量回表取字段	建联合覆盖索引，字段顺序：过滤列 + 排序列

sql 开发规范建议

禁止在 where 条件的列上直接使用函数（find_in_set、date()、year() 等），改为在参数侧做处理，保持列的"裸露"。

慎用 exists / in 关联子查询，考虑改写为 join 或分步查询，避免产生 dependent subquery。

分页列表接口推荐「两段式查询」：先查 id 列表（轻查询，走索引），再用主键 in 查完整字段，两步走比一步复杂查询更可控。

新建索引需覆盖 where 过滤字段 + order by 字段，减少回表和 filesort，字段顺序按选择性从高到低排列。

上线前必须通过 explain 验证执行计划，重点关注：

type 不得为 all
extra 不得出现 using filesort / using temporary

压测出现大量非业务 404 时，优先排查接口响应时间和数据库慢查询日志，而非只看应用层错误日志。

执行计划关键字速查

字段	危险值（需优化）	目标值
type	`all`（全表）	`range` / `ref` / `eq_ref` / `const`
key	`null`（未用索引）	命中具体索引名
rows	远大于实际返回行数	接近实际返回行数
extra	`using filesort` / `using temporary`	`using index`（覆盖索引最佳）
select_type	`dependent subquery`	`simple` / `primary`

总结

这次优化的核心收获是：慢不一定在业务代码里，404 也不一定是路由问题。当压测出现大量超时类 404 时，第一步应该打开慢查询日志，把 explain 拿出来看。

记住三个关键词：全表扫描、n+1、filesort。这三者任意一个在高并发下都足以拖垮接口，三个叠加则必然超时。

优化的本质不是"加索引"这么简单，而是要理解优化器的决策逻辑——让 where 条件能走索引，让子查询不随主表行数膨胀，让 order by 不产生额外排序，三点都满足，性能自然就上去了。

到此这篇关于sql性能优化之压测404的根因追查与解决方案的文章就介绍到这了,更多相关sql压测404错误排查与解决内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

SQL性能优化之压测404的根因追查与解决方案

2026年03月25日 • MsSqlserver •我要评论