mysql 学习 - B+树索引

本篇已收录在 MySQL 是怎样运行的 学习笔记系列

我们已经知道在单一数据页中查找数据时, 如果查找条件是主键的话, 可以使用二分法定位槽, 然后顺序遍历槽中的数据查找指定数据. 但是我们并不知道如何在数以万计的页中定位数据在哪个页中, 在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中根据我们刚刚唠叨过的查找方式去查找指定的记录。

简单索引介绍

为了能够快速定位数据在哪个页中, 索引规定, 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。 下面使用一个案例来看索引如何提升查找数据的效率的:

创建一个表:

mysql> CREATE TABLE index_demo(
    ->     c1 INT,
    ->     c2 INT,
    ->     c3 CHAR(1),
    ->     PRIMARY KEY(c1)
    -> ) ROW_FORMAT = Compact;
Query OK, 0 rows affected (0.03 sec)

插入三条数据:

mysql> INSERT INTO index_demo VALUES(1, 4, 'u'), (3, 9, 'd'), (5, 3, 'y');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

假设一个页里面只能存放三条普通 user records. index_demo 表中的 3 条记录都被插入到了编号为 10 的数据页中了, 再来插入一条记录：

mysql> INSERT INTO index_demo VALUES(4, 4, 'a');
Query OK, 1 row affected (0.00 sec)

因为页 10 最多只能放 3 条记录，所以我们不得不再分配一个新页：

页 10 中用户记录最大的主键值是 5，而页 28 中有一条记录的主键值是 4，因为 5 > 4，所以这就不符合下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值的要求，所以在插入主键值为 4 的记录的时候需要伴随着一次记录移动，也就是把主键值为 5 的记录移动到页 28 中，然后再把主键值为 4 的记录插入到页 10 中:

这个过程表明了在对页中的记录进行增删改操作的过程中，我们必须通过一些诸如记录移动的操作来始终保证这个状态一直成立：下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。这个过程我们也可以称为页分裂。

在多次插入数据后, 此时变成了:

所以如果想从这么多页中根据主键值快速定位某些记录所在的页，我们需要给它们做个目录，每个页对应一个目录项，每个目录项包括下边两个部分：

页的用户记录中最小的主键值，我们用key来表示。
页号，我们用page_no表示。

我们只需要把几个目录项在物理存储器上连续存储，比如把他们放到一个数组里，就可以实现根据主键值快速查找某条记录的功能了。比方说我们想找主键值为20的记录，具体查找过程分两步：

先从目录项中根据二分法快速确定出主键值为20的记录在目录项3中（因为 12 < 20 < 209），它对应的页是页9。
再根据前边说的在页中查找记录的方式去页9中定位具体的记录。

这个页目录的名字起名为索引.

InnoDB 索引

上边之所以称为一个简易的索引方案，是因为我们为了在根据主键值进行查找时使用二分法快速定位具体的目录项而假设所有目录项都可以在物理存储器上连续存储

InnoDB是使用页来作为管理存储空间的基本单位，也就是最多能保证16KB的连续存储空间，而随着表中记录数量的增多，需要非常大的连续的存储空间才能把所有的目录项都放下, 而且, 我们时常会对记录进行增删，假设我们把页28中的记录都删除了，页28也就没有存在的必要了，那意味着目录项2也就没有存在的必要了，这就需要把目录项2后的目录项都向前移动一下

此时需要 innodb 提供一个非常灵活地方式去管理目录项记录, 那是不是可以将目录项记录按照普通数据项记录的管理方式进行管理. 只不过数据的 record_type 会区别数据是目录项记录还是普通数据记录. 经过重新构思后, 数据项记录也放入页中, 按照页管理数据的方式进行管理:

目录项 record 和普通数据 record 的区别:

1.目录项记录的record_type值是1，而普通用户记录的record_type值是0。
2.目录项记录只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列，另外还有InnoDB自己添加的隐藏列。
3.只有在存储目录项记录的页中的主键值最小的目录项记录的min_rec_mask值为1，其他别的记录的min_rec_mask值都是0

除了上述三点不同, 其他的部分均相同. 那么也就是说当存储索引的页装满了以后可以扩展一个页继续存储, 并且生成一个更高级的页目录, 最后效果图就成了这样:

以及慢慢的抽象化:

它的名称是 B+ 树.

不论是存放用户记录的数据页，还是存放目录项记录的数据页，我们都把它们存放到 B+ 树这个数据结构中了，所以我们也称这些数据页为节点。从图中可以看出来，我们的实际用户记录其实都存放在 B+ 树的最底层的节点上，这些节点也被称为叶子节点或叶节点，其余用来存放目录项的节点称为非叶子节点或者内节点，其中 B+ 树最上边的那个节点也称为根节点. 为了讨论方便, 规定最下边的那层，也就是存放我们用户记录的那层为第 0 层，之后依次往上加.

粗略的估算一下树的威力. 如果一个页能存放 100 条数据, 那么:

如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放100条记录
如果B+树有2层，最多能存放1000×100=100000条记录
如果B+树有3层，最多能存放1000×1000×100=100000000条记录。
如果B+树有4层，最多能存放1000×1000×1000×100=100000000000条记录

