做MySQL性能排查这件事我这几年前前后后做过不下几百次。不管是线上慢查询报警还是接手一个老项目发现列表接口卡成幻灯片我的第一步几乎永远是同一个打开MySQL的EXPLAIN把SQL的执行计划拉出来看一眼。EXPLAIN就是一条SQL的“体检单”——优化器打算怎么扫描表、打算用哪个索引、大概要扫多少行一屏就能看个大差不差。对于做数据库设计的同学来说EXPLAIN更是验证索引设计是否合理的捷径建完索引之后跑一条EXPLAIN设计有没有踩坑立刻原形毕露。这篇文章我想把EXPLAIN的每个字段、每个级别的含义掰开揉碎讲清楚再结合我实际优化中反复用到的思路给你一套可以直接抄作业的检查方法。刚入门MySQL的开发者可以把它当字典被慢查询折磨过的老手也能从中间歇性找到一些之前忽略过的细节。1. 先搞懂EXPLAIN在干什么执行计划是怎么来的1.1 优化器不是执行器EXPLAIN是“预估单”很多刚接触EXPLAIN的同学会以为它真的把SQL跑了一遍其实不是。MySQL收到一条SQL之后会先做语法解析、语义检查然后交给优化器生成执行计划这个计划决定表与表的连接顺序、是否使用索引、索引选择哪一棵、每张表大概扫多少行、是否需要临时表和排序。EXPLAIN展示的就是这个“执行计划”它不会真正去读用户数据所以线上环境直接跑EXPLAIN是安全的不会产生锁、不会修改数据、也不会拖垮数据库。但要注意优化器不是神仙它生成计划时依赖的是表统计信息、索引统计信息和成本模型数据量变化、统计信息过期、内存参数调整都可能导致同一条SQL在不同时间花不同的执行路线。这也是为什么我习惯把EXPLAIN当成“当前时刻的体检单”而不是“永久结论”。从数据库设计的角度看EXPLAIN其实是在帮我们回答一个核心问题你为业务设计的索引优化器到底认不认账。如果表结构和索引设计得很合理EXPLAIN会给你漂亮的type、合理的rows、干净的Extra如果设计有问题EXPLAIN会毫不留情地告诉你全表扫描、文件排序、临时表这些坏信号。1.2 EXPLAIN三种最常用打开方式最基础的用法就是在SELECT关键字前面加EXPLAIN比如EXPLAIN SELECT order_no, amount FROM orders WHERE user_id 1024 AND status 1;默认输出是一张多列的表格。字段太多的时候终端里会被折行折得很难看我建议使用\G结尾让每一列独占一行EXPLAIN SELECT order_no, amount FROM orders WHERE user_id 1024 AND status 1\G如果想看得更细可以用JSON格式输出结果里包含了优化器估算的成本信息量比普通表格大得多EXPLAIN FORMATJSON SELECT order_no, amount FROM orders WHERE user_id 1024 AND status 1;MySQL 8.0.18之后还有一个EXPLAIN ANALYZE这个才是真正把SQL跑一遍然后返回每一步的实际执行时间和实际返回行数。它比普通EXPLAIN更接近真相但因为真跑所以生产环境要谨慎使用尤其是大查询或者写语句。下面的讲解会围绕一个统一的示例表展开我们假设有一张电商订单表CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL, user_id INT NOT NULL, status TINYINT NOT NULL DEFAULT 0, amount DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL, KEY idx_user_status (user_id, status), KEY idx_created_at (created_at) ) ENGINEInnoDB;这张表里有几万行数据索引一个联合索引idx_user_status(user_id, status)一个单列索引idx_created_at(created_at)。后续所有例子都围绕它展开这样你复现的时候思路能对上。2. EXPLAIN字段逐个拆解每一列到底在说什么2.1 id与select_type搞清楚SQL里有多少个“小动作”先说id。它表示执行计划中每一步的编号。同一个查询块内的多张表id通常是相同的子查询、UNION等复杂结构会产生不同的id。优化器的执行顺序有一定规律id越大越先执行id相同的一般从上往下看id为NULL的通常是最后一步比如UNION RESULT就常见id为NULL。举个例子EXPLAIN SELECT id FROM orders WHERE user_id (SELECT user_id FROM orders WHERE order_no20240101001);这个SQL里外层查询的id通常是1内层子查询的id可能是2执行顺序先跑id为2的子查询再跑id为1的外层查询。select_type告诉我们这一步在干哪种活常见的有select_type含义SIMPLE没有子查询、没有UNION的简单查询PRIMARY最外层查询当一个查询包含子查询时外层标记为PRIMARYSUBQUERY子查询本身通常先于外层执行DERIVEDFROM子句中的派生表优化器可能物化成临时表UNIONUNION中的第二个或之后的SELECTUNION RESULTUNION合并结果的最终步骤DEPENDENT SUBQUERY依赖外层结果的子查询通常意味着每取一行外层结果就执行一次子查询要警惕MATERIALIZED子查询结果被物化成临时表后再参与连接这里特别想提醒一句看到DEPENDENT SUBQUERY要打起精神它往往是相关子查询执行次数可能等于外层扫描行数很容易变成慢查询的根源。能用JOIN或者EXISTS改写的时候优先改写。2.2 table与partitions知道每一步在动哪张表table列显示的是这一步访问的表名或派生表名。如果SQL里写了别名这里显示的是别名。看到derived2这类名字代表它访问的是id为2的那步生成的结果比如FROM子句的子查询。看到union1,2表示它合并了id为1和id为2的两个结果集。partitions列在MySQL 8.0里默认会显示用来判断分区裁剪是否生效。如果你查询分区表这里会列出实际需要访问的分区如果能裁剪掉大量分区这里的分区数量就很少。没用到分区表时这一列是NULL。读这两列时我的习惯是判断“驱动表”是谁。一般来说EXPLAIN里第一行对应的表就是整个查询的驱动表在JOIN中尤其重要。驱动表的扫描方式直接决定了整个连接过程的复杂度。2.3 type整张表里最该先看的一列type描述的是访问类型也就是MySQL在表里定位数据的方式。官方文档把type从好到差排列我直接给一张速查表type含义健康度system表只有一行是const的特例极优const根据主键或唯一索引等值匹配最多一条极优eq_ref被驱动表通过主键或唯一索引等值匹配每次只读一行优ref通过普通索引等值匹配可能返回多行良range通过索引做范围扫描包括 BETWEEN、IN、、 等良index扫描了索引树的全部叶子节点但不需要回表的情况一般ALL全表扫描差看到ALL和index要本能地警觉但也不要一竿子打死。几百行的小表全表扫描成本可能比走索引还低优化器选ALL反而合理大数据量表上出现ALL基本可以判断查询需要优化了。index虽然扫描的是索引但本质上是把整棵索引树都过了一遍如果查询需要的数据量很大、又恰好能覆盖那还说得过去否则也是需要警惕的信号。2.4 possible_keys、key、key_len、ref索引到底用没用上possible_keys列出这张表理论上可能用到的索引key表示优化器最终选中的索引key_len表示它实际用到索引的字节长度ref表示与索引列进行比较的列或常量。这里有几个关键点列表里有索引不代表它被使用了以key为准。possible_keys是优化器的候选名单可能因为成本更高等原因被放弃。key_len特别能揭示联合索引用了多少列。比如idx_user_status(user_id, status)里两个列都是NOT NULL的整数user_id占4字节status占1字节key_len5就表示两列都用到了如果key_len4说明只用了user_id这一列。能用varchar的时候key_len计算更复杂。utf8mb4字符集下一个字符最多占4字节varchar还需要额外的2字节存长度如果列允许NULL通常还要再加1字节。比如order_no VARCHAR(32) NOT NULL在utf8mb4下key_len一般是32*42130看到130说明索引用了完整列看到128就可能是字符集或字段差异造成的认知偏差。ref列可以帮助确认比较方式显示const表示索引列在和常量比较显示某个字段名表示索引列在和另一个表的列比较。如果两张表关联时字段类型不一致ref可能显示为NULL但type大概率会变得很差字符集差异导致的隐式转换经常在这里露出马脚。2.5 rows、filtered、Extra预估成本与附加操作的判定rows是优化器估计的需要扫描的行数它是一个估算值不是真实行数。优化器在做成本比较时很依赖这个数字但它来自统计信息和采样偏差有时候非常大。单独看rows意义有限把它和type、key_len放一起看才有意义。filtered表示通过索引条件定位后在存储引擎层返回的数据里还有多少比例的数据满足剩下的WHERE条件。比如rows100filtered20.00意味着优化器预估有20行能通过其他过滤条件。这个值越低说明剩余条件选择性越强优化器也可能因此选择不同的连接顺序。Extra是信息量最大的一列很多关键操作都会在这里提示Using index表示覆盖索引查询不需要回表效率高Using where表示MySQL在服务层对存储引擎返回的数据又做了一次过滤Using index condition表示用到了索引下推ICP在存储引擎层就用索引过滤掉一部分行Using temporary表示用了临时表常见于GROUP BY、ORDER BY、DISTINCT等操作Using filesort表示需要额外的排序操作可能发生在内存也可能落盘大批量排序时要小心Using join buffer表示JOIN没有使用索引性能往往不好。如果一只查询里同时出现Using temporary和Using filesort比如GROUP BY status ORDER BY amount那基本就是典型的优化目标有没有可能通过设计联合索引让排序和分组都顺着索引顺序走把这两项从Extra里干掉。3. type级别看懂之后EXPLAIN才算真正入门3.1 不同type在真实业务里的表现差距我把type比作“导航路线”同样是从A点到B点const相当于出门就到ref相当于全程高速range相当于部分高架ALL相当于走村级小路。数据量小的时候走村级小路也无所谓数据量一大差距就是毫秒和秒级的区别。实际操作中我一般把const、eq_ref、ref作为目标range作为底线index和ALL必须有理由。一个查询如果typeALL但表只有几万行且查询频率极低那优化带来的收益也有限应该去优化真正高频的查询。3.2 几个典型EXPLAIN场景一眼看出好坏假设orders表数据量在10万行左右。场景一全表扫描没有任何索引可用。EXPLAIN SELECT * FROM orders WHERE status 1;由于我们在status上并没有独立索引联合索引idx_user_status又因为缺少user_id无法触发最左前缀优化器大概率只能走全表。EXPLAIN结果的关键列就是typepossible_keyskeyrowsExtraALLNULLNULL100000Using where场景二等值查询命中联合索引。EXPLAIN SELECT * FROM orders WHERE user_id 1024 AND status 1;这次where里带上了user_id联合索引idx_user_status的完整两列都在等值条件下EXPLAIN结果就是typepossible_keyskeykey_lenrefrowsrefidx_user_statusidx_user_status5const,const8type是refkey_len5说明联合索引两列都生效回表行数很少性能很好。场景三覆盖索引连回表都省了。EXPLAIN SELECT id, user_id, status FROM orders WHERE user_id 1024 AND status 1;查询需要的三个列都在idx_user_status里优化器直接扫索引就够用Extra会显示Using index这种状态是EXPLAIN能给出的最好评价之一。场景四范围查询。EXPLAIN SELECT * FROM orders WHERE user_id 1024 AND created_at 2024-06-01;由于created_at有自己的索引idx_created_at对user_id等值、对created_at做范围优化器可能在两个索引中选一个。如果用created_at索引type会变成rangerows会偏大如果用user_id索引type可能是ref但需要额外过滤created_at。到底选哪个是由成本模型决定的所以EXPLAIN输出可能因数据分布而变化。这些场景建议你自己在测试环境跑一遍亲眼看看type、key、rows、Extra如何联动。看多了之后你对“什么样SQL会走什么样计划”会形成一种直觉而这种直觉在后续优化里非常值钱。4. 优化实操从发现问题到动手改写SQL4.1 第一步永远是开慢查询日志没有慢查询日志优化就是盲人摸象。我建议每个MySQL实例都常态化开启慢查询而不是等到出故障再打开。SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;这样执行时间超过1秒的查询会被记录下来同时不适用索引的查询也会被记录。生产环境如果担心日志量太大可以把long_query_time调到2或3先观察几天。拿到慢查询SQL之后第一步不是改SQL而是先EXPLAIN。这个顺序很多人搞反了以为慢SQL就是没索引结果加了索引发现没用因为真正的问题可能是JOIN顺序、隐式转换或者排序字段。4.2 拿到EXPLAIN后按“四看”顺序排查我习惯按这个顺序快速定位问题一看type。是不是ALL或index如果是基本可以确定查询没有有效利用索引定位数据。二看key。优化器最终用了哪个索引如果possible_keys有值但key为NULL要思考为什么候选索引被放弃常见原因是数据分布、字符集、成本模型。三看rows。估算扫描行数是否和表的规模匹配如果表只有1万行rows显示9000那就是扫描了绝大多数行。四看Extra。有没有Using temporary、Using filesort、Using join buffer这些附加操作往往比扫描行数更致命。只要把四步走完大多数慢查询的毛病就能定位到具体原因。4.3 实战案例三种典型的索引失效场景案例一对索引列使用函数。EXPLAIN SELECT * FROM orders WHERE DATE(created_at) 2024-06-01;在created_at上套了DATE()函数之后索引失效typeALL。正确的改法是把条件改成范围SELECT * FROM orders WHERE created_at 2024-06-01 00:00:00 AND created_at 2024-06-02 00:00:00;改完之后type变成rangekey使用idx_created_at性能立刻不一样。案例二隐式类型转换。EXPLAIN SELECT * FROM orders WHERE order_no 202406010001;order_no是varchar但这里拿数字去比较MySQL会做隐式类型转换索引大概率失效。应该写成字符串字面量。案例三违反最左前缀。EXPLAIN SELECT * FROM orders WHERE status 1;status在联合索引里是第二列查询条件没有user_id索引用不上。要么把status作为首列建索引要么在此基础上扩展一个以status开头的联合索引具体取决于业务最常怎么查。4.4 实战案例深分页优化分页查询是开发里极容易被忽略的坑。假设一个订单列表接口用户翻到第10000页SELECT * FROM orders ORDER BY created_at DESC LIMIT 199980, 20;这个查询需要先按created_at排序然后扫描大约200000行其中前199980行全部丢弃浪费极大。即使created_at有索引行数一大回表成本也很惊人。我的优化方案是“延迟关联”先用覆盖索引找出需要的id再回表取完整数据。SELECT o.* FROM orders o JOIN ( SELECT id FROM orders ORDER BY created_at DESC LIMIT 199980, 20 ) t ON o.id t.id;子查询里只查id这一列可以完整走idx_created_at覆盖索引排序和分页都在索引树上完成回表的行数只有最后真正要返回的20行。同样的逻辑也适用于“下一页”游标分页如果业务允许直接用上次最后一条的created_at做条件性能会更好。4.5 实战案例JOIN与排序的优化先看一个JOIN的常见病SELECT o.order_no, u.name FROM orders o LEFT JOIN users u ON o.user_id u.id WHERE o.status 1;优化器选择驱动表和被驱动表顺序时会考虑成本。理想情况下被驱动表users的JOIN条件是主键u.idtype应该是eq_ref每次只回一行成本很低。如果EXPLAIN结果里出现Using join buffer基本可以断定关联字段没有索引或者字段类型不一致导致索引无法使用。再看排序优化。如果SQL里有ORDER BY和GROUP BYExtra里出现Using filesort时优先考虑能否通过联合索引消除排序。比如业务高频查询是“按created_at倒序按status分组统计”与其对created_at和status分别建索引不如考虑status, created_at这样的联合索引让分组和排序都能顺着索引顺序走。但索引设计也不能无脑堆每个索引都会增加写入成本和占用存储。我个人的经验是把业务里最高频的十类查询列出来根据它们的WHERE等值条件、范围条件、排序字段去设计组合索引而不是遇到一个慢查询就加一个索引。4.6 数据库设计阶段就引入EXPLAIN很多项目是上线后慢查询爆发才回来补索引其实成本远高于设计阶段就做好。我在做数据库设计评审时通常要求每张核心表给出主键、唯一键、至少一个覆盖高频查询的索引并且用一条典型业务SQL打出EXPLAIN确认type至少是ref或range。这个习惯让很多潜在问题在开发阶段就被消灭而不是等DBA半夜被报警吵醒。5. 进阶工具FORMATJSON与EXPLAIN ANALYZE5.1 FORMATJSON能看到哪些额外信息普通EXPLAIN表格已经能满足大部分场景但做深度的成本分析时FORMATJSON更友好。它会把优化器的成本拆得更细还能看到每一步的cost_info。比如{ query_block: { select_id: 1, cost_info: { query_cost: 2.91 }, table: { table_name: orders, access_type: ref, possible_keys: [idx_user_status], key: idx_user_status, used_key_parts: [user_id, status], rows_examined_per_scan: 8, filtered: 100.00 } } }实际输出会比这个冗长很多每个步骤都有prefix_cost、total_cost这类成本预估。虽然这些成本不是真实执行时长但修改SQL前后对比这两个值能辅助判断优化方向。每次我用FORMATJSON主要是为了开optimizer_trace之前的粗筛先直观感受优化器把“大头”花在了哪里。如果多个表连接哪个表的成本占比最高先从那里下手。5.2 EXPLAIN ANALYZE不再只是纸上谈兵MySQL 8.0.18开始提供EXPLAIN ANALYZE它会真正执行SQL然后返回每一步的actual time、rows、loops。输出形如- Filter: (o.status 1) (cost12.30 rows1000) (actual time0.45..0.62 rows800 loops1)这里cost12.30还是预估actual time0.45..0.62是实际耗时范围rows800是实际读取返回的行数loops1是这一步执行了几次。如果看到某个节点loops很大说明它被循环执行了很多次比如相关子查询、嵌套循环连接里的小表被反复扫描这往往是性能瓶颈所在。这个工具唯一的坑是“真跑”。在生产环境一条重SQL本身就已经在拖垮数据库再EXPLAIN ANALYZE一次等于火上浇油。我的建议是先在测试环境用EXPLAIN ANALYZE定位瓶颈生产环境最多用普通EXPLAIN别轻易真跑。对写语句更是绝对不要在EXPLAIN ANALYZE里执行。6. 常见误区与我的避坑经验6.1 EXPLAIN结果不是“事实”rows是采样估算不是实际行数。我见过不少同学拿着EXPLAIN里rows80去和线上实际返回1000行争论其实两边都没错只是EXPLAIN从不保证估算精确。统计信息过期时偏差会更大这时候可以执行ANALYZE TABLE orders;刷新统计信息然后再EXPLAIN一次。另外MySQL 8.0的优化器在某些情况下会生成跳过的统计直方图如果查询范围跨度很大旧统计信息会导致执行计划跟不上真实数据分布。6.2 我踩过的几个具体坑希望你少踩第一个坑是字符集不一致。有一回一个查询明明两个字段都有索引EXPLAIN却显示全表扫描查到最后是A表用了utf8、B表用了utf8mb4关联时MySQL做了隐式转换索引直接失效。设计表结构时关联字段字符集和排序规则最好全局统一。第二个坑是冗余索引。曾经接手过一张表user_id、status、created_at各种单列索引叠了一堆看起来好像很安全实际上写入性能被拖垮优化器还因为候选索引太多产生开销。联合索引能覆盖多条查询路径时优先用联合索引并定期清理重复索引。第三个坑是小数据量下的“假健康”。测试环境几千行数据全表扫描也很快EXPLAIN显示ALL没人觉得有问题结果上生产瞬间爆炸。做性能方案时一定要模拟线上量级哪怕用几百万行造假数据也值得。第四个坑是ORDER BY RAND()。这个写法会让优化器彻底放弃索引属于典型的性能杀手。需要随机取行时可以先用一个子查询随机取主键ID再通过主键回表效果会好得多。6.3 适合放进团队规范的三件事如果让我给团队定执行计划相关的开发规范我会强制三件事。第一新接口上线前必须有EXPLAIN验证记录哪怕只是贴一张截图也能把“无索引上线”的坑堵死大半。第二慢查询日志常态化开启每周定期看一眼慢日志不要等系统卡死了才想起它。慢查询的收敛是一个持续的过程不是一次优化就一劳永逸。第三每次优化做完把优化前后的EXPLAIN和执行时间记录在案无论是文档还是评论里。这个习惯能让你在半年后再遇到类似问题时有据可查不用重新踩一遍。我自己每一次优化一直保持着“先EXPLAIN再改再EXPLAIN”的循环。很多老手说EXPLAIN是MySQL调试的起点我觉得更准确的说法是它是一个让你不断逼近真相的坐标系。数据分布会变统计信息会变数据库版本会变但只要你坚持用EXPLAIN去验证每一次判断优化方向就不会偏得太远。