索引是MySQL性能优化的核心手段，但很多开发者对索引类型的认知停留在“建个普通索引”层面。实际上，MySQL提供了多种索引类型，各有不同的数据结构、适用场景和限制。本文将全面介绍MySQL的索引分类体系，并通过流程图和实战案例帮你精准选择。

1. 索引分类全景图

MySQL的索引可以从多个维度进行分类，下面这张全景图帮你建立整体认知：

2. 按数据结构分类

这是最底层的分类，决定了索引的存储结构和查找算法。

2.1 B+Tree索引

B+Tree 是MySQL中最核心、最常用的索引结构，InnoDB和MyISAM的默认索引类型。

核心特性：

适用场景：

• 大部分OLTP业务
• 需要范围查询（BETWEEN、>、<）
• 需要排序（ORDER BY）
• 需要模糊匹配（LIKE 'abc%'）

2.2 Hash索引

Hash索引基于哈希表实现，理论上等值查询性能最高，但MySQL中只有Memory引擎默认支持。

核心特性：

InnoDB中的自适应Hash索引：

-- InnoDB会根据查询频率自动将热点索引页转为Hash索引
-- 相关参数
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';
-- 查看自适应Hash索引使用情况
SHOW ENGINE INNODB STATUS\G

适用场景：

• Memory引擎的临时表
• 等值查询极频繁且无范围查询需求
• 作为缓存层（如会话表）

2.3 全文索引（Full-Text Index）

全文索引专门用于文本内容的搜索，支持自然语言查询和布尔模式查询。

核心特性：

使用示例：

-- 创建全文索引
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT INDEX ft_title_content (title, content)
) ENGINE = InnoDB;

-- 自然语言模式搜索
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('MySQL索引' IN NATURAL LANGUAGE MODE);

-- 布尔模式搜索（支持+/-操作符）
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

-- 查询扩展模式
SELECT * FROM articles 
WHERE MATCH(title, content) AGAINST('数据库' WITH QUERY EXPANSION);

2.4 空间索引（R-Tree）

空间索引用于地理空间数据类型（GEOMETRY、POINT、POLYGON等）。

-- 创建空间索引
CREATE TABLE spatial_data (
    id INT PRIMARY KEY,
    location POINT NOT NULL,
    SPATIAL INDEX sp_idx (location)
);

-- 空间查询示例
SELECT * FROM spatial_data 
WHERE MBRContains(
    GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'),
    location
);

3. 按功能逻辑分类

这是开发中最常接触的分类方式，基于索引的约束特性。

3.1 各类索引对比

3.2 详细对照表

3.3 使用示例

-- 1. 主键索引（自动创建）
CREATE TABLE users (
    user_id INT PRIMARY KEY,  -- 主键索引
    id_card VARCHAR(18)
);

-- 2. 唯一索引
CREATE UNIQUE INDEX uk_id_card ON users(id_card);

-- 3. 普通索引
CREATE INDEX idx_name ON users(name);

-- 4. 组合唯一索引
CREATE UNIQUE INDEX uk_user_period ON orders(user_id, period);

4. 按列数分类

4.1 单列索引 vs 联合索引

联合索引的核心原则（最左前缀）：

-- 创建联合索引
CREATE INDEX idx_name_age ON user(name, age);

-- ✅ 使用索引 (name, age)
SELECT * FROM user WHERE name = 'Tom' AND age = 20;

-- ✅ 使用索引 (name)
SELECT * FROM user WHERE name = 'Tom';

-- ❌ 无法使用索引（缺少最左列）
SELECT * FROM user WHERE age = 20;

5. 按存储方式分类（InnoDB核心）

这是InnoDB特有的分类方式，深刻影响查询性能。

5.1 聚簇索引 vs 二级索引

关键区别：

6. 按应用特性分类

6.1 覆盖索引

前面文章已详细讲解，简单示意：

-- 索引 (name, age) 覆盖了查询
SELECT name, age FROM user WHERE name = 'Tom';
-- Extra: Using index ✅

6.2 前缀索引

针对字符串列，只索引前N个字符，节省空间。

-- 只索引name字段的前10个字符
CREATE INDEX idx_name_prefix ON user(name(10));

-- 查询如何选择前缀长度
SELECT 
    COUNT(DISTINCT LEFT(name, 5)) / COUNT(*) AS sel5,
    COUNT(DISTINCT LEFT(name, 6)) / COUNT(*) AS sel6,
    COUNT(DISTINCT LEFT(name, 7)) / COUNT(*) AS sel7
