mysql索引的个人理解

• 索引是什么？
• 索引有哪些分类
• 总结

索引是辅助存储引擎高效获取数据的一种数据结构。

我们经常从以下几个方面对索引进行分类

从数据结构的角度对索引进行分类

1. B+tree

2. Hash

3. Full-texts索引

从物理存储的角度对索引进行分类

1. 聚簇索引

2. 二级索引(辅助索引)

从索引字段特性角度分类

1. 主键索引

2. 唯一索引

3. 普通索引

4. 前缀索引

从组成索引的字段个数角度分类

1. 单列索引

2. 联合索引（复合索引）

数据结构角度看索引

下表是MySQL常见的存储引擎InnoDB，MyISAM和Memory分别支持的索引类型

在实际使用中，InnoDB作为MySQL建表时默认的存储引擎

对上表进行横向查看可以了解到，B+tree是MySQL中被存储引擎采用最多的索引类型。

这里浅尝辄止的谈一下B+tree 与 Hash和红黑树的区别。

B+tree和B-tree

1970年，R.Bayer和E.Mccreight提出了一种适用于外查找的平衡多叉树——B-树，磁盘管理系统中的目录管理，以及数据库系统中的索引组织多数采用B-Tree这种数据结构。–数据结构C语言版第二版 严蔚敏

B+tree是B-Tree的一个变种。（哦，对了，B-tree念B树，它不叫B减树。。。）

B+tree只在叶子节点存储数据，而B-tree非叶子节点也存储数据。

因此，B+tree单个节点的数量更小，在相同的磁盘IO下能查询更多的节点。

另外B+tree叶子节点采用单链表链接适合MySQL中常见的基于范围的顺序检索场景，而B-tree无法做到这一点。

B+tree和红黑树

对于有N个叶子节点的B+tree，搜索复杂度为O(logdN) ,d是指degree是指B+tree的度，表示节点允许的最大子节点个数为d个，在实际的运用中d值是大于100的，即使数据达到千万级别时候B+tree的高度依然维持在3-4左右，保证了3-4次磁盘I/O就能查到目标数据.

从上图中可以看出红黑树是二叉树，节点的子节点个数最多为2个，意味着其搜索复杂度为O(logN),比B+树高出不少，因此红黑树检索到目标数据所需经理的磁盘I/O次数更多

B+tree索引与Hash表

范围查询是MySQL数据库中常见的场景，而Hash表不适合做范围查询，Hash表更适合做等值查询，另外Hash表还存在Hash函数选择和Hash值冲突等问题。

因为这些原因，B+tree索引要比Hash表索引有更广的适用场景。

物理存储角度看索引

MySQL中的两种常用存储引擎对索引的处理方式差别较大。

InnoDB的索引

首先看一下InnoDB存储引擎中的索引，InnoDB表的索引按照叶子节点存储的是否为完整表数据分为聚簇索引和二级索引。

全表数据就是存储在聚簇索引中的。

聚簇索引以外的其它索引叫做二级索引。

InnoDB表要求必须有聚簇索引，默认在主键字段上建立聚簇索引，在没有主键字段的情况下，表的第一个NOT NULL 的唯一索引将被建立为聚簇索引，在前两者都没有的情况下，InnoDB将自动生成一个隐式自增id列并在此列上创建聚簇索引。

说了聚簇索引和二级索引 肯定要提到回表查询和覆盖索引。

二次回表

由于二级索引的叶子节点不存储完整的表数据，所以当通过二级索引查询到聚簇索引的列值后，还需要回到局促索引也就是表数据本身进一步获取数据。

个人理解回表查询是指要查询的几列数据，在二级索引的叶子节点中并不存在或只存在一部分，这个时候会查询到叶子节点中数据的主键索引，然后通过这个索引，再次去表中查询。

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道在 InnoDB 存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。

MyISAM的索引

说完了InnoDB的索引，接下来我们来看MyISAM的索引

以MyISAM存储引擎存储的表不存在聚簇索引。

MyISAM索引B+tree示意图

MyISAM表中的主键索引和非主键索引的结构是一样的，从上图中我们可以看到

他们的叶子节点是不存储表数据的，节点中存放的是表数据的地址，所以MyISAM表可以没有主键。

MyISAM表的数据和索引是分开的，是单独存放的。

MyISAM表中的主键索引和非主键索引的区别仅在于主键索引B+tree上的key必须符合主键的限制，

非主键索引B+tree上的key只要符合相应字段的特性就可以了。

索引字段特性角度看索引

主键索引

• 建立在主键字段上的索引
• 一张表最多只有一个主键索引
• 索引列值不允许为null
• 通常在创建表的时候一起创建
• 唯一索引

建立在UNIQUE字段上的索引就是唯一索引

一张表可以有多个唯一索引，索引列值允许为null

普通索引

主键索引和唯一索引对字段的要求是要求字段为主键或unique字段，

而那些建立在普通字段上的索引叫做普通索引，既不要求字段为主键也不要求字段为unique。

前缀索引

前缀索引是指对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引，而不是在整个字段上建索引。

前缀索引可以建立在类型为

• char

• varchar

• binary

• varbinary

的列上，可以大大减少索引占用的存储空间，也能提升索引的查询效率。

索引列的个数角度看索引

建立在单个列上的索引为单列索引

建立在多列上的称为联合索引（复合索引）

索引就是一种便于数据库查询的数据结构，mysql数据库使用不同的引擎，生成的索引也有所不同，但总的来讲，使用索引可以大大提高数据的查询效率，但是当数据量过大或者表中数据改多读少的情况下，数据库每次插入和修改数据都会去维护索引，导致数据库压力过大，因此索引不能随便创建。