Skip to content

Mysql有哪些索引

按数据结构划分

这决定了索引的底层实现方式和它的适用场景。

  1. B+Tree索引： 这是最常见、也是默认的索引类型，InnoDB和MyISAM都使用它。它的数据结构是一个平衡的多路搜索树，所有数据都按顺序存储。

    • 优点：非常适合用于全键值、键值范围或最左前缀的查找。对=, >, <, BETWEEN, IN以及以常量开头的LIKE查询效率都很高。
    • 适用场景：绝大多数的查询场景。

  2. 哈希索引： 这是Memory存储引擎的默认索引类型。它基于哈希表实现，只有精确匹配索引所有列的查询才有效。

    • 优点：对于等值查询（= 和 IN），它的速度极快，时间复杂度是O(1)。
    • 缺点：不支持范围查询和排序。因为数据不是有序存储的，所以ORDER BY操作也无法利用哈希索引。
    • 补充：InnoDB有一个“自适应哈希索引”（Adaptive Hash Index）的功能，它会在B-Tree索引的基础上，对热点数据自动构建哈希索引，以提升查询性能。

  3. 全文索引 (Full-text Index)： 这是一种特殊的索引，主要用于在大量文本中进行关键词搜索。

    • 工作原理：它通过建立一个“倒排索引”来实现，记录了每个词在哪些文档中出现。
    • 使用方式：配合MATCH() ... AGAINST()语法使用，而不是LIKE。
    • 适用场景：用于文章、评论等文本内容的搜索。

  4. 空间数据索引 (R-Tree Index)： 这是一种比较特殊的索引，主要用于地理空间数据类型（GIS），可以高效地进行空间查询。这个在常规的业务开发中用得比较少。

按逻辑功能划分

这是我们开发者在创建索引时，从业务角度给索引赋予的“角色”。

  1. 普通索引 (Normal Index)： 最基本的索引，没有任何限制。它的唯一作用就是加速查询。

  2. 唯一索引 (Unique Index)： 它在普通索引的基础上，增加了数据唯一性的约束。即索引列的值必须是唯一的，但允许有多个NULL值。

  3. 主键索引 (Primary Key Index)： 这是一种特殊的唯一索引，它不允许有NULL值，并且一张表只能有一个主键索引。它用于唯一地标识表中的每一行记录。在InnoDB中，主键索引有非常特殊的地位。

  4. 组合索引 (Composite Index)： 也叫复合索引。它是指在表的多个列上创建一个索引。

    • 遵循“最左前缀原则”：一个组合索引idx(col1, col2, col3)，相当于创建了(col1)、(col1, col2)、(col1, col2, col3)这几个索引的效果。如果查询条件没有使用到索引的最左边的列，比如WHERE col2 = 'x'，那么这个索引就无法被使用。

按物理存储方式划分（InnoDB特有）

这是InnoDB引擎中一个非常重要的概念，它决定了数据和索引是如何组织在一起的。

  1. 聚簇索引 (Clustered Index)：

    • 定义：聚簇索引的叶子节点存储的不是索引指针，而是完整的行数据。因此，索引和数据是存放在一起的。
    • 特性：一张表只能有一个聚簇索引。InnoDB会按照以下顺序来选择或创建聚簇索引：
      1. 如果表定义了主键，则主键索引就是聚簇索引。
      2. 如果没有主键，则会选择第一个非空的唯一索引作为聚簇索引。
      3. 如果以上都没有，InnoDB会自动生成一个隐藏的6字节的ROW_ID作为聚簇索引。
    • 优点：根据主键进行查询时，速度极快，因为找到索引就找到了数据，不需要额外的回表操作。

  2. 非聚簇索引 (Non-Clustered Index) 或 辅助索引 (Secondary Index)：

    • 定义：除了聚簇索引之外的所有索引，都称为非聚簇索引或辅助索引。
    • 特性：它的叶子节点存储的不是行数据，而是该行数据对应的主键值。
    • 查询过程：当使用辅助索引进行查询时，会先通过该索引找到对应的主键值，然后再用这个主键值去聚簇索引中查找完整的行数据。这个过程被称为“回表”。
    • 优化：为了避免回表带来的性能开销，我们可以使用“覆盖索引”。如果一个查询所需的所有数据（SELECT列表和WHERE条件）都能在一个辅助索引中直接获取，那么就不需要回表了，这种查询方式就叫覆盖索引查询，性能会很高。