MySQL 索引类型选择

B-Tree索引

基本原理

B-Tree索引是MySQL中最常用的索引类型，基于B+Tree数据结构实现。B+Tree是一种平衡树，所有叶子节点在同一层级，叶子节点之间通过双向链表连接，便于范围查询。

特点

支持范围查询和精确查询
支持排序操作
支持前缀查询
适用于高选择性列
索引大小较大，维护成本较高

适用场景

频繁用于WHERE条件的列
用于ORDER BY和GROUP BY的列
用于JOIN操作的列
高选择性的列（重复值较少）

类型变体

B-Tree索引

功能：标准B-Tree索引，适用于大多数场景

使用示例：

sql

CREATE INDEX idx_name ON users(name);

Unique索引

功能：确保索引列的值唯一

特点：

允许NULL值（但只能有一个NULL值）
自动创建B-Tree索引
用于数据完整性约束

使用示例：

sql

CREATE UNIQUE INDEX idx_email ON users(email);

Primary Key索引

功能：主键索引，确保表中每行数据的唯一性

特点：

不允许NULL值
每个表只能有一个主键索引
自动创建B-Tree索引
用于表的行标识

使用示例：

sql

ALTER TABLE users ADD PRIMARY KEY (id);

Composite索引

功能：基于多个列创建的索引

特点：

遵循最左前缀原则
适用于多列查询条件
可以减少索引数量

使用示例：

sql

CREATE INDEX idx_name_age ON users(name, age);

最左前缀原则：

对于复合索引(idx_name_age)，以下查询可以使用索引：
- WHERE name = 'John'
- WHERE name = 'John' AND age = 30
- WHERE name LIKE 'J%'
以下查询无法使用索引：
- WHERE age = 30
- WHERE name LIKE '%ohn'

Hash索引

基本原理

Hash索引基于哈希表实现，通过哈希函数将索引列的值转换为哈希值，直接定位到数据行。

特点

仅支持精确查询，不支持范围查询
不支持排序操作
索引大小较小，维护成本较低
适用于等值查询场景

适用场景

频繁进行等值查询的列
低选择性的列（重复值较多）
不涉及范围查询的列

实现限制

MySQL只有Memory存储引擎支持显式创建Hash索引
InnoDB存储引擎支持自适应Hash索引（AHI），由MySQL自动管理
不支持NULL值

使用示例：

sql

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    INDEX idx_name USING HASH (name)
) ENGINE=MEMORY;

自适应Hash索引（AHI）

功能：InnoDB存储引擎自动创建的Hash索引

特点：

由InnoDB自动管理，无需手动创建
基于B-Tree索引构建
适用于频繁访问的索引页
可通过innodb_adaptive_hash_index参数控制

配置参数：

sql

SHOW GLOBAL VARIABLES LIKE 'innodb_adaptive_hash_index';
-- 默认值为ON

Full-Text索引

基本原理

Full-Text索引用于全文搜索，基于倒排索引实现，将文档中的单词映射到文档ID。

特点

支持自然语言搜索和布尔搜索
适用于大型文本字段
支持中文、英文等多种语言
索引大小较大，维护成本较高

适用场景

文章内容搜索
产品描述搜索
评论内容搜索
其他需要全文搜索的场景

实现方式

MyISAM Full-Text索引：

支持全文搜索
不支持事务
全文索引更新较快

InnoDB Full-Text索引：

支持全文搜索
支持事务
全文索引更新较慢
MySQL 5.6及以上版本支持

使用示例

sql

-- 创建全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

-- 使用全文搜索
SELECT * FROM articles WHERE MATCH(content) AGAINST('MySQL 索引' IN NATURAL LANGUAGE MODE);

-- 使用布尔全文搜索
SELECT * FROM articles WHERE MATCH(content) AGAINST('+MySQL -索引' IN BOOLEAN MODE);

配置参数

ft_min_word_len：全文索引的最小单词长度（默认4）
ft_max_word_len：全文索引的最大单词长度（默认84）
innodb_ft_min_token_size：InnoDB全文索引的最小标记长度（默认3）
innodb_ft_max_token_size：InnoDB全文索引的最大标记长度（默认84）

Spatial索引

基本原理

