MySQL的B+树索引详细介绍

1、在没有索引时进行数据查找

select [查询列表] from 表名 where 列名 = xxx;

1.1、在一个页中进行查找

假设目标表中记录较少，所有记录都可以存在一个页中，在查找时可以根据搜索条件的不同分为两种情况：

（1）以主键为搜索条件：可以在页目录中使用二分法快速定位到对应的槽，再遍历该槽对应的分组中的记录，即可快速找到指定的记录。

（2）以其他列为搜索条件：因为没有索引，数据中并没有为非主键列建立所谓的页目录，所以无法通过二分法快速定位，这种情况下，只能从infimum记录开始依次遍历单向链表的每条记录，然后逐条对比是否符合搜索条件。

1.2、在很多页中查找

很多时候，表中存放的记录都很多，需要用到很多的数据页来存储记录，在很多页中查找记录分为两步：

（1）定位到记录所在页，

（2）从所在页中查找相应的记录。

在没有索引的情况下，无论是根据主键列或是其他列的值进行查找，我们都不能快速定位到记录所在页，只能从第一页沿着双向链表一直往下找，在每一页当中按照上面的方式进行查找执行的记录，这样查找很浪费时间及性能，所以我们就需要索引。

2、索引

首先创建一张表：

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

为了方便理解，我们简化下index_demo表的行格式示意图：

record_type：记录头信息的一项属性，表示记录的类型，0表示普通记录，2表示Infimum记录，3表示Supremum记录，1表示目录项记录。
next_record：记录头信息的一项属性，表示当前记录的真实数据到下一条记录的真实数据的距离。

一个简单的索引方案

我们在根据某个条件查找一些记录时，为什么要遍历所有的数据页呢？，原因是各个页中的记录并没有规律，我们并不知道搜索条件会匹配到哪些页中的记录，所以不得不遍历所有的数据页。如果想要快速定位到需要查找的记录在那些数据页中，我们为了根据主键值快速定位一条记录在页中的位置而设立页目录，也可以想办法为快速定位记录所在的数据页而建立一个别的记录，创建这个目录的过程必须要完成两件事：

--1、下一个数据页中的用户记录的主键值必须要大于上一个页中用户记录的主键值。

假如一个页中最多有三条数据，页1中有主键值为1 3 5三条记录，在插入一条主键为4的数据，那么这个时候还会怎么办呢？
我们不得不再分配一个新页(页5)，注意再分配页的时候，页号可能不是连续的，也就是说在磁盘中这些页可能并不是挨着的。在新页中插入主键为4的数据，但是这时就不满足下一个数据页中的用户记录的主键值必须要大于上一个页中用户记录的主键值，所以我们会有一次记录移动，把主键值为5的记录移动到页5种，再将主键值为4的数据插入到页1。这个过程也称为页分裂。

--2、给所有的页建立一个目录项

由于数据页的编号可能不是连续的，所以想从这么多页中根据主键值快速定位到某些记录所在页，就需要给他们编一个目录，每个页代表一个目录项，每个目录项包含以下两个部分：
----页的用户记录中最小的主键值，用key表示；
----页号，用page_no表示。

而在查找记录时，我们可以通过二分法快速定位到查找记录的主键值所在的页，再通过页目录中使用二分法快速定位到对应的槽，再遍历该槽对应的分组中的记录，即可快速找到指定的记录。

InnoDB中的索引方案

刚才是个简易的索引方案，因为假如数据量很大的时候，页的目录项会很多，通过二分法去查找可能也要很久，这样也会很浪费时间。
所以这时候就需要一种可以灵活管理所有目录项的方式。而这些目录项其实和用户记录很像，所以就复用了之前存储用户记录的数据页来存储目录项。为了和用户记录区分，我们把这些用来表示目录项的记录成为目录项记录，而这类记录的record_type的值是1 。

到此，我们来强调一下目录项记录和普通用户记录的不同点：
1、目录项记录的record_type的值是1 ，普通用户记录的record_type是0；
2、目录项记录只有主键值和页的编号两个列，而普通用户记录的列是用户自己定义的；
3、之前说过的min_rec_flag的属性，只有目录项记录的min_rec_flag才可能为1，而普通用户记录的min_rec_flag都是 0。

当用户记录很多的时候，目录项记录同样会很多，目录项记录所在页也会有很多，这时进行数据查找则会分为三步：
1、确定存储目录项记录的页；
2、通过存储目录项记录的页确定用户记录真正所在的页；
3、在真正存储用户记录的页中定位到具体的记录。

