数据库索引底层数据结构与算法（oraclesqlServermysql）

数据库索引底层数据结构与算法（oraclesqlServermysql）
要了解数据库索引的底层原理，我们就得先了解⼀种叫树的数据结构，⽽树中很经典的⼀种数据结构就是⼆叉树！所以下⾯我们就从⼆叉树到平衡⼆叉树，再到B-树，最后到B+树来⼀步⼀步了解数据库索引底层的原理！
⼆叉树（Binary Search Trees）
⼆叉树是每个结点最多有两个⼦树的树结构。

通常⼦树被称作“左⼦树”（left subtree）和“右⼦树”（right subtree）。

⼆叉树常被⽤于实现⼆叉查找树和⼆叉堆。

⼆叉树有如下特性：
1、每个结点都包含⼀个元素以及n个⼦树，这⾥0≤n≤2。

2、左⼦树和右⼦树是有顺序的，次序不能任意颠倒。

左⼦树的值要⼩于⽗结点，右⼦树的值要⼤于⽗结点。

光看概念有点枯燥，假设我们现在有这样⼀组数[35 28 49 13 30 37 60]，顺序的插⼊到⼀个数的结构中，步骤如下
图1
图2
图3
图4
图3是错误⽰范，请⼤家在分析⼆叉树的时候不要犯这个错误。

好了，这就是⼀棵⼆叉树啦！我们能看到，经通过⼀系列的插⼊操作之后，原本⽆序的⼀组数已经变成⼀个有序的结构了，并且这个树满⾜了上⾯提到的两个⼆叉树的特性！
但是如果同样是上⾯那⼀组数，我们⾃⼰升序排列后再插⼊，会怎么样呢？
由于是升序插⼊，新插⼊的数据总是⽐已存在的结点数据都要⼤，所以每次都会往结点的右边插⼊，最终导致这棵树严重偏科上图就是最坏的情况，也就是⼀棵树退化为⼀个线性链表了，这样查找效率⾃然就低了，完全没有发挥树的优势了呢！为了较⼤发挥⼆叉树的查找效率，让⼆叉树不再偏科，保持各科平衡，所以有了平衡⼆叉树！
平衡⼆叉树 (AVL Trees)
平衡⼆叉树是⼀种特殊的⼆叉树，所以他也满⾜前⾯说到的⼆叉树的两个特性，同时还有⼀个特性：
它的左右两个⼦树的⾼度差的绝对值不超过1，并且左右两个⼦树都是⼀棵平衡⼆叉树。

⼤家也看到了前⾯[35 28 49 13 30 37 60]插⼊完成后的图，其实就已经是⼀颗平衡⼆叉树啦。

那如果按照[35 28 49 13 30 37 60]的顺序插⼊⼀颗平衡⼆叉树，会怎么样呢？我们看看插⼊以及平衡的过程：
整个插⼊的以及平衡的过程就如上图所⽰。

右⼦树与左⼦树的⾼度差绝对值⼤于1就需要调整。

这棵树始终满⾜平衡⼆叉树的⼏个特性⽽保持平衡！这样我们的树也不会退化为线性链表了！我们需要查找⼀个数的时候就能沿着树根⼀直往下找，这样的查找效率和⼆分法查找是⼀样的呢！
⼀颗平衡⼆叉树能容纳多少的结点呢？这跟树的⾼度是有关系的，假设树的⾼度为h，那每⼀层最多容纳的结点数量为2^(n-1)，整棵树最多容纳节点数为2^0+2^1+2^2+...+2^(h-1)。

这样计算，100w数据树的⾼度⼤概在20左右，那也就是说从有着100w条数据的平衡⼆叉树中找⼀个数据，最坏的情况下需要20次查找。

如果是内存操作，效率也是很⾼的！但是我们数据库中的数据基本都是放在磁盘中的，每读取⼀个⼆叉树的结点就是⼀次磁盘IO，这样我们找⼀条数据如果要经过20次磁盘的IO？那性能就成了⼀个很⼤的问题了！那我们是不是可以把这棵树压缩⼀下，让每⼀层能够容纳更多的节点呢？虽然我矮，但是我胖啊...
B-Tree
这颗矮胖的树就是B-Tree，注意中间是杠精的杠⽽不是减，所以也不要读成B减Tree了~
那B-Tree有哪些特性呢？⼀棵m阶的B-Tree有如下特性：
1、每个结点最多m个⼦结点。

