慢SQL自动发现与闭环处理

文章目录每日一句正能量1. 背景与问题2. 环境与数据3. 复现过程4. 方案实施执行计划对比5. 结果对比6. 风险与复盘7. 常见问题与排查问题一索引未生效问题二采集遗漏问题三工单误报每日一句正能量每一次微小的前进都在加固你面对世界时的骨架。强大非一日建成而是在日复一日的微小践行中如同骨骼吸收钙质般悄然变得坚实。1. 背景与问题生产数据库每天都会产生新的慢SQL仅依赖人工巡检容易遗漏热点语句导致性能问题长期存在。为提升处理效率需要建立“自动发现—自动分派—优化验证—回归关闭”的闭环机制将慢SQL治理纳入标准化运维流程。2. 环境与数据环境PostgreSQL 16Linux 9Prometheus Grafanapg_stat_statements工单平台目标RTO≤30分钟RPO≤5分钟慢SQL采集SELECTquery,calls,total_exec_time,mean_exec_timeFROMpg_stat_statementsORDERBYtotal_exec_timeDESCLIMIT10;3. 复现过程故障注入构造缺少索引的大表查询。持续执行高并发访问。自动采集慢SQL。触发告警并生成工单。构造缺少索引的大表查询-- 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()*interval365 days)-- 随机创建时间覆盖近一年FROMgenerate_series(1,100000)ASid;-- 3. 不创建任何索引直接执行按 user_id create_time 过滤的查询-- 此时优化器只能选择 Seq Scan 全表扫描触发慢SQLSELECT*FROMordersWHEREuser_id10086ANDcreate_time2026-08-01;示例执行计划优化前Seq Scan on orders Execution Time: 5.62 s4. 方案实施部署流程定时采集 pg_stat_statements。根据阈值自动创建工单。DBA分析执行计划并优化SQL或索引。回归测试验证。自动关闭工单并归档。慢SQL治理闭环流程定时采集 pg_stat_statements自动创建工单DBA 优化 SQL 或索引回归验证自动关闭工单并归档慢SQL治理思维导图慢SQL治理自动发现定时采集 pg_stat_statements阈值告警生成工单优化分析DBA 分析执行计划优化 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 (cost0.00..4821.00 rows100000 width24) 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 (cost0.42..8.45 rows1 width24) Index Cond: (user_id 10086 AND create_time 2026-08-01) Planning Time: 0.35 ms Execution Time: 0.74 s关键差异说明启动成本startup costSeq Scan 为0.00Index Scan 为0.42。索引扫描需要先定位到索引根节点因此启动成本略高但整体影响极小。总成本total costSeq Scan 高达4821.00Index Scan 仅为8.45相差约 570 倍。全表扫描需读取全部 10 万行数据而索引扫描只需访问少量索引页与数据页。预估行数rowsSeq Scan 预估100000行Index Scan 预估1行。优化器通过索引条件大幅缩小了扫描范围这也是成本骤降的根本原因。数据宽度width两者均为24字节说明返回的列集合一致对比公平。执行耗时从5.62s降至0.74s与成本下降趋势吻合验证了索引对查询性能的显著提升。检查清单执行计划改善平均耗时下降工单关闭RTO/RPO记录业务验证通过5. 结果对比指标优化前优化后SQL耗时5.62s0.74sP99延迟168ms61ms慢SQL数量439工单处理周期3天6小时RTO29分钟23分钟RPO5分钟2分钟6. 风险与复盘风险阈值过低可能产生大量无效工单。仅优化SQL而不验证执行计划可能导致效果不稳定。未建立回归测试可能引入新的性能问题。复盘建议建立慢SQL分级与自动工单机制。每次优化保留执行计划、监控指标及SQL版本。定期开展故障注入和恢复演练验证RTO/RPO。建立检查清单覆盖发现、分析、优化、验证、回归全过程。7. 常见问题与排查问题一索引未生效现象优化后 SQL 仍走全表扫描耗时未明显下降。排查步骤使用EXPLAIN ANALYZE查看实际执行计划确认是否命中新建索引。检查查询条件与索引列顺序、类型是否匹配避免隐式类型转换导致索引失效。确认表统计信息是否过期必要时重新执行ANALYZE。解决建议按查询条件调整索引列顺序或使用覆盖索引减少回表。定期维护统计信息保证优化器能正确选择索引。问题二采集遗漏现象部分慢SQL未被采集未触发告警与工单。排查步骤检查pg_stat_statements是否开启以及采样周期是否合理。核对采集 SQL 的过滤条件确认是否因LIMIT或阈值设置漏掉低频但耗时的语句。查看采集任务日志确认是否存在连接中断或权限不足。解决建议提高采样频率并适当放宽排序范围避免只取 Top N。为采集任务增加失败重试与告警确保数据不丢失。问题三工单误报现象正常业务语句被判定为慢SQL产生大量无效工单。排查步骤核对慢SQL阈值是否过低或未排除维护窗口、批量任务等特殊时段。结合调用来源与执行频率判断是否为偶发或可接受的耗时。检查是否缺少对同类型语句的聚合与去重。解决建议按业务时段分级设置阈值并对批量任务单独豁免。增加白名单与聚合规则减少重复工单提升治理效率。转载自https://blog.csdn.net/u014727709/article/details/164256923欢迎 点赞✍评论⭐收藏欢迎指正