线上接口突然从几千的QPS一路掉到几十数据库CPU飙到99%翻出慢查询日志一看一条跑了58秒的SQL正躺在列表第一位。这种场面凡是写过业务代码的兄弟多少都经历过。SQL优化这事特别有意思你说它难吧翻来覆去无非就是索引、执行计划、改写语句那几板斧你说它简单吧真到了生产环境各种千奇百怪的慢查询分分钟教做人。这篇文章不去给你背八股文就结合我这些年实际踩坑、排查、优化的真实经历把SQL优化从最底层的思路到执行计划怎么看再到索引怎么建、SQL怎么改、并行怎么用最后到慢SQL排查的完整闭环从头到尾捋一遍。不管你是刚写SQL没多久的新手还是被慢查询折磨过几回的开发老手这篇都能给你一些能直接拿去用的东西。1. 先把优化思路理清楚别一上来就改SQL很多朋友拿到一条慢SQL第一反应就是给字段加个索引或者把子查询改成JOIN。这种条件反射式的优化方式十次有八次要翻车。原因很简单SQL慢只是表象至于慢在哪一层、慢的根源是什么没搞清楚之前瞎动手往往会越改越糟。1.1 先判断瓶颈在哪个层级一条查询从发起到返回中间要经过网络传输、SQL解析、优化器生成执行计划、存储引擎扫描数据、回表、排序、分组、结果返回等一堆环节。SQL慢的时候先别急着盯着SQL本身而是要分清楚瓶颈到底出在哪个层级。我自己排查时会按这个顺序过一遍是不是网络问题比如应用服务器和数据库之间延迟高、丢包是不是数据库服务器本身扛不住了CPU、内存、IO是不是已经打满是不是有锁等待尤其是行锁、间隙锁、MDL锁把查询堵住了是不是表数据量太大却没有合适的索引导致全表扫描是不是SQL写法有问题导致优化器选错了执行计划。这里面最容易踩坑的是锁等待和资源瓶颈。你辛辛苦苦把SQL改了半天结果一查发现是另一个事务一直没提交锁把查询堵死了。这种时候改SQL根本没意义要解决的是事务并发的问题。真实场景里有一个很典型的例子有个同事跑来跟我说有一条SQL特别慢让我帮忙优化。我看了一眼SQL写得确实不怎么样但奇怪的是这条SQL之前在测试环境跑得飞快上了生产就慢得跟蜗牛似的。后来查了一圈才发现是有人在生产环境跑了一个大事务一直没提交导致这条SQL的锁等待时间特别长。这时候你再怎么优化SQL也白搭得先把事务处理掉。1.2 把优化目标拆成可量化的指标在动手优化之前最好先明确一个问题这条SQL到底要多快才算达标没有目标的优化很容易陷入改了又改、越改越玄学的怪圈。如果拿不出明确的业务指标我一般会用三个量化维度来定目标响应时间Response Time从发起到拿到结果的时间这个最直观扫描行数Rows ExaminedSQL执行过程中实际扫了多少行这个数越小越好返回行数Rows Returned最终结果返回了多少行这个由业务需求决定没法变。优化的本质就是尽量缩小扫描行数和返回行数之间的差距。你只想拿10条数据结果数据库翻了100万行出来那不管怎么换写法都是在做无用功。所以拿到一条慢SQL第一件事不是改语句而是先看它按要求扫描了多少行、返回了多少行差距一大问题基本就锁定在索引或者SQL写法上了。我自己在优化前会习惯性地把执行时间、扫描行数、返回行数记下来优化完再跑一遍做对比。没有这些数字你根本说不清楚自己的优化到底提升了多少。很多时候改完SQL感觉上好像快了不少实际上执行时间从500毫秒变成490毫秒这种优化就是自欺欺人。2. 执行计划SQL优化的第一现场执行计划就是数据库优化器根据SQL、表结构和统计信息算出来的一套执行方案。它决定了这条SQL是先查哪张表、走哪个索引、怎么关联、怎么排序。看不懂执行计划的SQL优化跟瞎子摸象没什么区别。2.1 看懂执行计划的几个关键字段在MySQL里一条EXPLAIN下去输出结果里有很多列新手往往盯着type那一列看觉得只要看到ALL就是全表扫描看到ref或者range就万事大吉。这个思路太粗了有几个字段其实比type更值得关注。第一条要看的是type列。它的值从好到差大致是这么个顺序system const eq_ref ref range index ALL。type为ALL的时候基本就是要做全表扫描SQL慢多半跟这个脱不了干系。但是type到了ref或者range也不代表就没问题了有时候索引虽然用上了但扫描行数还是特别大照样慢得离谱。第二条是rows列这个表示优化器预估的需要扫描的行数。注意这只是个预估值不是实际值但它是衡量执行计划好坏的一个非常重要的参考。如果预估要扫几百万行哪怕type显示的是range这条SQL也快不起来。第三条是filtered列它表示从扫描的行中最终能通过WHERE条件过滤出来的行数比例。扫描了100万行filtered是0.1%说明最终只有1000行是有效数据。这个值越低说明扫描的无效数据越多索引或者SQL写法肯定有问题。第四条是Extra列这里经常会出现决定生死的提示。比如Using filesort表示需要额外的排序操作Using temporary表示要用临时表Using index表示走的是覆盖索引Using where表示在存储引擎层拿到数据后又做了条件过滤。这几个提示组合起来几乎就能把一条慢SQL的病根讲清楚。我之前遇到过一条统计报表的SQL慢到什么程度呢跑一次要一分多钟。EXPLAIN一看type是ALLrows预估1200万Extra里赫然写着Using temporary; Using filesort。这三个问题叠加在一起不快才怪了。后来建了复合索引把排序字段和分组字段覆盖进去再把临时表那个环节去掉直接降到300毫秒这就是执行计划的价值。2.2 执行计划里常见的坏味道经验多了之后我总结出几个一眼就能看出问题的执行计划特征遇到这些基本可以直接定位问题type为ALL全表扫描要么没建索引要么索引没生效rows比实际数据量小很多统计信息过期优化器低估了扫描行数Using filesort有排序操作没走索引数据量大时是性能杀手Using temporary用了临时表常见于GROUP BY、DISTINCT、子查询等场景key为NULL明明有索引可用优化器却没用上。这几类问题每一个背后都有对应的解决方案。比如Using filesort解决办法一般是检查ORDER BY字段是不是在索引里能不能通过索引天然有序的数据来避免排序Using temporary则要重点排查GROUP BY的字段顺序和索引结构是否匹配。另外还有一个特别容易忽略的地方优化器判断一个索引好不好用依赖的是表的统计信息。如果一张表的数据量变化很大比如删了大批量数据但统计信息没有及时更新优化器可能会选错索引。遇到这种情况手动ANALYZE TABLE或者优化完再跑一次EXPLAIN可能就能看到完全不同的执行计划。3. 索引设计SQL优化最关键的一环说句实在话SQL优化八成的功夫都花在索引上。索引设计得好不好直接决定了一条SQL是全表扫描还是走索引快速定位。但索引这东西也是一把双刃剑建多了写入慢建少了查询慢建错了还可能导致优化器犯迷糊。3.1 索引失效的典型场景踩过的坑都在这里很多朋友跟我一样最早学SQL优化的时候都被索引失效四个字坑过。明明字段上有索引SQL也用了这个字段做条件但执行计划就是不走索引气得想砸电脑。后来踩坑踩多了总结出几个最典型的失效场景。第一种是函数操作导致索引失效。比如在WHERE条件里写了WHERE DATE(create_time) 2024-01-01哪怕create_time上有索引优化器也没法用因为函数把字段的值改写了索引里的排序就失效了。正确的做法是改成范围查询WHERE create_time 2024-01-01 AND create_time 2024-01-02。第二种是隐式类型转换。最常见的坑是字符串字段和数字比较比如mobile字段是VARCHAR类型用WHERE mobile 13800138000MySQL会隐式地把字符串转成数字导致索引失效。我之前排查过一条生产事故就是这个问题一条查手机号的SQL把千万级数据全扫了一遍。解法就是给字符串加引号WHERE mobile 13800138000。第三种是前导模糊查询。WHERE name LIKE %张这种写法索引树是没法利用的因为不知道要从哪个字符开始匹配。反过来WHERE name LIKE 张%是可以走索引的。如果非要用前导模糊就得考虑全文索引或者搜索引擎了。第四种是OR条件导致索引失效。WHERE status 1 OR status 2这种如果两个条件里只有一列有索引优化器可能选择全表扫描。解决办法是改成两个查询UNION ALL或者在某些情况下用IN来替代。这些坑每一个看起来都不复杂但在实际写SQL的时候特别容易犯尤其是隐式类型转换有时候你根本意识不到类型不匹配写出来跑起来才发现慢得要命。3.2 复合索引到底怎么建复合索引的设计规则理解最左前缀原则是关键。所谓最左前缀就是说复合索引按照定义的字段顺序从左到右匹配可以只用最左边的部分字段但不能跳过中间的字段直接用后面的字段。举个例子假设在(a, b, c)三个字段上建了复合索引那么WHERE a1、WHERE a1 AND b2、WHERE a1 AND b2 AND c3都可以走这个索引但WHERE b2、WHERE c3、WHERE b2 AND c3都不能完整利用这个索引。这个规则决定了建复合索引时字段顺序极其重要。在实际业务里我一般会按照等值条件字段放前面排序字段或者范围字段放后面的原则来设计。比如一个订单查询场景常用的筛选条件是user_id和status还要按create_time排序那么可以建成(user_id, status, create_time)这样的复合索引。这样既满足等值查询又能让排序走索引避免Using filesort。除了最左前缀还有一个很重要的概念叫覆盖索引。覆盖索引就是查询的字段全部包含在索引里这样查询过程中就不用回表查数据行直接从索引就能拿到结果。覆盖索引在高频查询场景下能带来非常明显的性能提升比如查订单列表时只查id和status如果索引里已经包含了这两个字段就不需要再去数据页里捞一遍了。索引下推是MySQL 5.6以后引入的优化简单说就是在使用复合索引查询的时候如果索引里已经有字段能满足部分过滤条件服务器会在存储引擎层就把这些不满足条件的行过滤掉减少回表次数。这个优化是自动的但前提是你的索引设计要把过滤字段给放进去不然想推也没得推。4. 常用优化方法清单从改写SQL到并行策略索引是硬功夫SQL改写则是软技巧。很多时候一张表,索引已经建得很完美了SQL语句本身也能走索引但就是快不起来。这种时候就需要对SQL进行改写或者换一种执行策略。4.1 改写SQL的几种常见套路先列几个高频改写的场景。避免SELECT *。这个老生常谈但真的很多人改不掉。SELECT *会把所有列都捞出来一方面增加了网络传输的数据量一方面也容易破坏覆盖索引的条件。改成只查需要的列性能提升往往立竿见影。深分页问题。LIMIT 100000, 10这种写法MySQL会把前10万行查出来再扔掉越到后面的页越慢。解决思路是用游标的方式记住上一页最后一条数据的ID或排序值下一页直接用WHERE id 上次的ID LIMIT 10来查。这种延迟关联或基于游标分页的方式在数据量大的场景下优势巨大。子查询改JOIN。在MySQL 8.0之前很多子查询会被处理成临时表再关联性能很差。把子查询改为JOIN更容易让优化器找到最优的执行路径。但要注意JOIN不宜过多一般超过3张表关联性能就开始快速下降这时候就得考虑冗余字段或者拆查询了。用EXISTS取代IN。当子查询的结果集比较小外层表的数据量大时用EXISTS往往比IN更高效因为EXISTS是逐个判断只要找到一个匹配就停止而IN需要把子查询结果全部算出来再做匹配。当然这个也不是绝对的还是要根据具体执行计划来看。分批操作替代大事务。比如要删除大量数据一次性DELETE FROM xxx WHERE create_time 2023-01-01这个操作会锁大量的行产生大量redo log拖垮数据库。稳妥的做法是写脚本循环分批删除每次删1000行删完就提交不影响线上业务。改写SQL的通用思路就是让数据库少干活。少扫描一些行少排序少用临时表少回表网络少传输一些数据。所有的改写技巧万变不离其宗。4.2 并行SQL优化什么时候该用什么时候别碰并行SQL优化是热词里大家问得比较多的一块尤其是在数据仓库和报表查询场景下。MySQL 8.0的InnoDB在并行查询方面的支持并没有很多人想象的那么强大真正大规模使用并行执行的更多是ClickHouse、OceanBase或者Oracle、PostgreSQL这些数据库。先说真正的并行SQL是怎么回事。简单来讲就是把一条SQL要处理的数据切分成多份让多个CPU核心同时处理最后汇总结果。这样省下的时间是近似线性的核心数越多理论上越快。但并行SQL不是银弹它对服务器CPU资源、内存和IO的要求非常高。我自己的经验是并行SQL主要适用于以下三种场景大表全量扫描或者汇总统计单线程扫不过来复杂的多表关联关联的表都很大大批量数据的DML操作比如每天凌晨跑批刷新数仓宽表。但是不建议在OLTP业务上盲目开并行。举个例子一个秒杀接口SQL本身就查几十毫秒你给它开并行结果就是白白占住多个CPU核心把数据库整体吞吐给拖垮。凡事都有代价并行是把多线程的优势放大同时也把资源消耗放大。拿WHERE条件加并行HINT来说MySQL里虽然可以用并行读比如设置innodb_parallel_read_threads但实际工作中我更习惯从应用层做并行比如把一个大查询按时间范围拆成多个小查询用多线程并发去查再在应用层合并结果。这种方式看着土但胜在可控故障影响范围小。这里面最核心的一个判断标准如果一条SQL本身有优化的空间比如没走索引、过度扫描先把这些基础问题解决掉再来谈并行。在一条垃圾SQL上开并行就好比给一辆漏油的车换了个大功率发动机该漏还得漏烧得更快而已。5. 慢SQL排查的完整流程前面讲的都是具体技术点下面把慢SQL排查的流程串起来讲一遍。这个过程在生产环境里非常重要尤其是当你接手一套老系统线上频繁报警慢查询的时候有条不紊地排查和治理才能稳住局面。5.1 从日志里找到慢SQL慢SQL不是凭感觉猜出来的一定要有数据支撑。数据库默认都会提供慢查询日志MySQL里可以动态开启SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;这样设置之后凡是执行时间超过1秒的SQL都会记录到慢查询日志里。但MySQL自带的慢查询日志读起来不够直观生产环境里我更习惯用pt-query-digest或者mysqldumpslow这类工具做统计分析。pt-query-digest会自动把相似的SQL参数归一化汇总出哪些SQL累计执行时间最长、出现频率最高方便按问题优先级去处理。另外在MySQL Performance Schema里也能查到很多执行信息不过在紧急排查的时候慢查询日志永远是第一入口。日志里看到一条SQL先记下执行时间、扫描行数、返回行数然后马上到数据库里EXPLAIN一遍看执行计划是否符合预期。5.2 一个完整的慢SQL优化案例这里分享一个我印象特别深的案例。有一次线上订单导出功能卡顿严重后台一点导出数据库CPU就直接拉满页面转圈十几秒没反应。慢查询日志里捞到这样一条SQLSELECT * FROM orders WHERE merchant_id 5001 AND status PAID AND pay_time BETWEEN 2024-01-01 AND 2024-06-30 ORDER BY create_time DESC LIMIT 200000;EXPLAIN一看实际扫描行数接近800万虽然用到了merchant_id上的普通索引但filtered只有15%大部分数据在status和pay_time过滤后被扔掉了再加上最后要对20万行做排序于是出现了Using filesort。当初这条SQL在数据量小的时候跑得还算可以等订单表涨到几千万行问题就彻底暴露了。针对这个情况我做了三步优化把查询字段从SELECT *改成只查需要用到的列避免回表拉取大量无用数据把原来的merchant_id单列索引改成了(merchant_id, status, pay_time)复合索引让WHERE条件里最常用的三个等值和范围条件能够最大程度命中索引排序字段create_time加入索引让ORDER BY走索引顺序消除filesort。优化之后同样的查询从原来的12秒降到了500毫秒。这个例子很好地说明了一次SQL优化往往是执行计划排查、索引设计、SQL改写三件事的合力而不是单靠某一方面。5.3 定位慢SQL的周边坑慢SQL排查还有一个容易被忽略的坑慢查询日志抓不到某些隐性慢SQL。举个例子有些SQL单次执行只有几百毫秒没达到long_query_time阈值但如果它在高并发下每秒被调用几百次累计起来对数据库的冲击甚至比一条跑10秒的SQL还要大。这类问题单靠慢查询日志是发现不了的要配合数据库的监控指标比如QPS、CPU使用率、InnoDB行锁等待、临时表创建频率等。所以在慢SQL治理这件事上我一直主张抓大不放小。先治大而慢的那是会直接拖垮系统的同时也要关注小而频的那往往是系统容量瓶颈的隐患。6. 常见问题与排查技巧实录这些年下来被问得最多的SQL优化相关的问题其实翻来覆去就那么几个。我把它们集中整理成一张速查表方便大家在实际工作中遇到类似问题时快速定位。常见问题可能原因解决方案加了索引还是全表扫描索引失效函数操作、隐式转换、前导模糊、统计信息过期重写SQL避免失效场景执行ANALYZE TABLE更新统计信息查询很快但排序很慢ORDER BY字段不在索引中把排序字段加入复合索引让索引天然有序明明就差几条数据却查出几十万行缺少有效过滤条件或WHERE顺序不合理看执行计划里rows和filtered优化过滤条件子查询执行奇慢MySQL优化器把子查询处理成了临时表改写为JOIN或改成EXISTS关联分页越翻越慢深分页导致大量偏移扫描改成基于游标的延迟分页或者用WHERE id ... LIMIT批量删除数据把库拖垮大事务锁行多、日志膨胀分批提交每批控制在1000行左右报告型SQL跑批很慢全表聚合计算代价高评估表数据量考虑并行读取或ETL预处理并发高时数据库排队单个SQL本身没那么慢但调用过于频繁加缓存、限流、合并请求避免重复计算这张表不是万能药但基本能覆盖80%的日常问题。真到了需要处理极端场景的时候记住一条原则先看执行计划再做决策。执行计划是SQL优化的地图没有地图你就是在瞎走。有些朋友准备SQL优化相关的面试喜欢背一堆所谓的优化方法口诀什么最左前缀、避免SELECT *、小表驱动大表这些背下来当然有用但面试官真正想听的是你面对一条具体的慢SQL会怎么一步步去定位和分析。我建议多练练EXPLAIN的解读多看看实际执行计划理解了背后的优化器逻辑和索引原理面试才能言之有物。7. 一些个人的优化习惯最后再分享几个我自己在SQL优化工作中养成的习惯和小心得吧。第一写SQL之前先想索引。不是在SQL变慢了才回头补索引而是在设计表结构、写业务SQL的阶段就把常用查询条件列出来根据这些条件设计对应的索引。等上线后再优化往往就要付出更大的代价。第二每一条慢SQL都应该有优化前-优化后的记录。我习惯在排查笔记里记录SQL原文、执行时间、扫描行数、执行计划关键字段以及做了哪些修改。下次再遇到类似问题直接翻笔记就能少走很多弯路。优化这件事经验是可以复利的。第三能用缓存解决的别为难数据库。很多慢SQL本质上不应该落在数据库上热点数据、读多写少的场景加一层Redis缓存就能挡掉大量查询流量。数据库SQL优化解决的是算法和数据结构层面的问题缓存解决的是访问频率和资源消耗的问题。两者配合着用系统才能稳。第四上线前记得备份表结构和SQL脚本。真的这个我吃过亏。有一回优化完SQL临时上了个新索引结果业务高峰期出了点问题想快速回滚结果没留备份只能靠脑子硬猜原来的索引怎么建的。现在不管多小的变更我都习惯先把当前的表结构导出一份存起来回滚的时候一分钟搞定。SQL优化这事说到底是理解数据是怎么被存的、怎么被取的然后想办法让数据库用最少的资源把结果算出来。它不是一个一次性的操作而是一个伴随业务增长持续演进的过程。你的表数据量从100万涨到1000万的时候原来好用的SQL可能就不灵了索引也要跟着调整这是一条没有终点的实践之路。希望这篇文章能帮你把零散的优化知识串成一条线下次再遇到慢SQL至少不慌知道从哪里下手。
