前言

上一节我们介绍了索引底层数据结构，以及MySQL为什么采用B+Tree作为底层数据结构；接下来我们了解一下MySQL不同存储引擎（InnoDB和MyISAM）下的索引分类以及索引的区别。

一、InnoDB存储引擎下的索引

1、InnoDB索引简介

以InnoDB作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。这是因为InnoDB是把数据存放在B+Tree中的，而B+Tree的键值就是主键，在 B+Tree的叶子节点中，存储了表中所有的数据。这种以主键作为B+Tree索引的键值，叶子节点存储的数据是整行记录而构建的B+Tree索引，我们称之为聚集索引，又叫聚簇索引

一般情况下，聚簇索引等同于主键索引。除聚簇索引之外的所有索引都称为辅助索引。在InnoDB存储引擎中，辅助索引中的叶子节点存储的数据都是该行的主键值。

这里我们来介绍一下InnoDB存储引擎创建索引的规则：

a、如果表中定义有主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。

b、如果表没有定义主键，InnoDB会选择第一个不为NULL的且值唯一的列用作聚簇索引。

c、如果以上两个都没有，InnoDB会使用一个6字节长整型的隐式字段ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

下面我们来看一下InnoDB引擎下的索引分类，为了方便介绍，我们先来创建一张以InnoDB为引擎的表t_user。

表结构如下：

CREATE TABLE `t_user` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

PRIMARY KEY (`id`) USING BTREE,

KEY `index_name` (`name`) USING BTREE

) ENGINE=InnoDB;

插入以下示例数据：

insert into t_user values(15,'Bob',34);

insert into t_user values(18,'Alice',77);

insert into t_user values(20,'Jim',5);

insert into t_user values(30,'Eric',91);

insert into t_user values(49,'Tom',22);

insert into t_user values(50,'Rose',89);

2、主键索引

InnoDB的数据组织方式是聚簇索引。表数据文件本身就是按B+Tree组织的一个索引结构文件；叶节点包含了完整的数据记录。InnoDB的数据和索引存储在同一个文件中，比如示例表t_user的数据和索引会存储在t_user.ibd中。

InnoDB引擎下表t_user的主键索引结构图如下：

注意：InnoDB存储引擎要求表必须有一个主键索引(MyISAM可以没有)。

查询数据的过程：

1）、等值查询：比如查询id=30的那条数据。

a、从根节点开始检索，将根节点加载到内存，比较30<56，走左路。（第1次磁盘IO）；

b、将左子树节点加载到内存中，比较20<30<49，继续向下检索。（第2次磁盘IO）；

c、检索到叶子节点，将节点加载到内存中遍历，比较20<30，30=30。查找到值等于30的索引项，然后获取整行数据并返回给客户端。（第3次磁盘IO）。

2）范围查询：比如查询30<=id<50的数据。

a、从根节点开始检索，将根节点加载到内存，比较30<56，走左路；（第1次磁盘IO）

b、将左子树节点加载到内存中，比较20<30<50，向下检索；（第2次磁盘IO）

c、检索到叶子节点，将节点加载到内存中遍历比较20<30，30<=30<50；查找到值等于30的索引项，然后获取行数据并缓存到结果集中。（第3次磁盘IO）

d、根据叶子节点的指针向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较30<49<50，于是获取行数据缓存到结果集中。（第4次磁盘IO）

e、最后得到2条符合条件的数据，将查询结果集返给客户端。

由此我们可以看到，以上范围查询经过了2次磁盘IO+检索叶子节点数量。在主键索引中索引和数据是存储在一起的（即索引中直接存储了行数据），与在索引树叶子节点中存储数据的磁盘地址的方式相比，InnoDB在使用主键查询时不用再去磁盘中获取数据，可以快速获取行数据，所以聚簇索引通常可以节省磁盘IO操作。

3、辅助索引

先来看一下t_user表的辅助索（即以‘name’建立的二级索引）引图：

我们可以看到辅助索引有以下特点：

a、InnoDB的辅助索引的叶子节点存储的是主键值而非磁盘地址；

