MySQL 索引

一、 介绍

1.1 定义

索引是用于快速查询数据的一种排序数据结构

作用：大幅度提高查询速度，优化排序、分组、关联查询，减少全表扫描。

2.1 优缺点分析

优点：查询快、优化排序分组、加速关联、保证唯一。
缺点：占用磁盘空间、降低增删改速度、过多索引会拖慢优化器。

二、索引的数据结构

2.1 B+树索引

mysql 默认存储引擎 InnoDB，底层索引用B+树实现.
B+ 树，二分查找，时间复杂度 O (logN)

• 非叶子节点只存储索引值
• 叶子节点存储完整数据
• 所有叶子节点形成有序链表

优点：

• 支持范围查询 ，范围查询极快
• 矮胖结构：树高度只有 2~4 层，IO 极少，磁盘I/O次数少
• 排序效率极高
• 分组极快

2.2. 哈希索引

• 基于哈希表实现
• 只支持等值查询（=、IN）
• 不支持范围查询
• Memory引擎使用

2.3. 全文索引

• 用于全文搜索
• MyISAM和InnoDB（5.6+）支持
• 适用于文本字段的模糊查询

三、索引机制

3.1 本质

B+树实现的排序数据结构的作用：
把无序的数据变成有序，让查询从 “全表扫描” 变成 “二分查找”。

索引 = 排序 + 目录，让数据库不用逐行找数据

3.2 加速原理

1. 避免全表扫描（最核心）

• 无索引：必须一行一行遍历，时间复杂度 O (N)
• 有索引：走 B+ 树，二分查找，时间复杂度 O (logN)

2. B+ 树结构天然适合数据库

• 矮胖结构：树高度只有 2~4 层，IO 极少
• 所有数据存在叶子节点，且用链表串联
• 范围查询极快（where id > 100）
• 排序极快（order by）
• 分组极快（group by）

3. 索引本身有序，避免排序耗时

• order by /group by 不需要再做文件排序，直接用索引顺序输出。

4. 聚簇索引让数据就近存储

• InnoDB 主键索引是聚簇索引
  数据和索引存在一起，定位到索引就直接拿到数据，减少 IO

5. 覆盖索引避免回表

• 查询的字段全部在索引内部，不需要二次查询，速度极快。

3.3 索引代价

1. 占用额外磁盘空间

• 索引是独立数据结构，必须存储
• 索引越多，空间越大
• 字符串索引更占空间

2. 降低写入性能（最关键代价）
   INSERT / UPDATE / DELETE 时：

• 数据要改
• 所有相关索引必须同步更新
• 写多的表，索引越多越慢。

3. 增加数据库优化器开销

• MySQL 会自动选择最优索引
• 索引越多，选择耗时越长。

4. 索引维护成本

• 数据频繁变动会产生索引碎片
• 需要定期 优化索引

5. 不能乱建，否则负优化

• 重复索引
• 从未使用的索引
• 区分度极低的索引（性别、状态）
  都会拖慢整个数据库。

四、索引分类

4.1 主键索引 (PRIMARY KEY)

• 唯一索引，一张表只能有一个
• 列值非空 + 唯一，默认创建
• 底层：聚簇索引（数据和索引存在一起）

-- 创建方式
CREATE TABLE user(
  id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键索引
  name VARCHAR(20)
);

4.2 唯一索引 (UNIQUE)

• 列值必须唯一，允许 NULL（只能一个 NULL）
• 一张表可以有多个唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

CREATE UNIQUE INDEX idx_user_phone ON user(phone);

4.3 普通索引 (INDEX)

最基础索引，它没有任何限制，用于快速查询

-- 普通索引
CREATE INDEX 索引名 ON 表名(列名);

CREATE INDEX idx_user_name ON user(name);

4.4 组合索引 (复合索引)

• 多个列组合成一个索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。
• 使用组合索引时遵循最左前缀原则

-- 索引：(age, name, gender)
CREATE INDEX 索引名 ON 表名(列1, 列2, 列3);

CREATE INDEX idx_age_name_gender ON user(age, name, gender);

4.5 全文索引(FULLTEXT)

