MySQL索引类型详解

MySQL 索引类型详解

索引是数据库优化的核心手段，理解不同索引类型的特点和适用场景，是写出高性能 SQL 的关键。

一、索引分类总览

分类维度索引类型
数据结构B+Tree、Hash、Full-Text、R-Tree
存储方式聚簇索引、非聚簇索引
物理层级主键索引、二级索引（辅助索引）
索引性质主键索引、唯一索引、普通索引、前缀索引
字段个数单列索引、联合索引（复合索引）

二、按数据结构分类

2.1 B+Tree 索引（默认）

MySQL 中最常用的索引类型，InnoDB 和 MyISAM 都基于 B+Tree 实现。

B+Tree 特点：

  • 非叶子节点只存储 key 和指针，不存储数据
  • 叶子节点存储所有 key 和数据，并通过双向链表连接
  • 支持范围查询和排序
1
2
-- 创建 B+Tree 索引（默认）
CREATE INDEX idx_name ON users(name);

2.2 Hash 索引

基于哈希表实现，等值查询 O(1) 复杂度。

优点缺点
等值查询极快不支持范围查询
结构简单不支持排序
存在哈希冲突

注意：InnoDB 不支持显式创建 Hash 索引，但内部有自适应哈希索引（Adaptive Hash Index）。

2.3 全文索引（Full-Text）

对文本内容进行分词搜索，支持 CHAR、VARCHAR、TEXT 类型。

1
2
3
4
5
-- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 使用全文索引
SELECT * FROM articles WHERE MATCH(content) AGAINST('关键词');

实际应用：生产环境通常使用 Elasticsearch 替代 MySQL 全文索引。

2.4 R-Tree 索引

用于空间数据类型（geometry），支持地理位置的范围查询，使用较少。

三、按存储方式分类

3.1 聚簇索引 vs 非聚簇索引

特性聚簇索引非聚簇索引
数据存储索引和数据存储在一起索引和数据分开存储
叶子节点存储完整行数据存储主键值
每表数量只能有一个可以有多个
查询方式直接获取数据需要回表查询
典型实现InnoDB 主键索引InnoDB 二级索引、MyISAM 所有索引

回表查询流程：

1
二级索引 → 找到主键值 → 主键索引 → 获取完整数据

3.2 覆盖索引

当查询的字段都包含在索引中时，无需回表，称为索引覆盖。

1
2
3
-- 假设有联合索引 (name, age)
SELECT name, age FROM users WHERE name = 'test';  -- 覆盖索引，无需回表
SELECT *        FROM users WHERE name = 'test';  -- 需要回表

四、按索引性质分类

索引类型特点创建语法
主键索引唯一 + 非空 + 每表一个PRIMARY KEY
唯一索引列值唯一，允许 NULLUNIQUE INDEX
普通索引仅加速查询INDEX
前缀索引只索引前 N 个字符，节省空间INDEX(col(N))
1
2
-- 前缀索引示例
CREATE INDEX idx_email ON users(email(10));  -- 只索引 email 前 10 个字符

五、联合索引与最左前缀

5.1 联合索引结构

1
CREATE INDEX idx_abc ON t(a, b, c);

联合索引按照 (a, b, c) 的顺序排序，遵循最左前缀原则。

5.2 最左前缀匹配

查询条件是否走索引说明
WHERE a = 1✅使用 a
WHERE a = 1 AND b = 2✅使用 a, b
WHERE a = 1 AND b = 2 AND c = 3✅使用 a, b, c
WHERE b = 2❌缺少最左列 a
WHERE a = 1 AND c = 3⚠️只使用 a，c 无法使用
WHERE a > 1 AND b = 2⚠️a 使用范围查询后，b 失效

六、MySQL 8.x 索引新特性

6.1 隐藏索引

索引存在但优化器不使用，用于测试删除索引的影响。

1
2
3
4
5
6
-- 创建隐藏索引
CREATE INDEX idx_name ON users(name) INVISIBLE;

-- 切换可见性
ALTER TABLE users ALTER INDEX idx_name VISIBLE;
ALTER TABLE users ALTER INDEX idx_name INVISIBLE;

6.2 降序索引

MySQL 8.0 开始真正支持降序索引。

1
2
-- 创建降序索引
CREATE INDEX idx_time ON orders(create_time DESC);

6.3 函数索引

支持对表达式或函数结果建立索引。

1
2
3
4
5
-- 对 UPPER(name) 建立索引
CREATE INDEX idx_upper_name ON users((UPPER(name)));

-- 查询时可以使用
SELECT * FROM users WHERE UPPER(name) = 'TEST';

七、索引使用建议

  1. 选择性高的列优先建索引：如用户 ID、订单号
  2. 避免过多索引：每个索引都需要维护，影响写入性能
  3. 联合索引优于多个单列索引：减少回表次数
  4. 长字符串使用前缀索引：节省空间
  5. 避免在索引列上使用函数：会导致索引失效（8.0 可用函数索引）