第 6 章 快速查询的秘籍——B+树索引

沉登c
53 阅读8分钟

6.1 没有索引时进行查找

6.1.1 在一个页中查找

  • 以主键为搜索条件。在页目录中使用二分法快速定位到对应的槽，然后再遍历槽分组中的记录。
  • 以其他列为搜索条件。从最小记录依次遍历，效率很低。

6.1.2 在很多页中查找

分为两步

  1. 定位到记录所在的页
  2. 从所在的页中查找相应的记录

6.2 索引

建表：

CREATE TABLE index_demo(
	c1 INT,
	c2 INT,
	c3 CHAR(1),
	PRIMARY KEY(c1)
) ROW_FORMAT = Compact;

在这里插入图片描述

  • record_type：记录类型，0-普通记录，2-最小记录，3-最大记录，1-稍后
  • next_record：下一条地址相对于本条记录的地址偏移量
  • 各个列的值：index_demo表的三个列
  • 其他信息：除了上述3种信息以外所有信息 在这里插入图片描述

6.2.1 一个简单的索引方案

INSERT INTO index_demo VALUES(1, 4, 'u'), (3, 9, 'd'), (5, 3, 'y');

在这里插入图片描述

要求：下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值

INSERT INTO index_demo VALUES(4, 4, 'a');

在这里插入图片描述 由于新插入的列主键为5，违反了上述要求，所以需要进行页分裂：即把5移动到页28，4插入到页10。 在这里插入图片描述

插入许多条记录后： 在这里插入图片描述

建立一个目录，每个页对应一个目录项：

  1. 页的用户记录中最小的主键值，用key表示
  2. 页号用page_no表示

在这里插入图片描述

比如我们想找主键值为20的记录：

  1. 先使用二分法从目录项中定位20的记录在目录项3中，它对应的页是9
  2. 再根据前面在页中查找记录的方法定位具体记录

这个针对数据页的目前，就是索引。

6.2.2 InnoDB中的索引方案

把目录项也使用页来管理，并通过record_type=1来标记。 在这里插入图片描述

目录项记录和用户记录的不同点：

  1. 目录项记录的record_type=1，而普通用户记录的record_type=0
  2. 目录项记录只有主键值和页的编码两列，普通用户记录列由用户定义
  3. 目录项记录的min_rec_mask（B+树的每层非叶子节点中的最小记录都会添加该标记） =1，其他记录的min_rec_mask =0

有了目录页之后，查找主键为20的记录步骤就是：

  1. 先在存储目录项的页（页30）中通过二分法快速定位到对应的目录项，也就是页9
  2. 再在存储用户记录的页9中根据二分法快速定位到主键为20的用户记录

存储目录项记录的页的大小也是16KB，也会存满，此时需要再分配一个新的页。 在这里插入图片描述

此时，查找主键20的记录步骤变成了：

  1. 确定目录项记录页，因此页30的主键范围是[1,320)，所在id=20的记录在页30
  2. 确定了目录项记录页的步骤上面已经有了~

这里我们发现随着目录项记录页越来越多，使用遍历的方式效率太过低下。于是再为这些存储目录项记录的页生成一个更高级的目录。 在这里插入图片描述 继续 在这里插入图片描述

目录一层层嵌套，形成了一棵倒过来的树，即B+树。

不论是存在用户记录的数据页，是存在目录项记录的数据页，都在这个B+树的结构中。其中用户记录都存储在最底层节点上，称为叶子节点，其余用来存放目录的节点称为非叶子节点或内节点，最上面的称为根节点。

B+树的最下边一层为第0层，之后依次往上加。假设叶子节点可以存储100条用户记录，非叶子节点可以存储1000条目录项记录，则有：

  • 如果B+树有1层，最多存储100条记录
  • 如果B+树有2层，最多存储1000*100=1万条记录
  • 如果B+树有3层，最多存储1000×1000×100=1千万条记录
  • 如果B+树有4层，最多存储1000×1000×1000×100=1000亿条记录

一般情况下我们用到的B+树不会超过4层，那么我们通过主键查找某条记录最多只需要做4个页内的查找（3个目录项页和一个用户记录页），并且每个页内都可以使用二分法实现快速定位。