Spatial索引用于地理空间数据查询，基于R-Tree数据结构实现，用于存储和查询空间数据类型。

支持的数据类型

GEOMETRY：通用空间类型
POINT：点类型
LINESTRING：线类型
POLYGON：多边形类型
MULTIPOINT：多点类型
MULTILINESTRING：多线类型
MULTIPOLYGON：多多边形类型
GEOMETRYCOLLECTION：空间集合类型

特点

支持空间关系查询（如包含、相交、距离等）
适用于地理信息系统（GIS）应用
索引大小较大，维护成本较高

适用场景

地图应用
位置服务
地理数据分析
空间关系查询

使用示例

sql

-- 创建空间表
CREATE TABLE locations (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    position POINT NOT NULL,
    SPATIAL INDEX idx_position (position)
) ENGINE=MyISAM;

-- 插入空间数据
INSERT INTO locations (id, name, position) VALUES (1, 'Beijing', POINT(116.4074, 39.9042));

-- 查询空间数据
SELECT * FROM locations WHERE MBRContains(GeomFromText('Polygon((116 39, 117 39, 117 40, 116 40, 116 39))'), position);

索引选择原则

基于数据类型选择

数据类型	推荐索引类型	不推荐索引类型
整数类型	B-Tree索引	Hash索引
字符串类型	B-Tree索引（短字符串）、Full-Text索引（长文本）	Hash索引（长字符串）
日期时间类型	B-Tree索引	Hash索引
空间数据类型	Spatial索引	B-Tree索引、Hash索引
枚举类型	B-Tree索引	Hash索引

基于查询类型选择

查询类型	推荐索引类型	不推荐索引类型
精确查询	B-Tree索引、Hash索引	Full-Text索引
范围查询	B-Tree索引	Hash索引
排序查询	B-Tree索引	Hash索引
全文搜索	Full-Text索引	B-Tree索引、Hash索引
空间查询	Spatial索引	B-Tree索引、Hash索引

基于列选择性选择

高选择性列（重复值较少）：适合创建B-Tree索引
低选择性列（重复值较多）：适合创建Hash索引或不创建索引
选择性计算公式：
```
选择性 = 不同值数量 / 总行数
```
建议：选择性大于20%的列适合创建索引

基于表大小选择

小表（少于1000行）：通常不需要创建索引
中表（1000-100000行）：根据查询需求创建索引
大表（超过100000行）：需要仔细设计索引策略

不同存储引擎的索引支持

InnoDB存储引擎

支持的索引类型：

B-Tree索引（默认）
Unique索引
Primary Key索引
Composite索引
Full-Text索引（MySQL 5.6及以上）
Spatial索引（MySQL 5.7及以上）
自适应Hash索引（AHI，自动管理）

索引特点：

聚集索引（Clustered Index）：主键索引包含数据行
二级索引（Secondary Index）：叶子节点存储主键值
支持事务和行级锁

MyISAM存储引擎

支持的索引类型：

B-Tree索引
Unique索引
Primary Key索引
Composite索引
Full-Text索引
Spatial索引
Hash索引

索引特点：

非聚集索引：索引和数据分开存储
叶子节点存储数据行指针
不支持事务和行级锁

Memory存储引擎

支持的索引类型：

B-Tree索引
Hash索引（默认）
Unique索引
Primary Key索引
Composite索引

索引特点：

数据存储在内存中，访问速度快
索引大小较小
服务器重启后数据丢失

索引选择最佳实践

1. 分析查询需求

识别频繁执行的查询
分析查询的WHERE条件
分析查询的ORDER BY和GROUP BY子句
分析JOIN操作的连接条件

2. 选择合适的索引类型

根据数据类型选择索引类型
根据查询类型选择索引类型
根据列选择性选择索引类型
根据表大小选择索引策略

3. 优化索引设计

优先使用复合索引，减少索引数量
遵循最左前缀原则设计复合索引
避免创建冗余索引
定期删除不使用的索引

4. 监控索引性能

使用EXPLAIN分析查询执行计划
监控索引使用率
监控索引维护成本
定期重新生成统计信息

5. 考虑索引维护成本

索引会增加插入、更新和删除操作的开销
索引会占用磁盘空间
过多的索引会影响写入性能
定期检查和优化索引

