数据库索引 ( 四 ) 原理及概念

春哥的魔法书

已于 2022-10-25 21:55:00 修改

阅读量663

点赞数 1

分类专栏： SQL与数据库文章标签：数据库 B+Tree 索引

于 2022-10-17 20:19:52 首次发布

本文链接：https://blog.csdn.net/yuanchun05/article/details/127374637

版权

SQL与数据库专栏收录该内容

27 篇文章 5 订阅

订阅专栏

3.索引原理

MySQL官方对索引定义：存储引擎用于快速查找记录的一种数据结构，需要额外开辟空间和数据维护工作。

索引是物理数据页存储，在数据文件中，利用数据页（page）存储
索引可以加快索引速度，但是同时也会降低增删改操作速度，索引维护需要代价。

3.1.二分查找法

二分查找法也叫做折半查找法，它是在有序数组中查找指定数据的搜索算法。他的优点是等值查询、范围查询性能好，缺点是更新数据、新增数据、删除数据维护成本高。

查找步骤：

首先定位left和right两个指针
计算(left+right)/2，除不尽向下取余。并记为m
判断除2后索引位置值与目标值的大小比对
索引位置值大于目标值，right移动（right=m-1）；如果小于目标值，left移动(left=m+1)

举例：下面的有序数组有17个值，查找的目标值是7

1	3	4	6	7	8	10	13	14	18	19	25	31	32	45	46	67
L0/L1			A(m2)	L2			R1/R2	A(m1)								R0

初始 :

L0 = 0 , R0 = 16

第 1 次计算 :

m1 = floor[ (L0 + R0 )/2 ] = floor[ (0 + 16 )/2 ] = 8

A(m1) = 14 > Target = 7

L1 = 0 , R1 = m1 - 1 = 7

第 2 次计算 :

m2 = floor[ (L1 + R1 )/2 ] = floor[ (0 + 7 )/2 ] = 3

A(m2) = 6 < Target = 7

L2 = m2 + 1 = 4 , R2 = 7

第 … 次计算 :

…

依次类推，直到索引位置值 = 查找的目标值

3.2.Hash索引

Hash是k,v形式，通过一个散列函数，能够根据key快速找到value

哈希索引就是采用一定的Hash算法，把键值换成新的哈希值，检索时不需要类似B+Tree那样从根节点到叶子节点逐级查找，只需要一次Hash算法即可立即定位到相应的位置，速度非常快。

缺点: 因为底层数据结构是散列的，无法进行比较大小，不能进行范围查找

MySQL 中 Memory引擎 两种引擎支持 Hash索引。

在这里插入图片描述

3.2.1.什么是 Hash 冲突？

哈希表其实就是一个存放哈希值的一个数组，哈希值是通过哈希函数计算出来的，那么哈希冲突就是两个不同值的东西，通过哈希函数计算出来的哈希值相同，这样他们存在数组中的时候就会发生冲突，这就是哈希冲突。

如果桶的空间小于输入的空间，不同的输入可能会映射到同一个桶中，这时就会产生 Hash 冲突，如果 Hash 冲突的量很大，就会影响读取的性能。通常 Hash 值的字节数比较少，简单的 4 个字节就够了。在 Hash 值相同的情况下，就会进一步比较桶（Bucket）中的键值，从而找到最终的数据行。Hash 值的字节数多的话可以是 16 位、32 位等，比如采用 MD5 函数就可以得到一个 16 位或者 32 位的数值，32 位的 MD5 已经足够安全，重复率非常低。

3.2.2.如何解决 Hash 冲突？

解决Hash 冲突（哈希冲突）有以下四种方法：

链地址法
再哈希法
建立公共溢出区
开放地址法

3.2.2.1.链地址法

对于相同的哈希值，使用链表进行连接。（HashMap使用此法）

优点:

处理冲突简单，无堆积现象。即非同义词决不会发生冲突，因此平均查找长度较短；适合总数经常变化的情况。（因为拉链法中各链表上的结点空间是动态申请的）占空间小。装填因子可取α≥1，且结点较大时，拉链法中增加的指针域可忽略不计删除结点的操作易于实现。只要简单地删去链表上相应的结点即可。

缺点:

查询时效率较低。（存储是动态的，查询时跳转需要更多的时间）在key-value可以预知，以及没有后续增改操作时候，开放地址法性能优于链地址法。不容易序列化。

3.2.2.2.再哈希法

提供多个哈希函数，如果第一个哈希函数计算出来的key的哈希值冲突了，则使用第二个哈希函数计算key的哈希值。

优点:

不易产生聚集

缺点:

增加了计算时间

3.2.2.3.建立公共溢出区

将哈希表分为基本表和溢出表两部分，凡是和基本表发生冲突的元素，一律填入溢出表。

3.2.2.4.开放地址法

当关键字key的哈希地址p =H（key）出现冲突时，以p为基础，产生另一个哈希地址p1，若p1仍然冲突，再以p为基础，产生另一个哈希地址p2，…，直到找出一个不冲突的哈希地址pi ，将相应元素存入其中。

即：Hi=（H（key）+di）% m （i=1，2，…，n）

开放地址法有下边三种方式：

1.线性探测再散列 : 顺序查看下一个单元，直到找出一个空单元或查遍全表
di=1，2，3，…，m-1

2.二次(平方)探测再散列 : 在表的左右进行跳跃式探测，直到找出一个空单元或查遍全表
di=12，-12，22，-22，…，k2，-k2 ( k<=m/2 )

