MySQL的索引类型有哪些

MySQL数据库系统中的索引是用来加快搜索和检索数据库记录的数据结构。这些索引类似于书籍的目录，帮助MySQL快速定位数据表中的数据，而无需逐行扫描整个表。

索引解释

主键索引（PRIMARY KEY）

在MySQL中，主键索引是一种特殊的索引，用于保证表中每一行数据的唯一性。主键的设定是数据库设计中的一个重要环节，因为它不仅影响数据的完整性，还直接影响数据库的查询性能。

主键索引确保所在列的每个值都是唯一的，这意味着不能有重复的行。这是通过在底层数据库管理系统中强制实施唯一性约束来实现的。

由于主键索引的唯一性和数据库内部的优化机制（如B+树索引结构），主键查询通常比其他类型的查询更快，尤其是在处理大量数据时。

唯一索引（UNIQUE）

在MySQL中，唯一索引是用来确保一列或列组合中的值不重复，即每个值都必须在该列中是唯一的。这种索引类型是数据完整性策略的重要组成部分，特别是在处理不允许重复的数据字段时。

唯一索引不允许任何重复值存在于索引列。这对于如电子邮件地址、身份证号等需要保证唯一性的数据非常有用。

当尝试插入重复值时，数据库会抛出错误，从而防止数据完整性受到破坏。这是通过在数据库层面自动进行约束检查来实现的。

普通索引（INDEX）

普通索引，也简称为索引，在MySQL中是最基础的索引类型之一，用于提升数据检索的速度，使得数据库能更快地找到指定的数据行。普通索引没有唯一性要求，即允许索引列包含重复值。

不是所有的列都适合建立普通索引。通常，选择那些在WHERE子句、JOIN条件或ORDER BY子句中频繁出现的列来创建索引。

虽然索引可以提高查询性能，但它们也会增加写操作（如插入、更新和删除）的成本，因为数据库需要同时维护索引数据结构。因此，应该避免对不经常查询的列创建索引。

全文索引（FULLTEXT）

全文索引在MySQL中是专门为文本搜索设计的索引类型，允许对VARCHAR、CHAR或TEXT类型列中的数据进行快速的文本搜索。这种索引适用于包含大量文本的列，如文章内容、评论或任何其他形式的自由文本。

全文索引支持复杂的文本查询，包括词语的匹配和相关性排序。它使用一种称为倒排索引的数据结构，这种结构记录了每个单词出现在哪些文档中，以及它们在文档中的位置。

与传统的LIKE查询相比，全文索引支持更复杂的搜索操作，如自然语言搜索、布尔文本搜索等。这些功能通过特定的查询扩展（如MATCH() ... AGAINST()语法）提供。

全文索引查询不仅可以找出包含特定词汇的文档，还可以根据词汇出现的频率和文档中的位置，对搜索结果进行相关性评分，从而提供更加相关的搜索结果。

全文索引通常比其他类型的索引占用更多的磁盘空间，并且在构建索引时需要更多的处理时间。因此，建立全文索引前需要考虑到性能和存储成本。

当表中涉及全文索引的列更新时，索引也需要相应更新，这可能会影响写操作的性能。因此，对于频繁更新的数据，使用全文索引需要权衡利弊。

在MySQL中，全文索引最初只在MyISAM存储引擎上支持，但从MySQL 5.6版本开始，InnoDB也开始支持全文索引。

组合索引（COMPOSITE）又叫联合索引，复合索引。

组合索引（也称为联合索引或复合索引）在MySQL中是指在两个或多个列上创建的索引。这种类型的索引可以极大地提高查询性能，特别是当查询涉及多个列的组合时。

索引的最左前缀规则：MySQL在查询时遵循所谓的“最左前缀”规则，这意味着索引中的列在被查询时，必须按照索引创建时列的顺序来使用。例如，如果一个索引是按（列A, 列B, 列C）的顺序创建的，那么查询可以利用包含列A，列A和列B，或者全部三列的条件，但不能只利用列B和/或列C。

空间索引（SPATIAL）

空间索引是专门用于优化和处理空间数据查询的索引类型。在MySQL中，这种索引主要支持地理空间数据类型，如GEOMETRY，POINT，LINESTRING，POLYGON等。空间索引对于GIS（地理信息系统）应用和任何需要进行空间位置分析的应用来说是至关重要的。

空间索引使得查询如地理位置查找、空间距离计算和空间关系判断（例如，一个点是否在一个多边形内）等操作更加高效。这种索引类型使用特殊的数据结构（通常是R树），优化了空间数据的存储和查询。

空间索引支持各种空间查询操作，包括但不限于检测两个空间对象之间的关系（如相交、包含、邻接等）。

外键索引

MySQL的外键索引主要用于两个表之间建立联系，确保数据库的引用完整性。
外键是一个表中的字段，它是另一个表的主键。外键用于将一张表中的数据与另一张表的数据相关联。

• 数据完整性：外键保证了数据库中的数据正确性和一致性。它确保了在一张表中引用的数据在另一张表中必须存在。
• 约束和级联操作：外键可以定义不同的行为，例如在删除或更新关联表中的数据时采取的动作（如级联删除、级联更新等）。

前缀索引

前缀索引不会索引整个字段的全部内容，而是只索引字段的前N个字符。

