浅谈MySQL索引分类

本篇我将为大家讲解MySQL的索引分类，这也是当初困惑我的一个点，看完本篇文章相信你会对MySQL的索引分类有一个更好的认识。

根据具体类型分类

在MySQL数据库，将索引具体类型主要分为以下几类：

  1. 主键索引
  2. 唯一索引
  3. 常规索引
  4. 全文索引

img

其中唯一索引当你给这个字段添加唯一约束的时候，inoodb会自动给这个字段添加唯一索引

你可能会问，为啥没联合索引啊，我记得还有一个多个字段的索引的啊。

可以看看创建索引的语法：CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,... ) ; 可以发现，创建联合索引的时候可以指定是唯一还是常规还是全文。也就是说联合索引包括在了唯一索引，常规索引，全文索引之中。

根据存储形式分类

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

img

聚集索引选取规则:

  • 如果存在主键，主键索引就是聚集索引。
  • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
  • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引（也就是说这个表必须得有聚集索引。什么都没有的话数据库会给你创建）。

聚集索引和二级索引的具体结构如下： img

聚集索引的叶子节点下挂的是这一行的所有数据 。

二级索引的叶子节点下挂的是该索引的字段值对应的主键值。

接下来，我们来分析一下，当我们执行如下的SQL语句时，具体的查找过程是什么样子的。

img

具体过程如下:

①. 由于是根据name字段进行查询，所以先根据name=’Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。

②. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row（这种操作叫回表查询）。

③. 最终拿到这一行的数据，直接返回即可。

回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

根据建索引的列数进行分类

单列索引：即一个索引只包含单个列。

联合索引：即一个索引包含了多个列。

我们先来看看 tb_user 表中目前的索引情况: img在查询出来的索引中，既有单列索引，又有联合索引。

接下来，我们来执行一条SQL语句，看看其执行计划：

img

通过上述执行计划我们可以看出来，在and连接的两个字段 phone、name上都是有单列索引的，但是最终mysql只会选择一个索引，也就是说，只能走一个字段的索引，此时是会回表查询的。

紧接着，我们再来创建一个phone和name字段的联合索引来查询一下执行计划。 

create unique index idx_user_phone_name on tb_user(phone,name)

img

此时，查询时，就走了联合索引，而在联合索引中包含 phone、name的信息，在叶子节点下挂的是对 应的主键id，所以查询是无需回表查询的。

所以： 在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引， 而非单列索引。

如果查询使用的是联合索引，具体的结构示意图如下：

img