2、除了根结点和叶⼦结点外，每个结点最少有m/2（向上取整）个⼦结点。

3、如果根结点不是叶⼦结点，那根结点⾄少包含两个⼦结点。

4、所有的叶⼦结点都位于同⼀层。

5、每个结点都包含k个元素（关键字），这⾥m/2≤k<m，这⾥m 2向下取整。

7、每个元素（关键字）字左结点的值，都⼩于或等于该元素（关键字）。

右结点的值都⼤于或等于该元素（关键字）。

是不是感觉很懵逼！下⾯我们以⼀个[0,1,2,3,4,5,6,7]的数组插⼊⼀颗3阶的B-Tree为例，将所有的条件都串起来，你就明⽩了！
不管裂变前后，所有叶⼦结点都在同⼀层，满⾜第4点特性，并且⼦叶结点的数量也满⾜第2点特性。

在⼆叉树中，每个结点只有⼀个元素。

但是在B-Tree中，每个结点都可能包含多个元素，并且⾮叶⼦结点在元素的左右都有指向⼦结点的指针。

 如果需要在B-Tree查找⼀个元素，那流程是怎么样的呢？我们看下图，如果我们要在下⾯的B-Tree中找到关键字24，那流程如下：
16<24<26所以数据在16和26中间找，然后就找到了24
从这个流程我们能看出，B-Tree的查询效率好像也并不⽐平衡⼆叉树⾼。

但是查询所经过的结点数量要少很多，也就意味着要少很多次的磁盘IO，这对性能的提升是很⼤的。

前⾯对B-Tree操作的图我们能看出来，元素就是类似1、2、3这样的数值，但是数据库的数据都是⼀条条的数据，如果某个数据库以B-Tree的数据结构存储数据，那数据怎么存放的呢？我们看下⼀张图
普通的B-Tree的结点中，元素就是⼀个个的数字。

但是上图中，我们把元素部分拆分成了key-data的形式，key(数字)就是数据的主键，data就是具体的数据。

这样我们在找⼀条数的时候，就沿着根结点往下找就ok了，效率是⽐较⾼的。

B+Tree
B+Tree是在B-Tree基础上的⼀种优化，使其更适合实现外存储索引结构。

B+Tree与B-Tree的结构很像，但是也有⼏个⾃⼰的特性：
1、所有的⾮叶⼦节点只存储关键字信息。

2、所有卫星数据（具体数据）都存在叶⼦结点中。

3、所有的叶⼦结点中包含了全部元素的信息。

4、所有叶⼦节点之间都有⼀个链指针。

如果上⾯B-Tree的图变成B+Tree，那应该如下：
注意红⾊标记框2中的箭头是双向的，懒得改了，声明⼀下。

请注意⼀下的该截图中标记的1、2两处。

b+图1
⼤家仔细对⽐于B-Tree的图能发现什么不同？
1、⾮叶⼦结点上已经只有key信息了，满⾜上⾯第1点特性！
2、所有叶⼦结点下⾯都有⼀个data区域，满⾜上⾯第2点特性！
3、⾮叶⼦结点的数据在叶⼦结点上都能找到，如根结点的元素4、8在最底层的叶⼦结点上也能找到，满⾜上⾯第3点特性！
4、注意图中叶⼦结点之间的箭头，满⾜满⾜上⾯第4点特性！
以上图为例，我们来讲解⼀下这颗B+Tree。

Mysql的底层规定⼀个节点是16kB,在节点的每个元素存储的值是这样的【索引（⽐如数字15）+指向下⼀个⼦结点的指针(磁盘⽂件指针)（15后⾯的空⽩）】，在B+Tree中只在叶⼦节点存储数据，其他⼦结点存储的都是索引以及指针，这样的话在有限的节点⾥⾯，存储的索引就⼤⼤的增加了，树的阶数可以变得更⼩了，在数据量在千万级的情况下，对数据的检索时间将⼤⼤的减少。

下⾯我们来具体的说明⼀下。

