接手过系统性能优化的开发都知道一谈到系统变慢了十有八九问题出在数据库而出在数据库的问题里又有七八成都能归结到SQL写得不够好。不管是接口响应时间超时、CPU飙高、IO打满还是半夜某个定时任务把主库拖垮顺着调用链一层层剥下去最后基本都会指向某一条或某几条糟糕的SQL。这个项目叫作系统优化之SQL优化名字很直白但背后要聊的东西远不止给表加个索引这么简单。它涵盖了一个完整系统从出现性能问题、定位瓶颈、分析执行计划、到改写SQL和调整索引的整个闭环过程。适合谁看如果你是刚接触数据库优化的后端开发、运维或DBA这篇文章能帮你建立一套相对完整的SQL优化方法论哪怕你已经做过一些优化工作里面排查问题和避坑的思路也可能给你一些新启发。整个优化过程中我遇到过不少印象深刻的案例一条看似简单却慢到离谱的查询、一个用了很久的索引失效、一晚上CPU直接拉满的批量更新语句。这些经验单独拿出来看都很有代表性这里我把它们整理成一套比较系统的操作流程从底层原理到实操步骤尽量说清楚。1. 整体设计思路SQL优化到底在优化什么首先得明确一件事SQL优化的本质不是把SQL写得看起来更短也不是把执行计划调成某个特定形状而是减少数据库在单位时间内需要处理的数据量以及降低每条语句的资源消耗成本。1.1 先搞清楚慢SQL的资源消耗在哪里一条SQL执行慢通常逃不出这几个维度CPU开销数据库需要对大量数据进行排序、分组、计算、类型转换等操作。数据量越大CPU开销越不成比例地增长。IO开销需要扫描的数据页太多或者频繁的随机IO导致磁盘响应延迟明显。锁竞争与阻塞行锁、表锁互相等待造成查询排队吞吐量急剧下降。网络传输返回给应用层的数据量过大白白消耗带宽和应用服务器内存。很多人在优化时有一个误区上来就盯着SQL本身改写却忽略了这条SQL为什么会产生这么大的资源消耗这个根本问题。实际上资源消耗往往由两个因素决定——表和索引的结构设计以及SQL的写法。这两者是相互影响的甚至有时候表结构设计的影响比SQL写法更大。举个例子我之前处理过某订单查询接口的慢问题。从应用层看接口平均耗时3秒多。排查后发现SQL长这样SELECT * FROM order_info WHERE user_id 123456 AND status IN (1, 2, 3) ORDER BY create_time DESC LIMIT 20;看上去没毛病吧等我看完表结构才发现order_info表当时已经有接近3800万行数据而user_id上虽然有索引但status和create_time都没有索引。这就导致数据库需要先把某个用户的所有订单都找出来然后进行一次大范围的ORDER BY create_time排序最后才取20条。如果这个用户历史订单非常多临时排序的开销会非常可观。所以SQL优化的第一步永远不是改SQL本身而是完整收集现状数据弄清楚这条SQL实际扫描了多少行、经历了哪些操作、每个步骤花了多少时间。1.2 优化的目标顺序先求快再求稳后求省做系统优化跟看病一样也得讲究顺序。我一般把优化目标分成三层第一层快速止血。让业务先恢复正常该加索引加索引该改写改写解决眼前的核心痛点。第二层稳定运行。避免因为数据量增长或业务变化导致SQL再次从快变成慢。第三层资源节省。在保证速度的前提下尽可能减少对数据库CPU、内存、IO的消耗给其他业务留出余量。这三层目标越靠前越紧急但也越短期。很多项目只做了第一层加完索引发现变快了就觉得优化结束了。结果过了几个月数据量涨一波问题再次爆发又得重新排查。一次合格的SQL优化应该在方案设计时就考虑到未来一年甚至更长时间的数据增长速度。1.3 前置条件你需要一套可靠的分析工具做SQL优化不能靠猜。我常用的工具和手段有这么几样工具/手段用途备注慢查询日志发现哪些SQL耗时长需要评估开启阈值避免日志量过大EXPLAIN查看语句的执行计划最核心的分析手段下文会展开讲PROFILES/PERFORMANCE_SCHEMA查看语句各阶段耗时用于定位具体瓶颈阶段数据库监控平台观察锁等待、IO、CPU趋势用于关联时间点与异常事件压测工具验证优化效果一般用本地脚本或压测平台需要注意的是很多开发同学习惯于工作中遇到慢SQL开个EXPLAIN看一看哦这里有全表扫描加个索引结束。但真实生产环境往往没有这么理想——有时加了索引也不走有时走了索引还是慢有时并发一高就出现锁等待。没有完整的数据支撑就不要轻易下结论。2. 核心细节解析执行计划怎么读才有效2.1 执行计划的核心关注点EXPLAIN的输出是SQL优化必须掌握的基本功。但光会看有没有type ALL或key NULL远远不够。我分享一个自己的基本判断逻辑按优先级来看type访问类型。从好到差大致是const eq_ref ref range index ALL。ALL代表全表扫描基本是优化信号里最醒目的一个。key实际使用的索引。key为NULL说明没走索引需要进一步确认原因。rows预估扫描行数。这个值是估算的但能直观反映这条SQL的数据量级。比如同一个查询rows是100和rows是1000000优化的优先级完全不同。Extra额外信息。这里面藏了很多关键的危险信号。比如Using filesort、Using temporary、Using index condition、Using where等每一种信息背后都要对应不同的优化动作。特别值得说的是Using filesort。很多新手看到filesort以为数据库在磁盘上做了排序其实它只是表示没有用到索引排序需要另起排序操作。如果排序的数据量很大这个操作就会非常昂贵。优化方向通常是让ORDER BY的字段能利用上联合索引的有序性或者减少参与排序的数据行数。2.2 从执行计划反推索引设计我习惯的做法是先看执行计划暴露的问题再反推什么样的索引设计能让这条SQL形成最优执行计划。假设我们有一个查询场景SELECT id, name, amount FROM trade_record WHERE merchant_id 20241201 AND status 3 ORDER BY create_time DESC LIMIT 20;如果表上只有merchant_id的单列索引执行计划很可能是通过索引定位该商户的所有记录回表过滤status 3再排序。如果该商户数据量很大回表和排序的成本都不低。这种情况下联合索引(merchant_id, status, create_time)往往能起到明显效果——前两列用于等值过滤第三列利用索引天然有序的性质直接避免排序。但要注意联合索引的设计不是随便加几个字段就行的这里有几个原则等值条件字段放前面用于等值匹配的字段放在联合索引前面最大程度缩减范围。排序字段跟在后面利用索引的有序性让ORDER BY不走额外的排序。考虑覆盖索引如果查询的字段全部都在索引里那么连回表都不需要性能会有量级提升。2.3 一个实际操作中的反例有一次我优化一批报表查询其中有条SQL涉及三张表Join外表120万行内表最大的有2000多万行。原SQL长这样SELECT a.id, b.name, c.amount FROM base_table a LEFT JOIN detail_table b ON a.code b.code LEFT JOIN pay_table c ON a.code c.code WHERE a.batch_date 2024-01-01 AND a.enabled 1执行计划里两张内表都是全表扫描。当时的第一反应是给两边的code字段加索引加上以后发现扫描策略变成了索引扫描但整体耗时还是高因为驱动表base_table本身行数不低每扫描一行都要对两张内表做一次索引查找。后来分析发现该业务场景其实可以先按batch_date缩小驱动表范围原本SQL的WHERE条件虽然写在base_table上但优化器对Join顺序的调整能力有限手动改用子查询先过滤驱动表或者直接调整驱动表的过滤条件效果立刻好了很多。这个案例告诉我Join优化不能只看内表的索引驱动表的数据量也一样重要。SQL优化的核心思路永远是最大程度减少每一条路径上的数据处理量。3. 实操过程一个完整SQL优化案例的记录现在分享一个我自己最近处理的真实优化案例整个操作过程基本代表了SQL优化日常工作的完整路径。3.1 发现问题从监控告警切入那是一个典型的业务高峰期某服务接口的P99延迟开始明显上涨从原来的120ms涨到了900ms以上。数据库监控上看到某台实例的CPU使用率在晚高峰达到90%磁盘IO等待也同步飙升。通过慢查询日志定位到一条核心SQL跑一次平均耗时2.5秒高峰时段更是超过5秒。这条SQL和业务有关查询当前时间往前推30天内某用户在某个渠道、某个状态下的记录列表。核心语句简化如下SELECT id, order_no, goods_id, amount, create_time, remark FROM user_order WHERE user_id ? AND channel ? AND status IN (?, ?, ?, ?) AND create_time DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY create_time DESC LIMIT 20 OFFSET 0;这条SQL本身看起来并不复杂表上其实也有(user_id, channel)的联合索引。但问题在于历史数据量大单用户的订单量又比较多每次执行都要过滤大量数据。3.2 第一轮分析用执行计划定位痛点对这条SQL执行EXPLAIN后结果关键部分如下type refkey idx_user_channelrows 184632Extra Using index condition; Using where; Using filesort看到这个结果我判断有几个核心痛点索引能过滤user_id和channel但status和create_time的过滤是在索引拿到结果后回表再做的所以rows高达18万。同时ORDER BY create_time DESC无法利用索引的有序性触发了Using filesort排序的开销进一步放大。这里有个关键知识点索引的等值匹配和范围匹配是有严格顺序的。create_time xxx是一个范围条件当它作为联合索引的一部分时它后面的字段都无法继续用于过滤。所以即使我把create_time加进联合索引一旦它前面的条件已经用范围判断后续的字段也就用不上了。3.3 方案设计重构索引 拆分查询条件为了让这条SQL既能过滤status又能利用create_time的有序性我把索引重新设计为ALTER TABLE user_order ADD INDEX idx_user_channel_status_time (user_id, channel, status, create_time);字段顺序的原则是user_id和channel是等值条件放前面status是IN列表虽然是范围匹配但在某些版本中优化器可以将其中的值分别匹配所以放到等值条件之后、排序字段之前往往也有一定过滤效果这里我先按这种方式设计create_time放在最后既用于时间范围过滤又同时让ORDER BY create_time DESC尽量能利用索引有序性。听完可能有人要问status IN (?, ?, ?, ?)也算范围为什么不是放在create_time后面这个问题的答案在于对于联合索引来说IN列表会被转换为多个等值条件处理只要列表数量可控把它放在create_time前所带来的过滤收益通常比放在后面更大。当然如果IN列表特别长那就要重新评估了因为每个值都会引发一次索引范围扫描太多的话反而可能劣化。3.4 优化效果验证索引加好后我重新执行EXPLAINtype rangekey idx_user_channel_status_timerows 4821Extra Using index condition; Using whereUsing filesort消失对比很明显预估扫描行数从18万降到不到5000而且不再有额外的排序操作。实际回归测试中这条SQL的平均耗时从2.5秒降到220毫秒左右高峰时段也能稳定在300毫秒内。整个改动的动作并不大——只是调整了索引设计SQL本身几乎没有改动。但这个案例很有代表性因为它揭示了SQL优化里一个不太容易注意的细节面对一条看起来还行的SQL关键瓶颈往往隐藏在索引匹配方式和排序策略上。3.5 同类场景中的衍生问题在完成主SQL优化后我又针对同类场景做了几项延伸处理把OFFSET分页改成了键集分页keyset pagination避免深翻页时的巨大扫描开销。对该业务接口做了结果集缓存减少数据库重复计算。针对不同渠道、不同状态的组合重新评估索引使用频率避免索引过多影响写入性能。这里特别想提一下键集分页传统的LIMIT 10000, 20在数据量变大后需要扫描并丢弃前10000行效率极低。键集分页是通过记住了上一页最后一条记录的create_time和id下次查询直接把这个值作为条件让扫描直接从正确位置开始。改动并不复杂但能让分页查询的数据扫描量保持恒定是非常值得推荐的做法。4. 常见问题与排查技巧实录日常的SQL优化工作中有一些问题是反复出现的这里我整理成一份速查表算是这么多年来踩坑换来的心得。4.1 高频问题速查表现象可能原因排查方向优化建议明明有索引却不走索引列参与了运算或函数操作查看WHERE条件中的列是否存在DATE_FORMAT(create_time, %Y)这类写法改成条件列的原始范围比较走了索引还是很慢回表次数过多看执行计划中的Extra是否大量出现Using index condition对比回表行数和最终结果行数改成覆盖索引把查询列纳入索引ORDER BY触发排序索引顺序与排序方向不一致检查联合索引是否包含排序字段调整索引字段顺序尽量让排序走索引深分页查询开销巨大LIMIT的偏移量过大观察分页页数变大后耗时线性增长改成键集分页或缓存前一页的最后ID表数据量大但统计信息不准确优化器选择了错误执行计划查看表的统计信息更新时间针对大表定期执行统计信息更新并发高时锁等待严重多事务同时更新同一批行查看监控中的锁等待事件重写业务逻辑减少冲突窗口隐式类型转换字段字符集不一致或类型不一致查看WHERE条件中列类型是否与传入参数一致统一字段类型杜绝隐式转换多表Join顺序不佳驱动表数据量过大查看执行计划中的驱动表和被驱动表使用STRAIGHT_JOIN仅限确认时或改写子查询4.2 一个典型的隐式转换坑有一次某同事让我帮忙看一条SQL执行计划显示主键索引都没走。SQL条件是这样的SELECT * FROM user_account WHERE mobile 13800138000表面上看没问题但mobile字段在表里是varchar(20)传给它的却是一个整数。数据库在比较时会将字段隐式转换为数字导致索引失效。改法很简单把参数改为字符串即可。这类问题在生产环境非常普遍特别是从外部接口传入参数时类型一不小心就变了。排查这类问题时我一般不会只盯EXPLAIN也会顺带查一下表结构DDL看看字段类型和字符集。曾经还遇到过两张表Join时字符集一个utf8mb4一个utf8导致Join列无法使用索引的情况这种问题很难一眼看出来需要非常细致地逐项排查。4.3 关于添加索引的副作用做SQL优化一定要有个意识索引不是越多越好更不是越全能越好。每增加一个索引都会带来写放大——插入、更新、删除时都需要同步维护索引结构。索引过多可能让一条批量更新语句的性能急剧下降甚至引发存储空间暴涨。我给自己的一个参考标准是单表的索引数量尽量控制在5个以内超过这个数量就要认真评估是否值得。尤其是联合索引字段越多占用空间越大。业务上很多需求是可以通过复用现有索引的最左前缀来满足的不必每个查询都建一个专属索引。4.4 生产环境操作的节奏与安全生产库上的索引变更不管看起来多小都建议遵循这样的节奏先在预发或灰度环境执行同样的变更用压测或线上读流量验证效果。评估加索引时对写入性能的影响特别是大表加索引耗时可能很长期间会导致阻塞。维护窗口期执行并且要有回滚方案。加错索引最多浪费点存储但如果是删旧索引加新索引顺序和时机都要格外小心。变更完成后利用慢查询日志和监控持续观察至少24小时确认没有新的计划问题。有一次在线上给一张5000万行的表加索引因为没选好窗口期导致锁表时间过长业务写入出现短暂阻塞。那次之后我把在任何线上环境做索引变更前先评估表大小、并发情况、变更时长写成了一个固定检查项。5. 这次优化项目里的一些深入思考SQL优化做到后面拼的已经不只是会不会写EXPLAIN和加索引了而是对整个数据库运行机制的理解深度。这里分享几个这次项目中引发的延伸思考。5.1 数据分布与优化器判断的错位很多时候一个SQL方案在测试环境表现很好一到生产环境就走样。根本原因在于测试环境的数据分布和生产环境完全不在一个量级。优化器选择执行计划依赖的是表的统计信息如果统计信息不准确或者因为数据倾斜导致某个字段的值的分布极不均匀优化器完全可能做出错误选择。比如status字段业务上有99%的数据是1这个状态只有1%是其他状态。如果查询条件里status1那即便这条查询走了索引最终也要扫过大量数据才能拿到结果。这种情况下优化器可能会觉得全表扫描反而比走索引更快于是执行计划就变成了ALL。这是走了索引还是慢的一个隐藏原因。碰到这种问题一般要从业务拆分的角度思考比如把历史数据归档或者依据状态字段做分区表而不是和优化器硬刚。5.2 没有银弹每种方案都有适用边界这几年我也接触过不少号称万能优化的方案比如强制走某个索引、改写SQL使用STRAIGHT_JOIN、甚至直接在SQL里加HINT。说实话这些手段都有效但都是特定场景下的手术刀不是常规武器。滥用HINT尤其危险——它会让优化器丧失自主判断能力一旦数据量级变化性能可能瞬间崩盘。我通常建议的做法是优先通过调整索引、改写SQL结构来解决问题把HINT留给极少数确有必要且经过充分验证的场景。让优化器自然选择最优路径长期看是更稳定的方案。5.3 优化之后的可观测性建设老实说每次优化完一条SQL我都觉得事情只做了一半。另一半是如何让下一次出现类似问题时我们能更快发现和定位。在这次项目的后续阶段我给核心链路补充了这几样东西慢查询阈值的定期review把过去三个月变慢的SQL重新拉出来复盘。把关键SQL的执行计划变化纳入监控当rows或type出现较大变化时主动告警。对核心业务表的数据量、索引使用情况做周期性统计提前预判风险。这个思路的核心是把一次性的SQL优化变成常态化的性能治理。系统的数据在涨业务在变今天的最优方案可能就是明天的性能瓶颈来源。只有具备了快速发现和定位问题的能力SQL优化这个事才算真正落地。6. 项目收尾复盘核心经验总结这个SQL优化项目做下来我给自己的工作复盘列了几条对自己后续最有用的经验分享在这里。6.1 把时间花在最重要的SQL上不是所有慢SQL都值得花大力气优化。一条每天只执行几次且不在核心链路上的慢SQL优先级远低于一条每天执行几百万次的次优SQL。我一般排序方法是执行频率 × 单次执行成本 优化收益。按照这个公式排序能把有限的优化精力放在最有价值的地方。实际项目里一条高峰期间每秒执行数十次的SQL哪怕优化20%的性能效果都远大于把一条凌晨报表SQL从2小时优化到10分钟。6.2 文档记录比优化本身更重要每做完一轮SQL优化我都会把完整的排查过程和方案记录归档包括原始SQL、执行计划、改动内容、效果对比、数据量变化等。这么做有三个好处一是后续出问题能快速回溯二是可以提炼共性问题沉淀成团队的SQL规范三是新人接手时不用从零摸索。可惜很多人没有这个习惯改完索引就完事等几个月后问题重现自己都记不清当时改了什么。6.3 技术之外与业务的沟通同样重要SQL优化的最后一道坎往往不是技术而是业务理解。有时候SQL慢是因为业务需求本身太过复杂比如一次交互要查询大量历史数据、接口要返回几百个字段。这时候单纯从SQL层面优化空间已经不大更好的路径是和产品沟通调整需求或改造数据模型。比如把部分实时计算改成预计算把联机查询和历史分析分离或者引入缓存组件——这些都不是SQL优化本身但都能从根本上降低对数据库的压力。从这个角度看SQL优化更是一项系统级的工作SQL只是最终的表现层而背后的表结构设计、索引策略、业务需求、数据生命周期每一层都有可以施展的空间。把视野拉高你的优化方案才不会只是一次治标的修补。 
长河君 · 2026/10/20 14:54:10 · 阅读 1.2k SQL优化实战：从执行计划到索引设计的完整方法论
接手过系统性能优化的开发都知道一谈到系统变慢了十有八九问题出在数据库而出在数据库的问题里又有七八成都能归结到SQL写得不够好。不管是接口响应时间超时、CPU飙高、IO打满还是半夜某个定时任务把主库拖垮顺着调用链一层层剥下去最后基本都会指向某一条或某几条糟糕的SQL。这个项目叫作系统优化之SQL优化名字很直白但背后要聊的东西远不止给表加个索引这么简单。它涵盖了一个完整系统从出现性能问题、定位瓶颈、分析执行计划、到改写SQL和调整索引的整个闭环过程。适合谁看如果你是刚接触数据库优化的后端开发、运维或DBA这篇文章能帮你建立一套相对完整的SQL优化方法论哪怕你已经做过一些优化工作里面排查问题和避坑的思路也可能给你一些新启发。整个优化过程中我遇到过不少印象深刻的案例一条看似简单却慢到离谱的查询、一个用了很久的索引失效、一晚上CPU直接拉满的批量更新语句。这些经验单独拿出来看都很有代表性这里我把它们整理成一套比较系统的操作流程从底层原理到实操步骤尽量说清楚。1. 整体设计思路SQL优化到底在优化什么首先得明确一件事SQL优化的本质不是把SQL写得看起来更短也不是把执行计划调成某个特定形状而是减少数据库在单位时间内需要处理的数据量以及降低每条语句的资源消耗成本。1.1 先搞清楚慢SQL的资源消耗在哪里一条SQL执行慢通常逃不出这几个维度CPU开销数据库需要对大量数据进行排序、分组、计算、类型转换等操作。数据量越大CPU开销越不成比例地增长。IO开销需要扫描的数据页太多或者频繁的随机IO导致磁盘响应延迟明显。锁竞争与阻塞行锁、表锁互相等待造成查询排队吞吐量急剧下降。网络传输返回给应用层的数据量过大白白消耗带宽和应用服务器内存。很多人在优化时有一个误区上来就盯着SQL本身改写却忽略了这条SQL为什么会产生这么大的资源消耗这个根本问题。实际上资源消耗往往由两个因素决定——表和索引的结构设计以及SQL的写法。这两者是相互影响的甚至有时候表结构设计的影响比SQL写法更大。举个例子我之前处理过某订单查询接口的慢问题。从应用层看接口平均耗时3秒多。排查后发现SQL长这样SELECT * FROM order_info WHERE user_id 123456 AND status IN (1, 2, 3) ORDER BY create_time DESC LIMIT 20;看上去没毛病吧等我看完表结构才发现order_info表当时已经有接近3800万行数据而user_id上虽然有索引但status和create_time都没有索引。这就导致数据库需要先把某个用户的所有订单都找出来然后进行一次大范围的ORDER BY create_time排序最后才取20条。如果这个用户历史订单非常多临时排序的开销会非常可观。所以SQL优化的第一步永远不是改SQL本身而是完整收集现状数据弄清楚这条SQL实际扫描了多少行、经历了哪些操作、每个步骤花了多少时间。1.2 优化的目标顺序先求快再求稳后求省做系统优化跟看病一样也得讲究顺序。我一般把优化目标分成三层第一层快速止血。让业务先恢复正常该加索引加索引该改写改写解决眼前的核心痛点。第二层稳定运行。避免因为数据量增长或业务变化导致SQL再次从快变成慢。第三层资源节省。在保证速度的前提下尽可能减少对数据库CPU、内存、IO的消耗给其他业务留出余量。这三层目标越靠前越紧急但也越短期。很多项目只做了第一层加完索引发现变快了就觉得优化结束了。结果过了几个月数据量涨一波问题再次爆发又得重新排查。一次合格的SQL优化应该在方案设计时就考虑到未来一年甚至更长时间的数据增长速度。1.3 前置条件你需要一套可靠的分析工具做SQL优化不能靠猜。我常用的工具和手段有这么几样工具/手段用途备注慢查询日志发现哪些SQL耗时长需要评估开启阈值避免日志量过大EXPLAIN查看语句的执行计划最核心的分析手段下文会展开讲PROFILES/PERFORMANCE_SCHEMA查看语句各阶段耗时用于定位具体瓶颈阶段数据库监控平台观察锁等待、IO、CPU趋势用于关联时间点与异常事件压测工具验证优化效果一般用本地脚本或压测平台需要注意的是很多开发同学习惯于工作中遇到慢SQL开个EXPLAIN看一看哦这里有全表扫描加个索引结束。但真实生产环境往往没有这么理想——有时加了索引也不走有时走了索引还是慢有时并发一高就出现锁等待。没有完整的数据支撑就不要轻易下结论。2. 核心细节解析执行计划怎么读才有效2.1 执行计划的核心关注点EXPLAIN的输出是SQL优化必须掌握的基本功。但光会看有没有type ALL或key NULL远远不够。我分享一个自己的基本判断逻辑按优先级来看type访问类型。从好到差大致是const eq_ref ref range index ALL。ALL代表全表扫描基本是优化信号里最醒目的一个。key实际使用的索引。key为NULL说明没走索引需要进一步确认原因。rows预估扫描行数。这个值是估算的但能直观反映这条SQL的数据量级。比如同一个查询rows是100和rows是1000000优化的优先级完全不同。Extra额外信息。这里面藏了很多关键的危险信号。比如Using filesort、Using temporary、Using index condition、Using where等每一种信息背后都要对应不同的优化动作。特别值得说的是Using filesort。很多新手看到filesort以为数据库在磁盘上做了排序其实它只是表示没有用到索引排序需要另起排序操作。如果排序的数据量很大这个操作就会非常昂贵。优化方向通常是让ORDER BY的字段能利用上联合索引的有序性或者减少参与排序的数据行数。2.2 从执行计划反推索引设计我习惯的做法是先看执行计划暴露的问题再反推什么样的索引设计能让这条SQL形成最优执行计划。假设我们有一个查询场景SELECT id, name, amount FROM trade_record WHERE merchant_id 20241201 AND status 3 ORDER BY create_time DESC LIMIT 20;如果表上只有merchant_id的单列索引执行计划很可能是通过索引定位该商户的所有记录回表过滤status 3再排序。如果该商户数据量很大回表和排序的成本都不低。这种情况下联合索引(merchant_id, status, create_time)往往能起到明显效果——前两列用于等值过滤第三列利用索引天然有序的性质直接避免排序。但要注意联合索引的设计不是随便加几个字段就行的这里有几个原则等值条件字段放前面用于等值匹配的字段放在联合索引前面最大程度缩减范围。排序字段跟在后面利用索引的有序性让ORDER BY不走额外的排序。考虑覆盖索引如果查询的字段全部都在索引里那么连回表都不需要性能会有量级提升。2.3 一个实际操作中的反例有一次我优化一批报表查询其中有条SQL涉及三张表Join外表120万行内表最大的有2000多万行。原SQL长这样SELECT a.id, b.name, c.amount FROM base_table a LEFT JOIN detail_table b ON a.code b.code LEFT JOIN pay_table c ON a.code c.code WHERE a.batch_date 2024-01-01 AND a.enabled 1执行计划里两张内表都是全表扫描。当时的第一反应是给两边的code字段加索引加上以后发现扫描策略变成了索引扫描但整体耗时还是高因为驱动表base_table本身行数不低每扫描一行都要对两张内表做一次索引查找。后来分析发现该业务场景其实可以先按batch_date缩小驱动表范围原本SQL的WHERE条件虽然写在base_table上但优化器对Join顺序的调整能力有限手动改用子查询先过滤驱动表或者直接调整驱动表的过滤条件效果立刻好了很多。这个案例告诉我Join优化不能只看内表的索引驱动表的数据量也一样重要。SQL优化的核心思路永远是最大程度减少每一条路径上的数据处理量。3. 实操过程一个完整SQL优化案例的记录现在分享一个我自己最近处理的真实优化案例整个操作过程基本代表了SQL优化日常工作的完整路径。3.1 发现问题从监控告警切入那是一个典型的业务高峰期某服务接口的P99延迟开始明显上涨从原来的120ms涨到了900ms以上。数据库监控上看到某台实例的CPU使用率在晚高峰达到90%磁盘IO等待也同步飙升。通过慢查询日志定位到一条核心SQL跑一次平均耗时2.5秒高峰时段更是超过5秒。这条SQL和业务有关查询当前时间往前推30天内某用户在某个渠道、某个状态下的记录列表。核心语句简化如下SELECT id, order_no, goods_id, amount, create_time, remark FROM user_order WHERE user_id ? AND channel ? AND status IN (?, ?, ?, ?) AND create_time DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY create_time DESC LIMIT 20 OFFSET 0;这条SQL本身看起来并不复杂表上其实也有(user_id, channel)的联合索引。但问题在于历史数据量大单用户的订单量又比较多每次执行都要过滤大量数据。3.2 第一轮分析用执行计划定位痛点对这条SQL执行EXPLAIN后结果关键部分如下type refkey idx_user_channelrows 184632Extra Using index condition; Using where; Using filesort看到这个结果我判断有几个核心痛点索引能过滤user_id和channel但status和create_time的过滤是在索引拿到结果后回表再做的所以rows高达18万。同时ORDER BY create_time DESC无法利用索引的有序性触发了Using filesort排序的开销进一步放大。这里有个关键知识点索引的等值匹配和范围匹配是有严格顺序的。create_time xxx是一个范围条件当它作为联合索引的一部分时它后面的字段都无法继续用于过滤。所以即使我把create_time加进联合索引一旦它前面的条件已经用范围判断后续的字段也就用不上了。3.3 方案设计重构索引 拆分查询条件为了让这条SQL既能过滤status又能利用create_time的有序性我把索引重新设计为ALTER TABLE user_order ADD INDEX idx_user_channel_status_time (user_id, channel, status, create_time);字段顺序的原则是user_id和channel是等值条件放前面status是IN列表虽然是范围匹配但在某些版本中优化器可以将其中的值分别匹配所以放到等值条件之后、排序字段之前往往也有一定过滤效果这里我先按这种方式设计create_time放在最后既用于时间范围过滤又同时让ORDER BY create_time DESC尽量能利用索引有序性。听完可能有人要问status IN (?, ?, ?, ?)也算范围为什么不是放在create_time后面这个问题的答案在于对于联合索引来说IN列表会被转换为多个等值条件处理只要列表数量可控把它放在create_time前所带来的过滤收益通常比放在后面更大。当然如果IN列表特别长那就要重新评估了因为每个值都会引发一次索引范围扫描太多的话反而可能劣化。3.4 优化效果验证索引加好后我重新执行EXPLAINtype rangekey idx_user_channel_status_timerows 4821Extra Using index condition; Using whereUsing filesort消失对比很明显预估扫描行数从18万降到不到5000而且不再有额外的排序操作。实际回归测试中这条SQL的平均耗时从2.5秒降到220毫秒左右高峰时段也能稳定在300毫秒内。整个改动的动作并不大——只是调整了索引设计SQL本身几乎没有改动。但这个案例很有代表性因为它揭示了SQL优化里一个不太容易注意的细节面对一条看起来还行的SQL关键瓶颈往往隐藏在索引匹配方式和排序策略上。3.5 同类场景中的衍生问题在完成主SQL优化后我又针对同类场景做了几项延伸处理把OFFSET分页改成了键集分页keyset pagination避免深翻页时的巨大扫描开销。对该业务接口做了结果集缓存减少数据库重复计算。针对不同渠道、不同状态的组合重新评估索引使用频率避免索引过多影响写入性能。这里特别想提一下键集分页传统的LIMIT 10000, 20在数据量变大后需要扫描并丢弃前10000行效率极低。键集分页是通过记住了上一页最后一条记录的create_time和id下次查询直接把这个值作为条件让扫描直接从正确位置开始。改动并不复杂但能让分页查询的数据扫描量保持恒定是非常值得推荐的做法。4. 常见问题与排查技巧实录日常的SQL优化工作中有一些问题是反复出现的这里我整理成一份速查表算是这么多年来踩坑换来的心得。4.1 高频问题速查表现象可能原因排查方向优化建议明明有索引却不走索引列参与了运算或函数操作查看WHERE条件中的列是否存在DATE_FORMAT(create_time, %Y)这类写法改成条件列的原始范围比较走了索引还是很慢回表次数过多看执行计划中的Extra是否大量出现Using index condition对比回表行数和最终结果行数改成覆盖索引把查询列纳入索引ORDER BY触发排序索引顺序与排序方向不一致检查联合索引是否包含排序字段调整索引字段顺序尽量让排序走索引深分页查询开销巨大LIMIT的偏移量过大观察分页页数变大后耗时线性增长改成键集分页或缓存前一页的最后ID表数据量大但统计信息不准确优化器选择了错误执行计划查看表的统计信息更新时间针对大表定期执行统计信息更新并发高时锁等待严重多事务同时更新同一批行查看监控中的锁等待事件重写业务逻辑减少冲突窗口隐式类型转换字段字符集不一致或类型不一致查看WHERE条件中列类型是否与传入参数一致统一字段类型杜绝隐式转换多表Join顺序不佳驱动表数据量过大查看执行计划中的驱动表和被驱动表使用STRAIGHT_JOIN仅限确认时或改写子查询4.2 一个典型的隐式转换坑有一次某同事让我帮忙看一条SQL执行计划显示主键索引都没走。SQL条件是这样的SELECT * FROM user_account WHERE mobile 13800138000表面上看没问题但mobile字段在表里是varchar(20)传给它的却是一个整数。数据库在比较时会将字段隐式转换为数字导致索引失效。改法很简单把参数改为字符串即可。这类问题在生产环境非常普遍特别是从外部接口传入参数时类型一不小心就变了。排查这类问题时我一般不会只盯EXPLAIN也会顺带查一下表结构DDL看看字段类型和字符集。曾经还遇到过两张表Join时字符集一个utf8mb4一个utf8导致Join列无法使用索引的情况这种问题很难一眼看出来需要非常细致地逐项排查。4.3 关于添加索引的副作用做SQL优化一定要有个意识索引不是越多越好更不是越全能越好。每增加一个索引都会带来写放大——插入、更新、删除时都需要同步维护索引结构。索引过多可能让一条批量更新语句的性能急剧下降甚至引发存储空间暴涨。我给自己的一个参考标准是单表的索引数量尽量控制在5个以内超过这个数量就要认真评估是否值得。尤其是联合索引字段越多占用空间越大。业务上很多需求是可以通过复用现有索引的最左前缀来满足的不必每个查询都建一个专属索引。4.4 生产环境操作的节奏与安全生产库上的索引变更不管看起来多小都建议遵循这样的节奏先在预发或灰度环境执行同样的变更用压测或线上读流量验证效果。评估加索引时对写入性能的影响特别是大表加索引耗时可能很长期间会导致阻塞。维护窗口期执行并且要有回滚方案。加错索引最多浪费点存储但如果是删旧索引加新索引顺序和时机都要格外小心。变更完成后利用慢查询日志和监控持续观察至少24小时确认没有新的计划问题。有一次在线上给一张5000万行的表加索引因为没选好窗口期导致锁表时间过长业务写入出现短暂阻塞。那次之后我把在任何线上环境做索引变更前先评估表大小、并发情况、变更时长写成了一个固定检查项。5. 这次优化项目里的一些深入思考SQL优化做到后面拼的已经不只是会不会写EXPLAIN和加索引了而是对整个数据库运行机制的理解深度。这里分享几个这次项目中引发的延伸思考。5.1 数据分布与优化器判断的错位很多时候一个SQL方案在测试环境表现很好一到生产环境就走样。根本原因在于测试环境的数据分布和生产环境完全不在一个量级。优化器选择执行计划依赖的是表的统计信息如果统计信息不准确或者因为数据倾斜导致某个字段的值的分布极不均匀优化器完全可能做出错误选择。比如status字段业务上有99%的数据是1这个状态只有1%是其他状态。如果查询条件里status1那即便这条查询走了索引最终也要扫过大量数据才能拿到结果。这种情况下优化器可能会觉得全表扫描反而比走索引更快于是执行计划就变成了ALL。这是走了索引还是慢的一个隐藏原因。碰到这种问题一般要从业务拆分的角度思考比如把历史数据归档或者依据状态字段做分区表而不是和优化器硬刚。5.2 没有银弹每种方案都有适用边界这几年我也接触过不少号称万能优化的方案比如强制走某个索引、改写SQL使用STRAIGHT_JOIN、甚至直接在SQL里加HINT。说实话这些手段都有效但都是特定场景下的手术刀不是常规武器。滥用HINT尤其危险——它会让优化器丧失自主判断能力一旦数据量级变化性能可能瞬间崩盘。我通常建议的做法是优先通过调整索引、改写SQL结构来解决问题把HINT留给极少数确有必要且经过充分验证的场景。让优化器自然选择最优路径长期看是更稳定的方案。5.3 优化之后的可观测性建设老实说每次优化完一条SQL我都觉得事情只做了一半。另一半是如何让下一次出现类似问题时我们能更快发现和定位。在这次项目的后续阶段我给核心链路补充了这几样东西慢查询阈值的定期review把过去三个月变慢的SQL重新拉出来复盘。把关键SQL的执行计划变化纳入监控当rows或type出现较大变化时主动告警。对核心业务表的数据量、索引使用情况做周期性统计提前预判风险。这个思路的核心是把一次性的SQL优化变成常态化的性能治理。系统的数据在涨业务在变今天的最优方案可能就是明天的性能瓶颈来源。只有具备了快速发现和定位问题的能力SQL优化这个事才算真正落地。6. 项目收尾复盘核心经验总结这个SQL优化项目做下来我给自己的工作复盘列了几条对自己后续最有用的经验分享在这里。6.1 把时间花在最重要的SQL上不是所有慢SQL都值得花大力气优化。一条每天只执行几次且不在核心链路上的慢SQL优先级远低于一条每天执行几百万次的次优SQL。我一般排序方法是执行频率 × 单次执行成本 优化收益。按照这个公式排序能把有限的优化精力放在最有价值的地方。实际项目里一条高峰期间每秒执行数十次的SQL哪怕优化20%的性能效果都远大于把一条凌晨报表SQL从2小时优化到10分钟。6.2 文档记录比优化本身更重要每做完一轮SQL优化我都会把完整的排查过程和方案记录归档包括原始SQL、执行计划、改动内容、效果对比、数据量变化等。这么做有三个好处一是后续出问题能快速回溯二是可以提炼共性问题沉淀成团队的SQL规范三是新人接手时不用从零摸索。可惜很多人没有这个习惯改完索引就完事等几个月后问题重现自己都记不清当时改了什么。6.3 技术之外与业务的沟通同样重要SQL优化的最后一道坎往往不是技术而是业务理解。有时候SQL慢是因为业务需求本身太过复杂比如一次交互要查询大量历史数据、接口要返回几百个字段。这时候单纯从SQL层面优化空间已经不大更好的路径是和产品沟通调整需求或改造数据模型。比如把部分实时计算改成预计算把联机查询和历史分析分离或者引入缓存组件——这些都不是SQL优化本身但都能从根本上降低对数据库的压力。从这个角度看SQL优化更是一项系统级的工作SQL只是最终的表现层而背后的表结构设计、索引策略、业务需求、数据生命周期每一层都有可以施展的空间。把视野拉高你的优化方案才不会只是一次治标的修补。