不知道你有没有遇到过这种场景：线上某个查询接口突然变慢，从几十毫秒掉到两三秒，你第一反应是“SQL写得不行”，于是把那条SQL复制出来，盯着看了半天，加个索引试试？可搞了半天，速度还是上不去。说实话，我刚开始做后端那阵也是这么过来的，加索引全靠瞎猜，效果全看运气。直到后来把SQL优化之EXPLAIN关键字分析这套方法彻底吃透，才明白慢SQL的问题，十有八九在执行计划里就能直接看出来。
EXPLAIN是MySQL提供的一个关键字，用来查看SQL语句的执行计划。它并不会真的去执行这条SQL，而是让优化器把它“打算怎么做”的方式讲给你听：先查哪张表、走哪个索引、大概扫多少行、最后怎么排序。所有写SQL、调SQL的人——后端开发、DBA、运维，只要你的工作离不开数据库，这东西就是必备技能。这篇文章我打算从EXPLAIN的每个字段讲起，再带一个真实慢SQL的优化全过程，最后把执行计划里常见的坑一次性说清楚，希望能帮你建立一套从发现问题到解决问题的完整思路。
1. 为什么优化SQL前，建议先跑一次EXPLAIN
1.1 慢SQL不是猜出来的，是“看”出来的
很多人拿到一条慢SQL，第一件事就是凭感觉改：条件加个索引、把子查询改成JOIN、把SELECT * 换成具体字段……改完一跑，快了一点，再改一下，又慢回去。这种试错式的优化方式最大的问题在于，你根本不知道数据库优化器是怎么理解这条SQL的。
EXPLAIN就是来解决这个问题的。它相当于让你站在优化器视角，提前看一眼这条SQL的“导航路线”。举个例子：同样是查一张订单表的数据，走主键定位和走全表扫描，耗时完全不是一个量级。但如果你不看执行计划，可能连“这条路到底是怎么走的”都不清楚，更别提针对性地加速了。
1.2 EXPLAIN能告诉你哪些核心信息
一次EXPLAIN输出会包含很多列，但真正需要重点关注的其实就几条：
- 访问类型（type）：这代表SQL走了全表扫描、索引范围扫描，还是主键点查。
- 可能用到的索引与实际使用的索引（possible_keys / key）：看看你建的索引到底有没有被用上。
- 估算扫描行数（rows）：粗略判断这条SQL会“摸”多少条数据。
- 额外信息（Extra）：有没有临时表、要不要文件排序，很多性能隐患都藏在这里。
比如下面这个最简单的EXPLAIN输出：
EXPLAIN SELECT * FROM users WHERE id = 1;结果里type如果是const，说明这是按主键等值查询，性能非常好；如果是ALL，那就意味着全表扫描，这时候就要注意了——哪怕表再小，也要想一下为什么没走索引。
EXPLAIN本身是只读操作，不会产生数据变更，所以线上生产环境是可以直接跑的。这一点是我一直推荐大家习惯性用它排查问题的原因，安全、无副作用，信息量还大。
2. 用EXPLAIN之前，先看懂它的每列
2.1 id与select_type：先分清谁先执行
EXPLAIN输出的第一列是id，第二列是select_type。id表示执行顺序，select_type表示这条子查询或表访问属于什么类型。
- 当id相同时，执行顺序是自上而下，也就是从上往下依次扫描。
- 当id不同时，数字越大的越先执行。
常见select_type有这些：
| select_type | 含义 |
|---|---|
| SIMPLE | 简单SELECT，不包含子查询或UNION |
| PRIMARY | 复杂查询中最外层的那条语句 |
| SUBQUERY | SELECT或WHERE中的子查询 |
| DERIVED | FROM子句中的派生表（比如子查询结果作为临时表） |
| UNION | UNION操作中的后续查询部分 |
| UNION RESULT | 从UNION的临时表中读取结果的查询 |
这里有个很容易忽略的点：看到SUBQUERY或DERIVED时，要注意这种写法往往意味着优化器可能已经把子查询改写成JOIN了，如果没改，就说明临时表成本较高。尤其是DERIVED，在并发量上去之后非常容易出现性能瓶颈。
2.2 type访问类型：你的SQL是“全表摸”还是“索引点查”
type是EXPLAIN里最值得看的一列。它表示MySQL定位数据的方式，性能从好到差大概是：
system > const > eq_ref > ref > range > index > ALL
逐个解释一下：
- system：表只有一行，基本只有系统内部场景才会出现。
- const：通过主键或唯一索引，用常量等值匹配到最多一行。
- eq_ref：JOIN中被驱动表通过主键或唯一索引做等值关联，典型的多表JOIN场景。
- ref：非唯一索引等值匹配，查到的是符合条件的一组数据。
- range：索引范围扫描，常见于BETWEEN、IN、>、<等操作。
- index：扫描了整棵索引树，虽然也走索引，但等于把所有索引数据过了一遍。
- ALL：全表扫描，这是最需要警惕的。
我的经验是，优化SQL时目标是把type从ALL尽量提升到range以上，核心高并发接口最好能达到ref甚至const。不过也要看数据量：一张几百行的配置表，每次ALL扫描其实没多大影响，没必要为了追求type好看而强行建索引。
2.3 possible_keys、key与key_len：索引是否落地的实锤
possible_keys列出这条SQL“可能用到”的索引，key表示优化器“实际选择”的索引。如果possible_keys非空但key是NULL，说明你建的索引优化器权衡之后觉得用不上或者代价太高。
key_len是经常被忽略但非常有价值的列，它表示实际使用的索引字节长度，能反映出联合索引到底用了哪几列。举个例子：字段a是varchar(100)、utf8mb4字符集且NOT NULL，字段b是int且NOT NULL，建立的联合索引是(a, b)。varchar(100)在utf8mb4下每个字符占4字节，变长字段额外2字节，所以a单独用时key_len = 100 * 4 + 2 = 402。如果key_len变成了402 + 4 = 406，说明a和b两列都用上了。
通过key_len判断“联合索引是否被完整使用”非常实用，因为你光看key只知道用了哪棵索引树，看不出用了树上的哪些分支。
2.4 rows与filtered：掂一掂这条SQL的分量
rows是优化器估算的扫描行数，注意是估算值，不是准确值。filtered表示经过条件过滤后，剩余行数的百分比。两者相乘，大概就是最终结果集的行数。
比如某张表有10000行，EXPLAIN显示rows=10000，filtered=10%，那意味着MySQL预计筛选后返回约1000行。如果这个数字和实际业务体量差别很大，就要考虑是不是统计信息过期了。
我遇到过一个真实案例：A同学负责的某个后台列表接口，SQL很简单，但rows估算只有几十，实际查询却要几百毫秒。后来一查，是表长期没有更新统计信息，导致优化器选错了索引。这种问题用EXPLAIN一看就露馅了。
2.5 Extra字段里的安全隐患：临时表、文件排序
Extra这一列的信息量很大，但也最容易被人忽略。常见值大概是下面这些：
| Extra值 | 含义 | 优化建议 |
|---|---|---|
| Using index | 覆盖索引，查询列和数据都在索引里，不需要回表 | 非常理想，继续保持 |
| Using index condition | 索引条件下推，先通过索引过滤一部分数据再回表 | 性能较好 |
| Using where | 读出来之后再用WHERE过滤 | 合理但要注意过滤比例 |
| Using filesort | 无法用索引顺序返回结果，需要额外排序 | 尽量让ORDER BY走索引 |
| Using temporary | 用了临时表，常出现在GROUP BY、DISTINCT、UNION | 尝试优化GROUP BY字段顺序 |
| Using join buffer | 关联查询时使用了JOIN缓冲区 | 很可能是关联字段没索引 |
这里重点说Using filesort。MySQL里面有“文件排序”不等于真的产生了磁盘临时文件，但它至少说明没法直接依靠索引的有序性来返回结果。当数据量一上来，排序的CPU和内存开销会非常明显。遇到这种情况，优先考虑把ORDER BY的字段和WHERE等值条件一起建成联合索引，因为联合索引天然有序，可以顺带解决排序问题。
Using temporary同样是个大坑。GROUP BY和DISTINCT一旦触发临时表，通常意味着MySQL要额外开辟内存或磁盘空间来保存中间结果。碰到这种情况，我的习惯是先看GROUP BY的字段是不是能被现有联合索引覆盖，如果可以，临时表往往能直接消掉。
3. 实战：一条慢SQL如何被EXPLAIN救回来
3.1 还原现场，先跑EXPLAIN看全貌
为了把上面这些概念串起来，我们模拟一个常见的业务场景：某个用户中心需要查询用户“某开发者”最近下的20笔订单，SQL大概长这样：
SELECT o.order_id, o.amount, u.nickname FROM orders o LEFT JOIN users u ON o.user_id = u.id WHERE u.nickname = '某开发者' ORDER BY o.created_at DESC LIMIT 20;orders表结构经过简化是这样的：
CREATE TABLE `orders` ( `id` INT NOT NULL AUTO_INCREMENT, `user_id` INT NOT NULL, `created_at` DATETIME NOT NULL, `amount` DECIMAL(10,2) NOT NULL, `status` TINYINT NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB;users表结构也做了简化：
CREATE TABLE `users` ( `id` INT NOT NULL AUTO_INCREMENT, `nickname` VARCHAR(50) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;此时orders表约10000行，users表约5000行，两个表除了主键外没有任何其他索引。跑一条EXPLAIN看一下：
EXPLAIN SELECT o.order_id, o.amount, u.nickname FROM orders o LEFT JOIN users u ON o.user_id = u.id WHERE u.nickname = '某开发者' ORDER BY o.created_at DESC LIMIT 20;输出结果关键列大致是：
| 表 | type | possible_keys | key | rows | Extra |
|---|---|---|---|---|---|
| users | ALL | NULL | NULL | 5000 | Using where |
| orders | ALL | NULL | NULL | 10000 | Using filesort |
看到这个结果，问题基本就清楚了：两边都是全表扫描，排序也没走索引。第一条SQL耗时约800毫秒到1秒，接口直接超时。
3.2 一步步加索引、改SQL，再用EXPLAIN验证
第一步，users表的nickname在业务上应该是唯一的，于是加上唯一索引：
ALTER TABLE users ADD UNIQUE KEY uk_nickname (nickname);这时候再跑EXPLAIN，users表的type从ALL变成了const，rows从5000降到了1，说明MySQL能直接根据nickname把用户定位出来。但orders表仍然是ALL，所以整体性能并没有真正改善。
第二步，给orders表建一个联合索引(user_id, created_at)：
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);同时把LEFT JOIN改成INNER JOIN。如果是真实业务里订单必然对应用户，内连接逻辑上是成立的，而且能让优化器有更多选择空间。
EXPLAIN SELECT o.order_id, o.amount, u.nickname FROM orders o JOIN users u ON o.user_id = u.id WHERE u.nickname = '某开发者' ORDER BY o.created_at DESC LIMIT 20;优化后的EXPLAIN结果大致变成：
| 表 | type | possible_keys | key | rows | Extra |
|---|---|---|---|---|---|
| users | const | uk_nickname | uk_nickname | 1 | Using where |
| orders | ref | idx_user_created | idx_user_created | 24 | Using index condition |
此时users表先被锁定为一行，orders表通过idx_user_created直接等值匹配user_id，估算扫描行数从10000降到24，Using filesort消失，因为联合索引(user_id, created_at)在user_id等值的前提下，created_at天然是有序的，排序可以由索引直接完成。
3.3 你以为建了索引就完了？三种典型的“索引失效”
刚把索引加上去时，很多人以为大功告成，结果EXPLAIN一看，type还是ALL，或者possible_keys有值但key是NULL。这里分享几个我踩过的“索引失效”场景。
第一种：对索引列做函数运算或表达式计算。
SELECT * FROM orders WHERE YEAR(created_at) = 2023;这种写法会让优化器无法利用B+树的有序结构，因为索引里存的是具体日期，而条件要求先计算YEAR再比较。改成范围查询会好很多：
SELECT * FROM orders WHERE created_at >= '2023-01-01 00:00:00' AND created_at < '2024-01-01 00:00:00';第二种：隐式类型转换。
如果字段是varchar类型，但查询条件传入的是数字，MySQL会做隐式转换，导致索引用不上。比如手机号字段phone是varchar，却写成WHERE phone = 13112345678。你在EXPLAIN里就会看到type退化。判断办法很简单：确保传入参数的类型和字段类型保持一致，或者通过CAST显式转换。
第三种：LIKE左模糊。
WHERE nickname LIKE '%开发者%'，因为通配符在最前面，B+树的最左前缀匹配直接从根部失效。要想优化这类搜索，得考虑分词索引或者全文索引，而不是指望普通B+树索引。
还有一个容易忽略的场景是OR条件：比如WHERE user_id = 1 OR status = 2，如果status没有索引，优化器可能直接放弃user_id上的索引，改成全表扫描。碰到这种情况，可以尝试拆成UNION ALL来验证效果。
3.4 优化前后对比，数据会说话
把整个优化过程汇总一下：
| 指标 | 优化前 | 优化后 |
|---|---|---|
| users访问类型 | ALL | const |
| orders访问类型 | ALL | ref |
| 估算扫描行数 | 15000 | 约25 |
| Extra | Using filesort | Using index condition |
| 耗时 | 约800ms | 约10ms以内 |
这个案例也说明，EXPLAIN不是优化工具本身，但它能在最短时间内告诉你“应该优化哪里”。没有执行计划的时候，你可能花一整天反复试错；有了执行计划，半小时就能定位到根因。
4. 实战踩坑记录：EXPLAIN里容易误判的细节
4.1 常见问题速查表
日常排查时，我总结了一张比较实用的对照表，遇到问题可以直接查：
| 现象 | 可能原因 | 处理方式 |
|---|---|---|
| key为NULL但possible_keys有值 | 统计信息过旧，或优化器认为走索引代价更高 | 重新收集统计信息，观察数据分布 |
| rows特别大但key非NULL | 索引选择性差，或回表成本高 | 考虑覆盖索引，减少回表 |
| 有Using filesort | ORDER BY没走索引 | 调整联合索引字段顺序 |
| 有Using temporary | GROUP BY、DISTINCT导致临时表 | 尝试让GROUP BY字段走索引 |
| 建了索引还是ALL | 函数运算、隐式类型转换、LIKE左模糊等 | 改写SQL条件 |
| EXPLAIN结果很好但线上慢 | 可能是锁等待、硬件资源不足或数据分布不均 | 结合慢查询日志、锁监控综合判断 |
4.2 更进阶的EXPLAIN用法：JSON格式与真实耗时
基础版EXPLAIN已经够用，但MySQL 8.0+还提供了两个更强大的形式。
一个是EXPLAIN FORMAT=JSON：
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 1;JSON格式里能看到优化器计算出的read_cost、eval_cost等成本参数，信息比表格形式更细，适合分析“为什么优化器选择这条路径”。
另一个是EXPLAIN ANALYZE：
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;EXPLAIN ANALYZE会真实执行SQL，并输出实际耗时、实际扫描行数和循环次数。普通EXPLAIN给的是优化器的“预估”，而ANALYZE给的是“实测”。两者配合起来，很容易发现统计信息不准或者数据分布倾斜的问题。
不过要注意：EXPLAIN ANALYZE是真正执行语句的，虽然它主要用于SELECT，但生产环境跑复杂查询时还是要关注临时负载，不要随手压上去。
4.3 几条独家使用心得
根据我的经验，有几点特别想分享：
- 看EXPLAIN时不要只盯type，key_len和Extra同样重要。type是ref但Extra带Using filesort的情况很常见，只优化type是治标不治本。
- 如果possible_keys有值但key是NULL，别急着强行加FORCE INDEX。先看统计信息是否需要更新，再用FORCE INDEX做对照测试，但要明白FORCE INDEX只是临时手段，优化器的选择逻辑还是需要理解清楚。
- 线上排查时，我习惯把EXPLAIN结果和慢查询日志一起看。慢查询日志告诉你是谁慢了，EXPLAIN告诉你为什么慢，两者组合几乎能解决90%的SQL性能问题。
- 别忘了在真实数据量下看EXPLAIN。本地开发库只有几百行数据，可能怎么查都是ALL，建索引和不建索引效果差不多；线上几百万行数据时，执行计划才会完全不一样。
5. 从EXPLAIN出发，我养成的三个SQL优化习惯
5.1 先写“容易走索引”的SQL
现在我写新SQL时会主动避开那些让索引失效的写法：不加函数在索引列上、保持字段类型一致、LIKE不从中间匹配、ORDER BY和GROUP BY尽量和WHERE等值条件组成联合索引。这不是什么高深技巧，就是把执行计划记在脑子里，倒推SQL怎么写更友好。
比如某次做一个报表查询，原来同事写了WHERE DATE_FORMAT(created_at, '%Y-%m-%d') = '2024-05-20'，我改成created_at BETWEEN '2024-05-20 00:00:00' AND '2024-05-20 23:59:59'，EXPLAIN的type直接从ALL变成range，查询耗时就降了一个数量级。
5.2 联合索引的顺序，永远先想EXPLAIN怎么走
联合索引设计时，我遵循一条简单经验：等值查询的列放前面，排序的列放后面，范围条件尽量靠后。因为B+树的索引顺序一旦遇到范围条件，后面的列就没办法继续用于排序或者过滤。
还是拿上面orders表的例子来说，(user_id, created_at)这个联合索引，等值条件user_id放在前面，排序字段created_at跟在后面，查询时既能定位用户订单，又能避免文件排序。这些决定如果不看EXPLAIN，很难验证是否真的有效。
5.3 数据量不同，优化方案要跟着变
最后想提醒一点：EXPLAIN给出的判断，在数据量不同时结论可能完全相反。几千行的小表全表扫描可能比走索引还快，因为回表和索引查找同样有开销；但几百万行的大表，少一次全表扫描就能节省好几秒。
所以我的习惯是：先确认业务数据量，再结合EXPLAIN做判断，最后用实际耗时验证。如果发现优化器选错了执行计划，先排查统计信息是不是过期了，再考虑SQL改写和索引设计，而不是一上来就质疑优化器。
优化SQL这事，说难也难，说简单也简单。难的是一直在黑暗里猜，简单的是有了EXPLAIN这双眼睛，所有线索都摆在你面前。把每个字段的含义吃透，把每次执行计划变化背后的原因弄明白，你的SQL优化能力一定会超过大多数靠“加索引碰运气”的人。
