我经常在技术群里看到这类求助有人贴一段EXPLAIN结果说相关列明明加了索引SQL却还是慢得离谱。每次遇到这种问题我基本都会先反问一句你确认过EXPLAIN里type走到了哪一级吗key_len算过没有Extra那一栏有没有出现Using filesort多数人回复我的都是沉默——因为大家习惯了EXPLAIN看一下却很少真正把这张表读完整。这篇文章想做的就是把EXPLAIN这件事讲透每个字段背后对应什么执行逻辑索引优化到底在优化什么以及当一条SQL从3秒优化到30毫秒时中间经历了怎样的排查链路。适合有SQL基础、但还没有系统啃过执行计划的人也适合那些被慢查询折腾过却始终不得其解的开发者。读完你就能具备一个基本能力拿到任何一条慢SQL知道先看什么、再判断什么、最后改什么。1. 一条“走了索引还是很慢”的SQL揭开EXPLAIN的真正价值1.1 先复现一个让人上火的场景假设有一张订单表600万行数据。业务侧反馈用户端我的订单页面打开特别慢。后来DBA抓到一条慢SQLSELECT order_no, amount, status, create_time FROM t_user_order WHERE user_id 1024 ORDER BY create_time DESC LIMIT 20;这条SQL有索引吗有。表上明明建了idx_user_id(user_id)EXPLAIN的结果也显示typerefkeyidx_user_id看起来一切正常。但实际执行就要800多毫秒。你看问题恰恰出在这里索引只是解决了怎么找到user_id1024的数据却没有解决怎么按create_time排序。最终优化器只能从索引里抓出这几千条记录再丢到排序缓冲区里做一次filesort。一个看似走了索引的查询其实在索引之外还偷偷干了一大堆活。1.2 EXPLAIN到底在做什么EXPLAIN的本质是MySQL优化器对这条SQL生成的一份执行计划说明书。优化器会基于表统计信息、索引结构、ref选择方式等估算出若干种执行路径然后选择一个它认为成本最低的方案。EXPLAIN给出的就是这个最终方案的拆解。所以你在EXPLAIN里看到的不是SQL执行后的真实结果而是优化器认为这条SQL该以怎样的步骤执行的预估描述。这个区别很重要——因为它是预估值所以存在失真可能也因为它是过程描述所以能暴露大量执行细节。真正要理解EXPLAIN就要理解优化器的决策逻辑它为什么选这个索引它为什么预估要扫那么多行它为什么宁可全表扫也不走索引1.3 怎么学EXPLAIN才不走弯路很多教程把EXPLAIN讲成了字典让你记住type有几种、Extra有几种。真遇到问题的时候光记住这些名词没用。我自己的经验是读EXPLAIN要带着三个问题这条SQL的访问路径是什么从哪个表开始每个表用没用索引用什么方式访问。索引用到了什么程度是只用了索引的第一个列还是完整用上了联合索引。Extra里有没有额外的操作排序、临时表、回表这些才是性能杀手。带着这三个问题去读EXPLAIN就不再是一张死表格而是一条有逻辑链的执行叙述。接下来我就按这个思路把字段逐个拆开。2. EXPLAIN字段全拆解type、key_len、rows、Extra里的诊断线索2.1 id与select_type你的查询被拆成了几步先看id。它标识的是执行计划中表的读取顺序。id越大越先执行相同id则从上往下执行。碰到子查询、联合查询时id能帮你理清执行嵌套关系。select_type主要有SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION等。其中DERIVED值得留意——如果你的查询里出现了派生表FROM子句的子查询MySQL一般会先把它物化成临时表再用临时表参与JOIN。这往往是一个性能隐患信号。EXPLAIN SELECT u.user_name, tmp.total_amount FROM t_user u JOIN ( SELECT user_id, SUM(amount) AS total_amount FROM t_user_order GROUP BY user_id ) tmp ON u.id tmp.user_id;这种写法常见但性价比很差。遇到应该考虑用JOIN改写或加合适索引消除派生表物化。2.2 type性能阶梯从ALL到const每一级代表什么type是整个EXPLAIN里最值得看的字段。从好到差依次是type含义典型场景说明system表只有一行系统表基本见不到const最多匹配一行主键或唯一索引等值查询最优eq_ref每次驱动表行只匹配被驱动表一行被驱动表用主键或唯一索引连接JOIN场景最优ref非唯一索引等值匹配普通索引等值查询常见的最优range索引范围扫描BETWEEN、IN、、 等可控范围查询index全索引扫描覆盖索引扫全表比ALL好一点但要小心ALL全表扫描无可用索引需要重点优化判断原则很直接type至少到range最好是ref或const。看到ALL基本等于全表扫描index则要确认是不是覆盖索引捞数据如果是覆盖索引倒不算太差毕竟比ALL少了一次回表。2.3 key_len与ref联合索引到底用到了哪几列这两个字段是联合索引诊断的核心。key显示的是优化器最终选中的索引名。possible_keys会列出所有可能用到的索引key只表示实际选的。如果你的possible_keys是空的说明这条SQL根本没有任何索引可走如果possible_keys有值但key为空说明优化器认为即使有索引也不会更快多半是要全表扫。key_len则是被选中索引的字节长度。它的计算规则有固定套路INT是4字节BIGINT是8字节DATETIME在5.6.4之后一般是5字节VARCHAR(50)在utf8mb4下是50×42202字节如果列允许NULL还要再加1字节。可空性、字符集、变长字段头都会影响最终值。为什么要费劲去算key_len因为它能精确告诉你联合索引到底用到了哪几列。比如联合索引(a,b,c)key_len如果等于len(a)说明只用到了第一列如果等于len(a)len(b)说明用到了前两列。尤其是排查为什么索引没完全生效时key_len是唯一的硬证据。ref这个字段则与key配合告诉你索引列是用什么做匹配的。常见的是const等值常量、某个列名与另一列等值连接或者func函数结果看到func要警惕通常意味着索引利用不充分。2.4 rows与filtered读懂优化器的成本估算rows是优化器估算的需要读取的行数。注意它只是一个估算值不是实际扫描行数。但这个数字对判断问题严重程度非常有价值。一条SQL如果rows预估在几十万即使typeref也说明筛选出的记录很多后面一定还有大量回表和过滤操作。filtered表示经过SQL条件过滤后剩余记录的比例单位是百分比。它和rows相乘才是真正要返回给上层操作的数据量。比如rows10000filtered10.00意味着大约有1000行会被保留参与后续操作。这里有个小技巧当你发现rows和实际执行耗时严重不成比例时往往是小部分数据均匀度出了问题或者统计信息过期了。遇到这种情况先执行ANALYZE TABLE更新统计信息再重新EXPLAIN看看。2.5 Extra关键标记隐藏的操作开销都写在这里Extra里出现的字眼往往直接决定这条SQL快不快。需要格外留意几个Using index走了覆盖索引查询所需字段全部在索引里不需要回表。这是非常好的信号。Using where在存储引擎返回记录后Server层再次过滤条件。看到它说明有一部分筛选没有完全下推到索引层面。Using filesort排序操作无法使用索引顺序必须额外排序。这是性能杀手后面会有专门解读。Using temporary使用了临时表常见于GROUP BY或DISTINCT处理也是大开销信号。Using index conditionICPMySQL 5.6引入的索引下推优化表示部分WHERE条件被下推到索引层面提前过滤。这是好信号。这几个标记组合起来能还原一条SQL的大部分行为。下一节我们重点聊聊索引失效的常见场景这些场景多与Extra和type的异常表现挂钩。3. 索引失效的六大典型场景与优化器背后的逻辑3.1 对索引列做函数运算最经典的场景SELECT * FROM t_user_order WHERE YEAR(create_time) 2024;如果create_time上有索引这条SQL基本走不上。原因在于B树索引存储的是原始列值索引的有序性是建立在原始值之上的。你把YEAR(create_time)当条件时优化器无法直接利用create_time的原始排序去定位区间只能把每一行的create_time都取出来计算YEAR值再判断是否等于2024。这等于把索引的快速定位功能完全绕开了。正确写法是SELECT * FROM t_user_order WHERE create_time 2024-01-01 AND create_time 2025-01-01;改写成范围条件后优化器可以直接用B树上的有序性做区间扫描typerange。类似还有DATE(create_time)...、DATE_FORMAT(col,...)...、LENGTH(col)...这类写法都要警惕。3.2 隐式类型转换这个坑比想象中更容易踩。最常见的是电话号码、身份证号这类被设计成VARCHAR的列SELECT * FROM t_user WHERE mobile 13800138000;mobile定义是varchar(11)右边却传了一个整数。MySQL在比较时会把字符串转成数字再比相当于对索引列做了CAST(mobile AS SIGNED)结果又回到函数运算的问题上索引失效。这类问题在EXPLAIN上看不到特别明显的标志type可能还是ref但rows会异常地大。排查时要仔细观察字段定义和传入参数类型是否一致。简单粗暴的解决办法就是写SQL时用引号包起来WHERE mobile 13800138000。3.3 联合索引最左前缀的边界联合索引(a,b,c)查询条件是WHERE b1 AND c2这样a没出现在条件里整个索引基本废掉。这是最左前缀原则的核心约束索引的有序性是先按a排再按b排最后按c排。跳过了a后面的b、c就无法参与连续的索引定位。在MySQL 8.0里推出了Skip Scan优化特定条件比如a的区分度非常低下可以跳跃扫描来部分利用索引但这不是银弹。宁可理解为设计联合索引时要预判查询条件里会稳定出现哪些列把最常等值匹配的列放在最前面。另外有些开发会问WHERE a1 AND b2 和 WHERE b2 AND a1 效果一样吗一样的。优化器会做条件重排最左前缀关心的是「哪些列条件存在」不关心它们在SQL里写的先后顺序。3.4 LIKE前置通配符与%位置SELECT * FROM t_user WHERE user_name LIKE %张%;这种需求很常见但索引真的无能为力。原因和函数运算类似B树索引只能按前缀匹配定位%张%意味着你要匹配的位置不确定无法利用有序性做区间扫描。如果确实要支持包含查询有几个方案一是考虑全文索引或倒排索引二是如果查询模式是张%这种前缀匹配那索引可以正常用三是引入外部搜索引擎。至少要知道LIKE右侧通配符能走索引左侧通配符不能。3.5 OR条件与索引合并SELECT * FROM t_user_order WHERE user_id 1024 OR order_no NO20240601001;假设user_id和order_no各自有单列索引这条SQL可能走不上任何一个因为你用OR把两个条件合并了。MySQL确实有Index Merge优化能让OR两边都走索引再合并结果但两个条件必须都有可用的索引且优化器评估合并成本更划算才会选。常见陷阱是一边条件有索引另一边没有优化器只能全表扫描。遇到OR查询更稳的做法是拆成两条SQL用UNION ALL合并或者改写IN。注意IN在合适情况下可以走range访问这和OR是完全不同的执行路径。3.6 排序、分组与回表成本的权衡ORDER BY create_time LIMIT 10这种查询单独看create_time有索引但如果你还要SELECT其他字段优化器会面临一个选择是按索引顺序读取并回表10行就结束还是先扫全表找出符合条件的数据再排序。多数情况下如果WHERE过滤出来的行数较多且返回行数不固定优化器宁可放弃索引排序。这一类问题在Extra里会看到Using filesort。我的判断思路是优先把排序字段和等值条件组合成联合索引。等值条件放前面排序字段放后面这样既能过滤又能排序彻底消灭filesort。4. 联合索引设计实战从最左前缀到覆盖索引的取舍4.1 联合索引的列顺序决策联合索引是最常用也最容易设计错的索引。设计时核心就一句话等值条件优先范围条件靠后排序字段最后面或与范围条件互换考虑。举个例子。查询经常是SELECT * FROM t_user_order WHERE status 1 AND pay_time BETWEEN 2024-06-01 AND 2024-06-30 ORDER BY pay_time;此时设计索引(status, pay_time)就比单列索引(status)好很多。因为status1先过滤出目标子集pay_time再在这个子集里做范围扫描同时排序也能顺带用上同一棵索引树。如果你把顺序反了索引(pay_time, status)那结果是先按pay_time范围查出大量数据再在这些数据里过滤status。范围查询切断了很多索引匹配的可能后续的status只能一层层回表过滤效率低。4.2 覆盖索引让Extra直接出现Using index覆盖索引是最被低估的优化手段。它指的是查询涉及的所有列都包含在同一个索引里这样查询直接遍历索引就能拿到全部数据无需回表。还是用前面的例子SELECT order_no, amount, status, create_time FROM t_user_order WHERE user_id 1024 ORDER BY create_time DESC LIMIT 20;如果只有idx_user_id(user_id)那么定位到user_id1024的所有记录后每一条都要根据主键回表才能取到order_no、amount、status这些字段。一次回表约等于一次随机读如果这个用户的订单有几千条回表开销就很可观。但如果把索引设计成idx_user_order_cover(user_id, create_time, order_no, amount, status)查询结果全部可以从索引树里拿到Extra会显示Using index成本立刻降下来。不过覆盖索引也有代价索引列越多索引树越大写入和更新时的维护成本越高。通常只在高频核心查询上做覆盖索引不要每个查询都堆字段。4.3 ICP索引下推5.6引入的隐性优化MySQL 5.6之后优化器多了一个索引下推Index Condition Pushdown能力。它在遍历索引时会把部分WHERE条件下推到存储引擎层在索引内提前过滤减少回表次数。比如联合索引(zipcode, lastname)查询SELECT * FROM t_user WHERE zipcode 100000 AND lastname LIKE 张%;不使用ICP时存储引擎按zipcode100000取出所有索引项再逐条回表回表后再过滤lastname。使用ICP后在遍历索引的同一棵树上lastname LIKE 张%的条件在索引内部就被判断不满足的直接不回表。你在EXPLAIN的Extra里会看到Using index condition。这个优化是自动发生的不需要改写SQL。但理解它的存在能帮你解释一个现象有些联合索引看起来违反最左前缀却能部分生效这正是ICP在起作用。4.4 索引基数与前缀索引低区分度列怎么处理索引有一个重要指标叫基数Cardinality表示索引列去重后的值个数。区分度越低比如status只有0、1、2三种值索引选择性就越差。优化器评估时如果觉得用这个索引过滤后还是要回大量行倒不如直接全表扫更快。所以不要给gender、status这类低区分度的列单独建索引收益极小而浪费空间。如果业务必须用这类列过滤优先考虑将它与高区分度列组合成联合索引让高区分度列在前面引导扫描。字符串前缀索引也是一个常用技巧。比如存储用户邮箱email可以在email前10个字符上建索引ALTER TABLE t_user ADD INDEX idx_email(email(10));注意这会导致一些查询无法利用覆盖索引但能显著缩小索引体积。取舍方法先试几个长度值对比区分度变化。5. 完整调优案例复盘一条3秒查询如何一步步降到30毫秒5.1 业务背景与慢SQL我们有个订单宽表t_order_info约600万行。业务方反馈按用户查最近订单的接口超时严重。拿到的核心SQL是SELECT order_no, amount, status, create_time FROM t_order_info WHERE user_id 1024 ORDER BY create_time DESC LIMIT 20;初始表结构如下CREATE TABLE t_order_info ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, order_no VARCHAR(32) NOT NULL, status TINYINT NOT NULL DEFAULT 0, amount DECIMAL(10,2) NOT NULL, create_time DATETIME NOT NULL, KEY idx_user_id(user_id), KEY idx_create_time(create_time) ) ENGINEInnoDB;user_id和create_time各有一个单列索引看起来挺齐全实测却要3秒。5.2 第一轮EXPLAIN全表扫描的诊断直接跑EXPLAINEXPLAIN SELECT order_no, amount, status, create_time FROM t_order_info WHERE user_id 1024 ORDER BY create_time DESC LIMIT 20;关键输出字段值typerefkeyidx_user_idkey_len4rows3520ExtraUsing filesort问题很清楚虽然用上了idx_user_id但user_id1024这一个用户就有3520条订单然后还要对这3520条记录做filesort回表取字段。整体耗时大部分花在排序和回表上。这里我判断直接在现有两个单列索引之间做文章已经无效需要联合索引同时覆盖过滤和排序。5.3 联合索引调整与验证我把两个单列索引改为组合索引(user_id, create_time)ALTER TABLE t_order_info DROP INDEX idx_user_id, DROP INDEX idx_create_time, ADD INDEX idx_user_time(user_id, create_time);再次EXPLAIN字段值typerefkeyidx_user_timekey_len8rows3520Extra(空)key_len从4变成8说明联合索引的两个列都被用到了。Extra里Using filesort消失了因为索引本身就是按user_id再按create_time排序的ORDER BY create_time直接利用索引顺序无需额外排序。实际执行时间从约800毫秒降到约40毫秒。已经能用了但我还想着能不能再进一步。5.4 覆盖索引的进阶尝试与成本权衡40毫秒对多数场景已经及格。但这张表是订单核心宽表查询频率极高我决定做一次覆盖索引尝试ALTER TABLE t_order_info ADD INDEX idx_user_time_cover(user_id, create_time, order_no, amount, status);此时SQL不需要再回表取order_no、amount、status这些字段EXPLAIN的Extra直接变成Using index。实测耗时降到约30毫秒以内且因为不需要回表IO开销大幅下降。这个优化也有代价表中现有600万行新增这么大的联合索引会增加不少存储空间和写入开销。我把这个方案上线前和业务团队确认过读多写多如果订单表写入频繁建议保留上一版(user_id, create_time)联合索引覆盖索引只作为特别高频接口的补充方案。最后实际保留的索引策略是一个(user_id, create_time)联合索引配合业务侧限流核心接口单独做了覆盖索引。整体查询从优化前的3秒降到了稳定30毫秒级别。5.5 EXPLAIN ANALYZE真实执行才能看到的细节MySQL 8.0.18开始提供了EXPLAIN ANALYZE它不只给估算而是真实执行后返回实际耗时和行数EXPLAIN ANALYZE SELECT order_no, amount, status, create_time FROM t_order_info WHERE user_id 1024 ORDER BY create_time DESC LIMIT 20;输出里能看到每个步骤的实际时间比如actual time0.123..0.532 rows20。这个工具最大价值是帮你判断EXPLAIN里的rows估算是否离谱。如果估算行数是3万实际扫了50万那统计信息多半过时了先ANALYZE TABLE刷新再优化。6. 把EXPLAIN变成肌肉记忆我总结的几条索引维护经验用EXPLAIN排查慢SQL这件事熟练之后会形成一套固定的判断节奏。最后分享几条实践经验都是踩坑换来的。第一索引不是越多越好。每次新增索引前先用sys.schema_unused_indexes看看有没有长期没被用到的索引该删就删。冗余索引不仅占空间还会拖慢写入。第二读EXPLAIN时要留意统计信息是否过期。rows如果长期对不上实际行数执行ANALYZE TABLE往往比调整索引更立竿见影。第三上线新SQL之前养成习惯跑一次EXPLAIN。很多慢SQL问题其实在开发环境就该发现类型不匹配、忘记带过滤条件、误用OR这些EXPLAIN一眼就能看出来等线上出问题再去救火成本高得多。第四EXPLAIN输出的是优化器评估的不是实际结果。遇到极端场景用EXPLAIN ANALYZE拿到真实执行数据才知道预估和实际差距在哪里。第五也是最想强调的一点索引优化不是把每个查询都优化到完美而是找到业务高频路径和核心交易的平衡点。覆盖索引很香但索引体积膨胀后的写入开销同样真实存在。做取舍时先看业务吞吐比例再决定索引设计的激进程度。