MySQL----索引

一.什么是索引

帮助存储引擎快速获取数据的一种数据结构，形象的说就是索引是数据的目录。

二.索引数据结构

二叉树

二叉查找树:顺序插入会退化为单向链表 ，大数据量的情况下层级较深，检索性能较低。

红黑树(自平衡二叉树)： 解决了顺序插入退化为单向链表的问题，但仍然存在数据量大，层级深检索慢的缺点

无论是二叉查找树还是红黑树，本质上都是二叉树，每个节点只能有2个子节点，当节点个数越多，树的高度也会变高，查询时磁盘的I/O次数也会增加，影响数据查询的效率

B树

B树不再限制一个节点只能有两个子节点，而是允许M个子节点， M 称为 B 树的阶，所以 B 树就是一个多叉树。 从而降低树的高度，降低查询时的磁盘I/O次数。

但B树的每个节点中都存储数据(索引+记录),单个节点能存储的索引数据更少，相对来说其树的高度高于b+树,查询时的磁盘I/O更多。

B+树

B+树的叶子节点才会存储实际数据(索引+记录),非叶子节点只存放索引。所有索引都会在叶子节点出现，叶子节点间构成有序链表。

B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。

B+树存在冗余节点，插入与删除效率高，不会发生复杂的树的变形。

B+树的叶子节点还用链表进行了链接，范围查询效率也更高。

对于索引的选择核心在于查询时如何尽可能减少磁盘I/O次数，我们知道InnoDB引擎在读取数据时是按照数据页为单位来进行读取的，读取一数据页的数据到内存中即为一次磁盘I/O，那么我们想要降低磁盘I/O次数，即提高数据页的有效利用率，在一页当中尽可能存储更多索引数据，B+树结构就完美符合了该需求。

总结

1. 核心目标：减少磁盘I/O次数 → 提升查询性能。

2. 实现手段：

3. 以数据页为单位加载（InnoDB默认16KB）。

4. 提高单页的有效利用率（存储更多索引键值）。

5. B+树的结构天然适配这一目标。

2. B+树如何“完美符合”这一需求？

（1）非叶子节点仅存储键值+指针

6. 空间利用率极高：

7. 假设主键为BIGINT（8字节），指针6字节，单页（16KB）可存储约 16 * 1024/(8+6) ≈ 1200 个键值。

8. 对比B树：若节点还需存储数据（如1KB的行记录），单页可能只能存16个键值 → 树更高，I/O更多。

9. （2）叶子节点存储数据+双向链表

10. 范围查询优化：

11. 链表结构使得范围查询（如WHERE id BETWEEN 10 AND 100）只需：

12. 3次I/O定位到起始叶子节点。

13. 顺序遍历链表（无需回溯父节点，减少随机I/O）。

14. 对比B树：需反复回溯父节点，引发随机I/O。

15. （3）树高度极低

16. 千万级数据仅需3层：

17. 非叶子节点单页存1200键值 → 3层树可索引 1200^3 ≈ 17亿 条数据。

18. 对比B树：同样数据量可能需要5层，查询多2次I/O。

---

3. 补充：B+树的其他隐藏优化

（1）磁盘预读（Read-Ahead）

19. 磁盘会预读相邻页（局部性原理），B+树的顺序存储特性（叶子节点链表）能充分利用预读，减少未来I/O。

20. （2）缓存命中率

21. 非叶子节点不存数据 → 内存可缓存更多索引页，根节点和热点索引常驻内存，进一步减少I/O。

22. （3）页分裂与合并高效

23. B+树的页分裂（插入数据时）和合并（删除数据时）仅影响相邻节点，而B树可能需递归调整父节点，导致更多I/O。

5. 结论
B+树通过最大化单页索引存储密度（非叶子节点仅存键值）和顺序访问优化（叶子节点链表），实现了：

24. 更矮的树高 → 减少I/O次数。

25. 更高的缓存命中率 → 减少磁盘访问。

26. 更优的范围查询 → 避免随机I/O。

27. 这正是MySQL选择B+树的核心原因！

三.索引的分类

三.索引的分类

按字段特性分

按存储内容分

聚集索引: 聚集索引的叶子节点下存放的是行数据；只有一个，主键索引即为聚集索引

二级索引: 二级索引的叶子节点下存放该字段的主键id，不存储实际数据

回表查询:先在二级索引中查询该字段对应的主键id ,再去聚集索引中查询整行数据(性能较低，尽可能避免)

按字段个数分

单列索引: 一个字段建立的索引,如主键索引

联合索引: 多个字段共同建立的索引

四.索引建立时机

索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：

需要占用物理空间，数量越大，占用空间越大；

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；

会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。

什么时候适用索引？

字段有唯一性限制的，比如商品编码；

经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度， 如果查询条件不是一个字段，可以建立联合索引。

经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序， 因为建立索引之后在 B+Tree 中的记录都是排序好的。

五.索引优化

前缀索引优化

字段类型为字符串时 选取字符串的一部分为前缀

前缀索引查询流程

先截取与前缀索引一致的字符串，去前缀索引中查询主键id，再去聚集索引查询数据 ，查到的数据与原始整体数据比对，是则返回结果，再进一步判断下一个节点前缀是否相同，相同则重复一次，不相同则结束

覆盖索引优化

回表查询 示例(id与name都已单独建立索引)

select id,name，phone from tb_user where name = "张三"

该行sql语句涉及到的索引有id(聚集索引/主键索引)，name(二级索引)，该sql语句的查询条件是根据name做等值匹配，那么首先会从二级索引name中查询到name = "张三"，这条记录对应的主键id，但二级索引中只包含主键与name的值并不包含phone的数据，所以需要根据主键id再到聚集索引中查询phone对应的值，磁盘I/O次数较多。

由此可见，回表查询涉及到多个索引的查询，多次磁盘I/O性能必定会下降；解决方式:对id，name，phone设置联合索引，新的联合索引叶子节点中就同时具有这三个字段的数据，无序再根据主键id去聚集索引中查询，即无序回表查询，通过建立联合索引降低磁盘I/O次数。

六.索引失效场景

当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者like %xx%这两种方式都会造成索引失效；

当我们在查询条件中对索引列做了计算、函数、类型转换操作，这些情况下都会造成索引失效；