SQL 性能优化实战：一次压测 404 的根因追查与解决

G探险者
8 阅读6分钟

大家好，我是G探险者！

今天聊一聊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 版本执行计划

idselect_typetypekeyrowsExtra
1PRIMARYALLNULL1233Using where; Using filesort
2DEPENDENT SUBQUERYeq_refuk_task_team_del1Using index condition; Using where

达梦版本执行计划

节点类型描述
NSET2 → PRJT2 → SORT3结果集 → 投影 → 排序存在额外排序开销
UNION FOR OR2OR 条件拆成两路扫描两路各自回表，代价翻倍
BLKUP2 × 2BOOKMARK 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'

优化后执行计划

idselect_typetypekeyrowsExtra
1SIMPLErangePRIMARY15Using where

达梦版本额外改写

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

-- 改前（索引失效）
OR FIND_IN_SET(?, RELATED_TEAM_CODE) > 0

-- 改后（应用层拆分参数后传入，索引可正常使用）
OR t.related_team_code IN (?, ?, ?)

推荐覆盖索引

-- MySQL
ALTER TABLE work_hour_task
ADD INDEX idx_covering (TENANT_ID, deleted, team_code, created_time DESC);

-- 达梦
CREATE INDEX idx_covering
ON work_hour_task (TENANT_ID, deleted, team_code, created_time DESC);

六、优化前后对比

指标优化前优化后
select_typePRIMARY + DEPENDENT SUBQUERYSIMPLE
type（扫描类型）ALL（全表扫描）❌range（索引范围扫描）✅
key（命中索引）NULL（全部放弃）❌PRIMARY（主键）✅
rows（扫描行数）1233 行 × 子查询 1233 次 ❌15 行 ✅
ExtraUsing filesort ❌Using where ✅
行数降幅—下降 98.8%

优化前执行路径：

全表扫描 1233 行 → 逐行触发子查询（× 1233 次）→ filesort 排序 → 取 Top 15

优化后执行路径：

主键 range 扫描 15 行 → 直接返回（无子查询，无排序）

七、经验总结与规范建议

本次问题根因清单

#根因影响解决方式
1FIND_IN_SET / EXISTS 对列施函数索引全部失效，退化为全表扫描改为 IN (?) 或应用层预处理
2DEPENDENT SUBQUERY（N+1）子查询随主表每行触发，I/O 放大 N 倍分步查询或改写为 JOIN
3Using filesort全量结果集额外排序，高并发时 CPU 飙升建包含 ORDER BY 字段的覆盖索引
4缺少覆盖索引索引命中后仍大量回表取字段建联合覆盖索引，字段顺序：过滤列 + 排序列

SQL 开发规范建议

  1. 禁止在 WHERE 条件的列上直接使用函数（FIND_IN_SET、DATE()、YEAR() 等），改为在参数侧做处理，保持列的"裸露"。

  2. 慎用 EXISTS / IN 关联子查询，考虑改写为 JOIN 或分步查询，避免产生 DEPENDENT SUBQUERY。

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

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

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

    • type 不得为 ALL
    • Extra 不得出现 Using filesort / Using temporary

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

执行计划关键字速查

字段危险值（需优化）目标值
typeALL（全表）range / ref / eq_ref / const
keyNULL（未用索引）命中具体索引名
rows远大于实际返回行数接近实际返回行数
ExtraUsing filesort / Using temporaryUsing index（覆盖索引最佳）
select_typeDEPENDENT SUBQUERYSIMPLE / PRIMARY

总结

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

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

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

如有问题欢迎留言交流 🙌

avatar
文章
阅读
粉丝