Mysql之B+树索引

B+树索引

索引(index)是为快速查找数据而提出的一种解决方案。

一：在没有索引的时候进行查找

1：记录在同一页

1：以主键为搜索条件，使用二分法定义所在的槽，然后顺序查找。
2：以非主键为搜索条件，从第一条记录按照顺序搜索。

2：记录在不同页

1：定位到记录所在的页。
2：在页中进行搜索。

二：索引

create table index_demo(
    c1 int,
    c2 int,
    c3 char(1),
    primary key(c1)
)  row_format=compact;

1：一个简单的索引方案

插入数据:

      insert into index_demo values (1, 4, 'u'),(3, 9, 'd'),(5, 3, 'y');

我们先假设每个页中最多只能含有3条记录，上述的3条记录排成一个单向链表。

如果此时再次插入一条记录，根据我们约定的每个页面只能存放3条数据，此时应当会分裂出一个页面。

      insert into index_demo values (4, 4, 'a');

新分配的数据页编号可能并不是连续的，通过维护一个双向链表使得各个页面之间串联起来。而且新插入的数据4比5小，因此不能将4分配在页28中，我们需要将5移动到页28中，然后将4移入到页10中。两个页之间的一个重要特征就是后一个页的最小记录要大于前一个页的最大记录，构成一个单向有序链表。这是为了查找的时候能够使用二分法进行快速定位。
当页数过多的时候我们应该如何实现快速定位呢？答案是利用页之间有序的特性建立目录项。(计算机的本质是套娃)
给所有的页建立一个目录项。每一个页都对应一个目录项，每个目录项包括下面两个部分：
(1)：页的所有用户记录中最小的主键值，用key表示。
(2)：页号，用page_no表示。
将上面的目录项在物理上连续排列就做成了一个简易的目录，这个目录有一个别名，称为索引。

2：InnoDB中的索引方案

以上的设计方案还有很多的缺点，需要改善。使用之前的数据页来存储目录项，为了与用户记录进行区分，把这些用来表示目录项的记录称为目录项记录。那么，如何区分一条记录是普通的用户记录还是目录项记录呢？此时使用record_type属性，他们的各个取值的含义：
0：普通的用户记录。 1：目录项记录。 2：Infimum记录。 3：Supremum记录。

按照上面的格式递归生成一颗B+树。什么是B+树呢？如果不了解什么是B+树，建议重新翻读一遍《算法导论》。
在InnoDB中，B+树的叶子节点存储用户真实的数据，而非叶子节点或者称为内节点存储目录项记录。一般情况下，B+树不会超过4层。如果感兴趣可以根据你所掌握的树的相关知识进行推导，你就会发现4层的B+树所拥有的数据节点数超过100000000。

2.1：聚簇索引

聚簇索引就是数据页和目录项存放在一起的一种索引格式。具有如下三个特点：
1：页内的记录按照主键的大小顺序排成一个单向链表，且页内被划分为多个组，形成一个一个的槽。
2：各个存放用户记录的页也是按照用户记录的主键大小顺序排成一个双向链表。
3：存放目录项记录的页分为多个层级，在同一层级目录中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。
B+树叶子节点存储的是完整的用户记录，所谓完整的用户记录，就是指这个记录中存储了所有列的值，包括隐藏列。在InnoDB中聚簇索引就是数据的存储方式，即索引即数据，数据即索引。

2.2：二级索引

聚簇索引只对主键起作用，如果我们搜索的记录不是主键呢？答案是多建几颗B+树，并且B+树中的数据采用不同的排序规则。
1：页内的记录不再是按照主键的大小排序，而是按照需要建立索引的列的值，剩下的聚簇索引的规则相同。
2：各个存放用户记录的页也是根据页中记录的c2列大小顺序排成一个双向链表。
3：存放目录项记录的页分为不同的层级，在同一层级中的页也是根据页中目录项记录的c2列大小顺序排成一个双向链表。
4：B+树的叶子节点存储的并不是完整的用户记录，而只是c2列+主键这个两个列的值。
5：目录项记录中不再是主键+页号的搭配，而变成了c2列+页号的搭配。
根据c2索引定位到主键以后进行回表，因为二级索引不包含完整的用户记录，需要回表查看。

2.3：联合索引

同时以多个列的大小作为排序规则。例如以c2,c3建立联合索引。首先按照c2排序，如果c2相同，按照c3排序。

2.4：一些注意事项

2.4.1：根页面不动

创建B+树的过程如下：
1：每当为一个表创建一个B+树索引时，都会为这个索引创建一个根节点页面，最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。
2：随后向表中插入用户记录时，先把用户记录存储到这个根节点中。
3：在根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页中，然后对这个新页进行页分裂操作，得到一个新页。这时新插入的记录会根据键值的大小分配到页a或者页b中，根节点此时便升级为存储目录的目录项记录的页，也就需要把页a和页b对应的目录项记录插入到根节点中。
在这个过程中，一个根节点自创建开始便不会再改变，这样只要我们对某个表建立一个索引，那么他的根节点的页号便会被记录到某一地方，后面凡事需要访问用到这个索引时，都会从那个固定的地方取出根节点的页号，从而访问这个索引。

2.4.2：页内记录的唯一性

在目录项中除了记录索引列的值的数据之外，为了防止重复访问还需要记录主键的值。

2.5：非聚簇索引

事实上，InnoDB的默认引擎不再是聚簇索引，而是非聚簇索引。聚簇索引中数据和索引是存储在一起的，在B+树的叶子节点上包含了所有完整的用户记录，但是非聚簇索引的索引和数据是分开的。通过将表中的数据按照记录的插入顺序单独存储在同一个文件中，这个文件并不划分为若干个数据页，有多少记录就全部塞入这个文件中，通过行号快速的定位一条数据。
MyISAM记录也需要一些记录头信息来存储一些额外信息，但是由于数据并没有按照主键的大小进行排序，所以我们并不能使用二分法进行查找。MyISAM存储引擎会默认把索引信息单独存储在另外一个文件中，为主键单独建立一个索引文件，但是索引文件的叶子节点中存储的并不是完整的用户记录，而是主键值和行号的组合。

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

create table index_demo_index(
     c1 int,
     c2 int,
     c3 char(1),
     primary key(c1),
     index idx_c2_c3 (c2, c3)
);

创建的索引名称为idx_c2_c3，建议以idx为前缀，

删除索引

alter table index_demo_index drop index idx_c2_c3;