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

前阵子接手了一个快到期的线上改造任务，业务方报障说某个查询页面打开越来越慢，从点击到看到结果经常要等五六秒，有时候直接超时。一开始我以为是接口代码问题，结果翻了一遍发现逻辑很简单，就是一次单表查询加一次列表遍历。后来开了慢查询日志才定位到真正的问题：某条SQL跑了接近5秒，扫描行数到了百万级别。这让我又一次意识到一个反复出现的真相——很多系统慢，根本不是服务器不够强，也不是代码写得烂，而是SQL在数据库里干了太多无用功。

这篇文章我就把SQL优化的完整思路写出来，从判断该不该优化、怎么取证定位，到索引失效排查、真实优化案例拆解，再到索引之外的手段和上线观测，尽量把踩过的坑和能用得上的细节都讲清楚。适合刚接手性能优化任务的开发同学，也适合那些被慢SQL折磨过、想系统性提升排查效率的同行。

1. 慢查询的定性判断：什么时候才需要动手优化SQL

很多团队一提性能优化就喊“加索引”，这其实是本末倒置。加索引只是手段之一，而且用错了地方不但没收益，还会拖慢写入。我一般拿到一条慢SQL，先不做任何改动，而是先回答三个问题：它真的慢吗？慢在哪一层？它对用户的影响有多大？这三个问题回答清楚了，再决定动不动手。

1.1 先搞清楚系统瓶颈落在哪一层

一条请求链路从浏览器到应用服务器，再到数据库，中间隔着网络、负载均衡、应用代码、连接池、缓存，最后才是SQL执行。慢的原因可能出现在任何一层。我见过最典型的例子，某团队花了两天优化SQL，把执行时间从200ms降到了2ms，结果接口还是慢，最后发现是应用里有个循环查库，一次请求触发了上百条SQL，单条再快也扛不住循环的次数。

所以拿到一条慢SQL，第一个动作是确认瓶颈确实在数据库这层。常规做法是：

1. 查看应用日志里的接口总耗时，对比数据库慢查询日志里对应SQL的执行时间。
2. 确认是否网络抖动、GC停顿或连接池排队导致整体延迟，而不是SQL本身执行慢。
3. 在测试环境单独执行同一条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，基本就能确定问题方向：要么没索引，要么有索引但被条件写法废掉了。

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是ALL，rows预估直接到了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：

执行时间从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优化说到底就是一个修炼基本功的过程，检索路径清晰、索引匹配合理、数据量可控，性能自然就差不到哪里去。