而当我们的目录项记录很多很多，这时候查找目录项记录所在页的时候也会很麻烦，那怎么办呢，那么我们就再生成一层更高级的目录用来存放目录项所在页的信息，假如数据还会更多，那么我们就再生成一层更高级的目录。而这种组织数据的形式就是B+树，一般情况下我们用到的B+树结构不会超过4层。

无论是存放用户记录的数据页还是存放目录项记录的数据页，我们都把它们存放在B+树这个数据结构中，我们也将这些数据页称为节点，我们真正的用户记录其实都存放在B+ 树的最底层的节点上，这些节点称为叶子节点或者叶节点。其余目录项记录的节点称为非叶子节点或者内节点。B+树最上边的节点称为根节点。

聚集索引

前面说的B+树所有本身就是个目录，它有以下两个特点：
1、使用记录主键值的大小进行记录和页的排序，这包括三方面的含义：

• 1.1 页内的记录按照主键值大小顺序排成一个单向链表，页内的记录被划分为若干个组，每个组中主键值最大的记录在页内的偏移量都会被当作槽依次存放在页目录中，我们可以在页目录中通过二分法快速定位到主键列等于某个值的记录。
• 1.2 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
• 1.3 存放目录项记录的页分为不同的层级，在同一层级中的页也是根据页中目录项记录的主键大小排成一个双向链表。

2、B+树的叶子节点存储的是完整的用户记录，所谓的完整的用户记录，就是指这个记录中存储了该表中所有的列的值（包括隐藏列）。

我们把具备以上两个特点的B+树索引称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处，InnoDB会自动创建好聚簇索引，并不需要我们去创建，这就是索引即数据，数据即索引。

二级索引

聚簇索引只能在搜索条件为主键值的时候才能发挥作用，原因是B+树种的数据都是按照主键进行排序的，如果我们想以别的列作为搜索条件，那么我们可以多建几个B+树，并且不同的B+树种的数据采用不同的排序规则，比如，我以c2列的大小作为数据页、页中记录的排序规则，然后在创建一颗B+树。

这个B+树和前面的聚簇索引有几处不同：
1、使用记录c2列的大小进行记录和页的排序，这包括3方面的含义。

• 1.1 页内的记录按照c2大小顺序排成一个单向链表，页内的记录被划分为若干个组，每个组中c3列值最大的记录在页内的偏移量都会被当作槽依次存放在页目录中，我们可以在页目录中通过二分法快速定位到c2列等于某个值的记录。
• 1.2 各个存放用户记录的页也是根据页中用户记录的c2大小顺序排成一个双向链表。
• 1.3 存放目录项记录的页分为不同的层级，在同一层级中的页也是根据页中目录项记录的c2大小排成一个双向链表。

2、B+树的叶子节点存储的不是完整的用户记录，而是c2列和主键的搭配。

目前，比如说我们想查找c2=4的记录，那么过程如下：
1.确定第一条符合c2=4条件的目录项记录所在的页；
2.通过第一条符合c2=4条件的目录项所在的页面确定第一条符合c2=4条件的用户记录所在的页；
3.在真正存储第一条符合c2=4条件的用户记录的页中定位到具体的记录；
4.这个B+树中只存储了c2和c1两个列，在定位到第一条符合条件的用户记录后，我们要根据该条记录的主键信息到聚簇索引中查找完整的用户记录。这个过程也叫做回表。然后再返回这颗B+树的叶子节点处，找到刚才定位到符合条件的那条用户记录，并沿着记录组成的单向链表向后继续搜索其他也符合c2=4的记录，每找到一条就进行回表操作。重复这个过程，直到下一条记录不满足c2=4的条件为止。

因为这种以非主键列的大小为排序规则而建立的B+树需要执行回表操作才可以定位到完整的用户记录，所以这种B+树被称为二级索引。

联合索引

我们可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比如我们想让B+树按照c2和c3列的大小进行排序，这里面包含两层含义：
1、先把各个记录和页按照c2列进行排序：
2、在记录的c2列相同的情况下，再采用c3进行排序。

需要注意的是：
1、每条目录项记录都是由c2列和c3列和页号这三部分组成，先按照c2列排序，再按照c3列排序。
2、B+树叶子节点处的用户记录由c2列和c3列及主键组成。