假设上图中的索引是8位的整型（8B），我们的指针占的位置为（4B）,那我们⼀个元素所占的内存空间就12B，⼀个节点我们可以放多少索引呢？是（(16KB*1024B/KB)/12B=1365）,⼀个节点可以存放1365个索引（元素）。

假设我们叶⼦结点存储的（data+索引）为1K,那么我们上图中的3阶树可以存放多少数据呢？我们来计算⼀下1365*1365*16=29811600，可以存放接近3千万的数据，是不是觉得存储量很⼤，效率很⾼。

同样的索引来查找，⽤B+tree和B-Tree有啥差别？我们来看⼀下，B-Tree的前⾯讲过了不讲了。

现在我们根据索引来查找30，同样的从根结点开始，15<30<56在根节点的左⼦树上,20<30<49在这之间，继续往下找，然后你就找到了30 下的⼀条条数据。

注意，在查找的时候我们是不是都进⾏了⽐较，B+Tree只是⽐较索引，但是B-Tree是带着数据的索引进⾏⽐较，速度谁更快不⽤讲。

在看B+Tree的时候“b+图1”中的红⾊标记1，⼤家都注意到了20的data在20的右⼦树的叶⼦结点上，这个称之为冗余结点，为什么要这么设计呢，这就引出另外⼀个问题，那就是图中的红⾊标记2，双向箭头。

其实叶⼦结点除了存储指针还存储了前后叶⼦结点的链针，这个链针和冗余结点的好处就是⽅便我们的范围查找，同时检索效率也⼤⼤的提⾼了。

B-Tree or B+Tree？
在讲这两种数据结构在数据库中的选择之前，我们还需要了解的⼀个知识点是操作系统从磁盘读取数据到内存是以磁盘块（block）为基本单位的，位于同⼀个磁盘块中的数据会被⼀次性读取出来，⽽不是需要什么取什么。

即使只需要⼀个字节，磁盘也会从这个位置开始，顺序向后读取⼀定长度的数据放⼊内存。

这样做的理论依据是计算机科学中著名的局部性原理：当⼀个数据被⽤到时，其附近的数据也通常会马上被使⽤。

预读的长度⼀般为页（page）的整倍数。

页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的⼤⼩相等的块，每个存储块称为⼀页（在许多操作系统中，页得⼤⼩通常为4k）。

B-Tree和B+Tree该如何选择呢？都有哪些优劣呢？
1、B-Tree因为⾮叶⼦结点也保存具体数据，所以在查找某个关键字的时候找到即可返回。

⽽B+Tree所有的数据都在叶⼦结点，每次查找都得到叶⼦结点。

所以在同样⾼度的B-Tree和B+Tree中，B-Tree查找某个关键字的效率更⾼。

2、由于B+Tree所有的数据都在叶⼦结点，并且结点之间有指针连接，在找⼤于某个关键字或者⼩于某个关键字的数据的时候，B+Tree 只需要找到该关键字然后沿着链表遍历就可以了，⽽B-Tree还需要遍历该关键字结点的根结点去搜索。

3、由于B-Tree的每个结点（这⾥的结点可以理解为⼀个数据页）都存储主键+实际数据，⽽B+Tree⾮叶⼦结点只存储关键字信息，⽽每个页的⼤⼩有限是有限的，所以同⼀页能存储的B-Tree的数据会⽐B+Tree存储的更少。

这样同样总量的数据，B-Tree的深度会更⼤，增⼤查询时的磁盘I/O次数，进⽽影响查询效率。

鉴于以上的⽐较，所以在常⽤的关系型数据库中，都是选择B+Tree的数据结构来存储数据！下⾯我们以mysql的innodb存储引擎为例讲解，其他类似sqlserver、oracle的原理类似！
延伸拓展：
Hash数据结构存储
在mysql中建⽴索引的时候，可以选择是⽤hash,还是B+Tree.Hash是这样的，当你存⼊索引的时候会通过hash算法⽣成⼀个hashCode，在存⼊内存的时候就将你的这个hashCode和地址指针配对存⼊。

这样做的好处就是当你在查询的时候，能够快速的定位，根本不需要去⽐较查找，只要根据这个值通过hash算法就可以找到你要的数据了。

