我做SQL调优差不多快十年一个很深的感受是慢查询从来不只是索引问题更多是管理问题。以前在一套订单分析系统里有个统计SQL跑一次要2.8秒加了一层复合索引后掉到80毫秒当时团队都觉得调优太简单了。结果半个月后数据量翻倍这条SQL又回到1.3秒EXPLAIN一看优化器换了访问路径之前加的索引根本没走。这种“救火式”调优救一次添一个疤系统越改越脆。真正让我从慢查询稳定走到秒级甚至毫秒级响应的是整个工程化调优的链路定位、看执行计划、算索引选择性、重写SQL、再固化成回归监控。这篇文章就把这套方法完整拆开适合正在被线上慢查询追着跑的开发、DBA和架构师。1. 为什么“多加点索引”从来救不了慢查询1.1 一次“越优化越慢”的复盘先讲一个真实踩过的坑。某交易报表服务在晚上十点告警一个查询订单汇总的接口响应耗时超过2.8秒调用方一直超时重试。我当时第一个念头也是“查索引”发现orders表在shop_id上确实有单列索引于是顺手补了一个(shop_id, created_at)复合索引重新执行耗时降到180毫秒告警解除。结果第三天同样的SQL又回来了这次1.4秒。EXPLAIN看完才发现根因根本不是索引少sql里有status IN (2,3,5)和created_at 某个时间点两个过滤条件优化器评估之后选择了全表扫描因为字段分布出现了倾斜某个大商铺的订单占了全表将近一半。复合索引只在小数据量下蒙对了路径数据分布一变化路径就变了。这次让我意识到一个很残酷的事实只对着单条SQL“加索引、看耗时”属于盲人摸象慢查询的根源经常在数据分布、统计信息、执行计划选择机制这些更下层的地方。1.2 救火式调优与工程化调优的分界线很多人把调优理解成哪里慢就加索引索引不行就改SQL改完SQL再慢就上缓存。这套逻辑在系统规模小的时候没问题但数据量一旦涨上去你会发现每次都在打地鼠。工程化调优和救火式调优有一个本质区别它把经验变成了一套可重复、可验证、可回滚的流程。维度救火式调优工程化调优定位凭感觉猜表、猜字段先采集慢查询日志按频率和耗时排序分析看执行计划但只扫一眼记录baseline对比优化前后的访问路径优化加索引直接上线评估区分度、索引代价、副作用验证本地跑一次不慢就结束用压测和线上小流量逐步灰度回归没人管后续变化监控耗时和扫描行数异常自动告警后面的内容全部围绕这条分界线展开。先把“凶手”找出来再谈怎么开枪。2. 慢查询定位先锁定异常再动手2.1 慢查询日志阈值怎么定才科学慢查询日志是最朴素也最可靠的第一手证据很多人却把阈值设成默认的10秒等于把大部分有用信息都过滤掉了。我一般会分环境看开发环境直接设到0.1秒测试环境0.2秒生产环境先看业务对接口的容忍度通常从1秒起步压到0.3秒左右。临时开启的命令很简单SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 0.2; SET GLOBAL log_queries_not_using_indexes OFF;注意log_queries_not_using_indexes这个开关生产环境建议保持OFF。很多刚接触调优的朋友打开这个参数后日志文件十几分钟就被刷爆因为一些本来就该走全表的小配置表也会被记录进去干扰判断。2.2 从慢日志到嫌疑SQL的完整筛查链路拿到慢日志别一条条肉眼看先做聚合。我通常用mysqldumpslow统计相同指纹的出现次数再按平均耗时倒序排mysqldumpslow -s at -t 20 /data/mysql/slow.log但慢日志只能回答“哪些查询慢”回答不了“哪些查询影响最大”。真正决定优化优先级的不是单次耗时而是单次耗时×调用频率这个乘积。一个300毫秒的查询如果一天被调用一百万次累计的CPU消耗远超过一条偶尔跑上2秒的报表SQL。把嫌疑SQL捞出来记录三个关键指标用于后续对比平均耗时和处理行数返回行数扫描行数与返回行数的比值这个比值如果超过100大概率是索引没命中或者SQL写成了让优化器不得不扫大量数据的形式。2.3 数据大起来后还要看历史基线对比有一类慢查询不是突然变慢的而是一周内从200毫秒缓慢涨到800毫秒。这种渐进式劣化靠慢日志的瞬间快照很难发现需要把历史的耗时、扫描行数保存成曲线。我自己的做法是每天凌晨把关键SQL的执行耗时和执行计划摘要导出到一个统计表保留30天。这样每次优化前能明确回答一个问题这条SQL是今天开始慢的还是一直都慢这两个问题的排查方向完全不同。今天开始慢大概率是统计信息过期、数据分布突变或并发升高一直都慢那才是SQL结构和索引设计本身有问题。3. 读懂执行计划让优化器交出证据3.1 从一条全表扫描的订单查询说起定位到嫌疑SQL之后第一件事永远是用EXPLAIN确认访问路径而不是直接改SQL。拿一个很常见的订单列表查询举例SELECT order_id, shop_id, user_id, amount FROM orders WHERE create_time 2024-06-01 00:00:00 AND status IN (2,3,5) ORDER BY create_time DESC LIMIT 20;EXPLAIN结果显示typeALL扫描行数约280万返回只有20行。这里最迷惑人的点在于create_time和status上都有独立的单列索引为什么优化器还是选择了全表扫因为create_time 某一天这个过滤条件即使走索引也需要回表读取大约几十万行订单数据优化器评估的回表成本比顺序扫描更大status IN (2,3,5)的区分度又很低。两个条件单独看都没什么性价比组合起来也没有可用的复合索引优化器只能选全表扫。解决方案不是简单加一个双列索引而是要看查询模式这个SQL是高频接口那么(status, create_time)会比(create_time, status)更合适因为等值条件status在前可以精确定位到少数几个索引分支再在分支内做范围扫描。EXPLAIN里的几个核心字段一定要养成看完的习惯type从system到ALL级别越靠后越危险key实际使用的索引名key_len索引字段使用了多少字节判断是否完全匹配rows优化器估算的扫描行数filtered经过条件过滤后剩余的比例3.2 驱动表选型为什么决定连接效率多表JOIN时慢查询最常见的问题不是缺索引而是驱动表选错。优化器会按评估成本选一个较小的结果集作为外层循环的驱动表然后用每一行的关联值去探测被驱动表的索引。这个思路是对的但统计信息不准确时优化器也会看走眼。我遇到过一个实测案例一个查询把100家商铺的近三个月订单聚合起来店铺表才100行订单表有600万行。正确做法应该是以店铺表为驱动表每行去订单表按shop_id探测总探测次数只有100次。但某次数据变更后统计信息失真优化器把订单表当成了驱动表先扫出全量订单再逐条去店铺表匹配耗时从200毫秒暴涨到7秒。遇到这类情况EXPLAIN里看清第一行的表是谁基本就能判断方向对不对。如果确认统计信息无误但优化器仍然选错可以在建模层面就这样写连接顺序但这属于应急手段上线前一定要复测。3.3 执行计划里“合理但不对劲”的陷阱还有一类情况最讨厌执行计划看起来每一步都合理总体却还是慢。通常是rows估算和实际严重不符。优化器依赖统计信息统计信息过期时它会做出“基于旧数据的最优决策”结果就是产生大量无效扫描。这时先跑一次ANALYZE TABLE刷新统计信息很多看起来无解的“幽灵慢查询”瞬间消失。如果ANALYZE TABLE之后执行计划变化了说明这条SQL本身没有问题问题出在统计信息自动更新的频率和数据变化幅度不匹配。4. 索引设计服从数据分布计算出来的不是拍脑袋加的4.1 区分度决定索引价值我见过很多人一上来就给所有WHERE字段都建索引结果索引占了几个GB查询还是慢。索引不是越多越好关键看区分度。区分度的计算很简单SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM orders;这个值越接近1说明该字段的重复值越少索引能排除掉的数据越多。像status这种只有几个固定取值的字段单独建索引几乎没有意义优化器看一眼区分度就知道扫描大部分行还不如直接全表扫。区分度高但不适合单独建索引的字段也很多比如订单号它适合等值查询却不适合范围查询和排序。真正有效的索引设计通常要结合等值和范围、排序需求一起看。4.2 复合索引列顺序为什么必须对应SQL前缀复合索引的列顺序不是想怎么排就怎么排它直接决定SQL能不能命中。索引(a, b, c)等价于三棵索引树(a)、(a,b)、(a,b,c)但就是不包含单独的(b)或者(c)。所以WHERE b ?走不了这个索引。排序优先级总结成一句话等值条件列放前面范围条件列放中间排序字段和分组字段放最后。比如查询需求是WHERE shop_id ? AND create_time ? ORDER BY id那么(shop_id, create_time, id)就是一个合理的组合。用EXPLAIN里的key_len可以验证索引到底吃到了哪几列。比如shop_id是整型占用4字节create_time是datetime占用5字节MySQL 5.6以后datetime占用5字节复合索引如果key_len9说明两列都用上了如果只有4说明第二列因为范围中断没吃到。4.3 覆盖索引与回表代价的取舍InnoDB的聚簇索引把整行数据都存在索引叶子节点上二级索引叶子节点存的则是对应的主键值。走二级索引查询时如果SELECT需要返回的列不在索引里每行都得回聚簇索引再查一次这个动作叫回表。覆盖索引的意思是让SELECT的所有列都包含在索引里查询只扫描索引页不需要回表。对于高频列表查询这是收益最明显的优化手段之一。比如SELECT order_id, status FROM orders WHERE shop_id 100;如果有一个(shop_id, status, order_id)索引这条SQL完全不需要回表。代价是插入和更新时要维护更多索引列写放大变高。所以我通常只在确实每天跑几十万次的高频查询上建覆盖索引从不为低频率的报表SQL盲目加列。5. 语句重写从“能跑”到“跑得对”5.1 深度分页翻车之后我做的延迟关联分页是慢查询的重灾区尤其当页码深到10万页之后。问题出在MySQL必须把offset加limit的之前所有行都扫出来再丢弃浪费得非常彻底。最常见的翻车写法是SELECT id, shop_id, user_id, amount FROM orders WHERE status 2 ORDER BY create_time DESC LIMIT 1000000, 10;这段SQL在排序阶段需要先找到第1000000行之前的全部符合条件记录再向后取10行。即使走了索引索引扫描也要跨过海量中间行。我通常会改成延迟关联先把主键取出来SELECT a.id, a.shop_id, a.user_id, a.amount FROM orders a JOIN ( SELECT id FROM orders WHERE status 2 ORDER BY create_time DESC, id DESC LIMIT 1000000, 10 ) tmp ON a.id tmp.id ORDER BY a.create_time DESC, a.id DESC;子查询只扫描索引列不碰数据页数量级上就减轻了回表压力等子查询拿到10个主键后再一次性回表取完整数据。这个过程实测通常能省掉80%以上的耗时。5.2 子查询展开和内连接怎么选子查询在旧版本优化器里容易产生“逐行执行”的糟糕计划特别是相关子查询外层每处理一行都要执行一次内层查询成本瞬间爆炸。比如SELECT name FROM users WHERE id IN (SELECT user_id FROM points WHERE points 5000);这里如果points表很大优化器又没有物化就会变成每个用户都去扫一遍points表。改写成JOIN之后优化器可以老老实实先把points表过滤成一个结果集再与users做连接。改写时要注意业务是否允许重复。IN天然去重JOIN不去重所以改写后要么加DISTINCT要么在连接前先对子查询去重。5.3 OR/IN/EXISTS背后的执行成本账很多朋友拿不准这三个关键字到底怎么选。我的判断依据是看分支能不能合并到同一个索引区间。WHERE status 1 OR status 2优化器可能把两个分支合并成范围OR访问性能尚可。但WHERE shop_id 1 OR user_id 2这种跨不同字段的OR如果shop_id和user_id在不同索引上优化器只能分别取索引结果合并代价翻倍。同样是跨字段查询改写UNION ALL往往更清晰SELECT * FROM orders WHERE shop_id 1 UNION ALL SELECT * FROM orders WHERE user_id 2前提是两个分支的结果集没有重叠或者业务允许重复。EXISTS则更适合小结果集驱动大表的场景外层结果很小内层只做存在性判断命中就返回不扫描完整数据。这个选择本质上是执行成本模型的选择没有固定的“标准答案”但有一条默认经验能用等值和范围描述就别用跨字段OR能用JOIN表达的关联就别用嵌套子查询逼优化器做选择。6. 防止回归把优化成果固化成工程护栏6.1 关键查询监控指标怎么定义调优做完不算完没有监控的优化结果会在下一次数据增长时悄悄退化。我建议至少盯住四个指标指标含义异常信号p99耗时最差的那1%请求有多慢连续三天上升慢查询计数超过阈值的查询数量突增两倍以上扫描行数/返回行数每一行有效结果消耗多少扫描比值超过100select_full_join没有索引可用的JOIN次数持续大于0这些指标可以通过performance_schema的语句摘要表拿到再定时导出到监控系统里画曲线。相比RDS自带控制台自建统计表的好处是可以把SQL指纹和版本发版时间关联起来一眼看出哪次变更引发的劣化。6.2 性能回归的自动化防线我所在的团队会把调优过的SQL做成一个回归清单每次发布前自动在标准数据集上跑一遍对比执行计划里的rows、type、耗时超过阈值就阻断发布。这个回归脚本不需要很复杂核心是三件事固定数据量、固定执行计划、固定阈值。数据量不一致会导致对比失真所以回归环境通常单独准备一份和生产规模相近的脱敏数据。高危SQL模式还要做成入库前的静态检查没有LIMIT的全表列表查询DELETE/UPDATE不带WHEREWHERE条件里对索引字段套函数比如WHERE DATE(create_time) 2024-01-01正确写法应该改成区间比较隐式转换导致索引失效比如字符串字段和整型比较6.3 给团队的那份标准作业流程最后把个人经验沉淀成团队能直接执行的标准作业流程核心分四步采集从慢日志和性能表里找高频高耗时SQL分析用EXPLAIN记录baseline确认访问路径优化先改统计信息再改索引最后才改SQL每次只改一个变量验证回归数据集重跑对比执行计划还要跑一次并发压测写规范文档时尽量用检查清单代替长篇大论。调整好一条SQL的结论必须带上“优化前执行计划→优化后执行计划→耗时对比→回滚方案”四件套缺少任何一项都不允许合入。我现在所有线上慢查询处理都走这套流程。每次复盘最值钱的不是那条SQL改对了而是把改对的过程装进了护栏让同样的问题不会再以另一个马甲回来。回头再看那个2.8秒的报表SQL真正救它的不是某个索引而是整套从定位到回归的方法。如果这篇文章能帮你少踩几个坑哪怕只省下一个通宵这五千多字就没白写。