【MySQL精通系列之四】【索引结构入门】

最新推荐文章于 2024-08-15 23:39:58 发布

技术自由之路

最新推荐文章于 2024-08-15 23:39:58 发布

阅读量94

点赞数

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_36165763/article/details/127606160

版权

数据库专栏收录该内容

5 篇文章 1 订阅

订阅专栏

常见的索引结构

MySQL数据库中的常见索引结构有多种，常用Hash、B-树、B+树等数据结构来进行数据存储。树的深度加深一层，意味着多一次查询，对于数据库磁盘而言，就是多一次IO操作，导致查询效率低下。

前置：二叉搜索树

了解下二叉搜索树有助于我们理解B-树、B+树，二叉搜索树的特点是：

所有非叶子结点至多拥有两个儿子（Left和Right）；
所有结点存储一个关键字；
非叶子结点的左指针指向小于其关键字的子树，右指针指向大于其关键字的子树；

以下都是二叉搜索树：

如果要找到65，左边的二叉树需要扫描3层（3次IO），而右边的却需要6层。

B-Tree(B树)

B-tree树即B树，B即Balanced，平衡的意思。因为B树的原英文名称为B-tree，而国内很多人喜欢把B-tree译作B-树，其实，这是个非常不好的直译，很容易让人产生误解。事实上，B-tree就是指的B树。

B树是一种多路搜索树，一棵m阶的B树满足下列条件：

树中每个结点至多有m个孩子
根结点的儿子数为[2, M]；
除根结点以外的非叶子结点的儿子数为[M/2, M]；
每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
非叶子结点的关键字个数 = 指向子节点的指针个数-1；
非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；
所有叶子结点位于同一层；

以下是3阶B树

以下结合磁盘作图

B树的特征：

关键字集合分布在整颗树中；
任何一个关键字出现且只出现在一个结点中；
搜索有可能在非叶子结点结束；
其搜索性能等价于在关键字全集内做一次二分查找；
自动层次控制；

B树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；

B+Tree

B+树是B-树的变体，也是一种多路搜索树：（❀ 表示两者间的不同点）

树中每个结点至多有m个孩子；
根结点的儿子数为[2, M]；
除根结点以外的非叶子结点的儿子数为[M/2, M]；
每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
❀ 非叶子结点的子树指针与关键字个数相同；
❀ 非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树；（B树是开区间）；
❀ 为所有叶子结点增加一个链指针；
❀ 所有关键字都在叶子结点出现；

B+树的特征：

所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
不可能在非叶子结点命中；
非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。
更适合文件索引系统；

B+树的搜索与B-树也基本相同，区别是B+树只有达到叶子结点才命中（B-树可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找。

为什么 B+ 树比 B 树更适合作为索引？

1.B+ 树的磁盘读写代价更低
        B+ 树的数据都集中在叶子节点，分支节点只负责指针（索引）；B 树的分支节点既有指针也有数据。这将导致B+ 树的层高会小于B 树的层高，也就是说B+ 树平均的Io次数会小于B 树。
2.B+ 树的查询效率更加稳定
        B+ 树的数据都存放在叶子节点，故任何关键字的查找必须走一条从根节点到叶子节点的路径。所有关键字的查询路径相同，每个数据查询效率相当。
3.B+树更便于遍历
        由于B+树的数据都存储在叶子结点中，分支结点均为索引，遍历只需要扫描一遍叶子节点即可；B树因为其分支结点同样存储着数据，要找到具体的数据，需要进行一次中序遍历按序来搜索。
4.B+树更擅长范围查询
        B+树叶子节点存放数据，数据是按顺序放置的双向链表。B树范围查询只能中序遍历。
5.B+ 树占用内存空间小
        B+ 树索引节点没有数据，比较小。在内存有限的情况下，相比于B树索引可以加载更多B+ 树索引。

Hash

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

Hash索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。也不支持任何范围查询，例如WHERE price > 100。
　　由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样。

从上面的图来看，B+树索引和哈希索引的明显区别是：

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；这有个前提，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；
如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；
哈希索引也不支持多列联合索引的最左匹配规则；
B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题

InnoDB B+ Tree 结构来存储索引

InnoDB使用B+Tree数据结构存储索引，根据索引物理结构可将索引划分为聚簇索引和非聚簇索引（也可称辅助索引或二级索引）。一个表中只能存在一个聚簇索引（主键索引），但可以存在多个非聚簇索引。

B+树叶子节点包含数据表中行记录就是聚簇索引（索引和数据是一块的）。

B+树叶子节点没包含数据表中行记录就是非聚簇索引（索引和数据是分开的）。

B+树可以存储多少行数据

InnoDB存储引擎也有自己的最小储存单元——页（Page），一个页的大小默认是16K。

mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+

磁盘扇区、文件系统、InnoDB存储引擎都有各自的最小存储单元。

数据表中的数据都是存储在页中的，所以一个页中能存储多少行数据呢？假设一行数据的大小是1k，那么一个页可以存放16行这样的数据。

如果数据库只按这样的方式存储，那么如何查找数据就成为一个问题？
因为我们不知道要查找的数据存在哪个页中，也不可能把所有的页遍历一遍，那样太慢了。

于是人们想到了用B+ 树的方式组织这些数据，下图以InnoDB为例。

pointer往往是6个字节，指明对应key值的页面位置信息。key一般为索引主键，如果为单字段 bigint 类型，则为8字节。如此可计算一个页大概可以存放16 * 1024/（6+8）=1170行数据。假设一行数据1k，那么2层B+ 树（第一层索引，第二层叶子节点存数据）就可以存储1170 * 16 = 18 720行；三层则可以存储1170 * 1170 * 16=21902400行。