MySQL 索引详解

索引是 MySQL 中提升查询效率的核心工具，就像书籍的目录——通过索引可以快速定位到目标数据，而无需逐行扫描整张表。下面从基础概念、类型、使用原则到最佳实践，由浅入深讲解。

---

一、索引的核心概念

1. 索引的作用

• 加速查询：这是最核心的作用，避免全表扫描（Full Table Scan）。
• 优化排序/分组：索引本身是有序的，基于索引的 ORDER BY/GROUP BY 无需额外排序。
• 唯一约束：如主键索引、唯一索引可保证数据唯一性。

2. 索引的代价

• 增加写操作耗时：插入/更新/删除数据时，需要同步维护索引（如B+树的分裂/合并）。
• 占用磁盘空间：索引是独立的物理结构，会额外消耗存储。

---

二、MySQL 常见索引类型

1. 按「数据结构」分类（底层实现）

MySQL 主流存储引擎（InnoDB）默认使用 B+树索引，其他结构仅特殊场景使用：

索引类型	适用场景	特点
B+树索引	绝大多数查询（等值、范围、排序）	所有数据存在叶子节点，有序且链表连接，支持范围查询
哈希索引	等值查询（如 =）	查询快，但不支持范围/排序，InnoDB 仅自适应哈希（隐式）
全文索引	文本模糊匹配（如 MATCH AGAINST）	针对长文本（如文章内容），仅 MyISAM/InnoDB（5.6+）支持
空间索引	地理空间数据（如 GEOMETRY 类型）	仅 MyISAM/InnoDB（5.7+）支持，用于 ST_* 函数查询

2. 按「功能/语法」分类（常用）

（1）主键索引（PRIMARY KEY）

• 每张表只能有1个主键索引，默认非空且唯一。
• InnoDB 中，主键索引的叶子节点存储整行数据（聚簇索引），是所有其他索引的基础。

-- 创建表时定义主键索引
CREATE TABLE user (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(20),
  PRIMARY KEY (id) -- 主键索引
);

（2）唯一索引（UNIQUE）

• 保证索引列的值唯一（允许 NULL，多个 NULL 不冲突），每张表可创建多个。

-- 单独创建唯一索引
CREATE UNIQUE INDEX idx_user_phone ON user (phone);

（3）普通索引（INDEX）

• 最基础的索引，无唯一性约束，仅用于加速查询，可创建多个。

-- 表创建后添加普通索引
ALTER TABLE user ADD INDEX idx_user_name (name);

（4）复合索引（联合索引）

• 基于多个字段的索引，遵循「最左前缀原则」（查询条件需匹配索引字段的最左顺序）。

-- 创建复合索引：name + age
CREATE INDEX idx_user_name_age ON user (name, age);
-- 有效查询（匹配最左前缀）：WHERE name = '张三'
-- 有效查询：WHERE name = '张三' AND age = 20
-- 无效查询（不匹配最左）：WHERE age = 20

---

三、索引的使用原则（避坑指南）

1. 适合创建索引的场景

• 查询频繁的字段（如 WHERE/JOIN/ORDER BY 中的字段）。
• 区分度高的字段（如手机号，而非性别——性别只有2种值，索引效率低）。
• 复合索引优先于多个单列索引（减少索引维护成本）。

2. 不适合创建索引的场景

• 数据量小的表（全表扫描比索引更快）。
• 写操作远多于读操作的表（如日志表，维护索引会拖慢写入）。
• 重复值高的字段（如状态字段：0/1/2）。
• NULL 占比高的字段（B+树对 NULL 处理效率低）。

3. 索引失效的常见情况

1. 索引列参与函数/运算（如 WHERE id + 1 = 10）。
2. 使用 LIKE '%xxx'（模糊匹配以%开头）。
3. 使用 OR 连接非索引字段（如 WHERE name = '张三' OR address = '北京'，address无索引）。
4. 复合索引不满足最左前缀原则。
5. 隐式类型转换（如索引列是INT，查询用字符串：WHERE id = '123'）。

---

四、索引的常用操作

1. 创建索引

-- 方式1：CREATE INDEX
CREATE [UNIQUE] INDEX 索引名 ON 表名 (字段1[, 字段2...]);

-- 方式2：ALTER TABLE
ALTER TABLE 表名 ADD [UNIQUE/PRIMARY] INDEX 索引名 (字段1[, 字段2...]);

2. 查看索引

-- 查看表的所有索引
SHOW INDEX FROM 表名;
-- 简写
SHOW INDEXES IN 表名;

3. 删除索引

-- 方式1：DROP INDEX
DROP INDEX 索引名 ON 表名;

-- 方式2：ALTER TABLE
ALTER TABLE 表名 DROP INDEX 索引名;
-- 删除主键索引（特殊）
ALTER TABLE 表名 DROP PRIMARY KEY;

4. 验证索引是否生效（EXPLAIN）

使用 EXPLAIN 查看查询执行计划，重点看 type 列（级别从优到差：const > eq_ref > ref > range > ALL）和 key 列（显示实际使用的索引）。

-- 示例：查看查询是否使用索引
EXPLAIN SELECT * FROM user WHERE name = '张三';
-- 结果中 key 列显示 idx_user_name 表示索引生效；key 为 NULL 表示索引失效。

---

总结

1. 核心作用：索引通过B+树结构加速查询，但会增加写操作和存储成本，需权衡使用。
2. 关键原则：复合索引遵循「最左前缀原则」，避免索引失效（如函数运算、%xxx模糊匹配）。
3. 最佳实践：仅为高频查询、高区分度的字段建索引，用 EXPLAIN 验证索引是否生效。