我遇到过这么一次线上事故一条平时只要几十毫秒的SQL突然就飙到八百多毫秒直接触发接口超时报警。一开始我的反应和大多数人一样怀疑是不是索引没生效赶紧补了个索引上去结果一点变化都没有。后来老老实实把这条SQL丢进explain里看执行计划才发现问题根本不在where条件上而在order by字段上——优化器为了排序宁可放弃我加的那个索引。从那以后我养成了一个习惯凡是SQL慢过一次二话不说先explain。这篇文章就把我这些年用explain执行计划排查慢SQL的经验整理一下。不管你是刚接触MySQL的初学者还是写了好几年业务代码但没系统看过执行计划的开发又或者是正在准备面试想补一补底层知识这篇文章都能帮你把explain从“会用”提升到“会看、会分析、会优化”。1. 为什么我说explain是SQL优化的第一入口很多人一提到SQL优化第一反应是“加索引”。但加索引之前得先搞清楚一个问题这条SQL到底慢在哪是全表扫描是索引没用上是排序太慢还是join的时候没有合适的关联字段这些问题explain一张表就能告诉你答案。explain其实是MySQL提供的一个执行计划查看工具。你在一条SELECT语句前面加上EXPLAIN关键字MySQL并不会真的去执行这条SQL而是让优化器把“我打算怎么执行这条SQL”的方案告诉你——包括以什么顺序访问哪些表、预估扫描多少行、会用到哪个索引、是不是需要临时表或文件排序等等。我用一个生活化的类比来解释一下。explain就像是去医院体检时拿到的那张体检报告单。医生的诊断还没开始但各项指标已经出来了血常规正常、肝功能偏高、血脂有异常。对应到SQL里type是核心指标、rows是血压值、Extra是各项备注。真正厉害的DBA扫一眼explain输出就能判断这条SQL“身体”是否健康问题出在哪个器官上。适合什么人重点学这个如果你是后端开发每天要写大量CRUDexplain是你判断自己写的SQL是否高效的最直接工具如果你是DBA或者专职做性能优化的工程师explain更是吃饭的家伙。就算你只是准备面试explain执行计划也是MySQL知识体系里几乎必考的一块——它能串起索引结构、优化器逻辑、回表、覆盖索引、索引下推这一整条链路。很多人以为explain很难其实它就是把一条SQL的“执行思路”摊开给你看关键是你得知道每一列在说什么。下面我就按字段分组来拆。2. 一张执行计划里的字段得按组来读才不乱直接用一条实际SQL来演示。假设我有两张表一张用户表user一张订单表order_info查询某状态下用户最近的订单EXPLAIN SELECT u.id, u.nickname, o.order_no FROM user u JOIN order_info o ON u.id o.user_id WHERE u.status 1 AND o.create_time 2024-01-01 ORDER BY o.create_time DESC;执行计划输出大概是这样的关键列我保留其他省略-------------------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | -------------------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | u | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1000 | 10.00 | Using where | | 1 | SIMPLE | o | NULL | ref | idx_user_id | idx_user_id | 4 | test.u.id | 5 | 33.33 | Using where | --------------------------------------------------------------------------------------------------------------------------------新手最容易犯的错就是一上来死磕type那一列的英文单词却忽略了整体结构。我个人的读法是分五组看下面逐个说。2.1 id、select_type、table先看懂SQL的执行结构这三列回答的是“这条SQL是几条SQL组成的、先执行谁、后执行谁、操作哪张表”。id是SELECT的标识符。id越大越先执行id相同则从上往下执行。如果看到id为NULL那一般是合并结果集的临时表不需要太纠结。多表join时id相同的行执行顺序是“从上往下”也就是驱动表在前、被驱动表在后。子查询场景下id会递增先执行的是最内层的子查询。这里有一个很多人容易理解反的点id越大的反而越先执行。比如id为2的子查询会比id为1的外层查询先跑。因为外层查询要拿到子查询的结果才能继续。这个顺序和“数字大的优先”正好相反算是新手最常见的误解之一。select_type描述这个SELECT在语句中的角色。最常见的几个值SIMPLE没有子查询、没有UNION的简单查询PRIMARY最外层的查询SUBQUERY出现在WHERE或SELECT列表里的子查询DERIVEDFROM后面的子查询也就是派生表UNIONUNION中第二个及之后的SELECTtable就是这步要访问的表名。这里偶尔会看到derived2、union1,2这种奇怪的“表名”意思是这一步访问的是id为2的派生表或者id为1、2两个结果合并后的临时表。看到这种名字说明这条SQL里有子查询或UNION结构上已经比简单查询复杂了先有个心理准备。2.2 type访问类型这是整张体检单的主指标type这一列是几乎所有MySQL调优文章都会重点强调的字段。它表示MySQL访问这张表时用的是哪种方式从好到差大概是这样system const eq_ref ref range index ALL我挨个解释一下顺便给每个都配个生活里能对上的场景。system表里只有一行数据。这种基本见不到只有MyISAM表且恰好只有一行时可能出现。见到了也说明不了什么因为数据量太小没有参考意义。const用主键或唯一索引等值匹配最多返回一行。比如WHERE id 1MySQL知道这个条件只能命中一条记录直接按常量处理。这就像你知道某人身份证号直接去户籍系统按身份证号精确查一个人结果必然唯一。eq_ref联表查询时被驱动表用主键或唯一索引做等值匹配。比如上面例子里如果把join条件改成o.id u.id这种唯一键关联被驱动表就会是eq_ref。每一次驱动表给过来一个值被驱动表都能通过唯一索引立刻定位到一行。想象一下按门牌号找一户人家一户一地址精确制导。ref使用普通索引做等值匹配可能返回多行。比如WHERE user_id 288765user_id上有普通索引同一个用户可能有多条订单。类似按快递单上的收件人名字找包裹——同名的人可能有好几个但至少比把整个仓库翻一遍快得多。range索引范围扫描。、、BETWEEN、IN这类条件MySQL能通过索引直接定位到范围的起点然后顺序扫到终点。比如WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31只要create_time有索引就是range。index扫描全部索引行。听起来也是“全扫”但比ALL好一点因为索引树比数据页小扫描成本低。常见于查询的列恰好都在某个索引里但无法利用索引做精确过滤只能把整个索引都过一遍。这就像查字典通读部首目录虽然没直接定位到字但至少不用翻完整本字典正文。ALL全表扫描最差的一种。意味着MySQL要把整张表的数据页从头到尾读一遍。数据量一大这就是噩梦。出现ALL时先别急着骂MySQL“怎么这么笨”大概率是没建索引或者建了索引但因为某些原因用不上这个后面细说。大多数人判断SQL健不健康先看type这一列就够了——如果是range以上级别const、ref、range问题不大如果掉到index甚至ALL就得高度警惕了。2.3 possible_keys、key、key_len索引的“有没有”和“用了多少”这一组是跟索引直接相关的三兄弟。possible_keys列出MySQL在优化时“可能”会用到的索引。注意只是可能不代表真的用了。key实际选用的索引。如果这一列是NULL说明possible_keys里的索引一个都没用上执行计划大概率要往ALL那边跑。key_len实际使用的索引字节长度。这个字段很多人不重视但我认为它是判断“联合索引到底用到了哪几列”的关键证据。比如一个联合索引(a, b, c)如果key_len只等于a这一列的长度说明只有a列参与了索引查找b和c都没用上。这在实际开发中太常见了——明明建了三列联合索引SQL条件里也写了b和c但因为条件顺序、范围查询、函数包裹等原因优化器实际只用到了第一列。key_len的计算规则要心里有数INT类型4字节BIGINT8字节VARCHAR(n)在utf8mb4字符集下n * 4 2字节2字节记录可变长度字段允许为NULL再加1字节DATE是3字节、DATETIME是5字节8.0版本、TIMESTAMP是4字节举个例子VARCHAR(50)的utf8mb4列允许NULL那它的key_len是50*421 203字节。如果你在explain里看到key_len 203就说明这一列被完整用到了。用这个办法你就能反推联合索引实际用了哪几列非常实用。2.4 ref、rows、filtered成本估算的照妖镜ref表示key那一列选中的索引列是用哪个表的哪个字段来匹配的。比如上面例子的ref是test.u.id意思是order_info表的idx_user_id索引是用user表的id字段的值去做等值匹配的。如果是常量条件这里会显示const。rows这是优化器估算的“为了拿到结果可能需要扫描的行数”。注意关键词估算。它不是真实值是基于表和索引的统计信息算出来的。但这个估算值非常能说明问题——同样的SQL如果rows从几十万优化到几百那性能提升是必然的。rows越大说明走这条路成本越高。filtered表示从扫描到的行中经过WHERE条件过滤后最终能留下多少百分比的数据。这个值要和rows配合看。比如rows1000、filtered10意味着扫描1000行后大约只有100行是满足条件的。官方文档里给出的“最终估算扫描行数”是rows * filtered / 100。filtered值越低说明扫描的浪费比例越大索引的筛选性越差。我在实际工作中看执行计划一般是看这个估算值对不对得上真实的业务数据量。如果一条SQL查的是某用户最近一个月订单实际数据也就几十条但rows估算出几万条那说明优化器对索引的分布统计已经不准确了可能需要ANALYZE TABLE重新收集统计信息。2.5 Extra执行细节里的彩蛋和坑Extra这一列信息量很大而且经常藏着决定性能的“关键先生”。常见的几个值必须认识。Using index这是个好信号表示“覆盖索引”。查询所需的所有列都能在索引里直接拿到不需要回表。比如索引是(user_id, status)查询只select了这两列那就直接走索引就把数据取完了。这个状态是最理想的能省掉回表的随机IO。Using index condition表示用到了“索引下推”ICPIndex Condition Pushdown。简单说以前是先通过索引找到记录、回表拿到整行再用WHERE条件过滤有了索引下推之后MySQL会把WHERE条件里能判断的部分“下推”给存储引擎在读取索引的时候就先过滤掉一部分不满足条件的记录减少回表次数。这个机制在MySQL 5.6之后默认开启对联合索引的使用帮助很大。Using where表示MySQL在存储引擎返回记录后又进行了一层过滤。通常出现在你的查询列不全在索引里、或者条件里包含了无法用索引判断的部分时。单看Using where不一定是坏事但如果它跟typeALL同时出现就等于“全表扫一遍逐行过滤”。Using temporary查询过程中使用了临时表来辅助计算。常见于GROUP BY、DISTINCT、某些ORDER BY场景。临时表可能落在内存也可能落到磁盘后者性能极差。看到这个值基本可以断定这条SQL需要改写了。Using filesort排序时无法利用索引的有序性只能额外排序。注意filesort不一定是真的在磁盘文件里排但一定是一种额外的开销。这是我最常在慢SQL里见到的坑——很多人建索引只考虑WHERE条件把ORDER BY字段抛在脑后结果排序全靠filesort。后面第5章我会专门给一个因为filesort导致慢查询的完整案例。Using join bufferjoin的时候被驱动表没有可用索引MySQL只能用join buffer把驱动表的数据缓存起来然后把被驱动表全表扫一遍去匹配。看到这个基本等于说你的join关联字段没建索引必须改。Impossible WHEREWHERE条件恒为假。比如WHERE 12优化器直接判定不用查了。这其实不算问题但看到这个值可以帮你发现某些写死的逻辑错误。3. 判断SQL健康程度我最看重这三个指标的配合字段都认识了但单独看单个字段容易误判。我在实际排查中真正最核心的是看type、key/rows、Extra这三者的“配合关系”。先说type和rows的配合。typeref加上rows5说明走了一个普通索引估算扫描5行这是很健康的组合。typeALL加上rows6000000那这条SQL几乎就是在做全表遍历无条件可谈要立刻优化。再说key和Extra的配合。key里有索引但Extra出现了Using filesort说明索引用在了查询过滤上但排序字段没有吃上索引。key里有索引、Extra里是Using index这叫完美——过滤、排序、取值全在索引里闭环了。我用表格把这几种常见组合总结一下方便你快速对照typekeyExtra结论const主键索引NULL极优点查ref普通索引Using index优覆盖索引查询ref普通索引Using where良需要回表过滤ref普通索引Using filesort中过滤还行排序需优化range索引Using index condition良范围查询索引下推ALLNULLUsing where差全表扫描逐行过滤ALLNULLUsing filesort极差全表扫排序典型慢SQL这个表格不是绝对标准但能帮你快速建立直觉。看到后两种组合基本可以直接定位为“需要优化的高危SQL”。还有一个细节经常被忽略rows的估算值和真实数据量对不上。优化器依赖表统计信息做估算如果统计信息过期或者采样不准rows可能和实际情况差很多。这时候光看执行计划判断还不够还要结合慢查询日志的实际耗时、或者EXPLAIN ANALYZE来看真实执行成本。4. explain不会告诉你的事索引失效的真实原因explain能告诉你索引“用没用”但很多时候不会直接告诉你“为什么用不上”。这一节我专门讲几个日常开发里最常踩的索引失效场景都是我在实际项目里遇到过的。第一个隐式类型转换。比如user_id字段在表里是VARCHAR(32)你写WHERE user_id 288765MySQL会把字符串字段和数字进行比较它默认会把字段转成数字再去比较结果索引就失效了。表和查询里的类型不一致是索引失效最常见的原因。explain里表现出来就是possible_keys里有索引但key是NULLtype直接ALL。第二个对索引列使用函数或计算。比如WHERE DATE(create_time) 2024-01-01哪怕create_time上有索引也用不上因为MySQL必须先对每一行的create_time做DATE函数计算才能和右边的值比较。正确写法应该是WHERE create_time 2024-01-01 AND create_time 2024-01-02改成范围查询就能走索引了。第三个违背联合索引最左前缀原则。联合索引(a, b, c)你查询条件是WHERE b 1 AND c 2跳过了a列b和c是用不上索引的。同理WHERE a 1 AND b 2这种a用了范围查询之后b也没法继续走索引。这是联合索引使用中最容易出问题的地方。第四个LIKE以通配符开头。WHERE name LIKE %张因为通配符在最前面MySQL没法利用B树的有序性去定位起点只能全扫。反过来WHERE name LIKE 张%就能走索引。第五个优化器统计信息过旧。这个比较隐蔽。表数据量发生剧烈变化之后如果没更新统计信息优化器可能基于过时的rows估算做出错误判断宁可全表扫也不用索引。这时候执行计划看起来是“没走索引”但根源不是索引本身的问题而是需要ANALYZE TABLE刷新统计信息。以上这些场景在explain里的表现各不相同但核心思路是一致的explain告诉你结果你得结合表结构和SQL写法去反推原因。这个过程熟练了以后基本上看一遍执行计划脑子里就能把整个优化方向想清楚。5. 从慢SQL到执行计划再到索引优化一个完整的排查案例理论说了这么多不如完整走一遍实际排查过程。这个案例是我模拟的一个电商订单查询场景表结构和数据为演示用途已做脱敏处理但它覆盖的排查思路和我在生产环境里遇到的问题是同一类。表结构大概是这样的CREATE TABLE order_info ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, status TINYINT NOT NULL DEFAULT 0, amount DECIMAL(10,2) NOT NULL, create_time DATETIME NOT NULL, KEY idx_create_time (create_time) ) ENGINEInnoDB;表里有大约600万行数据。业务反馈说后台系统里“查某个用户最近30天已支付订单”的接口非常慢慢到快超时了。对应的SQL是这样的SELECT id, user_id, status, amount, create_time FROM order_info WHERE user_id 288765 AND status 1 AND create_time 2024-03-01 ORDER BY create_time DESC LIMIT 20;5.1 第一轮排查全表扫描的实锤把SQL丢进explain关键列如下-------------------------------------------------------------- | type | key | rows | filtered | Extra | -------------------------------------------------------------- | ALL | NULL | 6123450 | 0.37 | Using where; | | | | | | Using filesort | --------------------------------------------------------------这一眼就能看出两个大问题第一type是ALLrows估算612万也就是这条SQL打算把整张表从头扫一遍。第二Extra里有Using filesort说明排序也没法走索引需要额外排序。但这里有个有意思的点表上明明有idx_create_time这个索引为什么possible_keys是空的因为user_id才是过滤条件的主角优化器评估之后发现哪怕走create_time索引也需要扫很大一段范围再过滤user_id干脆直接全表扫了。5.2 第二轮优化加单列索引排序问题暴露第一步肯定是先给user_id加个单列索引ALTER TABLE order_info ADD INDEX idx_user_id (user_id);再explain执行计划变成了这样---------------------------------------------------------------------- | type | key | rows | filtered | Extra | ---------------------------------------------------------------------- | ref | idx_user_id | 318 | 33.33 | Using where; Using filesort | ----------------------------------------------------------------------type从ALL变成了refrows从600万降到了318这一步效果非常显著。走索引定位user_id288765的记录估算只有318行再在这318行里过滤status和create_time。但注意Extra里的Using filesort还在。也就是说WHERE用上了索引ORDER BY create_time却没法继续用索引排序MySQL需要把这318行拿出来做一次额外的排序操作。数据量只有318行的时候这个排序代价其实不高但如果某用户订单量大比如几万条那文件排序的代价就会急剧上升。5.3 第三轮优化联合索引吃掉排序既然排序字段是create_time最理想的方案是让排序也走索引。B树天然有序如果联合索引把user_id、status、create_time按顺序放进去那么对于同一个user_id、同一个status的所有记录create_time在索引里天然就是有序的排序可以直接从索引里顺序读出来。于是加联合索引ALTER TABLE order_info ADD INDEX idx_user_status_time (user_id, status, create_time);再explain------------------------------------------------------------------------- | type | key | key_len | rows | filtered | Extra | ------------------------------------------------------------------------- | ref | idx_user_status_time | 5 | 42 | 100.00 | Using index condition | -------------------------------------------------------------------------这轮的结果就很理想了type是refkey用上了联合索引key_len等于54字节的user_id 1字节的status说明联合索引的前两列都被用到了。rows降到了42行filtered变成了100%意思是扫描42行就能拿到全部目标记录。最关键的是Using filesort消失了因为create_time的有序性已经由索引天然提供。5.4 为什么不是(user_id, create_time)有人可能会问既然排序是create_time为什么中间还要放一个status直接(user_id, create_time)不是更简单吗这里的关键在于SQL里的两个等值条件和一个排序条件。如果联合索引是(user_id, create_time)那么对于user_id288765的所有记录create_time在索引里确实是有序的。但这条SQL还要过滤status 1如果status不在索引里MySQL就得从索引里把每一条记录都拿出来回表看status是不是1再排序。虽然排序借助索引消失了但回表次数多了效率也一般。而(user_id, status, create_time)这个顺序可以把两层等值过滤user_id、status都通过索引精确定位然后在这个已经缩小的集合里create_time天然有序直接顺序读取索引就能拿到全部数据。这个方案能让一次索引查找同时解决“过滤”和“排序”两个问题是更优解。当然联合索引不是越多越好。索引本身要占存储空间每次插入、更新、删除都要额外维护索引树索引过多反而会拖慢写入性能。所以加索引之前最好结合业务实际确认这类查询是高频查询值得用索引空间换查询时间。5.5 验证与收尾索引加完之后我在模拟环境验证了一下同样的SQL执行计划从“全表扫描612万行文件排序”变成了“索引定位42行无排序”。实际接口耗时从接近800ms降到了十几毫秒这个提升非常直观。这个案例也说明了一个道理explain执行计划不只是告诉你SQL慢不慢它还在一步步引导你找到“最优索引形态”。第一轮告诉你需要过滤索引第二轮告诉你排序有问题第三轮帮你验证组合索引的效果。顺着执行计划的线索走优化方向基本不会跑偏。6. 我自己总结的explain使用习惯和注意事项最后分享几个我踩过坑之后总结出来的经验这些属于那种“文档里不一定会写但实际用起来能救命”的细节。第一看执行计划不要只看type一定要看rows和Extra的配合。typeref虽然好看但rows估算出几十万行也不是没可能——普通索引的区分度太差比如性别字段上的索引typeref但rows可能接近全表的一半。这种索引建了还不如不建。所以我的习惯是type决定“访问方式”rows决定“访问量”Extra决定“有没有隐藏代价”三者缺一不可。第二排查线上慢SQL时别直接拿测试环境的小表explain。测试环境几万行数据和线上几百万行数据优化器做出的选择可能完全不同。最稳妥的做法是在预发布环境用接近线上的数据量验证或者直接对线上只读实例执行explain。explain本身不修改数据但大表上执行也是有一定资源消耗的注意避开高峰期。第三加上limit之后执行计划可能会“失真”。优化器很聪明它知道LIMIT 20意味着只要找到20行就可以停了所以可能会选择一个和“全量查询”完全不同的执行路径。排查慢SQL时我建议先跑不带limit的完整版本看看全量情况下的执行计划再带上limit对比这样能确认limit是不是掩盖了真正的性能问题。第四MySQL 8.0里可以试试EXPLAIN ANALYZE。和普通EXPLAIN只展示估算计划不同EXPLAIN ANALYZE会真实执行这条SQL并输出每一步的实际耗时、实际行数。它是验证优化器估算是否准确的最强工具。但注意线上大查询慎用因为它是真跑的。第五别忘了字段类型和字符集的一致性。我见过太多join条件里一个表是int、一个表是varchar的情况执行计划直接出现Using join buffer两张表全扫。这种问题explain看得很清楚但根本原因是建表时字段类型没对齐改SQL是治标改表结构才是治本。我自己现在排查SQL的顺序已经固定下来了先跑EXPLAIN看type和rows有没有明显异常再看Extra有没有filesort和temporary然后用慢查询日志定位真实耗时最后结合表结构和数据分布决定是加索引、改写法还是调整业务逻辑。这套流程走下来绝大多数慢SQL都能在十几分钟内找到根因。explain执行计划这个工具不复杂真正复杂的是你愿不愿意在每次写完SQL之后多花那几秒钟把它跑一遍。