2.2 MySQL中的索引类型

MySQL 的索引，按具体作用划分，常用的为聚集索引、辅助索引、唯一索引和联合索引。

2.2.1 聚集索引

在InnoDB中，表中的数据是以B+树的形式存储的，这种存储了所有数据的B+树一般称为聚集索引。InnoDB通过主键聚集数据，如果没有定义主键，那么InnoDB会选择第一个非空的唯一索引代替，如果没有非空的唯一索引，那么InnoDB会隐式定义一个ROW ID代替。

聚集索引占用的空间最大，因为它保存了全部数据。

为了方便读者理解聚集索引，下面先创建一张测试表并写入数据：

查看表t1中的所有数据：

表t1的聚集索引建立在主键ID上，它的聚集索引的大致结构如图2-6所示。

从图2-6中可以看出，表t1的聚集索引的叶子节点存储的是整行数据。

2.2.2 辅助索引

辅助索引，也称为二级索引，单张表可以有多个。聚集索引的叶子节点存储的是整行数据，但是InnoDB辅助索引的叶子节点只存放对应索引字段的键值和主键ID。

有时需要统计表的总行数，此时优化器可能会选择辅助索引作为统计目标索引，因为它占用的空间最小。

在使用二级索引时，因为它只存储了索引字段的值和主键，所以如果需要查询其他列的数据，就需要先通过二级索引中的值找到对应的主键，再通过主键找到聚簇索引中其他列的数据。这个过程称为回表。

为了减少回表次数，可以将语句中经常使用到的所有列以合适的顺序建立一个二级联合索引（联合索引在2.2.4节中会详细讲解）。这样所有需要的列都被这个二级索引覆盖，就不需要回表。

当通过辅助索引来检索数据时，InnoDB 先遍历辅助索引树查找对应记录的主键，然后通过主键索引找到对应的行数据。继续用上面的表t1举例，它的辅助索引idx_a的结构大致如图2-7所示。

从图2-7中可以看出，idx_a根据a字段的值创建了B+树结构，并且每个叶子节点保存的是a字段自己的键值和主键ID。

通常采用以下几种方式创建辅助索引。

1.在建表的时候创建索引

例如，上面例子中的表t1：

其中，key`idx_a`（`a`）表示在a字段创建索引。

2.使用create index语句创建索引

使用表t1举例，为b字段添加索引的语句如下：

查看表t1上的索引：

如果要删除上面添加的索引，则可以执行如下语句：

3.使用alter table语句创建索引

使用表t1举例，为b字段添加索引的语句如下：

如果要删除上面添加的索引，则可以执行如下语句：

2.2.3 唯一索引

唯一索引是一个不包含重复值的二级索引（读者可以简单理解为：唯一索引由唯一约束和二级索引两部分组成）。为某个字段添加唯一索引之后，那么写入该字段的值必须是不同的，否则会报如下错误：

如果为唯一索引中的字段指定了前缀，那么字段的值的前缀的长度必须唯一，如下面的实验：

2.2.4 联合索引

有时普通索引已经无法满足我们的需求了，如单个字段唯一性很低，需要联合多个字段才能达到最优效果，这种由多个字段组成的二级索引称为联合索引。

联合索引适用于where条件中的多列组合，并且在某些场景中可以避免回表（本节会进行测试）。

为了方便读者理解联合索引，下面创建测试表t2，并写入测试数据，SQL语句如下：

在表t2上，联合索引idx_a_b的大致结构如图2-8所示。

从图2-8中可以看出，与单个键值的B+树的结构差不多，联合索引也是按照键值排序的。需要注意的是，当a字段和b字段都作为条件时，查询是可以使用索引的；单独对a字段进行查询也是可以使用索引的。但是单独对b字段进行查询就无法使用索引，因为在图2-8中的叶子节点上，b字段对应的值为c、b、a、e、i、g、f，显然是无序的，所以无法使用b字段的索引。下面通过实验验证这个结论。

当a字段和b字段都作为条件时：

key列的值为idx_a_b，表示使用了索引idx_a_b。key_len列的值为12，因为a字段是不允许为null的int类型，所以它的key_len列的值为4，因为b字段是不允许为null的char（2）类型，并且表的字符集为utf8mb4，所以它的key_len列的值为8，两者相加正好是12，这说明上面的SQL语句完整地使用了联合索引idx_a_b。

下面补充介绍key_len的计算方式。

explain中的key_len列用于表示在这次查询中所选择的索引长度有多少字节，常用于判断联合索引有多少列被选择了。表2-1总结了常用字段类型的key_len。

当只有a字段作为条件时：

key列的值为idx_a_b，表示使用了索引idx_a_b。key_len列的值为4，因为a字段是不允许为null的int类型，所以它的key_len列的值为4，说明上面的SQL语句使用了联合索引idx_a_b的a的索引。

当只有b字段作为条件时：

可以看到，key为null，Extra为Using where，所以上面的SQL语句没有使用任何索引。

其实覆盖索引也是一个重要的话题。所谓的覆盖索引就是从辅助索引中就可以查询到结果，不需要回表查询聚集索引中的记录。例如，下面的SQL语句：

上面的SQL语句直接通过idx_a_b就能查到记录，不用再通过主键回到聚集索引中查询记录（也就是不需要回表），可以减少SQL语句执行过程中的IO次数。

在实际工作中，如果某张表经常将某个字段作为条件查询另一个字段，那么可以考虑为这两个字段添加联合索引进行优化，使SQL的效率达到最大化。