FROM user;

6.3 表达式索引（MySQL 8.0+）

-- 基于表达式创建索引
CREATE INDEX idx_upper_name ON user((UPPER(name)));

-- 使用表达式查询
SELECT * FROM user WHERE UPPER(name) = 'TOM';

6.4 不可见索引（MySQL 8.0+）

-- 创建不可见索引（优化器忽略）
CREATE INDEX idx_invisible ON user(age) INVISIBLE;

-- 修改可见性
ALTER TABLE user ALTER INDEX idx_invisible VISIBLE;

-- 用途：测试删除索引的影响，无需真正删除

7. 索引类型选择决策树

8. 不同存储引擎的索引支持

*InnoDB有自适应Hash索引，但不可手动创建

9. 实战：为不同场景选择正确索引

9.1 用户登录表

-- 需求：用户名登录（等值、唯一）
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    password_hash VARCHAR(255)
);

-- ✅ 选择：唯一索引（保证用户名唯一，加速登录查询）
CREATE UNIQUE INDEX uk_username ON users(username);
CREATE INDEX idx_email ON users(email);  -- 普通索引即可

9.2 订单表

-- 需求：时间范围查询 + 用户筛选 + 排序
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    user_id INT NOT NULL,
    order_status TINYINT,
    create_time DATETIME,
    amount DECIMAL(10,2)
);

-- ✅ 选择：联合索引，等值列在前，范围列在后
CREATE INDEX idx_user_time ON orders(user_id, create_time);
-- 支持：user_id = ? / user_id = ? + create_time范围

9.3 文章表

-- 需求：标题和内容搜索
CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    category_id INT,
    publish_time DATETIME
);

-- ✅ 选择：全文索引 + B+Tree联合索引
CREATE FULLTEXT INDEX ft_title_content ON articles(title, content);
CREATE INDEX idx_category_time ON articles(category_id, publish_time);

9.4 地理数据表

-- 需求：查找附近的门店
CREATE TABLE stores (
    store_id INT PRIMARY KEY,
    name VARCHAR(100),
    location POINT NOT NULL,
    SPATIAL INDEX sp_location (location)
);

-- 查询附近门店
SELECT name, ST_Distance(location, POINT(116.397, 39.908)) AS distance
FROM stores
WHERE MBRContains(
    GeomFromText('POLYGON((...))'),
    location
)
ORDER BY distance;

10. 索引类型速查表

11. 常见错误与最佳实践

❌ 常见错误

-- 1. 对低基数列盲目建索引
CREATE INDEX idx_status ON orders(status);  -- status只有几个值

-- 2. 索引过多导致写入慢
-- 每增加一个索引，INSERT/UPDATE/DELETE都需要维护

-- 3. 忘记最左前缀原则
-- 联合索引(a,b,c)，只查b无法使用

-- 4. 在长文本上建完整索引
CREATE INDEX idx_remark ON orders(remark(255));  -- 过长
-- 应该用前缀索引

✅ 最佳实践

1. 选择合适的索引类型：根据查询模式（等值/范围/文本）选择
2. 控制索引数量：单表索引不超过5-6个
3. 遵循最左前缀：高频查询条件放在联合索引左侧
4. 使用覆盖索引：减少回表
5. 定期分析索引使用：利用sys.schema_unused_indexes

结语

MySQL的索引类型丰富多样，理解每种索引的特点和适用场景是数据库优化的基本功。记住这张索引选择地图：

等值查询用B+Tree或Hash，范围排序用B+Tree，文本搜索用Full-Text，地理空间用R-Tree，唯一约束用Unique，覆盖查询用联合索引。

实际开发中，80%的场景使用普通B+Tree索引就够了，剩下的20%特殊需求再考虑其他索引类型。使用EXPLAIN验证索引效果，避免过度索引，才能在性能和存储之间找到最佳平衡。

📌 核心要点回顾

1️⃣ 数据结构：B+Tree（默认）、Hash、Full-Text、R-Tree
2️⃣ 功能逻辑：普通、唯一、主键、全文、空间
3️⃣ 列数维度：单列、联合索引
4️⃣ InnoDB特性：聚簇索引、二级索引
5️⃣ 高级特性：覆盖索引、前缀索引、表达式索引、不可见索引