3.伪随机探测再散列 : 建立一个伪随机数发生器，并给一个随机数作为起点
di=伪随机数序列。具体实现时，应建立一个伪随机数发生器，（如i=(i+p) % m），并给定一个随机数做起点。

优点:

容易序列化
若可预知数据总数，可以创建完美哈希数列

缺点:

占空间很大。（开放地址法为减少冲突，要求装填因子α较小，故当结点规模较大时会浪费很多空间）删除节点很麻烦。不能简单地将被删结点的空间置为空，否则将截断在它之后填人散列表的同义词结点的查找路径。这是因为各种开放地址法中，空地址单元(即开放地址)都是查找失败的条件。因此在用开放地址法处理冲突的散列表上执行删除操作，只能在被删结点上做删除标记，而不能真正删除结点。

3.3.B+Tree结构

MySQL的索引底层实现，其底层是通过B+Tree来实现的数据结构存储。

3.3.1.B Tree

B Tree是一种多路自平衡的搜索树，它类似普通的平衡二叉树(AVL树)，不同的一点是B Tree 允许每个节点有更多的子节点。
注：B-Tree就是我们常说的B Tree
那么m阶B Tree是满足下列条件的数据结构：
所有键值分布在整棵树中
搜索有可能在非叶子节点结束，在关键字全集内做一次查找，性能逼近二分查找
每个节点最多拥有m个子树
根节点至少有2个子树
分支节点至少拥有m/2颗子树（除根节点和叶子节点外都是分支节点）
所有叶子节点都在同一层，每个节点最多可以有m-1个key，并且以升序排列

在这里插入图片描述

每个节点占用一个磁盘块，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为22和45，P1指针指向的子树的数据范围小于22，P2指针指向的子树的数据范围为22~45，P3指针指向的子树的数据范围大于45.

模拟查找关键字26的过程：
1、根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
2、比较关键字26在区间（22,45），找到磁盘块1的指针P2。
3、根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
4、比较关键字26在区间（23,27），找到磁盘块3的指针P1。
5、根据P1指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
6、在磁盘块8中的关键字列表中找到关键字26。
7、分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B Tree查找效率的决定因素。

但同时B Tree也存在问题：
每个节点中有key，也有data，而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小。
当存储的数据量很大时同样会导致B Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率

3.3.2.B+Tree

B+Tree是在B-Tree基础上的一种优化，InnoDB存储引擎就是用B+Tree实现其索引结构。它带来的变化点：
B+Tree每个节点可以包含更多的节点，这样做有两个原因，一个是降低树的高度。另外一个是将数据范围变为多个区间，区间越多，数据检索越快
非叶子节点存储key，叶子节点存储key和数据
叶子节点两两指针相互链接（符合磁盘的预读特性），顺序查询性能更高

注：MySQL的InnoDB存储引擎在设计时是将根节点常驻内存，因此力求达到树的深度不超过3，也就是说I/O不需要超过3次。

在这里插入图片描述

这个过程看下来，貌似与B Tree 的查询过程没有什么区别。但实际上有两点不同：

a、首先B+Tree的中间节点不存储数据，因此一样大小的磁盘页能够容纳更多的节点元素，如此一来，相同数量的数据下，B+Tree就相对来讲要更加矮胖些，磁盘IO的次数更少。

b、因为只有叶子节点才保存数据，B+Tree每次查询都要到叶子节点；而B Tree每次查询则不同，最好的状况是根节点，最坏的状况是叶子节点，没有B+Tree 稳定。

c、通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构，因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找的分页查找，另一种是从根节点开始，进行随机查找。

3.3.3.B Tree和B+Tree的区别

在 InnoDB中默认定义的B+tree的节点大小是16kb

B+Tree内节点不存储数据，所有数据存储在叶节点导致查询时间复杂度固定为log n
B Tree查询时间复杂度不固定，与Key在树中的位置有关，最好为O(1)
B+Tree叶节点两两相连可大大增加区间访问性，可使用在范围查询等
B+Tree更适合外部存储（存储磁盘数据）。由于内节点无data域，每个节点能索引的范围更大更精确。

3.3.4.Hash 索引与 B+ 树索引的区别

Hash 索引不能进行范围查询，而 B+Tree可以。这是因为 Hash 索引指向的数据是无序的，而 B+ Tree的叶子节点是个有序的链表。
Hash 索引不支持联合索引的最左侧原则（即联合索引的部分索引无法使用），而 B+ Tree可以。对于联合索引来说，Hash 索引在计算 Hash 值的时候是将索引键合并后再一起计算 Hash 值，所以不会针对每个索引单独计算 Hash 值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。
Hash 索引不支持 ORDER BY 排序，因为 Hash 索引指向的数据是无序的，因此无法起到排序优化的作用，而 B+Tree索引数据是有序的，可以起到对该字段 ORDER BY 排序优化的作用。同理，我们也无法用 Hash 索引进行模糊查询，而 B+ Tree使用 LIKE 进行模糊查询的时候，LIKE 后面前模糊查询（比如 % 开头）的话就可以起到优化作用。

我们也无法用 Hash 索引进行模糊查询，而 B+ Tree使用 LIKE 进行模糊查询的时候，LIKE 后面前模糊查询（比如 % 开头）的话就可以起到优化作用。

3.4.聚集索引(聚簇索引) 与辅助索引

数据库中的B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。