1. 为什么每个SQL优化都绕不开Explain做数据库开发或者后端开发的人迟早会遇到一条慢SQL把你按在地上摩擦的场景。页面响应3秒、接口超时、数据库CPU被打满排查半天发现就是某条查询没走索引全表扫了几百万行。这种时候大家的第一反应基本都是同一个动作把SQL拎出来在前面加上EXPLAIN看一眼执行计划。可以说Explain是SQL优化这摊事里最基础也最核心的起手式。Explain这东西说简单也简单就是一条关键字扔在SELECT前面MySQL就会告诉你这条SQL准备怎么执行先查哪张表、用没用索引、大概扫多少行、有没有临时表和文件排序。但说复杂也复杂因为很多人看完Explain的输出之后只知道看type是不是ALL、key是不是NULL看到这俩不对劲就给where条件加个索引加完发现没效果又不知道问题出在哪。其实Explain输出的每一列都有讲究它们组合在一起才是一个完整的执行计划。这篇文章我不会讲什么高深理论就把Explain这个命令掰开揉碎从输出字段讲到实战诊断带着真实案例走一遍慢SQL优化的完整过程。不管你用的是MySQL 5.7还是8.0这些东西都通用。说到这得先明确一个前提MySQL的Explain在不同版本里的字段略有差异比如5.7开始多了partitions列8.0.18之后多了explain formattree这种新玩法但核心字段和解读逻辑基本没变。所以下面讲的这些你可以直接套到自己的生产环境里。2. 读懂Explain输出每个字段都是一条线索2.1 id、select_type先搞清查询的层级关系执行计划的第一行是id和select_type。id其实是一个序号表示这条SQL里SELECT的编号数字越大越先执行相同id则从上往下顺序执行。很多人一开始不理解id的意义觉得它就是一行编号而已其实不是。遇到子查询、UNION这种复杂SQL时id能帮你理清楚MySQL到底先干了哪一步。举个例子一条SQL里既有子查询又有UNION执行计划会列出多个id。id2的比id1的先执行id3的又比id2的先执行。这个顺序不是拍脑袋定的MySQL优化器会把子查询当成临时表先算出来再参与外层查询。搞清楚id的顺序你就知道哪一步是真正的瓶颈而不是被外层那个大查询误导。select_type是告诉你这一步查询的类型。常见的几种SIMPLE普通查询没有子查询也没有UNION。PRIMARY最外层的查询。SUBQUERY子查询中的第一个SELECT。DERIVED派生表也就是FROM后面的子查询。UNIONUNION中第二个及之后的SELECT。UNION RESULT多个UNION查询合并后的结果。这里面最值得警惕的是DERIVED。一旦执行计划里出现派生表往往意味着MySQL把子查询的结果物化成了一个临时表这个临时表可能没有索引后续关联查询就得全表扫描。我在生产环境里遇到过很多次一条看似不复杂的SQL因为FROM子句里套了一个子查询执行计划直接显示临时表扫全量怎么加索引都没用。这种情况的解决思路一般是把子查询改写或者用JOIN替代。2.2 table与partitions明确到底在动哪张表table这一列不用多说就是这一步操作的是哪张表。注意如果出现derived2、subquery3这种带数字的写法表示它查的是id为2的派生表结果或id为3的子查询结果不是物理表。partitions列是MySQL 5.7里加的表示命中了分区表的哪个分区。大多数业务表不会用分区所以这列通常是NULL但在排查大表慢查询时这列很关键。如果你用了分区表结果EXPLAIN显示partitionsNULL那说明你的查询条件没带上分区键MySQL被迫遍历全部分区性能直接打骨折。举一个我踩过的坑。某个日志表按月份做了RANGE分区查询时明明带了时间范围但写法的姿势不对比如时间字段套了函数DATE_FORMAT(create_time)导致索引和分区裁剪都失效了。EXPLAIN一看partitions列直接列出了所有分区名。后来把函数去掉改用范围条件partitions立刻变成只有对应月份的那个分区查询时间从2秒降到0.05秒。这个教训就是分区表字段上做任何函数操作都是灾难。2.3 type访问类型一眼看出这条SQL的档次如果说Explain整个输出里只能看一列那一定是type。type表示MySQL用什么方式访问这张表按性能从好到差排序大概是system表只有一行几乎不可能出现是const的特例。const主键或唯一索引等值查询最多返回一行。eq_ref被驱动表使用主键或唯一索引进行等值关联。ref非唯一索引等值匹配。range索引范围扫描比如BETWEEN、、、IN。index全索引扫描遍历了整个索引树。ALL全表扫描你的噩梦。日常优化最关键的分水岭就是range往左都是能接受的index和ALL基本属于不可接受的级别。一个线上慢SQL如果type是ALL优先考虑加索引把type提升到ref或range如果已经是ref但还是慢那要考虑的是索引区分度、扫描行数或者是不是排序导致的问题。const和eq_ref代表的是精确命中一般出现在主键查询或JOIN关联条件上。ref稍微弱一点是非唯一索引的等值匹配虽然不会回表扫全表但可能命中多行。range很好理解就是你WHERE里的条件把索引的范围划定出来了比如id 500 AND id 1000。index看着比ALL好一点其实也很危险它表示索引树被整个扫了一遍常见于查询条件没走索引但查询列恰好都在索引里MySQL懒得回表就直接扫索引树了。2.4 possible_keys与key索引用了还是没用的真相possible_keys列出MySQL认为这条SQL可能用到的索引key是实际选用的索引。这两列放一起看能发现一类很坑的问题possible_keys有值key却是NULL。这说明优化器在权衡之后觉得用这个索引还不如全表扫描快。为什么呢常见原因有三个。第一区分度太低。比如性别的索引整表数据一半男一半女优化器一算跳过索引直接全表扫反而更快。第二数据量太小。表里面就几十条记录全表扫描的成本和走索引再回表的成本差不多优化器自然选择放弃索引。第三查询使用了函数、隐式类型转换破坏了索引的可相关性优化器只能放弃。遇到过一种情况很迷惑明明在where里用了索引字段EXPLAIN却显示key是NULL。排查到最后发现字段类型是varchar条件值传了一个数字MySQL做了隐式类型转换。这种转换会导致索引失效而且EXPLAIN里完全看不出来只有去翻binlog或者结合业务代码才能定位。所以平时写SQL条件值的类型一定要跟字段类型对应上。2.5 key_len这个字段能算出索引用到了几层key_len是很多人忽略的一个指标但它能告诉你索引到底被“用满了”还是没有。一条复合索引(a, b, c)你在WHERE里只用了a和bkey_len就是a和b的长度之和比(a, b, c)全用上要短。怎么算大概是字符类型varchar变长类型要加2字节记录长度按utf8mb4算一个字符占4字节int占4字节bigint占8字节。还要考虑是否允许NULL允许的话再加1字节。举个例子一张表的索引是idx_phone_name(phone varchar(11), name varchar(20))都允许NULLutf8mb4。那么只用phone条件时key_len 114 2 1 47。如果同时用了phone和namekey_len 47 204 2 1 130。你看到EXPLAIN里key_len是47就知道索引只用了第一列name那列没参与。这个信息有什么用排查复合索引是否被充分使用以及索引里有没有字段被函数破坏。如果设计了一个三列复合索引但实际执行key_len永远只有第一列的长度说明你的SQL写法让后面两列根本没派上用场。这时候该改的往往不是索引而是查询条件的写法。2.6 rows与filtered扫描行数的估算和真实命中率rows列是优化器估算执行这一步要扫描的行数它不是准确值但能用来横向对比不同执行方案的代价。同样的SQL加索引后rows从几十万降成几百性能基本稳了。filtered是5.7之后新增的表示经过WHERE条件过滤后剩下多少比例的记录和rows配合看能判断优化器选这个方案的信心有多大。这里有个人经验rows数据在生产环境受限于统计信息的准确性不一定完全可靠。尤其是有大量增删改的表如果你没及时做ANALYZE TABLE优化器拿到的统计信息可能是几天前的导致它做出了错误选择。遇到EXPLAIN看着很好但实际跑起来很慢的情况可以先跑一下ANALYZE TABLE刷新统计信息再回头看执行计划有没有变化。我在一个订单表上遇到过这种事EXPLAIN显示走了唯一索引rows只有一条但真实执行要5秒。后来发现是表数据量修正后优化器判断走了另一个索引但是查询条件匹配到的行数其实很多二次回表验证浪费了时间。刷新统计信息并强制使用正确索引后问题解决。2.7 Extra隐藏信息的宝库偶尔一句话就是答案Extra列是执行计划的附加信息里面出现的每一句话都值得仔细读。常见的有Using where表示存储引擎返回记录后MySQL server层又做了条件过滤。这个本身没啥问题但如果type是ALL且出现Using where说明WHERE条件没走索引。Using index覆盖索引。查询的所有列都在索引树里不需要回表这是非常高效的状态。看type和Extra组合如果type是index同时Extra是Using index说明索引树被整体扫了一遍但省了回表成本。Using index condition索引条件下推ICPMySQL 5.6之后引入表示部分WHERE条件能在索引层过滤掉。这算优化但和Using index不同它还要回表。Using filesort文件排序。这不是磁盘文件的意思而是说MySQL需要额外做一次排序操作。出现这个通常在ORDER BY上有优化空间。Using temporary使用临时表常见于GROUP BY、DISTINCT或某些UNION场景。这条最危险往往意味着查询会消耗大量临时表空间甚至写到磁盘。Using join buffer关联查询时驱动表太小或关联字段没索引MySQL用join buffer来硬扛。出现这个基本可以断定关联条件上没建索引。Extra里最难看懂的是filesort。很多新手以为文件排序就是把数据写到磁盘其实没那么严重它只是说明排序无法借助索引完成需要额外开销。如果数据量小内存排序就搞定了没太大影响如果数据量大那就得想办法让ORDER BY字段走索引或者改查询结构消除排序。3. 实战操演用Explain完成一次慢SQL诊断3.1 场景还原我拿一个实际调优过的场景来说明这样比较好理解。假设有一张用户订单表结构是CREATE TABLE t_order ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, order_no VARCHAR(32) NOT NULL, status TINYINT NOT NULL, amount DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL, KEY idx_user_id (user_id), KEY idx_created_at (created_at) ) ENGINEInnoDB;这张表攒了大概800万行数据。某天业务方反馈有个导出订单的接口越跑越慢最终要十几秒才能返回。我把SQL捞出来一看长这样SELECT id, order_no, amount, created_at FROM t_order WHERE user_id 102345 AND created_at 2024-06-01 00:00:00 AND created_at 2024-07-01 00:00:00 ORDER BY created_at DESC LIMIT 100;单看这条SQL条件有user_id等值还有created_at范围理论上走idx_user_id或者idx_created_at都行为什么慢3.2 第一次EXPLAIN分析直接在MySQL里执行EXPLAIN SELECT id, order_no, amount, created_at FROM t_order WHERE user_id 102345 AND created_at 2024-06-01 00:00:00 AND created_at 2024-07-01 00:00:00 ORDER BY created_at DESC LIMIT 100;执行计划大致是idselect_typetabletypepossible_keyskeykey_lenrowsExtra1SIMPLEt_orderrefidx_user_id, idx_created_atidx_user_id8153200Using where; Using filesort看这个结果type是refkey走了idx_user_idkey_len是8bigint占8字节说明user_id等值条件正常命中索引。但有两个疑点rows高达15万多Extra里有Using filesort。问题就出在这里。MySQL选择了idx_user_id作为访问路径先把user_id102345的所有记录找出来。这个用户是个大客户历史订单本身就多6月份一个月也有15万多条。然后MySQL要把这15万条数据按created_at排序再取前100条排序走的是Using filesort。整个流程下来扫描15万行加一次大排序不慢才怪。3.3 优化方案与原理分析这个时候有两条路可以走。第一条路让MySQL走idx_created_at先把6月的订单捞出来再过滤user_id。但6月整月的订单量可能上百万过滤出来user_id102345的记录再排序未必比现在快。这条路不稳。第二条路改索引。既然查询条件里同时有user_id和created_at排序字段也是created_at那就建立一个复合索引ALTER TABLE t_order ADD INDEX idx_user_created (user_id, created_at);这个索引设计有讲究。查询条件是user_id等值 created_at范围复合索引把user_id放前面等值匹配精确命中created_at放后面既可以做范围过滤又天然有序。最关键的一点ORDER BY created_at正是这个索引的第二列MySQL可以直接按索引顺序读取不需要额外排序。改完索引后再次EXPLAINidselect_typetabletypepossible_keyskeykey_lenrowsExtra1SIMPLEt_orderrangeidx_user_id, idx_created_at, idx_user_createdidx_user_created124820Using where; Using index conditiontype从ref变成了rangerows从15万降到了4800左右Using filesort消失了。执行时间从之前的8.7秒降到0.08秒。为什么省了排序因为复合索引本身定义了(user_id, created_at)的排序规则MySQL查到user_id102345的索引区间后直接顺着索引顺序往回读created_at倒序的数据取前100条就够了不需要把15万条先攒起来再排序。3.4 补充细节这个案例里的坑这个案例里有几个坑值得单独说说。第一个坑复合索引建好之后原来的单列索引是不是该删在这个场景里idx_user_id已经没有存在价值了因为idx_user_created完全覆盖了它的功能。留着它反而浪费写性能多一个索引就意味着每次INSERT、UPDATE都要多维护一棵B树。我的建议是确认没有其他SQL依赖idx_user_id之后直接DROP掉。第二个坑LIMIT 100在第一次执行计划里压根没起到作用。MySQL在走idx_user_id方案时必须先找到全部user_id102345的行排序后再LIMIT这个LIMIT是在server层最后一步执行的无法提前终止扫描。这也是为什么rows那么高还没法救。而改成复合索引后索引顺序帮我们提前终止了扫描LIMIT才能真正发挥作用。第三个坑key_len从8变成了12。8是bigint的user_id12是8 4多出来的4字节是DATETIME在MySQL中的存储长度DATETIME在5.6是5字节这里的12其实意味着实际计算有差别但方向是对的索引用到了两列。4. 常见问题与排查技巧你遇到的坑我基本都踩过4.1 索引失效的几大典型场景索引失效这件事说多了都是泪。常见的失效场景我列一下每条都是拿生产事故换来的经验对索引列使用函数或计算比如WHERE DATE(created_at) 2024-06-01索引直接失效。正确写法是范围条件created_at 2024-06-01 AND created_at 2024-06-02。隐式类型转换。字段是varchar传参是数字类型MySQL会隐式把字段转成数字来比较索引就废了。反过来字段是bigint传参是字符串数字反而没事这点很多人搞反。LIKE前缀通配符。%abc这种写法索引无法定位到B树的起始位置只能全扫。abc%可以走索引。复合索引最左前缀原则被打破。复合索引(a, b, c)查询条件只写b和c不带a索引直接放弃。这里要特别说一句MySQL 8.0引入了索引跳跃扫描Index Skip Scan某些条件下可以绕过这个限制但别指望它老老实实按最左前缀写条件才稳。WHERE的OR条件中有一部分没索引。比如WHERE a 1 OR b 2a有索引b没有MySQL会选择全表扫而不是分两段查。排查索引失效没有捷径就是一条条EXPLAIN去看。我的习惯是任何一条SQL上线前必须贴出EXPLAIN结果type到不了range级别的直接打回。4.2 排序与分组filesort和temporary的破解思路前面订单案例里已经体验过filesort的威力。这里再多说一个GROUP BY的场景。业务上很常见统计每个用户6月的订单金额。SQL长这样SELECT user_id, SUM(amount) FROM t_order WHERE created_at 2024-06-01 00:00:00 AND created_at 2024-07-01 00:00:00 GROUP BY user_id;一EXPLAIN大概率看到Using temporary; Using filesort。MySQL的处理方式是扫描完范围内的数据后先放入临时表按user_id分组聚合最后排序输出。如果6月有500万条订单这个临时表会非常大内存放不下就改用磁盘临时表性能直接拉胯。破解思路有两种。一种是建复合索引(idx_created_at, user_id)让索引带出user_id虽然还是可能Using temporary但至少数据在内存临时表里能装下。另一种是改变查询语义把GROUP BY改成先子查询取user_id再关联求和。具体哪种好用还得看执行计划的实际表现。但这里有个更重要的优化点很多GROUP BY慢查询根因不是分组操作本身而是WHERE范围太大。问业务方能不能缩小时间窗口或者提前用汇总表把数据算好是更合理的出路。索引能解决一部分问题但别指望索引包治百病。4.3 大表分页排序深分页与filesort的组合拳还有一个高频问题ORDER BY LIMIT的深分页。比如SELECT * FROM t_order WHERE status 1 ORDER BY created_at DESC LIMIT 100000, 20;这种写法的问题在于MySQL必须先把前100020条全部找出来排序后丢弃前100000条只返回最后20条。随着分页页码变大扫描行数越来越多性能急剧下降。用EXPLAIN看大概率是type ALL或indexExtra里必有Using filesort。优化方案有几种第一种延迟关联。先只查主键定位到目标页的20个id再join原表取完整行SELECT t.* FROM t_order t INNER JOIN ( SELECT id FROM t_order WHERE status 1 ORDER BY created_at DESC LIMIT 100000, 20 ) tmp ON t.id tmp.id;这个写法把深度排序限制在只查id的子查询里子查询可能走了覆盖索引回表只回20条整体代价小很多。第二种记录上次查询的锚点。记住上一页最后一条记录的created_at和id下一页直接WHERE created_at 上次值 AND (created_at 上次值 AND id 上次id)配合复合索引就能走range扫描。这是最推荐的长列表方案但需要业务改动配合。4.4 EXPLAIN的进阶玩法FORMAT与ANALYZEMySQL 8.0里EXPLAIN多了几个新玩法值得平时用起来。EXPLAIN FORMATJSON能把执行计划的细节全部输出包括每个步骤的实际代价估算、是否用到了ICP、排序方式等。排查复杂SQL时JSON格式比表格格式多出很多字段尤其是read_cost、eval_cost这些代价指标能看出优化器的决策逻辑。EXPLAIN ANALYZE是8.0.18之后的杀手锏它真的会执行SQL并输出每个步骤的实际耗时和行数。跟普通EXPLAIN的估算值完全不同。比如我一直对这个有印象的生产案例一条SQL的EXPLAIN看着完美全部命中索引但EXPLAIN ANALYZE显示某个步骤实际扫描行数是估算值的100倍。原因就是表统计信息没更新MySQL估算错了。用EXPLAIN ANALYZE一跑真相立刻浮出水面。不过注意EXPLAIN ANALYZE会真实执行SQL在超大表上跑一条扫几百万行的SELECT本身就够数据库喝一壶的。生产环境慎用尽量在测试库或者错峰时段跑。4.5 一个容易被忽略的细节连表JOIN的驱动顺序EXPLAIN看连表查询时第一行出现的表是驱动表后面是被驱动表。优化器决定谁驱动谁核心逻辑是谁的扫描行数少谁当驱动表被驱动表的关联条件必须有索引。一个典型的慢查询长这样两张表各10万行JOIN条件上被驱动表没索引。执行计划会出现Using join buffer然后被驱动表被全表扫了一遍又一遍。这时候优化方案就是在被驱动表的关联字段上建索引让type从ALL变成ref。但有个反直觉的坑有时候执行计划显示驱动表选择错了优化器不是因为算错了代价而是因为统计信息不准。5.7之后有个可以手动干预的办法使用STRAIGHT_JOIN强制指定驱动顺序比如SELECT STRAIGHT_JOIN或者FROM t1 STRAIGHT_JOIN t2。但这是我最后的手段一般先确认索引都建对了、统计信息刷新了再考虑手动干预。5. 日常SQL质量保障把Explain变成团队规范最后分享一点经验之谈。Explain不应该只是出了问题才用的调试工具它应该是SQL上线的硬性门槛。在我带过的团队里所有涉及新查询、新索引变更的代码评审必须要附带EXPLAIN输出而且有几个硬性要求type不允许ALL涉及排序的不允许出现Using filesort涉及大范围聚合的不允许出现Using temporary。不满足的SQL打回重写。为了执行这个规范我自己整理了一个快速检查清单每次REVIEW SQL时对着看是否命中了预期索引possible_keys、key、key_len是否合理rows是否跟实际表数据量匹配如果估算远小于实际警惕统计信息过期。Extra里有没有Using filesort、Using temporary、Using join buffer这些危险信号连表查询里被驱动表的关联字段有没有索引复合索引是不是真的用到了全部需要参与查询的列分页查询是否已经规避了深分页问题这套清单看着简单但真的一步一步打下来能挡掉大部分线上慢SQL。我自己踩过的坑已经够多了像那个订单表复合索引的案例如果早一点用EXPLAIN验证执行计划根本不用等到用户投诉才去救火。另外多说一句Explain的输出毕竟基于优化器的估算它不是万能的。遇到那种“EXPLAIN看着没问题但SQL就是慢得离谱”的玄学问题建议按顺序排查统计信息是否过期、有没有磁盘碎片、锁竞争是不是把查询拖住了、是不是回表次数爆炸。用EXPLAIN ANALYZE实测一下把实际行数和耗时拉出来大部分玄学也就变成玄学了。优化SQL这事本质上就是优化执行计划。而看执行计划又绕不开Explain。把Explain读透了你的SQL调优水平已经有了一半。剩下的一半就是多拿真实业务场景去练看执行计划里每一个字段在一堆烂SQL面前是怎么表现的。这个东西经验积累得越早越值钱。