MySQL中的索引

索引的概述

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。如下图所示：

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。建立索引是数据库中用来提高性能的最常用的方式。

索引优劣势

建立了索引优点自然是提高了查找数据的效率，节省了时间，但缺点就是，首先也是占用了一定的空间，其次当进行一些表的更新操作时比方说插入，更新等，不仅要对表进行操作，对索引也需要进行维护和修改，就变得稍微麻烦了。

B+树

B+树 是 B树的变种，也就是一棵多路平衡搜索树，B+树 与 B树 的区别为：

• n叉B+树最多含有n个key，而 B树最多只能含有n - 1个key；

• B+树的叶子结点保存所有的key信息，依照key的大小顺序排列；

• 所有的非叶子节点都可以看作key的索引部分。
  

如上图，就是一颗典型的B+ Tree，一个节点拥有n个子节点，那么就拥有n个key。同时非叶子节点仅具有充当索引的作用，不存放跟记录有关的信息。树的所有叶子节点构成一个有序链表，可以按照key排序依次遍历全部记录。

MySQL中B+树

我们现在按上图的索引图要查找3这个元素，需要到几次磁盘IO呢？首先将磁盘块1加载到内存中，判断出3小于28，那么就会去锁定p1指针（这个比较过程以及指针的切换是非常快的，这个过程是发生在内存中，相较于IO操作可以忽略不计），将磁盘块2加载到内存中，继续判断，从而将磁盘块4也加载到内存中，此时为叶节点，叶节点存储数据，那么就只需要在当前的块中将3查找出来即可，整个过程只发生了3次磁盘IO，大大地提高了效率。

为什么是B+树而不是B树？

• 首先B+树的空间利用率更高（非叶节点没有data域），可减少IO次数，磁盘读写所耗费的代价更低；

• B+树的查询效率更加地稳定，B树搜索在非叶子节点还是叶子节点结束都有可能，约靠近根节点，查找效率越快；而B+树无论查找的是什么数据，最终都需要从根节点一直走向叶节点，所有查找所经过的次数都是一样的；

• B+树能同时支持随机检索和顺序检索，而B树只适合随机检索，顺序检索的效率比B+树低；

• 增删文件时，B+树的效率更高，因为所有的data都在叶子节点中，而B树删减节点时还需要分裂，中间节点向上等操作；

InnoDB存储引擎支持的常见索引：

• B+树索引
• 全文索引
• 哈希索引

InnoDB存储引擎支持的哈希索引是自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。

注意
另一个常常被DBA忽视的问题是:B+树索引并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

B+树索引

• 聚集索引

InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引(clustered index）就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接，另一点是每个页中的记录也是通过双向链表进行维护的。
由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外，由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。

• 辅助索引

对于辅助索引(Secondary Index，也称非聚集索引)，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签(bookmark)。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。
辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。举例来说,.如果在一棵高度为3的辅助索引树中查找数据，那需要对这棵辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到-一个完整的行数据所在的页，因此一共需要6次逻辑IO访问以得到最终的一个数据页。

• 联合索引

联合索引是指对表上的多个列进行索引。

• 覆盖索引

InnoDB存储引擎支持覆盖索引(covering index，或称索引覆盖)，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

哈希索引

哈希算法是一种常见算法，时间复杂度为О(1)，且不只存在于索引中，每个数据库应用中都存在该数据库结构。设想一个问题，当前服务器的内存为128GB时，用户怎么从内存中得到某一个被缓存的页呢?虽然内存中查询速度很快，但是也不可能每次都要遍历所有内存来进行查找，这时对于字典操作只需О(1）的哈希算法就有了很好的用武之地。

哈希表技术很好地解决了直接寻址遇到的问题，但是这样做有一个小问题，两个关键字可能映射到同一个槽上。一般将这种情况称之为发生了碰撞(collision)。在数据库中一般采用最简单的碰撞解决技术，这种技术被称为链接法( chaining)。
在链接法中，把散列到同一槽中的所有元素都放在一个链表中，槽j中有一个指针，它指向由所有散列到j的元素构成的链表的头;如果不存在这样的元素则j中为NULL。
最后要考虑的是哈希函数。哈希函数h必须可以很好地进行散列。最好的情况是能避免碰撞的发生。即使不能避免，也应该使碰撞在最小程度下产生。一般来说，都将关键字转换成自然数，然后通过除法散列、乘法散列或全域散列来实现。数据库中一般采用除法散列的方法。
在哈希函数的除法散列法中，通过取k除以m的余数，将关键字k映射到m个槽的某一个去，即哈希函数为:
h (k) = k mod m

• 自适应哈希索引

自适应哈希索引采用之前讨论的哈希表的方式实现。不同的是，这仅是数据库自身创建并使用的，DBA本身并不能对其进行干预。自适应哈希索引经哈希函数映射到一个哈希表中，因此对于字典类型的查找非常快速，但对于范围查找就无能为力了。

全文索引

全文检索（Full-Text Search）是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。
全文检索通常使用倒排索引(inverted index）来实现。倒排索引同B+树索引一样，也是一种索引结构。它在辅助表( auxiliary table）中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。