线上某个接口偶发慢查询告警，DBA 甩来一张 EXPLAIN 截图，问"这个查询走没走索引"。很多同学盯着 key 字段非 NULL 就觉得万事大吉，殊不知 type 是 ALL、Extra 出现了 Using filesort，索引其实根本没起作用。索引优化真正的门槛不在写 SQL，而在能不能读懂执行计划背后数据库到底在干什么。这篇文章我们把 EXPLAIN 的每个字段拆开揉碎，配合几个真实踩过的坑，讲清楚索引是怎么工作的。
一、B+ 树：索引的物理底座
InnoDB 的索引组织表（Index Organized Table）建立在 B+ 树之上。理解两种索引的物理差异，是看懂回表代价的前提。
1. 聚簇索引（Clustered Index）
聚簇索引就是数据本身。InnoDB 表按主键的 B+ 树组织，叶子节点存的是完整行数据。一张表只能有一个聚簇索引。如果没显式定义主键，InnoDB 会选第一个所有列都非空的唯一索引；都没有的话，会生成一个隐藏的 6 字节 ROW_ID 列作为聚簇键。
2. 二级索引（Secondary Index）
二级索引的叶子节点不存完整行，而是存 (索引列值, 主键值)。这意味着通过二级索引查到主键后，还要拿着主键回到聚簇索引里找完整行——这个过程叫回表（Bookmark Lookup）。回表的本质是再一次随机 IO，代价远高于顺序扫描索引页。
关键结论：二级索引天然包含主键。这就是为什么主键设计上推荐用短而连续的整型（如 BIGINT），过长的主键会让所有二级索引都跟着膨胀。
二、最左前缀匹配
联合索引 (a, b, c) 在 B+ 树里是按 a 排序、a 相同按 b 排序、再按 c 排序存储的。所以索引能匹配的范围，严格受字段顺序约束。
WHERE a=1 AND b=2 AND c=3—— 三个字段全部命中WHERE a=1 AND c=3—— 只能用 a，c 用不上（中间断了 b）WHERE b=2 AND c=3—— 完全走不了该索引（缺少最左 a）WHERE a=1 AND b>5 AND c=3—— a、b 命中，但 b 是范围查询，c 用不上
这里有个常被忽略的细节：范围查询会让其右侧的列失效。范围条件（> < BETWEEN LIKE 'x%' 中的前缀模糊除外）一旦作用在某列上，该列右边的列都无法参与索引过滤。这也是索引下推 ICP 出场的原因。
三、EXPLAIN 字段详解
跑一条 EXPLAIN SELECT ...，输出有 12 列。下面只挑最关键的几列展开。
type —— 访问类型（性能等级）
从优到差，常用等级如下。生产中一般要求至少达到 range，index 和 ALL 都要警惕。
| 类型 | 含义 | 典型场景 |
|---|---|---|
| system | 表只有一行 | 系统表 |
| const | 通过主键或唯一索引等值匹配，最多一条 | WHERE id=1 |
| eq_ref | JOIN 中被驱动表用主键/唯一索引等值匹配，最多一条 | JOIN 关联主键 |
| ref | 通过普通索引等值匹配，可能多条 | WHERE user_id=100 |
| range | 索引范围扫描 | WHERE id BETWEEN 1 AND 100 |
| index | 扫描整个索引树（不回表） | 统计 count(*) 且有覆盖索引 |
| ALL | 全表扫描，最差 | 无可用索引或索引失效 |
possible_keys 与 key
possible_keys 是优化器认为可能用到的索引，key 是实际选中的。如果 possible_keys 有值但 key 是 NULL，多半是优化器评估后认为全表扫描更划算（数据量小、或索引区分度低）。可以用 FORCE INDEX(idx_xxx) 强制干预，但要配合 rows 验证效果。
key_len —— 索引到底用了几列
这是判断联合索引命中列数的核心字段。计算规则（基于 utf8mb4，每字符 4 字节）：
- 整型：
TINYINT=1、SMALLINT=2、INT=4、BIGINT=8 CHAR(n)= 4n；VARCHAR(n)= 4n + 2（2 字节存长度）- 允许 NULL 的列，再加 1 字节记录 NULL 标记
DATE=3、DATETIME=5、TIMESTAMP=4
举个例子，联合索引 idx(a INT, b VARCHAR(20), c INT)，三列都允许 NULL：
a: 4 + 1(null) = 5
b: 4*20 + 2 + 1(null) = 83
c: 4 + 1(null) = 5
全命中 key_len = 5 + 83 + 5 = 93如果 EXPLAIN 显示 key_len=5，说明只命中了 a 列。这是个比盯着 SQL 看更可靠的判断手段。
rows —— 估算扫描行数
基于统计信息估算，不是精确值。优化器选索引就是基于它和索引区分度算成本。如果统计信息过期导致误判，可以 ANALYZE TABLE t; 重新采样。生产中遇到执行计划突然变差，先怀疑统计信息。
Extra —— 附加信息（重点关注）
| 取值 | 含义 | 是否需要优化 |
|---|---|---|
| Using index | 覆盖索引，无需回表 | 理想状态 |
| Using where | 存储引擎返回数据后 Server 层再过滤 | 视情况 |
| Using index condition | 启用了索引下推 ICP | 已优化 |
| Using filesort | 无法用索引完成排序，需额外排序 | 必须优化 |
| Using temporary | 用临时表（常见于 GROUP BY/DISTINCT） | 必须优化 |
四、覆盖索引与索引下推
覆盖索引
如果一个查询的所有字段都能从索引里直接拿到，就不用回表，Extra 会显示 Using index。常见做法是给高频查询建"宽"联合索引，把 SELECT 的列都纳入。例如：
-- 慢：每次都要回表
SELECT id, user_id, amount FROM orders WHERE user_id=100;
-- 建 idx(user_id, amount) 后，amount 在索引里，但 id 是主键也在二级索引叶子节点
-- EXPLAIN Extra: Using index索引下推（Index Condition Pushdown, ICP）
ICP 是 MySQL 5.6 引入的优化。在它出现前，联合索引 (a, b) 上执行 WHERE a=1 AND b LIKE '%xyz%' 时：存储引擎按 a=1 取出所有记录回表，再把每行交给 Server 层过滤 b。回表次数 = a=1 的行数。
有了 ICP，Server 层会把 b 的过滤条件下推到存储引擎层，引擎在索引上就先过滤掉不符合 b 的记录，再回表。回表次数显著下降。判断是否启用看 Extra 是否有 Using index condition，可用 SET optimizer_switch='index_condition_pushdown=off'; 关闭后对比 rows。
五、真实慢查询案例
下面四个案例都来自实际线上排查，给出优化前后的 EXPLAIN 对比。
案例 1：隐式类型转换导致索引失效
表结构：user_id VARCHAR(20)，建有 idx_user_id。Java 里 userId 是 Long，MyBatis 拼成了数字。
-- 错误写法：字符串列传了数字，MySQL 把 user_id 转成数字比较
SELECT * FROM t_order WHERE user_id = 10086;
-- EXPLAIN: type=ALL, key=NULL（索引失效，全表扫描）
-- 正确写法：值加引号
SELECT * FROM t_order WHERE user_id = '10086';
-- EXPLAIN: type=ref, key=idx_user_id原理：MySQL 的规则是"字符串转数字"，于是对列套了函数 CAST(user_id AS ...)，对索引列使用函数等同于破坏了索引匹配。
案例 2：前导 % 模糊查询
SELECT * FROM t_user WHERE name LIKE '%张';
-- type=ALL。B+ 树是有序的，前导 % 无法定位起点
-- 改为前缀匹配或覆盖索引扫描
SELECT id FROM t_user WHERE name LIKE '张%';
-- type=range，能用上 idx_name真有全文检索需求，请用 FULLTEXT 索引或外置 Elasticsearch，不要硬扛 LIKE。
案例 3：OR 连接导致全表扫描
-- 两个列分别有索引，但 OR 让优化器放弃
SELECT * FROM t_order WHERE user_id=100 OR amount>10000;
-- type=ALL（多数情况）
-- 用 UNION ALL 改写，让两支各自走索引
SELECT * FROM t_order WHERE user_id=100
UNION
SELECT * FROM t_order WHERE amount>10000 AND user_id<>100;案例 4：范围查询右侧列失效
-- 索引 idx(status, create_time, user_id)
SELECT * FROM t_order
WHERE status=1 AND create_time>'2026-01-01' AND user_id=100;
-- key_len 只覆盖 status + create_time，user_id 用不上
-- 改写思路：把等值条件放前面
-- 索引调整为 idx(status, user_id, create_time)
-- 这样 user_id 也能命中，key_len 增加六、联合索引字段顺序设计原则
设计联合索引本质是在回答"哪些列放前面"。几条经验法则：
- 等值优先于范围：把等值查询的列放最左，范围列放右边。
- 区分度高的放前面：区分度 = 不同值数量 / 总行数。高区分度能更快收敛扫描范围。
- 覆盖索引优先：把 ORDER BY / GROUP BY 涉及的列、以及高频 SELECT 的列纳入，争取覆盖索引。
- 避免冗余：如果已有
(a, b)，单独建(a)就是冗余。
一条 SELECT 用 EXPLAIN 验证，比看一百条理论都准。养成"建索引前先 EXPLAIN，上线后看慢查询日志"的习惯，索引才不会越建越多却越来越慢。
七、写在最后
索引优化不是玄学，是数据结构与成本估算的结合。当你能从 key_len 推断出命中了几列、从 Extra 读出是否有回表和排序，慢查询就不再是黑盒。希望这篇拆解能成为你排查线上问题时顺手翻的清单。