关系型数据库索引，二叉树、平衡二叉树、多路平衡树B-Tree、B+Tree，聚簇索引、非聚簇索引，聚集索引、非聚集索引

一、索引是什么？

       索引是为了加速对表数据行的检索而创建的分散存储的数据结构。

      索引的工作机制 大概如下图：

如上图可知，索引是一种数据结构，它保存了对应数据表中列的地址或数据（根据不同数据表引擎而定）

那么问题来了，用索引就是要加快数据的查询，单靠映射是没有意义的，需要一套能快速查找的方案，而索引的算法需要依次从如下几个快速查找算法来理解：

1.二叉树查找 binary search tree

2.平衡二叉树查找 balanced binary search tree

3.多路平衡查找树 B-Tree

4.加强版多路平衡查找树（绝对平衡查找树）B+Tree

 

二、二叉树

二叉树结构图如下：

比如我们要查找9，那么从根节点10开始查，9<10 所以向左边子节点继续找，与子节点5对比，9>5 向右边子节点继续找，9>7 向右子节点继续找，9=9 命中查找结果。

如果添加一个数则也是每层对比，对比小于当前节点值的向左字节点，大于当前节点值的向右，直到末尾，比如上图增加一个20的数据（用此网站可以模拟：https://www.cs.usfca.edu/~galles/visualization/BST.html）

通过如上图可以知道，二叉树上添加数据，如果再添加21、22、23..... 那么树的层级会越来越高，我们知道数据是存储在硬盘里面的，每一层的数据对比，需要从硬盘中取出数据进行对比，那也就是没达到一层都进行了一次I/O操作，所以为了使树的分支左右平衡（即左右分支层次相对平衡），减少查找时的I/O操作次数，所以需要改良二叉树的分支规则，那么平衡二叉树就能更好的减少I/O操作次数。

三、平衡二叉树

数据结构如下图：

每一个节点 分三部分：

1.关键字（要查找的关键字）

2.数据区（要查找的数据的存储地址或数据）

3.子节点引用（指向子节点的指针）

 

数结构的平衡是相对平衡的，即叶子节点不一定都在同一个层。

在数据结构中加 16、17、18 的变化

  

从如上图增加 3个数可知道，为了保持树的结构左右相对平衡，尽量减少树的层，添加数的时候，要保证末端的叶子节点层数，不能超过最小层2个层次，一旦超过2个层次将对分支结构进行重构来保证结构的相对平衡。

当数据比较少的时候，平衡二叉树的层次不会太高，当数据量越来越多的时候，层数也会越来越高，查找数据的时候I/O操作同样也会越来频繁（数据所在的层次决定了I/O操作的次数），这种算法依旧没有很好的利用操作系统和磁盘的数据交换特性，那么我们就需要在分支数上进行加强，多路平衡查找树就能使每个节点能产生更多的分支，大大降低了树的层数，同时每个节点能检索的数据比平衡二叉树要多。

 

三、多路平衡查找树（B-Tree）

数据结构图如下：

注意：这里的平衡是绝对平衡，即保证所有的叶子节点都在树的同一层，如新增的数据不能保证绝对平衡，则对树进行重构。

B-Tree （2-3）的每个节点中包含：关键字（最多2个）、对应关键字的数据存储区、子节点的引用（3个）

数据查找的规则（根据上图数据）： 

X < 17 ->P1  所查找数据小于17 去P1指向的子节点继续查找

17 < X < 35 ->P2 所查找的数据大于17 且小于35 则去P2所指向的子节点继续查找

X > 35 ->P3 所查找的数据大于35 则去P3所指向的字节点继续查找

X = 17 命中，当查找的数据等于当前的节点的值，那么就命中了，结束查找。

 

B-Tree （2-3）增加数据的时候，需要保证树的结构绝对平衡，如下是添加过程的示例：

 

根据如上的模拟，为了保证树的绝对平衡，叶子节点都在同一层，添加数据的时候都会按照规则进行调整。（具体的重构算法没必要深究，理解B-Tree （2-3）结构规则即可）

规则（如下的m即 分支数）

• 每个节点最多只有m个子节点。
• 每个非叶子节点（除了根）具有至少⌈ m/2⌉子节点。（⌈ m/2⌉ 向上取整 3/2=2）
• 如果根不是叶节点，则根至少有两个子节点。
• 具有k个子节点的非叶节点包含k -1个键。
• 所有叶子都出现在同一水平，没有任何信息（高度一致）。

B-Tree算法对数据查找效率已经非常好了，为什么mysql索引算法没有用B-Tree算法而是用了B+Tree？ 当然是B+Tree的综合查询效率更加优秀。

 

四、B+Tree（加强版多路平衡查找树）

数据结构图如下：

与B-Tree对比：

1.B+Tree除了叶子节点，每个字节点没有数据区，只有 关键字 和 子节点引用

2.每个子节点（包括叶子节点）都包含了父节点所属的关键字

3.所有叶子节按升序排列在同一层，且叶子节点存储了关键字对应表的行数据（或行的地址），即每次查找都会到最终的叶子层拿到目标数据。

.B+Tree的查找规则（如上图）查找1

1 <= x < 28 ->p1 （根节点） 查找的1等于1 则去p1的字节点继续查找

1 <= x < 10 ->p1 （子节点-非叶子节点） 查找的1等于1 则去p1的字节点继续查找

1=x （叶子节点） 命中，找到叶子节点中对应的数据或数据地址，查找结束。

