面试官问SQL优化与执行计划分析EXPLAIN一张图导航比喻彻底拿下这道必考题附图解比喻避坑指南预计阅读15分钟 你是不是也这样知道EXPLAIN这个命令但面试官一追问“type从好到差怎么排”“Extra出现Using filesort是什么意思”就答不上来了今天一张图 一个导航比喻 全字段深度解析 实战案例 六道追问彻底拿下这道题。摘要EXPLAIN是MySQL分析SQL执行计划的核心工具能展示SQL语句的访问类型、使用索引、扫描行数、额外操作等关键信息。掌握EXPLAIN是SQL优化的基本功。本文用“导航软件路线规划”比喻 各字段深度解析id/select_type/type/possible_keys/key/key_len/ref/rows/filtered/Extra 实战案例分析 6道面试官追问彻底讲透这道MySQL面试必考题。一句话EXPLAIN SQL的执行地图看懂地图才能精准修路加索引和导航改SQL。我是折哥《Java 85题图解版》系列连载中已更新32题建议收藏本系列。每周2-3篇85题通关路线一键追完。点击关注第一时间收到每篇新题推送。上一篇面试官问慢SQL如何定位与优化下一篇预告面试官问MyBatis面试专题含源码MP待发布全部85题点击查看总目录关注专栏追更不迷路一句话总结EXPLAIN SQL的执行地图看懂地图才能精准修路加索引和导航改SQL。type访问类型从好到差依次是systemconsteq_refrefrangeindexALL→ 像导航规划道路类型高速constvs 徒步穿越田野ALL至少要达到range级别。key实际使用的索引实际走哪条路 →NULL表示没走索引像导航说“没有规划好的路自己穿田野”。rows预估扫描行数预计经过的路口数 → 越大越慢像导航说“预计经过100个红绿灯”。Extra额外信息路况提示 →Using index绿波带、Using filesort需要绕圈/额外排序、Using temporary需要服务区中转/临时表。背诵口诀id看顺序type看效率key看索引rows看行数Extra看陷阱。核心设计理念EXPLAIN是SQL的“导航地图”——看懂地图才能精准修路加索引和导航改SQL。 面试还原面试官你会用EXPLAIN分析SQL吗执行计划里的各个字段分别代表什么意思这是数据库面试中区分“会用索引”和“真正懂优化”的核心题直接进入正题。 一图看懂EXPLAIN执行计划全貌 生活比喻导航软件路线规划场景设定你打开导航软件规划从A到B的路线执行SQL导航软件MySQL优化器会给出几条候选路线执行计划你选最优的一条实际执行。type 道路类型通行效率system/const高速公路直达——最快几乎没有红绿灯eq_ref城市快速路——很快有少数几个路口ref主干道——还行有一些红绿灯range次干道——一般红绿灯较多index乡间小路——很慢红绿灯极多ALL徒步穿越田野——最慢没有任何路完全靠走key 实际走的路线导航可能给你推荐了3条路线possible_keys你实际选了1条key。如果key是NULL说明你没走任何规划好的路直接穿田野全表扫描。rows 预计经过的路口数导航告诉你“预计经过50个红绿灯扫描50行”。红绿灯越多肯定越慢。目标是尽可能减少经过的路口数。Extra 路况提示Using index全程绿波带一路绿灯覆盖索引最快Using where需要人工判断路口Server层过滤Using filesort需要调头或绕圈额外排序很慢Using temporary需要在服务区中转临时表极慢Using index condition部分绿波带索引下推还行 EXPLAIN各字段深度解析1. id执行顺序规则说明id相同从上到下依次执行id不同id越大越先执行子查询优先id为NULL表示这是一个结果集合并UNION2. select_type查询类型类型含义优先级SIMPLE简单查询不包含子查询和UNION常见PRIMARY最外层查询复杂查询SUBQUERY子查询不在FROM中需要优化DERIVEDFROM子句中的子查询派生表需要优化UNIONUNION中的第二个或后续查询—UNION RESULTUNION的结果集—3. type访问类型⭐ 核心字段type从好到差排序type含义典型场景优化建议system系统表只有一行极少见无需优化const主键/唯一索引常量查询WHERE id 1最优满意eq_ref唯一索引关联查询JOIN中的主键关联很好ref非唯一索引关联查询JOIN中的普通索引好range范围查询BETWEEN、、、IN及格线index索引全扫描查询只涉及索引列需要优化ALL全表扫描没有索引必须优化优化目标至少达到range级别争取达到ref或更好。4. possible_keys vs keypossible_keysMySQL认为可能用到的索引候选名单keyMySQL实际选择的索引最终方案⚠️ 关键场景possible_keys有值但key为NULL → MySQL认为索引成本太高宁愿全表扫描。这说明数据量小或索引区分度低。5. key_len索引长度表示实际使用的索引列的长度。可用于判断联合索引使用了哪几列。示例联合索引(name, age)name是VARCHAR(50)utf8mb4占4字节age是INT4字节。key_len 20050*4只用到了name列key_len 20450*44用到了name和age两列6. rows预估扫描行数表示MySQL预估需要扫描的行数。这是一个预估数字不是实际值但它是衡量SQL效率的核心指标。优化目标是大幅减少rows。7. filtered过滤比例表示存储引擎返回的数据在Server层过滤后剩余的比例。值越高越好100%表示所有返回数据都符合条件没有额外的Server层过滤。注意filtered是MySQL的估算值不一定完全准确实战中结合rows和Extra综合判断。8. Extra额外信息⭐ 最重要的优化信号Extra值含义优化方案Using index✅覆盖索引——最理想保持这是最优状态Using whereServer层过滤如果能用索引过滤则加索引Using index condition索引下推ICP不错MySQL 5.6优化Using filesort⚠️需要额外排序在ORDER BY字段上加索引Using temporary⚠️需要临时表优化GROUP BY/DISTINCT加索引Using join buffer没有索引的JOIN为关联字段加索引Impossible WHEREWHERE永远为false检查SQL逻辑 实战案例分析案例1慢查询优化前后对比优化前全表扫描EXPLAINSELECT*FROMordersWHEREstatuspending\G-- type: ALL全表扫描-- possible_keys: NULL-- key: NULL-- rows: 1000000-- Extra: Using where诊断status字段无索引 → 全表扫描100万行。方案ALTER TABLE orders ADD INDEX idx_status (status);优化后走索引EXPLAINSELECT*FROMordersWHEREstatuspending\G-- type: ref-- possible_keys: idx_status-- key: idx_status-- rows: 5000-- Extra: Using where效果扫描行数从100万降到5000行性能提升200倍。案例2文件排序优化优化前需要额外排序EXPLAINSELECT*FROMordersWHEREstatuspendingORDERBYcreate_time\G-- type: ref-- key: idx_status-- rows: 5000-- Extra: Using where; Using filesort ← 需要优化诊断create_time没索引 → 需要额外排序。方案ALTER TABLE orders ADD INDEX idx_status_time (status, create_time);优化后排序走索引EXPLAINSELECT*FROMordersWHEREstatuspendingORDERBYcreate_time\G-- type: ref-- key: idx_status_time-- rows: 5000-- Extra: Using where ← filesort消失了效果消除排序查询效率大幅提升。案例3覆盖索引优化最优状态EXPLAINSELECTid,statusFROMordersWHEREstatuspending\G-- type: ref-- key: idx_status-- rows: 5000-- Extra: Using index ← 覆盖索引最优查询只涉及索引列不需要回表读取数据行。 高频面试追问6道大厂真题追问1typeindex和typeALL有什么区别哪个更差回答要点index是索引全扫描ALL是全表扫描。index通常比ALL好一些但两者都是需要优化的。详细回答index扫描的是索引树ALL扫描的是数据表。索引树比数据表小只存键值所以index通常比ALL快。但当查询只涉及索引列时覆盖索引index可能较快如果涉及非索引列index仍需回表实际效率也很低。两者都是需要优化的至少要达到range级别。追问2Extra中的Using index和Using where有什么区别回答要点Using index表示覆盖索引不需要回表Using where表示Server层额外过滤。详细回答Using index查询所需的数据全部在索引中不需要回表读取数据行。这是最理想的情况被称为覆盖索引Covering Index。Using where存储引擎层返回数据后Server层还需要进行额外的条件过滤。如果WHERE条件字段有索引通常不会出现Using where除非索引无法完全覆盖筛选条件。追问3possible_keys有值但key是NULL说明什么回答要点MySQL选择了全表扫描认为索引成本更高。详细回答说明MySQL虽然识别到有可用的索引possible_keys但经过成本估算后认为走索引不一定比全表扫描快——可能因为数据量小、索引区分度低、或者查询需要回表的数据太多。常见原因查询条件中的数据占表比例过高如WHERE status active而大部分数据都是activeMySQL会认为全表扫描更划算。追问4key_len怎么解读有什么用回答要点key_len表示实际使用的索引列长度可用于判断联合索引的使用情况。详细回答key_len是实际使用的索引列占用的字节数。联合索引(a, b, c)通过key_len可以判断使用了哪几列如果key_len等于a列的长度只用到了第一列如果key_len等于ab列的长度用到了前两列如果key_len等于abc列的长度用到了全部三列这有助于分析联合索引是否完全生效。追问5filtered字段怎么看值高低代表什么回答要点filtered表示存储引擎返回的数据中符合WHERE条件的比例越高越好。详细回答filtered值表示存储引擎返回的行中有多少比例实际满足WHERE条件。100%表示所有返回的行都符合条件此时没有不必要的Server层过滤。例如rows1000filtered50%表示存储引擎返回1000行Server层过滤后只剩500行有效。这时可以考虑在WHERE条件字段上加索引让存储引擎直接过滤掉更多数据减少Server层压力。追问6什么是回表如何通过EXPLAIN判断是否回表回答要点回表是指通过二级索引找到主键后再通过主键索引查询完整数据行的过程。详细回答在InnoDB中二级索引的叶子节点存储的是主键值。当查询需要索引中没有的字段时MySQL需要先通过二级索引找到主键再到聚簇索引中查找完整行数据——这个过程就是回表。通过EXPLAIN判断Extra中有Using index→ 覆盖索引不回表Extra中没有Using index且查询了非索引列 → 需要回表可以尝试建立覆盖索引消除回表 避坑指南序号错误做法正确做法后果1只看type不看Extra结合所有字段综合判断漏看filesort/temporary2rows越小就一定快rows是预估还要看type和Extra误判优化效果3见到Using filesort就恐慌大结果集排序必然需要filesort盲目优化4忽略possible_keys为NULL检查WHERE条件字段是否有索引漏建索引5只看EXPLAIN不验证实际效果用SHOW PROFILES验证前后耗时优化无效6同时生产环境直接用EXPLAIN可在测试环境验证后再上生产无风险 可运行验证代码-- 准备测试数据CREATETABLEexplain_test(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(50),ageINT,statusVARCHAR(20),create_timeDATETIME,KEYidx_age(age),KEYidx_status(status),KEYidx_age_status(age,status));INSERTINTOexplain_test(name,age,status,create_time)SELECTCONCAT(user,n),FLOOR(RAND()*100),ELT(FLOOR(RAND()*3)1,active,pending,inactive),NOW()-INTERVALFLOOR(RAND()*365)DAYFROM(SELECTrow:row1ASnFROM(SELECT1UNIONSELECT2UNIONSELECT3)t1,(SELECTrow:0)r)tmp;-- 1. 基本EXPLAIN用法EXPLAINSELECT*FROMexplain_testWHEREage25;-- 2. 查看全表扫描无索引字段EXPLAINSELECT*FROMexplain_testWHEREcreate_time2024-01-01;-- 3. 查看联合索引使用情况EXPLAINSELECT*FROMexplain_testWHEREage25ANDstatusactive;-- 4. 查看覆盖索引EXPLAINSELECTage,statusFROMexplain_testWHEREage25;-- 5. 查看ORDER BY是否走索引EXPLAINSELECT*FROMexplain_testWHEREage25ORDERBYstatus;-- 6. 使用FORMATJSON查看更详细的信息EXPLAINFORMATJSONSELECT*FROMexplain_testWHEREage25;-- 7. EXPLAIN ANALYZEMySQL 8.0.18可显示实际执行耗时EXPLAINANALYZESELECT*FROMexplain_testWHEREage25;❓ 评论区挑战问题以下关于EXPLAIN执行计划的描述哪一个是错误的EXPLAINSELECT*FROMusersWHEREname张三ORDERBYage;-- type: ALL-- possible_keys: NULL-- key: NULL-- rows: 100000-- Extra: Using where; Using filesortA.typeALL表示这个查询是全表扫描需要优化B.possible_keysNULL表示没有可用的索引需要为name字段建索引C.rows100000表示实际扫描了10万行数据D.Extra中有Using filesort表示排序没有使用索引需要优化 欢迎在评论区写出你的答案和理由我会在下一篇文章发布后更新本文公布答案及错误选项逐项解析。✅ 答案公布正确答案C.rows100000表示实际扫描了10万行数据解析rows表示的是预估扫描行数不是实际值实际的扫描行数可能和rows有出入选项A正确typeALL表示全表扫描选项B正确possible_keysNULL表示没有可用索引选项D正确Using filesort表示额外排序错误选项逐项解析AALL是全表扫描正确。typeALL是最差的访问类型。Bpossible_keysNULL表示没索引正确。没有可用的索引候选。DUsing filesort需优化正确。表示需要额外排序操作。Crows表示实际扫描行数错误。rows是MySQL优化器的预估值不是实际值。 总结字段作用优化目标type访问类型至少range争取refkey实际使用的索引非NULL且匹配查询条件rows预估扫描行数越小越好Extra额外信息尽量出现Using index避免filesort/temporarypossible_keys候选索引有值且与key匹配key_len使用到的索引长度能覆盖查询条件联合索引使用情况filtered过滤后的比例越高越好接近100%低值说明需优化WHERE条件select_type查询类型尽量SIMPLE避免子查询/派生表面试官最看重的三个点type排序能准确说出从好到差的顺序知道优化目标Extra含义能解释Using index覆盖索引、Using filesort额外排序、Using temporary临时表rows是预估知道rows不是实际值但可用于判断优化效果 系列导航上一篇面试官问慢SQL如何定位与优化下一篇预告面试官问MyBatis面试专题含源码MP待发布全部85题目录点击查看关注专栏每周2-3篇一键追更搭配学习效果更佳本篇图解帮你快速建立知识画面记忆如果想深入理解源码实现和实战避坑细节可以配合姊妹系列《Java 100天进阶之路》对应章节一起学从零基础到上岗就业108篇完整学习地图每篇标配生活类比 可运行代码 避坑表 面试高频题 练习题不背八股文真正讲透“为什么”。 《Java 100天进阶之路》完整目录导航学习建议图解系列负责“快速建立知识图谱”进阶系列负责“深入理解原理”两个系列搭配使用面试备考效率翻倍。你遇到过因为没看懂EXPLAIN导致加错索引的情况吗或者通过EXPLAIN发现了什么隐藏性能问题欢迎评论区分享你的故事