mysql索引的内部实现原理_面试官：聊一下你对MySQL索引实现原理!

在数据库中，若是索引太多，应用程序的性能可能会受到影响，若是索引太少，又会对查询性能产生影响。因此，咱们要追求二者的一个平衡点，足够多的索引带来查询性能提升，又不由于索引过多致使修改数据等操做时负载太高。面试

文章会从，B+树索引，索引的分类，哈希索引，全文索引，这个几个方面讲解算法

B+树索引sql

索引的查找

索引的插入

索引的删除

索引的分类数据库

汇集索引

辅助索引

联合索引

覆盖索引

哈希索引数组

哈希算法

自适应哈希索引

全文索引缓存

倒排索引

全文检索索引缓存

全文索引的一些限制

InnoDB支持3种常见索引，咱们接下来要详细讲解的就是 B+ 树索引，哈希索引，全文索引。bash

B+树索引

一、B+树中的B不是表明的二叉(Binary) ，而是表明平衡(Balance)，由于B+树是从最先的平衡二叉树演化而来，可是B+树不是一个二叉树。数据结构

二、B+树是为磁盘或其余直接存取辅助设备设计的一种平衡查找树，在B+树中，全部的记录节点都是按照键值大小顺序存在同一层的叶子节点，由叶子节点指针进行相连。函数

三、B+树在数据库中的特色就是高扇出，所以在数据库中B+树的高度通常都在2~4层，这也就是说查找一个键值记录时，最多只须要2到4次IO,当前的机械硬盘每秒至少能够有100次IO，2~4次IO意味着查询时间只须要0.02~0.04秒。性能

四、B+树索引并不能找到一个给定键值的具体行，B+树索引能找到的只是被查找的键值所在行的页，而后数据库把页读到内存，再内存中进行查找，最后找到要查找的数据。

五、数据库中B+树索引能够分为，汇集索引和非汇集索引，可是不论是汇集索引仍是非汇集索引，其内部都是B+树实现的，即高度是平衡的，叶子节点存放着全部的数据，汇集索引和非汇集索引不一样的是，叶子节点是否存储的是一整行信息。每张表只能有一个汇集索引。

六、B+树的每一个数据页(叶子节点)是经过一个双向链表进行连接，数据页上的数据的顺序是按照主键顺序存储的。

先来看一个B+树，其高度为2，每页能够放4条记录，扇出为5。

图：一颗高度为2的B+树

索引的查找

B+树索引使用二分法查找，也称折半查找法，基本思想就是：将记录有序化(递增或递减)排列，在超找过程当中采用跳跃式方式查找，既先以有序数列的中心点位置比较对象，若是要查找的元素小于该元素的中心点元素，则将待查找的元素缩小为左半部分，不然为右半部分，经过一次比较，将查找区间缩小一半。

如图所示，从有序列表中查找 48，只须要3步：

图：二分法查找

索引的插入

B+树的查找速度很快，可是维护一颗平衡的B+树代价就是很是大的，一般来讲，须要1次或者屡次左旋右旋来保证插入后树的平衡性。

B+树的插入为了保持树的平衡，须要作大量的页(叶子节点)的拆分，页的存储基本都在磁盘，页的拆分意味着磁盘的操做，因此应该尽可能减小页的拆分，在采用自增加ID，做为主键，会大量的减小页的拆分，提高的性能。

B+树 插入的三种状况

图：一颗高度为2的B+树

咱们用实例来分析B+树的插入。

一、咱们插入28这个键值，发现当前Leaf Page和Index Page都没有满，咱们直接插入就能够了。

二、此次咱们再插入一条70这个键值，这时原先的Leaf Page已经满了，可是Index Page尚未满，符合表(B+树 插入的三种状况)的第二种状况，这时插入Leaf Page后的状况为50、5五、60、6五、70。咱们根据中间的值60拆分叶节点。将中间节点放入到Index Page中。

