1. 项目概述为什么SQL优化绕不开Explain做后端开发或者数据库管理最怕的就是线上慢查询。用户页面转圈圈DBA半夜打电话十有八九是某条SQL语句在数据库里“卡住了”。这时候你光盯着代码逻辑看是没用的你得知道数据库内部到底是怎么执行你这条SQL的。它用了哪个索引是全表扫描还是走了索引覆盖有没有做临时表或者文件排序这些问题就是EXPLAIN命令要回答的。EXPLAIN中文常译作“执行计划分析”是MySQL、PostgreSQL等关系型数据库提供的一个诊断工具。它不是去运行你的SQL而是让数据库的优化器告诉你“如果我现在要执行这条SQL我打算怎么干。” 通过解读这份“作战计划”我们能精准定位SQL的性能瓶颈比如发现它本该走索引A却阴差阳错走了全表扫描或者本可以一次关联完成却做了多次嵌套循环。很多朋友觉得EXPLAIN的输出结果字段多、概念抽象看了官方文档还是一头雾水。实际上你不需要一次性记住所有细节但必须掌握几个核心字段的“黑话”。这就像老司机看汽车仪表盘不需要懂所有故障码但转速、车速、油温这几个关键指标必须门清。本文将带你像老司机一样看懂EXPLAIN这份“性能仪表盘”把慢SQL的病灶一个个揪出来。2. EXPLAIN执行计划核心字段全解执行计划的结果通常以表格形式返回每一行代表查询中一个操作比如访问表、进行连接、排序等。不同数据库的具体字段略有差异但核心思想相通。我们以最常用的MySQL的EXPLAIN或EXPLAIN FORMATJSON为例深入拆解每个关键字段的含义和实战价值。2.1 身份标识id、select_type与table这三个字段告诉你当前行描述的是哪个查询、什么类型、操作哪张表。id (查询序列号)这是查询执行的顺序标识。规则很简单id相同执行顺序从上到下。通常出现在子查询或连接查询中表示这些操作是同一层级的按EXPLAIN输出顺序执行。id不同如果是子查询id序号会递增。id值越大优先级越高越先执行。这很像程序里的函数调用栈最内层的子查询id最大最先执行。id相同和不同同时存在id相同的可以理解为一组组内从上到下执行所有组中id值越大的组优先级越高越先执行。select_type (查询类型)这个字段说明了这一行对应的是简单查询还是复杂查询里的哪一部分。常见且重要的类型有SIMPLE最简单的SELECT查询不包含子查询或UNION。PRIMARY查询中若包含任何复杂的子部分最外层的查询被标记为PRIMARY。可以理解为“主查询”。SUBQUERY在SELECT或WHERE列表中包含了子查询该子查询被标记为SUBQUERY。DERIVED在FROM列表中包含的子查询会被标记为DERIVED衍生MySQL会递归执行这些子查询把结果放在临时表里。这是一个需要警惕的信号因为创建和访问临时表通常有性能开销。UNIONUNION中的第二个或后面的SELECT语句。UNION RESULT从UNION表获取结果的SELECT。table (访问的表)显示这一行数据是关于哪张表的。有时你看到的不是表名而是derivedN这里的N就是id值指代id为N的查询产生的衍生临时表。unionM,N指代id为M和N的查询进行UNION操作后的结果集。注意看到DERIVED或derivedN时要特别关注。如果衍生表数据量很大会导致大量数据被写入临时表可能在磁盘严重影响性能。优化思路通常是尝试将子查询重写为JOIN或者确保子查询内部有高效的索引。2.2 访问策略type与possible_keys、key这是EXPLAIN的精华所在直接反映了数据库查找数据的方式性能好坏天差地别。type (访问类型)表示MySQL决定如何查找表中的行。从最优到最差常见的类型有system const eq_ref ref range index ALL。system表只有一行记录等于系统表这是const类型的特例几乎遇不到。const通过索引一次就找到了用于比较主键或唯一索引的所有列与常数值。比如WHERE id 1。速度极快。eq_ref唯一性索引扫描对于每个来自前表的行组合从本表中读取一行。常见于主键或唯一索引作为连接条件的多表查询。性能非常好。ref非唯一性索引扫描返回匹配某个单独值的所有行。比如在非唯一索引的列上使用等值查询WHERE col value。这是一种很常见的、高效的访问类型。range只检索给定范围的行使用一个索引来选择行。关键是在WHERE子句中出现了BETWEEN、、、IN()等范围查询。它比全索引扫描index好因为它只需要扫描索引树的某一部分。index全索引扫描Full Index Scan。index与ALL的区别是index只遍历索引树。这通常比ALL快因为索引文件通常比数据文件小。但如果需要回表查数据开销也不小。ALL全表扫描Full Table Scan。这意味着MySQL必须扫描整张表来找到匹配的行。这是需要极力避免的情况尤其是在大表上。possible_keys 与 keypossible_keys显示查询可能使用哪些索引。如果为空表示没有可用的索引。key显示查询实际决定使用的索引。如果为NULL则表示没有使用索引。实操心得possible_keys列出了一堆索引但key是NULL这通常是个坏信号。说明MySQL认为使用这些索引的成本比全表扫描还高可能因为数据分布、索引选择性差或者查询需要回表的数据量太大。这时你需要检查索引设计或重写查询。反之如果key使用了你期望的索引那至少访问路径是对的。2.3 扫描评估key_len、ref、rows与filtered这几个字段用于评估索引的使用效率和需要扫描的数据量。key_len (索引长度)表示查询中实际使用到的索引字段的总字节数。通过这个值你可以判断索引是否被“充分”使用。计算规则取决于字段定义。例如一个INTNOT NULL是4字节INTNULL是5字节多1字节存储NULL标志。VARCHAR(100)UTF8且NOT NULLkey_len 100*3 2变长字段长度标识 302字节。实战意义如果你建了一个复合索引(col1, col2, col3)查询条件是WHERE col11 AND col22那么key_len应该是col1和col2的长度之和。如果key_len只等于col1的长度说明索引只用了第一列col2没用上索引。ref显示索引的哪一列被使用了如果可能的话是一个常数。常见值有const常量值。库名.表名.列名来自其他表的列。func使用函数的结果。rows (预估扫描行数)MySQL根据统计信息估算出执行当前查询需要扫描多少行记录。这是一个预估值但非常关键。注意这是一个估算值有时和实际偏差很大。但如果这个值非常大比如几万、几十万那这条查询几乎肯定是慢的。filtered (过滤百分比)这是一个百分比值表示存储引擎返回的数据在经过WHERE条件过滤后剩余数据量的百分比。rows * filtered / 100可以估算出将要和下一张表进行连接的行数。理想情况下是100表示返回的行完全满足条件。值越小说明过滤效果越差需要传递给下一阶段的数据越多性能越差。2.4 额外信息ExtraExtra字段包含了不适合在其他列显示但非常重要的额外信息。很多性能问题在这里露出马脚。Using index (索引覆盖)这是最好的情况之一。查询的列都包含在索引中引擎只需要读取索引就能返回结果无需回表查询数据行。性能提升显著。Using where表示MySQL服务器在存储引擎返回行之后再应用WHERE条件进行过滤。如果type是ALL或index出现这个就说明性能不佳因为所有行都被读取了再在内存里过滤。Using temporary危险信号。表示查询需要创建临时表来保存中间结果常见于GROUP BY和ORDER BY子句的列不同或者DISTINCT操作。临时表可能在内存或磁盘创建磁盘临时表性能极差。Using filesort另一个危险信号。表示MySQL无法利用索引完成排序需要额外的排序步骤。这个排序可能在内存或磁盘完成称为“文件排序”。如果数据量大会非常慢。Using join buffer表示使用了连接缓冲区。当被驱动表join中的第二张表没有可用索引时可能会分配一块内存join buffer来加速连接过程。这提示你可能需要为连接字段添加索引。Impossible WHEREWHERE子句的条件永远为假查不到任何数据。踩坑记录我曾遇到一个分页查询巨慢EXPLAIN显示Using filesort。原因是ORDER BY create_time DESC和WHERE status1同时存在但索引是(status, create_time)。由于status是范围查询等值也算一种范围导致索引在status之后的部分create_time无序无法避免排序。后来将索引改为(status, create_time DESC)MySQL 8.0支持降序索引或者考虑其他分页方案才解决。Extra里的信息往往是优化的直接突破口。3. 实战演练从Explain结果反推优化方案光看理论不够我们结合几个典型的慢查询场景手把手教你如何分析EXPLAIN输出并制定优化策略。3.1 案例一全表扫描typeALL的优化问题SQLSELECT * FROM users WHERE phone 13800138000 AND is_deleted 0;假设users表有百万数据phone字段有索引但is_deleted没有。执行计划关键信息type: ALLkey: NULLrows: 1000000Extra: Using where分析typeALL且keyNULL说明没走索引进行了全表扫描。ExtraUsing where说明是在扫描所有行后再用WHERE条件过滤。虽然phone有索引但优化器可能认为phone13800138000筛选出的行数仍然很多如果phone索引选择性不高或者因为查询包含了*所有列即使走phone索引也需要回表查所有列成本估算后认为不如全表扫描。优化方案创建复合索引这是最直接的方案。为(phone, is_deleted)创建复合索引。这样查询可以快速定位到phone13800138000的索引叶子节点并且索引中已经包含了is_deleted信息可以直接过滤最后只对少量满足条件的行进行回表。ALTER TABLE users ADD INDEX idx_phone_deleted (phone, is_deleted);优化后type应变为refkey显示为idx_phone_deletedrows大幅下降。使用覆盖索引如果业务上不需要所有列可以只查询索引包含的列或主键。例如如果只是检查是否存在或获取IDSELECT id FROM users WHERE phone 13800138000 AND is_deleted 0;如果索引(phone, is_deleted)包含了查询的所有列这里是id主键一定在二级索引叶子节点中就会出现Using index性能最佳。3.2 案例二索引失效与文件排序Using filesort问题SQLSELECT * FROM orders WHERE user_id 100 AND status PAID ORDER BY amount DESC LIMIT 10;假设已有索引idx_user_status (user_id, status)。执行计划关键信息type: refkey: idx_user_statusrows: 5000Extra: Using filesort分析typeref且使用了idx_user_status索引说明在查找user_id100 and statusPAID的记录时效率是高的。rows5000估算有5000条记录符合前两个条件。问题出在Extra: Using filesort。我们的排序条件是amount DESC但索引是(user_id, status)索引中amount是无序的。因此MySQL不得不将筛选出的5000条记录收集起来在内存或磁盘上进行一次额外的排序才能应用LIMIT 10。优化方案创建包含排序列的复合索引将排序列加入索引尾部形成(user_id, status, amount)。这样对于固定的user_id和status索引本身就已经按照amount排好序了默认升序DESC可以反向扫描。MySQL可以直接按索引顺序读取前10条完全避免filesort。ALTER TABLE orders ADD INDEX idx_user_status_amount (user_id, status, amount);优化后Extra中的Using filesort会消失。权衡索引长度amount如果是DECIMAL类型将其加入索引会增加索引大小。需要评估user_id和status的筛选能力如果筛选后行数很少比如只有几十条那么filesort的成本可能低于维护一个大索引的成本。这时可以保持原索引通过SHOW PROFILES等工具对比两种方案的实际执行时间。3.3 案例三衍生表与临时表Using temporary问题SQLSELECT t1.* FROM ( SELECT user_id, MAX(login_time) as last_login FROM user_logs GROUP BY user_id ) AS t1 JOIN users u ON t1.user_id u.id WHERE u.country CN;执行计划关键信息第一行子查询select_type: DERIVED,table: user_logs,Extra: Using temporary; Using filesort第二行主查询table: derived2,type: ALL分析 这个查询很典型。首先内层子查询SELECT ... GROUP BY user_id被标记为DERIVED并且因为GROUP BY触发了Using temporary创建临时表来分组和Using filesort排序以进行分组。这个临时表衍生表derived2没有索引。然后外层查询需要将这个临时表与users表进行连接对derived2的访问类型是ALL全表扫描如果衍生表数据量大性能会非常差。优化方案尝试消除衍生表重写为JOIN很多时候子查询可以转化为更高效的JOIN。但本例中因为内层是聚合查询MAX直接JOIN可能改变语义需要小心。为聚合查询创建高效索引对于内层SELECT user_id, MAX(login_time) FROM user_logs GROUP BY user_id最优索引是(user_id, login_time)。这是一个典型的“松散索引扫描”或“覆盖索引”场景索引本身已经按user_id分组并且login_time在同一组内有序可以快速找到最大值避免临时表和文件排序。ALTER TABLE user_logs ADD INDEX idx_user_login (user_id, login_time);优化后内层查询的Extra应变为Using index for group-by或类似信息Using temporary和Using filesort会消失。考虑物化视图或定期汇总如果这是频繁执行的报表查询可以考虑在业务低峰期预计算每个用户的最后登录时间存入一张汇总表查询直接扫汇总表代价最小。4. 进阶技巧与深度排查指南掌握了基础字段和常见案例你已经能解决80%的问题。下面这些进阶技巧能帮你应对更复杂的场景。4.1 使用 EXPLAIN FORMATJSON 获取更详细信息标准的EXPLAIN输出是表格信息有限。MySQL 5.6提供了EXPLAIN FORMATJSON它会输出一个详细的JSON文档包含了成本估算、访问路径的详细选择等海量信息。EXPLAIN FORMATJSON SELECT ...;在JSON输出中重点关注query_cost字段它代表了优化器估算的该执行计划的相对成本。对比不同查询写法或索引下的query_cost可以直观看出优化器认为哪个方案更优。此外JSON格式还包含了每个步骤的详细输入输出行数、过滤条件等对于深度分析嵌套查询、复杂连接特别有用。4.2 结合 SHOW WARNINGS 查看优化器重写有时候你写的SQL会被优化器“重写”以尝试优化。执行EXPLAIN后紧接着执行SHOW WARNINGS;可以看到优化器重构后的查询语句。这对于理解为什么优化器选择了某个特定索引或者为什么你的子查询被转换成了连接非常有帮助。4.3 关注索引选择性Cardinalityrows字段是估算值其准确性依赖于索引的“区分度”即索引基数Cardinality。你可以通过SHOW INDEX FROM table_name;查看。Cardinality值越接近表总行数索引选择性越好优化器越倾向于使用它。如果发现优化器严重误判了行数例如rows估算100实际扫描10万行可能是表的统计信息过期了。这时可以手动更新统计信息ANALYZE TABLE table_name;4.4 强制索引与忽略索引的用法大多数时候应该相信优化器但如果你确信优化器选错了索引可以使用索引提示Index Hints来干预。USE INDEX (index_name)建议优化器使用某个索引优化器仍可能选择其他索引。FORCE INDEX (index_name)强制优化器使用某个索引。这是在你有充分理由时的最后手段。IGNORE INDEX (index_name)忽略某个索引。SELECT * FROM users USE INDEX(idx_phone) WHERE phone LIKE 138% AND status1;注意事项强制索引是一把双刃剑。数据分布会随时间变化今天高效的索引强制明天数据量变了可能就成了性能杀手。因此强制索引通常只作为临时解决方案长期方案应该是优化索引设计或查询语句或者更新统计信息帮助优化器做出正确判断。4.5 排查连接查询的性能瓶颈对于多表JOINEXPLAIN结果的每一行对应一个表。分析时要从id最小的行开始看驱动表。驱动表的选择至关重要理想情况下应该是筛选后行数最少的表。查看驱动表的rows和filtered它们的乘积决定了要循环多少次去探查下一张表被驱动表。对于被驱动表第二张及以后的表其type应该至少是ref或eq_ref。如果出现ALL全表扫描通常意味着连接字段缺少索引这是主要的性能瓶颈必须为被驱动表的连接字段添加索引。留意Extra字段中的Using join buffer这明确提示被驱动表没有有效索引可用导致使用内存缓冲来加速应尽快添加索引。5. 系统化SQL优化检查清单最后我将日常工作中使用EXPLAIN进行SQL优化的步骤总结成一份检查清单。当你面对一条慢SQL时可以按此顺序排查执行EXPLAIN对慢SQL执行EXPLAIN或EXPLAIN FORMATJSON。看type列是否出现ALL或index如果是优先解决。目标是提升到range、ref或更高。看key列实际使用的索引是否合理如果为NULL考虑添加或优化索引。看rows列估算扫描行数是否过大如果很大看能否通过更优的索引条件减少扫描范围。看Extra列是否有Using temporary或Using filesort尝试通过调整索引包含排序列、分组列或重写查询来消除。Using filesort检查ORDER BY/GROUP BY的列是否在索引中且顺序符合“最左前缀”原则。Using temporary检查GROUP BY和ORDER BY的列是否不同或是否使用了DISTINCT。看filtered列如果值很低如小于10%说明索引过滤效果差可能需要优化查询条件或考虑复合索引。分析连接顺序对于多表连接检查驱动表选择是否合理rows * filtered最小的表作为驱动表通常更优被驱动表是否有高效索引连接字段。考虑索引覆盖检查查询的列是否都可以从索引中获取避免回表。Extra中出现Using index是理想状态。验证与对比根据分析结果实施优化如加索引、改SQL。优化后再次执行EXPLAIN和实际查询对比优化前后的type、rows、Extra以及实际执行时间。长期监控优化不是一劳永逸的。表数据量的增长、数据分布的变化都可能使今天高效的执行计划明天变得低效。对核心查询建立监控是必要的。读懂EXPLAIN就像是拿到了数据库引擎的“诊断报告”。它不会直接告诉你答案但会给你所有线索。真正的优化功夫在于如何根据这些线索结合业务逻辑和数据特点设计出最有效的索引和查询语句。这个过程没有银弹需要不断地实践、观察和调整。从我自己的经验来看养成在开发阶段就对复杂SQL执行EXPLAIN的习惯远比在线上出问题后再来救火要划算得多。