索引类型分类

数据库中的索引是提高数据检索速度的重要工具。它们就像书的目录或图书馆的索引卡片，可以帮助数据库系统快速定位到所需的数据，而无需扫描整个表。

根据不同的分类标准，数据库索引可以分为多种类型。下面我们来详细介绍几种主要的索引类型以及它们的使用场景。

主要索引类型分类

1. 按数据结构分类

• B-Tree 索引 (B+ Tree 索引)：

  • 最常见和默认的索引类型。 几乎所有的关系型数据库（MySQL 的 InnoDB、PostgreSQL、Oracle、SQL Server）都使用 B+ Tree 作为其主要索引结构。

  • 结构特点： B+ Tree 是一种多路平衡查找树，所有叶子节点都包含指向下一叶子节点的指针，形成一个有序链表。非叶子节点只存储索引键值，不存储数据，所有数据都存储在叶子节点。

  • 适用场景：

    • 等值查询： WHERE col = 'value'
    • 范围查询： WHERE col BETWEEN 'v1' AND 'v2' 或 WHERE col > 'v'
    • 排序： ORDER BY col (如果排序顺序和索引顺序一致)
    • 前缀匹配： WHERE col LIKE 'prefix%'
    • 多列排序和分组： 在复合索引上进行 ORDER BY 或 GROUP BY。

• 哈希索引 (Hash Index)：

  • 结构特点： 基于哈希表实现，将索引列的值通过哈希函数计算成哈希码，然后根据哈希码定位到对应的物理地址。

  • 优点： 在等值查询时（如 WHERE col = 'value'）查找速度非常快，理论上接近 O(1)。

  • 缺点：

    • 不支持范围查询： 哈希值是离散的，无法进行范围查找。
    • 不支持排序： 数据的物理存储顺序与键的逻辑顺序无关。
    • 不支持模糊查询： LIKE 操作无法使用。
    • 哈希冲突： 存在哈希冲突的可能，需要额外的处理机制。
    • 无法利用索引进行排序： 无法避免 Using filesort。

  • 适用场景： 仅适用于等值查询，且数据不常变动（因为哈希冲突可能导致性能下降）。

  • 使用方式：

    • MySQL 的 Memory 存储引擎支持哈希索引。
    • InnoDB 存储引擎有“自适应哈希索引” (Adaptive Hash Index)，它是 InnoDB 内部自动创建和管理的，用户无法直接控制。
    • PostgreSQL 支持 HASH 索引，但通常不推荐使用，除非你知道你在做什么，因为其在性能上通常不如 B-Tree 索引稳定。

• 全文索引 (Full-Text Index)：

  • 结构特点： 用于在文本数据中进行关键词搜索，类似于搜索引擎。它通常会分词、去停用词、词干提取等处理，然后建立倒排索引。

  • 优点： 能够高效地进行文本内容匹配搜索。

  • 缺点： 不适用于精确匹配，主要用于模糊文本搜索。

  • 适用场景： 文章内容、商品描述、评论等文本字段的关键词搜索。

  • 使用方式：

    • MySQL (MyISAM/InnoDB for 5.6+): CREATE FULLTEXT INDEX index_name ON table_name (text_column);
    • SQL Server: CREATE FULLTEXT CATALOG ... CREATE FULLTEXT INDEX ...
    • PostgreSQL: 使用 GIN 或 GiST 索引配合 tsvector 和 tsquery。

• R-Tree 索引：

  • 结构特点： 用于处理多维空间数据，例如地理空间数据（点、线、多边形）。
  • 适用场景： 地理信息系统 (GIS) 中的空间查询，例如查找某个区域内的所有餐馆。
  • 使用方式： MySQL 的 MyISAM 存储引擎支持，PostgreSQL 的 PostGIS 扩展使用 GiST/SP-GiST 索引来支持空间查询。

2. 按功能或特性分类

