这个标题是我在内部性能治理专项里沉淀下来的一套完整打法。很多团队处理慢SQL基本还停留在“线上告警响了赶紧抓个慢日志看一眼加个索引试一试”的阶段。这套流程不是不能用但问题在于它靠人肉盯、靠运气碰慢SQL的发现是滞后的复现是不可控的优化效果的验证更是凭感觉。我写这篇文章是想把这套从“被动救火”升级到“主动巡逻”的完整方法拆开讲透重点落在两个关键词上自动识别和压测复现。前者解决的是“怎么提前、全面、不带主观偏差地把慢SQL找出来”后者解决的是“怎么在测试环境里把问题复现出来并且用数据验证优化到底有没有效”。无论你是后端开发、DBA还是负责接口性能的测试同学这套思路都能直接搬到你自己的项目里。1. 为什么慢SQL不能靠“看见才处理”——从被动救火到自动巡逻我最早接触慢SQL治理的时候踩过一个大坑线上数据库的慢查询日志一直开着但阈值设的是默认的10秒。结果就是日志文件里记录的SQL寥寥无几而且能超过10秒的SQL基本都是已经把线上拖垮了的级别。团队当时的状态是每周手动导一次慢日志用文本编辑器打开搜几个耗时高的SQL看一眼然后就不了了之。说实话这种节奏根本谈不上“治理”更像是在做考古。真正让我下定决心做自动识别是因为一次典型的线上事故。某天下午一个报表查询接口突然从200毫秒飙到了8秒紧接着数据库的CPU直接被打满连带同库的十几个接口全部超时。事后追查问题SQL在一个多月前就出现过只是当时的耗时是1.2秒没有触发阈值也没有人注意到。等到它数据量涨上去、查询条件变了直接变成灾难。这个经历给了我两个教训第一慢SQL的定义不能一刀切1秒对于核心交易接口来说已经很慢了但对于一个跑批任务来说完全正常第二靠人眼盯日志一定会漏因为人不可能24小时盯着波动曲线。后来我开始搭自动识别链路核心思路是四个字持续采集、分级推送。具体来说把慢日志的采集从“手动导出”变成“定时任务实时监控”把判定标准从“绝对值阈值”变成“基线与绝对值结合”把推送方式从“一个邮件列表所有人”变成“按严重程度分级定向通知”。这套链路跑起来之后团队从“等用户投诉”变成了“提前干预”识别时效从“周”级别缩短到了“分钟”级别。这里有一个很重要的认知转变慢SQL自动识别不是简单地写个脚本扫日志文件它是一个完整的闭环覆盖采集、分析、判定、通知、归档五个环节。采集环节解决“数据从哪来”的问题分析环节解决“哪些SQL值得关注”的问题判定环节解决“什么程度才算慢”的问题通知环节解决“让谁在什么时间知道”的问题归档环节解决“下次优化怎么找历史依据”的问题。五个环节少了任何一个自动识别都会出现断层。采集层从数据库慢日志、性能视图、监控代理三条路径取数据互为备份分析层对SQL做去参数化把具体值替换成占位符归类聚合统计执行次数、总耗时、平均耗时、扫描行数等指标判定层基线与绝对值双阈值识别突增和新出现两类问题SQL通知层按严重程度路由到不同渠道避免告警疲劳归档层按天归档分析结果支持趋势对比和回归分析。2. 慢SQL自动识别的完整落地链路采集、筛选、分级与推送2.1 采集层慢日志、性能视图与监控代理三路并进采集是整个链路的地基。我的建议是不要只依赖慢查询日志文件因为很多云数据库默认不开启慢日志或者日志保留周期很短。我在实际项目中用了三种来源互相校验、互为补充。第一路是慢查询日志。以MySQL为例开启慢日志需要调整两个核心参数slow_query_log设为ONlong_query_time设为需要捕获的阈值。这里有一个关键细节线上环境的long_query_time不要直接设成1秒建议从0.5秒开始收集宁多勿缺。因为日志只做存档用最终的分析判断交给后面的判定层如果采集阶段就漏掉了后面再想追回来就难了。收集到的日志可以用pt-query-digest做离线解析把不同格式的慢日志统一成结构化数据方便后续分析。第二路是数据库自带的性能视图。MySQL有performance_schema和sys库PostgreSQL有pg_stat_statements这些视图能直接看到SQL的执行次数、总耗时、平均耗时、扫描行数、临时表使用等统计信息。性能视图的优势是数据实时、开销低劣势是只能看到聚合后的结果看不到单条SQL的完整执行细节。所以它适合做“面”上的监控发现问题后再用慢日志做“点”上的回溯。第三路是监控代理。如果你用的是云数据库或者线上有自建的监控系统可以采集数据库的线程数、连接数、CPU、IO延迟等指标和SQL执行情况关联起来。这一路的目的是发现“系统指标异常但慢日志还没刷出来”的情况。比如有一次我们发现连接数异常升高但慢日志里没有新增记录最后排查发现是一个批量更新任务触发了大量的行锁等待每条SQL本身不算慢但并发叠加之后把整个库拖垮了。这种场景只看慢日志是发现不了的。三路采集的数据统一落到一个存储里我推荐用时序数据库保留原始SQL样本和聚合指标。存储周期至少要覆盖一个完整的业务周期我个人习惯保留30天这样既能做月度趋势对比又不至于占用太多磁盘。2.2 分析层去参数化与归类聚合把“一堆日志”变成“少数问题”原始慢日志的问题是零散、重复、难以统计。同一个查询只是条件参数不同在日志里就会记录成几十上百条。如果不做去参数化分析层看到的就是一片散沙根本看不出哪个SQL才是真正的瓶颈。去参数化的做法是把SQL里的字面量替换成统一的占位符。比如下面这条SQLSELECT * FROM order_detail WHERE user_id 10234 AND status 1 ORDER BY create_time DESC LIMIT 10;去参数化之后变成SELECT * FROM order_detail WHERE user_id ? AND status ? ORDER BY create_time DESC LIMIT ?;这一步的目的是把同一类查询聚合到一起计算这一类查询的总执行次数、总耗时、平均耗时、最大耗时、扫描行数等指标。做完这一步原本几百条零散日志可能就收敛成十几个模板问题范围瞬间缩小。去参数化工具可以直接用pt-query-digest它是Percona Toolkit里的组件能自动完成解析和聚合。用法很简单pt-query-digest /var/log/mysql/slow.log digest_report.txt生成报告里会按“总耗时/执行次数”排序排在前面的是最值得关注的SQL模板。不过我后来很少直接用这个报告作为最终清单因为它缺少“上下文”——它不知道哪些SQL是核心交易链路上的哪些只是后台任务。所以我的习惯是把它作为中间产物再叠加业务侧的路由信息做二次过滤。2.3 判定层绝对值阈值与基线偏离度双轨并行判定层是整个自动识别链路的核心也是最容易设计错的地方。很多团队的判定逻辑只有一个绝对值执行时间超过1秒就告警。这个做法的问题很明显不同接口、不同时段的性能基线的差异完全被忽略掉了。凌晨的批量计算跑3秒很正常白天高峰的核心查询跑800毫秒可能已经是事故。我的做法是双阈值并行。第一条是绝对值阈值这个比较好理解选一个保守的下限比如500毫秒超过就进入候选集。第二条是基线偏离度这是一个动态概念以过去7天同一时段、同一SQL模板的平均耗时为基线当当前耗时的偏离度超过一定倍数比如3倍时即使绝对值没超过阈值也要纳入告警。这就是前面提到的“1.2秒慢SQL”能提前被发现的方法——它的基线的历史均值可能只有300毫秒某次突然涨到1.2秒偏离度已达4倍说明SQL的执行计划、数据量或系统状态发生了变化。偏离度判定不能只看单次样本要看窗口中位值和滚动均值。单次抖动可能是网络波动但持续3分钟以上的偏离就有实际意义了。所以我把偏离度计算放到一个滚动窗口里窗口长度设为5分钟每30秒计算一次。只有连续N个窗口命中才推送告警这样能过滤掉不少噪声。2.4 通知层按严重程度分级定向推送避免告警疲劳告警疲劳是慢SQL治理里一个很隐蔽的杀手。如果群里每天被刷几十条“发现慢SQL”慢慢就没人看了。我的分级策略是三层递进只有命中高优规则的SQL才会打到值班群其余做日报聚合。低优日报汇总单次执行超过阈值但出现的频率很低一天不超过几次且不涉及核心接口。这类SQL沉淀到日报里每周集中处理一批。中优工作群通知SQL模板的平均耗时持续超过阈值或偏离度超过3倍影响到部分接口的可用性。这类及时发到项目群通知相关模块的负责人确认原因。高优电话/紧急群慢SQL直接打满数据库资源或核心交易链路的成功率开始下降。这类必须立即响应推送的同时附带上问题SQL、耗时曲线、涉及的接口列表。分级推送的实现不复杂判完级别后按规则路由到不同的钉钉/企微群的机器人地址就行。推送文案要标准化至少包含SQL模板去参数化后的、当前耗时、基线耗时、首次发现时间、持续时间、涉及的接口和数据库实例。信息不全的告警等于没告警接收方还得自己去查一遍效率会低很多。3. 压测复现的难点与解法让测试环境“复刻”线上的慢识别到慢SQL只是第一步真正的硬骨头是复现。很多SQL的问题在测试环境调不出来原因很简单测试环境的数据量、数据分布、并发模型和线上差距太大。一条SQL在测试库里100毫秒跑完到了线上要2秒你在测试环境怎么压都压不出那个2秒来。压测复现的本质不是“跑一遍这条SQL”而是“复刻导致它慢的系统条件”。3.1 复现失败的三大根因数据量、数据分布、并发环境我在很多个项目里碰过至少三次以上这类现象开发拿到慢SQL去测试库一执行耗时几十毫秒随手标记一个“复现不了”就关了。这个结论下得太早问题SQL背后有三个关键要素必须在复现环境里对齐缺一个都复现不出来。第一个是数据量。优化器选择执行计划时一个很重要的依据是表的行数和列的数据分布。表里只有1万行数据和一个有1亿行数据的表优化器大概率会选择不同的索引策略。所以在复现环境里目标表的数据量至少要到线上的一定量级。不需要精确到一模一样但如果要复现一个因为“基数估算偏差”导致的慢查询数据量级一定要接近。我一般会先把相关表的数据行数对齐到线上的80%以上再考虑怎么处理敏感字段。第二个是数据分布。这个是最容易忽略的。比如线上有一个status字段绝大多数行都是1正常只有极小比例是0待审核查询条件里status 0应该走索引范围扫描。但如果测试环境是随机生成的1和0各占50%优化器算出来的区分度完全不一样执行计划可能直接变成全表扫描。这就完全复现不出线上的真实问题。第三个是并发环境。单条SQL慢和系统慢是两个层面的事。一条SQL单独执行只要200毫秒但如果同时有200个这样的查询并发进来会导致数据库的连接池被打满、锁竞争加剧、IO队列堆积最终每条SQL的实际耗时可能翻好几倍。这种慢是“压出来的”不是“查出来的”。只靠单条执行根本复现不了。3.2 复现五步法从样本采集到并发模拟的完整流程我总结了一套复现流程按顺序执行能够尽可能还原线上的慢SQL问题。第一步采集线上样本。用pt-query-digest从慢日志里提取目标SQL模板然后回到最近几天的归档日志里找出这个模板的N条真实样本SQL尽量保留真实的参数值。这一步是为了后面做参数化扫描的时候能贴近线上的实际查询模式。第二步同步表结构与数据。建一个独立的压测库表结构完全从线上同步索引结构一模一样。数据先做脱敏手机号、身份证这类字段做哈希或者混淆再按业务规则把数据量填充到接近线上的量级分布不一致的地方要重点圈出来。第三步执行计划基线比对。在压测库里先跑一遍目标SQL模板用EXPLAIN查看执行计划确认它与线上慢日志抓取时段内的执行计划是否一致。这一步非常关键如果执行计划都不一样后面压出来的数据没有任何参考价值。常见的不一致原因包括索引没同步、统计信息过期、参数值不同导致的分区裁剪不一致。第四步单SQL循环压测。写一个简单的脚本用真实参数样本循环执行看平均耗时和最大耗时记录SQL的单次执行特征。注意用短连接压测时要把连接建立的开销排除掉用长连接复用否则压出来的耗时里混入了大量握手开销。第五步并发场景模拟。用压测工具我常用的是sysbench和JMeter也可以用自研简单压测脚本模拟目标接口的真实并发数。并发数的取值建议直接参考线上监控里的QPS和连接数峰值。如果拿不到精确值可以从接入层日志里统计该接口的平均QPS乘上压测倍率比如2倍或3倍。第五步跑完之后如果压测库里的平均耗时和线上慢日志里的耗时基本对齐复现就算成功了。如果相差很大回到第三步检查执行计划再检查数据分布重复迭代。3.3 压测工具选型与脚本要点压测工具选型上我强调一下能用现成工具就用现成工具不要一上来就写并发框架。很多人一听到压测就想到要写一个高性能的并发客户端其实90%的场景根本到不了这个复杂度。sysbench做数据库纯压力测试很好用特别是做OLTP混合场景的基准测试JMeter做接口级压测更合适因为能拼出完整的HTTP请求MySQL Shell自带的压测工具也可以跑简单的报告。如果只是针对一条SQL做循环压测我一般用Python的concurrent.futures模块就能解决简单场景根本不需要引入分布式压测集群。一个小技巧压测脚本里要记录每次执行耗时和是否超时的标记不要只看平均耗时。平均耗时会被“大多数快请求”拉低把长尾掩盖掉。慢SQL治理关注的就是长尾所以统计分位数P50、P95、P99比平均值重要得多。我压测出来的结果一般会汇总成类似下面这样的表格指标P50P95P99最大耗时基线版本120ms300ms850ms1200ms优化版本45ms80ms150ms260ms看到P99和最大值的变化才能真正判断优化是否压住了长尾。4. 复现之后如何定位根因执行计划、数据分布与成本模型复现成功只代表你“看到了问题”但离“解决问题”还有一段路。定位根因时我有一套固定的排查顺序先看执行计划再看数据分布然后结合成本模型推算最后用实际压测结果来验证。4.1 执行计划解读从type、key和rows三个字段入手拿到一条慢SQL第一件事是看EXPLAINMySQL 8.0以上还可以用EXPLAIN ANALYZE拿到更精确的执行耗时和行数。但要注意执行计划是优化器基于统计信息和成本模型“估计”出来的它只能告诉你优化器“打算怎么做”不一定代表实际执行就是这样的。所以我会同时看执行计划和实际执行结果重点关注三个字段。type代表访问类型从好到差大致是const、eq_ref、ref、range、index、ALL。其中ALL是全表扫描这个一般来说是性能警报但也不是绝对的如果表很小比如几千行全表扫描可能反而比走索引更快。对慢SQL来说如果type是ALL且表很大基本可以断定索引设计有问题。key代表实际使用的索引。这条SQL用了哪个索引、有没有索引可以命中一眼就能看出来。很多慢SQL的根因是“明明有索引优化器偏偏不用”这种情况往往跟数据分布或查询条件里的函数运算有关。rows是优化器预估需要扫描的行数。这个值要和实际执行返回的行数做对比。预估行数和实际行数差距巨大说明统计信息过期或者修改过表结构后没有重新统计。遇到这种情况第一要做的是ANALYZE TABLE刷新统计信息而不是急着改SQL。有时候问题就这么简单统计信息一更新执行计划从全表扫描变成索引范围扫描性能立刻回升。4.2 一条“优化后反而更慢”的SQL排查实录我曾经排查过一个典型案例一条SQL刚上线的时候执行很快运行两周后突然变慢。执行计划显示它从走ref变成了走range扫描行数从几万涨到几百万。当时第一反应是索引失效了检查了索引文件大小又检查了索引碎片都没问题。后来又怀疑是数据量涨了但表行数表明确实涨得不多。最后仔细看EXPLAIN输出才发现问题出在查询条件里一个时间字段上优化器在估算范围扫描效率时认为范围太大改选了另一条覆盖范围更广的索引结果扫描行数更多反而更慢。这个场景很典型优化器选的索引不一定执行最快它只是基于成本模型的估算“认为”最快。解决办法是用索引提示FORCE INDEX强制走原先那个索引跑完压测确认耗时确实降下来了再在代码里固化这个索引选择。这里提一个经验FORCE INDEX只是临时手段最终还是要重新设计索引组合或者改写成效率更高SQL因为强制指定索引会让后续的索引变更失去灵活性。4.3 数据倾斜与统计信息过期两个容易被忽略的根因数据倾斜问题在订单表、用户表这类带状态字段的表里特别常见。有一个订单查询接口慢日志里频繁出现但执行计划里显示走了索引。后来把查询参数和行的分布一对比才发现问题查询条件是status 0 AND create_time ? AND shop_id ?而status 0的行数极少但刚好这个店铺的数据全部集中在某一天当天数据量特别大。索引列虽然命中了但扫描的还是很多行加上海量排序直接把临时表磁盘IO打满。这种场景的根本解法是重新考虑索引字段的排列顺序把区分度更高的shop_id放到索引最左列同时适当调整条件字段的顺序让优化器有更好的选择空间。统计信息过期则是一个更隐蔽的问题。如果数据变更频繁统计信息可能严重滞后。我遇到过执行计划里rows显示预估扫描100行实际却扫了100万行的情况。更新统计信息后问题自然消失。所以排查慢SQL时如果发现执行计划异常第一步先做统计信息更新成本几乎是零却可能省下大量的无用调优时间。5. 一次慢SQL优化的完整复盘从识别到压测验证的全过程光讲方法论不够我拿一次真实的线上优化做个全程复盘把前面说的自动识别、压测复现、根因定位、优化验证串一遍。项目背景是内部的一个订单查询服务数据库是MySQL 8.0订单表数据量大概在1.2亿行每天新增50万行左右。5.1 发现问题自动识别链路抓到一个“隐形慢SQL”某天上午10点告警群里推了一条中优日志订单查询接口的平均耗时从基线的800毫秒飙升到2.5秒持续了5分钟。告警给出的SQL模板是这样的脱敏后的SELECT id, order_no, status, amount FROM t_order_detail WHERE status 1 AND shop_id 345 AND create_time 2024-06-01 00:00:00 AND create_time 2024-06-08 00:00:00 ORDER BY create_time DESC LIMIT 20;这个模板在此之前从没进过慢日志是典型的“偏离度触发”案例——它的历史平均耗时只有800毫秒而这天突然涨到了2.5秒。如果只靠绝对值阈值比如1秒这条SQL在10点整就会被识别但有了偏离度判断我们在9点55分提前收到了候选集报告比原定阈值得到了更早的预警时间。这一步直接验证了我前面说的双阈值判定的价值。5.2 压测复现按五步法在测试环境还原出2.3秒的耗时拿到告警后我按五步法在测试环境复现。先抽取当天慢日志里该模板的真实样本发现220毫秒的耗时主要集中在shop_id IN (300, 345, 400)的这一批查询上。之后同步压测库的表结构和索引把测试库订单表的数据量灌到1亿以上并且特别把这几个店铺的订单集中分布在一周内。执行计划比对时发现了一个明显差异线上执行计划里type是ref用的索引是idx_shop_status_time但测试库里这个索引还没建完整优化器选择了另外一个索引导致类型变成range。我把索引补齐、重新收集统计信息之后测试库的EXPLAIN才和线上对齐。单SQL循环压测时平均耗时已经能稳定在1.9秒到2.3秒左右和线上的2.5秒基本在一个量级。到这里复现算是成功了。接下来我用并发模型做叠加验证——模拟20个并发请求同时调用这个接口数据库连接池马上开始堆积P99飙升到4秒以上。这个问题如果不做并发模拟根本看不到。5.3 根因定位索引设计失效与覆盖索引缺位压测复现跑通后根因很快就浮出水面。EXPLAIN显示SQL用了idx_shop_status_time这个索引但扫描行数还是到了400万行。原因是这个索引结构是(shop_id, status, create_time)理论上效率应该不差。但问题在于返回的列里有amount字段索引里没有覆盖每条命中的行都得回表一次400万行的回表导致大量随机IO。真正让我确认根因的是拿真实的数据分布验证了索引区分度。这些店铺的单量非常大即便过滤了status 1和时间段剩下的行数依然有上百万索引代价很高。所以问题的根子在于这个查询本质上是高区分度小结果集查询却在索引设计上把它当成低区分度大范围扫描来处理了。5.4 优化方案与压测验证P99从850毫秒降到150毫秒优化方案做了两件事。第一件是把索引改成覆盖索引把amount和id加进索引里这样查询结果可以直接从索引返回不用回表。新的索引长这样ALTER TABLE t_order_detail ADD INDEX idx_shop_status_time_cover (shop_id, status, create_time, amount, id);第二件是调整了业务侧的一个参数绑定习惯让shop_id的查询更稳定地走固定值传递。因为有些调用方把shop_id当成可选参数导致status 1 AND create_time BETWEEN ...单独命中索引而不是优先命中shop_id。这个层面上靠SQL改写比较麻烦我在代码层面对SQL模板做了参数化改写始终保持shop_id必传并调整了条件条件的顺序表达。改完之后重新压测结果就是上面那张性能对比表P50从120毫秒降到45毫秒P95从300毫秒降到80毫秒P99从850毫秒降到150毫秒。而且这是在数据量、数据分布、并发模型都和线上对齐的前提下压出来的数据。之后在灰度环境放量验证跑了一周没有再出现偏离度告警慢日志里这个模板的耗时也恢复到了基线水位。6. 落地慢SQL治理的几条经验教训与工具清单文章最后我想把这几年来踩过的坑和沉淀下来的工具清单整理一下。这些经验不一定都写在官方文档里但遇到实际问题时它们往往比那些常规建议更管用。第一慢SQL治理要先定基线再定阈值不要上来就拍一个固定的慢查询时间。没有基线的阈值设置不是治理是碰运气。基线的计算建议至少取7天同时间段的窗口并且排除大促、发布、变更日的异常数据避免把偏差数据当常态。第二执行计划的比对要放在压测复现之前完成。我见过太多团队直接跳过执行计划对比满心欢喜地以为在测试环境复现了结果压测耗时和线上差十几倍回头才发现是执行计划不对所有压测数据全部作废。这个顺序一定不能乱。第三压测结果的关注顺序是P99、P95、P50最后才是平均值。平均值会掩盖长尾而长尾是整个系统的真实体验。如果你的系统P99稳平均值高一点无伤大雅反过来平均值很好看但P99超高说明有少量请求体验极差这种问题最坑人。对慢SQL的优化验证尤其如此判定优化成功的标准应该是P99显著下降而不是平均耗时降了多少。第四自动识别的归档数据要做趋势对比。这能帮你提前发现“潜在的慢SQL”——虽然某些SQL模板还没触发告警但它的平均耗时连续两周每周涨15%这可能就是索引失效或数据倾斜的早期信号。早发现半个月能避免一次线上事故。工具清单方面我日常用到的核心工具有这些整理成表格方便参考环节推荐工具说明慢日志采集与解析pt-query-digestPercona Toolkit组件去参数化、聚合统计慢日志开启与参数调优MySQLslow_query_log/long_query_time阈值建议从0.5秒起收集性能视图辅助MySQLperformance_schema/sys库聚合统计低开销SQL执行计划分析EXPLAIN/EXPLAIN ANALYZEMySQL 8.0推荐用ANALYZE实测压测工具sysbench/JMeter/ Python并发脚本数据库级到接口级均可覆盖监控告警自研脚本指标入库结合时序数据库做基线偏离度计算我个人实际操作的体会是慢SQL治理不是一个一次性的专项它应该是一个持续运转的流程。你不可能把线上所有SQL都优化到极致但只要有自动识别和压测复现这两套机制在最坏的情况也不过是“晚发现一天”。那种“线上已经报警了还靠人去日志里翻SQL”的日子是真的一去不复返了。最后再分享一个小技巧每次优化完SQL后把优化前后的执行计划、压测报告、上线时间一起存到归档目录里三个月后回头翻一遍你会对系统性能变化的规律有更清晰的认识。