索引选择案例分析

案例1：用户表索引设计

表结构：

sql

CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    email VARCHAR(100),
    age INT,
    created_at DATETIME
);

查询需求：

根据id查询用户
根据email查询用户
根据name和age查询用户
查询最近注册的用户
根据name进行模糊查询

索引设计：

PRIMARY KEY (id)：主键索引，用于唯一标识用户
UNIQUE INDEX idx_email (email)：唯一索引，确保email唯一性
INDEX idx_name_age (name, age)：复合索引，用于name和age查询
INDEX idx_created_at (created_at)：用于查询最近注册的用户

案例2：订单表索引设计

表结构：

sql

CREATE TABLE orders (
    id INT,
    user_id INT,
    product_id INT,
    amount DECIMAL(10,2),
    status ENUM('pending', 'paid', 'shipped', 'delivered'),
    created_at DATETIME
);

查询需求：

根据id查询订单
根据user_id查询用户的所有订单
根据product_id查询产品的所有订单
查询特定状态的订单
查询最近的订单

索引设计：

PRIMARY KEY (id)：主键索引
INDEX idx_user_id (user_id)：用于查询用户订单
INDEX idx_product_id (product_id)：用于查询产品订单
INDEX idx_status_created_at (status, created_at)：复合索引，用于查询特定状态的最近订单

常见索引选择误区

1. 为所有列创建索引

问题：过多的索引会增加写入操作的开销，影响性能 解决方案：只为频繁用于查询、排序和JOIN的列创建索引

2. 不考虑索引选择性

问题：为低选择性列创建索引会导致索引效率低下 解决方案：计算列的选择性，只为选择性高的列创建索引

3. 忽略复合索引的最左前缀原则

问题：不合理的复合索引设计会导致索引无法被使用 解决方案：根据查询需求，遵循最左前缀原则设计复合索引

4. 不监控索引使用情况

问题：不使用的索引会浪费资源，影响性能 解决方案：定期监控索引使用率，删除不使用的索引

5. 忽略索引维护成本

问题：索引会增加写入操作的开销 解决方案：在查询性能和写入性能之间取得平衡，定期优化索引

索引选择工具

EXPLAIN命令

功能：分析查询执行计划，查看索引使用情况

使用示例：

sql

EXPLAIN SELECT * FROM users WHERE name = 'John';

输出说明：

type：访问类型（ALL、index、range、ref、eq_ref、const、system、NULL）
key：使用的索引名称
key_len：使用的索引长度
rows：估计扫描的行数
Extra：额外信息

SHOW INDEX命令

功能：查看表的索引信息

使用示例：

sql

SHOW INDEX FROM users;

输出说明：

Table：表名
Non_unique：是否非唯一索引
Key_name：索引名
Seq_in_index：索引中的列序号
Column_name：列名
Cardinality：索引基数估计值

Performance Schema

功能：提供详细的索引使用统计信息

查询示例：

sql

-- 查看索引使用情况
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage;

sys schema

功能：提供易用的索引使用视图

查询示例：

sql

-- 查看未使用的索引
SELECT * FROM sys.schema_unused_indexes;

-- 查看索引使用情况
SELECT * FROM sys.schema_index_statistics;

常见问题（FAQ）

Q1: 如何选择合适的索引类型？

A1: 选择合适的索引类型需要考虑以下因素：

数据类型
查询类型
列选择性
表大小
索引维护成本

Q2: B-Tree索引和Hash索引有什么区别？

A2: B-Tree索引和Hash索引的主要区别：

B-Tree索引支持范围查询和排序，Hash索引只支持精确查询
B-Tree索引适用于高选择性列，Hash索引适用于低选择性列
B-Tree索引索引大小较大，Hash索引索引大小较小
B-Tree索引维护成本较高，Hash索引维护成本较低

Q3: 复合索引的最左前缀原则是什么？

A3: 复合索引的最左前缀原则是指，对于复合索引(col1, col2, col3)，查询条件中包含col1，或col1和col2，或col1、col2和col3时，可以使用该索引。如果查询条件只包含col2或col3，则无法使用该索引。

Q4: 什么时候不需要创建索引？

A4: 以下情况通常不需要创建索引：

