“谈谈你对 MySQL 执行计划的理解”——这是 MySQL 面试里频率高得离谱的一道题。很多候选人都知道执行计划就是 EXPLAIN 打印出来的那几张结果集也听说过 type 列最好能达到 const 或者 ref但面试官一旦往下追问每个字段的含义、索引选择的前因后果、以及实际优化案例中的决策过程很多人的回答就会散掉。我自己的习惯是不管写的 SQL 多简单上线前都会先跑一遍 EXPLAIN 看执行计划。这就像出门前看天气——不能保证路上不出状况但能提前预判大部分问题。这篇文章我会把执行计划的完整体系梳理一遍从 MySQL 内部是怎么执行一条 SQL 的到 EXPLAIN 输出每一列怎么读再到一个真实慢 SQL 的优化过程最后是面试时最能加分的排查思路。不管是准备面试还是想把手头线上业务的慢查询理清楚这份内容应该都能直接用上。1. 执行计划到底在回答什么问题1.1 一条 SQL 从输入到返回经历了什么先把执行链路讲清楚。一条 SELECT 语句到达 MySQL 服务端之后大概会经历四层处理连接管理、解析与预处理、查询优化、执行器执行。连接管理就是拿到一个线程和会话上下文这一步决定了你后面的查询能读到什么隔离级别下的数据解析器负责把 SQL 文本拆成语法树顺便检查表名、列名是否存在优化器是整条链路里最核心的一环它负责决定用什么索引、按什么顺序 JOIN、是否做排序或临时表执行器拿到优化器给出的“方案”后逐行调用存储引擎接口读取数据最终把结果返回给客户端。我们平时说的“执行计划”其实就是优化器最终选定的那套执行方案。它不是一个神秘的内部黑盒而是一张可以被 EXPLAIN 翻译成人类可读行的表。1.2 为什么优化器生成的“路线”决定了性能优化器在生成执行计划时本质上在做一件和地图导航一样的事情输入是 SQL 文本输出是“最快到达终点”的路线。导航会考虑路况、距离、红绿灯优化器会考虑表的行数、索引的区分度、JOIN 的成本、是否要回表、是否要排序。它会在多个候选计划之间做成本估算选一个它认为代价最小Cost 最小的。这里的重点是“它认为”。优化器和你掌握的信息不一样它依赖的是统计信息。如果统计信息过旧或者表数据发生了剧烈变化优化器就可能选出“理论上正确但实际很慢”的计划。这也是为什么 DBA 会经常跟你强调 ANALYZE TABLE 的原因。提示理解执行计划的第一层境界是能够把 EXPLAIN 的输出翻译成“MySQL 到底打算怎么干”第二层境界是你能反过来判断优化器为什么这么选甚至在它选错时用改写 SQL 或加 hint 的方式来纠正。1.3 执行计划影响范围其实比你想的大很多人以为执行计划只跟 SELECT 有关。实际上 UPDATE 和 DELETE 同样有执行计划同样会涉及索引选择和行扫描策略。生产环境里常见的“一条 UPDATE 锁住了全表”本质就是执行计划选择了全表扫描把扫描范围内所有命中的行都加上了锁。这让我想起一个真实场景某业务表有 500 万行执行UPDATE orders SET status 1 WHERE seller_id 123 AND amount 1000时因为 seller_id 列没有索引优化器只能走全表扫描导致大量无关行被锁住主库写入直接阻塞。后面加上联合索引(seller_id, amount)之后执行计划从 ALL 变成 ref锁范围瞬间缩到几十行。所以执行计划不只是“查询优化”的范畴它直接关系到线上稳定性、锁竞争和主从延迟。这也是面试官爱问它的核心原因——他们想看你对 MySQL 底层机制是否有完整认知。2. EXPLAIN 输出逐列拆解清楚2.1 id、select_type、table 的基础关系直接跑一条示例 SQLEXPLAIN SELECT o.order_id, u.user_name FROM orders o JOIN users u ON o.user_id u.id WHERE o.status 1;执行计划输出的大概长这样idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEoALLidx_statusNULLNULLNULL1000050.0Using where1SIMPLEueq_refPRIMARYPRIMARY8test.o.user_id1100.0NULLid是 SELECT 的标识符。同一行 id 相同代表这几张表是在同一个查询里被 JOIN 的执行顺序通常是从上往下。id 不同则说明存在子查询或派生表数字越大越先执行。select_type描述这一行代表的查询类型。常见值有SIMPLE最简单的查询没有子查询和 UNIONPRIMARY外层主查询SUBQUERY子查询中的第一个 SELECTDERIVEDFROM 列表里的派生表也就是平时说的“临时表”UNIONUNION 中的第二个或后续 SELECTMATERIALIZED物化子查询优化器把子查询结果固化成一张临时表。面试时如果能顺口解释这几个值的区别尤其是 DERIVED 和 MATERIALIZED 的差异会显得你对优化器行为有真实理解而不只是背过概念。table就是表名或别名。值得注意的一种情况是table列显示derived2或union1,2这表示当前行是从 id2 的派生表而来。2.2 type 列访问类型的效率阶梯type 列是执行计划里含金量最高的一列。它描述 MySQL 找到目标行的方式也是面试官判断你有没有真正理解索引效率的试金石。按效率从高到低排序type含义典型场景system表中只有一行系统表或LIMIT 1命中的单行表const主键或唯一索引等值匹配WHERE id 100eq_refJOIN 时使用被驱动表的主键或唯一索引上述示例中的 u 表ref使用非唯一索引前缀等值匹配WHERE status 1range索引范围扫描BETWEEN、、、INindex全索引扫描覆盖索引扫描但仍比 ALL 好一点ALL全表扫描没有可用索引或优化器认为全表更快这里有个容易被忽略的点type 为 range 并不一定比 ref 差。如果范围扫描命中的行数很少而 ref 匹配的行数很多比如 status1 占全表 60%前者的实际性能反而更好。所以不要死记“ref 一定比 range 好”要结合 rows 列一起看。面试官如果要你背顺序你可以背但如果他问你“为什么 ref 通常比 range 快”你需要说出本质ref 是基于等值比较的索引查找定位到具体键值而 range 走的是索引区间扫描需要遍历区间内的所有索引项并逐行回表判断。2.3 possible_keys 与 key候选索引与真正用到的索引possible_keys 列出优化器认为可能用到的索引key 是实际选用的索引。两者不一致时就意味着优化器“放弃”了某个可用索引。常见原因有几个索引区分度太差。比如性别列区分度低优化器估算后认为走索引回表的成本比全表扫描还高索引选择性低且小表。表只有几百行MySQL 觉得直接扫全表比走索引更快函数或隐式类型转换导致索引失效。典型的是WHERE DATE(create_time) 2024-01-01以及字符串列与数字比较。有一个实用经验possible_keys 里有索引但 key 为 NULL 时优先考虑是不是函数、隐式转换或者统计信息过期。三级排查路径就是先看 SQL 写法再看统计信息最后考虑用 FORCE INDEX 临时验证。2.4 key_len一个常被忽略但是最值得算的数key_len 表示 MySQL 实际使用的索引字节数。它不仅用于判断索引利用率还可以帮我们确认联合索引到底用到了哪几列。以联合索引(a, b, c)为例列类型key_len 计算int NOT NULL4 字节int NULL5 字节多 1 字节标记 NULLvarchar(20) 且 utf8mb420 * 4 2 82 字节varchar(20) 且 utf8mb4允许 NULL20 * 4 2 1 83 字节datetime5 字节MySQL 5.6或 8 字节假设执行计划 key_len 9说明联合索引只用了 2 列比如一个 int 和一个 varchar(1)第三列没能参与索引查找只能做索引内过滤。面试官常在这里挖坑他递给你一个 key_len83 的案例问你 “为什么这个 varchar(20) 的索引长度不是 82”很多人漏掉 NULL 的标记字节。注意key_len 表示实际使用到的索引前缀长度不是索引总长度。用a1 AND b2查联合索引(a,b,c)时key_len 只包含 a 和 b 的字节数和 c 无关。2.5 rows、filtered 与 Extra成本评估与额外动作rows 是优化器估算的需要扫描的行数filtered 表示经过 WHERE 过滤后剩余行数的百分比。估算的 rows * filtered 可以用来大致评估 JOIN 时驱动表需要返回给上层的数据量。有一点要特别提醒rows 是估算值基于统计信息和索引基数不一定准确。真正精准的数据需要EXPLAIN ANALYZEMySQL 8.0 可用它会真实执行 SQL 并输出实际行数和耗时。平时排查慢 SQL 时我会把 EXPLAIN 的估算当第一档参考把 EXPLAIN ANALYZE 当最终验证。Extra 列的信息密度最高。面试中出现频率最高的几个值Using index覆盖索引无需回表这是理想状态Using where存储引擎返回行后Server 层再做条件过滤Using filesort需要额外排序可能是文件排序或内存排序严重影响性能Using temporary使用了临时表常见于 GROUP BY 或 DISTINCTUsing index condition触发了索引条件下推ICP部分 WHERE 条件下推到存储引擎过滤Using join bufferJOIN 时没用上索引使用了连接缓冲区。我之前排查过一条排序导致 CPU 飙高的 SQLSELECT ... ORDER BY create_time DESC LIMIT 20因为缺少合适的索引Extra 出现Using filesort每次查询都要对几千行做排序。加上(create_time)单列索引后Extra 变成空排序直接在索引完成CPU 负载立刻降了下来。3. 一个真实的慢 SQL 排查过程3.1 从慢查询日志抓到嫌疑 SQL有一次线上商城项目的订单列表接口突然变慢接口耗时从平均 80ms 涨到 2.3s。我首先打开慢查询日志SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time;确认慢查询日志已开启、阈值是 1s 后直接翻日志。抓到这样一条SELECT * FROM order_items WHERE order_id IN (128873, 128874, ..., 129372) ORDER BY item_status, created_at;ORDER BY 里有两个字段item_status 和 created_at。这条 SQL 的执行频率很高而且 IN 的集合还不小大概有 500 个订单 ID。3.2 用 EXPLAIN 定位瓶颈把这条 SQL 拿到测试库执行 EXPLAINEXPLAIN SELECT * FROM order_items WHERE order_id IN (128873, ..., 129372) ORDER BY item_status, created_at\G输出关键行type: range possible_keys: idx_order_id key: idx_order_id key_len: 8 rows: 526 Extra: Using index condition; Using filesort当时看到rows: 526和Using filesort心里就有数了。虽然走了 order_id 索引但 WHERE 条件只筛选订单维度返回的 526 行数据还要在 Server 层做一次ORDER BY item_status, created_at排序。因为排序列不在索引的后续位置优化器只能把所有满足条件的行收集起来再额外排序。而且SELECT * 意味着每行都要回表。如果查询结果集较大回表成本会被放大。3.3 优化方案落地与前后对比我把需求拆解清楚业务上其实只要订单详情里几条关键字段不需要全部列。于是做了两处改动。第一处把SELECT *改成明确的字段列表并创建一个覆盖索引ALTER TABLE order_items ADD INDEX idx_order_status_time (order_id, item_status, created_at, sku_id, quantity, price);这样执行计划里 Extra 会变成Using index排序也不用手动做索引天然按 order_id 和 item_status、created_at 排序。第二处如果业务确实需要全字段查询就改成先查主键再回表SELECT oi.* FROM order_items oi JOIN ( SELECT id FROM order_items WHERE order_id IN (...) ORDER BY item_status, created_at LIMIT 500 ) tmp ON oi.id tmp.id;改完后再看 EXPLAINtype: range key: idx_order_status_time key_len: 8 rows: 526 Extra: Using indexUsing filesort 消失接口耗时回到 90ms 左右。这次排查的完整路径也很有面试价值发现问题定位 SQL分析执行计划调整索引验证前后表现。实操心得线上改索引前一定要评估表数据量和业务低峰期ALTER TABLE在 InnoDB 8.0 里虽然支持在线 DDL但对大表仍会产生额外负载。最好用pt-online-schema-change或 gh-ost 之类的工具或者至少在流量低谷执行。4. 执行计划里的常见“坑”和面试追问点4.1 明明有索引却不走面试官特别喜欢把这条拎出来考“为什么表上明明建了索引执行计划却是 ALL”常见原因我在前面提到了一部分这里整理成速查表现象原因解决方案对索引列使用函数WHERE DATE(create_time) ...改写为范围条件create_time ... AND create_time ...隐式类型转换字符串列与数字比较统一参数类型避免 MySQL 自动 castLIKE 前置通配符WHERE name LIKE %abc%改后缀匹配或用全文索引OR 条件中有一列无索引WHERE a 1 OR b 2拆成 UNION ALL或对 b 建索引统计信息严重过期数据量剧增但未 ANALYZEANALYZE TABLE优化器认为索引回表成本更高小表或低区分度用 FORCE INDEX 验证或调整 SQL 逻辑其中隐式类型转换那道题很经典WHERE phone 13800138000如果 phone 是 varcharMySQL 会把字符串列转成数字进行比较导致这个索引失效。面试时可以顺手答出“在 phone 列上没走索引因为发生了隐式转换MySQL 对索引列做了 cast 处理”。4.2 回表与覆盖索引回表是 MyISAM/InnoDB 索引机制里的核心概念。InnoDB 的二级索引叶子节点存的是主键值查询如果需要主键之外的列就得拿着主键再去聚簇索引里查一次完整行。这就是回表。执行计划里如果 Extra 是Using index说明查的列全部在索引里不需要回表也就是覆盖索引。覆盖索引的价值不只是省一次 IO它还能让 MySQL 在索引层面过滤更多行减少 Server 层工作。我面试时通常会把一个类似问题抛给候选人“有一条 SQLWHERE 用了联合索引的第一列SELECT 的列是第二列和第三列你压一下 key_len 和 Extra 可能出现什么”如果对方能答出 key_len 仅包含第一列长度但 Extra 可能是 Using index说明他真正理解联合索引与覆盖索引的关系。4.3 如何向面试官展示你的优化思路面试官不只想听你背概念更想看排查思维。比较讨巧的回答结构是先用 EXPLAIN 看当前执行计划确认 type、key、rows、Extra 的现状分析 rows 是否过大Extra 是否出现 Using filesort 或 Using temporary结合 SQL 逻辑判断是否存在索引失效条件比如函数、隐式转换、OR 或前置通配符提出索引调整方案并预估 key_len 变化用EXPLAIN ANALYZE或加FORMATJSON验证优化前后实际耗时和行数变化。如果面试官追问“你平时怎么定位慢 SQL”可以补充先看慢查询日志和performance_schema里的events_statements_summary_by_digest找到高频慢语句再针对性 EXPLAIN最后结合业务适当做 SQL 改写或索引设计。4.4 关联子查询与 JOIN 的执行计划MySQL 5.6 之前子查询的效率普遍不高很多子查询会被物化成临时表再参与 JOIN。5.6 之后优化器做了大量改进支持子查询去物化、半连接等优化。执行计划里看到select_type是MATERIALIZED或SUBQUERY时要追问一下具体是哪一层。一个常见面试题是“IN 和 EXISTS 哪个快”。旧版本的结论是外层表小用 IN内层表小用 EXISTS。但在 MySQL 8.0 里优化器会把 IN 改写成半连接也会把 EXISTS 做等价改写实际差异已经很小。关键是看执行计划是否走了正确索引。我习惯用EXPLAIN FORMATJSON看更详细的成本信息里面会有cost_info包含 read_cost 和 eval_cost。虽然普通人一般不看这组数据但面试时你能说出来会显得和平铺直叙的候选人完全不在一个层级。5. 一条经验总结把执行计划当工具而不是面试题做了这么多年的 MySQL 优化我的体会是执行计划不是背完就扔的题目它是一个定位问题的手术刀。每当你写完一条 SQL跑一遍EXPLAIN花一分钟看看 type、key_len、rows 和 Extra就能提前知道这条 SQL 上线后会不会变成定时炸弹。个人建议可以有这么几步日常开发自测阶段就执行 EXPLAIN不要等到线上报警再来排查把慢查询日志和performance_schema的监控接入告警系统超过阈值的 SQL 自动触发分析记录每条慢 SQL 的优化前后执行计划形成团队知识库新索引上线时对比旧执行计划和新执行计划确认是否发生计划回退。最后分享一个小技巧我习惯在执行计划旁边写一行注释备注 SQL 的业务场景比如“订单列表页默认查询最近 30 天订单”这样后面再看这张表时能快速回忆起当初加索引的目的。执行计划这件事熟练了之后你会发现它其实就是 MySQL 在跟你对话——告诉你它打算怎么走这条路而你只需要判断这条路要不要换一条。
