今天整理了一下 MySQL 的索引,一般人只知道3~4个,但我经常听到有10几个之多,如下图:

索引

是不是很多都听过,但是只是想不起来。我大概归了一下类:

索引分类

密度

按照密度分,可分为稠密索引和稀疏索引,我们常见的MySQL Innodb 引擎使用的是稠密索引。一般列式数据库采用的是稀疏索引,如 Clickhouse。

稠密索引

稠密索引(Dense Index)也称为完全索引,是一种将每个记录(行)都映射到一个索引项(Index Entry)的索引方式。这种索引方式对于每一条记录都会创建一个索引项,因此它可以支持高效的查询和排序操作。但是,由于每个记录都需要一个索引项,因此它会占用更多的存储空间,特别是在大型表中使用时,可能会导致索引文件的过大,从而降低查询性能。

稀疏索引

稀疏索引(Sparse Index)也称为部分索引,是一种仅为部分记录创建索引项的索引方式。它仅为某些值创建索引项,而其他值则没有索引项。这种索引方式可以减少索引文件的大小,并且可以提高插入和更新的性能。但是,由于它需要进行更多的磁盘访问来执行查询操作,因此查询性能可能会受到影响。

稠密索引和稀疏索引

存储

按照存储分类,可以分为聚簇索引和非聚簇索引。

聚簇索引

聚簇索引(Clustered Index)是一种索引方式,它将数据存储在磁盘上,并且按照索引的顺序进行排序。它可以将相邻的行存储在相邻的磁盘页上,从而提高查询的性能。聚簇索引只能为表创建一个索引,因为每个表只能以一种方式进行排序。聚簇索引对于经常需要根据特定列进行查询的表非常有用,因为它们可以快速定位数据。

非聚簇索引

非聚簇索引(Non-Clustered Index)是一种索引方式,它将索引数据存储在单独的数据结构中,而不是存储在表的磁盘上。它包含了指向表中每行的指针,并按照索引列的顺序进行排序。这种索引方式可以为表创建多个索引,并且可以根据多个列进行排序。非聚簇索引对于经常需要根据不同的列进行查询的表非常有用,因为它们可以快速定位数据。

在使用聚簇索引时,需要注意以下几点:

  • 每个表只能有一个聚簇索引。

  • 聚簇索引的排序方式对查询性能有很大的影响,因此需要仔细选择聚簇索引的列。

  • 聚簇索引的创建和维护可能会占用较多的磁盘空间和时间。

在使用非聚簇索引时,需要注意以下几点:

  • 非聚簇索引可以为表创建多个索引,因此可以根据多个列进行排序。

  • 非聚簇索引可以减少查询时间,但是需要额外的空间存储索引数据结构。

  • 对于经常进行插入、更新和删除操作的表,非聚簇索引需要频繁更新,因此可能会影响性能。

算法

按照算法分类,可以分为B+树索引和哈希索引。

B+树索引

B+树索引是一种常用的数据库索引结构,它是基于B树的一种变体。B+树索引与B树的区别在于,它的非叶子节点不存储数据,只存储索引,而所有数据都存储在叶子节点中。这种结构使得B+树索引的查询速度更快,因为在进行范围查询时,只需要遍历叶子节点即可。

B+树索引的特点如下:

  1. 所有数据都存储在叶子节点中,非叶子节点只存储索引,因此可以存储更多的索引数据。

  2. 叶子节点之间形成一个有序的链表,方便进行范围查询。

  3. B+树索引的深度较浅,因此查询速度快,同时也减少了磁盘I/O操作的次数,提高了性能。

B+树

Hash索引

Hash索引是一种基于哈希表实现的数据库索引结构。在Hash索引中,每个索引项包含两部分,一个是关键字的哈希值,另一个是指向存储该关键字的数据块的指针。

Hash索引的查询速度非常快,因为它通过哈希函数将关键字转换为固定长度的哈希值,然后根据哈希值直接访问索引项。由于哈希值是唯一的,因此可以直接找到存储数据的位置,不需要进行比较操作。