28 <= x < 66 ->p2  如果查找的值 大于等于 28  小于66 则去p2子节点继续查找，直到叶子节点找到数据

66 <= x ->p3  如果查找的值大于当前节点最大值，那么去p3子节点继续查找 ，直到叶子节点

注意：从如上的规则中我们可以 明确直到，各节点（根节点、子节点）都是左闭合的，即树的左侧的数据分支判断范围在 节点值的最小值和中间值之间， 而右侧是开放的，大于当前节点最大 值就往右边节点走。

 

B-Tree与B+Tree的区别

1. B树每个节点都存储数据，所有节点组成这棵树。B+树只有叶子节点存储数据（B+数中有两个头指针：一个指向根节点，另一个指向关键字最小的叶节点），叶子节点包含了这棵树的所有数据，所有的叶子结点使用链表相连，便于区间查找和遍历，所有非叶节点起到索引作用。

2. B树中叶节点包含的关键字和其他节点包含的关键字是不重复的，B+树的索引项只包含对应子树的最大关键字和指向该子树的指针，不含有该关键字对应记录的存储地址。

3. B树中每个节点（非根节点）关键字个数的范围为[m/2(向上取整)-1,m-1](根节点为[1,m-1])，并且具有n个关键字的节点包含（n+1）棵子树。B+树中每个节点（非根节点）关键字个数的范围为[m/2(向上取整),m](根节点为[1,m])，具有n个关键字的节点包含（n）棵子树。

4. B+树中查找，无论查找是否成功，每次都是一条从根节点到叶节点的路径。

关系型数据库，数据的查找中范围查找是非常多的，根据B-Tree与B+Tree的区别可以知道，B+Tree在遍历数据时更具有优势。所以mysql索引算法最终选择了B+Tree

 

五、B+Tree结构 具体应用于mysql索引的形式

因为mysql数据库 表的搜索引擎不同（InnoDB与Myisam）,所体现的形式有所不同。

1）Myisam索引B+Tree算法的体现形式如下图：

在myisam中，索引和具体数据是分开文件存储的，树的叶子节点存储了对应数据行的地址（指针），最终数据需要拿着叶子节点匹配到的地址去获取。

当一个表中有多个索引时，多个索引都放置在同一个MYI索引文件中。

 

2）InnoDB索引B+Tree算法的体现形式如下图：

索引结构与数据是在同一个文件当中的（IBD），叶子节点直接存储了数据行的数据。

InnoDB引擎的表必须有主键索引（创建表的时候指定主键就会创建）

在InnoDB引擎中，创建的辅助索引，它的叶子节点所存储的值是主键的值，如果触发辅助索引，需先根据当前索引找到目标数据的主键值，然后再在主键索引中找到目标行数据，实际上是进行了两次索引查找。 

 

InnoDB与Myisam的对比：

为什么InnoDB直接将数据存储在主键索引的叶子节点上？ 因为可以直接拿到查找的数据

为什么InnoDB辅助索引叶子节点存储的是主键值，而不是直接存储要查找的数据行？ 因为在数据表中更改了非主键列的数据，数据库不用去维护辅助索引的结构，只需要去修改主键索引就行。

所以，InnoDB引擎的写性能要比Myisam强，而Myisam的读性能比Innodb强。

 

六、索引的几大原则

1.列的离散性（如下图）：

所谓的离散型就是，当前列的数据 唯一的数据越多越好，如上图所示 sex列 重复的数据占一半，name列没有重复的数据，那么name的离散性就要比sex列的好。

离散性越高，选择性就越好。离散性越高则查找的目标数据越明确，范围越小。

 

2.最左匹配原则（如下图）：

之前的结构图都是用数字来进行对比的（因为数字更简单直接），而数据库的索引实际上是通过字符序列来对比排序的，而字符是通过编码而来的， 比如ASCII码；

不恰当的比喻：

ABC  = 91 92 93          ACD = 91 93 94    通过从左往右对比最终的字码，ABC < ABD  

 

七、联合索引

即多列组合成一个索引

 

 

覆盖索引

 

 

八、聚簇索引与非聚簇索引（是从数据存储上来进行区分的）

聚簇索引：对磁盘上的数据物理位置重新组织以按照特定的一个或者多个列的值排序的算法进行排列，且数据行与叶子节点存储在一起，搜索到叶子节点也就能直接拿到数据。一个表只能有一个聚族索引。InnoDB主键索引就是聚簇索引。

非聚簇索引：叶子节点的值有指针指向数据行存储的位置，数据行没有存储在叶子节点，最终数据需要根据叶子节点的指针去数据文件上获取。myisam的主键索引和辅助索引都是非聚簇索引。

 

九、聚集索引与非聚集索引（叶子层排序方式 与数据的物理排序 进行区分）

1、聚集索引

聚集索引：指索引项的排序方式和表中数据记录排序方式一致的索引 

也就是说聚集索引的顺序就是数据的物理存储顺序。它会根据聚集索引键的顺序来存储表中的数据，即对表的数据按索引键的顺序进行排序，然后重新存储到磁盘上。因为数据在物理存放时只能有一种排列方式，所以一个表只能有一个聚集索引。

聚集索引会降低 insert，和update操作的性能，所以，是否使用聚集索引要全面衡量。

2、非聚集索引： 索引顺序与物理存储顺序不同

 

 

本文通过如下重新整合：

https://v.qq.com/x/page/k0849vd8k0h.html       索引算法的演变 

https://blog.csdn.net/guzhangyu12345/article/details/96423704  索引分类