Java面试第七山！《MySQL索引》

一、索引的本质与作用

索引是帮助MySQL高效获取数据的数据结构，类似于书籍的目录。它通过减少磁盘I/O次数（即减少数据扫描量）来加速查询，尤其在百万级数据场景下，索引可将查询效率提升数十倍。

核心作用：

1. 加速查询：避免全表扫描，快速定位数据。

2. 保证数据唯一性：唯一索引可避免重复值。

3. 优化排序与分组：索引天然有序，减少排序开销。

---

二、索引的底层数据结构：B+树

为什么选择B+树？

MySQL索引默认使用B+树而非二叉树、红黑树或B树，原因如下：

对比项	B+树优势
树高控制	节点存储多个键值，树高通常为3~4层，支持千万级数据（如单节点可存约1170个键）。
范围查询	叶子节点通过指针形成有序链表，适合BETWEEN、ORDER BY等操作。
磁盘I/O优化	每次读取一页（16KB），充分利用局部性原理，减少磁盘寻道次数。

（图示：B+树的非叶子节点仅存键值，叶子节点存储完整数据或主键，并形成有序链表）

---

三、存储引擎的索引实现差异

1. InnoDB vs MyISAM

特性	InnoDB（聚集索引）	MyISAM（非聚集索引）
数据存储	数据与索引合并存储（.ibd文件）	数据（.MYD）与索引（.MYI）分离
主键索引	叶子节点存储完整数据行	叶子节点存储数据行地址
辅助索引	存储主键值，需回表查询	直接存储数据地址
事务支持	✅	❌

示例：

• InnoDB查询流程：通过辅助索引找到主键，再通过主键索引获取数据（二次查找）。

• MyISAM查询流程：直接通过索引找到数据地址，一次定位。

---

四、索引类型与使用场景

1. 常见索引类型

类型	特点
主键索引	唯一且非空，InnoDB中为聚集索引
唯一索引	列值唯一，允许NULL
联合索引	多列组合索引，遵循最左前缀原则（如(a,b,c)仅支持a、a,b等）
覆盖索引	索引包含查询所需字段，避免回表（如SELECT id FROM table WHERE name=?）。

2. 联合索引的最左前缀原则

示例：索引(name, age, position)

• ✅有效查询：WHERE name='Alice' AND age=25

• ❌无效查询：WHERE age=25（未以name开头）

原理：索引按字段顺序构建，只有左前缀字段有序，后续字段仅在左前缀固定时有序。

---

五、索引优化实战技巧

1. 索引失效的六大场景

1. 未遵循最左前缀：如跳过联合索引首字段。

2. 索引列使用函数或计算：如WHERE YEAR(create_time)=2023。

3. 类型不一致：如字符串列用数字查询（WHERE id='100'）。

4. LIKE以通配符开头：如LIKE '%abc'。

5. OR连接非索引列：如WHERE a=1 OR b=2（若b无索引）。

6. 全表扫描更快：当数据量小时，优化器可能放弃索引。

2. 分页查询优化

问题：LIMIT 100000,10会扫描前100010行，效率极低。
优化方案：使用覆盖索引+延迟关联。

SELECT * FROM table 
JOIN (SELECT id FROM table ORDER BY id LIMIT 100000, 10) AS tmp 
ON table.id = tmp.id;

3. 索引设计原则

• 选择性高：列值区分度高（如COUNT(DISTINCT col)/COUNT(*) > 0.1）。

• 短字段优先：整型比字符串更适合索引（主键推荐自增整型）。

• 避免冗余：联合索引可替代多个单列索引。

---

六、高频面试题解析

1. 为什么InnoDB推荐自增主键？

• 顺序写入：减少页分裂和碎片，提升插入效率。

• 存储紧凑：整型比UUID更省空间，加速比较。

2. 什么是回表？如何避免？

• 回表：通过辅助索引找到主键后，需回主键索引获取完整数据。

• 避免方法：使用覆盖索引（如SELECT id, name，若索引包含这两个字段）。

3. B+树 vs B树

对比项	B+树	B树
数据存储	仅叶子节点存数据	所有节点均可存数据
查询效率	稳定O(logN)，适合范围查询	随机查询更快，但范围查询差
空间占用	非叶子节点更小，存储更多键值	节点存储数据，占用更大空间

---

七、总结

理解索引底层原理（B+树结构、存储引擎差异）是优化数据库性能的关键。合理设计索引可让查询速度提升数十倍，而盲目添加索引可能导致写入性能下降。建议结合EXPLAIN分析执行计划，定期监控慢查询日志，实践中灵活运用覆盖索引、最左前缀等技巧。

翻过这座山，他们就会听到你的故事！