先问一句你手上有条SQL跑了三秒领导让你优化你第一步干什么打开客户端敲EXPLAIN这个动作90%的人都会。但EXPLAIN结果出来之后呢看到typeALLrows十几万然后呢然后就没有然后了只能去翻书、搜文章最后看了一眼索引就随手加了个索引交差。这就是典型的“EXPLAIN看了但没看懂”。我见过太多人栽在这一步。慢SQL定位这件事SQL本身往往看不出毛病索引也建了一堆问题就出在执行计划这一层——你根本没读懂MySQL到底是怎么执行这条SQL的自然就谈不上精准优化。这讲的内容就是围绕EXPLAIN展开它的输出每一列到底代表什么、怎么从一堆数字和英文词里读出真正的性能瓶颈、遇到疑似低效SQL时按什么顺序做判断。适合那种已经会用MySQL写业务SQL、但一遇到慢查询就不知道从哪下手的同学。看完之后你可以直接拿线上的慢SQL练手把执行计划里那几个关键字段对号入座基本上就能定位出个七七八八。1. 执行计划到底是什么EXPLAIN输出的阅读顺序先说一个我见过的普遍误区很多人拿到EXPLAIN结果第一件事就是去找type、key这两列看到type是ALL就慌看到key不是NULL就安心。这种只看两个字段的习惯会让你漏掉大量信息。EXPLAIN输出的每一行其实都对应优化器选择的一个执行步骤你首先得搞清楚这几个步骤谁先谁后。1.1 先读懂id和select_type执行顺序别搞反EXPLAIN结果里的id列是理解整个执行过程的钥匙。id越大越先执行id相同则从上往下执行。这句话看起来简单但很多人就是记反了。举一个最常见的例子两条SQL一条是带子查询的一条是带JOIN的。带子查询的EXPLAIN经常会看到id分别1和2id2那行会是子查询它先执行然后结果才交给id1的外层查询用。有些人看EXPLAIN就从上往下读以为外层先执行然后去分析外层表走了全表扫描结果分析半天方向全反了。select_type这一列更是重灾区。SIMPLE、PRIMARY、SUBQUERY、DERIVED这些单词都好理解但有两个你千万要注意DEPENDENT SUBQUERY这个单词的意思是“相关子查询”也就是子查询里引用了外层表的字段。这种子查询的实际执行方式是对外层每一行都执行一次。假如外层返回1000行子查询就被执行1000次。这时候EXPLAIN看不太出来但你只要看到DEPENDENT SUBQUERY就要警惕这条SQL的表连接方式可能不太健康能不能改成JOIN或者临时表关联。UNCACHEABLE SUBQUERY意味着子查询结果无法被缓存执行代价更高。常见于子查询中使用了用户变量或某些随机函数。还有DERIVED它在MySQL 5.7之后通常会被优化器尝试物化或合并。你在table列里看到像derived2这样的名字就说明这行操作的是id2那个步骤生成的派生表它并不是你原有业务表之一。1.2 table列里的花活真正的底层操作对象table列看起来最简单就是表名。但除了derived2这种派生表标记你还可能看到union1,2之类的表示UNION合并的结果来自id1和id2两个步骤。还有subquery3这种表示物化子查询的结果被临时存成了内部表。这里想强调一个经验EXPLAIN结果的行数就是优化器认为这条SQL需要做“几步”才能完成。执行步骤越多中间的临时产物越多性能通常越差。这不绝对但作为一个初步判断标准非常有效。1.3 我习惯的浏览优先级拿到EXPLAIN后我自己的习惯是先数行数再看id顺序然后逐行扫type和Extra最后回到key_len、rows去估算量级。这个顺序的好处是先建立对“这条SQL整体执行路径”的认识再落到细节。如果你一上来就盯着单个字段看被某一行特别好看的type迷惑很容易漏掉旁边的Using temporary。2. type列才是优化等级表从ALL到const逐级拆解type列是执行计划里最能快速反映性能的一列但它也最容易被误读。很多人觉得typeindex就是“走了索引”是好事这完全是把词义理解错了。准确点说index代表的是“全索引扫描”不是“索引查找”它的代价通常仅比全表扫描好一点点。2.1 type的完整等级梯度MySQL官方文档里type有很多种取值按照性能从优到劣排大概是这么个顺序type取值含义常见出现场景性能等级system表只有一行系统表极少见最好const通过主键或唯一索引等值命中一行WHERE id 1极好eq_ref被驱动表通过主键或唯一索引等值关联JOIN关联条件的被驱动表很好ref通过普通索引等值匹配可能返回多行WHERE status 1好range索引范围扫描WHERE id BETWEEN 100 AND 200中等index扫描整个索引树覆盖索引但无过滤条件或某些排序场景较差ALL全表扫描无可用索引或优化器选择放弃索引差这里要特别提一下unique_subquery和index_subquery它们其实对应子查询中的IN (SELECT ...)场景前者表示子查询走唯一索引后者表示走普通索引性能介于eq_ref和ref之间。遇到IN子查询时看到这两个type反而是好事说明子查询本身能被索引高效处理比DEPENDENT SUBQUERY强太多了。2.2 ALL和index两种“扫全”的差别ALL指的是扫聚集索引也就是整张表的数据页而index指的是扫某个二级索引的完整索引树。二级索引树通常比表数据小因为只包含索引列和主键所以同样“全扫一遍”index通常比ALL快一些。但注意如果你用的是SELECT *即使用了index扫描二级索引最终还是要回表取完整行此时回表代价可能大到拖垮整体性能。那什么情况下index这种“全索引扫描”会是一个合理选择比如你要取一列的唯一值SELECT DISTINCT category_id FROM product而category_id上有索引那么优化器直接扫索引树就能拿到全部去重值不需要回表这比全表扫描快得多。这种场景EXPLAIN里typeindexExtra里往往还会跟着Using index那是可接受的。2.3 range/ref/eq_ref/const索引到底用到了什么程度const和system是最理想的情况WHERE id 1主键等值查询优化器能直接确定返回一行。eq_ref常见于主键关联SELECT * FROM a JOIN b ON a.id b.a_id如果a作为被驱动表并且关联列是主键a那行type就是eq_ref。ref则对应普通索引等值匹配比如WHERE user_id 888user_id上有非唯一索引会扫索引树找到一个区间内的多条记录。range就是范围条件大于、小于、BETWEEN、IN列表以及LIKE prefix%。出现range大部分时候都算不错但你要看一下rows列如果扫描的区间行数非常大那代价同样可观。2.4 什么时候ALL不一定是坏消息这里想替你纠一个偏看到typeALL不要条件反射地觉得必须加索引。如果一张表总共就一两千行并且查询条件过滤不出什么东西优化器算来算去觉得全表扫描比走索引回表还划算那它就会选择ALL。这种时候你强行为了让EXPLAIN好看去加索引反而可能拖慢写入。我一个实际体会是小表上的全表扫描在秒级内就能完成真正要警惕的是大表上的ALL。判断“大”还是“小”可以看rows列如果rows直接奔着几十万去了而你的业务表又确实有大几十万行那基本可以断定这条SQL未来会随着数据量增长越来越慢。3. key_len、ref、rows联合起来才能看透索引type只能告诉你“走没走索引、大概怎么走的”但真正要回答“索引吃没吃透”你得看下面三兄弟key_len、ref、rows。3.1 key_len的计算方法和意义key_len表示MySQL在索引中实际使用的字节数。注意这个词——“实际使用”。你建了一个联合索引(a, b, c)但查询条件只用了a那key_len就只会等于a字段占的字节数b和c根本没参与索引查找。你只看key列看到用的是你刚建的idx_a_b_c觉得挺满意的但再一看key_len发现跟a字段单列索引的长度一样那这个联合索引建了等于白建后面两列没吃到。那key_len具体怎么算我总结一个简化版规则字段类型字节计算备注INT4字节BIGINT则为8字节TINYINT1字节SMALLINT为2字节VARCHAR(n)n × 字符集字节数 22字节为变长长度标识若字段允许NULL再加1字节CHAR(n)n × 字符集字节数若允许NULL再加1字节可空字段在定长基础上 1因为索引元组要记录NULL标志举个例子假设有一张用户表CREATE TABLE user ( id INT NOT NULL AUTO_INCREMENT, status TINYINT NOT NULL DEFAULT 0, phone VARCHAR(20) NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_status_phone (status, phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;字段字符集是utf8mb4一个字符最多4字节。那么status这个TINYINT占1字节phoneVARCHAR(20)占20×4282字节两者累加联合索引idx_status_phone理论上最长是18283字节。现在跑一条查询EXPLAIN SELECT * FROM user WHERE status 1;如果EXPLAIN结果显示keyidx_status_phone但key_len1那就说明只用了联合索引的第一列status。条件里如果用上了phone即WHERE status 1 AND phone 138...key_len就会变成83说明联合索引的两个字段都生效了。这里还有个细节经常让人困惑为什么不允许NULL的INT还是可能显示5字节确切讲MySQL在索引记录中允许NULL字段要有一个额外的NULL标志位不同版本和引擎实现略有差异这个1字节额外开销在经验估算中通常按“可空字段加1”来算。严谨起见你可以拿真实表跑一下EXPLAIN对比验证。我记得最常见的场景是一个INT NULL的主键关联look的key_len显示5而不是4很多人以为建错索引了其实是因为字段允许NULL。3.2 ref和rows的匹配关系ref列显示的是“使用索引查找时用来与索引比较的列或者常量是什么”。如果是const说明是常量等值条件如果是test.user.id这种说明是用另一个表的id列作为关联条件如果ref里是空值那说明这行并没有使用有效的等值匹配来定位索引。rows则是优化器估算的需要扫描的行数。这里必须强调“估算”二字。它来自统计信息和采样不等于真实值。但rows是判断SQL量级的核心参考如果rows是50万那这条SQL再怎样也快不到哪去。如果rows很小但实际执行还是慢就要怀疑是不是统计信息过期了或者后续有大量回表。你要养成一个习惯把key_len、ref、rows三列连在一起读。比如key是idx_status_phonekey_len1refconstrows8000。这说明查询只用了联合索引的status列定位扫描到了约8000行phone列没用上。那如果要优化思路可能是把条件补全、让联合索引吃到第二列或者单独考虑更贴合查询的索引。3.3 一个完整的联合索引判断实例再给你看一个更真实的例子。商品订单表有一张索引KEY idx_user_pay(user_id, pay_status)。执行计划显示EXPLAIN SELECT * FROM payment_order WHERE user_id 9527 AND pay_status 1 ORDER BY create_time DESC LIMIT 20;执行计划输出精简后idtabletypekeykey_lenrefrowsExtra1payment_orderrefidx_user_pay4const368Using filesort看到key_len4user_id是INT就知道索引的第二列pay_status没有被用于等值定位只定位了user_id一个条件然后扫描368行再排序。但如果你把条件改为不查pay_status先只看WHERE user_id 9527 ORDER BY create_time DESC其实可能更适合建idx_user_create(user_id, create_time)这样连filesort都能省掉。这就是一列一列读EXPLAIN、再反过来审视现有索引设计的过程。4. Extra列里的警报filesort、temporary、ICP各自暗示什么如果说type和key_len告诉你“索引用得怎么样”那Extra告诉你的是“MySQL在索引之外还额外做了什么”。Extra里出现的很多词基本都是优化要动手的地方。4.1 Using filesort排序没走索引代价很容易被忽略Using filesort的意思是这条SQL需要排序但没能借助索引的有序性于是MySQL得自己把结果集放进内存或磁盘排序。它不一定会慢死但只要数据量上去了排序本身的开销非常可观。常见触发场景ORDER BY create_time DESC但create_time没有跟WHERE条件里的列组合成合适的联合索引。举个例子WHERE category_id 5 ORDER BY create_time DESC如果只在category_id上建了单列索引那MySQL会用索引找出所有category_id5的记录然后对这些记录按create_time做filesort。要是这个分类下有几千甚至几万条订单每一次翻页都要全排一遍性能自然就崩了。正确做法是建(category_id, create_time)联合索引让索引天然有序Extra里就不再出现Using filesort。注意联合索引的字段顺序必须是“等值条件列在前、排序列在后”这个顺序写反了排序还是吃不到索引。还有一种常见的隐藏filesort是GROUP BY。MySQL里GROUP BY经常附带排序操作如果不需要排序可以加ORDER BY NULL来消掉不过MySQL 8.0里这个优化方式已经不太需要了优化器更聪明了。4.2 Using temporary临时表出现基本可以判定SQL写得有问题Using temporary意味着MySQL需要创建内部临时表来完成操作。常见于GROUP BY、DISTINCT、UNION以及某些带ORDER BY子查询的嵌套场景。临时表如果小还能在内存里扛一扛一旦数据量超过tmp_table_size或max_heap_table_size就会落到磁盘上那性能就是断崖式下跌。我遇到过一个真实案例一条统计SQL对一张月流水千万级的表做SELECT COUNT(*) FROM ... GROUP BY provinceEXPLAIN里typeALLExtra里既有Using temporary也有Using filesort。这意味着MySQL先把全表扫出来存进临时表再对临时表排序分组。那还谈什么性能后来加了(province, id)的覆盖索引并且改成子查询先去重再做聚合Extra里的临时表和filesort都消失了查询从4秒多降到0.2秒。4.3 Using index condition / Using where / Using index / Using MRR这四个词容易混淆我一并给你说清楚Using index这是好消息代表“覆盖索引”查询所需字段都能从索引树里直接取到不需要回表。你在优化的最高目标之一就是尽量让查询走到覆盖索引。Using index condition这是ICPIndex Condition Pushdown索引条件下推MySQL把部分WHERE条件下推到存储引擎层让存储引擎在读取索引记录时就过滤掉不满足条件的行减少回表次数。这个属于正面的优化手段但要注意它依然可能伴随回表。Using where不算坏消息也不是好消息。它表示存储引擎返回记录后Server层还需要进一步过滤。常见于索引无法完全覆盖所有条件比如WHERE name xx AND status 1只有name列有索引status过滤就要在Server层做。Using MRR多范围读优化存储引擎先收集一批主键再按主键顺序批量回表减少随机IO。看到MRR一般说明MySQL在尝试用工程手段缓解回表压力。我把这些Extra常见值整理成一个速查表方便你对照Extra值性能影响典型SQL形态你该做什么Using filesort负面ORDER BY字段不在索引中调整/新增联合索引Using temporary负面GROUP BY、DISTINCT、UNION改写SQL或建匹配索引Using index正面覆盖索引保持值得追求Using index condition中性偏正部分条件下推确认回表量是否可控Using where中性索引只覆盖部分条件考虑增加过滤列到索引Using join buffer负面JOIN无索引可用给关联字段加索引5. 三个真实慢SQL的执行计划拆解从输出反推根因前面讲的是方法论这一部分我把手头做过优化的三个典型案例po出来每个都带完整EXPLAIN输出和当时的优化动作让你看看“反推根因”到底怎么玩。5.1 案例一深分页的SELECT为什么会把CPU打满原SQL长这样SELECT * FROM payment_order WHERE status 1 ORDER BY id DESC LIMIT 100000, 20;payment_order有三百万行status1的记录约60万条。EXPLAIN输出如下idtabletypekeykey_lenrowsExtra1payment_orderrefidx_status1620000Using index condition乍一看typeref索引也用上了问题在哪在rows620000。因为LIMIT 100000, 20意味着MySQL要从这62万条里先按id倒序找出前100020条然后丢掉前10万条只返回最后20条。也就是说索引虽然命中了status1但深分页让前面的10万次索引遍历全浪费了。解决办法是改成“游标分页”写法把LIMIT offset转换成基于上一页最后一条id的条件SELECT * FROM payment_order WHERE status 1 AND id 100020 ORDER BY id DESC LIMIT 20;这里把上一页最后一条记录的id假设是100020传进来让MySQL直接从id 100020开始扫扫20条就停整体执行次数从62万变成20。改完之后EXPLAIN的rows直接变成20查询从1.8秒变成20毫秒级别。5.2 案例二关联查询驱动表选错执行计划排序才是关键有两条表用户表user50万行、订单表payment_order300万行。业务查询是找出某时间段内注册用户和他们的订单数SELECT u.id, COUNT(o.id) FROM user u LEFT JOIN payment_order o ON u.id o.user_id WHERE u.created_at 2024-01-01 GROUP BY u.id;当时的EXPLAIN第一行table是payment_ordertypeALLrows300万第二行才是usertyperange。这意味着MySQL选择了payment_order作为驱动表user作为被驱动表先把300万行订单全扫出来然后再去关联用户并做分组聚合。这是一条灾难级别的执行计划。为什么优化器会做出这种选择多半是因为统计信息不准或者我们当时在payment_order.user_id上还没有索引导致优化器算不清楚怎么关联更划算。解决办法很直接在payment_order.user_id上加索引同时把SQL改写为内连接并显式让用户表做驱动SELECT u.id, COUNT(o.id) FROM user u JOIN payment_order o ON o.user_id u.id WHERE u.created_at 2024-01-01 GROUP BY u.id;加完索引后EXPLAIN里第一行是usertyperangerows8000左右第二行是payment_ordertyperefkeyidx_user_idrows2左右。整个查询从12秒降到1秒以内。这个案例最有价值的点是你以为SQL写法没问题实际执行计划里驱动表的选择已经出卖了真正的瓶颈。5.3 案例三隐式类型转换让索引静默失效这是一个特别容易踩的坑。表结构里用户手机号字段是VARCHAR(20)SQL写成SELECT * FROM user WHERE phone 13812345678;注意等号右边是数字不是字符串。MySQL在比较时会尝试把字符串字段转成数字这意味着phone列上的索引无法正常使用。EXPLAIN输出idtabletypekeyrowsExtra1userALLNULL500000Using wheretypeALLkeyNULL50万行全扫。只要把条件改成phone 13812345678让类型一致EXPLAIN立刻变成typerefkeyidx_phonerows1。这个改动只需要加一对引号但执行效率差了十万八千里。类似的坑还有WHERE DATE(created_at) 2024-01-01对索引列使用函数导致索引失效。正确写法是范围条件created_at 2024-01-01 AND created_at 2024-01-02。EXPLAIN里type会从ALL变成range。6. 我在真实业务里看EXPLAIN的几条经验这部分不打算给你讲新概念就说点实用的工作习惯和踩坑心得。6.1 先捞慢SQL再谈EXPLAINEXPLAIN的一次输出只针对一条SQL。你要优化的是线上真实的慢查询那就得先从慢查询日志或者performance_schema里把SQL捞出来别看到一条SQL跑得慢就想当然地去猜。我习惯按“平均耗时 × 执行次数”来排序先处理那些虽然单次不算最慢、但高频执行的SQL。一条每秒执行100次的400毫秒SQL危害远大于一条每天执行一次却跑10秒的SQL。6.2 不要只依赖传统EXPLAIN会用TREE和ANALYZEMySQL 8.0.18之后提供了EXPLAIN ANALYZE它会真正执行这条SQL并返回每一步的实际耗时和行数比传统EXPLAIN的“估算值”靠谱太多。比如EXPLAIN ANALYZE SELECT * FROM payment_order WHERE status 1 LIMIT 1000;输出会带上实际执行时间、返回行数、循环次数等信息。当你对EXPLAIN的rows估算有怀疑时就用EXPLAIN ANALYZE去验证一下。此外EXPLAIN FORMATTREE可以把执行计划以树形结构输出阅读起来比表格直观得多特别适合看多表JOIN的嵌套关系。6.3 审查执行计划时我固定检查这几个点每次改完一条SQL后我都有一个固定checklisttype是否从ALL提升到了range/ref/eq_ref如果没有提升先别急着想加索引先问为什么优化器不选。key_len是否覆盖了你的所有等值条件联合索引后面几列到底用没用上这里一眼就能看出来。Extra里是否还有Using filesort或者Using temporary如果有优先解决排序和临时表问题。rows和真实数据量是否在一个量级如果rows显示几万但你觉得应该只有几百检查统计信息是否过期或者条件本身是否有隐式类型转换。还有一条容易被忽略的EXPLAIN的结果是会变的。同样的SQL在数据量增长后、加了新索引后、甚至同一条SQL在不同参数值下执行计划都可能完全不同。所以不要看一次结果就下终身结论每次大版本发布、大促前我会把核心查询的EXPLAIN重新过一遍。说回开头那个场景——如果现在有人再拿一条慢SQL问你EXPLAIN出来typeALL、keyNULL、Extra里一堆filesort你应该能清楚地告诉他这条SQL要优化的不是一个点而是从索引设计到SQL写法的一条完整链路。能看懂EXPLAIN里每一列的含义和它们之间的联动关系才算真正迈进了SQL性能调优的门。我自己这些年优化线上MySQL慢查询几乎没有一次是脱离执行计划靠猜索引猜出来的。实践出真知收藏再多执行计划解读表格都不如你亲手跑一条EXPLAIN再把慢日志里那些“老朋友”逐一拎出来过一遍。