mysql innodb 索引算法_MySQL技术内幕InnoDB存储引擎（五）——索引及其相关算法

索引概述

索引太多可能会降低运行性能，太少就会影响查询性能。

最开始就要在需要的地方添加索引。

常见的索引：

B+树索引

全文索引

哈希索引

B+树索引

B+树

所有的叶子节点存放完整的数据，非叶子节点就是索引节点，只存放索引信息。

1. 插入操作

插入操作需要考虑节点是否被占满了，如果满了，久需要生成新节点。

叶节点和非叶节点都没满：直接插入到叶子节点。

叶节点满了，非叶节点没满：根据大小拆分叶子节点变成两个，再将中间节点放到上面的非叶节点，然后再插入数值。

都满了：先拆分叶子节点，再拆分非叶子节点，中间节点放在上一次的非叶子节点。

上面的拆分操作之后，都要重新整理叶子节点的归属，这样就能保持树的平衡。

拆分的问题

B+树主要用于磁盘，拆分操作会提高磁盘的开销，所以要尽可能减少拆分。

减少拆分

利用旋转的功能，如果检测到相邻叶子节点有空位，就可能通过旋转操作，将边缘的元素直接挤到相邻的叶子节点当中去，同时改变非叶子节点的数值。

2. 删除操作

为了保持空间利用率和查询效率，数据库通过一个填充因子来控制节点中的元素占空比。50%是最小值。

都大于等于填充因子：直接删除叶子节点的元素，如果该节点时非叶的索引值，那么就会用该元素的右元素代替。

叶子节点小于填充因子：合并叶子节点和他邻接节点，然后更新它们的父节点。

都小于填充因子：合并叶子节点，更新父节点，合并父节点，更新父父节点。

B+树索引的分类

B+树索引可以分为聚集索引和辅助索引，它们都是高度平衡的。

聚集索引和辅助索引的区别？

聚集索引的叶子节点存放一整行的数据，而辅助索引的叶子节点并不包含行记录的全部数据。

辅助索引叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签。书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。书签就是相应行数据的聚集索引键(主键)。

一张表只能有一个聚集索引，而辅助索引可以有很多个。

聚集索引的存储是逻辑上连续的，而非聚集索引不是。

1.聚集索引

聚集索引就是按照每张表的主键构造的一棵B+树。

聚集索引的特点：

叶子节点存放一张表的行记录数据。

每个数据也都通过一个双向链表进行链接。

每张表只能有一个聚集索引。

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

主键排序查找和范围查找速度非常块。

为什么聚集索引是逻辑上连续的？

数据页通过双向链表链接，并且按照主键的顺序排序；每个页中的记录也是通过双向链表进行维护的，物理存储上就不一定按照主键的顺序存储。

2.辅助索引

也称为非聚集索引。

聚集索引的特点：

叶子节点不包含行记录的所有数据。

叶子节点除了键值，还包含一个书签，用来指向相应行的聚集索引键，这样就能拿到所有数据。

同一张表可以拥有多个辅助索引。

完整数据查找速度慢一些，因为需要先从再辅助索引树中查找得到聚集索引，然后再去聚集索引树中查找，这样开销就大了一倍。

B+树索引的分裂

问题：

如果像上面介绍B+树添加元素的时候，节点元素满了，就直接从节点的中点元素为分界点，将节点分为两个节点，这样做不一定是最优的。因为数据可能是递增的，这样，左节点中的元素就不可能再增加，空间就浪费了。

解决方法：

存储引擎通过几个指针觉决定分裂方向。

如果发现节点元素添加是随机的，就中间分裂。

如果发现节点元素是递增的，右边添加进来的元素开始分裂。

Cardinality

什么是Cardinality？

用于计算对表中的索引是否具有高选择性。所谓高选择性，就是某一行的一个键值，在表中的区分度大不大。例如性别的区分度就很小，而姓名就大得多。区分度越大，就是高选择性。

这个值的估算重要，根据这个值选择好的索引，对操作的效率有很大提升。

InnoDB的Cardinality工作原理

因为索引的更新可能很频繁，每次都去估算Cardinality就造成非常大的开销。内部的更新策略是：

表中1/16的数据发生了改变。

数据变化次数大于二十亿。

InnoDB引擎默认对随机的8个叶子节点进行采样，然后取平均。所以，每次的估算Cardinality值都可能不一样。

B+树索引的使用

针对环境

OLTP

大量的操作次数，每次获取的数据条数却很少，所以利用B+树索引是有意义的。

OLAP

总的操作不频繁，每次获取的数据条数很多，比如每一个月的统计报表。不需要对一些少用的字段进行索引。通常对时间需要建立索引，因为数据统计会根据时间。

联合索引

联合索引就是指对表上的多个字段合并产生索引。联合索引的键值是多个的。

优点：

对数据进行有效的筛选。

当第一个键值相同的时候，对第二个键值也进行了排序。

覆盖索引

从辅助索引中就可以查询到想要的记录，不从再去查聚集索引。

优点：

不包含整行的记录，占用空间就小，减少了IO操作。

不使用辅助索引的情况

对于以下操作，可能就会通过全表扫描的方法来得到数据：

范围查找；

JOIN链接操作。

为什么数据库要这样做？

如果用户需要整行的数据，而辅助索引的数据是部分的，这样导致还需要去一次书签查找，这样数据就是离散的，磁盘离散读取效率低。

哈希算法

时间复杂度是O(1)的。

哈希表

哈希表也是散列表，由直接寻址表改进而来。地址是根据数据的值来变化的，如果所有数据都使用直接寻址，数据值范围很大，那就需要一个而很大的表来存储数据地址表。

原理：

利用哈希函数，将数据进行哈希，映射到较小的一个空间槽位上。同时需要解决两个元素的哈希值映射到了同一个槽位上，这就是碰撞，简单的碰撞解决计数就是每个槽位上放一个链表，如果两个元素映射到一个槽位，那就去遍历这个链表，找到一个正确的值。

** 哈希函数**

哈希函数是将数据进行散列的。越好的哈希函数，可以将元素在槽上分布得更加平均，从而减少碰撞。数据库中一般采用除法散列得方法。也就是取余数。

关键字K映射到m个槽的某一个，那么哈希值就是：

h

(

k

)

=

k

m

o

d

e

m

h(k) = k \quad mode \quad mh(k)=kmodem

InnoDB中的哈希算法

同样采用链表解决碰撞。

采用除法散列，这个槽的数量很关键，一般是略大于缓冲池页数两倍的质数。

自适应哈希索引

引擎自己判断是否该建立哈希索引。

全文索引

全文索是将存储于数据库中的整本书或者文章中的任意内容信息进行查找的技术。

倒排索引

全文索引通过倒排索引进行实现。

倒排索引也是一种索引结构。通过辅助表中存储单词与单词自身在一个或者多个文档中的位置映射，进行联合查找。就绪一对多的映射，包括文档的编号和单词在文档中的位置。

InnoDB全文检索

实现原理

利用一个word字段，一个ilist字段，并在word字段上设置索引。ilist上防止了word的位置信息。

倒排索引将word存放到一张辅助表上，并通过全文索引缓存提高全文建所的性能。全文检索索引缓存是一个红黑树结构，根据(word，ilist)进行排序。这全文索引缓存有点像插入缓存，为了提高检索性能，先在内存上修改读取，然后再同步到磁盘上。

存在限制：

每张表只能有一个全文检索的索引。

由多列组合而成的全文检索的索引必须使用相同的字符集和排序规则。

不支持每个单词界定符的语言，比如中日韩。