小表（少于1000行）
低选择性列（重复值较多）
很少用于查询条件的列
频繁更新的列

Q5: 如何监控索引使用情况？

A5: 可以使用以下方法监控索引使用情况：

使用EXPLAIN分析查询执行计划
查询performance_schema.table_io_waits_summary_by_index_usage
查询sys.schema_unused_indexes视图
监控索引维护成本

Q6: 如何优化索引？

A6: 优化索引的方法包括：

删除不使用的索引
合并冗余索引
优化复合索引顺序
定期重新生成统计信息
考虑索引维护成本

Q7: 全文索引和B-Tree索引有什么区别？

A7: 全文索引和B-Tree索引的主要区别：

全文索引用于全文搜索，B-Tree索引用于精确查询和范围查询
全文索引基于倒排索引实现，B-Tree索引基于B+Tree实现
全文索引适用于长文本列，B-Tree索引适用于短字符串列
全文索引支持自然语言搜索，B-Tree索引不支持

Q8: Spatial索引适用于什么场景？

A8: Spatial索引适用于地理空间数据查询，如：

地图应用中的位置查询
地理数据分析
空间关系查询
位置服务

Q9: 如何选择复合索引的顺序？

A9: 选择复合索引顺序的原则：

将选择性高的列放在前面
将频繁用于查询条件的列放在前面
考虑最左前缀原则
考虑排序和分组需求

Q10: 索引会影响写入性能吗？

A10: 是的，索引会影响写入性能，因为：

插入、更新和删除操作需要维护索引
索引会增加磁盘I/O开销
过多的索引会导致写入性能下降
需要在查询性能和写入性能之间取得平衡

MySQL 索引类型选择 ​

B-Tree索引 ​

基本原理 ​

特点 ​

适用场景 ​

类型变体 ​

B-Tree索引 ​

Unique索引 ​

Primary Key索引 ​

Composite索引 ​

Hash索引 ​

基本原理 ​

特点 ​

适用场景 ​

实现限制 ​

自适应Hash索引（AHI） ​

Full-Text索引 ​

基本原理 ​

特点 ​

适用场景 ​

实现方式 ​

使用示例 ​

配置参数 ​

Spatial索引 ​

基本原理 ​

支持的数据类型 ​

特点 ​

适用场景 ​

使用示例 ​

索引选择原则 ​

基于数据类型选择 ​

基于查询类型选择 ​

基于列选择性选择 ​

基于表大小选择 ​

不同存储引擎的索引支持 ​

InnoDB存储引擎 ​

MyISAM存储引擎 ​

Memory存储引擎 ​

索引选择最佳实践 ​

1. 分析查询需求 ​

2. 选择合适的索引类型 ​

3. 优化索引设计 ​

4. 监控索引性能 ​

5. 考虑索引维护成本 ​

索引选择案例分析 ​

案例1：用户表索引设计 ​

案例2：订单表索引设计 ​

常见索引选择误区 ​

1. 为所有列创建索引 ​

2. 不考虑索引选择性 ​

3. 忽略复合索引的最左前缀原则 ​

4. 不监控索引使用情况 ​

5. 忽略索引维护成本 ​

MySQL 索引类型选择

B-Tree索引

基本原理

特点

适用场景

类型变体

B-Tree索引

Unique索引

Primary Key索引

Composite索引

Hash索引

基本原理

特点

适用场景

实现限制

自适应Hash索引（AHI）

Full-Text索引

基本原理

特点

适用场景

实现方式

使用示例

配置参数

Spatial索引

基本原理

支持的数据类型

特点

适用场景

使用示例

索引选择原则

基于数据类型选择

基于查询类型选择

基于列选择性选择

基于表大小选择

不同存储引擎的索引支持

InnoDB存储引擎

MyISAM存储引擎

Memory存储引擎

索引选择最佳实践

1. 分析查询需求

2. 选择合适的索引类型

3. 优化索引设计

4. 监控索引性能

5. 考虑索引维护成本

索引选择案例分析

案例1：用户表索引设计

案例2：订单表索引设计

常见索引选择误区

1. 为所有列创建索引

2. 不考虑索引选择性

3. 忽略复合索引的最左前缀原则

4. 不监控索引使用情况

5. 忽略索引维护成本