但是这种结构在实际应⽤中基本上是很少⽤的（⾄少我是没遇到过），为什么呢？因为在实际的应⽤中我们的sql语句查询⽤的⽐较多的是范围查找⽽不是精准定位。

综上所述，B+Tree成为了mysql,oracle等数据库的底层数据结构的不⼆之选。

索引的底层数据结构和算法
聚集索引&⾮聚集索引
知道数据库基本的数据结构以后，下⾯我们来了解索引的底层数据结构和算法，⽤mySql为例。

MySql 有两种索引引擎：MyISAM索引实现（⾮聚集），innodb索引实现（聚集索引）；存储引擎是形容数据的表的。

MyISAM索引实现（⾮聚集）
这种索引下⾯会有三个⽂档，分别是：
MyISAM存储引擎是形容数据的表的:
xxx_MyISAM.frm :存储表定义结构
xxx_MyISAM.myd ：存储表数据
xxx_MyISAM.myi ：存储表的索引
从以上三个⽂件结合B+Tree的知识，⼤家就会很清楚的知道，MyISAM索引将表结构、索引、数据是分开在三个⽂件中进⾏存储的。

以下图为例，我们来进⾏讲解：
从上图中红⾊⽅框标记可以看到MyISAM的叶⼦结点中除了索引以外，存储的的不是data⽽是地址指针，在找数据的时候是通过地址指针去寻找的数据。

除此之外索引的也是分开存储的（红⾊圆形标记），所以说MyISAM是⾮聚集的。

⾮聚集索引的存储结构与前⾯是⼀样的，不同的是在叶⼦结点的数据部分存的不再是具体的数据，⽽数据的聚集索引的key。

所以通过⾮聚集索引查找的过程是先找到该索引key对应的聚集索引的key，然后再拿聚集索引的key到主键索引树上查找对应的数据，这个过程称为回表！
innodb引擎数据存储（聚集）
　Innodb引擎下⾯有以下两种⽂件：
xxx.frm: 存储表定义结构
xxx.ibd:存储表数据和表的索引
它的聚集结构我们来看⼀下下图：
图innoDB-1
从上图中我们可以看到InnoDB的存储跟那两个⽂件是保持⼀致的，它的叶⼦结点存储的就是索引+完整的数据。

如图图innoDB-1所⽰，如果我们插⼊⼀个28，那么这个数应该插⼊在20与30之间，这样的话相应的（20,30）前后指针就需要变化，如果我们再插⼊⼀个29的话我们的B+TREE就需要分裂，⼀旦分裂的话，整颗树就要重构，因为指针地址变更。

数据量⼤的时候树的重构是⾮常耗时的，所以innodb要求主键⾃增！
在InnoDB存储引擎中，也有页的概念，默认每个页的⼤⼩为16K，也就是每次读取数据时都是读取4*4k的⼤⼩！假设我们现在有⼀个⽤户表，我们往⾥⾯写数据。

这⾥需要注意的⼀点是，在某个页内插⼊新⾏时，为了不减少数据的移动，通常是插⼊到当前⾏的后⾯或者是已删除⾏留下来的空间，所以在某⼀个页内的数据并不是完全有序的，但是为了数据访问顺序性，在每个记录中都有⼀个指向下⼀条记录的指针，以此构成了⼀条单向有序链表。

这棵树的⾮叶⼦结点上存的都是主键，那如果⼀个表没有主键会怎么样？在innodb中，如果⼀个表没有主键，那默认会找建了唯⼀索引的列，如果也没有，则会⽣成⼀个隐形的字段作为主键！
有数据插⼊那就有删除，如果这个⽤户表频繁的插⼊和删除，那会导致数据页产⽣碎⽚，页的空间利⽤率低，还会导致树变的“虚⾼”，降低查询效率！这可以通过索引重建来消除碎⽚提⾼查询效率！
主键⾃增写⼊时新插⼊的数据不会影响到原有页，插⼊效率⾼！且页的利⽤率⾼！但是如果主键是⽆序的或者随机的，那每次的插⼊可能会导致原有页频繁的分裂，影响插⼊效率！降低页的利⽤率！这也是为什么在innodb中建议设置主键⾃增的原因！
innodb引擎数据查找
数据插⼊了怎么查找呢？
1、找到数据所在的页。

