MySQL InnoDB索引原理详解

1 简介       

       索引（Index）是帮助MySQL高效获取数据的数据结构。我们知道，数据库查询是数据库的最主要功能之一。但每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

       索引一般以文件形式存储在磁盘上，索引检索需要磁盘I/O操作。所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的(例如树结构的选型和查找算法的实现)，本文主要讨论InnoDB的索引实现方式。

      当数据保存在磁盘类存储介质上时，它是作为数据块存放。这些数据块是被当作一个整体来访问的，这样可以保证操作的原子性。硬盘数据块存储结构类似于链表，都包含数据部分，以及一个指向下一个节点（或数据块）的指针，不需要连续存储。所以-- 可以说数据库必须有索引，没有索引则检索过程变成了顺序查找，O(n)的时间复杂度几乎是不能忍受的。另一篇文章也会介绍mysql中如何建立索引。MYSQL索引建立(innoDB)需要注意,某些操作会使得数据库放弃索引而进行全表扫描。导致引擎放弃使用索引而进行全表扫描的条件。

   2   在 InnoDB 中，索引使用的数据结构是 B+ Tree

                                     

       这里的 B 是 Balance 的意思。B 类树的一个很鲜明的特点就是树的层数比较少，而每层的节点都非常多，树的每个叶子节点到根节点的距离都是相同的（这也是为什么叫 Balance Tree 的原因）。另外，树的每一个节点都是一个数据页，这样每个节点只需要一次 IO 就可以全部读取。这样的结构保证了查询数据时能尽量少地进行磁盘 IO，同时保证 IO 的稳定性。B+ Tree 和 B Tree 不同，B+ Tree 中，只能将数据存储在叶子结点中，内部节点将只包含指针，而 B Tree 可以将数据存储在内部的叶节点中。因此 B+ Tree 的关键优势是中间节点不包含数据，因此 B+ Tree 的大小远小于 B Tree，并且可以将更多数据存储到存储器中。另外，B+ Tree 的每一个叶子节点包含了到相邻的节点的链接，这样可以快速地进行范围遍历。

为什么用B+ree 做索引

   1)  B+数是由B-数演变而来，所以B+数拥有B-数的所有特性
   2)  B+树的非叶子节点只保存关键字和子节点的地址，而叶子节点保留了当前路节点的所有节点的关键字、数据区和地址，所以要得到节点的数据就要到叶子节点上去获取，所以我们每次对数据的检索的时间都差不多，不像其他树，非叶子节点也有保留数据区，这样子当数据量庞大，当检索第一个跟最后一个的索引时间就相差比较大
   3)  B+树是一颗多路平衡查找树，由于它是多路的，所以它的高度比其他二叉树都矮，树的高度决定了检索数据的时间复杂度
计算机默认检索的一页是4k，而mysql对这个4k做了调整增加到16k，这个一页是16k，假如这里保存的是一个id的索引树，那id设置为int类型，一个int类型为4个字节，那这一页可以保存的id的个数就可以这样算（（16* 1024）/4），所以索引的类型和字节数都决定了数据库检索数据的效率，所以该id树的一个节点可以设置的路数就为（（16*1024）/4）路，所以这一页就可以保存这么多数据，一次加载到内存中就可以加载那么多，充分利用了计算机的IO读取性能和空间局部性原理，极大降低了计算机IO的次数
   4)  B+树的叶子节点上保存一个指针，这个指针指向的是下一个叶子节点的指针，譬如第一路的叶子节点上数据有567这三个树，而第二路有8910，则第一路的7有个指针会指向第二路的8，这样做的好处是使数据自带有顺序性的特性，这个顺序性在我们做一个范围查询时，性能就得到充分的发挥，这个指针也是B-树跟B+树的区别之一