一般表中都不会有 100000000000 记录, 所以一般情况下，我们用到的 B+ 树都不会超过4层. 通过主键值去查找某条记录最多只需要做4个页面内的查找（查找3个目录项页和一个用户记录页），又因为在每个页面内有所谓的Page Directory（页目录），所以在页面内也可以通过二分法实现快速定位记录

聚簇索引

上面的树的结构有以下两个特点:

1.使用记录主键值的大小进行记录和页的排序
2.B+树的叶子节点存储的是完整的用户记录

具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建, InnoDB存储引擎会自动的为我们创建聚簇索引。

由于聚簇索引是通过主键去查数据的, 当我们的查询条件无法包含主键时, 难道就得从头到尾遍历所有的数据了吗?

二级索引

假设我们的查询条件是 c2 列的值, 那么我们就应该创建一个新的以 c2 列的值进行排序后的 b+ 树.

这个B+树与上边介绍的聚簇索引有几处不同：

  • 使用记录c2列的大小进行记录和页的排序，这包括三个方面的含义：

    • 页内的记录是按照c2列的大小顺序排成一个单向链表。
    • 各个存放用户记录的页也是根据页中记录的c2列大小顺序排成一个双向链表。
    • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的c2列大小顺序排成一个双向链表。

  • B+树的叶子节点存储的并不是完整的用户记录，而只是c2列+主键这两个列的值。

  • 目录项记录中不再是主键+页号的搭配，而变成了c2列+页号的搭配。

所以如果我们现在想通过c2列的值查找某些记录的话就可以使用我们刚刚建好的这个B+树了。以查找c2列的值为4的记录为例，查找过程如下：

  • 确定目录项记录页, 根据根页面，也就是页44，可以快速定位到目录项记录所在的页为页42（因为2 < 4 < 9）。

  • 通过目录项记录页确定用户记录真实所在的页。在页42中可以快速定位到实际存储用户记录的页，但是由于c2列并没有唯一性约束，所以c2列值为4的记录可能分布在多个数据页中，又因为2 < 4 ≤ 4，所以确定实际存储用户记录的页在页34和页35中。

  • 在真实存储用户记录的页中定位到具体的记录。到页34和页35中定位到具体的记录。

  • 但是这个B+树的叶子节点中的记录只存储了c2和c1（也就是主键）两个列，所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。这个过程也被称为回表。也就是根据c2列的值查询一条完整的用户记录需要使用到2棵B+树, 之所以需要回表操作是因为节省空间.

因为这种按照非主键列建立的B+树需要一次回表操作才可以定位到完整的用户记录，所以这种B+树也被称为二级索引（英文名secondary index），或者辅助索引。由于我们使用的是c2列的大小作为B+树的排序规则，所以我们也称这个B+树为为c2列建立的索引。

联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按照c2和c3列的大小进行排序，这个包含两层含义：

先把各个记录和页按照c2列进行排序。
在记录的c2列相同的情况下，采用c3列进行排序

效果图:

  • 每条目录项记录都由c2、c3、页号这三个部分组成，各条记录先按照c2列的值进行排序，如果记录的c2列相同，则按照c3列的值进行排序。

  • B+树叶子节点处的用户记录由c2、c3和主键c1列组成

  • 以c2和c3列的大小为排序规则建立的B+树称为联合索引，本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的

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

根页的页码是不会改变的

当我们为一个表创建聚簇索引时(不创建的话也会默认有一个), 就已经有根页了, 只是此时根页里面没有存储任何数据.

当为表中添加数据后, 根页先存储数据, 当数据存储满了以后, 会将数据复制出来放入页 a, 再加入数据时, 产生页分裂, 出现页 b, 此时根页里面存储的则是页目录记录.

凡是InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引。

页中节点的唯一性

这是针对二级索引才有的问题, 当二级索引按照某列进行排序后, 可能会出现相同的情况, 此时需要加上主键用以区分唯一性. 再添加数据才会定位到该添加的页.

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

这是由于如果只放一条记录的话, 画面太美不敢想象.

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

InnoDB 会自动为主键或者声明为 UNIQUE 的列去自动建立 B+ 树索引，但是如果我们想为其他的列建立索引就需要我们显式的去指明。

我们可以在创建表的时候指定需要建立索引的单个列或者建立联合索引的多个列：

CREATE TALBE 表名 (
    各种列的信息 ··· , 
    [KEY|INDEX] 索引名 (需要被索引的单个列或多个列)
)

或者对表修改:

ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (需要被索引的单个列或多个列);

也可以在修改表结构的时候删除索引：

ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;

具体例子:

CREATE TABLE index_demo(
    c1 INT,
    c2 INT,
    c3 CHAR(1),
    PRIMARY KEY(c1),
    INDEX idx_c2_c3 (c2, c3)
);

ALTER TABLE index_demo DROP INDEX idx_c2_c3;
posted @ 2020-04-02 21:42  YanyuWu  阅读(595)  评论(0)  编辑  收藏  举报