MySQL索引

1.索引简介

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录

总结:
索引是帮助MySQL高效获取数据的排好序的数据结构

2.索引的数据结构-B+Tree

2.1假设使用二叉树作为索引结构

从上图中可以看出,为 user 表建立了一个二叉查找树的索引

圆为二叉查找树的节点，节点中存储了键（key）和数据（data）。键对应 user 表中的 id，数据对应 user 表中的行数据

二叉查找树的特点就是任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。顶端的节点我们称为根节点，没有子节点的节点我们称之为叶节点

例如:
查找 id=12 的用户信息，利用我们创建的二叉查找树索引，查找流程如下：

1. 从根节点出发,比对12和根节点的值,发现12大于根节点,则往右子节点出发继续比对
2. 比对12和当前右子节点的的值13,发现小于当前的节点.则往当前节点的左子节点出发继续比对
3. 把 12 和当前节点的键值 12 对比，12 等于 12，满足条件，我们从当前节点中取出 data，即 id=12，name=xm

**结论:**查找id=12 的用户信息,在二叉树结构下,需要3次IO.如果走全表扫描的话,需要6次IO.

可能会出现的问题:
假设索引键是递增的,则会出现链表结构:

2.2假设使用平衡二叉树作为索引结构

相对于二叉树,平衡二叉树保证了树的构造是平衡的,有效防止了链表的产生.当我们插入或删除数据导致不满足平衡,导致二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。

可能会出现的问题:
平衡二叉树(AVL树)是严格的平衡二叉树，所有节点的左右子树高度差不能超过1.AVL树查找、插入和删除在平均和最坏情况下都是O(lgn)

当插入数据时,最多需要1次旋转(单旋转或双旋转)
删除数据时,会导致树失衡,需要维护从被删除节点到跟节点这条路径上所有节点的平衡,旋转的量级为O(lgn)

2.3假设使用红黑树作为索引结构

与平衡二叉树(AVL树)相比,红黑树并不追求严格的平衡,而是大致的平衡:

确保从根节点到叶子节点的最长的可能路径不多余最短的可能路径的两倍长

红黑树每个节点都属于红色或黑色节点之一,且节点的颜色的划分需要满足特定的规则

与平衡二叉树(AVL树)相比,红黑树的查询效率会有所下降,是因为树的平衡性变差,高度更高.
红黑树的删除效率比较高,因为引入了颜色,当插入和删除数据时,只需要进行O(1)次数的旋转以及变色就能保证基本的平衡,不需要像平衡二叉树(AVL树)进行O(lgn)次数的旋转

可能会出现的问题:
当数据量过大时,会出现红黑树的高度过高的现象,导致磁盘IO的次数过大,影响性能.

2.4假设使用B 树（Balance Tree）作为索引结构

p节点为指向子节点的指针.图中的每个节点称为页，页就是读取数据时的磁盘块，在 MySQL 中数据读取的基本单位都是页.

B 树相对于平衡二叉树，每个节点存储了更多的键值（key）和数据（data），并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的 B 树为 3 阶 B 树，高度也会很低。

B树也称B-树(其中-不是减号)，是为磁盘等辅存设备设计的多路平衡查找树，与二叉树相比，B树的每个非叶节点可以有多个子树。
因此，当总节点数量相同时，B树的高度远远小于AVL树和红黑树(B树是一颗“矮胖子”)，磁盘IO次数大大减少。

对于一颗m阶B树，需要满足以下条件：

• 每个节点最多包含 m 个子节点
• 如果根节点包含子节点，则至少包含 2 个子节点；除根节点外，每个非叶节点至少包含 m/2 个子节点
• 拥有 k 个子节点的非叶节点将包含 k - 1 条记录
• 所有叶节点都在同一层中

例:查找 id=28 的用户信息的流程

• 先找到根节点也就是页 1，判断 28 在键值 17 和 35 之间，那么我们根据页 1 中的指针 p2 找到页 3
• 将 28 和页 3 中的键值相比较，28 在 26 和 30 之间，我们根据页 3 中的指针 p2 找到页 8
• 将 28 和页 8 中的键值相比较，发现有匹配的键值 28，键值 28 对应的用户信息为（28，bv）

B树的优势除了树高小，还有对访问局部性原理的利用。所谓局部性原理，是指当一个数据被使用时，其附近的数据有较大概率在短时间内被使用。B树将键相近的数据存储在同一个节点，当访问其中某个数据时，数据库会将该整个节点读到缓存中；当它临近的数据紧接着被访问时，可以直接在缓存中读取，无需进行磁盘IO；换句话说，B树的缓存命中率更高。

2.5索引结构 : B+ 树（Balance Tree）

B+ 树是对 B 树的进一步优化。B+ 树的结构图：

简化图:

与B树的区别:

① B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据

原因:
在数据库中页的大小是固定的，InnoDB 中页的默认大小是 16KB。

如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数又会再次减少，数据查询的效率也会更快

② 叶子节点包含所有索引字段,而且数据是按照顺序排列的.

那么 B+ 树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而 B 树因为数据分散在各个节点，要实现这一点是很不容易的

③ 叶子节点用指针连接，提高区间访问的性能

④ B树中一条记录只会出现一次，不会重复出现，而B+树的键则可能重复重现——一定会在叶节点出现，也可能在非叶节点重复出现。

与B树相比的优点:

• 更少的IO次数:B+树的非叶节点只包含键，而不包含真实数据，因此每个节点存储的记录个数比B数多很多（即阶m更大），因此B+树的高度更低，访问时所需要的IO次数更少。此外，由于每个节点存储的记录数更多，所以对访问局部性原理的利用更好，缓存命中率更高。
• 更适用于范围查询:在B树中进行范围查询时，首先找到要查找的下限，然后对B树进行中序遍历，直到找到查找的上限；而B+树的范围查询，只需要对链表进行遍历即可。
• 更稳定的查询效率:B树的查询时间复杂度在1到树高之间(分别对应记录在根节点和叶节点)，而B+树的查询复杂度则更稳定，因为所有数据都在叶节点。

2.6 总结

• 二叉查找树(BST)：解决了排序的基本问题，但是由于无法保证平衡，可能退化为链表
• 平衡二叉树(AVL)：通过旋转解决了平衡的问题，但是旋转操作效率太低
• 红黑树：通过舍弃严格的平衡和引入红黑节点，解决了AVL旋转效率过低的问题，但是在磁盘等场景下，树仍然太高，IO次数太多
• B树：通过将二叉树改为多路平衡查找树，解决了树过高的问题
• B+树：在B树的基础上，将非叶节点改造为不存储数据的纯索引节点，进一步降低了树的高度；此外将叶节点使用指针连接成链表，范围查询更加高效

3.聚集索引和非聚集索引

3.1 聚集索引

以 InnoDB 作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。

这是因为 InnoDB 是把数据存放在 B+ 树中的，而 B+ 树的键值就是主键，在 B+ 树的叶子节点中，存储了表中所有的数据。

这种以主键作为 B+ 树索引的键值而构建的 B+ 树索引，称之为聚集索引。

3.2 非聚集索引

以主键以外的列值作为键值构建的 B+ 树索引，称之为非聚集索引。

非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。