参考

mysql索引之聚簇索引与非聚簇索引

数据结构

Mysql里聚簇索引和非聚簇索引（二级索引）都是采用的B+树结构

聚簇索引

B+树

B+树由二叉树->平衡二叉树->B树->B+树演化而来。

B树

B树对于平衡二叉树的改进

B树是对平衡二叉树的改进，平衡二叉树要求每个非叶子节点拥有不超过两个的子节点，这对于空间的利用率来说并不高，也会导致阶数过高，而B树的每个节点可以拥有不高于阶数的子节点数，这样可以减少阶数，提高查找效率。

B树的存储结构

B树的每个节点都包含指针（指向子节点）、键值（key）、数据（value）这三部分，如图

capture_20230319152852902(1).bmp

B树的优点

相对于平衡二叉树来说，B树提高了每一阶的利用率，存储的内容更多了，查找效率更高了

B树的缺点

这里有个知识点，系统读取数据是以磁盘块为单位整个读取的，页是多个磁盘块的组成，MySQL的InnoDB存储引擎就是以页为基本存储单位的，每页都是多个连续的磁盘块组成。页空间大小默认为16K（可以自定义大小），每个节点占据一页空间，也就是说在节点空间大小固定的情况下，假如数据部分过大的话，每个节点将存储不了多少内容，同时也会增加树的高度，也就没有很好解决查找效率的问题，于是产生了B+树，即在B树的基础上优化过来的存储方案。

B+树

B+树对于B树的改进

首先我们明确B树的问题在哪，是在有限空间下存储不了太多子节点地址，那么为什么呢？看看每个节点的组成，指针和键值一般不会太大，数据库设计也要求索引值不能太长，数据部分就是关键了，有的表一下子几十个字段上百个字段，肯定很大。那么优化的关键在于数据。

B+树为了节省空间，将数据转移到了叶子节点，这样就能节省出大量的空间可以存放其他的指针和键值。下面做一个推算：

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^3）。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿条记录，4层则是100000亿条数据，几乎使用不完。因此B+树的层数几乎在2-4层之间，磁盘IO开销也在4次以下。查找效率高了很多

B+树的存储结构

如图

另外，在B+树里还存储有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

非聚簇索引（二级索引）

二级索引，顾名思义就是有两个索引表。

为什么需要非聚簇索引（二级索引）？

我们考虑一下下面的情况。当有两个需求出现，一个要求用主键查找，一个要求用name查找，那么为了提高效率，按照之前的办法，我们是不是要保存两个B+树来保证查找效率。但是这样一来就会产生问题，也就是数据保存了两次，我们都知道，一个B+树指针和键值的占据的空间并不大，但是数据占据的空间就大了，那么怎么解决这个问题呢？这就是二级索引的方案。

非聚簇索引（二级索引）的设计方案

首先我们已知B+树的方式是高效率的，那么我们可以考虑在这个基础上进行设计，这样可以利用B+树的优点提高查找效率，为了保证只保存一份数据，我们要将第二份索引的叶子节点数据去除，换成主键，这样可以先找一遍name索引，找到主键，再去找主键索引，找出所有行数据。

为什么二级索引叶子节点不保存指向数据的指针而是用主键值呢？

这里插一个知识点，页分裂和页合并。索引数据有序的情况下，我们添加一条数据，当它应该在中间位置而不是添加到最后，同时所要添加的页已满的情况下，将产生页分裂现象，也就是开辟新的一页，将满了的页的数据分一半出来，添加到新的页同时添加上新数据。页合并则是在删除数据的时候产生，当一页数据少于一半的时候，MySQL将会考虑将前后页合并。页的物理地址是由固定的连续的数据块来组成的，但是页并不连续，所以页分裂和页合并都会导致数据的地址改变。

假设有以下几个数据

id  name Course score
1   a    c1    50
2   a    c2    60
3   b    c1    70
4   b    c2    80

如果二级索引叶子节点保存的是指向数据地址的指针？ 我们知道当产生页分裂/页合并现象的时候，对应的数据的位置会发生变化。因此，如果二级索引存储的是位置，那么当数据的位置变化后，需要同步更新二级索引的的位置信息，难度大效率差。如果存的是key的值，永远能找到对应的叶子节点。

如果保存的是主键值 ：此时当我们更新一条数据的时候，索引是怎么改变的？ 首先主键索引肯定变了，因为他保存了所有数据，name列的索引改变吗？不会，因为对他来说索引(name,主键)还在，也可以通过二级索引查找到这条记录，并不需要更改。而当我们插入/删除一条数据， 同时主键索引产生页分裂/页合并现象的时候，name列的索引也就改变一条数据，因为主键索引里的主键没变，其他也就不需要改变，不受数据地址的改变的困扰。

联合索引

即有多个字段的索引，如name+age。

该类索引本质上还是二级索引，在联合索引上按照字段的先后顺序排序。

MySQL索引详解

参考