• 主键索引 (Primary Key Index)：

  • 特点： 每个表最多只有一个主键索引。它强制表中的每一行数据具有唯一的标识，并且非空。数据库会自动为主键创建索引（通常是 B+ Tree 索引）。
  • 优点： 提供数据的唯一性约束，并极大地加速基于主键的查询。
  • 适用场景： 任何需要唯一标识一行数据的列，如用户 ID、订单号。
  • 使用方式： CREATE TABLE table_name (id INT PRIMARY KEY, ...);

• 唯一索引 (Unique Index)：

  • 特点： 保证索引列中的所有值都是唯一的，但允许有 NULL 值（对于大多数数据库，但 MySQL InnoDB 对唯一索引的 NULL 值处理有所不同，允许多个 NULL 值）。一个表可以有多个唯一索引。
  • 优点： 保证数据唯一性，并加速基于唯一列的查询。
  • 适用场景： 用户名、电子邮件地址、身份证号等需要保持唯一性的字段。
  • 使用方式： CREATE UNIQUE INDEX unique_idx ON table_name (column_name); 或 ALTER TABLE table_name ADD UNIQUE (column_name);

• 普通索引 (Normal Index / Non-Unique Index)：

  • 特点： 最基本的索引类型，没有唯一性约束，允许索引列中有重复值。
  • 优点： 提高查询效率。
  • 适用场景： 任何经常用于查询条件的列，如用户年龄、商品类别、订单状态等。
  • 使用方式： CREATE INDEX idx_name ON table_name (column_name);

• 复合索引 (Composite Index / Combined Index / Multi-column Index)：

  • 特点： 在多个列上创建的索引。它的顺序很重要，遵循“最左前缀原则”。
  • 优点： 可以同时优化涉及多个列的查询条件、排序或分组。
  • 适用场景： 查询条件经常同时包含多个列，例如 WHERE col1 = ? AND col2 = ?。
  • 使用方式： CREATE INDEX idx_col1_col2 ON table_name (column1, column2);

3. 按物理存储分类 (主要针对特定数据库如 SQL Server)

• 聚簇索引 (Clustered Index)：

  • 特点： 决定了表中数据行的物理存储顺序。一个表只能有一个聚簇索引。数据行本身就是按照聚簇索引的键值逻辑上排序和物理上存储的。当通过聚簇索引查询时，可以直接获取数据，无需二次查找。
  • 优点： 对于范围查询和排序查询非常高效。
  • 缺点： 插入、更新和删除操作可能导致数据重新排列，开销较大。
  • 适用场景： 通常在主键上创建（SQL Server 默认为主键创建聚簇索引），或在经常需要范围查询、排序的列上创建。
  • 使用方式： 在 SQL Server 中，PRIMARY KEY 默认创建聚簇索引。也可以显式指定 CREATE CLUSTERED INDEX ...。

• 非聚簇索引 (Non-Clustered Index)：

  • 特点： 独立于数据存储，它包含索引键值和指向实际数据行的指针（可以是行 ID 或聚簇索引键）。一个表可以有多个非聚簇索引。
  • 优点： 提供了额外的查询路径，但查找数据需要“回表”操作（通过指针访问数据行），除非是覆盖索引。
  • 适用场景： 任何需要加速查询的非聚簇列。
  • 使用方式： SQL Server 中，普通索引和唯一索引默认都是非聚簇索引。

如何选择和使用索引？

1. 分析查询模式： 使用 EXPLAIN (或 EXPLAIN ANALYZE) 命令分析 SQL 查询的执行计划，找出没有使用索引或使用不当的查询。
2. 选择性： 对高选择性（唯一值多）的列建立索引。
3. 查询条件： 在 WHERE、JOIN、ORDER BY、GROUP BY 子句中经常使用的列上创建索引。
4. 复合索引： 考虑创建复合索引以优化多列查询，并遵循最左前缀原则。
5. 覆盖索引： 尽量使索引覆盖查询所需的所有列，以避免回表。
6. 写入性能的权衡： 索引会增加写入（INSERT、UPDATE、DELETE）操作的开销，因此不要创建过多不必要的索引。
7. 定期维护： 对于某些数据库和索引类型，可能需要定期进行索引重建或统计信息更新，以保持索引的效率。