MySQL索引(2)：B+树索引基本概念

B+树索引的本质就是B+树在数据库中的实现，但是B+树索引在数据库中有一个特点是高扇出性，所以在数据库中B+树的高度一般在2到4层，即查找某个键值的行记录最多需要2到4次IO。

B+树索引可以分为聚簇索引(一级索引)和辅助索引(二级索引)，二者都是B+树，叶节点存放数据。二者的区别在于聚簇索引的叶节点存放的是主键索引值与行数据的映射，而辅助索引的叶节点存放的是普通索引与主键索引的映射。

聚簇索引

InnoDB存储引擎表是索引组织表，表中数据按照主键顺序存放。聚簇索引就是按照每张表的主键构造一棵B+树，同时叶子节点存放的就是整张表的行数据，也将聚簇索引的叶节点称为数据页。这个特性决定了索引组织表中的数据也是索引的一部分。B+树索引的每个数据页之间都使用双向链表进行连接。

每张表只能有一个聚簇索引，查询优化器倾向于使用聚簇索引，因为可以在聚簇索引的叶节点上直接找到数据。由于定义了数据的逻辑顺序，聚簇索引能很快的进行范围查询，即优化器可以快速发现某一段范围的数据并在数据页上进行扫描。

聚簇索引的存储在物理上并不连续，而是在逻辑上连续，

1. 数据页通过双向链表连接，数据页中按照主键的顺序进行排序
2. 每个数据页中的记录使用双向链表进行维护，物理上可以不连续存储

辅助索引

辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含非主键索引的键值外，每个键值对应一个bookmark，该bookmark实际上就是相应行数据的聚簇索引的键值。

辅助索引的B+树与聚簇索引的B+树高度是相同的，每张表可以有多个辅助索引。当使用辅助索引查询时，遍历辅助索引树拿到相应的主键值后，再通过对聚簇索引搜索主键值获取到相应的行数据。

假如在一棵高度为3的辅助索引树中查找数据，需要对索引树进行3次遍历找到指定的辅助索引值，通过该值可以获取到对应的主键值。之后在聚簇索引中搜索主键值，遍历的次数同样为3次才能得到行数据所在的页，对该页进行二分法查找获取完整的行数据。因此一共需要6次逻辑IO访问得到最终的数据页。

B+索引的分裂

B+树的分裂是将叶节点的中间元素作为分裂元素进行的，但是这是最简单的一种情况，B+树索引的分裂与B+树稍有不同。B+树索引的分裂并不总是从页的中间记录开始的，因为可能会导致空间浪费。如《MySQL技术内幕》中给出的例子，

InnoDB引擎的Page Header中有几个部分用于保存插入的顺序信息，

• PAGE_LAST_INSERT
• PAGE_DIRECTION
• PAGE_N_DIRECTION

这些信息可以指引引擎决定向左还是向右分裂，并决定分裂点是哪个元素。

现在存在一条记录等待插入，定义"定位(cursor)到的记录"是待插入记录的前一条记录，

• 若插入是随机的，则取页的中间记录作为分裂点的记录
• 若往同一方向进行插入的记录数量为5，且目前已经定位到的记录之后还有3条记录，则分裂点是待插入记录后第三条记录；否则分裂点就是待插入的记录

第一种情况较好理解，第二种情况以下面的向右分裂为例，
i) cursor之前的条目数大于5，待插入记录后还有3条以上的记录

ii) 分裂点就是插入记录本身

B+树索引的管理及Cardinality值

索引的创建和删除有两种方法，

1. alter table语句
   
2. create / drop index语句
   

查看索引使用的是show index命令，

show index from table_name\G;

该命令返回的信息中，每个索引Cardinality项的信息格外关键，优化器会根据该值判断是否会使用该索引。这个值表示表中不重复记录的估计值(非精确)。返回的信息中，$cardinality=\frac{estimated\_value}{row\_number}$的值越接近1越好，如果十分接近0，优化器会放弃使用该索引。

Cardinality值的更新发生在insert和update操作中，但不是每次都更新。更新的策略如下，

1. 如果表中$\frac{1}{16}$的数据都发过变化则进行更新
2. stat_modified_counter>2亿时进行更新，这种情况描述的是对少部分数据频繁更新的情况（这种情况不能触发1中的条件）

计算Cardinality的过程实际上是一个采样过程，默认从八个叶节点上进行采样，统计每个数据页中不重复记录的总条数为$P_1,P_2,...,P_8$，B+树索引的数据页总数（叶节点总数）为$A$，则可以估算出 $estimated\_value=\frac{P_1+P_2+...+P_8}{8}\cdot A$。每次调用show index命令得到的Cardinality信息可能是不同的，当表中数据量很多且辅助索引较多时，计算Cardinality是很漫长的过程。