三、由于图片显示的关系，此次我没有能在各叶节点加上双向链表指针。最后咱们来插入记录95，这时符合表(B+树 插入的三种状况)讨论的第三种状况，即Leaf Page和Index Page都满了，这时须要作两次拆分。

能够看到，无论怎么变化，B+树老是会保持平衡。可是为了保持平衡，对于新插入的键值可能须要作大量的拆分页(split)操做，而B+树主要用于磁盘，所以页的拆分意味着磁盘的操做，应该在可能的状况下尽可能减小页的拆分。所以，B+树提供了旋转(rotation)的功能。

索引的删除

B+树使用填充因子(fill factor) 来控制树的删除变化，50%是填充因子可设的最小值，B+树的删除也一样必须保证删除后树的平衡性，删除的过程当中会涉及合并叶子节或兄弟节点，可是都是为了保持树的平衡。

索引的分类

在了解B+树索引的本质和实现后，咱们看看索引分为几类，汇集索引，辅助索引，联合索引，覆盖索引

汇集索引

就是按照每张表的主键构造一颗B+树，同时叶子节点存储整张表的行记录数，也将汇集索引的叶子节点成为“数据页”，汇集索引的特性决定了表中的行记录数据也是索引的一部分。同B+树数据结构同样，每一个数据页都经过一个双向链表进行连接。

数据页只能按照一颗B+树进行排序，所以每张表只能有一个汇集索引，因为数据页定义了逻辑顺序，汇集索引可以很快的在数据页访问指针进行范围的查找数据。

汇集索引在物理上不是连续的，在逻辑上是连续的，前面已经说过是经过双向链表进行维护，物理存储能够不按照主键顺序存储。

辅助索引

辅助索引(也称非汇集索引)，叶子节点并不包含行记录的所有数据。叶子节点除了包含键值外，每一个叶子节点还包含了一个书签，该书签告诉InnoDB 存储引擎能够从哪里找到辅助索引相对应行的记录。所以InnoDB 存储引擎的辅助索引的书签就是相应整行数据的汇集索引键。

一个表中能够有多个辅助索引。例如，一个辅助索引树须要遍历3次才能找到主键索引，若是汇集索引树的高为一样为3，那么它还须要对汇集索引树进行三次查找，最终才能找到一个完整的数据页，所以一共须要6次IO访问才能获得最终的数据页。

联合索引

联合索引是指对表上多个列进行创建索引，联合索引本质仍是一颗B+树，不一样的是索引的键值数量不是1个，而是大于等于2。联合索引的键值在B+树中也是有序的，经过叶子节点能够在逻辑的顺序上读出全部数据。

覆盖索引

InnoDB存储引擎支持覆盖索引(或称索引覆盖)，就是从辅助索引中就能够直接获得查询的记录，而不须要再次查询汇集索引中的记录。使用覆盖索引的好处就是，辅助索引不包括整行记录的全部信息，因此覆盖索引的大小要小于汇集索引，所以能够减小IO操做。

通俗的解释：

覆盖索引是非汇集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的全部列(即创建索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也就是索引包含了查询正在查找的全部数据)

哈希索引

学习哈希索引以前，咱们先了解一些基础的知识：哈希算法。哈希算法是一种经常使用的算法，时间复杂度为 O(1)。它不只应用在索引上，各个数据库应用中也都会使用。

哈希算法

InnoDB存储引擎使用哈希算法来对字典进行查找，哈希碰撞采用转链表解决，哈希函数采用除法散列方式。

例如：当前参数InnoDBbufferpool_size大小为10M，则共有640个16k的页，对于缓冲页内存的哈希表来讲，须要分配640×2=1280个槽，可是因为1280不是质数，因此须要取比1280更大的一点的质数，应该是1399，因此启动的时候，会分配1399个槽的哈希表，用来哈希查询所在的缓冲池中的页。

InnoDB存储引擎是经过除法散列到1399个其中的一个槽中。

自适应哈希索引

自适应哈希索引采用以前说的哈希表方式，不一样的是哈希索引对字典类型的等值查找很是快，对范围查询就无能为力了。

