MySQL 索引的分类

MySQL 索引是提升数据库查询性能的核心机制,其分类方式多样。主要可以从 数据结构物理存储逻辑功能/使用特性 三个维度进行划分:

一、按数据结构划分 (最本质的分类)

  1. B+Tree 索引

    • 最常用、最核心:InnoDB 和 MyISAM 存储引擎的默认索引类型
    • 特点
      • 多路平衡搜索树,查询效率高(时间复杂度近似 O(log n))。
      • 叶子节点形成有序链表,非常适合范围查询>, <, BETWEEN, ORDER BY, GROUP BY)和排序。
      • 数据存储在叶子节点,非叶子节点只存储键值和指针,树的高度相对较低,减少 I/O。
      • 支持全值匹配、最左前缀匹配。
    • 适用场景:绝大多数场景(等值查询、范围查询、排序分组)。
  2. Hash 索引

    • 原理:基于哈希表实现,对索引键计算哈希码,存储指向数据行的指针。
    • 特点
      • 查询效率极高(时间复杂度 O(1)),仅适用于等值查询=IN())。
      • 不支持范围查询、排序、部分索引匹配(最左前缀)
      • 存在哈希冲突,需要处理冲突(如链地址法)。
      • Memory 存储引擎默认索引。InnoDB 支持自适应哈希索引(Adaptive Hash Index, AHI),由引擎自动管理,对频繁访问的 B+Tree 索引页建立哈希索引加速访问。
    • 适用场景:精确匹配查询为主的场景(如 KV 缓存),且数据存储在 Memory 引擎中。
  3. Full-Text (全文) 索引

    • 原理:专门用于在文本内容(CHAR, VARCHAR, TEXT)中进行关键词搜索。它会对文本进行分词,建立倒排索引(记录关键词到文档的映射)。
    • 特点
      • 使用特殊的查询语法 MATCH(column) AGAINST('keyword')
      • 支持自然语言搜索、布尔搜索、查询扩展等模式。
      • 解决 LIKE '%keyword%' 效率低下的问题。
    • 存储引擎:MyISAM 和 InnoDB (MySQL 5.6+) 都支持。
    • 适用场景:文章搜索、商品描述搜索等需要文本关键词检索的场景。
  4. R-Tree (空间) 索引

    • 原理:用于对空间数据类型GEOMETRY, POINT, LINESTRING, POLYGON)进行索引。基于 R-Tree 数据结构,支持高效的近邻搜索和范围搜索(如“查找附近 1 公里内的所有点”)。
    • 特点
      • 使用空间函数进行查询(如 MBRCONTAINS(), ST_DISTANCE())。
    • 存储引擎:MyISAM 支持。InnoDB 从 MySQL 5.7 开始也支持空间索引(使用 R-Tree)。
    • 适用场景:地理信息系统(GIS)、位置服务(LBS)等处理空间数据的应用。

二、按物理存储(数据与索引的关系)划分

  1. 聚簇索引 (Clustered Index)

    • 定义表数据行的物理存储顺序与索引键值的逻辑顺序一致。一个表只能有一个聚簇索引。
    • 特点 (InnoDB)
      • 主键 (PRIMARY KEY) 就是聚簇索引。
      • 如果没有定义主键,InnoDB 会选择一个唯一的非空索引 (UNIQUE NOT NULL) 作为聚簇索引。
      • 如果以上都没有,InnoDB 会隐式创建一个基于行 ID (ROWID) 的聚簇索引。
      • 数据行实际存储在索引的叶子节点上(即索引即数据)。
      • 按主键查询极快(直接定位行)。
      • 范围查询效率高(物理相邻)。
      • 插入速度可能受影响(需要插入到有序位置,可能导致页分裂)。
    • 存储引擎InnoDB 支持聚簇索引。MyISAM 不支持(数据和索引是分开存储的)。
  2. 非聚簇索引 / 二级索引 / 辅助索引 (Secondary Index)

    • 定义:索引的叶子节点存储的不是完整的数据行,而是聚簇索引键的值(在 InnoDB 中就是主键值)。需要根据这个值再去聚簇索引中查找完整的数据行,这个过程称为回表查询 (Bookmark Lookup / Key Lookup)
    • 特点
      • 一个表可以有多个非聚簇索引。
      • 叶子节点存储索引键 + 主键值(对于 InnoDB)。
      • 查询时需要额外的回表操作,可能增加 I/O(尤其是范围查询)。
      • 覆盖索引 (Covering Index) 是避免回表的优化手段:如果查询的列都包含在索引键中(或包含在 InnoDB 二级索引的叶子节点中),引擎可以直接从索引中获取数据,无需回表。
    • 存储引擎:所有支持索引的引擎(InnoDB, MyISAM)都有非聚簇索引。在 MyISAM 中,所有索引(包括主键索引)都是非聚簇索引,叶子节点存储的是指向数据文件物理位置的指针

