SQL优化实战：从慢查询定位到索引失效排查，一次讲透

前阵子接手了一个快到期的线上改造任务业务方报障说某个查询页面打开越来越慢从点击到看到结果经常要等五六秒有时候直接超时。一开始我以为是接口代码问题结果翻了一遍发现逻辑很简单就是一次单表查询加一次列表遍历。后来开了慢查询日志才定位到真正的问题某条SQL跑了接近5秒扫描行数到了百万级别。这让我又一次意识到一个反复出现的真相——很多系统慢根本不是服务器不够强也不是代码写得烂而是SQL在数据库里干了太多无用功。这篇文章我就把SQL优化的完整思路写出来从判断该不该优化、怎么取证定位到索引失效排查、真实优化案例拆解再到索引之外的手段和上线观测尽量把踩过的坑和能用得上的细节都讲清楚。适合刚接手性能优化任务的开发同学也适合那些被慢SQL折磨过、想系统性提升排查效率的同行。1. 慢查询的定性判断什么时候才需要动手优化SQL很多团队一提性能优化就喊“加索引”这其实是本末倒置。加索引只是手段之一而且用错了地方不但没收益还会拖慢写入。我一般拿到一条慢SQL先不做任何改动而是先回答三个问题它真的慢吗慢在哪一层它对用户的影响有多大这三个问题回答清楚了再决定动不动手。1.1 先搞清楚系统瓶颈落在哪一层一条请求链路从浏览器到应用服务器再到数据库中间隔着网络、负载均衡、应用代码、连接池、缓存最后才是SQL执行。慢的原因可能出现在任何一层。我见过最典型的例子某团队花了两天优化SQL把执行时间从200ms降到了2ms结果接口还是慢最后发现是应用里有个循环查库一次请求触发了上百条SQL单条再快也扛不住循环的次数。所以拿到一条慢SQL第一个动作是确认瓶颈确实在数据库这层。常规做法是查看应用日志里的接口总耗时对比数据库慢查询日志里对应SQL的执行时间。确认是否网络抖动、GC停顿或连接池排队导致整体延迟而不是SQL本身执行慢。在测试环境单独执行同一条SQL观察耗时是否稳定复现。如果单条SQL在数据库层面执行时间确实长才进入SQL层面的排查。否则应该去查代码、查缓存命中率、查下游依赖而不是闷头改SQL。1.2 性能基线与“可接受的慢”之间存在一条红线另一个需要提前统一认知的问题是什么叫慢不同业务容忍度完全不一样。核心交易接口要求毫秒级报表查询几秒钟可以接受后台导数据跑几分钟也属正常。所以不要拿着一条查询报表的SQL去跟交易链路的标准比也不要看某条SQL执行了500ms就急着改先看它的业务场景和调用频率。我常做的一套判断逻辑是高频低耗还是低频高耗。高频低耗的SQL比如每次请求都要查的主页数据接口执行时间超过100ms就值得怀疑低频高耗的SQL比如每日跑批的生产报表执行几秒甚至几十秒反而是常态。真正需要重点盯住的是执行频率高、单次耗时不低的SQL这类SQL叠加起来最容易被慢查询日志抓到也最能直接影响用户体验。还有一个容易被忽略的指标扫描行数与返回行数的比例。如果一条SQL返回100行却扫描了100万行说明索引和过滤条件大概率没配合好即使当前执行时间只有几百毫秒数据量翻倍后也会迅速恶化。这个比例是我判断健康度的核心依据之一也是后续优化是否见效的参照线。2. 排查链路从慢查询日志到执行计划的完整取证过程确定要动某条SQL之后不要凭感觉猜要按证据链走。完整排查链路大概是开慢查询日志抓到真实数据 → 提取具体SQL → 执行EXPLAIN看执行计划 → 在测试环境复现并计量时间 → 定位代价最大的节点 → 设计优化方案。这套链路能保证每次改动都有前后对比不会出现“改了一堆代码数据库更慢了”的情况。2.1 慢查询日志开关与阈值设置排查的第一步是拿到真实线上在执行的慢SQL而不是拿测试环境里构造出来的语句说事。以MySQL为例慢查询日志默认可能没开很多线上实例一上来就配了1秒阈值结果一些执行300ms但调用1万次的SQL根本留不下痕迹。我自己习惯把阈值先调低再观察白天高峰期抓半小时就够用了。几个常用的配置项-- 打开慢查询日志 SET GLOBAL slow_query_log ON; -- 单位秒建议线上先设为1高峰期可临时降到0.5 SET GLOBAL long_query_time 1; -- 记录没有使用索引的查询这套参数视版本而定 SET GLOBAL log_queries_not_using_indexes ON; -- 确认日志路径 SHOW VARIABLES LIKE slow_query_log_file;要特别提醒的一点是long_query_time的阈值是执行时间不包含等待锁的时间也不包含IO等待外部的排队。有些SQL在日志里看起来只有1秒多实际对接口的贡献时间可能远不止因为客户端等待连接、等待事务提交等时间都不算在内。所以慢查询日志只是起点不能完全替代应用链路追踪。2.2 explain执行计划的核心列怎么读拿到慢SQL后立刻执行EXPLAIN不要先去看索引存在不存在而是先看执行计划当前是怎么跑的。很多问题一眼就能从执行计划里看出来。下面是我日常最关注的几个列列名关注重点常见问题信号type访问类型ALL、index、range、ref、eq_ref、const依次变好ALL代表全表扫描是首要怀疑对象key实际选中的索引名明明建了索引却为NULL说明没被用上rows预估扫描行数执行时间跟rows高度正相关filtered存储引擎返回后经过条件过滤的比例比例低说明索引筛选力度弱Extra额外的执行信息Using filesort、Using temporary、Using where等关键词要重点关注type这一列最有说服力。ALL是灾难index是扫了整个二级索引range说明走了索引范围扫描ref是普通等值匹配eq_ref出现在连接查询中且每次只匹配一行const是主键或唯一索引的等值查找。我一眼扫过去只要看到ALL基本就能确定问题方向要么没索引要么有索引但被条件写法废掉了。Extra里出现Using filesort说明排序没走索引需要单独处理出现Using temporary说明查询过程用到了临时表往往是GROUP BY或DISTINCT引起的出现Using where代表存储引擎返回后还要进一步过滤配合rows列能看出过滤力度。如果你看到Using index反而是好消息这说明查询用到了覆盖索引不需要回表。2.3 复现与变量控制线上环境和数据分布是变化的执行计划的估算行数也不一定精确。我习惯把慢SQL原样拿出来在一个独立的测试库中复现测试库的数据量和数据分布尽量和线上保持同一个量级。复现时要控制变量一次只改一个因素要么加索引要么改SQL要么改表结构不能同时动多个。复现以后要记录两组基础数据作对比优化前的执行时间和扫描行数优化后的执行时间和扫描行数。注意执行时间这种东西跑第一次和跑第二次往往差距很大受缓存影响明显。我通常的做法是同一SQL交替执行5次去掉最高最低取中间值再拿中间值做对比。有时候一条SQL第一次跑是1秒第二次只剩50ms纯粹是因为数据页已经被加载到内存里了这时候拿第二次的数据说优化有效就太草率了。3. 索引失效与回表优化中最容易踩的隐藏雷区很多人优化SQL的第一步就是建索引这没错但真正难的地方不在于“建不建”而在于“建了之后数据库认不认”。索引是B树结构它的前提是能够按顺序比较和定位一旦查询条件里对索引列做了运算、函数处理、隐式类型转换索引就废了。这一节我把实际业务里遇到过的高频失效场景挨个说一遍。3.1 三种最典型的索引失效场景逐个分析第一个典型场景是对索引列使用函数或表达式。比如某个表上有创建时间create_time的索引但条件写成WHERE DATE(create_time) 2024-01-01索引就彻底没用了。数据库必须先把每一行的create_time都算一遍日期函数才能跟右边的常量比较B树的有序性在这里荡然无存。正确写法是算好边界写成WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00。第二个典型场景是隐式类型转换。比如订单表里order_no是字符串类型条件写成了WHERE order_no 202408150001注意右侧是整数某些数据库的规则会触发列上的隐式CAST相当于在列上套了一层函数索引同样失效。这个问题特别隐蔽因为普通数据量下感觉不出来一旦表到了千万级就变成全表扫描。验证方法很简单用EXPLAIN看type是不是变成了ALL或者看表的字段类型和传入的参数类型是否一致。第三个典型场景是前导模糊查询。WHERE name LIKE %关键词%这种写法的核心问题是条件没法定位到B树的具体范围只能全索引扫描甚至全表扫描。如果业务真的需要模糊搜索我建议要么改写成前缀匹配试试要么上专职的全文检索引擎而不是指望数据库在百万行里做包含匹配。当然前缀匹配LIKE 关键词%仍然可以走索引但要注意排序规则和字符集是否一致。3.2 回表、覆盖索引与联合索引的取舍即使索引被正常用上了还有一个被忽视的代价叫回表。二级索引的叶子节点只存了索引列和主键值查询需要的其他字段如果不在索引里就需要拿着主键再去主键索引里捞一次这就是回表。少量回表没感觉但当你明明可以用索引过滤掉99%的数据却因为要取字段而对剩余行逐个回表时性能还是会打折扣。解决回表最直接的手段是覆盖索引让查询需要的所有字段都出现在同一个索引里这样二级索引的叶子节点就够用了完全不需要回表。实际项目中我发现很多人喜欢给每个查询单独设计一个索引结果索引越来越多写放大越来越明显。合理做法是设计一两个覆盖核心查询的联合索引替代多个单列索引。联合索引还有个经典原则叫最左前缀。索引(user_id, order_time)可以支撑user_id等值查询、user_id order_time范围查询但如果直接只查order_time这个索引就帮不上忙。设计联合索引时要把等值条件放前面范围条件放后面因为等值条件下一个字段仍然可以利用索引的有序性做精细定位范围条件之后的所有字段就只能做手工过滤了。这里我还想多提一句不是索引建得越多越好。每多一个索引写入时就要多维护一棵B树导致INSERT和UPDATE变慢。我见过某线上库单表建了十几个索引查询没快到哪去反而把写入拖垮了。索引设计的核心思想是用尽量少的索引覆盖尽量多的高频查询路径。4. 一轮真刀真枪的优化实录从4800ms到38ms的拆解光讲理论容易飘。这里我复盘一个实际项目里的例子为了行文方便细节做了脱敏但排查过程和技术细节都是真实的。这个场景是模拟项目X里的订单查询功能用户反馈查询订单列表越来越慢测试环境无法稳定复现后来在线上慢查询日志里抓到了罪魁祸首。4.1 现场问题描述与初步取证业务方报障后我先看了应用监控和数据库慢查询日志发现有一条SQL的累计执行次数很高平均执行时间接近4.8秒。SQL大致长这样SELECT * FROM order_detail WHERE user_id 100223 AND order_time 2024-01-01 00:00:00 AND order_time 2024-02-01 00:00:00 ORDER BY create_time DESC LIMIT 20;这条SQL看起来逻辑很常规按用户和时间范围过滤按创建时间倒序只取20条。可问题恰恰出在“只取20条”上面。我先执行了EXPLAIN结果显示type是ALLrows预估直接到了150万Extra里还带着Using filesort。也就是说它把整个用户的历史数据全部翻出来排序后再取20条整个操作不谈索引设计连基本的下压过滤都没做到。当时第一反应是检查表上的索引。一查看表的索引是(user_id)单列索引而这条SQL里最核心的时间过滤字段order_time没和user_id组合起来。单靠(user_id)索引数据库确实能把记录缩小到某个用户但一个高频用户的历史订单可以有几万甚至几十万行这些行仍然全部进入排序流程。4.2 定位病根不是缺索引而是条件写法与结构的双重问题初步分析下来病根有两层。第一层是索引结构不合理(user_id)单列索引不支撑时间和排序条件第二层是SQL里ORDER BY create_time DESC和LIMIT 20的组合对索引要求很高最理想的情况是索引里已经包含排序字段让数据库直接按索引顺序取前20行而不是全量排序后再截断。这里我特意多验证了一件事把order_time条件去掉单独用user_id查执行计划会不会好一点结果是type上升到了ref但Extra仍然有Using filesort。这说明就算过滤条件走了索引排序还是老大难。所以方案必须同时解决索引覆盖排序字段的问题。很多人这时候会直接建一个(user_id, order_time, create_time)联合索引但我考虑到订单数据还在继续增长而且查询还会按更多字段筛选所以先把方案拆成两步第一步建一个(user_id, order_time, create_time)联合索引让过滤和排序都能下推到索引层第二步把SELECT *改成先查主键再回表取完整行避免一开始就拖出整行数据。4.3 优化组合落地下来的真实效果我先在测试库上模拟了相近的数据量建好联合索引后重新执行EXPLAIN项目优化前优化后typeALLrefkey无idx_user_time_createrows约150万约800ExtraUsing filesort无执行时间从4.8秒降到了38毫秒左右扫描行数从百万级别降到个位数百。这中间的差距本质上就是B树有没有被正确用起来的差别。为了稳定性我还对生产环境做了两步变更先在低峰期执行在线DDL工具创建索引避免锁表影响业务然后灰度发布观察应用侧接口耗时和数据库负载。整个变更持续了一个多小时期间没有任何写入阻塞最终接口P99从5秒掉到了120ms以内。这里我要专门提一个细节当时查询条件里的order_time和create_time是两个不同的时间字段语义也不完全一样。order_time是下单时间create_time是记录创建时间。把create_time放进联合索引用作排序前提是这两个时间在该场景下基本一致或者说业务上接受按创建时间排序。这个判断一定要跟业务确认否则索引建了排序顺序却不符合需求那就白忙一场。5. 手段分层索引之外还能动哪些手术索引确实是最常用的优化手段但它不是万能药。很多时候SQL写法本身有改进空间再往上还有表结构、缓存、架构分工可以做。我的习惯是从执行细节开始往后端延伸一级一级看能不动架构就不动架构但也不能只盯着执行计划。5.1 语句改写与执行计划引导先说SQL改写。最典型的优化点是深分页问题。很多后台分页查询写的是SELECT * FROM order_detail WHERE user_id 100223 ORDER BY create_time DESC LIMIT 100000, 20;这里的LIMIT 100000, 20意味着要扫描并且丢掉前10万行才能取出第100001到100020行。数据量一大这条SQL无论如何都快不了就算有索引数据库也得先扫出10万行再往后走。常见的优化手法是延迟关联把分页定位和结果取行拆开SELECT od.* FROM ( SELECT id FROM order_detail WHERE user_id 100223 ORDER BY create_time DESC LIMIT 100000, 20 ) t JOIN order_detail od ON od.id t.id;子查询里只查主键主键在二级索引里就能拿到这一层定位走索引就够快拿到20个主键后再跟原表做连接取完整行回表次数也被限制在了20次。实测下来深分页场景用这套写法可以稳定加速几十倍。另一个常见改写是子查询改JOIN。某些数据库版本对子查询的优化并不好尤其是IN (SELECT ...)这种结构优化器有时候会把它当成逐行执行相关子查询性能惨不忍睹。改成JOIN之后连接顺序和驱动表选择往往更可控。但要注意去重问题如果子查询结果可能有重复直接改JOIN会导致结果行数变多需要加DISTINCT或者先聚合。执行计划引导的手段不多比如FORCE INDEX、IGNORE INDEX但我不建议在正常代码里长期使用。数据库统计信息一变优化器对执行计划的选择也会变强制索引很可能把优化器绑死在一个不再合理的路径上。如果确实要兜底我会把这类SQL单独管理并且每次数据分布变化后都要重新评估。5.2 架构层缓解缓存、读写分离与数据归档如果SQL本身已经优化到执行计划很健康但接口压力就是大这时候要考虑的不再是单条SQL而是整个查询链路的压力。最直接的手段是引入缓存层把高频查询的结果缓存一段时间。这里要小心的是缓存一致性问题比如订单状态一直在变缓存过期时间设计得不好用户看到的可能就是旧数据。我的原则是业务上能容忍秒级延迟的数据才放缓存强实时数据别碰缓存。读写分离也是常见的扩展手段。把报表查询、后台导出等重查询引到只读副本上主库专注处理事务。但这套方案会增加运维成本副本延迟、一致性、故障切换都要处理。如果团队没有专门的DBA我建议慎用别把简单问题复杂化。还有一个经常被忽视的基础动作是数据归档。很多表的慢不是因为SQL写得差而是表里有大量永远查不到的历史数据。把三年以上的订单迁移到历史库线上表维持在稳定规模很多SQL不用优化也能自动变快。我见过某张统计表的扫描行数上亿但实际业务只查近三个月数据归档之后表只剩几千万行同样的SQL执行耗时从20秒降到了2秒以内。6. 上线前后的观测手段与几个压箱底的经验优化SQL不是改完代码就结束真正的验证在线上。很多人改完自己一测觉得快了就直接上线结果高峰一顶又打回原形甚至比原来更差。原因是测试环境和线上环境的数据分布、并发量、硬件规格完全不一样。所以我把线上观测和回归对比看得比改SQL本身还重。6.1 灰度对比与回滚预案每次SQL优化上线我会先做一次对比观测。优先选一个低峰时段把老版本的SQL和新版本的SQL各自独立跑一组离线查询记录执行时间、CPU消耗、IO消耗做成对比表。如果只有一两条SQL直接在数据库端分别执行看慢查询日志里的耗时记录就行如果改动涉及多个接口就走灰度发布比例让一小部分流量先走新逻辑和剩下的老逻辑对比接口耗时。灰度期间要看三件事接口P95/P99耗时、数据库相关等待事件、错误量和超时量。P95下降P99不降说明尾延迟可能还有问题错误量上升要立刻看是不是索引变更引起了锁等待或主从复制延迟。回滚预案也要提前想好我的习惯是索引变更先确认能在低峰期反向删除代码变更保留旧分支并提前准备好回滚发布任务这样一旦异常可以十分钟内恢复。6.2 优化之外写几条我实际沉淀下来的经验做SQL优化这几年踩过的坑也不少有几条经验几乎能套用到任何项目里。第一条经验上线前一定要看执行计划上线后一定要看慢查询日志。执行计划告诉你数据库打算怎么跑慢查询日志告诉你它实际跑了多久。很多SQL在测试库怎么跑都快一上生产就慢差别就在数据分布。如果你有测试库务必让它的数据量和分布接近生产否则优化就是盲人摸象。第二条经验不要为了优化而优化。有些SQL确实是慢但一个月只跑一次每次跑3分钟完全在可接受范围内你花一整天去优化它不如去处理一条每秒钟执行一次却耗时200ms的SQL。价值计算公式很简单优化收益等于执行频率乘以单次节省的时间。先算账再动手。第三条经验警惕宁滥勿缺的索引设计。每多一个索引写入就多一分负担。我看到很多表上的索引利用率很低有些索引建了几个月都没有被优化器选中过一次。排查索引利用率的方法也不难可以看数据库统计信息里各索引的使用频率把半年以上没被用过的索引清掉写入性能往往能恢复不少。第四条经验SQL优化不是一锤子买卖。业务在变数据在涨统计信息在变今天健康的执行计划三个月后可能就退化。我每周会抽半小时扫一遍慢查询日志里新出现的SQL把超过阈值的新面孔加进观察清单。优化是持续运营的过程而不是一次性的救火。最后再分享一个比较实际的小技巧我习惯在所有新模块的开发规范里强制要求编写SQL后自查EXPLAIN并且把rows预估行数控制在一个明确的量级内。这个习惯的收益不是立刻能看到的但几个月后回头一看线上新增的慢SQL数量会明显比其他模块少得多系统也能在更长周期里保持稳定。SQL优化说到底就是一个修炼基本功的过程检索路径清晰、索引匹配合理、数据量可控性能自然就差不到哪里去。