• 用于长文本模糊查询（替代低效 LIKE %关键词%），主要用来查找文本中的关键字，而不是直接与索引中的值相比较
• 支持 MyISAM / InnoDB（MySQL 5.6+）

CREATE FULLTEXT INDEX idx_content ON article(content);

五、索引使用场景

5.1 ✅ 推荐建索引

1. 主键自动创建索引
2. WHERE 经常查询的列
3. ORDER BY / GROUP BY 排序分组列
4. JOIN 关联查询的关联列，外键关联的字段
5. 数据量大（1000 行以上）的表
6. 列值重复率低（性别这种重复率高的不建）

5.2 ❌ 不推荐建索引

1. 表数据很小
2. 频繁写入、读少写多
3. 列值重复率极高（性别、状态）
4. 经常修改的列
5. 大文本字段（考虑前缀索引）

六、索引失效场景

只要出现以下情况，索引直接失效，变成全表扫描：

6.1 索引列上运算 / 函数

-- ❌ 失效
SELECT * FROM user WHERE YEAR(create_time) = 2025;
SELECT * FROM users WHERE create_time >= '2023-01-01'; -- 有效

6.2 模糊查询以 % 通配符开头

-- ❌ 失效
SELECT * FROM user WHERE name LIKE '%张三';

6.3 违反最左前缀原则

组合索引必须从最左列开始使用

-- ✅ 命中索引（全匹配）
SELECT * FROM user WHERE age=18 AND name='张三' AND gender=1;

-- ✅ 命中索引（最左2列）
SELECT * FROM user WHERE age=18 AND name='张三';

-- ✅ 命中索引（最左1列）
SELECT * FROM user WHERE age=18;

-- ❌ 不命中索引（跳过age）
SELECT * FROM user WHERE name='张三';

6.4 使用！= / <> / IS NOT NULL

SELECT * FROM users WHERE status != 1; -- 失效

6.5 隐式类型转换

SELECT * FROM users WHERE phone = 13800138000; -- phone是varchar，失效

6.6 OR 连接非索引列

SELECT * FROM users WHERE name = '张三' OR age = 20; -- 可能失效

七、常见语法

7.1 创建索引

-- 普通索引
CREATE INDEX 索引名 ON 表名(列名);

-- 唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名);

-- 联合索引
CREATE INDEX 索引名 ON 表名(列1, 列2, 列3);

-- 修改表结构添加索引
ALTER TABLE table_name ADD INDEX idx_name(column);
ALTER TABLE table_name ADD UNIQUE INDEX idx_name(column);

7.2 查看索引

-- 查看索引
SHOW INDEX FROM 表名;
SHOW INDEX FROM table_name;
SHOW CREATE TABLE table_name;

7.3 删除索引

-- 删除索引
DROP INDEX idx_name ON table_name;
ALTER TABLE table_name DROP INDEX idx_name;

7.4 查看是否命中索引

-- 分析索引使用
EXPLAIN SELECT * FROM table WHERE condition;
EXPLAIN SELECT * FROM user WHERE age=18;

• type: ref/range → 命中索引
• type: ALL → 全表扫描（失效）

八、使用注意

1. 优先考虑组合索引，而不是多个单列索引
2. 选择区分度高的字段作为索引
3. 控制索引数量，一般不超过5个
4. 避免过长的索引字段，考虑前缀索引
5. 定期分析索引使用情况，删除无用索引
6. 更新统计信息：ANALYZE TABLE table_name;

九、常见问题

9.1 什么是聚簇索引？什么是非聚簇索引？

• 聚簇索引：主键索引，数据和索引存在一起，一张表只有一个。
• 非聚簇索引：二级索引（普通 / 唯一），叶子节点存主键值，需要回表查数据。

9.2 什么是回表？怎么避免回表？

通过二级索引查到主键，再通过主键查完整数据 = 回表。
避免：覆盖索引（查询的列刚好都在索引里）

9.3 主键自增好还是 UUID 好？

一定用自增主键

• 自增：顺序插入，B+ 树分裂少，性能高。
• UUID：无序，插入频繁分裂，空间大、速度慢