MySQL索引的B+树实战哲学

一、B+树：MySQL索引的“物理存储引擎”

1. 为什么B+树比哈希表更适合MySQL？

场景对比：

• 哈希索引：查询WHERE id=100只需1次计算，但无法支持BETWEEN price 100 AND 200这样的范围查询；
• B+树索引：查询单条数据需3次IO，但通过叶子节点的链表结构，范围查询只需“找到起点+顺序遍历”。

核心优势：

• 磁盘IO优化：B+树每个节点（默认16KB）可存储上千个索引键，树高通常仅3层（千万级数据），查询只需3次IO；
• 查询稳定性：任何数据都需从根节点到叶子节点，避免哈希表的“哈希冲突”导致性能波动。

2. B+树的“3层存储魔法”

结构拆解：

• 非叶子节点：只存索引键+子节点指针（如id:100 → 指针P1），不存实际数据；
• 叶子节点：存索引键+完整数据（InnoDB聚簇索引）或数据地址（MyISAM非聚簇索引），并通过双向链表连接。

计算示例：
假设每个索引键占8字节，指针占6字节，16KB节点可存储：
16*1024 / (8+6) ≈ 1170个索引键
→ 3层B+树可存储：1170 * 1170 * 16 (叶子节点数据行数) ≈ 2200万条数据

二、索引设计“避坑指南”：从失效到高效

1. 最左前缀原则：别让组合索引“白建”

错误案例：
创建组合索引(name, age)，却用WHERE age=25查询，索引完全失效。
原理：B+树的组合索引按“name→age”顺序排序，没有name条件，无法定位age的范围。

正确用法：

• WHERE name='张三' → 可用索引前缀；
• WHERE name='张三' AND age=25 → 全索引命中；
• WHERE name LIKE '张%' AND age=25 → 前缀匹配+范围条件（仅name生效）。

2. 索引选择性：性别列别建索引！

计算公式：
选择性 = 索引列不同值数量 / 总行数

• 高选择性（如id、email）：选择性≈1，索引效率高；
• 低选择性（如gender、status）：选择性≈0.02，索引效果不如全表扫描。

实战建议：

• 单表数据量<10万时，除非查询频繁，否则无需建索引；
• 组合索引将高选择性列放前面（如(order_id, product_id)而非(product_id, order_id)）。

3. 覆盖索引：避免“回表”的性能杀手

场景：
查询SELECT id, name FROM user WHERE age>20，若索引仅包含age，需先查索引找id，再回表查name（2次B+树查询）。

优化方案：
创建覆盖索引(age, id, name)，索引叶子节点已包含所有查询字段，无需回表。

验证方法：
用EXPLAIN查看Extra列，出现“Using index”表示命中覆盖索引。

三、索引失效的“6大陷阱”

陷阱场景	错误SQL示例	正确写法
索引列用函数	WHERE SUBSTR(name, 1, 3)='张三'	WHERE name LIKE '张三%'（前缀索引）
隐式类型转换	WHERE phone='13800138000'（phone为int）	WHERE phone=13800138000
不等于（!=、<>）	WHERE status != 1	改用status IN (0,2,3)
like以%开头	WHERE name LIKE '%三'	改用全文索引（FULLTEXT）
or连接非索引列	WHERE id=100 OR age=25（age无索引）	拆分为两个查询或给age加索引
组合索引非最左列	WHERE age=25（索引为(name,age)）	补充name条件或调整索引顺序

四、实战工具：用EXPLAIN诊断索引问题

关键字段解读：

• type：查询类型，ref（索引匹配）、range（范围查询）为优，ALL（全表扫描）为劣；
• key：实际使用的索引，NULL表示未用索引；
• rows：预估扫描行数，数值越小越好；
• Extra：Using filesort（文件排序）、Using temporary（临时表）需优化。

优化案例：

慢查询：

sql

SELECT * FROM order WHERE create_time > '2023-01-01' AND status=1;

诊断：EXPLAIN显示type=ALL，未用索引。
优化：
创建组合索引(create_time, status)，再次执行EXPLAIN，type=range，key=create_time_status。