Mysql B+树索引的推演

一、索引的数据结构

1.为什么要建索引

目的为了减少磁盘I/O的次数，加快查询效率

2.索引及其优缺点

1）索引概述

● 官方定义：索引(index)是帮助MySQL高效获取数据的数据结构

● 索引的本质：索引是数据结构，可以简单理解为"排好序的快速查找数据结构"，满足特定查找算法。数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级算法。

● 索引是在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同。

2）优点

● 提供数据检索的效率，降低数据库的IO成本，最主要原因

● 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性

● 在实现数据的参考完整性方面，可以加速表和表之间的连接。对于有依赖关系的子表和父表联合查询时，可以提高查询速度

● 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间

3）缺点

● 创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加

● 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，存储在磁盘上，如果有大量索引，索引文件就可能比数据文件更快达到最大尺寸

● 虽然索引大大提高了 查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这就降低了数据的维护速度

PS：索引可以提高查询速度，但会影响插入速度，这时候，最后先删除表中的索引，然后插入数据，插入完成后再创建索引

3.InnoDB中索引的推演

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

这个新建的表中有两个int类型的列，1个char(1)类型的列，并且规定了c1为主键，表使用行格式Compact来实际存储记录。

简化版的行格式示意图：

● record_type：记录头信息的一项属性，表示记录的类型，0表示普通记录、2表示最小记录、3表示最大记录，1表示目录项。

● next_record：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，用箭头来表明下一条记录是谁。

● 各个列的值：表中列的值

● 其他信息：除了上述3种信息意外的所有信息，包括其他隐藏列的值以及记录的额外信息。

1）简单索引

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

● 给所有的页建立一个目录项

①迭代一次：目录项记录的页

②迭代二次：多个目录项记录的页

③迭代三次：目录项记录页的目录页

④B+Tree

● 不论是存放用户记录的数据页，还是存放目录项记录的数据页，都成为了B+Tree的节点

● 用户记录的节点称为叶子节点，其余用来存放目录项的节点称为非叶子节点或者内节点

● B+Tree最上边的称为根节点

● B+tree一般不超过四层，所以最多做四次I/O交互(一次根节点，2次内节点，一次叶子节点)，每个节点内都有所谓的Page Director(页目录)，所以可根据二分法快速定位

3）常见索引概念

①聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种存储方式(所有的用户记录都存储在了叶子节点)，也就是所谓的索引及数据，数据即索引

PS：聚簇：表示数据行和相邻的键值聚簇的存储在一起

特点：

● 使用记录主键值的大小进行记录核页的排序，这包括三个方面的含义：

○ 页内的记录是按照主键的大小顺序排成一个单向链表

○ 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排查一个双向链表

○ 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表

● B+树的叶子节点存储的是完整的用户记录

○ 完整记录：指这个记录中存储了所以列的值(包括隐藏列)

● InnoDB存储引擎会自动的为我们创建聚簇索引

优点：

● 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+数中，因此从聚簇索引中获取数据比非聚簇索引更快

● 聚簇索引对于主键的排序查找和范围查找速度非常快

● 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的I/O操作

缺点：

● 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，一般对于InnoDB，我们一般都定义一个自增的ID主键

● 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新

● 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

限制：

● 对于MySQL数据库目前只有InnoDB数据引擎支持聚簇索引，而MylSAM并不支持聚簇索引

● 对于数据物理存储排序方式只能有一种，所以每个MySQL的表只能有一个聚簇索引。一般情况就是该表的主键

● 如果没有定义主键，InnoDB会选择非空的唯一索引代替。如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引

● 为了充分利用聚簇索引的聚簇特性，所以InnoDB表的主键列尽量选用有序的顺序ID，而不建议用无序的id，比如UUID、字符串等列作为主键无法保证数据的顺序增长

②二级索引(辅助索引、非聚簇索引、联合索引)

● 对于非主键之外建立的索引，额外多建n颗B+树，不同的B+树中的数据采用不同的排序规则。比如说用C2列的大小作为数据页，页中记录的排序规则，再建一颗B+树：

(1)此时这颗B+树与上边的聚簇索引有几处不同：

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

○ 页内的记录是按照C2列的大小顺序排成一个单向链表

○ 各个存放用户记录的页也是根据页中记录的C2列大小顺序排成一个双向链表

○ 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的C2列大小顺序排成一个双向链表

● B+树的叶子节点存储的并不是完整的用户记录，而只是C2列和主键列的值

● 目录项记录中不再是主键+页号的搭配，而是C2+页号的搭配

(2)按照非主键建立的B+树需要进行一次回表操作才可以定位到完整的用户记录，所以称为二级索引

● 总结：聚簇索引和非聚簇索引的区别

● 聚簇索引的叶子节点存储的就是我们的数据记录，非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序

● 一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引目录提供数据检索

● 使用聚簇索引的时候，数据的查询效率高，但如果对数据进行插入、删除、更新等操作，效率会比非聚簇索引低

● 联合索引

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

● 先把各个记录和页按照C2列进行排序

● 在记录的C2列相同的情况下，采用C3列进行排序

(2)为C2和C3建立的索引示意图：

如图所示，需要注意一下几点：

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

● B+树叶子节点处的用户记录由C2、C3和主键C1列组成

③IoonDB的B+树索引的注意事项

(1)实际B+树的形成过程：

● 每当为某个表创建一个B+树索引时，为该索引创建一个根节点。最开始表中无数据时，每个B+树索引对应的根节点中既无用户记录，也无目录项记录

● 随后向表中插入用户记录时，先把用户记录存储到这个根节点中

● 当根节点中可用空间被用完时继续插入记录，会将根节点的所有记录复制到一个新分配的页(假如页a)，然后对这个新页进行页分裂的操作，得到另一个

页(假如页b)，这时插入的记录根据键值的大小就会被分配到页a或者页b中，而根节点变升级为存储目录项记录的页

● 根节点产生之后便不会移动，对某个表建立索引，根节点便会记录到某个地方，然后凡是InnoDB存储引擎需要用到索引时，都会从固定地方取根节点页

号，从而访问这个索引

(2)内节点中目录项记录的唯一性

● 为了让新插入的记录能找到自己所在的里，需要保证在B+树的同一层内节点的目录项记录除了页好以外是唯一的。

● 所以二级索引内节点的目录项由三个部分构成：

○ 索引列的值

○ 主键值

○ 页号

(3)一个页最少存储2条记录

④MylSAM中的索引方案

(1)MySAM索引的原理

(2)MySAM与InnoDB的对比

⑤MySQL数据结构选择的合理性