3.InnoDB索引

1.索引的本质

索引是存储引擎用于快速检索记录行的一种数据结构，用于提升数据库的查找速度。

为什么需要使用索引：

①索引能极大的减少存储引擎需要扫描的数据量

②索引可以把随机I/O变为顺序I/O

③索引可以帮助我们进行分组、排序等操作时，避免使用临时表

索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要，在数据量较小且负载较低时，不恰当的索引对性能的影响可能还不明显，但当数据量逐渐增大时，性能则会急剧下降。

索引优化应该是查询性能优化最有效的手段了（可以起到立竿见影的效果），索引能够轻易将查询性能提高几个数量级，’最优’的索引有时比一个‘好的’索引性能要好两个数量级，创建一个真正’最优’的索引经常需要重写查询。

2.索引的类型

2.1B-Tree索引

当人们谈论索引的时候，如果没有特别指明类型，多半说的是B-Tree索引，它使用B-Tree数据结构（ InnoDB使用的是B+Tree ）来存储数据。大多数MySQL存储引擎都支持这种索引。

不同的存储引擎以不同的方式使用B-Tree索引，性能也各有不同，各有优劣，例如MyISAM使用前缀压缩技术使用索引更小，但InnoDB则按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

数据库索引为什么使用B+树？

二叉树

为什么不用二叉树做B-Tree索引底层数据结构？

①当数据量大时，树的高度会比较高（树的高度决定着它的IO操作次数，IO操作耗时大），查询会比较慢。

②每个磁盘块（节点/页）保存的数据太小（IO本来是耗时操作，每次IO只能读取到一个关键字，显然不合适）

没有很好的利用操作磁盘IO的数据交换特性，也没有利用好磁盘IO的预读能力（空间局部性原理），从而带来频繁的IO操作。

B树

B树的搜索：从根节点开始，对节点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子节点；重复，直到所对应的儿子指针为空,或已经是叶子节点。 关键字集合分布在整颗树中 ，即叶子节点和非叶子节点都存放数据，搜索可能在非叶子节点结束。其搜索性能等价于在关键字全集内做一次二分查找。

假设检索26，先把磁盘块1加载到内存中，然后26与28和46比较，26比28小，然后基于P1子节点引用，P1是指向磁盘块2的一个指针地址，基于P1引用可以通过顺序IO快速加载磁盘块2，然后26与19和23比，26大于23，通过P3子节点引用，加载磁盘块7。然后命中，基于节点数据区加载数据。

B树的特点：

①不再是二叉搜索，而是m叉搜索；

②叶子节点，非叶子节点，都存储数据；

③中序遍历，可以获得所有节点；

名词解释：

局部性原理：软件设计要尽量遵循 “数据读取集中”与“使用到一个数据，大概率会使用其附近的数据”，这样磁盘预读能充分提高磁盘IO；

磁盘预读能力：磁盘读写并不是按需读取，而是按页预读，一次会读一页的数据，每次加载更多的数据，如果未来要读取的数据就在这一页中，可以避免未来的磁盘IO，提高效率；

数据交换特性：操作系统去硬盘读取一次，做一次I/O交换，一次交换数据是4k（Linux默认页大小），交换单位以页为单位，1页就是4k（索引按数据页为单位读写的，在InnoDB中，每个数据页的大小默认是16KB）

B+树

它是B-Tree数的变体，也是一种多路搜索树B+Tree和B-Tree基本相同，区别在于B-Tree树非叶子节点和叶子节点都可以存放数据，而B+Tree树关键字存储在叶子节点上，非叶子节点不存真正的数据。（B+树中根到每一个节点的路径长度一样，因此查询速度更稳定；而B树不是这样）

叶子之间，增加了链表，获取所有节点，不再需要中序遍历，直接遍历叶子节点就行；

比如查找28，其实图顶端的28是索引，并不是真实数据，他会继续往下找。

B+Tree与B-Tree比较

①B+Tree范围查找，定位min与max之后，中间叶子节点，就是结果集，不用中序回溯；

②B+Tree磁盘读写能力更强（叶子节点不保存真实数据，因此一个磁盘块能保存的关键字更多，因此每次加载的关键字越多）

③B+Tree扫表和扫库能力更强（B-Tree树需要扫描整颗树，B+Tree树只需要扫描叶子节点）

2.2Hash索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

但是范围查找不适合，因为存储引擎都会为每一行计算一个hash码，hash码都是比较小的，并且不同键值行的hash码通常是不一样的，hash索引中存储的就是Hash码，hash 码彼此之间是没有规律的，且 Hash 操作并不能保证顺序性，所以值相近的两个数据，Hash值相差很远，被分到不同的桶中。这就是为什么hash索引只能进行全职匹配的查询，因为只有这样，hash码才能够匹配到数据。

在MySQL中，只有Memory引擎显示支持哈希索引，这是Memory引擎的默认索引，Memory引擎同时也支持B-Tree索引，指得一提的是，Memory引擎是支持非唯一哈希索引的，如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。HASH时间复杂度O(1)，链表时间复杂度是O(n)

InnoDB支持Hash索引吗？

①InnoDB用户无法手动创建哈希索引，这一层上说，InnoDB确实不支持哈希索引；

②InnoDB会自调优(self-tuning)，如果判定建立自适应哈希索引(Adaptive Hash Index, AHI)，能够提升查询效率，InnoDB自己会建立相关哈希索引，这一层上说，InnoDB又是支持哈希索引的；