b、在检索时首先需要检索辅助索引得到对应的叶子节点上的主键值，然后根据主键值去主键索引树中检索需要的数据（这个过程又叫做回表查询）。

查询数据的过程：

1）等值查询：比如查询name=Bob的数据：

a、从根节点开始检索，将根节点加载到内存，按照ASCII码排序规则比较Bob<Woo，走左路；（第1次磁盘IO）

b、将左子树节点加载到内存中，按照ASCII码排序规则比较Bob<Eric，向下检索；（第2次磁盘IO）

c、检索到叶子节点，将节点加载到内存中从前往后遍历比较。（第3次磁盘IO）第一个Alice不符合要求，丢弃。第二个Bob：等于Bob，符合要求，获取对应的主键id=15，然后去主键索引树中检索id=15的数据放入结果集中。（回表查询：第4、5、6次磁盘IO）。

d、继续向后遍历底层叶子节点链表，将下一个节点加载到内存中，遍历比较。（第7次磁盘IO）第一个Eric，不符合要求，丢弃。查询结束；

e、最后将符合条件的1条数据，返回给客户端。

2）范围查询：比如查询Jim<=name<Tom的数据：

辅助索引的范围查询的过程和等值查询差不多，这里就不再详细介绍了，各位可以根据以上学到的查询过程自己分析一下。这里先简单介绍一下：先根据辅助索引检索到叶子节点找到第一个符合条件的索引项，然后向后遍历，直到遇到第一个不符合条件的索引项后终止。检索过程中需要取出符合条件的id值，依次到主键索引检索中找到对应数据放入结果集中，然后将查询结果返回客户端。

二、MyISAM存储引擎下的索引

1、MyISAM索引简介

MyISAM的索引和数据文件是分开存储的，索引一般存储在“.MYI”格式的文件中，数据存储在“.MYD”格式的文件中。（“.frm”格式文件中存储的是表结构）MyISAM索引树的叶子节点中存储的是索引所在数据行的磁盘地址。

同理为了方便介绍，我们先来创建一张以MyISAM为引擎的表m_user。

表结构如下：

CREATE TABLE `m_user` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`age` int(11) DEFAULT NULL,

PRIMARY KEY (`id`) USING BTREE,

KEY `index_name` (`name`) USING BTREE

) ENGINE=InnoDB;

插入以下示例数据：

insert into m_user values(15,'Bob',34);

insert into m_user values(18,'Alice',77);

insert into m_user values(20,'Jim',5);

insert into m_user values(30,'Eric',91);

insert into m_user values(49,'Tom',22);

insert into m_user values(50,'Rose',89);

2、主键索引

先来看一下MyISAM存储引擎下m_user表的主键索引图：

由此我们可以看到，MyISAM存储引擎下，m_user表的主键索引与InnoDB存储引擎下的t_user表的主键索引的唯一区别就是，m_user表的主键索引的叶子节点中存储的并不是数据行，而是数据对应的磁盘地址。

注意：表m_user的索引存储在m_user.MYI文件中，数据存储在m_user.MYD文件中。

查询数据的过程：

1）等值查询：查询id=30的数据

a、从根节点开始查找，将根节点加载到内存中，比较30<56，走左路；（第1次磁盘IO）

b、将左子树节点加载到内存中，比较20<30<49，向下检索；（第2次磁盘IO）

c、检索到叶子节点，将节点数据加载到内存中遍历，比较20<30，30=30；找到值等于30的索引项。（第3次磁盘IO）

d、从索引项中获取磁盘地址，然后到m_user.MYD文件中获取对应整行记录，并返回给客户端。（第4次磁盘IO）

2）范围查询：查询30<=id<50的数据

a、从根节点开始检索，将根节点加载到内存，比较30<56，走左路。（第1次磁盘IO）

b、将左子树节点加载到内存中，比较20<30<49，向下检索。（第2次磁盘IO）

c、检索到叶子节点，将节点加载到内存中遍历比较20<30，30<=30<50。查找到值等于30的索引项；获取磁盘地址并从数据文件中获取行记录缓存到结果集中；（第3、4次磁盘IO）

