引言
MySQL支持多种索引类型，每种有不同的特点和适用场景。
一、主键索引（PRIMARY KEY）
1.1 特点
- 唯一且非空
- 聚簇索引：数据和索引存在一起
- 每个表只能有一个主键
1.2 创建
-- 方式1：建表时指定
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50)
);
-- 方式2：后续添加
ALTER TABLE users ADD PRIMARY KEY (id);
1.3 聚簇索引（Clustered Index）
InnoDB的主键索引是聚簇索引：
- 叶子节点存储完整的数据行
- 表数据按主键顺序存储
B+树结构：
[10, 20]
/ | \
[1→row] [10→row] [20→row] ← 叶子节点存完整数据
优势：
- 主键查询快（一次IO获取完整数据）
- 范围查询快（数据有序）
劣势：
- 主键不能太长（影响所有二级索引）
- 插入乱序会导致页分裂
二、唯一索引（UNIQUE INDEX）
2.1 特点
- 值必须唯一
- 允许NULL（但只能有一个NULL）
- 可以有多个唯一索引
2.2 创建
-- 方式1：建表时
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE
);
-- 方式2：单独创建
CREATE UNIQUE INDEX idx_email ON users(email);
-- 方式3：ALTER TABLE
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);
2.3 使用场景
-- 用户名、邮箱、手机号等唯一字段
CREATE UNIQUE INDEX idx_username ON users(username);
CREATE UNIQUE INDEX idx_phone ON users(phone);
三、普通索引（NORMAL INDEX）
3.1 特点
- 最基本的索引
- 值可以重复
- 也称为二级索引（Secondary Index）
3.2 创建
-- 方式1：CREATE INDEX
CREATE INDEX idx_name ON users(name);
-- 方式2：ALTER TABLE
ALTER TABLE users ADD INDEX idx_age (age);
-- 方式3：建表时
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
INDEX idx_name (name)
);
3.3 二级索引（非聚簇索引）
存储结构：
- 叶子节点存储：索引列值 + 主键值
- 不存储完整数据行
二级索引B+树：
[张三, 李四]
/ \
[name:张三→id:10] [name:李四→id:20] ← 只存主键id
回表查询：
- 在二级索引找到主键id=10
- 回到主键索引（聚簇索引）找完整数据
SELECT * FROM users WHERE name = '张三';
-- 1. 在idx_name找到id=10
-- 2. 回表到主键索引找完整数据
四、联合索引（Composite Index）
4.1 定义
多个列组合的索引。
CREATE INDEX idx_user_date ON orders(user_id, created_at);
4.2 最左前缀原则
-- 可以使用索引
WHERE user_id = 123;
WHERE user_id = 123 AND created_at > '2024-01-01';
-- 不能使用索引
WHERE created_at > '2024-01-01'; -- 缺少user_id
原因：联合索引按从左到右的顺序排序。
五、全文索引（FULLTEXT INDEX）
5.1 特点
- 用于文本搜索
- 支持中文分词（MySQL 8.0+）
- 只能用于CHAR、VARCHAR、TEXT列
5.2 创建
-- 创建全文索引
CREATE TABLE articles (
id INT PRIMARY KEY,
title VARCHAR(200),
content TEXT,
FULLTEXT INDEX ft_title_content (title, content)
) ENGINE=InnoDB;
5.3 使用
-- MATCH ... AGAINST
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('MySQL索引');
-- 布尔模式
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);
-- 自然语言模式
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE);
5.4 限制
- 最小搜索长度（默认4个字符）
- 中文分词需要插件（如ngram）
- 性能不如专业搜索引擎（Elasticsearch）
六、空间索引（SPATIAL INDEX）
用于地理位置数据（GEOMETRY、POINT等）。
CREATE TABLE locations (
id INT PRIMARY KEY,
name VARCHAR(100),
coordinate POINT NOT NULL,
SPATIAL INDEX idx_coordinate (coordinate)
);
-- 查询附近的位置
SELECT name FROM locations
WHERE ST_Distance_Sphere(coordinate, ST_GeomFromText('POINT(116.4 39.9)')) < 5000;
七、索引管理
7.1 查看索引
-- 查看表的所有索引
SHOW INDEX FROM users;
-- 查看索引统计
SHOW INDEX FROM users WHERE Key_name = 'idx_email';
7.2 删除索引
-- 方式1：DROP INDEX
DROP INDEX idx_email ON users;
-- 方式2：ALTER TABLE
ALTER TABLE users DROP INDEX idx_email;
-- 删除主键
ALTER TABLE users DROP PRIMARY KEY;
7.3 重建索引
-- 重建索引（优化碎片）
ALTER TABLE users ENGINE=InnoDB;
-- 或
OPTIMIZE TABLE users;
八、索引选择建议
| 场景 | 索引类型 | 示例 |
|---|---|---|
| 主键 | 主键索引 | id INT PRIMARY KEY |
| 唯一字段 | 唯一索引 | email UNIQUE |
| 频繁查询 | 普通索引 | name, age |
| 多条件查询 | 联合索引 | (user_id, created_at) |
| 文本搜索 | 全文索引 | title, content |
| 地理位置 | 空间索引 | coordinate POINT |
九、实战案例
案例1：用户表索引设计
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键索引
username VARCHAR(50) UNIQUE, -- 唯一索引
email VARCHAR(100) UNIQUE, -- 唯一索引
phone VARCHAR(20),
age INT,
created_at DATETIME,
INDEX idx_phone (phone), -- 普通索引
INDEX idx_age_created (age, created_at) -- 联合索引
);
案例2：订单表索引设计
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
order_no VARCHAR(50) UNIQUE, -- 唯一索引
user_id INT,
status VARCHAR(20),
created_at DATETIME,
INDEX idx_user_id (user_id), -- 普通索引
INDEX idx_status_created (status, created_at) -- 联合索引
);
十、总结
核心要点
- 主键索引：聚簇索引，数据和索引在一起
- 唯一索引：值唯一，可有NULL
- 普通索引：二级索引，存索引值+主键
- 联合索引：多列组合，最左前缀
- 全文索引：文本搜索，MATCH AGAINST
- 回表：二级索引查询需要回表到主键索引
记忆口诀
主键索引聚簇型，数据索引存一起，
唯一索引值不重，NULL只能有一个。
普通索引二级称，存储主键不存行，
联合索引多列合，最左前缀要记清。
全文索引搜文本，空间索引查位置。
本文字数：约2,600字 难度等级：⭐⭐⭐（索引进阶）