在MySQL中，创建前缀索引的语法如下：

CREATE INDEX index_name ON table_name(column_name(length));

其中，length是你希望索引的字符数。例如，如果你有一个包含电子邮件地址的VARCHAR(100)字段，你可以只对前20个字符建立索引：

CREATE INDEX idx_email_prefix ON users(email(20));

聚簇索引（clustered index）

MySQL中的聚簇索引（Clustered Index）是一种特殊类型的索引，它决定了表中行数据的物理存储顺序。与非聚簇索引（非聚簇索引存储数据和索引分开）相比，聚簇索引的关键特点是表数据直接存储在索引的叶子节点上。这意味着每个表只能有一个聚簇索引。

二级索引（Secondary Index）

二级索引是数据库中一个非常重要的功能，它允许对非主键列创建索引。这种索引有助于加快查询速度，尤其是在查询条件不包含主键的情况下。在一个表中，主键索引通常被称为主索引，而所有其他索引则被称为二级索引。二级索引的每一个条目包含索引的键值和一个指向该行主键的指针，这样就可以通过二级索引快速定位到表中的相应行。

多列索引

多列索引，又称为组合索引，是在数据库表的两个或更多列上建立的索引。这种索引可以极大地提高执行多列查询条件时的性能。在创建多列索引时，列的顺序非常关键，因为数据库引擎会根据索引中列的顺序来优化查询。例如，如果经常根据姓和名进行查询，则应该创建一个以姓和名为顺序的多列索引。

覆盖索引

覆盖索引是指一个索引包含了查询中需要的所有数据。换句话说，如果一个查询能够仅通过索引就能获取到所需的全部数据，而无需回表查询实际的数据行，那么这个索引就被称为覆盖索引。使用覆盖索引可以显著提高查询性能，因为它减少了磁盘I/O的需求和数据行的访问。

哈希索引

哈希索引基于哈希表实现，适用于快速查找操作。在哈希索引中，索引项的存储位置是通过对键值进行哈希处理后得到的。这种索引的优势是提供了快速的查找性能，尤其是在等值查询（即精确查找）中。然而，哈希索引不支持范围查询和部分匹配查询，并且对键值的小变动可能导致存储位置的大变动。

B+树索引

B+树索引是数据库中使用最广泛的索引类型之一。它是一种自平衡的树数据结构，可以保持数据有序。B+树索引的特点是所有实际的数据指针都存在于叶子节点，而内部节点仅存储键值，这使得B+树在进行范围查询时更加高效。由于其结构的有序性，B+树可以快速进行查找、插入和删除操作。

索引分类

按照功能和特性分类

1. 主键索引（PRIMARY KEY）
   保证表中每一行数据的唯一性，并提供快速的数据访问能力。每个表只能有一个主键索引。

2. 唯一索引（UNIQUE）
   类似于主键索引，确保列中的所有值都是唯一的。不同之处在于表可以有多个唯一索引，且列可以接受NULL值。

3. 普通索引（INDEX）
   最基本的索引类型，用于提高数据检索速度，无额外的唯一性或其他约束。

4. 全文索引（FULLTEXT）
   专为文本数据设计，能够在字符数据中进行快速的搜索，常用于搜索引擎内部实现。

5. 外键索引
   用于维护数据库的完整性，确保一个表中的字段值必须出现在另一个表的主键或唯一索引中。

6. 聚簇索引（Clustered Index）
   数据存储与索引的顺序相同，一个表只能有一个聚簇索引。

7. 二级索引（Secondary Index）
   非聚簇索引，可以加速非主键字段的数据访问速度。

按照结构分类

1. B+树索引
   适用于大部分数据库索引，特别是主键和二级索引，优化了范围查询的性能。

2. 哈希索引
   基于哈希表实现，适用于等值查询，但不支持范围查询。

3. 空间索引（SPATIAL）
   专为地理空间数据设计，如地图位置的存储和查询。

4. 前缀索引
   针对文本字段的一种优化方式，只索引字段值的一部分前缀以减少索引大小。

按照组合方式分类

1. 组合索引（COMPOSITE）
   又称联合索引或复合索引，是在两个或更多列上建立的索引，用于优化多条件查询。

2. 覆盖索引
   索引包含所有查询所需的字段，查询操作不需要访问数据表，可以显著提高查询性能。

参考链接

• 二级索引：https://en.wikipedia.org/wiki/Secondary_index
• 多列索引：https://www.techopedia.com/definition/24029/composite-index
• 覆盖索引：https://en.wikipedia.org/wiki/Covering_index
• 哈希索引：https://en.wikipedia.org/wiki/Hash_table
• B+树索引：https://en.wikipedia.org/wiki/B%2B_tree
• 主键索引与唯一索引：https://www.techtarget.com/searchdatamanagement/definition/primary-key
• 普通索引与全文索引：https://www.elastic.co/guide/en/elasticsearch/reference/current/full-text-indices.html
• 组合索引与聚簇索引：https://www.sqlshack.com/understanding-clustered-indexes/
• 二级索引与哈希索引：https://www.geeksforgeeks.org/indexing-in-databases-set-1/
• B+树索引与空间索引：https://www.postgresql.org/docs/current/indexes-types.html