慢SQL总上线才救火？这套治理体系帮我们18个月0故障

慢SQL总上线才救火这套治理体系帮我们18个月0故障去年年末做技术复盘的时候我们翻了全年的故障记录17次数据库相关的P0/P1故障里有12次根源都是慢SQL有新人上线没加索引的全表扫描有大事务持锁导致的雪崩有联合索引顺序写错导致的索引失效还有深分页把从库查挂的。印象最深的是618前一周一个新上线的活动页接口没做SQL审核上线10分钟就把主库CPU打满到100%下单、支付、商品详情全卡整整18分钟才恢复当天晚上算下来直接损失了两百多万GMV整个技术组罚了三个月的绩效。那次之后我们痛定思痛花了三个月搭了一整套全流程慢查询治理体系从开发写代码、测试、上线到线上运行全环节卡口之后整整18个月我们没出过一次慢SQL导致的线上故障。很多团队优化慢SQL永远是“救火模式”库被打满了才捞慢日志找问题改完上线没几天又出新的慢SQL永远追着问题跑其实慢SQL根本不用靠救火只要把治理体系搭起来完全能把99%的慢SQL拦在上线之前。慢查询治理工程与全流程管控实战‌一、为什么救火式优化永远解决不了慢SQL问题我见过太多团队的慢SQL治理模式平时没人管等到数据库CPU跑满、业务超时了DBA赶紧捞慢查询日志找到问题SQL扔给对应的开发开发手忙脚乱改完上线业务恢复了这事就算过去了至于下次什么时候出问题全看运气。这种模式的问题在哪首先是永远被动慢SQL已经上线跑了几个小时甚至几天已经把业务搞挂了才去处理损失已经造成了再怎么优化也补不回来损失的GMV和用户体验。其次是改不完线上的业务一直在迭代每天都有新代码上线新的慢SQL源源不断冒出来DBA和开发永远在补窟窿根本没时间从根源上解决问题。我们当时统计过线上出现的慢SQL里70%都是特别低级的错误没加索引、隐式类型转换、SELECT *查了不需要的字段、对索引字段用函数导致失效、深分页、关联表没加索引这些问题不需要什么高深的优化技巧只要开发写完SQL跑个EXPLAIN5秒钟就能发现为什么还是流到线上了本质上是没有卡口全靠开发的自觉和经验人总有疏忽的时候再资深的开发也有写漏索引的时候靠人盯永远不靠谱。还有个误区是很多人觉得慢SQL治理是DBA的事和业务开发没关系实际上DBA根本管不过来一个公司几百个开发每天上线几千行SQLDBA不可能每一条都去审核。真正的慢SQL治理从来不是某几个人的事是个系统工程要从开发、测试、上线、运行全流程搭卡口用工具代替人审核用流程倒逼规范落地才能从根源上解决问题。二、开发阶段把慢SQL拦在写代码的第一时间治理慢SQL的第一个关口就是开发写代码的阶段要让开发在本地写SQL的时候就能发现问题不要等到提测了、上线了才知道SQL有问题。1、首先要把模糊的规范变成可落地的强约束很多团队都有厚厚的一本SQL开发规范写了几十上百条规则但是没人看也没人执行最后规范就是个摆设。我们当时把规范精简成了12条“红线规则”只要违反就不能提交代码没有商量的余地禁止写SELECT *必须明确指定查询的字段禁止在索引字段上做函数运算、表达式计算、隐式类型转换禁止在业务高峰期直接跑ALTER TABLE等DDL操作单表的索引数量不能超过5个禁止建冗余索引、重复索引单条SQL关联的表不能超过3张超过的话拆分SQL或者做冗余事务里禁止有RPC调用、HTTP请求、缓存查询等耗时操作禁止写不带LIMIT的查询语句避免全表拉取大量数据批量操作禁止循环单条写入必须用批量接口每次操作不超过1000条禁止在数据库里做大量数据的排序、分组、计算尽量在应用层做LIKE查询禁止前缀带%复杂搜索必须走ES深分页必须用延迟关联或者游标分页禁止写大offset的LIMITupdate/delete语句的where条件必须走索引禁止全表更新这些规则没有一条是高深的全是线上踩过无数坑总结出来的而且每条都能通过工具自动检查不需要人眼去看。2、在ORM层加自动校验开发本地就能发现问题我们当时在MyBatis拦截器里加了个SQL校验插件开发在本地跑代码的时候只要执行SQL插件会自动在前面拼上EXPLAIN先跑一遍执行计划如果发现有问题控制台直接红色大字警告甚至直接抛异常不让执行。我给你贴一下这个拦截器的核心逻辑非常简单但是效果特别好java// MyBatis SQL执行校验拦截器核心逻辑Intercepts({Signature(type StatementHandler.class, method query, args {Statement.class, ResultHandler.class})})public class SqlCheckInterceptor implements Interceptor {Overridepublic Object intercept(Invocation invocation) throws Throwable {StatementHandler handler (StatementHandler) invocation.getTarget();BoundSql boundSql handler.getBoundSql();String originalSql boundSql.getSql();// 只校验SELECT语句if (originalSql.trim().toUpperCase().startsWith(SELECT)) {String explainSql EXPLAIN originalSql;Connection conn handler.getExecutor().getTransaction().getConnection();try (Statement stmt conn.createStatement(); ResultSet rs stmt.executeQuery(explainSql)) {while (rs.next()) {String type rs.getString(type);String key rs.getString(key);long rows rs.getLong(rows);String extra rs.getString(Extra);List warnings new ArrayList();// 逐条校验规则if (ALL.equals(type)) warnings.add(SQL全表扫描请检查索引);if (key null) warnings.add(SQL未走索引请检查条件写法);if (rows 1000 !system.equals(type) !const.equals(type))warnings.add(SQL预计扫描行数超过1000行当前rows rows);if (extra ! null extra.contains(Using filesort))warnings.add(SQL使用文件排序请优化索引顺序);if (extra ! null extra.contains(Using temporary))warnings.add(SQL使用临时表请优化分组排序逻辑);if (!warnings.isEmpty()) {// 开发环境直接阻断测试/生产环境打告警if (Env.isLocal() || Env.isDev()) {throw new RuntimeException(SQL性能校验不通过 String.join(, warnings) SQL originalSql);} else {log.warn(SQL性能告警{}SQL{}, warnings, originalSql);}}}}}return invocation.proceed();}}这个插件上线之后开发在本地调试的时候只要写了慢SQL直接就跑不通控制台会明确告诉他哪里有问题怎么改。一开始还有人抱怨麻烦用了一个月之后大家都养成了写SQL就注意性能的习惯本地调试的时候就把慢SQL改好了根本流不到测试环节。3、封装常用场景的工具类从根源上避免错误除了校验我们还把容易出问题的场景封装成了统一的工具类不让开发自己写从根源上避免低级错误比如封装了统一的分页插件默认用延迟关联优化深分页封装了批量插入、批量更新工具自动分批提交封装了ES查询工具类所有模糊搜索、多条件筛选直接调用工具类走ES不用写MySQL的LIKE。举个例子之前新人很容易写循环单条插入的代码我们直接把Mapper的单条insert方法在默认情况下禁用批量插入必须用提供的batchInsert工具自动分批次自动控制事务从工具层面就堵死了单条循环写入的可能性再也没出现过批量操作导致的大事务问题。三、测试阶段自动审核卡口不让坏SQL流到线上开发本地的校验只能拦住一部分问题比如开发本地库数据量小可能几百条数据全表扫描也很快发现不了问题所以测试阶段是上线前最重要的一道卡口这一步我们靠CI/CD流水线自动审核不需要人去看代码。1、流水线自动扫描所有SQL做静态审核代码提交到Git之后CI流水线会自动跑一个SQL审核的任务扫描项目里所有的MyBatis XML文件、注解里的SQL语句把SQL全部提取出来做静态校验比如检查有没有SELECT *、有没有不带LIMIT的查询、有没有LIKE前缀带%、update/delete有没有where条件这些静态规则不符合的话直接卡CI代码合并不了必须改完才能提交。静态审核完了之后会把所有SQL自动在集成测试库上跑EXPLAIN这个测试库我们每天都同步生产的脱敏数据数据量和生产是1:1的所以执行计划和生产几乎完全一致。如果发现SQL全表扫描、扫描行数超过1万、有filesort/temporary直接把结果打在CI报告里标清楚是哪个SQL、哪个类、哪行代码是什么问题应该怎么优化开发直接照着改就行。2、自动做索引审核避免乱建索引很多开发改SQL的时候乱加索引导致冗余索引、重复索引一大堆拖慢写入性能。我们在流水线里加了索引校验如果代码里有DDL建索引的语句自动检查新增的索引是不是和已有索引重复比如已有(a,b,c)又建(a)或者(a,b)就提示冗余索引不需要新建联合索引的顺序是不是合理等值字段在前排序分组字段在中范围字段在后顺序不对就给出调整建议单表新增索引之后总数量是不是超过5个超了就提示优化索引不要新增字符串字段是不是默认建了过长的索引提示用前缀索引3、压测阶段必须过SQL性能基线所有涉及到数据库变更的功能上线前必须做性能压测压测的时候我们会自动收集接口调用的所有SQL统计每个SQL的平均耗时、99分位耗时、扫描行数要求所有SQL的99分位执行时间不能超过100毫秒单SQL扫描行数不能超过1万行不符合要求的压测直接不通过必须优化完再压。之前我们压测一个新的商品列表接口发现有个SQL平均耗时200毫秒99分位到了1秒多开发一开始觉得“200毫秒也能用”后来按照要求优化加了合适的联合索引耗时降到了20毫秒上线之后高峰期也没出问题。要是没压测直接上线大促的时候肯定要慢查询报警。这套流水线自动审核上线之后第一个月就卡下了127个不符合规范的SQL其中32个是全表扫描的严重问题要是这些SQL直接上线按之前的故障率最少要出3-4次P1/P0故障相当于还没上线就避免了几次故障。如果你们团队不想自己写这套审核逻辑也可以直接用开源的SQL审核工具比如Yearning、Archery直接集成到流水线里开箱即用效果差不多。四、上线阶段灰度验证安全变更哪怕测试环境测的再全测试环境和生产环境还是有区别的比如统计信息不准、数据分布不一致可能导致生产环境的执行计划和测试环境不一样所以上线阶段也要做管控避免一上线就炸。1、所有DDL和慢SQL变更必须灰度禁止直接全量加索引、改SQL、大表变更这种操作禁止直接在主库全量执行首先在从库上跑一遍EXPLAIN确认执行计划正确走了预期的索引然后先在1台从库上执行变更观察10分钟看看从库的CPU、IO、延迟有没有问题没问题再在其他从库执行最后再在主库执行。所有DDL操作必须用pt-online-schema-change或者gh-ost这种无锁变更工具禁止直接执行ALTER TABLE避免大表加索引锁表。而且DDL必须选在业务低峰期比如凌晨2-4点执行避开业务高峰期哪怕出问题影响也小。2、新功能灰度放量实时观察SQL性能新功能上线的时候不要上来就切100%流量先切1%的流量观察10分钟重点看数据库的几个指标CPU使用率、慢查询数量、锁等待时间、主从延迟如果发现新上的SQL慢查询数量暴涨立刻切回流量回滚代码不要硬扛。等1%流量没问题了再逐步放到10%、30%、50%、100%每一步都观察监控确认没问题再继续。我们之前有个新功能上线1%流量的时候就发现有个SQL执行时间500多毫秒赶紧回滚了后来发现是优化器选错了索引加了FORCE INDEX改完再上就没问题了如果当时直接全量上线肯定又要出故障。3、上线必须有人值守做好回滚预案所有涉及数据库变更的上线必须有后端开发和DBA双值守盯着监控大屏一旦出现慢SQL暴涨、CPU飙升、锁等待立刻执行回滚预案先恢复业务再排查问题不要在线上debug。我们当时做了一键回滚的脚本只要出问题点一下就能把代码回滚、把新增的索引删掉1分钟就能恢复业务。五、运行阶段监控预警自动止血就算前面的卡口再严也难免有漏网之鱼线上运行阶段的监控体系是最后一道防线要做到问题刚出现就发现刚影响业务就止血不要等业务挂了才知道。1、建立分钟级慢查询监控和精准告警我们用Prometheusmysqld_exporter采集数据库的指标用pt-query-digest实时分析慢查询日志把所有慢SQL按指纹归类分钟级统计每个SQL的执行次数、平均耗时、扫描行数。一旦某个SQL的1分钟慢查询次数超过5次或者99分位耗时超过1秒立刻给对应的开发发告警信息带上SQL内容、执行计划、负责人信息让开发第一时间处理不要等CPU打满了才告警。这里要注意告警不要泛滥不要所有慢SQL都打电话只有影响业务的才打电话告警普通慢SQL发企业微信消息就行不然告警多了大家就不看了。2、SQL指纹库责任到人我们给所有线上运行过的SQL建了指纹库记录每个SQL的内容、对应的业务模块、代码位置、负责人一旦出现慢SQL直接推送给对应的负责人不用DBA一个个查Git日志找人处理效率高了很多。每个月我们还会统计每个团队的慢SQL数量、优化率作为团队质量的考核指标。3、自动止血机制先保业务再排查为了避免某个SQL突然爆量把库打满我们做了几个自动止血的策略自动kill执行时间超过5秒的SELECT语句避免长查询占用连接和CPU资源如果数据库CPU超过90%自动开启SQL限流优先放行支付、下单这些核心接口的SQL限流非核心的后台统计、报表查询SQL如果出现大量锁等待自动kill掉执行时间最长的那个事务释放锁避免雪崩4、定期巡检主动优化不要等慢SQL触发告警了才去优化每周我们都会自动跑巡检任务生成数据库健康报告TOP20慢SQL列表、冗余索引列表、长期没用到的索引、长事务统计、表碎片率把这些任务分发给对应的开发每周固定时间优化把问题消灭在萌芽状态。比如我们每个季度都会清理一次没用的冗余索引每次清理完数据库的写入性能都能提升10%-20%。六、落地治理体系的核心不是工具是人和流程很多团队也买了SQL审核工具也搭了监控但是最后慢SQL还是一堆根源是只买了工具没落地流程和责任。1、首先要明确责任谁写的SQL谁负责慢SQL优化的第一责任人是写代码的开发不是DBADBA只负责提供工具、平台和技术指导不对业务SQL的性能负责。之前我们慢SQL都是DBA追着开发改效率特别低后来改成责任到人出了慢SQL直接算开发的故障开发自己就会主动注意SQL性能根本不用人催。2、不要搞一刀切要给开发赋能。比如你说不让写深分页你得告诉开发深分页怎么优化提供现成的工具你说不让写LIKE你得提供ES的封装工具不然开发为了实现业务需求还是会写不符合规范的SQL。我们当时做了很多内部培训把常见的坑、优化方法、工具用法讲给开发还整理了一本SQL优化手册开发遇到问题直接查就行降低优化的门槛。3、持续迭代优化体系不是搭完就完事了。每个月我们都会复盘当月的慢SQL问题看看是哪个环节没卡口才流到线上的如果是开发阶段的校验规则没覆盖就给拦截器加规则如果是流水线审核漏了就给流水线加校验如果是监控没覆盖就加监控指标慢慢的漏洞越来越少慢SQL自然就越来越少。这套体系我们落地之后效果比我们预期的还好我把治理前后的核心指标整理成了表格变化非常明显表格指标项 治理前 治理后月均慢SQL数量 827条 28条年均慢SQL导致故障数 12次 0次数据库CPU峰值 75% 32%慢SQL平均发现时间 故障后20分钟 上线前/上线后1分钟内慢SQL平均修复时间 45分钟 5分钟上线半年后每个月的慢SQL基本稳定在30条左右而且都是一些边缘的后台报表SQL根本影响不了核心业务。18个月来我们没出过一次慢SQL导致的P0/P1故障数据库的CPU峰值从常年70%多降到了30%本来计划要给数据库扩容分库分表最后也没扩省了几十万的服务器成本。很多人觉得慢SQL优化是个技术活要懂很多数据库底层原理才能做好其实我做了这么多年觉得90%的慢SQL问题根本不需要什么高深的技术只要你能在正确的环节把卡口做好用工具代替人去做重复的审核用流程把规范落地就能避免绝大多数的问题。比起你精通B树原理、能把优化器源码讲的头头是道搭一套能落地的治理体系才是解决慢SQL问题真正的终极方案毕竟你技术再强也不可能看完所有开发写的每一行SQL。注意本文所介绍的软件及功能均基于公开信息整理仅供用户参考。在使用任何软件时请务必遵守相关法律法规及软件使用协议。同时本文不涉及任何商业推广或引流行为仅为用户提供一个了解和使用该工具的渠道。你在生活中时遇到了哪些问题你是如何解决的欢迎在评论区分享你的经验和心得希望这篇文章能够满足您的需求如果您有任何修改意见或需要进一步的帮助请随时告诉我感谢各位支持可以关注我的个人主页找到你所需要的宝贝。博文入口山峰哥-CSDN博客复制到【浏览器】打开即可,宝贝入口常用软件宝贝精品文件作者郑重声明本文内容为本人原创文章纯净无利益纠葛如有不妥之处请及时联系修改或删除。诚邀各位读者秉持理性态度交流共筑和谐讨论氛围