MySQL 索引为什么要选用 B+ tree

  3 主索引和辅助索引
      在 InnoDB 存储引擎中，每一个索引都对应一棵 B+ Tree，InnoDB 的索引主要分为主索引和辅助索引：
      主索引：包含记录的文件按照某个 key 制定的顺序排序，这个 key 就是主索引，也就是主键，也被称为聚簇索引。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚集索引。在 InnoDB 中，主索引的叶子节点存的是整行数据，这也意味着 InnoDB 中的表一定要有一个主索引；
      辅助索引：某个 key 指定的顺序与文件记录的物理顺序不同，这个 key 就是辅助索引。InnoDB 中的辅助索引在叶子节点中并不存储实际的数据，只会包含主索引的值 。这就意味着如果使用辅助索引进行数据的查找，只能查到主索引，然后根据这个主索引再次扫描以下主索引的树，进行一次回表操作；
      上面讲到，InnoDB 的表中要求必须有一个主键，那么可能有人会将身份证号这种唯一性的标识作为主索引，这样就大错特错了。刚刚说到主键也被称为聚簇索引，它是要按照顺序进行排序的，要求有聚簇性。如果将身份证号作为主键，不能保证每次插入的数据都是按照身份证号的顺序进行排列的，这就使得每次主键的插入都变得完全随机，可能导致每次插入一条数据都会引起页分裂的问题。
      所以在表结构定义的时候，应该使用一个具有聚集性的 key 作为主键，如果真的没有的话，可以使用一个 AUTO INCREMENT 代理键作为主索引，这样可以保证数据行是顺序写入的。如果你真的完全没有定义主键，InnoDB 会选择一个唯一的非空索引代替，但是如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚集索引。

 正因为 InnoDB 索引的这种结构，产生了一些限制：

1 如果不是按照索引的最左列开始查找，则无法使用索引；
2 不能跳过联合索引中的某些列；
3 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找；

4 哈希索引

    InnoDB存储引擎使用哈希算法来查找字典，冲突机制采用链表，哈希函数采用除法散列。对于缓冲池的哈希表，在缓存池中的每页都有一个chain指针，指向相同哈希值的页。对于除法散列，m的值为略大于2倍缓冲池页数量的质数。如当前innodb_buffer_pool_size大小为10M，则共有640个16KB的页，需要分配1280个插槽，而略大于的质数为1399，因此会分配1399个槽的哈希表，用来哈希查询缓冲池中的页。 而对于将每个页转换为自然数，每个表空间都有一个space_id，用户要查询的是空间中某个连续的16KB的页，即偏移量（offset），InnoDB将space_id左移20位，再加上space_id和offset，即K=space_id<<20+space_id+offset，然后使用除法散列到各个槽中。

自适应哈希索

       自适应哈希索引采用之前讨论的哈希表的方式实现。不同的是,这仅是数据库自身创建并使用的,DBA本身并不能对其进行干预。自适应哈希索引经哈希函数映射到一个哈希表中,因此对于字典类型的查找非常快速,如 SELECT* FROM TABLE WHERE index col=xxx。但是对于范围查找就无能为力了。通过命令 SHOW ENGINE INNODB STATUS可以看到当前自适应哈希索引的使用状况。

5 全文索引

       通过前面章节的介绍,已经知道B+树索引的特点,可以通过索引字段的前缀( prefix)进行查找。例如,对于下面的查询B+树索引是支持的:SELECT FROM blog WHERE content like 'xXxs上述SOL语句可以查询博客内容以x开头的文章,并且只要 content添加了B+树索引,就能利用索引进行快速查询。然而实际这种查询不符合用户的要求,因为在更多的情况下,用户需要查询的是博客内容包含单词xxx的文章,即:SELECT FROM blog WHERE content like ' 8xxx8根据B+树索引的特性,上述SQL语句即便添加了B+树索引也是需要进行索引的扫描来得到结果。类似这样的需求在互联网应用中还有很多。例如,搜索引擎需要根据用户输入的关键字进行全文查找,电子商务网站需要根据用户的查询条件,在可能需要在商品的详细介绍中进行查找,这些都不是B+树索引所能很好地完成的工作。全文检索(Fu- Text Search)是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息,也可以进行各种统计和分析。在之前的 MySQL数据库中, InnoDB存储引擎并不支持全文检索技术，从InnoDB 1.2.x开始支持。

   MySQL · 引擎特性 · InnoDB 全文索引简介

   MySQL中InnoDB全文检索