【数据库】---详解mysql索引的数据结构

索引简介

索引通过将无序的数据变成相对有序的数据来提高查询速度。

使用索引的好处：

1. 通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性。
2. 可以大大加快数据的检索速度
3. 将随机IO变为顺序IO（顺序IO不需要多次磁盘寻道，所以比随机IO快很多，当对于groupby查询时无需再做排序了）
4. 可以加速表与表之间的连接，特别是在实现数据的参考完整性方面

注：
当创建索引后，当对表中数据进行增加、删除和修改的时候，索引也要进行动态维护，降低了数据的维护速度。

索引需要占用物理空间，除了数据表占用数据空间以外，每一个索引还要占用一定的物理空间，如果要简历聚簇索引、那么需要的空间就会更大

创建索引和维护索引都要耗费时间，这种时间随着数据量的增大而增大。

索引使用场景：
在经常需要搜索的列上使用索引可以加快搜索的速度
在经常使用where子句的列上创建索引，加快条件的判断速度
在经常需要排序的列上使用索引非常有效，但是对于特大型表的维护开销非常大，不适合创建索引。
在经常使用连接的列上，这些列都是一些外键，可以加快连接的速度。

以下场景避免使用索引：
避免where子句中对字段施加函数，会造成无法命中索引。
在使用InnoDB时，使用与业务无关的自增主键作为索引，即逻辑主键，而不要使用业务主键
将打算加索引的列设置为not null，否则将导致引擎放弃使用索引而进行全表扫描
删除长期未使用的索引，不用的索引会造成不必要的性能消耗
在使用limit offset查询缓慢时，可以借助索引来提高性能

避免使用冗余索引：若索引的功能相同，能够命中就一定能够命中，如（name,city）和（name）就是冗余索引，因此一般应该扩展已有的索引，而不是创建新的索引。

覆盖索引：
如果一个索引包含所有需要查询的字段的值，我们就称之为覆盖索引。在InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值，最终还是要回表，也就是通过主键再找一次，这样会比较慢，而覆盖索引就是把要查询的列和索引是对应的，不做回表操作。

最左前缀原则：MySQL可以以一定的顺序引用多列，这种索引叫做联合索引，如果查询时条件精确匹配索引的左边连续一列或几列，（如果跳过左边的列对右边的列进行匹配，则无法命中，但是如果只是顺序不同，但是查询时索引中的列都用上了，查询引擎会自动优化查询顺序）则此列就可以被用到。因此列的排列顺序决定了可命中索引的列数。

索引的数据结构

MySQL的基本存储结构是页（记录都存在页里面），各个数据页可以组成一个双向链表，每个数据页都会为存储在它里面的记录生成一个页目录，每个数据页中的内容又可以组成一个单向链表。

数据页和页目录的关系图如下：

通过主键查找记录的过程：
从第一个页开始，沿着双向链表对每一个页进行查找，每个数据页都会为存储在它里面的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中 使用二分法快速定位到对应的槽，然后遍历该槽里面对应分组的记录就可以快速找到指定的记录。

普通查询过程：
当我们查询一个没有任何优化的sql语句时，会先遍历双向链表，定位到所在的页，由于不是根据主键查询，因此会从最小记录开始一条一条遍历页所在的单链表中的每条记录，然后对比每条记录是不是复合搜索条件。

表的存储结构

记录头信息里的record_type属性，它的各个取值代表的意思如下：

0：普通的用户记录

1：目录项记录

2：最小记录

3：最大记录

当我们使用主键索引时，存储引擎会按照主键的大小，将页内的记录按照顺序排成一个单向链表

然后按照每一个页中记录的最大主键值对页进行排序成为一个有序双向链表，要求下一个页中的主键值必须大于上一个页中的主键值，因此在双向链表中，页号基本是不连续的。

为了能快速定位到页，通过对每个页增加了一个记录，这个记录只包含两个值，分别是：页的用户记录中最小的主键值key，和页号page_no

然后采用页的方式，将这些页记录顺序连接成为一个单向链表放在一个叫目录项记录的页中，目录项记录页通过对页记录的主键值生成一个Page Directory（页目录）以加快在页内的查询速度，通过这些主键值，再将这些目录项记录顺序连接为一个双向链表。