因此说哈希索引只能用于搜索等值查询，范围查询是不能使用哈希索引。

全文索引

以前已经说过，B+树索引的特色，对于使用以下sql，是支持B+树索引的，只要content 加了B+树索引，就能利用索引进项快速查询。

咱们经过 B+ 树索引能够进行前缀查找，如：

select * from blog where content like

'xxx%'

;

复制代码

只要为content列添加了B+树索引(汇集索引或辅助索引)，就可快速查询。但在更多状况下，咱们在博客或搜索引擎中须要查询的是某个单词，而不是某个单词开头，如：

select * from blog where content like

'%xxx%'

;

复制代码

此时若是使用B+树索引依然是全表扫描，而全文检索(Full-Text Search)就是将整本书或文章内任意内容检索出来的技术。

根据B+树索引的特色是不支持的，InnoDB存储引擎从1.2.x开始支持全文索引技术，其特性支MyISAM的所有功能。

具体实现原理接下来会介绍

倒排索引

全文检索使用倒排索引来实现，倒排索引同B+树索引同样，也是一种数据结构，它在辅助表中存储了单词与单词自身在一个或多个文档中所在位置的映射，这一般利用关联数组实现。

倒排索引它须要将分词(word)存储在一个辅助表(Auxiliary Table)中，为了提升全文检索的并行性能，共有6张辅助表。辅助表中存储了单词和单词在各行记录中位置的映射关系。它分为两种：倒排文件索引，详细倒排索引

一、inverted file index(倒排文件索引)，表现为{单词，单词所在文档ID}

二、full inverted index(详细倒排索引)，表现为{单词，(单词所在文档ID, 文档中的位置)}

全文检索表

DocumentIDText1Souyunku Technical team 2Go Technical stack

inverted file index(倒排文件索引)-辅助表存储为

倒排文件索引类型的辅助表存储为：

Documents表现为{单词，单词所在文档ID}

NumberText 分词Documents 1Souyunku12Technical1，23team14Go25stack2

full inverted index( 详细倒排索引)-辅助表存储为

详细倒排索引类型的辅助表存储为，占用更多空间，也更好的定位数据，比提供更多的搜索特性：

Documents表现为{单词，(单词所在文档ID, 文档中的位置)}

NumberTextDocuments

1Souyunku1:12Technical1:2 ，2:23team1:34Go2:15stack2:3

全文检索索引缓存

辅助表是存在与磁盘上的持久化的表，因为磁盘I/O比较慢，所以提供FTS Index Cache(全文检索索引缓存)来提升性能。FTS Index Cache是一个红黑树结构，根据(word, list)排序，在有数据插入时，索引先更新到缓存中，然后InnoDB存储引擎会批量进行更新到辅助表中。

当数据库宕机时，还没有落盘的索引缓存数据会自动读取并存储，配置参数innodbftcache_size控制缓存的大小，默认为32M，提升该值，能够提升全文检索的性能，但在故障时，须要更久的时间恢复。

在删除数据时，InnoDB不会删除索引数据，而是保存在DELETED辅助表中，所以一段时间后，索引会变得很是大，能够经过optimize table命令手动删除无效索引记录。若是须要删除的内容很是多，会影响应用程序的可用性，参数innodbftnumwordoptimize控制每次删除的分词数量，默认为2000，用户能够调整该参数来控制删除幅度。

全文索引的一些限制

一、如今只支持myisam和innodb

二、不支持分区表

三、多列组合的全文检索索引必须使用相同的字符集和字符序

四、象形文字不支持。须要ngram来分词

五、创建全文索引的各个字段必须统一

六、match()里的查找列，必须是在fulltext索引里定义过的

七、against()必须为字符串且为常量

八、索引提示会更差

九、在innodb中，全部涉及到全文索引列的DML操做(update，insert，delete)，只会在事务提交的时候，执行。中间可能要分词，标记等

十、不能用 % 通配符

十一、不支持没有单词界定符 (delimiter)的语言，如中文、日语、韩语等