三、按逻辑功能 / 使用特性划分

  1. 普通索引 (Index / KEY)

    • 定义:最基本的索引类型,没有任何唯一性约束。
    • 创建语法CREATE INDEX idx_name ON table_name (column_name);
    • 作用:加速基于该列的查询。
  2. 唯一索引 (Unique Index)

    • 定义:索引键值必须唯一,但允许有 NULL 值(对于单列索引,只能有一个 NULL;对于组合索引,列组合必须唯一)。
    • 创建语法CREATE UNIQUE INDEX idx_name ON table_name (column_name);
    • 作用:加速查询 + 保证数据唯一性约束。主键索引是一种特殊的唯一索引(不允许 NULL)。
  3. 主键索引 (Primary Key)

    • 定义:特殊的唯一索引,且不允许 NULL 值。在 InnoDB 中,它就是聚簇索引
    • 创建语法CREATE TABLE ... PRIMARY KEY (column_name)ALTER TABLE ... ADD PRIMARY KEY (column_name)
    • 作用:加速查询 + 唯一标识一行数据 + 强制物理组织顺序(InnoDB)。每个表只能有一个主键。
  4. 前缀索引 (Prefix Index)

    • 定义:对字符串类型列(CHAR, VARCHAR, BINARY, VARBINARY, TEXT, BLOB)的前 N 个字符建立索引,而不是整个字符串。
    • 创建语法CREATE INDEX idx_name ON table_name (column_name(N));
    • 作用减小索引体积,节省存储空间,提高写入速度。适用于长文本列。
    • 缺点降低了索引的选择性(区分度),可能影响查询效率(需要找到合适的 N 值平衡空间和选择性)。
  5. 组合索引 / 联合索引 / 复合索引 (Composite Index)

    • 定义:在多个列上建立一个索引。
    • 创建语法CREATE INDEX idx_name ON table_name (col1, col2, col3);
    • 关键特性 - 最左前缀匹配原则 (Leftmost Prefixing)
      • 索引按照创建时列的顺序从左到右组织。
      • 查询条件必须包含最左边的列才能有效使用该索引。
      • 可以匹配索引的最左前缀列、连续的前缀列或全部列。例如索引 (A, B, C)
        • 有效查询:WHERE A=val, WHERE A=val AND B=val, WHERE A=val AND B=val AND C=val
        • 有效排序/分组:ORDER BY A, ORDER BY A, B, GROUP BY A, B, C
        • 无效查询:WHERE B=val, WHERE C=val, WHERE B=val AND C=val (无法使用索引或只能部分使用)
        • 无效排序/分组:ORDER BY B, ORDER BY B, C
    • 作用
      • 支持多列条件查询、排序、分组。
      • 一个组合索引可能满足多个查询需求,减少创建多个单列索引的开销。
      • 更容易实现覆盖索引。
  6. 全文索引 (Full-Text Index) - 在数据结构部分已详述。

  7. 空间索引 (Spatial Index) - 在数据结构部分已详述。

总结与选择建议

维度索引类型核心特点/用途首选场景
数据结构B+Tree默认,支持范围查询、排序、分组,综合性能最好绝大多数场景
Hash仅等值查询快,不支持范围Memory引擎精确匹配
Full-Text文本分词搜索大文本关键词搜索
R-Tree空间数据索引GIS, LBS
物理存储聚簇索引(InnoDB主键) 数据即索引,顺序存储,范围查询快InnoDB表的主键
非聚簇索引叶子存主键值(指针),需回表查询辅助查询、覆盖索引
逻辑功能普通索引加速查询,无约束非唯一字段查询加速
唯一索引加速查询 + 强制唯一性保证列值唯一性
主键索引(特殊唯一索引) 表标识,InnoDB聚簇索引每表必备,选择稳定唯一短字段
组合索引多列索引,遵循最左前缀原则多条件查询、排序、分组、覆盖索引
前缀索引索引字符串前N字符,节省空间超长字符串列

选择策略:

  1. 理解需求: 明确查询模式(等值?范围?排序?分组?)、数据分布、唯一性要求。
  2. 主键优先: InnoDB 表务必设计好主键(短整型自增ID常见)。
  3. 善用组合索引: 分析高频查询的 WHERE、ORDER BY、GROUP BY 子句,按最左前缀原则创建组合索引。避免创建过多单列索引。
  4. 考虑覆盖索引: 尽量让索引包含查询所需的所有列,避免回表。
  5. 利用唯一索引: 确保数据唯一性时使用。
  6. 长文本用前缀索引: 权衡选择性和空间开销。
  7. 文本搜索用全文索引: 替代低效的 LIKE '%...%'
  8. 空间数据用空间索引: 处理地理位置查询。
  9. 避免过度索引: 索引虽好,但会占用空间,降低写入(INSERT/UPDATE/DELETE)速度(需维护索引)。只创建真正需要的索引。定期审查和删除无用索引。
  10. 监控分析: 使用 EXPLAIN / EXPLAIN ANALYZE 分析查询执行计划,验证索引是否被正确使用,并根据分析结果优化索引设计。

理解这些分类及其特点,是设计高效数据库索引的基础。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值