6.2.2.1 聚簇索引

聚簇索引也是B+树，它有两个特点：

  1. 使用记录主键值的大小进行记录和页的排序
    • 页内的记录按照主键大小顺序排成一个单向链表
    • 各个存放用户记录的页根据主键排成一个双向链表
    • 存放目录项记录的页分为不同的层次，同一层次的页也排成一个双向链表
  2. B+树的叶子节点存储的是完整的用户记录，即存储了所有列的值（包括隐藏列）。

InnoDB存储引擎会自动地为我们创建聚簇索引。在InnoDB中，聚簇索引就是数据的存储方式（所有用户数据都存储在了叶子节点上），也就是所谓的索引即数据，数据即索引。

6.2.2.2 二级索引

聚簇索引只在搜索条件是主键值才能发挥作用，因为B+树中的数据都按照主键进行排序的。如果要以其他列（比如c2）作为搜索条件，则需要单独再以这列为排序规则，建立一棵B+树。 在这里插入图片描述

这个B+树与聚簇索引有几处不同：

  1. 按照c2列的大小顺序排成一个单向链表
    • 页内按照c2排成单向链表
    • 用户记录页按照c2排成双向链表
    • 同一层的目标项记录页按照c2排成双向链表
  2. B+树的叶子节点存储的并不是完整的用户记录，只有c2+c1(主键)两个列的值
  3. 目录项记录中不再是主键+页号，而是c2列+页号

此时查找c2=4的步骤如下

  1. 确定目录项记录页，因为 2<4<9，确定页42
  2. 通过目录项记录页确定用户记录所在的页，因为 2<4<=4，确定页34和页35
  3. 在用户记录页定位到具体的记录，在页34和35中定位到具体记录
  4. 因为叶子节点中只存储了两个列，所以需要通过主键再去聚簇索引中查找一遍完整记录，这个动作称作回表

联合索引

我们也可以同时以多个列的大小作为排序规则，也就是使用多个列建立一个索引，以c2+c3为例：

  • 先把各个记录和页按照c2列进行排序
  • 在c2列相同的情况下，使用c3进行排序 在这里插入图片描述

6.2.3 InnoDB中B+树索引的注意事项

6.2.3.1 根页面万年不动窝

B+树的形成过程：

  1. 每当为某个表创建一个B+树索引的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中即没有用户记录，也没有目录项记录。
  2. 随后向表中插入用户记录时，先把用户记录存储到这个根节点中
  3. 当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值（聚簇索引的主键、二级索引的索引列）的大小就会被分配到页a或者页b中，而根节点便升级为存储目录项记录的页。

TIPS：一个B+树索引的根节点自诞生之日起，便不会再移动。当InnoDB需要这个索引的时候，都会从固定的地方取出根节点的页号，从而访问这个索引。

6.2.3.2 内节点中目录项记录的唯一性

保证唯一的好处是新插入的记录可能精确地定位到插入位置，而不是在面对同一个值时陷入迷茫。

聚簇索引的目录项记录是ID + 页号，自然是唯一的。

二级索引的目录项记录是同上索引列的值 + 页号，不能保证唯一。所以需要加上ID，通过索引列的值 + ID + 页号来保证唯一。

6.2.3.3 一个页面最少存储2条记录

龟腚。太少了浪费我么精心的设计。

6.2.4 MyISAM中的索引方案简介

MyISAM的索引方案同样使用树形结构，但是却将索引和数据分开存储。

  1. 将表中的记录按照记录的插入顺序单独存储在一个文件中，称为数据文件。
  2. MyISAM存储引擎会把索引信息存储到另外一个索引文件中，先通过索引找到对应的行号，再通过行号去找对应的记录。也就是相当于InnoDB中的二级索引。

6.2.5 MySQL中创建和删除索引的语句

略

6.3 总结

  1. InnoDB存储引擎的索引是一棵B+树，完整的用户记录都存储在B+树第0层的叶子，其他层次的内节点存储的是目录项记录
  2. InnoDB的索引分为聚簇索引和二级索引
  3. MyISAM存储引擎的数据和索引分开存储，都是二级索引
avatar
文章
阅读
粉丝