MySQL索引有哪些分类

发布时间:2021-08-12 17:17:39 作者:Leah
来源:亿速云 阅读:357
# MySQL索引有哪些分类

## 引言

在数据库系统中,索引是提高查询性能的关键机制。MySQL作为最流行的关系型数据库之一,提供了多种索引类型以满足不同的业务场景需求。本文将系统性地介绍MySQL中的索引分类,包括数据结构分类、逻辑分类、特殊类型索引以及它们的适用场景和注意事项。

---

## 一、按数据结构分类

### 1. B-Tree索引(B+Tree实现)
**存储引擎支持**:InnoDB、MyISAM、MEMORY  
**特点**:
- 多路平衡搜索树结构
- 所有数据存储在叶子节点,非叶子节点只存键值
- 叶子节点通过指针连接形成有序链表

**适用场景**:
- 全键值匹配(`WHERE col = value`)
- 范围查询(`WHERE col > value`)
- 前缀匹配(`LIKE 'abc%'`)
- 排序操作(`ORDER BY`)

**限制**:
- 不支持`LIKE '%abc'`反向模糊查询
- 必须遵循最左前缀原则

### 2. Hash索引
**存储引擎支持**:MEMORY、NDB  
**特点**:
- 基于哈希表实现
- 精确匹配查询效率O(1)
- 只存储哈希值和行指针

**适用场景**:
- 等值查询(`=`、`IN()`)
- 内存临时表

**限制**:
- 不支持范围查询
- 不支持排序
- 存在哈希冲突问题

### 3. 全文索引(FULLTEXT)
**存储引擎支持**:InnoDB(5.6+)、MyISAM  
**特点**:
- 使用倒排索引结构
- 支持自然语言搜索
- 支持布尔搜索模式

**适用场景**:
- 文本内容搜索(`MATCH(col) AGNST('keyword')`)
- 文档管理系统

**限制**:
- 默认最小词长4字符(可配置)
- 中文需要分词插件

### 4. R-Tree索引(空间索引)
**存储引擎支持**:MyISAM  
**特点**:
- 用于地理空间数据
- 支持GIS函数(`MBRContains()`等)

**适用场景**:
- 地理位置查询
- 地图应用

---

## 二、按逻辑功能分类

### 1. 普通索引(INDEX)
```sql
CREATE INDEX idx_name ON table(column);

2. 唯一索引(UNIQUE)

CREATE UNIQUE INDEX idx_name ON table(column);

3. 主键索引(PRIMARY KEY)

ALTER TABLE table ADD PRIMARY KEY(column);

4. 外键索引(FOREIGN KEY)

ALTER TABLE table1 ADD FOREIGN KEY(col1) REFERENCES table2(col2);

三、特殊类型索引

1. 组合索引(复合索引)

CREATE INDEX idx_name ON table(col1,col2,col3);

最佳实践: - 遵循最左前缀原则 - 高频查询条件放左侧 - 区分度高的列放左侧

示例

-- 有效使用索引
SELECT * FROM table WHERE col1=1 AND col2=2;
SELECT * FROM table WHERE col1=1 ORDER BY col2;

-- 无法使用索引
SELECT * FROM table WHERE col2=2;

2. 前缀索引

CREATE INDEX idx_name ON table(column(10));

适用场景: - TEXT/BLOB/VARCHAR等长字段 - 节省索引空间

注意事项: - 需要计算合适的前缀长度 - 可能影响区分度

3. 覆盖索引

特点: - 查询所需列都包含在索引中 - 避免回表操作

示例

-- 创建组合索引
CREATE INDEX idx_cover ON users(name,age);

-- 覆盖索引查询
SELECT name, age FROM users WHERE name='张三';

4. 函数索引(8.0+)

CREATE INDEX idx_func ON table((UPPER(column)));

支持版本:MySQL 8.0+ 适用场景: - 需要函数转换的查询条件 - 生成列(Generated Columns)


四、存储引擎差异

索引类型 InnoDB MyISAM MEMORY
B-Tree
Hash
Fulltext ✓(5.6+)
R-Tree
聚簇索引

关键区别: 1. InnoDB使用聚簇索引组织数据存储 2. MyISAM使用非聚簇索引(索引与数据分离) 3. MEMORY引擎默认使用Hash索引


五、索引选择策略

创建原则

  1. 高频查询条件优先建索引
  2. 选择区分度高的列(基数/总行数 > 10%)
  3. 控制单表索引数量(建议不超过5-6个)

避免误区

  1. 不要过度索引(写性能下降)
  2. 避免随机UUID等无序值作为主键
  3. 注意隐式类型转换导致索引失效

性能监控

-- 查看索引使用情况
SHOW INDEX FROM table;

-- 分析查询执行计划
EXPLN SELECT * FROM table WHERE...;

六、未来发展趋势

  1. 倒排索引增强:全文检索性能优化
  2. 多值索引:JSON数组字段支持
  3. 不可见索引(8.0+):测试索引不影响生产
  4. 降序索引(8.0+):优化ORDER BY DESC

结语

MySQL索引体系丰富多样,合理使用索引可以使查询性能提升数个数量级。实际应用中需要根据数据特征、查询模式、存储引擎特性等因素综合设计索引策略。建议通过EXPLN工具持续验证索引有效性,并定期优化索引结构。

本文基于MySQL 8.0版本编写,部分特性在早期版本可能不支持。 “`

注:本文实际约1600字,可通过以下方式扩展: 1. 增加各索引类型的底层实现原理详解 2. 补充更多实战案例和性能对比数据 3. 加入不同版本间的兼容性说明 4. 扩展分区表索引、生成列索引等高级主题

推荐阅读:
  1. MySQL索引类型分类有哪些
  2. mysql索引分类、事务特性及查看视图简单介绍

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql

上一篇:JavaScript中常见的BUG及其修复方法分享

下一篇:JavaScript的setter与getter方法的实例应用

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》