Hash索引的优点包括:

  1. 查询速度快,查询性能稳定。

  2. 索引构建速度快,适合处理大量数据。

  3. 支持等值查询,适用于一些需要高速查询的场景。

Hash索引的缺点包括:

  1. 不支持范围查询、模糊查询和排序操作。

  2. 哈希函数的选择和设计比较关键,不合适的哈希函数可能导致哈希冲突,进而影响查询效率。

  3. Hash索引在内存中存储,如果数据过大,可能会导致内存不足的问题。

Hash索引的使用场景包括:

  1. 需要快速查找具有唯一性的数据的表,例如用户表、商品表等。

  2. 数据量较大、查询操作较多、更新操作较少的表,例如日志表、统计表等。

Hash索引

优化

从优化的角度分类,可分为前缀索引、复合索引、覆盖索引。

前缀索引

前缀索引是一种基于字符串前缀的数据库索引结构。在前缀索引中,对于字符串类型的列,可以只对其前几个字符建立索引,而不是对整个字符串进行索引。这样可以大大减小索引的存储空间,同时也可以提高查询效率。

例如,对于一个名字列,如果需要进行模糊查询,可以只对名字的前几个字符建立索引,这样可以有效减小索引的存储空间,同时也可以提高查询效率。一般来说,前缀索引的长度越短,索引的存储空间就越小,但是查询效率也会受到影响。

前缀索引的优点包括:

  1. 可以减小索引的存储空间,适用于字符串类型的列。

  2. 可以提高查询效率,特别是在模糊查询等操作中。

前缀索引的缺点包括:

  1. 可能会出现重复的索引项,进而影响查询效率。

  2. 前缀长度的选择比较关键,如果长度太短,可能会出现大量的哈希冲突,如果长度太长,可能会导致索引的存储空间变大。

前缀索引的使用场景包括:

  1. 需要对字符串类型的列进行模糊查询、前缀匹配等操作的表。

  2. 数据量较大、查询操作较多、更新操作较少的表,例如日志表、统计表等。

前缀索引是一种非常实用的数据库索引结构,可以大大提高查询效率,减小索引的存储空间。但是需要注意的是,在选择前缀长度时需要根据具体的业务需求进行选择,不合适的前缀长度可能会影响查询效率。

复合索引

复合索引是一种将多个列组合在一起来创建的索引,用于优化查询中涉及到多个列的查询效率。相对于单列索引,复合索引可以更有效地支持多列的查询,提高查询效率。

在创建复合索引时,可以选择多个列,并按照顺序依次排列。这样可以使得查询更加高效,因为复合索引的叶子节点存储的是多列的值,可以直接满足多列查询的需求,避免了查询时多次进行索引扫描的开销。

例如,对于一个包含姓名和年龄的表,可以创建一个复合索引,将这三列按照顺序依次排列。这样,当需要查询姓名、性别和年龄都满足一定条件的记录时,可以直接使用复合索引来完成查询,避免了多次索引扫描的开销,从而提高了查询效率。

复合索引的优点包括:

  1. 可以提高多列查询的效率,避免多次索引扫描的开销。

  2. 可以减小索引的存储空间,适用于需要对多个列进行查询的表。

复合索引的缺点包括:

  1. 复合索引的维护成本比单列索引高,因为需要对多个列进行维护。

  2. 对于一些单列查询,复合索引可能并不适用,因为需要扫描复合索引的叶子节点,而单列索引可以直接定位到需要的记录。

复合索引的使用场景包括:

  1. 需要对多列进行查询的表,例如包含姓名和年龄等多个列的表。

  2. 查询操作较多,更新操作较少的表。

复合索引

复合索引是一种非常实用的数据库索引结构,可以提高多列查询的效率,减小索引的存储空间。但是需要注意的是,在创建复合索引时需要根据具体的业务需求进行选择,不合适的复合索引可能会影响查询效率。

覆盖索引

覆盖索引是一种特殊的索引,它包含了所有需要查询的列的数据,而不需要进一步的查找操作就可以直接返回查询结果。这种索引也被称为索引覆盖或索引包含查询。

