MySQL进阶之：索引

什么是索引？

​ 官方定义：索引是帮助MySQL高校获取数据的排好序的数据结构；索引的定义就是帮助存储引擎快速获取数据的一种数据结构，形象的说就是索引是数据的目录。

索引的分类

可以从不同的角度将索引分为以下几类：

• 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
• 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
• 按「字段个数」分类：单列索引、联合索引。

接下来我们来讲讲各个索引的优点吧

按照数据结构分类

按照数据结构对索引进行分类的话，常见的有B+tree索引，Hash索引，而MySQL的Innodb存储引擎使用最多的索引类型是B+tree索引；为什么要使用B+tree而不使用例如二叉搜索树，B树诸如此类的树结构呢？首先我们来看看如果使用二叉搜索树来作为存储引擎会发生什么：

二叉搜索树

我们都知道，在创建表时，Innodb存储引擎会根据不同的场景选择不同的列作为索引

• 如果存在主键，默认会使用主键作为索引值
• 如果不存在主键，InnoDB会选择一个不包含NULL值的唯一列作为索引键
• 如果y以上两种情况都不存在的话。InnoDB就会自动维护一个隐式自增的row-id作为索引键

这里我们采取一个较为极端的情况：如果使用一个自增的键作为索引键，那么每次增加索引都是最大的，就会将其存储在树的最右边，如下图所示：

可以看出这样做的坏处就是整个二叉搜索树就退化成了一个链表，且高度非常高。

平衡二叉树

那么换成二叉平衡树呢？二叉平衡树保持了其左子树和右子树的高度差不超过 1，这样就不会出现树退化成链表这种问题了：

从图中可以看出，二叉平衡树通过保持树的平衡性和相对较小的高度，提供了更快的查找和插入操作，以及更稳定的性能表现，但是由于每个节点只能有两个子节点，还是会随着数据量增大，高度不断增大，如果我们的数据库存储10000条数据，那么需要大约15层，也就是说如果要查询到最下面一层数据的话需要进行15次磁盘IO，性能极低，那么有没有一种办法可以一个节点下面有三个，四个甚至多个子节点呢？这样就可以大大减少磁盘IO的次数，提高性能了。那么就是B树；

B树

平衡二叉树虽然能保持查询操作的时间复杂度在O(logn)，但是因为它本质上是一个二叉树，每个节点只能有 2 个子节点，那么当节点个数越多的时候，树的高度也会相应变高，这样就会增加磁盘的 I/O 次数，从而影响数据查询的效率。

为了解决降低树的高度的问题，就引出了 B 树，它不再限制一个节点就只能有 2 个子节点，而是允许 M 个子节点 (M>2)，从而降低树的高度。我们以最大节点数为3的B树为例：

可以看出，由于增加了各节点的子节点数，树的高度也会降低，所以B树在数据查询中比平衡二叉树效率要高很多。

但是 B 树的每个节点都包含数据（索引+记录），而用户的记录数据的大小很有可能远远超过了索引数据，这就需要花费更多的磁盘 I/O 操作次数来读指定的索引数据。

而且，在我们查询位于底层的某个节点（比如 A 记录）过程中，「非 A 记录节点」里的记录数据会从磁盘加载到内存，但是这些记录数据是没用的，我们只是想读取这些节点的索引数据来做比较查询，而「非 A 记录节点」里的记录数据对我们是没用的，这样不仅增多磁盘 I/O 操作次数，也占用内存资源。

另外，如果使用 B 树来做范围查询的话，需要使用中序遍历，这会涉及多个节点的磁盘 I/O 问题，从而导致整体速度下降。

B+树

B+树就是对B树进行了一个升级，不再非叶子节点中存储数据，其特性如下：

• 非叶子节点不存储data，只存储索引（冗余），可以放更多的索引
• 叶子节点包含所有的索引字段
• 叶子节点用指针连接，提高区间访问的性能

由于B+树只在叶子节点中存储有效数据，则在一个B+树中存储千万级的数据只需要三阶，也就是说在一千万个数据中查找一条数据，只需要进行三次磁盘IO，性能非常的快；且B+ 树有大量的冗余节点，这些冗余索引让 B+ 树在插入、删除的效率都更高，比如删除根节点的时候，不会像 B 树那样会发生复杂的树的变化；

由于每个B+树的子节点之间由双指针互相连接，因此B+树也支持范围查找。

Hash索引

Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。

但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因

按照物理存储分类

从物理存储的角度来讲，索引分为聚簇索引和二级索引：

聚集索引：叶子节点包含了完整的数据记录（Innodb）非聚集索引：MyISAM索引文件和数据文件是分离的，所以是非聚集索引

那么是聚集索引查找速度快还是非聚集索引速度快？

一般来说还是聚集索引查找的速度更快一些，因为聚集索引在查找到该索引时就可以直接拿到相关的其他列的数据，而非聚集索引在找到指定索引后还需要进行一次回表操作，才能拿到所有的数据。

为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？

由于表数据文件本身是按照B+Tree组织的一个索引结构文件，如果在表中没有设置主键，那么就无法创建一个B+Tree将表数据进行存储。此时MySQL就会逐列查找表中所有数据，找到能建立唯一索引的那一列，使用该列来组织整张表的其他所有数据，并且构建一个B+树；如果没有找到就会自动帮我们维护一个隐藏列（rowid），使用该隐藏列创建B+树，这样的话会加大MySQL的工作量；

显而易见，两个整型的数比价大小速度比两个字符串类型比较大小快。

按照字段特性分类

从字段特性角度来讲的话，索引可以分为主键索引、唯一索引、普通索引、前缀索引。

• 主键索引：建立在主键上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，且不允许有空值。
• 唯一索引：建立在UNIQUE字段上的索引，一张表可以有多个唯一索引，允许有空值，但索引列的值必须唯一。
• 普通索引：建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE。
• 前缀索引：
• 对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。
  • 使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。