MySQL 索引是提升数据库查询性能的核心机制,其分类方式多样。主要可以从 数据结构、物理存储 和 逻辑功能/使用特性 三个维度进行划分:
一、按数据结构划分 (最本质的分类)
-
B+Tree 索引
- 最常用、最核心:InnoDB 和 MyISAM 存储引擎的默认索引类型。
- 特点:
- 多路平衡搜索树,查询效率高(时间复杂度近似 O(log n))。
- 叶子节点形成有序链表,非常适合范围查询(
>
,<
,BETWEEN
,ORDER BY
,GROUP BY
)和排序。 - 数据存储在叶子节点,非叶子节点只存储键值和指针,树的高度相对较低,减少 I/O。
- 支持全值匹配、最左前缀匹配。
- 适用场景:绝大多数场景(等值查询、范围查询、排序分组)。
-
Hash 索引
- 原理:基于哈希表实现,对索引键计算哈希码,存储指向数据行的指针。
- 特点:
- 查询效率极高(时间复杂度 O(1)),仅适用于等值查询(
=
,IN()
)。 - 不支持范围查询、排序、部分索引匹配(最左前缀)。
- 存在哈希冲突,需要处理冲突(如链地址法)。
- Memory 存储引擎默认索引。InnoDB 支持自适应哈希索引(Adaptive Hash Index, AHI),由引擎自动管理,对频繁访问的 B+Tree 索引页建立哈希索引加速访问。
- 查询效率极高(时间复杂度 O(1)),仅适用于等值查询(
- 适用场景:精确匹配查询为主的场景(如 KV 缓存),且数据存储在 Memory 引擎中。
-
Full-Text (全文) 索引
- 原理:专门用于在文本内容(
CHAR
,VARCHAR
,TEXT
)中进行关键词搜索。它会对文本进行分词,建立倒排索引(记录关键词到文档的映射)。 - 特点:
- 使用特殊的查询语法
MATCH(column) AGAINST('keyword')
。 - 支持自然语言搜索、布尔搜索、查询扩展等模式。
- 解决
LIKE '%keyword%'
效率低下的问题。
- 使用特殊的查询语法
- 存储引擎:MyISAM 和 InnoDB (MySQL 5.6+) 都支持。
- 适用场景:文章搜索、商品描述搜索等需要文本关键词检索的场景。
- 原理:专门用于在文本内容(
-
R-Tree (空间) 索引
- 原理:用于对空间数据类型(
GEOMETRY
,POINT
,LINESTRING
,POLYGON
)进行索引。基于 R-Tree 数据结构,支持高效的近邻搜索和范围搜索(如“查找附近 1 公里内的所有点”)。 - 特点:
- 使用空间函数进行查询(如
MBRCONTAINS()
,ST_DISTANCE()
)。
- 使用空间函数进行查询(如
- 存储引擎:MyISAM 支持。InnoDB 从 MySQL 5.7 开始也支持空间索引(使用 R-Tree)。
- 适用场景:地理信息系统(GIS)、位置服务(LBS)等处理空间数据的应用。
- 原理:用于对空间数据类型(
二、按物理存储(数据与索引的关系)划分
-
聚簇索引 (Clustered Index)
- 定义:表数据行的物理存储顺序与索引键值的逻辑顺序一致。一个表只能有一个聚簇索引。
- 特点 (InnoDB):
- 主键 (
PRIMARY KEY
) 就是聚簇索引。 - 如果没有定义主键,InnoDB 会选择一个唯一的非空索引 (
UNIQUE NOT NULL
) 作为聚簇索引。 - 如果以上都没有,InnoDB 会隐式创建一个基于行 ID (
ROWID
) 的聚簇索引。 - 数据行实际存储在索引的叶子节点上(即索引即数据)。
- 按主键查询极快(直接定位行)。
- 范围查询效率高(物理相邻)。
- 插入速度可能受影响(需要插入到有序位置,可能导致页分裂)。
- 主键 (
- 存储引擎:InnoDB 支持聚簇索引。MyISAM 不支持(数据和索引是分开存储的)。
-
非聚簇索引 / 二级索引 / 辅助索引 (Secondary Index)
- 定义:索引的叶子节点存储的不是完整的数据行,而是聚簇索引键的值(在 InnoDB 中就是主键值)。需要根据这个值再去聚簇索引中查找完整的数据行,这个过程称为回表查询 (Bookmark Lookup / Key Lookup)。
- 特点:
- 一个表可以有多个非聚簇索引。
- 叶子节点存储索引键 + 主键值(对于 InnoDB)。
- 查询时需要额外的回表操作,可能增加 I/O(尤其是范围查询)。
- 覆盖索引 (Covering Index) 是避免回表的优化手段:如果查询的列都包含在索引键中(或包含在 InnoDB 二级索引的叶子节点中),引擎可以直接从索引中获取数据,无需回表。
- 存储引擎:所有支持索引的引擎(InnoDB, MyISAM)都有非聚簇索引。在 MyISAM 中,所有索引(包括主键索引)都是非聚簇索引,叶子节点存储的是指向数据文件物理位置的指针。
三、按逻辑功能 / 使用特性划分
-
普通索引 (Index / KEY)
- 定义:最基本的索引类型,没有任何唯一性约束。
- 创建语法:
CREATE INDEX idx_name ON table_name (column_name);
- 作用:加速基于该列的查询。
-
唯一索引 (Unique Index)
- 定义:索引键值必须唯一,但允许有 NULL 值(对于单列索引,只能有一个 NULL;对于组合索引,列组合必须唯一)。
- 创建语法:
CREATE UNIQUE INDEX idx_name ON table_name (column_name);
- 作用:加速查询 + 保证数据唯一性约束。主键索引是一种特殊的唯一索引(不允许 NULL)。
-
主键索引 (Primary Key)
- 定义:特殊的唯一索引,且不允许 NULL 值。在 InnoDB 中,它就是聚簇索引。
- 创建语法:
CREATE TABLE ... PRIMARY KEY (column_name)
或ALTER TABLE ... ADD PRIMARY KEY (column_name)
- 作用:加速查询 + 唯一标识一行数据 + 强制物理组织顺序(InnoDB)。每个表只能有一个主键。
-
前缀索引 (Prefix Index)
- 定义:对字符串类型列(
CHAR
,VARCHAR
,BINARY
,VARBINARY
,TEXT
,BLOB
)的前 N 个字符建立索引,而不是整个字符串。 - 创建语法:
CREATE INDEX idx_name ON table_name (column_name(N));
- 作用:减小索引体积,节省存储空间,提高写入速度。适用于长文本列。
- 缺点:降低了索引的选择性(区分度),可能影响查询效率(需要找到合适的 N 值平衡空间和选择性)。
- 定义:对字符串类型列(
-
组合索引 / 联合索引 / 复合索引 (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
- 有效查询:
- 作用:
- 支持多列条件查询、排序、分组。
- 一个组合索引可能满足多个查询需求,减少创建多个单列索引的开销。
- 更容易实现覆盖索引。
-
全文索引 (Full-Text Index) - 在数据结构部分已详述。
-
空间索引 (Spatial Index) - 在数据结构部分已详述。
总结与选择建议
维度 | 索引类型 | 核心特点/用途 | 首选场景 |
---|---|---|---|
数据结构 | B+Tree | 默认,支持范围查询、排序、分组,综合性能最好 | 绝大多数场景 |
Hash | 仅等值查询快,不支持范围 | Memory引擎精确匹配 | |
Full-Text | 文本分词搜索 | 大文本关键词搜索 | |
R-Tree | 空间数据索引 | GIS, LBS | |
物理存储 | 聚簇索引 | (InnoDB主键) 数据即索引,顺序存储,范围查询快 | InnoDB表的主键 |
非聚簇索引 | 叶子存主键值(指针),需回表查询 | 辅助查询、覆盖索引 | |
逻辑功能 | 普通索引 | 加速查询,无约束 | 非唯一字段查询加速 |
唯一索引 | 加速查询 + 强制唯一性 | 保证列值唯一性 | |
主键索引 | (特殊唯一索引) 表标识,InnoDB聚簇索引 | 每表必备,选择稳定唯一短字段 | |
组合索引 | 多列索引,遵循最左前缀原则 | 多条件查询、排序、分组、覆盖索引 | |
前缀索引 | 索引字符串前N字符,节省空间 | 超长字符串列 |
选择策略:
- 理解需求: 明确查询模式(等值?范围?排序?分组?)、数据分布、唯一性要求。
- 主键优先: InnoDB 表务必设计好主键(短整型自增ID常见)。
- 善用组合索引: 分析高频查询的 WHERE、ORDER BY、GROUP BY 子句,按最左前缀原则创建组合索引。避免创建过多单列索引。
- 考虑覆盖索引: 尽量让索引包含查询所需的所有列,避免回表。
- 利用唯一索引: 确保数据唯一性时使用。
- 长文本用前缀索引: 权衡选择性和空间开销。
- 文本搜索用全文索引: 替代低效的
LIKE '%...%'
。 - 空间数据用空间索引: 处理地理位置查询。
- 避免过度索引: 索引虽好,但会占用空间,降低写入(INSERT/UPDATE/DELETE)速度(需维护索引)。只创建真正需要的索引。定期审查和删除无用索引。
- 监控分析: 使用
EXPLAIN
/EXPLAIN ANALYZE
分析查询执行计划,验证索引是否被正确使用,并根据分析结果优化索引设计。
理解这些分类及其特点,是设计高效数据库索引的基础。