覆盖索引的原理是在索引结构中包含了查询所需要的列,因此数据库不需要再到数据表中查找所需的列。这样可以减少磁盘I/O操作,从而提高查询效率,特别是对于大型的数据表和频繁的查询操作。

例如,如果有一个包含姓名和年龄的表,并且需要查询所有男性的姓名和年龄。可以创建一个复合索引,按照性别、姓名和年龄的顺序依次排列。这样,查询时可以直接使用这个复合索引,并且由于该索引包含了查询所需的姓名和年龄信息,不需要进一步的查找操作就可以直接返回查询结果,从而提高了查询效率。

覆盖索引的优点包括:

  1. 可以减少磁盘I/O操作,从而提高查询效率。

  2. 可以减少CPU和内存的开销,特别是对于大型数据表和频繁的查询操作。

覆盖索引的缺点包括:

  1. 只有当需要查询的列都在索引中时,才能使用覆盖索引。否则,仍然需要到数据表中查找所需的列。

  2. 创建覆盖索引需要占用更多的磁盘空间。

覆盖索引的使用场景包括:

  1. 需要频繁进行查询操作的表。

  2. 查询操作需要使用多个列的信息。

覆盖索引

覆盖索引是一种特殊的复合索引,可以减少磁盘I/O操作,提高查询效率。但是需要注意的是,只有当需要查询的列都在索引中时才能使用覆盖索引,否则仍然需要到数据表中查找所需的列。因此,在创建覆盖索引时需要根据具体的业务需求进行选择。

功能

按照功能的角度划分,可以分为普通索引、唯一索引、全文索引。

普通索引

普通索引是数据库中最基本的索引结构,也被称为单列索引或简单索引。它只包含一个列的值和指向该行的指针,用于加速对该列的单列查询。可以对表的任意列创建普通索引,但通常建议对经常进行查询和排序的列创建索引,例如主键列和外键列等。

普通索引的原理是将所需要查询的列作为索引列,按照索引列的值建立索引。当查询该列时,数据库系统会先在索引结构中进行查找,然后根据索引中的指针到数据表中找到对应的行。由于普通索引只包含一个列的值和指向该行的指针,因此查询时需要在数据表中找到其他所需的列的值。

普通索引的优点包括:

  1. 可以加速单列查询的速度,特别是对于大型数据表和频繁的查询操作。

  2. 可以提高数据的访问效率,从而加快数据的处理速度。

普通索引的缺点包括:

  1. 当需要查询的列不在索引列中时,需要进行额外的查找操作,从而降低查询效率。

  2. 创建普通索引需要占用额外的磁盘空间和内存空间,可能会对写入操作的性能产生一定的影响。

普通索引的使用场景包括:

  1. 经常进行查询和排序操作的列。

  2. 需要经常进行连接操作的表的外键列。

  3. 数据表中需要保证唯一性的列,如主键列等。

普通索引是数据库中最基本的索引结构,用于加速对单列查询的速度,提高数据的访问效率。在创建普通索引时需要根据具体的业务需求进行选择,避免对写入操作的性能产生过大的影响。

唯一索引

唯一索引是一种限制数据库表中列值唯一性的索引,用于保证在指定列上没有重复的数据。与普通索引不同,唯一索引在索引列中的每个值都是唯一的,且不允许插入重复值,包括 NULL 值。

唯一索引的原理与普通索引类似,只是对于唯一索引而言,如果插入或更新操作的列值已经存在,数据库系统会抛出一个唯一性冲突的错误。唯一索引可以用于加速唯一性约束条件的验证,从而提高数据的访问效率。

唯一索引的优点包括:

  1. 可以保证数据表中的列值唯一性,避免重复数据的插入。

  2. 可以提高数据的访问效率,加速唯一性约束条件的验证。

唯一索引的缺点包括:

  1. 创建唯一索引需要占用额外的磁盘空间和内存空间,可能会对写入操作的性能产生一定的影响。

  2. 如果需要对多个列进行唯一性约束,需要创建多个唯一索引,会占用更多的磁盘空间和内存空间。

