1. 慢SQL排查第一步为什么先看EXPLAIN而不是直接改语句干过几年MySQL调优的人应该都有这种体验线上一条SQL突然把数据库拖垮CPU飙升、连接数打满业务方十万火急地甩过来一条查询问你能不能快点修好。这时候最忌讳的事情就是一上来凭感觉改语句——换写法、加索引、拆查询改了半天可能压根没动到点子上。我个人的习惯是不管多着急先打开命令行或者客户端工具把这条SQL前面加上EXPLAIN跑一遍看清楚MySQL到底打算怎么执行它再动手。EXPLAIN这个词大家都不陌生面试也常考但很多人只是背了输出列的名字真到了用的时候却不清楚每一列背后意味着什么更不知道该怎么根据输出调整索引和语句。这篇文章就把我这些年用EXPLAIN排查慢SQL的实际经验完整梳理一遍希望能帮你建立一套看到EXPLAIN输出就知道该干什么的条件反射。EXPLAIN的输出本质上就是MySQL优化器对这条SQL生成的执行计划的可读展示。MySQL不会真的把数据查出来只是告诉你我准备用哪个索引、先读哪张表、预估扫多少行、最后的Extra里还有什么额外操作。你可能要问了优化器给的计划就一定是对的不一定但绝大多数时候它比人拍脑袋猜得准得多。而且通过EXPLAIN你能一眼看出有没有走索引走了什么索引有没有文件排序有没有临时表这些才是真正决定SQL快慢的核心因素。提示EXPLAIN只负责解释执行计划不执行真正的查询所以即使线上数据量很大你也可以放心跑不会对业务造成额外压力。在正式开始之前先说明一下我这篇文章的讨论范围。MySQL版本不同EXPLAIN的输出也有差异比如MySQL 5.7和8.0在JSON格式、直方图、反连接优化上的展示就不太一样。我这里的示例默认基于MySQL 8.0如果你还在用5.6或5.7个别列的字段名和含义会有出入但总体的分析思路是通用的。2. 读懂输出列type、key、rows、Extra是最核心的四组信号EXPLAIN输出列很多初学者最容易被一堆字段吓住。但实际上你在排查慢SQL时真正需要重点盯住的就四样type访问类型、key实际用到的索引、rows预估扫描行数、Extra额外信息。我甚至可以说把这四列吃透了80%的慢SQL问题你都能定位出来。剩下的filtered、partitions这些列可以后续再慢慢补充理解。2.1 type这一列从system到ALL的完整阶梯type列描述的是MySQL在表中查找数据行的访问方式它有一个从好到坏的排序。我习惯把它想象成一条从精确定位到全表硬扫的斜坡type值含义性能评价system表只有一行相当于系统表最好但几乎遇不到const用主键或唯一索引等值匹配最多只返回一行非常好eq_ref连接查询时被驱动表用主键或唯一索引等值匹配非常好ref用普通二级索引等值匹配好range用索引做范围扫描、、BETWEEN、IN等还不错index扫描整个索引树而不是全表比ALL好一点但也好不到哪去ALL全表扫描最差慢SQL重灾区我在实际排查中遇到最多的两类问题一是ALL全表扫描二是明明是range或ref却因为某些原因退化成了全表扫描。举个例子假设我们有一张用户订单表CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, order_no VARCHAR(64) NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL, created_at DATETIME NOT NULL, KEY idx_user_id (user_id), KEY idx_created_at (created_at) ) ENGINEInnoDB;如果执行EXPLAIN SELECT * FROM orders WHERE status 1;因为status字段没有任何索引你大概率会看到type为ALLrows接近全表行数。这就是最经典的全表扫描问题。而如果换成EXPLAIN SELECT * FROM orders WHERE user_id 1024;user_id上有二级索引idx_user_idtype就会显示为refrows会大幅下降。2.2 key列别被possible_keys骗了possible_keys展示的是优化器可以考虑哪些索引key展示的是优化器实际选用了哪个索引。两者经常不一致而你最后要看的只有key。如果key列是NULL说明这条SQL根本没用到任何索引哪怕possible_keys里列一堆也没用。还有一个很常见的问题key显示用到了索引但rows依然很大。这种情况通常意味着索引的选择性不够好比如你在性别字段上建了索引但性别只有男女两个值过滤完以后还是剩下一半数据优化器算来算去觉得走索引和全表扫描差不多干脆就回到全表了。2.3 rows列预估值既要关注绝对值也要关注比例rows列表示优化器预估需要读取的行数。这里有两个容易踩的坑我先说清楚第一rows不是真实扫描行数优化器就是根据统计信息大概估算的跟实际值经常有偏差第二真正要看的不是rows的绝对数而是rows和表总行数的比例。比如一张一亿行的表rows预估为1000行这已经是非常好的结果了因为走索引后只需要访问极少一部分数据。但如果一张1万行的表rows预估为8000行那这SQL基本就没救了。2.4 Extra列Using filesort、Using temporary、Using index是最需要警觉的三组词Extra列是执行计划的附加题很多关键线索都藏在这里。我按照严重程度排个序碰到下面这几种情况你就要警惕了Using filesort表示MySQL需要对结果集额外做一次排序操作而且这个排序往往不能利用索引顺序需要在内存或磁盘上临时排。数据量大起来以后这绝对是性能杀手。Using temporary表示MySQL需要使用临时表来辅助查询常见于GROUP BY、DISTINCT、某些子查询。临时表是要在内存或磁盘上建表的代价非常高。Using index这反而是个好信号它表示你要查的列刚好都在索引里可以直接从索引树获取数据而不需要回表这叫覆盖索引优化。Using where表示MySQL在存储引擎层拿到数据后还要在server层再做一次条件过滤也就是有些条件没法用到索引。我举一个非常典型的慢查询案例很多人在MySQL里遇到过类似的EXPLAIN SELECT user_id, SUM(amount) FROM orders WHERE created_at 2024-01-01 GROUP BY user_id ORDER BY user_id;这条SQL一看就有两个隐患GROUP BY user_id可能需要临时表ORDER BY user_id如果正好和GROUP BY一致还好但如果不一致就会触发Using filesort。我在EXPLAIN结果里见过太多这种组合了。3. 覆盖索引与回表看着走了索引为什么还是慢有一次线上系统报警说某条订单查询接口的P99延迟从200ms涨到了3秒。我把SQL拿来一看发现EXPLAIN输出里type是refkey也用了idx_user_id一切看起来都挺正常那为什么还是慢问题就出在Extra列的描述上。那条SQL长这样EXPLAIN SELECT * FROM orders WHERE user_id 1024;注意这里的SELECT *。二级索引idx_user_id里只存了user_id和主键id你要把整行数据都查出来MySQL就必须拿着索引中得到的id去主键索引也就是聚簇索引里找完整的数据行。这个动作有一个专门的术语叫回表。如果user_id1024这个条件匹配到几千行几千行的话那就要回表几千次每次回表都是一次随机IO速度自然快不了。3.1 覆盖索引是用空间换性能的典型做法既然回表代价高那有没有办法不回表有就是让查询所需的所有列都包含在同一个二级索引里。这样MySQL只需要扫索引树就能拿到你想要的数据完全不需要回表Extra列里就会出现Using index。还是上面的例子如果把SELECT *改成只查user_id和order_no并且为(user_id, order_no)建一个联合索引ALTER TABLE orders ADD KEY idx_user_order (user_id, order_no);然后执行EXPLAIN SELECT user_id, order_no FROM orders WHERE user_id 1024;你会发现Extra列出现了Using indexrows也变小了。这就是覆盖索引生效的标志。但覆盖索引也有代价联合索引占据的磁盘空间更大写入时的维护成本更高因为你每插一条数据都要更新索引。所以我的建议是不要为了追求覆盖而把所有列都塞进索引里而是要把高频查询中WHERE条件里的列和SELECT列表中的列做一个权衡。比如某个查询每天跑几百万次那给它专门建覆盖索引完全值得如果只是临时跑一次的分析任务那就没必要。3.2 区分回表与索引条件下推很多人对回表的理解存在一个误区觉得只要用了二级索引就没问题。事实上二级索引和聚簇索引是完全不同的两棵树二级索引的叶子节点存储的是索引列值加主键值聚簇索引的叶子节点存储的是完整行数据。所以只要查询列超出索引列范围回表就必然发生。这里顺便提一个MySQL 5.6以后引入的优化——索引条件下推Index Condition PushdownICP。假设联合索引是(user_id, status)但你的WHERE条件是user_id 1024 AND amount 100。以前的做法是先用索引找到所有user_id1024的行然后回表在server层再过滤amount100有了ICP之后MySQL会在存储引擎层、遍历索引的时候就顺手把amount100这个条件给过滤掉减少回表次数。在EXPLAIN的Extra里会出现Using index condition这是一个积极的信号说明优化器已经尽量帮你省了回表操作。所以当你看到一条SQL走了索引但rows还是很大的时候先不要急着加索引可以想想是不是SELECT的列太多导致不得不回表是不是完全可以通过调整查询列或设计联合索引来做到覆盖索引搞清楚这个问题很多走了索引还是很慢的困惑都能解开。4. 一次真实调优全过程从ALL到ref再到覆盖索引的演进前面讲了很多理论和概念这一节我用一条实际踩坑的SQL把整个过程连起来走一遍。这次我带大家模拟一个真实场景。假设我们有一个电商系统的订单表姑且叫order_detail大概几千万行数据表结构简化后是CREATE TABLE order_detail ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL, buyer_id BIGINT NOT NULL, seller_id BIGINT NOT NULL, pay_status TINYINT NOT NULL, pay_amount DECIMAL(12,2) NOT NULL, pay_time DATETIME, delivery_province VARCHAR(32) DEFAULT NULL, cancel_reason VARCHAR(255) DEFAULT NULL, KEY idx_buyer (buyer_id), KEY idx_seller (seller_id), KEY idx_pay_time (pay_time) ) ENGINEInnoDB;业务方反馈说有一个后台报表页面查某个卖家在某段时间内已支付订单的金额总和跑一次要十几秒页面直接超时。我拿到的SQL大概是这样的SELECT seller_id, SUM(pay_amount) FROM order_detail WHERE seller_id 8801 AND pay_status 1 AND pay_time BETWEEN 2024-03-01 AND 2024-03-31 GROUP BY seller_id;第一次EXPLAIN的结果让我非常意外——明明seller_id上是有索引的type却是ALL。我确认了一遍seller_id 8801这个条件用的是等值匹配字段上又有索引优化器怎么可能不用后来看了下数据分布才明白8801这个卖家是个大卖家他一个人的订单量占了全表的60%以上。优化器一算账走idx_seller的代价比全表扫描还高干脆选了ALL。4.1 第一轮优化从ALL到range既然单靠seller_id撑不住选择性我把目光放在pay_time的索引上。业务场景是查一个月的数据再加上卖家过滤思路是让MySQL先通过pay_time索引锁定一个月的数据范围再在这个范围内过滤seller_id和pay_status。所以我尝试强制走idx_pay_time或者干脆重新设计联合索引。最终我选择新建一个联合索引ALTER TABLE order_detail ADD KEY idx_seller_paytime (seller_id, pay_time);这个索引把两个高频过滤字段拼在一起优化器的选择余地就大了。EXPLAIN结果变成了这样type: rangekey: idx_seller_paytimerows: 从几百万降到几万Extra: Using where这一步的效果非常明显查询直接从十几秒降到了几百毫秒。但我的工作还没结束因为rows虽然少了Extra里的Using where还在。我简单解释一下这意味着MySQL在索引范围内取到行以后还需要回表读取完整行然后在server层过滤pay_status条件。对于一个每天跑几十次的后台报表来说几百毫秒能接受但我还是想看看能不能再往上一层。4.2 第二轮优化挤进覆盖索引报表只需要seller_id、pay_amount、pay_status这几个字段那我能不能把pay_amount也加进索引这样MySQL在扫描索引的时候就能直接拿到金额数据做SUM连回表都省了。于是我把索引改成ALTER TABLE order_detail DROP INDEX idx_seller_paytime; ALTER TABLE order_detail ADD INDEX idx_seller_time_amount (seller_id, pay_time, pay_amount);注意索引字段顺序是有讲究的seller_id是等值匹配放最前面没问题pay_time是范围条件放第二pay_amount是SELECT要用的列放最后用来实现覆盖索引。再次EXPLAIN结果很有意思type: rangekey: idx_seller_time_amountrows: 还是一万多Extra: Using where; Using index看到Using index出现在Extra里我就知道成了——回表彻底被省掉SUM(pay_amount)的计算直接在索引树上完成。最终这个查询从最初的十几秒优化到了300毫秒以内报表页面恢复正常。4.3 为什么联合索引的顺序那么关键很多人建联合索引的时候不思考顺序觉得把字段随便拼一拼就行这其实是个很要命的问题。MySQL联合索引遵循最左前缀原则也就是说索引的字段顺序决定了它能匹配哪些查询模式。索引idx_seller_time_amount实际上能生效的场景是WHERE seller_id ? 只要seller_idWHERE seller_id ? AND pay_time ? 只要前两列WHERE seller_id ? AND pay_time BETWEEN ? AND ? 同样用前两列只是pay_time变范围如果有人把字段顺序写成(pay_time, seller_id, pay_amount)那seller_id 8801这个条件就没法利用索引的前缀因为最左列是pay_time而你的WHERE条件里没有提供pay_time的等值或范围。这就是建的索引没被用上最常见的原因。5. 四类最常见EXPLAIN输出误判以及我踩过的三个坑前面把核心列和分析思路都讲完了但实际用下来EXPLAIN本身也会有一些迷惑性。我遇到过的几类误判借助这篇文章一并给你提个醒。5.1 误判一possible_keys为空就以为索引没用possible_keys为空不代表你的索引建设有问题。有时候是因为SQL语句中的列名做了函数操作比如WHERE DATE(created_at) 2024-01-01created_at上的索引就废了因为函数操作让索引无法定位。这种时候识别为索引失效是OK的但你要明白这不是EXPLAIN的错而是SQL写法的问题。解决办法要么是把函数换成范围条件WHERE created_at 2024-01-01 00:00:00 AND created_at 2024-01-02 00:00:00要么建一个函数索引MySQL 8.0支持但一般优先改SQL。5.2 误判二看到Using filesort就慌其实有时可以避免Using filesort确实不好但你要分清楚它排序的数据量。如果排序结果集只有几百行那性能影响几乎可以忽略。真正要命的是几百万行的大排序。它的根源往往是ORDER BY的字段顺序和索引顺序不一致。一个实用的优化思路是让ORDER BY的字段顺序恰好对应索引的最左前缀顺序。比如有索引(a, b)那么ORDER BY a, b就能直接利用索引顺序如果写成ORDER BY b, a那mysql大概率就要filesort了。5.3 误判三rows小就一定快不一定有时候rows显示几十行但实际执行时间反而很长。这时候要怀疑是不是Extra里有Using temporary、Using filesort在悄悄拖后腿。比如GROUP BY一个没有索引的字段即使WHERE条件过滤后只有几十行GROUP BY也可能在临时表里进行分组排序操作。所以永远不要只看一两列就下结论要四列一起看。5.4 我的踩坑实录坑一在长字符串上建前缀索引结果走了索引还是慢。表里有个字段是用户备注remarkVARCHAR(200)我在上面建了索引。EXPLAIN显示type是refkey也用上了但实际查询还是慢。后来发现备注字段的索引选择性和内容长度太相关前缀索引导致匹配范围太宽回表次数极多。最后我没法直接索引长文本改成冗余一个分类码字段或者直接用全文索引性能才上来。坑二IN查询的数据量分布不均EXPLAIN的预估和实际差距极大。有次排查一个IN子查询EXPLAIN预估rows只有几千实际执行却扫了上百万行。原因是优化器对IN列表里的每个值单独估算然后相加但不知道某些值在表里占了特别大的比例导致估算严重失真。这种可以用FORCE INDEX或者改写SQL来绕过优化器的错误判断但前提是你对数据分布足够了解。坑三分页查询越到后面越慢EXPLAIN却看不出名堂。经典的分页慢SQLLIMIT 1000000, 20前面999980行的扫描代价被EXPLAIN的rows完美地隐藏了因为它只显示预估访问行数不显示你offset跳过了多少行。这种情况要用延迟关联先查出主键再回表SELECT * FROM order_detail JOIN (SELECT id FROM order_detail WHERE seller_id 8801 ORDER BY id LIMIT 1000000, 20) t ON order_detail.id t.id;6. 针对慢SQL的完整排查顺序建议我个人的一条路走到黑的经验最后我把这些经验整理成一套可以直接照着操作的排查顺序算是给自己留的一份避坑清单也给刚接触EXPLAIN的同行一个抓手。如果你遇到一条线上慢SQL我的建议流程是先看是不是全表扫描型。执行EXPLAIN观察type是否为ALL。是ALL优先检查WHERE条件里有没有可建索引的等值或范围字段或者字段上是否被函数、隐式类型转换破坏了索引使用。再看索引选择和选择性。如果type是ref或者range但rows很大回到表数据分布上想想这个索引列的区分度够不够要不要换字段组合有时候单独一个字段区分度不够联合两个字段就能显著缩窄扫描范围。看SELECT列有没有拖后腿。Extra里如果没有Using index同时SELECT列表里有很多不在索引中的字段那基本就是在大量回表。考虑把高频查询做成覆盖索引或者减少SELECT的列比如先select id再用join查详情。看有没有排序和分组的隐患。Extra里出现Using filesort或Using temporary时把GROUP BY、ORDER BY的字段和索引顺序对一遍能优化就优化不能优化就评估结果集大小实在太大就要改写SQL或者走汇总表方案。最后再看执行计划是否稳定。如果发现优化器有时候走对索引有时候走错索引你可以考虑ANALYZE TABLE更新统计信息或者在SQL里用FORCE INDEX固定计划但我不太建议动不动就FORCE INDEX因为数据分布一变固定的计划可能反而更糟。在MySQL 8.0里我还会习惯性用EXPLAIN ANALYZE去看真实执行时间和实际行数。它真的会执行SQL所以只适合在测试库或低峰期使用但输出比普通EXPLAIN直观太多。比如它会明确告诉你每个步骤消耗的时间以及表扫描实际行数。如果你手头有8.0强烈建议试一下。不过在生产环境不要乱跑EXPLAIN ANALYZE毕竟它会真查一遍。写到这里基本把EXPLAIN的分析路径和常见问题过了一遍。说实话SQL优化不是一锤子买卖你优化一次之后随着数据量增长、索引变化、业务查询模式变化执行计划还会漂移。我的经验是养成一个习惯每次上线新查询或者发现线上慢查询第一件事就是存一份EXPLAIN的输出截图或文本存档后面再出问题时翻开旧记录马上就能看出执行计划哪里发生了偏移。这个习惯帮我省了非常多的时间建议你也试试。