做 MySQL 性能优化最绕不过去的命令就是 EXPLAIN。不管是线上突然冒出一条慢查询还是新功能上线前评审 SQLEXPLAIN都能把 MySQL 优化器准备怎么执行这条语句的前半程用一张二维表摊开给你看。说白了它就是优化器给你交的底先查哪张表、走哪个索引、预计扫多少行、要不要排序、会不会建临时表。有了这些信息你才知道一条查询到底慢在哪里而不是靠猜。我第一次接触 EXPLAIN 时也被那一堆字段唬住了。后来发现真正需要优先看懂的字段其实没有那么多关键是 type、key、rows、Extra 这几列再配合 select_type、ref、filtered 理解完整执行过程。这篇文章我会从字段含义讲起再带一个完整的索引优化案例最后回归数据库设计把 EXPLAIN 和建表、索引规划串成一条线。适合刚入门 MySQL 的开发者也适合那些 EXPLAIN 看过很多次但还没形成排查体系的同学。1. 项目概述为什么慢查询排查迟早要学 EXPLAIN1.1 EXPLAIN 是 MySQL 优化器的体检报告很多人把 EXPLAIN 当成慢查询出现之后才用的工具这没错但格局小了。实际上新 SQL 上线之前、表结构变更之后、大促前容量评估阶段都可以先铺一遍 EXPLAIN。线上慢查询是果设计阶段的隐患是因EXPLAIN 能把因和果都暴露出来。我见过太多项目平时不关注执行计划等到线上接口超时才想起来看结果发现一条关联查询全表扫描拖垮了整个库。与其这样不如把 EXPLAIN 当成日常开发里的体检报告定期做、按需做、变更后必做。具体命令很简单在 SELECT、INSERT、UPDATE、DELETE 前加 EXPLAIN 前缀就行MySQL 8.0 还支持EXPLAIN ANALYZE得到真实的执行时间和行数。需要注意EXPLAIN 本身并不真正执行查询EXPLAIN ANALYZE 除外它只是让优化器生成执行计划所以可以放心使用。这个特性非常友好哪怕是在生产环境你也可以直接跑 EXPLAIN 看计划不会给数据库带来额外压力。1.2 什么情况下应该使用 EXPLAIN我总结了几类典型场景你可以对照自己的工作情况新 SQL 评审接需求时要写复杂的关联查询先 EXPLAIN 看执行计划别等线上报警。索引调整后验证加了索引、删了索引用 EXPLAIN 对比前后 rows 和 type。数据量增长后的回归同样一条 SQL数据量从 10 万涨到 1000 万执行计划可能完全变了。排查死锁和超时虽然 EXPLAIN 不直接看锁但大面积扫描会让锁范围扩大间接导致锁等待和死锁。有人会问DBA 工具或监控平台不是也能看执行计划吗确实像慢查询日志、performance_schema 都能拿到信息但 EXPLAIN 是门槛最低、最直接的一步。你不需要安装任何额外组件只要会写 SQL就能用 EXPLAIN 查看优化器意图。越是复杂的库越要学会用这个基础工具建立自己的判断力。2. EXPLAIN 输出字段逐项拆解2.1 id、select_type执行顺序从哪里看EXPLAIN 结果里每一行代表一个执行单元第一列 id 就是这个单元的唯一编号。多数简单查询只有一个 id但如果 SQL 里出现子查询、UNION就会有多行。id 的排序规则很简单id 越大越先执行id 相同则从上往下执行。看到 id 顺序基本就能脑补出 MySQL 是先算哪部分。比如一个 SQL 里有子查询子查询的 id 通常比外层查询大说明优化器会先执行子查询拿到结果后再去驱动外层查询。select_type 描述每一行的查询类型常见值有 SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION 等。我平时最关注的是 DERIVED 和 DEPENDENT SUBQUERY。DERIVED 代表派生表通常是把子查询结果当临时表再参与关联这类操作如果数据量大很容易变成性能瓶颈DEPENDENT SUBQUERY 意味着子查询依赖外层表每查一行都可能要执行一次需要特别警惕。遇到 select_type 很复杂的 SQL第一反应不是调索引而是考虑能不能改写 SQL把子查询拆开或者用 JOIN 替代。2.2 table、partitions确定物理对象table 列显示这一行对应的是哪张表有时候会带上别名比如o就表示 orders 表的别名 o。partitions 列是 MySQL 5.7 开始引入的表示查询命中了哪个分区没分区则是 NULL。分区的价值在于“裁剪”查询条件如果能落到少数分区扫描的数据量会大幅下降。但分区表并不总是越快如果查询条件里不带分区键优化器无法裁剪分区就得把所有分区都扫一遍这种场景下分区表反而可能比普通表更慢。真正常见的坑是表名看起来很眼熟但实际不是你想的那张。比如多表 JOIN 时table 列会显示每行的表名结合 id 和 select_type 才能确定执行顺序。如果你看到某行是 DERIVEDtable 列往往是一个临时表名比如derived2这时候你关心的不是临时表结构而是它来源子查询的执行效率。很多人一看到衍生的临时表就去查字典其实第一步应该反推回原始 SQL看是哪一段子查询产生了它。2.3 type访问类型决定扫描量级type 是整个 EXPLAIN 结果里信息量最大的一列它表示 MySQL 找到目标行的方式。常见取值从好到差大致是 NULL、system、const、eq_ref、ref、range、index、ALL另外还有 fulltext、ref_or_null、index_merge、unique_subquery 等高频场景之外的取值。很多人喜欢背这张优先级表但说到底它反映的就是“扫描数据越来越少”的排列组合。ALL全表扫描最该警惕通常是没建索引或者索引没被选上。index扫描了整个索引树数据量也不小但比 ALL 强因为索引一般比表小加载成本更低。range索引范围扫描比如 BETWEEN、、、IN 列表这是比较可以接受的状态。ref非唯一索引等值匹配比如WHERE user_id 100user_id 上有普通索引。eq_ref唯一索引等值匹配通常出现在 JOIN 中使用主键或唯一索引关联效率很高。const、system最多匹配一行主键或唯一索引等值查询就是这种状态。如果你在优化一个查询type 从 ALL 变成 range说明索引已经生效如果变成 ref 或 eq_ref通常性能已经非常好了。当然type 是优化器基于统计信息的判断不是真实执行结果后面我会专门讲怎么验证。2.4 possible_keys、key、key_len真正用的是哪个索引possible_keys 列出这个查询可能用到的索引key 列出优化器实际选中的索引。两者不一致时不见得是优化器蠢更多时候是“能用”不等于“好用”。比如有一个复合索引(status, created_at)WHERE 条件只有 created_at优化器可能就不选因为索引前缀没对上走这个索引还不如全表扫。key_len 是我个人最喜欢看的一列它表示实际使用的索引字节长度。索引能覆盖的查询条件越多通常 key_len 越大但索引本身占用空间越大写入成本也越高。看 key_len 有个很实用的技巧如果你建了复合索引某个查询的 key_len 只使用了前缀字段的长度说明后面字段没用上这时候要考虑调整索引顺序或者改写查询。关于字节计算常见类型可以这么记int 占 4 字节tinyint 占 1 字节bigint 占 8 字节datetime 在 MySQL 5.6.4 以上无小数秒时占 5 字节varchar 还要根据字符集计算并额外加 2 字节存储长度信息允许 NULL 的字段还会多 1 字节标记。2.5 ref、rows、filtered估算工作量与筛选比例ref 列表示 key 列使用的索引与哪个值做比较常见值是 const常数、某个列名、func函数返回值。看到 ref 是 func 就要多留个心眼比如WHERE DATE(created_at) 2024-01-01对索引列套了函数索引很可能失效。这种问题在代码里特别隐蔽你以为建了索引就万事大吉实际上函数让索引根本没机会用上。rows 是优化器估计需要扫描的行数注意是估算不是精确值。它是判断查询代价最直观的数字之一。如果有 JOIN每一行的 rows 表示在该执行步骤里扫描的行数不是最终结果集行数。filtered 表示按查询条件过滤后剩余行数的百分比比如 20 意味着预计扫描 100 行过滤后剩下 20 行。JOIN 时通过 filtered 可以推算最后关联的行数filtered 过低说明过滤条件不够有效需要调整 WHERE。2.6 Extra隐藏的执行细节都在这里Extra 列经常出现 Using index、Using where、Using temporary、Using filesort、Using index condition、Using join buffer 等每个信息都对应一种执行细节。Using index覆盖索引命中查询的所有字段都能在索引中找到不用回表这是最理想的。Using where存储引擎层返回后再由 MySQL 服务层过滤常见于条件里含有无法在索引中直接判断的列。Using index condition走的是索引下推。比如复合索引(name, age)查询WHERE name LIKE 张% AND age 18MySQL 会先利用索引里的 name 过滤再把满足 name 的行传给存储引擎用 age 判断减少回表。Using temporary查询需要临时表常见于 GROUP BY、ORDER BY 和 DISTINCT 组合不当。Using filesort需要额外的排序操作。注意 filesort 不一定是磁盘排序可能内存排序但只要出现它查询就有额外成本。一条 SQL 的 Extra 里如果同时出现 Using temporary 和 Using filesort几乎可以断定排序和分组写得有问题十有八九要重构。3. 实操从 EXPLAIN 到索引优化跑通一个完整案例3.1 准备一张测试表和两种查询理论说再多不如跑一遍。我这里模拟一个电商订单表字段简单但能说明问题CREATE TABLE orders ( id int NOT NULL AUTO_INCREMENT, user_id int NOT NULL, order_no varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, status tinyint NOT NULL DEFAULT 1, total_amount decimal(10,2) NOT NULL DEFAULT 0.00, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;注意我在建表时已经给 user_id 建了普通索引实际项目里也会这么做因为订单表查询用户订单是很高频的操作。现在只造了基础索引没给 created_at 建索引也没考虑 status 和 created_at 的复合索引。下面这条查询按月份统计订单总额SELECT user_id, SUM(total_amount) AS amt FROM orders WHERE status 2 AND created_at 2024-01-01 AND created_at 2024-02-01 GROUP BY user_id;先直接执行 EXPLAINEXPLAIN SELECT user_id, SUM(total_amount) AS amt FROM orders WHERE status 2 AND created_at 2024-01-01 AND created_at 2024-02-01 GROUP BY user_id;在没有额外索引的情况下type 肯定先是 ALLrows 是整张表的数据量Extra 里大概率有 Using where。为什么优化器宁可全表扫也不走 idx_user_id因为这条 SQL 的 WHERE 根本没用到 user_id它没有可用的索引前缀当然只能 ALL。哪怕表里有别的索引只要条件列和索引前缀对不上优化器就会选择全表扫描。3.2 第一步让时间条件走索引既然查询条件是 status 和 created_at最直接的优化是给 created_at 建单列索引ALTER TABLE orders ADD INDEX idx_created_at (created_at);再执行 EXPLAINtype 大概率变成 rangekey 是 idx_created_atrows 缩小到满足时间范围的行数Extra 会多一个 Using index condition 或 Using where。这说明查询已经迈过“全表扫描”这个坎了。但只看成功率还不够。假设这个表有 2000 万行一个月的数据可能有 200 万行range 扫描 200 万行依然不轻松尤其是后面还有 GROUP BY user_id。这时候你需要问自己能不能让查询直接定位到更少的行status 2 是一个过滤性很差的等值条件单独给 status 建索引没有意义但如果把它放在复合索引的最前面(status, created_at)就可以同时利用等值和范围条件。3.3 第二步复合索引顺手优化排序和回表把单列索引删掉改成(status, created_at)ALTER TABLE orders DROP INDEX idx_created_at; ALTER TABLE orders ADD INDEX idx_status_created_at (status, created_at);再 EXPLAIN 一次key 变成 idx_status_created_attype 还是 range但 key_len 变长了说明索引用到的列多了。更关键的是查询条件 status 是等值created_at 是范围这种组合能充分发挥复合索引的价值。如果查询还需要 GROUP BY user_id而 user_id 不在索引里Extra 里大概率还是会冒出 Using temporary。把 user_id 加进复合索引例如(status, created_at, user_id)有一定概率让分组利用索引顺序减少临时表成本但并不是百分之百生效优化器版本和执行计划都会影响最终结果。更进一步如果你希望 Extra 里出现 Using index也就是覆盖索引可以把查询里出现的 user_id、total_amount 也一并考虑进去。不过 total_amount 是 decimal 类型索引宽度会明显变大写入成本跟着上升。这个案例看起来很简单但它包含了 EXPLAIN 优化的核心思路先看当前执行计划缺什么再改索引或 SQL每次改动都用 EXPLAIN 验证而不是凭感觉。索引不是越建越多越好每增加一个索引写入都会多一分成本只保留能实际降低 rows 的索引才是正解。4. 常见问题与排查技巧EXPLAIN 实战中的坑4.1 为什么 type 已经是 range查询还是慢很多人看到 EXPLAIN 里 type 不是 ALL 就觉得万事大吉实际踩过坑才知道range 也分好坏。如果你圈定的范围太大比如时间跨度覆盖了全表 80% 的数据那 range 扫描的行数跟 ALL 差不了多少。另一个常见情况是 rows 估算严重偏离实际统计信息过期了这时候先执行ANALYZE TABLE orders;更新统计信息再跑 EXPLAIN。还有一种情况是回表成本被低估。走二级索引查到主键之后如果查询需要回表读取其他列InnoDB 每读一行都可能是一次随机 IO几万行回表就会明显变慢。这时候重点看 Extra 里有没有 Using index如果查询字段都能塞进索引改成覆盖索引往往立竿见影。我习惯把这类问题整理成一张速查表方便团队里其他同学快速定位现象可能原因优先排查方向typeALL条件列无索引或索引前缀不匹配检查 WHERE / JOIN ON 字段是否有合适复合索引typerange 但很慢范围跨度过大、回表过多看 rows 和 Extra必要时改覆盖索引出现 Using filesort排序字段与索引顺序不一致调整索引或 SQL让 ORDER BY 走索引出现 Using temporaryGROUP BY / DISTINCT 与索引不匹配重写 SQL或调整索引列顺序possible_keys 有索引但没用索引选择性不高或统计信息失真ANALYZE TABLE或强制验证特定索引key_len 过短复合索引只用了前缀列调整条件顺序让更多索引列生效4.2 EXPLAIN 显示走了索引但是未命中理想前缀复合索引(status, created_at, user_id)你执行WHERE created_at 2024-01-01EXPLAIN 展示 key 是复合索引key_len 却可能只有一小段。这个细节很容易被忽略你以为走了索引实际上只是走了不完整的索引。判断方法我前面说过key_len 越小说明索引列参与越少。如果查询本来能用 status 等值过滤却发现条件里没有 status那就要回头检查 WHERE 条件的顺序和写法。需要特别提醒的是MySQL 优化器对复合索引的利用遵循最左前缀原则这个原则在实践中非常容易出现误解。很多人以为只要 SQL 里包含索引的第一个列就行其实优化器更关心的是能否用上索引中的连续前缀列做等值、范围或排序。所以设计复合索引时要把等值条件放前面、范围条件放后面并且尽量让 ORDER BY、GROUP BY 字段能使用索引排序。4.3 Using filesort 和 Using temporary 不一定能用索引解决我见过很多新手看到 Extra 里有 Using filesort反手就加一个排序列的索引结果发现没效果。原因在于filesort 的消除不仅依赖索引里有排序列还要求前面的等值、范围条件都满足索引的最左前缀并且排序方向和索引定义一致。比如索引是(status, created_at ASC)你执行ORDER BY created_at DESC在 MySQL 8.0 之前未必能倒序扫索引8.0 之后才支持一定程度的倒序索引优化。GROUP BY 也一样。GROUP BY user_id如果 user_id 不在当前索引里优化器很可能用临时表。但有时候临时表是源于查询本身的数据处理逻辑比如GROUP BY user_id, status而索引顺序是(status, user_id)这时候调整索引顺序比加索引更有效。遇到这种问题别急着改索引先试着把 SQL 改写得更贴合已有索引结构或者把分组字段的顺序和索引保持一致。4.4 老版本 MySQL 与新版 EXPLAIN 的差异不同 MySQL 版本EXPLAIN 字段和语法有细微差别。老版本没有 partitions、filtered 列MySQL 8.0 增加了EXPLAIN ANALYZE和FORMATJSON后者能给出每个步骤的耗时估算。如果还在用 5.6、5.7很多优化手段会有局限比如索引下推是 5.6 引入的但旧版本的 EXPLAIN 看不出细节。同一台服务器升级或降级版本后同一个 SQL 的 EXPLAIN 可能都不一样这一点在做性能对比时必须注意。我在实践中更推荐直接使用 MySQL 8.0它除了 EXPLAIN 增强还支持窗口函数、公共表表达式很多原本需要临时表绕行的 SQL 可以写得干净利落对优化器也更友好。如果你所在环境仍然以 5.7 为主也完全可以把文中的字段含义和优化思路平移过去大部分结论是通用的。5. 结合数据库设计与其事后调索引不如设计时留好余地5.1 从字段设计阶段预测 EXPLAIN 结果EXPLAIN 不是调优工具更像一张答卷分数在表结构设计阶段就定下了一半。我见过太多订单表把所有用户行为属性塞进一个 JSON 字段查询某个业务维度时要先全表扫描再用 JSON 函数过滤EXPLAIN 里不出现 ALL 才怪。数据库设计的核心原则之一是让高频过滤字段有明确的数据类型和索引空间。比如状态字段如果取值范围只有 0、1、2用 tinyint 就够了别用 varchar(20) 存 PAID、REFUND 这种字符串。字符串不仅占用空间索引长度变大相等比较也没有整数高效。同样日期字段不要为了展示方便存成 varchar否则你用范围查询时即使建了索引也发挥不出 datetime 的类型优势EXPLAIN 里可能出现函数转换索引命中率很低。字段类型选对了索引才能长在合适的土壤上。5.2 索引规划要为已知查询模式服务设计索引之前先列出现有业务里高频的查询模式而不是等到 B 端反馈慢才开始补救。我习惯用一张表把公共查询条件、排序字段、分组字段都列出来然后设计尽可能少的复合索引去覆盖这些模式。EXPLAIN 就是验证索引设计是否合理的手段但如果你根本没有索引设计清单后续就只能不断打补丁。这里有一个实用的设计原则能合并的索引尽量合并。比如你有KEY (status)和KEY (created_at)又有WHERE status ? AND created_at BETWEEN ?那就应该考虑KEY (status, created_at)而不是保留两个单列索引。单列索引在等值查询时表现不错但如果涉及多条件联合过滤优化器通常只能选择其中一个走另一个条件要回表过滤效率打折扣。通过 EXPLAIN 观察 possible_keys 和 key你能看到优化器每次是怎么做选择的。5.3 大表一定要考虑数据生命周期数据库设计里最容易被忽略的是“表随时间变大”。同样的索引在小表上是 ref在大表上可能被优化器放弃选择因为全表扫描的成本变成更优解了。EXPLAIN 常被用于当前数据量下的排查但你得知道半年后数据翻倍执行计划很可能不一样。应对方式无非几种按时归档历史数据、用分区表把冷热数据分开、或做读写分离。分区表的分区键要与查询条件严格匹配否则 EXPLAIN 的 partitions 列会告诉你它扫了所有分区那是一种比全表扫描更尴尬的情况。比如订单表按 created_at 做 RANGE 分区查询 WHERE 条件没有 created_at优化器无法裁剪分区所有分区都要进来开销不会比全表扫描小。所以不是“建了分区就快”要让查询条件天然携带着分区键。这些思考说起来有点偏数据库架构但它和 EXPLAIN 是互相印证的。你设计一张表时心里应该能预判出主要查询在 EXPLAIN 里会长什么样一旦实际结果和预判差异很大就要回到设计层面找原因。6. 一些踩坑之后的真心话关于 EXPLAIN我还有几句压箱底的话想送给做数据库维护的同学。它不是一个看完就丢的执行计划更像一张记录优化器决策的现场照片。你不仅要会看还要学会预判它什么时候会变。6.1 执行计划是会“漂移”的EXPLAIN 不是一劳永逸的结果。昨天还走 idx_created_at 的查询今天可能因为数据量上涨或者统计信息变化突然变成全表扫描。我在排查线上一个定时报表时遇到过这种情况同样的 SQL白天 EXPLAIN 正常凌晨大促后却慢了几十倍。后来发现那张订单表在夜里被批量任务频繁更新统计信息失效优化器对行数的估算严重偏高于是放弃了本该走得很好的索引。解决方法是定期ANALYZE TABLE同时对大表查询尽量用更贴近真实分布的过滤条件而不是只依赖一个低基数等值条件。6.2 让 EXPLAIN 成为团队代码的一部分我还有一个习惯在建立或调整索引后把对应 SQL 的 EXPLAIN 结果以注释形式写到 Mapper 或模型文件里。比如在 SQL 注释中记录# explain: typeref, rows120, keyidx_status_created_at。这样下次别人接手时不用重新跑一遍也能知道这条查询的预期执行计划。一旦线上环境数据量变化导致执行计划漂移通过对比注释里的预期值就能快速发现问题。这个习惯听起来很小但在团队协作里比任何规范文档都管用。代码会说话执行计划也是把它留在代码旁边是最好的文档。6.3 把 EXPLAIN 当快照别当最终答案EXPLAIN 是优化器给你的一张计划快照它能告诉你怎么查但不告诉你每一行处理会不会真的快。真正下结论之前我还会结合慢查询日志、EXPLAIN ANALYZE、performance_schema三者交叉验证。如果你只盯着 type 是不是 ref很容易漏掉回表次数和排序开销。我自己的排查顺序是先看 rows 判断扫描量级再看 Extra 判断有没有排序和临时表最后才回头看有没有选错索引。一次排查里如果同时出现多个问题优先处理 rows 最大的那一环通常收益也最大。