如果数据非常庞大，就会通过逐级生成目录项纪录页，直到得到一个顶层目录页。这个就是B+树。因此我们的实际用户记录其实都存放在B+树的最底层的节点上。

InnoDB存储引擎会自动的为我们创建聚簇索引,聚簇索引的特点是：
1.使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

• 页内的记录是按照主键的大小顺序排成一个单向链表。
• 各个存放用户记录的页也是根据页中记录的主键大小顺序排成一个双向链表。
• 各个存放目录项的页也是根据页中记录的主键大小顺序排成一个双向链表。

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

回表

当我们查询主键时会采用上述的B+树结构内容进行查询，如果是查询其他列，那么就会为相应的列也同样创建一个这样的B+树。
但是页内的记录是按照指定列的大小顺序排成一个单向链表。
各个存放用户记录的页也是根据页中记录的指定列大小顺序排成一个双向链表。
各个存放目录项的页也是根据页中记录的指定列大小顺序排成一个双向链表。
在这个B+树的叶子节点存储的并不是完整的用户记录，而只是指定列+主键这两个列的值，所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录，这个过程也被称为回表。
所以这种B+树也被称为二级索引（英文名secondary index），或者辅助索引。由于我们使用的是c2列的大小作为B+树的排序规则，所以我们也称这个B+树为为c2列建立的索引。

我们也可以为多个列创建索引，存储引擎会先对最左边的列的记录和页进行排序，如果在最左边列的记录相同的情况下，会对右边列进行排序，这就是为什么最左前缀原则中对左边连续一列或几列进行精确匹配时才可以命中的原因。

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构

B-树

B-树是一种多路搜索树，其特点是：

• 关键字分布在整棵树中
• 任何一个关键字出现且只出现在一个节点中
• 搜索有可能在非叶子节点中结束
• 搜索性能等价于在关键字全集内做一次二分查找
• 具有自动层次控制
  • B树中每一个内部节点会包含一定数量的键值。通常，键值的数量被选定在d和2d之间。在实际中，键值占用了节点中大部分的空间。因数2将保证节点可以被拆分或组合。如果一个内部节点有2d个键值，那么添加一个键值给此节点的过程，将会拆分2d键值为2个d键值的节点，并把此键值添加给父节点。每一个拆分的节点需要最小数目的键值。相似地，如果一个内部节点和他的邻居两者都有d个键值，那么将通过它与邻居的合并来删除一个键值。删除此键值将导致此节点拥有d-1个键值;与邻居的合并则加上d个键值，再加上从邻居节点的父节点移来的一个键值。结果为完全填充的2d个键值。

B-树的搜索：从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；

B+树

B+树是B树的一个变种，mysql使用的就是B+树实现索引结构
B+树的特点有：

• 非叶子节点的子树指针与关键字的个数相同
• 所有叶子节点连接成为一个单链表，且这个链表是有序的
• 所有关键字都在叶子节点出现，因此不可能在非叶子节点命中
• 内节点不存数据，只存key
• 非叶子节点相当于是叶子节点的索引，叶子节点相当于是存储数据的数据层
• 适合文件索引系统（）

索引一般以文件形式存储在磁盘上，索引检索需要磁盘I/O操作，由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。
在数据库中，B-Tree的一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。

MyISAM索引的实现：

InnoDB中索引即数据，也就是聚簇索引的那棵B+树的叶子节点中已经把所有完整的用户记录都包含了，而MyISAM的索引方案虽然也使用B+树，但是却将索引和数据分开存储：
将表中的记录按照插入时间顺序的存储在一块存储空间上，我们可以通过行号而快速访问到一条记录

MyISAM会单独为表的主键创建一个B+树索引，只不过在B+树的叶子节点中存储的不是完整的用户记录，而是主键值 + 行号的组合。也就是先通过索引找到对应的行号，再通过行号去找对应的记录，因此在MyISAM中对主键查询需要进行一次回表操作，意味着MyISAM中建立的索引全部都是二级索引