Mysql索引与算法

书籍推荐：《Mysql技术内幕 InnoDB存储引擎》

InnoDB存储引擎支持三种索引：

1. B+树索引
2. 全文索引
3. 哈希索引

一. B+树索引

B指的是balanced，B树是在二叉平衡树的基础上修改的，是一种多路平衡查找树，它的每一个节点最多包含K个孩子，K被称为B树的阶。K的大小取决于磁盘页的大小。【m/2 <= K <=m】

之所以没有使用二叉搜索树是为了减少磁盘IO的次数。
索引都保存在磁盘上，数据量比较大的时候，索引的大小可能有几个G甚至更多。当我们需要利用索引查询的时候，不能把整个索引全部加在到内存，只能逐一加载每一个磁盘页。这里的磁盘页对应着索引数的节点。当二叉查找数是索引树，假设高度是4，最坏的情况，磁盘IO次数就是树的高度。

能查找到的是被查找数据行所在的页 ，接着数据库把页读入内存，再在内存中进行查找，最后得到要查找的数据。

B-树：

B+树：

二者的差别在于：

1. B+树的根节点以及中间节点都是索引，不包含数据，叶子结点包含了所有数据，包括指向下一个元素的链表指针；B-树的根节点以及中间节点是数据的一部分。
2. B+树的中间节点包含k个元素，B-树包含k-1个元素。
3. 因为B+树的中间节点只是索引，没有数据，所以同样的磁盘页可以容纳更多的节点元素，即同样的数据量情况下，B+树查询的io次数更少。
4. B+树的单点查询性能更稳定，都要查询到叶子结点；B-树可能是根结点一次找到也可能查到叶子结点。
5. B+树的范围查找直接通过链表指针；B-树的查找是通过中序遍历，效率低。

这里我们再详细聊聊B+树：

1. 插入
   
   需要补充的是leaf page已经满，但左右兄弟节点没有满的情况下，不会拆分页，而是将记录转移到所在页的兄弟节点上。

实际上索引的分裂要更加复杂，会根据Page Header中的

• PAGE_LAST_INSERT
• PAGE_DIRECTION
• PAGE_N_DIRECTION

来决定分裂的方向以及分裂点记录。

2. 删除

B+树使用填充因子(fill factor)来控制树的删除变化。

3. 索引

聚集索引中叶子结点存储的是完整的行记录，辅助索引中存储的是聚集索引的key。

聚集索引的叶子结点是靠双向链表连接的，所以是逻辑连续存储数据的。

辅助索引的增删在innoDB1.0.x版本后，不需要创建表，增加只需对创建删除索引的表加一个S锁(读锁)，删除只需更新内部视图，将索引空间标记为可用；聚集索引的增删需要重建表。

cardinality值为索引中不重复记录数量的预估值，该值和总记录数量的比值越接近1，代表数据的重复率低，更有必要去建立索引。对于cardinality低的字段，比如性别，就不需要去建立索引了。

4. 联合索引

本质上来说也是一棵B+树。会先按照第一个索引排序，再按照第二个索引排序。所以索引(a,b)可以替代索引a但不能替代索引b。好处是已经对后几个key进行了排序，可以减少order的次数。

联合索引无法替代单个索引，因为联合索引消耗的空间更多，一个页能放下的行更少。当二者都存在时，优化器会选取适合的索引。

5. 覆盖索引

从辅助索引中得到查询的结果。好处是辅助索引存储的是聚集索引中的key，空间相对来说更小，所以存储的数据会更多，io更少。

二. 全文检索

全文索引通常使用倒排索引来实现，elastic search中也是使用倒排索引实现的，具体的实现在链接中。

稍有不同的是，其拥有两种表现形式：

• inverted file index: value是单词所在文档id
• full inverted index(ilist): value是(单词所在文档id，在具体文档中的位置)

key都是单词。

后者占用更多的空间，但能更好定位位置，并扩展其他的搜索特性。

在搜索的时候，会将word存储到一张表中，该表称为辅助表，持久化在磁盘上，在innoDB引擎中共有6张辅助表以提高并行性能。

缓存是使用的FTS Index Cache，底层是红黑树，根据(word, list)。类似于insert buffer，功能就是缓存全文索引的更新，当缓存过大或者要进行全文搜索的时候，将缓存与辅助表合并，从而减少合并次数。

三. 哈希索引

散列方式：除法散列
哈希值：space_id<<20+space_id+offset
自适应哈希索引不能被dba干预。