MySQL执行计划实战：从Explain字段到索引优化与慢查询调优

一条 SQL 跑得慢十个人里有八个第一反应是“加个索引吧”但索引加在哪一列、加几列、加完之后是不是真的被用上了很多人心里其实没底。我见过太多团队里索引加了一堆SHOW INDEX看起来热热闹闹结果慢查询日志照样一天涨几百兆。问题的根子往往不在“有没有索引”而在于优化器最终挑了哪条路走——而这条路的完整地图就是 MySQL 的执行计划。Explain 这条命令几乎每个 MySQL 使用者都敲过但能把它输出里的type、rows、filtered、Extra几列连起来讲清楚的人并不多更别说用FORMATJSON和EXPLAIN ANALYZE去深挖优化器的成本估算了。这篇内容想做的事很直接把 MySQL 执行计划从“看得懂”推到“用得上”从单条 SQL 的索引判读一路讲到你日常调优流程里怎么把它当成固定动作。不管你是刚接触 mysql 执行计划的新手还是写过几年业务 SQL、想补上执行计划这块短板的后端同学都能顺着往下走。1. Explain 到底是什么先搞懂执行计划这张“体检单”1.1 一条 SQL 从敲下回车到返回结果中间发生了什么要真正读懂执行计划得先知道它是在哪个环节被“画”出来的。MySQL 的架构可以粗略分成两层上面是 Server 层负责连接管理、SQL 解析、优化、执行调度下面是存储引擎层InnoDB 就住在这里负责真正的数据读写。当你把一条查询发过去Server 层先经过连接器做权限校验再交给解析器把 SQL 文本切成语法树接着预处理阶段会做一些语义检查和常量折叠然后才轮到优化器登场——这一步就是执行计划的诞生地。优化器拿到这棵语法树之后会做两件核心的事一是决定表的连接顺序二是决定每张表用哪个索引、用什么访问方式。这两件事的组合可能有几十上百种优化器不可能穷举它靠的是基于成本的估算——也就是常说的 CBOCost-Based Optimizer基于成本的优化器。它会去统计信息里翻出索引的基数、数据分布估算每种方案大概要读多少页、扫多少行最后挑一个总成本最低的。Explain 输出的正是优化器最终拍板的那个方案而不是你脑子里预想的那个方案。这里有个特别容易被忽略的点优化器的成本估算依赖统计信息而统计信息是采样的不是精确值。也就是说执行计划本身就是一个“带误差的预测”它会错判也会在数据分布变化后失真。理解了这一层你后面看到rows估值和实际差了几十倍、看到走错索引的情况就不会觉得莫名其妙了。1.2 Explain 这张表里的每一列分别在看什么不同 MySQL 版本下 Explain 的输出列略有差异8.0 多了partitions但核心列基本稳定。先把每一列的作用摆出来后面再逐个深挖。列名含义调优时怎么看id查询序号同一 id 越大优先级越高判断子查询与连接的执行顺序select_type查询类型如 SIMPLE、PRIMARY、SUBQUERY、DERIVED识别是否被改写成派生表table当前行对应的表名或别名定位是哪张表出了问题partitions命中哪些分区分区表调优必看type访问类型决定扫描方式最关键的列之一看有没有掉到 ALLpossible_keys理论上可用的索引为空说明压根没有可用索引key实际选用的索引和 possible_keys 对比能发现误选key_len使用到的索引字节长度判断联合索引用了几列ref与索引列比较的对象看是常量还是别的表的列rows预估扫描行数数量级不是精确值filtered存储引擎返回后剩余比例值低说明过滤条件用不上索引Extra补充信息filesort、temporary 都藏在这里把这张表存下来遇到慢 SQL 逐列过一遍绝大多数问题都能定位。我自己的习惯是先看type和key确认走没走索引再看rows配合filtered估算扫描量级最后扫一眼Extra有没有临时表和文件排序。这个顺序不是随便定的——它对应的是“能不能用索引 → 用了多少 → 代价大不大 → 有没有额外开销”这条判断链。注意rows是优化器的估算值在数据分布倾斜严重的表上它可能和真实扫描行数差出一个数量级。看到rows很小但 SQL 很慢第一反应应该是统计信息该更新了。2. 动手之前环境准备与被测数据构造2.1 版本差异先对齐别拿 5.7 的经验套 8.0讲具体操作之前得先解决一个现实问题EXPLAIN的行为跨版本变化不小。MySQL 5.7 和 8.0 在优化器行为、默认连接算法、统计信息收集方式上都有差别。比如 8.0.18 之后才引入EXPLAIN ANALYZE8.0 才默认开启哈希连接8.0 的直方图统计也是 5.7 没有的。如果你在本地装的是 5.7却拿 8.0 的文章对着调很容易对不上号。装环境这块不复杂。Windows 上直接从 mysql 下载官网拿 8.0 的安装包就行走 mysql 安装教程 8.0 的常规流程记得选 Developer Default 或者 Custom把 Server 和 Workbench 都勾上。装完立刻会遇到的一个问题就是 mysql 的初始密码是什么——现在安装器会强制你设一个强密码不会再给随机临时密码了设完记牢。Linux 上更省事用 docker 安装 mysql 一条命令起一个容器配上-e MYSQL_ROOT_PASSWORDxxx就能跑测执行计划用一次性容器最舒服用完删掉不留垃圾。客户端工具按习惯来。mysql workbench 使用教程 那套界面化操作对新手友好能直接图形化看执行计划navicat 连接 mysql 也很普遍它有个“解释”按钮能出可视化树喜欢轻量的可以用 DBeaver。有人搜过“dbeaver 连接 sql server 怎么查看执行计划”思路其实相通就是把数据库连接配好后用工具自带的执行计划视图只不过不同数据库的执行计划格式不一样跨库对比时别混着看。提示调优环境最好和生产同版本、同存储引擎、同字符集。utf8mb4 和 latin1 在key_len上的差异会让你的判断直接偏掉。2.2 造一张能暴露问题的订单表光看文档记不住得有数据练手。我一般用一张订单表做实验字段覆盖数值、字符、时间三类方便观察联合索引、排序、覆盖索引的各种场景。CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status TINYINT NOT NULL DEFAULT 0, channel TINYINT NOT NULL DEFAULT 0, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, remark VARCHAR(255) DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_user_status (user_id, status), KEY idx_created (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;建完表往里灌数据。手动插几万行太慢用存储过程循环插是最省事的办法这也是 mysql 存储过程 的经典练手场景DELIMITER $$ CREATE PROCEDURE fill_orders(IN n INT) BEGIN DECLARE i INT DEFAULT 0; WHILE i n DO INSERT INTO orders (user_id, status, channel, amount, remark, created_at) VALUES ( FLOOR(1 RAND() * 5000), FLOOR(RAND() * 5), FLOOR(RAND() * 3), ROUND(RAND() * 1000, 2), CONCAT(note-, i), DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY) ); SET i i 1; END WHILE; END$$ DELIMITER ; CALL fill_orders(200000); ANALYZE TABLE orders;二十万行数据量不大但足够让优化器在“走索引”和“全表扫描”之间做出真实的选择。这里务必执行一次ANALYZE TABLE它会重新采样索引基数让统计信息贴近真实分布——很多“为什么没走索引”的玄学问题根源就是统计信息还停留在空表状态。注意RAND()生成的数据分布是均匀的和真实业务里用户活跃度长尾分布不同。想模拟倾斜数据可以把user_id换成FLOOR(POW(RAND(), 3) * 5000)之类让少数用户占据大量订单这样更容易复现“优化器选错索引”的场景。3. 逐字段拆解type、key_len 与 Extra 的判读逻辑3.1 type 的优先级排序以及最常见的三种误判type列决定了 MySQL 用什么方式去访问数据它的取值从优到劣大致有这样一个顺序system const eq_ref ref range index ALL。中间还夹着 fulltext、ref_or_null、index_merge、unique_subquery、index_subquery 这些不常遇到的类型。日常业务里 90% 的情况就落在 ref、range、index、ALL 这四个上。const是理想状态意思是优化器通过主键或唯一索引定位到了唯一一行在优化阶段就把结果算出来了执行阶段直接返回常量。eq_ref出现在被驱动表通过主键或唯一索引连接时每行驱动数据只匹配一行也很健康。ref是最常见的良好状态用普通索引做等值匹配可能匹配到多行。range表示走索引做范围扫描比如BETWEEN、、IN这类通常可以接受但要留意范围别开太大。真正要警惕的是index和ALL。index听起来像是走了索引其实它是把整棵索引树从头扫到尾只是比全表扫描少读一些列——本质上依然是全量扫描。ALL则是赤裸裸的全表扫描。这两个出现时一般意味着没有可用索引或者优化器算下来觉得全扫更便宜。这里有个非常典型的误判很多人看到type是range就放心了但如果范围条件落在了一个选择性很差的列上比如状态字段只有 3 个取值range扫出来的行数可能占全表三分之一那还不如直接全扫。所以type必须和rows结合起来看。-- 走 ref正常 EXPLAIN SELECT * FROM orders WHERE user_id 1001 AND status 1; -- 走 range但因为 status 选择性差可能扫掉大量行 EXPLAIN SELECT * FROM orders WHERE status 0; -- 大概率 ALL因为 channel 上没有索引 EXPLAIN SELECT * FROM orders WHERE channel 2;type只是入口信号它告诉你“怎么扫”rows才告诉你“扫多少”。3.2 key_len 里藏着联合索引用了几个字段key_len是很多人直接跳过的列但它在联合索引场景下价值极高。它的含义是本次查询实际用到的索引字节长度。因为联合索引是“最左前缀”生效的你只要看到key_len的值就能反推出到底用到了索引的哪几列。计算规则得记牢InnoDB 下索引列的key_len由列类型本身决定NOT NULL不额外加字节允许为NULL的列要额外加 1 字节变长类型varchar、varbinary还要再加 2 字节的长度前缀。整数类型里tinyint是 1 字节int是 4 字节bigint是 8 字节。拿前面那张表举例idx_user_status (user_id, status)里user_id是INT NOT NULL占 4 字节status是TINYINT NOT NULL占 1 字节。那么查询条件用到的索引列key_len 理论值WHERE user_id 1001user_id4WHERE user_id 1001 AND status 1user_id, status5所以当你写了一条联合索引的查询发现key_len只有 4 而不是 5就说明status那一列没被用上。原因可能是类型不匹配——比如status传了个字符串MySQL 隐式转换后索引失效也可能是条件写法导致优化器放弃了后半段。这种“差几个字节”的细节看一眼key_len就现原形了比反复猜快得多。提示做EXPLAIN时如果key_len和预期不符先查参数类型再看字符集。utf8mb4 下VARCHAR(20)是 80 字节换成 utf8 就只有 60 字节跨版本迁移时这类长度变化很容易让索引判定走偏。3.3 Extra 一栏才是问题真正的藏身处Extra是 Explain 里信息量最大、也最容易被忽略的一列。它出现NULL或Using index通常是好消息出现Using filesort和Using temporary就要警觉了。Using index表示覆盖索引生效查询所需的列全部在索引里不需要回表。这是优化时特别想追求的状态一次索引扫描就能出结果省掉了随机 IO。但要注意覆盖索引一旦变胖——比如为了覆盖而把大字段塞进索引——索引本身的维护成本会上去写入性能会掉所以它是权衡的结果。Using filesort出现在排序无法借助索引完成时。它不一定真的落磁盘但意味着 MySQL 要额外开辟空间、按排序字段重排结果集代价不小。触发它最常见的原因是ORDER BY的列和WHERE用到的索引顺序对不上。Using temporary表示用到了内部临时表常见于GROUP BY、DISTINCT、UNION这些场景。临时表可能在内存里也可能落到磁盘一旦落盘性能断崖式下跌。Using index condition是索引条件下推ICP表示部分过滤条件下推到了存储引擎层减少了回表次数一般算好事。Using join buffer表示被驱动表没走索引连接时靠内存缓冲块拼数据出现它基本就是索引缺了。再看一个组合案例EXPLAIN SELECT user_id, SUM(amount) FROM orders WHERE created_at 2024-01-01 GROUP BY user_id;这条大概率会同时出现Using temporary和Using filesort。idx_created只能帮上WHERE的忙GROUP BY user_id没法借助它MySQL 得把过滤后的行拉进临时表再按user_id分组。改造思路就是给user_id建索引或者调整成(created_at, user_id)这样的组合索引让分组也能沾上光。4. 从执行计划到索引改造三个真实慢查询的剖析4.1 案例一联合索引最左前缀失效有个常见的查询模式按用户查订单然后再按状态过滤偶尔还会按时间排序。EXPLAIN SELECT id, amount, created_at FROM orders WHERE status 1 AND user_id 1001;这条看起来条件里status在前、user_id在后但优化器并不笨它会自动调整等值条件的顺序去匹配索引所以照样能走上idx_user_statuskey_len是 5。但下面这条就不一样了EXPLAIN SELECT id, amount FROM orders WHERE status 1 AND channel 0;idx_user_status的第一列是user_id这里既没用到user_idstatus又是索引的第二列最左前缀断了索引直接作废type掉到ALL。这就是最左前缀原则的具体表现索引树是按(user_id, status)的顺序排列的跳过user_id就没法利用排序性去定位。解决办法要么是补一个(status, channel)的索引要么改查询逻辑带上user_id。我在实际项目里更倾向后者——因为如果status本身只有几个取值给它单独建索引收益也不大还不如老老实实按用户维度去查。索引不是越多越好每个索引都要额外占用空间、拖慢写入所以每次加索引前先想清楚这个查询是否值得为它单独开一条索引路径。4.2 案例二排序字段与索引顺序错位排序引发的问题特别隐蔽因为WHERE过滤走得好好的你可能完全没意识到排序在背后拖后腿。EXPLAIN SELECT id, amount FROM orders WHERE user_id 1001 ORDER BY created_at DESC LIMIT 20;这条WHERE用idx_user_status能过滤出这个用户的订单但ORDER BY created_at和索引里的列顺序对不上于是Extra里冒出Using filesort。数据量一大排序开销就顶不住。这里有两种改造方向。一种是建(user_id, created_at)的联合索引让过滤和排序共用同一条索引ORDER BY就不再需要额外排序。另一种是如果这个用户订单量本身很小排序开销可以接受那就不折腾。我一般会先看rows估算。如果user_id 1001匹配的行数只有几十行filesort 几十行根本不算事没必要为它调整索引结构。但如果这个用户下了一万多单那就必须处理。这里体现的是一个基本判断优化不是无脑消灭所有Using filesort而是看它排多少行、代价占比多大。补一个容易忽略的细节ORDER BY后跟DESC时如果索引是默认的升序MySQL 在 8.0 之前无法反向利用索引排序8.0 之后引入了降序索引才支持。如果你在 5.7 上做(user_id, created_at)联合索引配DESC排序可能依旧出现 filesort这时候要么把索引改成(user_id, created_at DESC)8.0 语法要么确认版本支持情况。4.3 案例三子查询被改写成派生表IN子查询是另一个重灾区。很多人在 where 里写子查询以为能分批过滤结果被优化器改写成派生表后执行计划面目全非。EXPLAIN SELECT o.id, o.amount FROM orders o WHERE o.user_id IN ( SELECT user_id FROM orders WHERE status 4 GROUP BY user_id );在较老的版本里这种写法会被改造成一张临时派生表select_type显示DERIVED配合Using temporary性能相当糟糕。MySQL 5.6 之后引入了半连接优化semijoin会把IN子查询改写成连接情况改善很多但不是所有写法都能命中这个优化。想确认到底走没走半连接看select_type里有没有DEPENDENT SUBQUERY、MATERIALIZED这些标记或者在warnings里看 rewrite 信息EXPLAIN SELECT o.id, o.amount FROM orders o WHERE o.user_id IN ( SELECT user_id FROM orders WHERE status 4 GROUP BY user_id ); SHOW WARNINGS;SHOW WARNINGS会把优化器改写后的 SQL 语句打出来这是很多老手都在用但文档里不显眼的技巧。看到改写结果你才能判断子查询是被合并了、被物化了还是原样保留。如果确实被物化成临时表改写思路通常是把它换成JOIN或者EXISTS让执行路径更可控。顺带说一句UPDATE语句里的子查询也容易翻车。像UPDATE orders SET amount 0 WHERE user_id IN (SELECT ... FROM orders ...)这种自引用更新在 MySQL 里会直接报错因为它不允许在更新同一张表时在子查询里再查这张表。绕开的办法是套一层派生表或者用JOIN形式改写。这是 mysql update 语法 里的一个经典坑。5. 进阶用法EXPLAIN ANALYZE、JSON 格式与优化器追踪5.1 EXPLAIN ANALYZE拿到真实执行数据前面反复强调rows是估算值那有没有办法看到真实值MySQL 8.0.18 之后可以用EXPLAIN ANALYZE它会把语句真正执行一遍然后输出每个算子的实际耗时和实际返回行数。EXPLAIN ANALYZE SELECT user_id, COUNT(*) FROM orders WHERE created_at 2024-01-01 GROUP BY user_id;输出的是一个树形结构每个节点上会带actual time... rows... loops...这种标注。actual time是实际耗时rows是实际处理的行数loops是算子被执行的次数。拿它和普通EXPLAIN的估算值一对比就能判断优化器的估算准不准。如果发现某个节点的估算rows是 100实际rows是 50000那说明统计信息严重失真这时候ANALYZE TABLE就能派上用场。我遇到过一个典型的案例订单表在某个时间点做了一次批量数据迁移统计信息没更新优化器还按老基数估算结果挑了一条看起来便宜、实际要全扫的索引路径。执行ANALYZE TABLE之后执行计划立刻改回了正确的索引。注意EXPLAIN ANALYZE会真实执行这条 SQL。如果是SELECT问题不大但绝不能用在INSERT、UPDATE、DELETE上否则数据就真的被改了。需要分析写操作时先在外面包一层事务再回滚或者把语句改写成等价的SELECT来观察执行路径。5.2 FORMATJSON看优化器算的成本账传统的表格形式输出适合快速判断但很多细节它藏起来了。加上FORMATJSON能看到成本估算的完整过程EXPLAIN FORMATJSON SELECT id, amount FROM orders WHERE user_id 1001 AND status 1;JSON 输出里会出现query_cost字段列出优化器为这个方案算出的总成本还会给出cost_info区分读取成本read_cost和评估成本eval_cost。如果你给这条 SQL 加个FORCE INDEX强制走另一条索引再把两个 JSON 的query_cost一对比就能明白优化器为什么选了它现在这条——因为它算下来成本更低。这套方法在排查“优化器选错索引”时特别好用。有时候你心里觉得应该走 A 索引优化器偏偏走了 B把两者的成本明细摆出来通常就能找到原因要么是 A 索引的基数统计偏低优化器误以为它区分度差要么是 B 索引虽然看起来笨但因为覆盖了查询列省去了回表总成本反而低。看懂了成本账你对优化器的判断就不再是黑盒。5.3 optimizer_trace钻进优化器的脑子里看如果你想看得更细MySQL 还提供了一个神器optimizer_trace。打开它之后所有关于优化器如何评估各种候选方案的细节包括每条候选索引的预估行数、成本、被选中的理由都会完整记录下来。SET optimizer_trace enabledon; SET optimizer_trace_max_mem_size 1048576; SELECT id, amount FROM orders WHERE user_id 1001 AND status 1; SELECT * FROM information_schema.OPTIMIZER_TRACE\G SET optimizer_trace enabledoff;输出的内容很长结构是一层层的 JSON里面有considered_execution_plans节点列出了每个被评估过的执行方案和它们的成本。看的时候容易眼花建议直接搜索chosen关键字定位到最终选中的那个方案然后再往上看它为什么击败了其他候选。这个工具最实用的场景就是“明明有两个索引为什么偏偏选了那个更差的”。打开 trace 一看往往能发现优化器对某条索引的基数估计有偏差或者某个条件被认为是不可下推的。找到原因之后该更新统计信息就更新该加直方图就加直方图实在不行再用FORCE INDEX兜底——但要记住FORCE INDEX是硬指定数据分布变了之后可能反而变慢不到万不得已不要写进生产代码。6. 常见问题速查与避坑清单6.1 执行计划与实际不符先按这个顺序排查排查执行计划问题我一般按下面的顺序走一遍能覆盖八九成的情况统计信息是否新鲜。对表执行ANALYZE TABLE再重新看执行计划。如果计划变了问题就出在统计信息上。条件列类型是否匹配。user_id是INT你传了个字符串1001MySQL 会做隐式类型转换转换发生在列上时索引就失效了。养成参数类型和列类型一致的习惯。查询列是否能被索引覆盖。对比possible_keys和key有时候优化器放弃某条索引是因为回表代价太高。是否被函数或运算包裹。WHERE DATE(created_at) 2024-01-01这种写法会让索引失效改成created_at 2024-01-01 AND created_at 2024-01-02就能用上。连接顺序是否合理。用STRAIGHT_JOIN临时固定驱动表看计划是否改善如果能改善说明优化器选错了驱动表。下面这张表把常见现象和对应的排查方向对应起来遇到问题可以直接查现象可能原因排查动作type 为 ALL无可用索引或索引失效查 possible_keys 是否为空检查条件列类型key 为 NULL 但 possible_keys 有值优化器认为全扫更便宜看 FORMATJSON 成本更新统计信息rows 估算远小于实际统计信息过期执行 ANALYZE TABLEExtra 出现 Using filesort排序字段与索引不匹配调整 ORDER BY 或新增联合索引Extra 出现 Using temporaryGROUP BY 或 DISTINCT 无索引支持为分组字段建索引type 为 index 而非 ref只用到索引扫描未精确匹配检查是否扫描了整棵索引树6.2 我踩过的几个坑分享出来少走弯路第一个坑是把EXPLAIN的结果当成铁律。有一回我按执行计划判断某个查询走的是range觉得没问题就上线了结果生产环境慢查询报警。后来用EXPLAIN ANALYZE一看实际扫描行数是估算值的二十倍——线上数据分布的倾斜程度远超测试环境。从此我养成了一个习惯凡是涉及大数据量表的优化一定要在有代表性的数据量上验证不要拿几千行的测试表下结论。第二个坑是无脑消灭Using filesort。早期我一看到 filesort 就急着加索引后来发现有的是排几十行加索引带来的写入开销反而更大。现在我判断的标准变成了三个排多少行、这个查询的调用频率有多高、表是读多还是写多。三个维度一起看才决定值不值得动索引。第三个坑是忽略SHOW WARNINGS。很多时候执行计划和你想的不一样不是因为优化器选错了而是因为你的 SQL 被改写过了。特别是IN子查询、OR条件、视图查询这几类改写之后执行路径完全变了。养成在EXPLAIN之后顺手SHOW WARNINGS的习惯能省下大量猜测的时间。第四个坑是只盯着单条 SQL不看整体。索引是有限的资源给这张表加三条索引可能让写入 TPS 掉一截。我现在的做法是把慢查询日志导出来按rows_examined、query_time和出现频率做聚类优先处理高频且扫描量大的那几条而不是逐条消灭。数据库命令大全 里那些命令不是拿来炫技的是用来帮你在全局视角做取舍的。关于后续能怎么延展我自己平时还会把执行计划的分析和上下游打通往下看 InnoDB 的SHOW ENGINE INNODB STATUS能看到行锁等待和事务情况判断慢是因为扫得多还是因为卡在锁上往上看应用的连接池配置连接数、超时时间设置不当也会让好端端的一条 SQL 表现异常。这套组合拳打下来基本能覆盖日常八九成的性能问题。执行计划从来不是终点它是你进入性能调优这个世界的第一步。