MySQL 基础里的索引入门（建索引/EXPLAIN 基本用法）见《MySQL 快速入门》。这篇把索引讲透：B+Tree 为什么快、聚簇索引和二级索引的区别（回表）、联合索引最左前缀、EXPLAIN 怎么读、索引失效场景。

一、B+Tree：索引为什么快

B+Tree 的特点（对应上图）：

1. 非叶子节点只存 key（做导航）→ 一页能装很多 key → 树很矮（千万行也只有 3-4 层）→ 每次查询只要 3-4 次磁盘 IO
2. 叶子节点存全部数据，且用链表串起来 → 范围查询（BETWEEN / > / <）顺着链表扫
3. 查找：每层二分定位 = O(log n)，全表扫描是 O(n)

对比：

结构	等值查询	范围/排序	磁盘友好
B+Tree（InnoDB）	✅	✅（叶子链表）	✅（矮、IO 少）
哈希	✅（O(1)）	❌	✅
红黑树	✅	✅	❌（节点散落，IO 多）

二、聚簇索引 vs 二级索引（回表）

InnoDB 的索引分两类，这是理解索引性能的关键：

	聚簇索引（主键索引）	二级索引（普通索引）
叶子存什么	整行数据	索引列 + 主键值
数量	只能一个（主键）	可以多个
查询	直接拿数据	先拿主键 → 回表再查一次

CREATE TABLE user (
    id INT PRIMARY KEY,          -- 聚簇索引
    name VARCHAR(50),
    age INT,
    INDEX idx_name (name)        -- 二级索引
);

-- 走聚簇索引：直接拿数据，不回表
SELECT * FROM user WHERE id = 1;

-- 走二级索引 idx_name：先找到 name 对应的 id，再回表查整行
SELECT * FROM user WHERE name = 'BinMaker';
-- 执行过程：idx_name 找到 id → 回表（用 id 再查聚簇索引）→ 拿整行

覆盖索引（避免回表）：查的字段都在索引里，就不用回表：

-- 只查 name 和 id——idx_name 里都有，不用回表（Extra 显示 Using index）
SELECT id, name FROM user WHERE name = 'BinMaker';

三、联合索引：最左前缀原则

CREATE INDEX idx_age_name ON user(age, name);
-- 等价于三个索引：(age)、(age, name)——但 NOT (name)

最左前缀：查询条件从联合索引最左列开始连续匹配才走索引：

查询条件	走不走 idx_age_name
WHERE age = 25	✅（只用 age 部分）
WHERE age = 25 AND name = 'a'	✅（全用）
WHERE name = 'a'	❌（没从 age 开始）
WHERE age > 25 AND name = 'a'	⚠️ 部分（age 用索引，name 用不了——范围查询后失效）

设计原则：把等值查询的列放前面，范围查询的列放后面（> < BETWEEN 会截断后面的列）。

四、EXPLAIN 怎么读（慢 SQL 排查核心）

EXPLAIN SELECT * FROM user WHERE age > 25;

字段	含义	关注点
type	访问类型	const/eq_ref/ref/range（好）→ index（一般）→ all（全表扫，⚠️）
key	实际用的索引	null = 没走索引
rows	预估扫描行数	越小越好（对比表总行数）
Extra	额外信息	Using index（覆盖✅）/ Using filesort（文件排序⚠️）/ Using temporary（临时表⚠️）

典型问题：

• type=all + rows=1000000 → 全表扫描，没走索引
• Extra=Using filesort → ORDER BY 没走索引（加索引或调整列顺序）
• key=null → 条件列没索引 或 索引失效（见下）

五、索引失效场景（面试必问）

场景	原因	解决
函数/运算套列 WHERE YEAR(create_time)=2026	破坏索引有序性	改成范围：create_time BETWEEN '2026-01-01' AND '2026-12-31'
隐式类型转换 WHERE phone = 13800138000（phone 是 varchar）	类型不匹配，MySQL 转类型	查询用字符串：phone = '13800138000'
前导模糊 WHERE name LIKE '%Bin%'	从中间匹配，无法走前缀	改后缀模糊 'Bin%'（走索引）或用全文索引
最左前缀不满足（见上）	联合索引没从最左列开始	调整查询或索引列顺序
OR 连接非索引列 WHERE age=25 OR name='a'（name 无索引）	必须全表	两边都建索引或拆查询
索引列参与运算 WHERE age+1 = 26	破坏有序	改 WHERE age = 25

六、实跑验证：EXPLAIN 各场景（MySQL 8 实测）

-- 建表 + 造 10 万行
CREATE TABLE t_user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    INDEX idx_age (age)
);

-- ① 走索引（range：选择性高的范围查询）
EXPLAIN SELECT * FROM t_user WHERE age > 95;    -- type=range, key=idx_age
-- ② 等值（ref）
EXPLAIN SELECT * FROM t_user WHERE age = 25;    -- type=ref, key=idx_age

-- ② 全表扫描（没索引的列）
EXPLAIN SELECT * FROM t_user WHERE name = 'a';  -- type=all, key=null

-- ③ 函数失效
EXPLAIN SELECT * FROM t_user WHERE age + 1 = 26; -- type=all（索引失效）

实跑结论：走索引 rows=3169（age>95）/ rows=832（age=25），全表扫 rows=78125——性能差 25-90 倍。

⚠️ 实测发现：有索引却不走（优化器决策）——EXPLAIN SELECT * FROM t_user WHERE age > 25 结果是 type=ALL（全表扫）！为什么？

• age 是 0-99 均匀分布，age > 25 要过滤 75% 的数据
• 走索引要逐个回表（每行一次随机 IO），75% 的行都要回表 = 比全表顺序扫还慢
• 优化器算完成本：全表扫更便宜 → 选 ALL（possible_keys=idx_age 但 key=NULL）

结论：索引不是建了就一定用——选择性差（过滤太多行）+ 回表成本会让优化器放弃索引。等值（=）和选择性高的范围（> 95）才会稳定走索引。

小结

• B+Tree：非叶子只存 key（树矮、IO 少）+ 叶子链表（范围查询快）
• 聚簇索引存整行，二级索引存"列+主键"，查二级索引要回表；覆盖索引避免回表
• 联合索引最左前缀：等值列放前、范围列放后
• EXPLAIN 看 type（别 all）/key/rows/Extra（别 filesort）
• 失效场景：函数/隐式转换/前导模糊/最左前缀/OR/运算

下一篇可以看《MySQL 事务与隔离级别》（并发控制另一半），或《分库分表》（数据量超千万后的扩展方案，整理中）。