EXPLAIN执行计划优化：从type到Extra核心技巧
执行计划深度剖析：从 type 到 Extra，榨干 EXPLAIN 的调优价值
你肯定用 EXPLAIN 排查过 SQL 执行计划，但你真的吃透所有字段了吗？type 到底有多少种取值？Extra 中的 Using index、Using where、Using temporary、Using filesort 各自代表什么？key_len 如何精确计算？filtered 的价值在哪？这次我们彻底拆解 EXPLAIN 的每一列。
打个比方，type 决定了数据检索的“分拣效率”：最理想的像“按门牌号直达包裹”（const），最差的就是“翻遍整个仓库”（ALL）。possible_keys 列出可用传送带，key 是实际选中的那一条。rows 标记待检包裹数量，filtered 表示初检后还需人工二次分拣的比例。Extra 则记录附加操作标记，例如“走了传送带但仍需人工挑拣”（Using where）或“需要临时搭货架”（Using temporary）。
一、EXPLAIN 输出列完整解读
执行 EXPLAIN SELECT ... 后会返回一张表，每列含义如下：
| 列名 | 含义 | 关键点 |
| id | SELECT 的标识序号 | 数字越大越优先执行；相等则按顺序执行 |
| select_type | 查询类型 | SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION |
| table | 表名或别名 | 可能显示为派生表名 |
| partitions | 命中的分区 | 分区表环境下才体现 |
| type | 连接类型（核心指标） | 性能排序：system > const > eq_ref > ref > range > index > ALL |
| possible_keys | 可选的索引 | 列举候选索引 |
| key | 实际采用的索引 | 为 NULL 说明未使用索引 |
| key_len | 索引使用字节数 | 帮助判断联合索引用了几列 |
| ref | 索引列与哪个值做比较 | 常量 const 或字段名 |
| rows | 预估扫描行数 | 数值越大性能越差 |
| filtered | 存储引擎返回后剩余条件过滤的比例 | 100% 最佳 |
| Extra | 附加信息 | Using index、Using where、Using temporary、Using filesort 等 |
表格内容虽全，日常重点关注几个字段即可：type、key、rows、Extra，以及联合索引场景下的 key_len。
二、type 详解：性能的分水岭
type 决定了 MySQL 如何定位目标行，按性能从优到劣排列：
| type | 含义 | 示例 | 触发条件 |
| system | 系统表，仅一行 | 几乎不出现 | 系统表或 const 的特例 |
| const | 最多匹配一行，主键或唯一索引等值查询 | WHERE id = 1 | 主键或唯一索引且查询为常量 |
| eq_ref | 唯一索引关联，每行只匹配一条 | JOIN ... ON t1.id = t2.id 且 t2.id 是主键 | 被驱动表使用主键或唯一索引连接 |
| ref | 非唯一索引或前缀索引等值匹配 | WHERE name = 'abc'（name 有普通索引） | 索引列非唯一或允许 NULL |
| range | 索引范围扫描 | WHERE id BETWEEN 1 AND 100 或 IN、>、< | 索引列上的范围条件 |
| index | 全索引扫描 | 覆盖索引但无过滤条件 | 遍历整个索引树 |
| ALL | 全表扫描（最差） | 无索引或优化器认为全表更快 | 大表且缺少有效索引 |
优化底线：至少达到 range 级别，力争达到 ref 或 const。
实战案例：
-- type = ALL 必须优化
EXPLAIN SELECT * FROM orders WHERE amount > 100;
-- 增加索引后 type 提升为 range
ALTER TABLE orders ADD INDEX idx_amount(amount);
三、Extra 详解：优化器背后的小动作
Extra 列记录了执行过程中的额外操作，是定位性能瓶颈的关键：
| Extra 信息 | 含义 | 优劣 | 优化思路 |
| Using index | 覆盖索引，无需回表 | ✅ 优秀 | 无需调整 |
| Using where | 存储引擎返回后在 Server 层过滤 | 普通 | 尝试将过滤条件纳入索引 |
| Using temporary | 使用临时表（常见于 GROUP BY 或 DISTINCT） | ⚠️ 差 | 通过索引优化 GROUP BY/ORDER BY |
| Using filesort | 无法利用索引排序，需额外排序 | ⚠️ 差 | 对 ORDER BY 列建立索引 |
| Using index condition | 索引下推（ICP） | ✅ 优秀 | MySQL 5.6+ 自动启用 |
| Using join buffer | 连接使用 Block Nested Loop 缓存 | 普通 | 添加索引消除缓存 |
| Impossible WHERE | WHERE 条件恒假 | 无需优化 | 检查 SQL 逻辑 |
| No tables used | 无 FROM 或 FROM DUAL | - | - |
强调：Using filesort 并非真实文件操作，而是指无法依赖索引排序，改在内存或磁盘中完成，当排序数据量较大时极慢。
实战案例：
-- 出现 Using filesort
EXPLAIN SELECT * FROM orders ORDER BY create_time;
-- 加索引后 Using filesort 消失
ALTER TABLE orders ADD INDEX idx_create_time(create_time);
四、组合索引与 key_len 实战
key_len 表示 MySQL 在索引中实际占用的字节数，通过它可以精准判断联合索引使用了多少列。
计算规则：
基础长度：INT=4, BIGINT=8, DATE=3, TIMESTAMP=4, CHAR(n)=n×字符集字节数（utf8mb4=4），VARCHAR(n)=n×4 + 2。允许 NULL 则再加 1。
举例说明：索引 (user_id, log_date, type)，user_id 为 INT NOT NULL (4)，log_date 为 DATE NOT NULL (3)，type 为 TINYINT (1)。执行 WHERE user_id=1 AND log_date='2026-06-01' 时 key_len=4+3=7，说明仅用到了前两列。
核心原则：联合索引遵循最左前缀规则，中间列不能跳过，否则后面的列无效。
五、filtered 的真实用途
filtered 表示存储引擎返回的行中，满足剩余 WHERE 条件的估算比例。100% 代表最佳状态。如果 filtered 很小（例如 10%），说明索引初筛后仍有 90% 的行被淘汰，导致回表成本剧增。
在 JOIN 场景中，驱动表的 filtered 直接影响被驱动表的读取次数，是衡量索引覆盖力的重要指标。
六、完整优化案例：从 0.5 秒到 0.02 秒
原始 SQL：
SELECT * FROM orders
WHERE customer_id = 12345
AND status = 'PAID'
AND create_time > '2026-01-01'
ORDER BY create_time DESC
LIMIT 10;
原执行计划：type=ref，key=customer_id，rows=1000，Extra="Using where; Using filesort"。
问题定位：
仅使用了 customer_id 索引，status 和 create_time 的过滤发生在回表之后；filesort 因 create_time 未参与索引排序而产生。
优化方案： 创建联合索引 (customer_id, status, create_time)。
新执行计划：type=ref，key=联合索引，key_len=4+?，Extra 中 filesort 消失（索引天然有序）。
效果：查询耗时从 0.5 秒降至 0.02 秒。
七、实用检查清单：快速诊断 EXPLAIN
阅读 EXPLAIN 时按以下顺序逐项排查：
- type： 是否存在 ALL 或 index？有则考虑加索引。
- key： 是否为 NULL？是则索引未被使用。
- rows： 是否远超预期？检查索引选择性。
- Extra： 出现 Using temporary 或 Using filesort ？优化排序和分组逻辑。
- filtered： 低于 30%？评估索引能否覆盖更多过滤条件。