慢SQL自动发现与闭环处理

每日一句正能量

每一次微小的前进，都在加固你面对世界时的骨架。
强大非一日建成，而是在日复一日的微小践行中，如同骨骼吸收钙质般，悄然变得坚实。

1. 背景与问题

生产数据库每天都会产生新的慢SQL，仅依赖人工巡检容易遗漏热点语句，导致性能问题长期存在。为提升处理效率，需要建立“自动发现—自动分派—优化验证—回归关闭”的闭环机制，将慢SQL治理纳入标准化运维流程。

2. 环境与数据

环境：

• PostgreSQL 16
• Linux 9
• Prometheus + Grafana
• pg_stat_statements
• 工单平台

目标：

• RTO≤30分钟
• RPO≤5分钟

慢SQL采集：

SELECTquery,calls,total_exec_time,mean_exec_timeFROMpg_stat_statementsORDERBYtotal_exec_timeDESCLIMIT10;

3. 复现过程

故障注入：

1. 构造缺少索引的大表查询。
2. 持续执行高并发访问。
3. 自动采集慢SQL。
4. 触发告警并生成工单。

构造缺少索引的大表查询：

-- 1. 创建测试表 orders，模拟业务订单表CREATETABLEorders(id BIGSERIALPRIMARYKEY,user_idBIGINTNOTNULL,order_noVARCHAR(64)NOTNULL,amountNUMERIC(10,2)NOTNULL,statusSMALLINTNOTNULLDEFAULT0,create_timeTIMESTAMPNOTNULLDEFAULTnow());-- 2. 批量插入 10 万行测试数据，模拟生产环境数据量INSERTINTOorders(user_id,order_no,amount,status,create_time)SELECT(random()*100000)::BIGINT,-- 随机 user_id，模拟多用户'NO'||lpad(id::TEXT,12,'0'),-- 生成唯一订单号(random()*10000)::NUMERIC(10,2),-- 随机金额(random()*3)::SMALLINT,-- 随机状态now()-(random()*interval'365 days')-- 随机创建时间，覆盖近一年FROMgenerate_series(1,100000)ASid;-- 3. 不创建任何索引，直接执行按 user_id + create_time 过滤的查询-- 此时优化器只能选择 Seq Scan 全表扫描，触发慢SQLSELECT*FROMordersWHEREuser_id=10086ANDcreate_time>'2026-08-01';

示例执行计划（优化前）：

Seq Scan on orders Execution Time: 5.62 s

4. 方案实施

部署流程：

1. 定时采集 pg_stat_statements。
2. 根据阈值自动创建工单。
3. DBA分析执行计划并优化SQL或索引。
4. 回归测试验证。
5. 自动关闭工单并归档。

慢SQL治理闭环流程：

慢SQL治理思维导图：

优化示例：

CREATEINDEXidx_orders_user_timeONorders(user_id,create_timeDESC);ANALYZEorders;

优化后：

Index Scan using idx_orders_user_time Execution Time: 0.74 s

执行计划对比

优化前（Seq Scan 全表扫描）：

Seq Scan on orders (cost=0.00..4821.00 rows=100000 width=24) Filter: (user_id = 10086 AND create_time > '2026-08-01') Planning Time: 0.42 ms Execution Time: 5.62 s

优化后（Index Scan 索引扫描）：

Index Scan using idx_orders_user_time on orders (cost=0.42..8.45 rows=1 width=24) Index Cond: (user_id = 10086 AND create_time > '2026-08-01') Planning Time: 0.35 ms Execution Time: 0.74 s

关键差异说明：

• 启动成本（startup cost）：Seq Scan 为0.00，Index Scan 为0.42。索引扫描需要先定位到索引根节点，因此启动成本略高，但整体影响极小。
• 总成本（total cost）：Seq Scan 高达4821.00，Index Scan 仅为8.45，相差约 570 倍。全表扫描需读取全部 10 万行数据，而索引扫描只需访问少量索引页与数据页。
• 预估行数（rows）：Seq Scan 预估100000行，Index Scan 预估1行。优化器通过索引条件大幅缩小了扫描范围，这也是成本骤降的根本原因。
• 数据宽度（width）：两者均为24字节，说明返回的列集合一致，对比公平。
• 执行耗时：从5.62s降至0.74s，与成本下降趋势吻合，验证了索引对查询性能的显著提升。

检查清单：

• 执行计划改善
• 平均耗时下降
• 工单关闭
• RTO/RPO记录
• 业务验证通过

5. 结果对比

6. 风险与复盘

风险：

• 阈值过低可能产生大量无效工单。
• 仅优化SQL而不验证执行计划可能导致效果不稳定。
• 未建立回归测试可能引入新的性能问题。

复盘建议：

1. 建立慢SQL分级与自动工单机制。
2. 每次优化保留执行计划、监控指标及SQL版本。
3. 定期开展故障注入和恢复演练，验证RTO/RPO。
4. 建立检查清单，覆盖发现、分析、优化、验证、回归全过程。

7. 常见问题与排查

问题一：索引未生效

现象：优化后 SQL 仍走全表扫描，耗时未明显下降。

排查步骤：

1. 使用EXPLAIN ANALYZE查看实际执行计划，确认是否命中新建索引。
2. 检查查询条件与索引列顺序、类型是否匹配，避免隐式类型转换导致索引失效。
3. 确认表统计信息是否过期，必要时重新执行ANALYZE。

解决建议：

• 按查询条件调整索引列顺序，或使用覆盖索引减少回表。
• 定期维护统计信息，保证优化器能正确选择索引。

问题二：采集遗漏

现象：部分慢SQL未被采集，未触发告警与工单。

排查步骤：

1. 检查pg_stat_statements是否开启，以及采样周期是否合理。
2. 核对采集 SQL 的过滤条件，确认是否因LIMIT或阈值设置漏掉低频但耗时的语句。
3. 查看采集任务日志，确认是否存在连接中断或权限不足。

解决建议：

• 提高采样频率，并适当放宽排序范围，避免只取 Top N。
• 为采集任务增加失败重试与告警，确保数据不丢失。

问题三：工单误报

现象：正常业务语句被判定为慢SQL，产生大量无效工单。

排查步骤：

1. 核对慢SQL阈值是否过低，或未排除维护窗口、批量任务等特殊时段。
2. 结合调用来源与执行频率，判断是否为偶发或可接受的耗时。
3. 检查是否缺少对同类型语句的聚合与去重。

解决建议：

• 按业务时段分级设置阈值，并对批量任务单独豁免。
• 增加白名单与聚合规则，减少重复工单，提升治理效率。

转载自：https://blog.csdn.net/u014727709/article/details/164256923
欢迎 👍点赞✍评论⭐收藏，欢迎指正