如果你维护过 MySQL却从来没认真看过一次 EXPLAIN 的输出那说明你的排查工具链里少了一把最趁手的刀。我见过太多开发同学面对慢查询的第一反应是“加索引”但加了之后到底走没走、走了多少行、有没有排序、有没有临时表基本靠猜。EXPLAIN 就是 MySQL 优化器给SQL开出的“执行计划说明书”看懂了它你就能在五分钟内判断一条 SQL 是“没救”还是“小问题”很多上线前发现不了的性能隐患也能在压测阶段就提前揪出来。这篇文章不绕弯子我直接带你从字段含义、实战案例到踩坑记录把 EXPLAIN 整个吃透。1. 为什么每个做 MySQL 的人都该先学会看 EXPLAIN1.1 一条慢 SQL 引发的“案发现场”先讲一个很典型的场景。某天线上接口突然超时慢查询日志里刷出来这样一条 SQLSELECT * FROM orders WHERE user_id 12345 AND status IN (0, 1) ORDER BY created_at DESC LIMIT 20;表里确实有 user_id 的索引很多人的第一反应是“这不科学啊明明有索引”。等你真的把这条 SQL 前面加上 EXPLAIN 跑一遍马上就会发现问题虽然走了 user_id 索引但 Extra 列里出现了 Using filesort还估算扫了几万行。真正慢的原因不是没索引而是排序字段和索引列不匹配MySQL 只能先把命中的几万行数据找出来再在内存或磁盘里做一次完整排序最后才取 20 条。这种问题不加 EXPLAIN 看是看不出来的加了 EXPLAIN一眼就能定位。所以我的习惯是任何超过 100ms 的查询第一步不是改代码而是先 EXPLAIN 一下。搞清楚优化器到底打算怎么执行这条语句再决定是加索引、改索引顺序还是干脆重写 SQL。1.2 EXPLAIN 能回答哪些高频问题把 EXPLAIN 想象成一个透视镜它帮助我们在不真正执行 SQL 的前提下老版本是估算8.0 还有 EXPLAIN ANALYZE 是真执行看穿优化器的内部决策。它能回答的核心问题包括这条 SQL 是否命中索引命中哪个索引优化器估算需要扫描多少行数据多表 join 时哪张表是驱动表哪张表被驱动有没有发生额外的排序filesort或临时表temporary table索引的 key_len 是多少有没有用到组合索引的完整前缀这些问题搞明白80% 的慢查询基本上就有了明确的优化方向。剩下 20% 可能涉及锁竞争、硬件瓶颈、数据分布极端不均匀等那就要再往深了查但 EXPLAIN 依然是整个排查过程的起点。2. EXPLAIN 输出里的每一列到底在说什么2.1 从 id、select_type、table 三列开始看执行顺序我在带新人查 SQL 的时候会让他们先别急着盯 rows 和 Extra先把 id、select_type、table 这三列连起来读一遍。因为这三列决定了整个查询的执行骨架。id 是查询执行的编号。一个 SELECT 语句里可能包含子查询、union、多表 join所以会出现多行输出。id 相同的行表示它们是同一次查询的一部分id 不同的行数字越大越先执行。注意子查询和派生表的执行顺序经常和直觉相反比如 WHERE 里的子查询可能比外层先执行。select_type 则是对查询类型的标注常见的有 SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、DEPENDENT SUBQUERY。遇到 DEPENDENT SUBQUERY 要格外小心这种相关子查询通常意味着每一行外层数据都要执行一次子查询性能差是必然的。table 列就是这一次访问的表名或别名。如果显示derived2这样的形式说明它来自 id2 派生出来的临时表。看懂了这三列你就能画出一张“查询执行先后顺序图”后面再分析每一列就顺理成章了。2.2 type 列访问方式从优到劣type 列是整个 EXPLAIN 结果里信息密度最高的一列。它表示 MySQL 用哪种方式访问这张表。我把常见的值按性能优劣排个序给你一张速查表type 值含义是否推荐system表只有一行系统表极罕见const主键或唯一索引等值匹配非常好eq_refjoin 中被驱动表按主键或唯一索引匹配非常好ref普通二级索引等值匹配好ref_or_nullref 且额外包含 NULL 判断尚可range索引范围扫描如 BETWEEN、IN、、可接受index全索引扫描不用回表但遍历整个索引树一般ALL全表扫描性能杀手重点优化这里我想强调一点typeALL 不一定是绝对的坏如果表只有几百行全表扫描反而比走索引更快。但生产环境里那些动辄上百万行的大表一旦出现 ALL就该拉起警报。typeindex 也有迷惑性它看着是走了索引其实是把整个索引树从头到尾扫了一遍如果索引本身很大照样慢。很多 DBA 说“至少要保证 range 以上”这个说法在实际业务里基本靠谱。但注意typerange 对索引的顺序和维护性要求很高后面讲索引下推的时候会再说。2.3 key、key_len、rows、filtered 四列组合分析单独看 key 列意义不大它只是告诉你优化器选了哪个索引真正有价值的是 key_len 和 rows 的组合。key_len 是 MySQL 实际使用索引的字节数。组合索引 idx_a_b_c如果只用了 a 列key_len 就只有 a 那一段的长度如果 a、b 都用上了key_len 就是两段之和。所以你可以通过 key_len 反向推导确认索引到底用了几列。计算规则后面我会专门用一节讲这里先记住一个结论key_len 越小往往表示索引利用得越浅回表行数可能越多。rows 是优化器预估需要扫描的行数。这是一个预估值不是实际值但它代表了优化器眼中的“成本标尺”。如果 rows 是 10 万但实际结果集只有 10 行说明这条 SQL 的定位能力很差索引选择空间设计有问题。filtered 表示经过 WHERE 条件过滤后剩余的比例百分比越低说明大量行被扫描后又放弃了优化空间越大。记住一个配合视角rows × filtered 可以估算最终返回的数据量级如果这个量级和实际业务需求差距很大那 SQL 本身或索引设计八成有问题。2.4 Extra 列里的几个关键词看见就别放过Extra 列是最容易暴露问题的地方也是新手最容易忽略的地方。我最关注这几个词Using filesortMySQL 需要额外做一次排序可能发生在内存也可能落盘。这是一个敏感信号ORDER BY 和索引顺序不一致的时候经常出现。Using temporary查询过程中使用了临时表GROUP BY、DISTINCT、子查询经常触发。临时表超过内存限制会落到磁盘字段是 TEXT/BLOB 时也可能直接落盘性能断崖式下降。Using index这代表覆盖索引不需要回表属于好事。Using index condition索引下推MySQL 5.6 之后的优化一般也是好事。Using where从存储引擎拿到数据后在 server 层又做了一次过滤说明索引没能完全覆盖 WHERE 条件但这不一定是坏事要结合 key 和 rows 判断。Impossible WHERE优化器直接判定 WHERE 条件永远为假一条数据都不会返回。这种通常是你自己业务逻辑写错了比如id 1 AND id 2。Extra 里同时出现的多个关键词要连起来读比如Using index condition; Using filesort说明优化器在索引下推后依然要排序问题出在排序字段没进索引。3. 实操演示用 EXPLAIN 给真实场景做调优3.1 准备实验表结构与数据光讲理论没意思我直接模拟一个电商订单系统的简化场景。下面这两张表是我在本地常年用来给团队演示的表结构CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(120) NOT NULL, status TINYINT NOT NULL DEFAULT 1, created_at DATETIME NOT NULL ) ENGINEInnoDB; CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, order_no VARCHAR(32) NOT NULL, total_amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, KEY idx_user_id (user_id), KEY idx_created_at (created_at) ) ENGINEInnoDB;两张表都没有太多索引模拟一个最初级的开发状态。测试数据量我准备了 50 万条 orders、10 万条 users足够看出各种执行计划的差异。3.2 场景一条件列没有索引出现全表扫描先看一个最常见的慢查询按订单号精确查询。订单号是业务里一定会用到的筛选条件但我故意先不建索引。EXPLAIN SELECT * FROM orders WHERE order_no SO20240001;输出大致是这样的idtabletypepossible_keyskeykey_lenrowsExtra1ordersALLNULLNULLNULL500000Using wheretypeALLrows50万说明这条查询会从头到尾扫一遍全表。type 为 ALL 时possible_keys 和 key 都是 NULL。优化器没找到任何可用索引只能硬扫。解决办法很简单给 order_no 加个唯一索引ALTER TABLE orders ADD UNIQUE KEY uk_order_no (order_no);再加 EXPLAIN 看idtabletypepossible_keyskeykey_lenrowsExtra1ordersconstuk_order_nouk_order_no1301NULLtype 直接变成 constrows 变成 1。因为 order_no 是唯一索引等值匹配时最多一条记录优化器直接把它当成常量处理。这里 key_len130我稍后会解释这个数字怎么来的。3.3 场景二二级索引等值查询typeref再看 user_id 查询EXPLAIN SELECT * FROM orders WHERE user_id 1001;idtabletypepossible_keyskeykey_lenrowsExtra1ordersrefidx_user_ididx_user_id43NULLtyperefkeyidx_user_idrows3说明同一个用户只有少量订单索引有效地缩小了扫描范围。因为 user_id 是 INTkey_len4正好等于一个 INT 的长度。这种查询已经算健康但如果业务上经常查某用户的订单并且还要按创建时间排序就引出场景三的问题。3.4 场景三ORDER BY 和索引顺序不一致出现 Using filesort现在执行这条业务里高频出现的查询EXPLAIN SELECT * FROM orders WHERE user_id 1001 ORDER BY created_at DESC LIMIT 10;你会看到idtabletypepossible_keyskeykey_lenrowsExtra1ordersrefidx_user_ididx_user_id43Using filesort虽然 typeref看起来不错但 Extra 里的 Using filesort 告诉我们MySQL 把 user_id1001 的全部订单捞出来之后又额外按 created_at 做了一次排序最后才取 10 条。如果这个用户订单多排序消耗会非常明显。最直接的优化方案是把索引改成组合索引让排序字段跟在筛选字段后面ALTER TABLE orders DROP INDEX idx_user_id; ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);再 EXPLAINidtabletypepossible_keyskeykey_lenrowsExtra1ordersrefidx_user_createdidx_user_created43NULLfilesort 消失了因为 InnoDB 的二级索引本来就按 user_id 排user_id 相同的数据内部再按 created_at 排。ORDER BY created_at 直接读索引顺序即可不需要额外排序。这里 key_len4因为只用了索引第一列 user_id。这是一个非常经典的问题你以为加了索引就万事大吉其实是要把 WHERE 筛选字段和 ORDER BY 排序字段一起设计进组合索引里。3.5 场景四多表 join 时驱动表该怎么看join 查询的 EXPLAIN 输出往往更复杂。为了演示我执行EXPLAIN SELECT o.order_no, u.email FROM orders o JOIN users u ON o.user_id u.id WHERE o.status 1;输出可能有几行我的环境里大致是这样idtabletypepossible_keyskeykey_lenrowsfilteredExtra1oALLidx_user_idNULLNULL50000030.00Using where1ueq_refPRIMARYPRIMARY41100.00NULL这里注意看第一行orders 作为驱动表typeALL全表扫描 50 万行。users 作为被驱动表typeeq_ref通过主键去查每次只查一行。这种 join 的总成本约等于 50 万次主键查询还能接受但驱动表本身扫了 50 万行一旦 rows 再大就会很吃力。再看另一条优化后的写法EXPLAIN SELECT o.order_no, u.email FROM users u JOIN orders o ON o.user_id u.id WHERE u.status 1;因为 users 表先经过 status 过滤行数可能只有几万MySQL 会选择将 users 作为驱动表orders 作为被驱动表访问方式保持一致但驱动表的 rows 降下来了总扫描量自然小很多。这说明 join 并不是“大表不能当驱动表”而是要尽量让过滤后行数少的表当驱动表。实际上MySQL 8.0 的优化器已经很智能通常会自动选择成本更低的驱动表。但在统计信息不准、或 SQL 里出现 ORDER BY、GROUP BY 等限制时优化器依然可能选错。遇到复杂 join 时可以用 STRAIGHT_JOIN 强制指定驱动表但这是险招建议先分析 EXPLAIN 再说。3.6 场景五覆盖索引用 ExtraUsing index 消灭回表二级索引查询通常要经历“索引查找 回表取整行数据”两步。但如果查询的字段全部存在于索引树里就不再需要回表这就是覆盖索引。假设我们有一个查询EXPLAIN SELECT user_id, status FROM orders WHERE user_id 1001;当前索引是 idx_user_created(user_id, created_at)那么 user_id 本身在索引里但 status 不在所以要回表。Extra 里不会出现 Using index。如果我们为这个业务专门建一个覆盖索引ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);然后再 EXPLAINidtabletypepossible_keyskeykey_lenrowsExtra1ordersrefidx_user_statusidx_user_status43Using indexExtra 里的 Using index 就是这个意思查询字段全部在索引里不需要回表。经典的高频小查询优化手段。但要注意覆盖索引不是越多越好因为每多一个索引写入时的维护成本都会增加。一定要权衡业务真实读多写少的情况。3.7 场景六前缀模糊查询和隐式类型转换的“翻车现场”再来看两个非常容易踩的坑。第一个是前缀通配符 LIKEEXPLAIN SELECT * FROM orders WHERE order_no LIKE %SO2024%;不管 order_no 有没有索引只要通配符在开头优化器通常就会放弃索引因为 B 树的排序方式没法为“包含在某位置”的字符串匹配做定位。结果就是 typeALL全表扫描。第二个是隐式类型转换。假如我们给 order_no 建了唯一索引 uk_order_no然后执行EXPLAIN SELECT * FROM orders WHERE order_no 20240101;注意 order_no 是 VARCHAR条件里的 20240101 却是数字。MySQL 会对列做隐式类型转换把字符串列转成数字去比较这会导致索引失效。EXPLAIN 结果通常是 typeALL哪怕明明有唯一索引。改成字符串写法EXPLAIN SELECT * FROM orders WHERE order_no 20240101;type 就回到 const。这类问题在联调环境里特别容易出现因为测试数据量不大ALL 也没感觉慢一上线就爆雷。所以查 EXPLAIN 时如果某个字段明明有索引却 typeALL第一反应就应该是检查类型转换。4. 常见问题与排查技巧实录4.1 为什么 EXPLAIN 里的 rows 和实际行数差很多rows 是优化器基于统计信息做的估算不是真实行数。如果表很久没做 ANALYZE TABLE统计信息非常陈旧rows 就会偏离实际。遇到这种情况先跑一下ANALYZE TABLE orders;重新收集完统计信息再看 rows。另外MySQL 8.0 给 InnoDB 引入了直方图功能可以针对某些高倾斜的列做更精细的分布统计。如果业务里某列数据分布很离谱比如 99% 的数据都集中在一个值上可以在该列上建立直方图帮助优化器做出更准确的判断。如果 ANALYZE 之后 rows 还是不准尤其是大表频繁增删改的场景就要考虑是不是索引本身的选择度太差。比如 status 列只有 0、1、2 三个值优化器可能认为扫描全表比走索引更快这是一条很重要的底层逻辑rows 不是越小越好优化器关心的是整体成本包括随机 IO 和顺序 IO 的权衡。4.2 key_len 到底是怎么算出来的很多人会问为什么前面 order_no 唯一索引的 key_len 是 130算一下你就明白了。order_no 是 VARCHAR(32)不设 NULL表默认字符集如果是 utf8mb4那么每个字符最多占 4 字节。VARCHAR 还有 2 字节用于记录实际长度所以32 个字符 × 4 字节 128 字节加上 2 字节变长长度 130 字节如果你看到 key_len130说明索引用满整个 order_no 列。如果是 INT 类型就是 4 字节BIGINT 是 8 字节DATETIME 在 MySQL 8.0 里存储是 5 字节旧版本是 8 字节可空再加 1 字节。组合索引判断用了几列就是用 key_len 除以对应列的长度。举个例子如果索引是 (user_id, created_at)user_id 是 INT(4字节)created_at 是 DATETIME(5字节)那么 key_len4 表示只用了 user_idkey_len9 表示两列都用上了。这个方法在我排查索引失效问题的时候非常管用。4.3 索引明明建了却没生效排除思路是什么我整理了一个“索引用不上”的排查清单遇到问题照着过一遍现象可能原因解决方案对索引列做函数操作WHERE DATE(created_at)2024-01-01改成和的范围写法隐式类型转换字符串列 数字统一类型参数也写成字符串LIKE 前缀通配LIKE %abc考虑全文索引或搜索引擎不硬扛OR 条件连接WHERE a1 OR b2改成 UNION ALL或建立联合索引组合索引列顺序不符合索引(a,b) 查 b调整索引列顺序或新增索引统计信息过期大量增删改后没 ANALYZE执行 ANALYZE TABLE优化器选择全表扫描返回行数占比太高换个更高选择度的列做索引或强制索引一条条猜很浪费时间最高效的办法是 EXPLAIN FORMATJSON 把优化器的成本估算调出来看看它在犹豫什么。JSON 格式会给出cost_info、used_key_parts、index_filter这些细节定位问题非常直观。4.4 MySQL 8.0 的 EXPLAIN ANALYZE 和 FORMATJSONMySQL 8.0.18 之后EXPLAIN ANALYZE 是一个非常实用的升级功能。它不同于普通 EXPLAIN 的“估算”而是真实执行一次 SQL并输出每一步的实际耗时和扫描行数。比如EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id 1001\G输出里会出现类似actual time0.123..0.456 rows3这样的信息这里面的时间是真实耗时的毫秒级记录可比 rows 估算靠谱多了。但要注意EXPLAIN ANALYZE 是真实执行语句如果是 UPDATE、DELETE 或带副作用的查询务必在事务里用完就回滚或者直接只对生产数据进行只读 SELECT 测试。我见过同事在线上对一个大表执行 EXPLAIN ANALYZE它真跑了那个查询直接把数据库 IO 打满这就得不偿失了。还有 FORMATJSONEXPLAIN FORMATJSON SELECT * FROM orders WHERE user_id 1001;它返回的是嵌套 JSON里面包含query_block、table、access_type、cost_info、used_columns等字段。我在排查“走了索引但还是慢”“为什么选择这个索引”的问题时基本都会用 JSON 格式看一轮因为普通表格行的信息密度不够。4.5 SHOW WARNINGS 能看出优化器改写了什么 SQLEXPLAIN 之后紧接着执行 SHOW WARNINGS有机会看到优化器对原 SQL 做了哪些等价改写。这个技巧可能知道的人不多但排查子查询、视图、IN 改写时有奇效。比如EXPLAIN SELECT * FROM orders WHERE order_no IN (SELECT order_no FROM temp_orders); SHOW WARNINGS;结果里会输出一条“被优化器改写后的 SQL”你可以清晰地看到 MySQL 把 IN 子查询转成了 semi-join 还是物化表。理解了优化器的改写方向你就能判断业务 SQL 有没有写成更高效的等价形式。5. 我长期实践积累的经验和避坑建议5.1 排查慢 SQL我习惯按这个顺序走拿到一条慢 SQL我个人的标准操作流程是先打开慢查询日志捞到原始 SQL然后一条条加 EXPLAIN把 type 是 ALL、index或者 Extra 里有 filesort、temporary 的全部标出来对可疑 SQL 再用 EXPLAIN FORMATJSON 和 EXPLAIN ANALYZE 做二次确认分析完索引设计再决定是改 SQL 还是加索引。这套流程走完之后我会顺手把这些 EXPLAIN 结果作为压测回归的基线保存在文档里。后续大版本升级或索引调整后再跑一遍对比谁把查询带偏了一个数量级一查便知。长期下来团队里 SQL 性能回归的概率会小很多。5.2 不要迷信 type也要看自己的业务量级typerange 或 typeref 不代表这条 SQL 一定快。我遇到过一个案例SQL 走了 range但 range 命中了表里 40% 的数据实际扫描行数接近几百万性能照样很惨。EXPLAIN 里的 type 只是一个定性的好坏排序真正判断有没有问题要把 rows 和实际业务规模结合起来看。反过来说typeALL 并不代表必须优化。如果是一张只有 500 行的配置表全表扫描的成本可能比走索引还低。优化不是看指标漂不漂亮而是看实际的 IO 成本和时间开销。5.3 用索引下推和覆盖索引可以对付一些“半吊子”索引MySQL 5.6 以来的索引下推优化让我在无法新增索引的场合也救了很多次场。即使组合索引只有前导列能用于 WHERE 筛选后续列如果带索引也能在回表之前先做一层过滤减少回表数量。我看 EXPLAIN 时如果看到 ExtraUsing index condition一般会顺着这个信息确认排序和过滤字段是否都能塞进同一个索引里。覆盖索引的好处不用多说但它在更新频繁的表上会拖慢写入。我的建议是只针对查询占比最高、QPS 最大的那几条 SQL 做覆盖索引不要为了炫技把表建出七八个索引。5.4 EXPLAIN 只是“意图说明书”不是万能诊断最后说点掏心窝的话。EXPLAIN 能告诉你优化器打算怎么干但它不告诉你真实执行的每一行耗时也不告诉你锁等待、网络延时、连接池资源这些跑在 SQL 之外的瓶颈。遇到整体系统变慢除了 EXPLAIN还要看系统状态、锁分析和每一层的耗时曲线。我自己用过很多次“先 EXPLAIN、后 SHOW WARNINGS、再 optimizer_trace”的组合拳在定位复杂 SQL 问题时几乎从不失手。建议你把这三招都试一试尤其 optimizer_trace它能一步步展现优化器决策时的行为对于想知道“为什么没选那个索引”这类问题比任何文档都讲得清楚。上手 EXPLAIN 最好的时机不是你遇到线上故障那一刻而是现在。找一条平时用得最多的查询给它加上 EXPLAIN一行行读过去。遇到不懂的字段回来对照这篇文章看一遍相信我用不了几次你就能形成肌肉记忆以后只要看到 typeALL 或 Using filesort身体都会本能地紧张起来。