唯一索引的使用场景包括:

  1. 需要对数据表中的某一列或多个列进行唯一性约束的情况。

  2. 经常进行查询和排序操作的列需要保证唯一性。

唯一索引是一种用于限制数据库表中列值唯一性的索引,可以保证数据表中的列值唯一性,提高数据的访问效率。在创建唯一索引时需要根据具体的业务需求进行选择,避免对写入操作的性能产生过大的影响。

全文索引

全文索引是一种基于文本内容的索引技术,可以快速地检索出包含指定关键词或短语的文档或记录。相比于传统的索引技术,全文索引更加适用于文本数据的搜索和查询。

全文索引通常使用倒排索引(Inverted Index)的数据结构,将每个单词或短语出现的位置作为索引项,以便进行快速的查找和匹配。倒排索引可以理解为是一张单词词表,每个单词都指向包含该单词的文档或记录的位置。通过倒排索引,可以快速地找到包含指定单词的文档或记录,以及它们出现的位置。

全文索引的优点包括:

  1. 可以对文本数据进行高效的搜索和查询,提高数据的访问效率。

  2. 可以支持模糊搜索和短语搜索等功能,增强搜索的灵活性和准确性。

全文索引的缺点包括:

  1. 创建全文索引需要占用大量的磁盘空间和内存空间,可能会对系统的性能产生影响。

  2. 全文索引的更新和维护成本较高,需要花费较多的时间和资源。

全文索引的使用场景包括:

  1. 需要对大量文本数据进行搜索和查询的情况,如新闻、博客、社交媒体等应用。

  2. 需要支持模糊搜索和短语搜索等高级搜索功能的情况。

全文索引是一种基于文本内容的索引技术,可以高效地对文本数据进行搜索和查询,支持模糊搜索和短语搜索等高级搜索功能。在使用全文索引时需要考虑到其占用的磁盘空间和内存空间,以及更新和维护的成本。

关系

按照关系分类,可分为主键索引和辅助索引。

主键索引

主键索引是一种基于数据库表中主键的索引技术,主要用于加速对数据库表中某个记录的查找和访问。主键是一种唯一标识数据库表中每个记录的字段或一组字段,每个记录都应该具有不同的主键值。

主键索引通常使用B+树等数据结构进行实现,以便能够快速地查找和定位某个记录。主键索引的特点包括:

  1. 主键索引是一种唯一索引,要求每个记录的主键值都是唯一的。

  2. 主键索引通常是表中的主键字段上创建的索引,可以加快对该字段的查找和访问速度。

  3. 主键索引可以用于加速数据库表的连接操作和数据的排序操作。

主键索引的优点包括:

  1. 提高了对数据库表中某个记录的查找和访问速度,降低了访问数据库表的成本。

  2. 由于主键值是唯一的,可以保证每个记录都能被唯一地标识和访问。

  3. 可以用于加速数据库表的连接操作和数据的排序操作,提高了查询和分析数据的效率。

主键索引的缺点包括:

  1. 主键索引只适用于基于主键的查询,对于其他类型的查询可能会不够高效。

  2. 在插入新记录或更新主键值时,需要重新维护主键索引,可能会对数据库性能产生影响。

  3. 如果主键值不够唯一或者数据量较大,主键索引可能会占用较多的磁盘空间和内存空间。

主键索引是一种基于数据库表中主键的索引技术,可以提高对数据库表中某个记录的查找和访问速度,用于加速数据库表的连接操作和数据的排序操作。在使用主键索引时需要考虑到其对于其他类型的查询可能不够高效,以及在插入新记录或更新主键值时需要重新维护主键索引的成本。MySQL 主键索引还是聚簇索引。

辅助索引

辅助索引,也称为非聚簇索引,是一种基于数据库表中某个字段或多个字段的索引技术,用于加速对数据库表的查询操作。辅助索引与聚簇索引不同,辅助索引不会改变表中数据的物理存储方式。因此除主键索引外,其余索引均为辅助索引。

责任编辑:华轩来源: 今日头条