1. 一次真实的慢查询事故为什么EXPLAIN是性能优化的第一站大概两个月前我们线上一个订单列表接口突然从平均 200ms 飙到了 8 秒监控面板直接亮红灯。第一反应是数据库连接池满了但看了一遍连接数、CPU、IO都没有明显异常。后来翻 slow log发现一条原本毫秒级完成的 SQL 变成了全表扫描单次执行要 7 秒多。那条 SQL 长这样SELECT o.id, o.order_no, o.amount, o.status, u.nickname FROM orders o LEFT JOIN users u ON o.user_id u.id WHERE o.created_at 2024-01-01 00:00:00 AND o.status IN (1, 2, 3) ORDER BY o.created_at DESC LIMIT 20;单独拿出来看这个查询结构非常简单索引也很齐全理论上不该慢。真正有意思的是数据量orders 表当时已经有 5000 多万行users 表也有 800 多万行。SQL 本身没变变的是数据量跨过了一个量级门槛之前走得好好的索引被优化器放弃了。这种问题不做执行计划分析光靠猜是永远猜不出来的。我当时的第一步操作就是EXPLAIN看到结果那一刻才明白性能问题不是加个索引这么简单而是要让优化器真正理解你的数据分布、索引结构、连接顺序和成本估算逻辑。这篇文章我想把这套从执行计划定位慢 SQL、到改造索引、再到验证优化效果的完整流程拆开来讲。内容以 MySQL 为主因为 EXPLAIN 在 MySQL 里最常用、信息最直观但核心思路对 PostgreSQL、SQL Server 同样适用——它们只是输出格式不同底层要解决的问题一模一样。如果你是刚入门的开发者看完可以知道一条慢 SQL 该怎么分析、索引该怎么建如果你已经写过不少 SQL我希望里面的几个案例和排查思路能帮你建立一套自己的执行计划分析方法论而不是停留在看到 typeALL 就加索引的层面。2. EXPLAIN 输出逐字段拆解读懂优化器在想什么先说一个很多人忽略的点EXPLAIN 不是执行 SQL它是让 MySQL 优化器按照成本模型估算一遍然后把它打算怎么执行这条 SQL告诉你。所以 EXPLAIN 输出的是执行计划不是真实执行结果。EXPLAIN SELECT ... 你的慢查询;在 MySQL 8.0 里输出通常包含 12 个字段。我建议不要只盯着一两个看要把关键字段组合起来解读。下面我按实际排查顺序逐个讲。2.1 id 和 select_type识别执行单元与子查询真实开销id是每个 SELECT 的编号select_type表示这个 SELECT 的类型。这两列放在一起可以快速判断查询里有多少个独立执行单元。常见的情况是 PRIMARY、SUBQUERY、DERIVED、UNION。其中DERIVED很值得留意它代表派生表也就是 FROM 子句里的子查询。MySQL 8.0 之前派生表大概率会被物化成一张临时表写入磁盘再读取代价极高8.0.14 之后有了派生表合并优化但也不是所有子查询都能合并。如果你在 EXPLAIN 结果里看到DERIVED后面跟着Materialize关键字就要警惕——这个子查询的每一行都会参与外层查询的计算成本是乘积关系。2.2 table执行计划里的每一步可能不是原表名table列表面上显示的是表名但有时候是derived2、union1,2这种形式。这代表当前步骤操作的不是原表而是第 2 步生成的派生表、或者是 UNION 合并后的结果集。这里有个实战经验如果看到derivedN一定要回到第 N 行去看那个子查询执行计划。线上经常出现的情况是外层查询看起来没有全表扫描但内层子查询把 30 万行全部物化了出来外层虽然走了索引实际耗时还是卡在内层。2.3 type访问类型整个执行计划里最关键的一列type列描述的是访问表数据的方式我在排查慢查询时第一个看的就是它。从好到差大致排列如下type含义典型场景system表只有一行系统表const主键或唯一索引等值匹配WHERE id ?eq_ref被驱动表用主键或唯一索引关联JOIN 时被驱动表按主键查找ref非唯一索引等值匹配WHERE status ?range索引范围扫描WHERE created_at ?index遍历索引树覆盖索引但需要扫全部叶子节点ALL全表扫描最差情况很多人只知道 ALL 差、const 好但实际分析时要结合 rows 来看。ref虽然比range优先级高但如果ref命中的行数有几十万可能还不如range之后做一次过滤。有一个容易被忽略的点type走的是单表访问路径。一条多表 JOIN 的 SQL每个表在 EXPLAIN 里都有一行各自有独立的 type。所以这条 SQL 是 range 还是 ALL这种说法其实不准确要分别看每一行。2.4 key、key_len、ref索引到底怎么被用上的key是优化器最终选中的索引名key_len是本次查询实际使用的索引字节长度ref表示索引列与什么值做比较。key_len是很多人没有好好利用的字段。它计算的是索引列在 BTree 里占用的字节长度设计复合索引时要靠它判断索引到底命中了哪几列。比如有一个联合索引(status, created_at)status 是 TINYINT1 字节created_at 是 DATETIME5 字节。如果key_len显示为1说明只用了 status 这一列如果显示为6说明两列都用上了。字符列还要算上字符集和变长字段标识utf8mb4下一个 VARCHAR(100) 的长度是100 * 4 2 402。2.5 rows 和 filtered成本估算里的数学期望rows是优化器预估需要扫描的行数filtered是经过 WHERE 条件过滤后剩余的比例百分比。两者的乘积约等于最终返回的数据量。这两列都是估算值不是真实执行结果。优化器做成本决策时看的是估算估算不准就会选错索引。这也是为什么明明建了索引优化器却选择全表扫描的一个重要原因——统计信息陈旧或数据分布出现了严重倾斜。2.6 Extra执行计划的备注栏藏着很多关键信息Extra列是执行计划里信息量最大、也最容易读漏的部分。我列几个高频出现的值Using index覆盖索引不需要回表这是最理想的情况。Using index condition发生了索引条件下推部分 WHERE 条件下推到存储引擎层过滤。MySQL 5.6 的特性通常是有利的。Using where存储引擎返回数据后Server 层又做了过滤。如果 rows 很大且这里出现 Using where大概率有优化空间。Using temporary使用了临时表常见于 GROUP BY、DISTINCT、ORDER BY 组合不当。Using filesort文件排序不一定真的落盘但意味着没有利用索引的有序性。Backward index scanMySQL 8.0 的倒序索引扫描看到这个说明优化器可以用索引反向扫描来避免 filesort。还有 MySQL 8.0.18 之后新增的EXPLAIN ANALYZE它不只看计划还会真正执行 SQL输出每个执行步骤的实际耗时和行数。后面我会专门讲怎么用它验证优化效果。3. 从 8 秒到 30ms一次完整慢查询排查链路复盘回到开头那个订单接口的案例。我不打算直接给结论而是把当时完整的排查过程写出来。因为排查思路本身比答案更有复用价值你下次遇到完全不同的慢 SQL也能按照同样的链路走一遍。3.1 第一步从慢日志定位问题 SQL 并确认执行频率MySQL 的 slow query log 是排查起点。先确认慢日志开启状态SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE long_query_time;如果没开启建议线上至少把long_query_time设为 1 并打开log_queries_not_using_indexes。当时我们在 slow log 里看到那条订单查询频繁出现在 Top 10平均执行时间从几百毫秒涨到 7.8 秒基本可以确定它就是接口慢的根因。这一步要注意一条 SQL 慢不代表要立刻去优化这条 SQL。先看它的执行频率和调用来源。如果是低频统计任务优化优先级可以往后排如果是高频接口的必经查询就必须当严重事故处理。3.2 第二步用 EXPLAIN 看原始执行计划这是最核心的一步。原始 SQL 的 EXPLAIN 结果如下关键列已截取idtabletypekeyrowsfilteredExtra1ordersALLNULL5200000010.5Using where; Using filesort1userseq_refPRIMARY1100NULL重点信息有两个orders 表是全表扫描type 是 ALLrows 估算 5200 万且 Extra 里有Using filesort。users 表反而是正常的 eq_ref根据主键回表开销小。也就是说慢的根源在 orders 表的访问路径上。明明有idx_status (status)和idx_created_at (created_at)两个单值索引为什么优化器不走3.3 第三步分析为什么优化器放弃了单列索引问题出在过滤性上。WHERE created_at 2024-01-01 00:00:00 AND status IN (1, 2, 3)这两个条件单独看任何一条命中的行数都非常大。status IN (1,2,3)覆盖了全表大部分正值状态单列索引idx_status在这里几乎没有区分度created_at 2024-01-01时间范围长达大半年估算会覆盖 45% 的数据。对优化器来说这两个单列索引的选择性都太差还不如直接全表扫一遍省去回表和索引随机 IO 的额外开销。这解释了为什么建了索引但执行计划不走——索引不是建了就一定被用优化器看的是成本。当索引访问可能涉及大量回表时成本模型会倾向全表扫描。3.4 第四步创建正确的复合索引明确了问题后我创建了一个复合索引ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);这里的关键是列的顺序。区分度高的列放在前面但也不能机械地按区分度排。这个查询的过滤过程是先按status IN (1,2,3)定位到若干条分支3 组再按created_at 2024-01-01 00:00:00在每组分支内部做范围扫描最后ORDER BY created_at DESC在索引内部按倒序读取。所以(status, created_at)这个顺序让 WHERE 条件和 ORDER BY 都能用上同一个索引。如果换成了(created_at, status)status 的 IN 条件就只能作为 post-filter 在回表后过滤效率差很多。添加索引后的 EXPLAIN 结果idtabletypekeyrowsfilteredExtra1ordersrangeidx_status_created280000100Using index condition; Backward index scan1userseq_refPRIMARY1100NULLrows 从 5200 万降到了 28 万type 从 ALL 变成了 rangeExtra 里出现了Using index condition说明索引条件下推起了作用而且倒序索引扫描可以直接按created_at DESC输出顺序filesort 消失了。实测这条 SQL 的执行时间从 7.8 秒降到了 180ms。但以这个结果作为终点还不够看下一步。3.5 第五步用覆盖索引进一步消除回表180ms 对接口来说已经可以接受但 orders 表 28 万行回表仍然存在。我们最终又加了一个覆盖索引把 SELECT 要取的列全部放进去ALTER TABLE orders ADD INDEX idx_status_created_cover (status, created_at, id, order_no, amount, user_id);这时候 EXPLAIN 的 Extra 列变成了Using index意味着直接扫描索引叶子节点就能拿到全部字段回表彻底消除。实测 SQL 耗时降到了 30ms 左右。需要提醒的是覆盖索引不能盲目滥用。索引列的增多会带来写入成本的增加空间占用也会明显上升。这个案例之所以值得建是因为它是一个高频读接口写入频率相对可控收益远大于成本。4. JOIN、子查询、排序场景下的执行计划专项分析单表查询的 EXPLAIN 相对容易读线上真正难处理的是复杂的多表 JOIN、子查询和排序分页。这一节我会按场景拆开讲。4.1 JOIN 的驱动表与被驱动表STRAIGHT_JOIN 的适用时机多表 JOIN 执行计划里第一行是驱动表后续行是被驱动表。MySQL 的优化器一般会按成本估算选择小表作为驱动表但不一定每次都选对。看一个经典案例SELECT s.name, COUNT(o.id) AS order_cnt FROM sellers s LEFT JOIN orders o ON o.seller_id s.id WHERE s.status 1 GROUP BY s.id;sellers 表有 5 万行orders 表 5000 万行。优化器的原始计划是拿 sellers 做驱动表orders 做被驱动表用ref访问idx_seller_id。这个方向是对的。但有一种情况会被带偏如果 orders 表的统计信息过期优化器估算驱动表行数错误就可能把大表当驱动表直接造成 N 次大表全扫。排查时如果发现第一个表 rows 异常大先检查表的统计信息ANALYZE TABLE orders;MySQL 8.0 里还提供了EXPLAIN ANALYZE可以看到每一步执行的实际耗时比纯估算靠谱得多。如果确认优化器选错驱动表可以用STRAIGHT_JOIN强制指定左右顺序SELECT s.name, COUNT(o.id) AS order_cnt FROM sellers s STRAIGHT_JOIN orders o ON o.seller_id s.id WHERE s.status 1 GROUP BY s.id;但STRAIGHT_JOIN是强约束一旦数据分布变化这个顺序可能变成劣势。我通常只在 EXPLAIN 确认优化器选反的情况下临时用不会写进长期代码。更好的做法是先ANALYZE TABLE修正统计信息让优化器自己做对选择。4.2 子查询的隐藏陷阱从 SUBQUERY 到 DERIVED子查询在 EXPLAIN 里最常见的两种形态是SUBQUERYWHERE 条件中的子查询和DERIVEDFROM 子句中的派生表。举一个典型慢查询SELECT id, order_no FROM orders WHERE user_id IN ( SELECT user_id FROM users WHERE register_time 2023-06-01 );这条 SQL 的本意是查 2023 年 6 月后注册用户的订单。在 MySQL 5.7 中EXPLAIN 可能看到 users 子查询被物化成临时表orders 做全表扫描。因为当时的优化器对 IN子查询的处理相对笨拙。改写方式是把子查询改成 JOINSELECT o.id, o.order_no FROM orders o JOIN users u ON u.user_id o.user_id WHERE u.register_time 2023-06-01;改写后的执行计划里orders 走idx_user_id的 ref 访问users 按 register_time 走范围扫描或全表取决于过滤比例整体效率大幅提升。但这里有个很重要的经验不是所有子查询改写为 JOIN 都会更快。如果子查询聚合后数据量很小保留子查询反而可以减少连接后的数据膨胀。改不改最终要以 EXPLAIN 和实际耗时的对比为准不要凭直觉。4.3 filesort 的三种出路索引、内存、磁盘Using filesort不代表一定慢但出现它时要判断排序数据量。内存排序在 sort_buffer_size 内耗时很低一旦超过阈值MySQL 会把中间结果写到磁盘采用归并排序这时性能会断崖式下降。消除 filesort 的最稳妥办法就是让 ORDER BY 的列和索引顺序保持一致。举个例子SELECT id, order_no, created_at FROM orders WHERE status 1 ORDER BY created_at DESC LIMIT 20;现有索引idx_status (status)时EXPLAIN 大概率出现Using filesort。因为 status 过滤后无法利用 created_at 的有序性。把索引改成(status, created_at)后索引内部的叶子节点已经天然按 created_at 有序SQL 直接按索引反向扫描取 20 条就结束。这个时候 EXPLAIN 里的 filesort 消失rows 也会显著下降。另一个容易掉进的坑是混合排序方向ORDER BY status ASC, created_at DESC如果索引是(status ASC, created_at ASC)那么 created_at 需要反向读取在 MySQL 8.0 之前无法直接利用索引只能 filesort。8.0 引入了支持降序索引的定义方式ALTER TABLE orders ADD INDEX idx_status_created_desc (status ASC, created_at DESC);这种场景做执行计划时要专门去看索引定义里的排序方向。4.4 LIMIT 深分页执行计划里的 rows 骗了你分页查询是很典型的看起来有索引实际越翻越慢的场景SELECT id, order_no, amount FROM orders WHERE status 1 ORDER BY created_at DESC LIMIT 100000, 20;EXPLAIN 的 type 可能是 rangekey 也正常rows 看起来也不大。但真实执行时MySQL 需要从第 1 行开始数到第 100000 行再把 100020 行里丢掉前 100000 行。EXPLAIN 的 rows 是最终返回行数加过滤不是扫描行数。这个场景的行数其实是 100020 行。深分页的标准解法是延迟关联延迟 join或者限定起始游标-- 延迟关联先取主键再回表 SELECT o.id, o.order_no, o.amount FROM orders o JOIN ( SELECT id FROM orders WHERE status 1 ORDER BY created_at DESC LIMIT 100000, 20 ) tmp ON o.id tmp.id ORDER BY o.created_at DESC; -- 游标方式更适合客户端持续翻页 SELECT id, order_no, amount FROM orders WHERE status 1 AND created_at 上次最后一条的created_at ORDER BY created_at DESC LIMIT 20;第二种方式在 EXPLAIN 里的 rows 非常小因为它直接利用索引跳到指定位置不再需要遍历前 100000 行。5. 优化完怎么验证从 EXPLAIN 到 EXPLAIN ANALYZE 的升级很多人的习惯是索引建好之后执行一次EXPLAIN看到 type 变了就觉得优化完成。这个习惯要改。EXPLAIN 给的是估算真实执行情况要用EXPLAIN ANALYZEMySQL 8.0.18来看。5.1 EXPLAIN ANALYZE 输出格式怎么读直接执行EXPLAIN ANALYZE SELECT o.id, o.order_no, u.nickname FROM orders o LEFT JOIN users u ON o.user_id u.id WHERE o.created_at 2024-01-01 00:00:00 AND o.status IN (1, 2, 3) ORDER BY o.created_at DESC LIMIT 20;输出长这样格式可能随版本微调但信息类似- Limit: 20 row(s) (cost12345 rows20) - Sort: o.created_at DESC (cost... rows...) - Index range scan on orders using idx_status_created (cost... rows...) - Index lookup on users using PRIMARY (cost1.01 rows1)每一行后面都会附actual time和actual rows。这是什么概念呢actual time是这个节点实际启动和结束的时间actual rows是真实处理的行数。举个例子你在 EXPLAIN 里看到 orders 的 rows 估算为 28 万但EXPLAIN ANALYZE显示 actual rows 是 45 万。这说明统计信息偏差比较大后续优化方向应该考虑更新统计信息而不是继续调索引。5.2 两个关键指标actual time 和 actual rowsactual time通常显示为两个数字比如0.203/2.340。第一个是返回第一行的时间第二个是返回所有行的时间。如果是流式处理两个数字差值很小如果first row很长说明前面的 join 或子查询初始化很慢。actual rows则直接暴露了估算失真。排查时我一般对比三组数据EXPLAIN 里的 rows 与 actual rows 是否量级一致filtered 估算的过滤比例与实际结果行数是否吻合每一层的 actual rows 是否出现指数级放大。如果某一层 actual rows 放大了几十倍说明这一层有严重的笛卡尔积或驱动方式错误优先在这一层优化。5.3 防止优化器随机选错索引有一类问题在环境切换后特别常见同样一条 SQL在测试库走的是索引 A上了生产环境却走索引 B性能差异巨大。这不是索引建错了而是两个库的统计信息和数据分布不同。处理方式有两个对相关表定期执行ANALYZE TABLE让优化器的统计信息跟上数据变化在 SQL 里用FORCE INDEX指定期望的索引但要注意强制两字——一旦强制未来数据分布变了这条 SQL 也无法自适应。一个更温和的折中方案是用USE INDEX它只是向优化器提议优先考虑某索引但不做硬性约束。线上遇到偶发的索引抖动我一般先加USE INDEX观察配合统计信息更新而不是直接上FORCE INDEX。5.4 回滚机制发版前的最后一道保险优化 SQL 之后最怕的是上线第二天出现反向效果。我建议在发布计划里加一个回滚开关比如在配置中心控制新 SQL/旧 SQL的切换或者保留旧索引一定时间再做删除。有一个真实教训优化了一条大查询后建了新复合索引同时 DBA 顺手把旧索引删了结果次日基础数据脚本跑批报错——因为那个旧索引是另一个低频查询的隐性依赖。索引不是越多越好但删除索引前一定要排查所有相关 SQL 的 EXPLAIN 结果。删除索引前至少做两件事-- 1. 确认该索引没有被其他查询用到慢日志、performance_schema 可查 SELECT * FROM performance_schema.events_statements_history_long WHERE SQL_TEXT LIKE %你的索引名%; -- 2. 在测试环境删除索引后重新 EXPLAIN 关联 SQL EXPLAIN SELECT ... 所有可能走此索引的SQL;这一步看起来啰嗦但可以避免大部分优化副作用的翻车事故。6. 我踩过的一些执行计划反直觉坑最后聊几个我实际踩过、比较有代表性的坑。这些不是标准文档里会写的但遇到过的朋友应该能会心一笑。第一个坑大表上数据分布极不均匀时复合索引的列顺序不能只看区分度。曾经有一张订单表的status字段 99% 的数据都是已完成只有 1% 是待支付。如果机械地按区分度高的列放前面去建(created_at, status)那么WHERE created_at ? AND status 待支付就可能出现按时间扫一大批已完成数据再过滤出这 1% 的情况。这种场景正确做法反而是让 status 在前面先精准定位到那 1% 的分支再按时间过滤。判断标准始终是哪个列能更快缩小扫描范围而不是区分度数字本身。第二个坑覆盖索引命中的 Extra 显示 Using index但实际还是慢。有一次我们给大表加了宽覆盖索引SELECT 字段全部覆盖EXPLAIN 非常漂亮实际却把磁盘 IO 打满了。原因是索引宽度太大单页能存的条目变少扫描量并没有真正减少。覆盖索引不是万能药它适合高频小查询不适合一次扫几十万行的分析型语句。第三个坑MySQL 8.0 里IN列表会被优化器改造成临时表 JOIN。以前有个认知是IN 列表走索引就没问题实际上IN (1,2,3,4,...大量值)在优化器内部会构建一个等值比较集合如果列表很长构造临时表的代价会直接体现在执行计划里。EXPLAIN 里如果突然出现Materialize相关的行不要惊讶先看看 IN 列表到底有多长。第四个坑不要忽略formattree带来的新维度。MySQL 8.0.16 之后支持EXPLAIN FORMATTRADITIONAL SELECT ...; EXPLAIN FORMATJSON SELECT ...; EXPLAIN FORMATTREE SELECT ...;JSON 格式会输出cost_info和used_columnsused_columns直接告诉你排序和过滤实际用了哪些列排查覆盖索引是否真正覆盖时非常好用。tree 格式则更适合阅读层级关系配合EXPLAIN ANALYZE可以快速建立执行流程的全貌。这些坑的共同点是它们都发生在EXPLAIN 看起来正常的表象之下。所以我现在有一条铁律执行计划的任何一项改动最终都要用真实执行耗时来验收不能只看 plan 变了就觉得优化完成了。如果你想把这套方法变成自己的能力建议准备一张平时最常慢的 SQL 清单把每次优化的 EXPLAIN 结果、索引 DDL、实际耗时变化记录下来。积累几十个案例之后你看执行计划的速度和理解深度会和一个只刷文档的人拉开明显差距。这个习惯比我上面写的任何单条技巧都值钱。