这个查找过程就跟前⾯说到的B+Tree的搜索过程是⼀样的，从根结点开始查找⼀直到叶⼦结点。

2、在页内找具体的数据。

读取第1步找到的叶⼦结点数据到内存中，然后通过分块查找的⽅法找到具体的数据。

 这跟我们在新华字典中找某个汉字是⼀样的，先通过字典的索引定位到该汉字拼⾳所在的页，然后到指定的页找到具体的汉字。

innodb中定位到页后⽤了哪种策略快速查找某个主键呢？这我们就需要从页结构开始了解。

左边蓝⾊区域称为Page Directory，这块区域由多个slot组成，是⼀个稀疏索引结构，即⼀个槽中可能属于多个记录，最少属于4条记录，最多属于8条记录。

槽内的数据是有序存放的，所以当我们寻找⼀条数据的时候可以先在槽中通过⼆分法查找到⼀个⼤致的位置。

右边区域为数据区域，每⼀个数据页中都包含多条⾏数据。

注意看图中最上⾯和最下⾯的两条特殊的⾏记录Infimum和Supremum，这是两个虚拟的⾏记录。

在没有其他⽤户数据的时候Infimum的下⼀条记录的指针指向Supremum，当有⽤户数据的时候，Infimum的下⼀条记录的指针指向当前页中最⼩的⽤户记录，当前页中最⼤的⽤户记录的下⼀条记录的指针指向Supremum，⾄此整个页内的所有⾏记录形成⼀个单向链表。

⾏记录被Page Directory逻辑的分成了多个块，块与块之间是有序的，也就是说“4”这个槽指向的数据块内最⼤的⾏记录的主键都要
⽐“8”这个槽指向的数据块内最⼩的⾏记录的主键要⼩。

但是块内部的⾏记录不⼀定有序。

每个⾏记录的都有⼀个nowned的区域（图中粉红⾊区域），nowned标识这个这个块有多少条数据，伪记录Infimum的nowned值总是1，记录Supremum的nowned的取值范围为[1,8]，其他⽤户记录nowned的取值范围[4,8]，并且只有每个块中最⼤的那条记录的nowned才会有值，其他的⽤户记录的n_owned为0。

所以当我们要找主键为6的记录时，先通过⼆分法在稀疏索引中找到对应的槽，也就是Page Directory中“8”这个槽，“8”这个槽指向的是该数据块中最⼤的记录，⽽数据是单向链表结构所以⽆法逆向查找，所以需要找到上⼀个槽即“4”这个槽，然后通过“4”这个槽中最⼤的⽤户记录的指针沿着链表顺序查找到⽬标记录。

InnoDB的数据引擎同时要求主键尽量是整型，为什么？我们下⾯来分析⼀下
假设我们的主键是英⽂字符串，那么innodb数据引擎在进⾏b+Tree的构建的时候就会将英⽂字母翻译成数字，⽐如as，（假如：
a=65，s=83）那么innodb会将其翻译成6583,6583就是主键，中间的⼀次翻译就会导致效率的降低。

所以InnoDB数据引擎要求主键尽量是整型。

当存在多个索引的时候具体的聚集结构是什么样的呢，如下图所⽰：
当你⼜多个索引的时候就将你的索引排序就可以了。

innodb与MyISAM两种存储引擎对⽐
那MyISAM与innodb在存储上有啥不同呢，根据以上的图我们能看到的不同是
1、MyISAM主键索引树的叶⼦结点的数据区域没有存放实际的数据，存放的是数据记录的地址。

2、MyISAM数据的存储不是按主键顺序存放的，按写⼊的顺序存放。

也就是说innodb引擎数据在物理上是按主键顺序存放，⽽MyISAM引擎数据在物理上按插⼊的顺序存放。

并且MyISAM的叶⼦结点不存放数据，所以⾮聚集索引的存储结构与聚集索引类似，在使⽤⾮聚集索引查找数据的时候通过⾮聚集索引树就能直接找到数据的地址了，不需要回表，这⽐innodb的搜索效率会更⾼呢！
以上是个⼈的理解，仅供参考，望与各位码农共勉，不喜勿喷，谢谢。