MySQL中的聚簇索引、非聚簇索引、联合索引和唯一索引是什么

    一、索引类型

    索引根据底层实现可分为B-Tree索引和哈希索引,大部分时候我们使用的都是B-Tree索引,因为它良好的性能和特性更适合于构建高并发系统。

    根据索引的存储方式来划分,索引可以分为聚簇索引和非聚簇索引。非聚簇索引的叶子节点仅包含所有字段和主键ID,而聚簇索引的叶子节点则包含了完整的记录行。

    根据聚簇索引和非聚簇索引还能继续下分还能分为普通索引、覆盖索引、唯一索引以及联合索引等。

    二、聚簇索引和非聚簇索引

    MySQL聚簇索引、非聚簇索引、联合索引和唯一索引详解

    聚簇索引也叫聚集索引,它实际上并不是一种单独的索引类型,而是一种数据存储方式,聚簇索引的叶子节点保存了一行记录的所有列信息。也就是说,聚簇索引的叶子节点中,包含了一个完整的记录行。

    非聚簇索引也叫辅助索引、普通索引,它的叶子节点只包含一个主键值,通过非聚簇索引查找记录要先找到主键,然后通过主键再到聚簇索引中找到对应的记录行,这个过程被称为回表。

    例如一个包含了用户姓名和年龄的的数据表,假设主键是用户ID,聚簇索引的结构为(橙色的代表id,绿色是指向子节点的指针):

    叶子节点中,为了突出记录,把(id, name, age)区分开来了,实际上是连在一起的,它们是构成一条记录的整体。

    而一个非聚簇索引(以age为索引)的结构是:

    除了年龄字段本身之外,在该节点的叶子节点中,仅包含当前记录的主键ID,而不包含完整记录的信息。需要通过id号到聚簇索引中进行回表查询才能获取整行记录数据。

    在InnoDB中,每张表必须有一个聚簇索引,默认情况下会根据主键建立。如果表中没有主键,InnoDB会选择一个合适的列作为聚簇索引,如果找不到合适的列,会使用一列隐藏的列DB_ROW_ID作为聚簇索引。

    三、覆盖索引

    非聚簇索引中因为不含有完整的数据信息,查找完整的数据记录需要回表,所以一次查询操作实际上要做两次索引查询。如果每个索引查询都需要进行两次才能获得结果,那么这一定会导致效率下降,因为能够减少一次查询就应该减少一次。

    以上面的age索引为例,它是一个非聚簇索引,如果我想通过年龄查询用户的id,执行了下面一条语句:

    1

    select id from userinfo where age = 10;

    这种情况是否还有必要去回表?因为我只需要id的值,通过age这个索引就已经能拿到id了,如果还去回表一次不就做了无用的操作了吗?实际上确实是不需要的。当辅助索引已经包含了所有查询所需的信息时,在索引查询中就可以避免回表操作,这就是覆盖索引。

    四、联合索引

    联合索引指的是同时对多列创建的索引,创建联合索引后,叶子节点会同时包含每个索引列的值,并且同时根据多列排序,这个排序和我们所理解的字典序类似。

    例如对同时对上面的姓名和年龄创建的索引结构:

    (name, age)都是简写,想不出十几个名字。

    每个叶子节点同时保存了所有的索引列,除此之外,还是只包含了主键id。

    最左前缀匹配原则

    当对多列创建索引后,并不是只要包含了创建索引的列就能使用索引,索引的使用要遵循最左前缀匹配原则。

    假设对列(A, B, C)创建索引,那么只有以下场景能使用索引:

    • 对列(A, B, C)/(A, C)或者(A, B)进行查询会匹配索引,对(C, A)或者(B, C)来说不能使用索引。

    • 通配符只能使用LIKE '
      val%'
      形式,不能使用LIKE '
      %VAL%'
      ,后者会导致全表扫描。

    • 索引列不能进行运算,例如WHERE A + 1 = 5这种场景会导致索引失效。

    • 索引列不能包含范围值查询,如LIKE/BETWEEN/>
      /<
      等都会导致后面的列无法匹配索引。

    • 索引列不能包含有NULL值。

    索引下推

    新版本的MySQL(5.6以上)中引入了索引下推的机制:可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

    例如针对上面表中的(name, age)做联合索引,正常情况下的查询逻辑:

    • 通过name找到对应的主键ID

    • 根据id记录的列匹配age条件

    这种做法会导致很多不必要的回表,例如表中存在(张三, 10)和(张三, 15)两条记录,此刻要查询(张三, 20)的记录。查询时先通过张三定位到所有符合条件的主键ID,然后在聚簇索引中遍历满足条件的行,看是否有符合age = 20的记录。在实际情况中,没有符合条件的记录,因此这个回表过程可以看作是无功之举。

    索引下推的主要功能就是改善这一点,在联合索引中,先通过姓名和年龄过滤掉不用回表的记录,然后再回表查询索引,减少回表次数。

    五、唯一索引

    唯一索引是一种不允许具有相同索引值的索引,系统在创建该索引时检查是否有重复的键值,每次对更新或增加记录时都会检查这一点。主键索引就是唯一索引。



    MySQL是一种常用的关系型数据库管理系统,其中索引是提高数据库性能的重要手段。在MySQL中,常用的索引有聚簇索引、非聚簇索引、联合索引和唯一索引,下面将详细介绍它们的含义和使用场景。
    聚簇索引
    聚簇索引是在主键或唯一索引上创建的一种特殊索引,它指定了数据的物理排序方式,可以提高数据的访问速度。聚簇索引中记录的排列顺序与磁盘上的物理存储顺序相同,因此可以在聚簇索引中按照数据的物理顺序存储数据,这样可以大大提高数据访问速度。但是,聚簇索引也有一些缺点,如插入速度较慢,占用磁盘空间较大等。
    非聚簇索引
    非聚簇索引是在非唯一索引或普通索引上创建的一种索引,它仅仅指向记录的物理地址,而不是物理排序方式。因此,非聚簇索引并不能直接用于数据的物理存储和检索。与聚簇索引相比,非聚簇索引具有插入速度快、占用磁盘空间小等优点。但是,由于非聚簇索引需要额外的I/O操作,因此访问数据的速度要慢一些。
    联合索引
    联合索引是在两个或多个列上创建的索引,它能够提高包含这些列的查询的性能。联合索引可以根据多个列的值来排列数据,在查询时可以根据这些列进行检索,提高查询速度。但是,如果联合索引中有某个列的值重复较多,那么查询可能会变得特别慢。
    唯一索引
    唯一索引是在列上创建的索引,它要求这些列的值必须是唯一的,因此可以用于确保数据的唯一性。唯一索引可以在单个列或多个列上创建。与普通索引相比,唯一索引可以加速查找操作,并且可以避免数据重复,保证数据的完整性和正确性。
    总结
    聚簇索引、非聚簇索引、联合索引和唯一索引都是MySQL中常见的索引类型,它们具有各自的优缺点和适用场景,需要根据具体的情况进行选择和使用。在使用索引时,还需要注意索引的设计和维护,避免索引失效、重复或过多等问题。