d、向后遍历叶子节点链表，将下一个节点加载到内存中，遍历比较，30<49<50，拿到磁盘地址从数据文件中获取行记录缓存到结果集中。（第5、6次磁盘IO）

e、然后继续向后遍历叶子节点链表，直至到达最后一个不满足条件终止；最后得到两条符合条件数据并返给客户端。

3、辅助索引

在MyISAM存储引擎下，辅助索引和主键索引的结构是一样的，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值是可以重复的。在查询数据时，由于辅助索引的键值不唯一，可能存在多个索引项中有相同的键值，所以即使是等值查询，也会先找到第一个符合条件的索引项，拿到地址后去m_user.MYD文件中找到数据，然后在叶子节点链表继续遍历符合条件的索引项，根据地址去m_user.MYD文件中获取数据。

三、联合索引

以上我们从不同的存储引擎方面分别介绍各自的索引情况，下面我们来介绍一下联合索引。联合索引，又叫组合索引，是基于多个字段（或列）创建的索引。联合索引是我们日常工作学习中最常用的索引。

1、联合索引的底层存储结构

看一张InnoDB引擎下的联合索引结构图：

该user表创建了一个基于age和money字段的联合索引，索引树节点中的索引项按照（age，money）的顺序从小到大排列，先按照age列排序，age列相同时按照money列排序，（当有多个列的联合索引时也以此规则进行排序）。在最底层的叶子节点中，如果两个索引项的age，money列都相同，索引项会按照主键id排序。所以组合索引的最底层叶子节点中不存在完全相同的索引项。

2、联合索引查询数据的过程

查找数据的过程：比如查找age=20 and money = 30的数据

a、从根节点开始检索，将根节点加载到内存，先比较age列，age=20，20<60，走左路；（第1次磁盘IO）

b、将左子树节点加载到内存中，先比较age列，20<30，走左路；（第2次磁盘IO）

c、达到叶节点，将节点加载到内存中从前往后遍历比较。（第3次磁盘IO）第一项（18,50,6）：先比较age列，age!=20不符合要求，丢弃。第二项（20,25,3）：先比较age列，age=20符合，然后比较money，但money!=30不符合条件，丢弃；

第三项（20,30,1）：age=20符合，然后比较money，也符合条件，然后取出对应的主键id值1并去主键索引里查找对应的数据；

第四项（30,10,2）：先比较age列，不符合条件，然后结束后续检索；

d、将得到的1条符合条件的数据结果集返回给客户端。

四、覆盖索引

前面我们在讲InnoDB存储引擎下的非主键索引（即二级索引或辅助索引）时，在查询数据的过程中，通常先需要检索辅助索引拿到叶子节点对应的主键值后，去主键索引里查询数据，也就是回表操作；回表操作也就意味着性能有所下降，也不是每次查询都必须要回表操作，那有没有一种只通过辅助索引就能查到我们想要的数据，避免每次都回表操作的方式呢？答案是肯定的，这就是覆盖索引（或索引覆盖），即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，提高了性能。

翻译一下就是：创建辅助索引所涉及的列（或字段）已经包含了select中的列（或字段）数据，也就是说索引树已经“覆盖”了select要查询的需求，这样MySQL就不用再回表查询，这就是覆盖索引。

举个例子：

一张以InnoDB为存储引擎的表t_index中有id（主键）、a、b、c、d三列，并且我们基于a、b、c三列建立了辅助索引，此时下列查询语句就会走覆盖索引：

a、select a from t_index where a = xxx;

b、select a、b from t_index where a = xxx and b = xxx;

c、select a、b、c from t_index where a = xxx and b = xxxand c = xxx;

d、select a、b、c、id from t_indexwhere a = xxx and b = xxx and c = xxx.

注意：覆盖索引是我们平时优化SQL语句时常用的一种手段。

索引的分类我们就介绍到这里了，接下来的文章我们会着重分析一下如何进行SQL优化以及如何利用索引来提高SQL的查询性能。

以上内容如有纰漏还请各位及时指正！