MySQL 从入门到精通（五）：索引深度解析 —— 性能优化的核心武器

目录

一、索引概述：数据库的 “目录”

1.1 什么是索引？

1.2 索引的性能验证：用事实说话

实验环境准备

无索引查询耗时

有索引查询耗时

索引的 “空间换时间” 特性

二、索引的创建：三种核心方式

2.1 方式 1：CREATE INDEX（已存在表上创建）

2.2 方式 2：ALTER TABLE（添加索引）

2.3 方式 3：创建表时直接定义索引

三、索引的查看与分析：诊断性能的关键

3.1 查看索引的 4 种方法

方法 1：SHOW CREATE TABLE

方法 2：SHOW INDEX FROM

方法 3：EXPLAIN 分析查询计划

方法 4：查询系统表（高级）

四、索引的分类：从不同维度理解

4.1 按数据结构分类（物理存储方式）

4.2 按字段数量分类

4.3 按功能逻辑分类（最常见的面试考点）

4.4 按存储方式分类

五、索引的底层原理：为什么是 B+Tree？

5.1 为什么不选其他数据结构？

5.2 B+Tree 的核心优势

六、索引的优劣分析：权衡利弊

6.1 索引的优势

6.2 索引的弊端

七、索引设计原则：从实战中总结

7.1 必建索引的场景

7.2 避免索引的场景

7.3 其他最佳实践

八、练习与作业：动手实践

8.1 练习 1：创建带索引的表

8.2 练习 2：删除冗余索引

---

一、索引概述：数据库的 “目录”

1.1 什么是索引？

在数据库中，索引是一种特殊的 “数据结构文件”，它通过对表中一列或多列的值进行排序，从而加速数据的查询速度。
举个生活化的例子：查字典时，我们通过 “拼音目录” 或 “部首目录” 快速定位目标汉字，而不需要逐页翻查。数据库索引的作用类似 —— 它将表中的关键数据（如id、name）按特定规则排序存储，当执行WHERE、JOIN等查询时，数据库无需扫描全表，而是通过索引快速定位目标数据。

1.2 索引的性能验证：用事实说话

为了直观感受索引的作用，我们通过一个经典实验验证：

实验环境准备

-- 创建数据库
CREATE DATABASE mydb13_indexdb;
USE mydb13_indexdb;-- 创建无索引的student表
CREATE TABLE student(id INT, name VARCHAR(64), age INT(2)
);-- 插入百万级数据（通过自复制快速扩容）
INSERT INTO student VALUES(1,'das',20),(2,'dasdas',19),(3,'dsfsfsd',18),(4,'bbggbbg',22),(5,'eeeee',19);
INSERT INTO student SELECT * FROM student; -- 多次执行，直到数据量达到百万级

无索引查询耗时

插入一条测试数据后查询：

INSERT INTO student VALUES(666,'andy',40);
SELECT * FROM student WHERE id=666;

输出结果：
1 row in set (2.75 sec)
（查询耗时 2.75 秒，全表扫描效率极低）

有索引查询耗时

为id字段创建索引后再次查询：

CREATE INDEX id_index ON student(id); -- 创建索引（耗时12.13秒）
SELECT * FROM student WHERE id=666;

输出结果：
1 row in set (0.00 sec)
（查询耗时几乎为 0，性能提升约 275 万倍！）

索引的 “空间换时间” 特性

创建索引前，student表文件大小约 120MB；创建索引后，文件增大至 164MB。这是因为索引需要额外存储排序后的数据结构（如 B+Tree），但牺牲少量空间换取查询性能的大幅提升，在 “读多写少” 的业务场景中是非常划算的。

---

二、索引的创建：三种核心方式

2.1 方式 1：CREATE INDEX（已存在表上创建）

语法：

CREATE [UNIQUE] INDEX index_name 
ON table_name (column_name(length) [ASC|DESC]);

• UNIQUE（可选）：创建唯一索引（字段值不可重复）。
• index_name：索引名称（建议用 “字段名 + index” 的命名规则，如id_index）。
• table_name：目标表名。
• column_name：要索引的字段名。
• length（可选）：若字段是长文本（如VARCHAR(255)），可指定前length个字符作为索引（减少索引大小）。
• ASC/DESC（可选）：索引排序方式（默认ASC升序）。

示例：

-- 创建普通索引（按name字段前10个字符索引）
CREATE INDEX name_index ON student(name(10));-- 创建唯一索引（id字段值必须唯一）
CREATE UNIQUE INDEX id_unique_index ON student(id);

2.2 方式 2：ALTER TABLE（添加索引）

语法：

ALTER TABLE table_name 
ADD [UNIQUE] INDEX index_name (column_name(length) [ASC|DESC]);

示例：

-- 为age字段添加普通索引
ALTER TABLE student ADD INDEX age_index (age);-- 为name和age字段添加联合索引（多列索引）
ALTER TABLE student ADD INDEX name_age_index (name, age);

2.3 方式 3：创建表时直接定义索引

语法：

CREATE TABLE table_name (column1 INT,column2 VARCHAR(20),-- 普通索引INDEX index_name (column1),-- 唯一索引UNIQUE INDEX unique_index (column2),-- 主键索引（特殊的唯一索引，非空）PRIMARY KEY (column1)
);

注意：

• 主键索引（PRIMARY KEY）必须在创建表时指定，且字段需设置NOT NULL。
• 多列索引需按顺序定义（如(name, age)），查询时只有使用第一个字段才会触发索引（“最左匹配原则”）。

示例：

-- 创建表时定义普通索引和主键索引
CREATE TABLE workinfo(id INT(10) NOT NULL AUTO_INCREMENT,name VARCHAR(20) NOT NULL,type VARCHAR(10),PRIMARY KEY (id), -- 主键索引（自动唯一且非空）INDEX name_index (name(10)) -- 普通索引（name前10字符）
);

---

三、索引的查看与分析：诊断性能的关键

3.1 查看索引的 4 种方法

方法 1：SHOW CREATE TABLE

语法：

SHOW CREATE TABLE table_name \G;

输出示例：

CREATE TABLE `student` (`id` int DEFAULT NULL,`name` varchar(64) DEFAULT NULL,`age` int DEFAULT NULL,KEY `id_index` (`id`) -- 显示索引信息
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

方法 2：SHOW INDEX FROM

语法：

SHOW INDEX FROM table_name \G;

输出字段解析（以student表的id_index为例）：

字段名	含义	示例值
Table	索引所属的表名	student
Non_unique	是否非唯一索引（0 = 唯一，1 = 非唯一）	1
Key_name	索引名称	id_index
Seq_in_index	多列索引中字段的顺序（单列索引为 1）	1
Column_name	索引关联的字段名	id
Cardinality	索引的基数（估计的唯一值数量，值越大索引效果越好）	1000000
Sub_part	字段中用于索引的字符长度（NULL表示全字段）	NULL
Index_type	索引类型（如BTREE、HASH）	BTREE

方法 3：EXPLAIN 分析查询计划

语法：

EXPLAIN SELECT * FROM table_name WHERE condition \G;

关键字段：

• possible_keys：可能使用的索引（未使用时为NULL）。
• key：实际使用的索引（核心性能指标）。
• rows：扫描的行数（值越小性能越好）。

示例：

EXPLAIN SELECT * FROM student WHERE id=666 \G;

输出中key字段显示id_index，说明查询使用了索引；若key为NULL，则表示全表扫描。

方法 4：查询系统表（高级）

通过mysql.innodb_index_stats系统表查看索引统计信息：

-- 查看指定数据库的所有索引
SELECT * FROM mysql.innodb_index_stats 
WHERE database_name = 'mydb13_indexdb';-- 查看指定表的索引
SELECT * FROM mysql.innodb_index_stats 
WHERE database_name = 'mydb13_indexdb' AND table_name = 'student';

---

四、索引的分类：从不同维度理解

4.1 按数据结构分类（物理存储方式）

MySQL 支持 4 种索引结构，最常用的是B+Tree：

类型	特点	适用场景
B+Tree	所有数据存储在叶子节点，支持范围查询和排序，InnoDB 默认结构	常规查询、排序、分组
Hash	通过哈希函数计算值的存储位置，查询速度极快但无法范围查询	等值查询（如WHERE id=1）
R-Tree	空间索引，用于存储地理坐标等空间数据	GIS 系统（如地图位置查询）
T-Tree	适用于内存数据库，支持高并发写入	MySQL Cluster 集群场景

4.2 按字段数量分类

• 单列索引：基于单个字段创建（如INDEX (name)）。
• 多列索引（联合索引）：基于多个字段创建（如INDEX (name, age)），需遵循 “最左匹配原则”（只有查询条件包含前导字段时才会触发索引）。

4.3 按功能逻辑分类（最常见的面试考点）

类型	特点	示例
普通索引	加速查询，无唯一性约束	INDEX (name)
唯一索引	加速查询 + 保证字段值唯一（允许NULL）	UNIQUE INDEX (email)
主键索引	特殊的唯一索引（不允许NULL），一张表只能有一个	PRIMARY KEY (id)
全文索引	用于文本内容的模糊搜索（MyISAM 引擎支持，InnoDB 5.6 + 支持）	FULLTEXT INDEX (content)
空间索引	存储和查询空间数据（如点、线、多边形）	SPATIAL INDEX (location)

4.4 按存储方式分类

• 聚簇索引（聚集索引）：
  索引数据与表数据存储在一起（物理连续），一张表仅有一个聚簇索引（通常是主键）。
  优点：查询效率极高（直接定位数据）；
  缺点：插入顺序影响性能（非顺序插入会导致页分裂）。

• 非聚簇索引（二级索引）：
  索引数据与表数据分开存储，通过 “键值 + 行指针” 关联。
  优点：支持多字段索引；
  缺点：可能需要 “回表”（先查索引，再查原表）。

---

五、索引的底层原理：为什么是 B+Tree？

5.1 为什么不选其他数据结构？

• Hash 索引：虽然等值查询快，但无法排序和范围查询（如WHERE age>20）。
• 二叉树：可能退化为链表（如所有节点只有右子树），导致查询时间复杂度从O(logN)变为O(N)。
• 平衡二叉树（AVL）：插入 / 删除时需要频繁旋转调整，维护成本高，且单节点存储数据少（磁盘 IO 次数多）。

5.2 B+Tree 的核心优势

B+Tree 是 B-Tree 的变种，其核心设计如下：

• 所有数据存储在叶子节点（非叶子节点仅存索引），叶子节点通过指针连接（支持范围查询）。
• 非叶子节点可以存储更多索引键（通过增加子节点数降低树的高度），减少磁盘 IO 次数。

InnoDB 的 B+Tree 实现：

• 叶子节点存储完整数据（聚簇索引）或主键值（非聚簇索引）。
• 单个节点大小为 16KB（InnoDB 默认页大小），可存储大量索引键（如INT类型可存约 4000 个键）。

---

六、索引的优劣分析：权衡利弊

6.1 索引的优势

• 查询速度提升：百万级数据查询从秒级到毫秒级（如前文实验）。
• 唯一性约束：唯一索引可替代UNIQUE约束，避免重复数据。
• 排序 / 分组加速：索引已排序，ORDER BY和GROUP BY无需额外扫描。
• 连表查询优化：主外键字段的索引可大幅减少 JOIN 时的扫描行数。

6.2 索引的弊端

• 空间占用：索引文件可能占原表空间的 20%-50%（如 120MB 的表可能需要 164MB 的索引）。
• 写入性能下降：插入 / 更新 / 删除时需同步维护索引（B+Tree 的分裂、合并操作）。
• 过度索引风险：索引越多，维护成本越高，可能导致写入性能骤降。

---

七、索引设计原则：从实战中总结

7.1 必建索引的场景

• 高频查询字段：如WHERE、JOIN、ORDER BY中的字段。
• 唯一性高的字段：如用户id（基数大，索引效果好）。
• 排序 / 分组字段：索引已排序，避免全表扫描后再排序。

7.2 避免索引的场景

• 低基数字段：如 “性别”（只有男/女），索引无法有效过滤数据。
• 频繁更新的字段：如订单状态（待支付/已支付），索引维护成本高。
• 长文本字段：如TEXT类型，建议使用前缀索引（如前 10 个字符）。

7.3 其他最佳实践

• 限制索引数量：单表索引数建议不超过 5 个（过多索引影响写入）。
• 多列索引的顺序：将高频查询字段、高基数字段放在前面（如(name, age)优于(age, name)）。
• 定期清理冗余索引：通过SHOW INDEX检查未使用的索引（Cardinality低的索引可删除）。

---

八、练习与作业：动手实践

8.1 练习 1：创建带索引的表

需求：新建数据库mydb14_job，创建workinfo表，要求：

• id字段为主键，自增，唯一索引（降序）。
• name字段创建长度为 10 的普通索引。
• type和address字段创建联合索引。

实现步骤：

-- 创建数据库
CREATE DATABASE mydb14_job;
USE mydb14_job;-- 创建表并定义索引
CREATE TABLE workinfo(id INT(10) NOT NULL AUTO_INCREMENT,name VARCHAR(20) NOT NULL,type VARCHAR(10),address VARCHAR(50),wage INT,content TINYTEXT,extra TEXT,PRIMARY KEY (id), -- 主键索引（聚簇索引）UNIQUE INDEX index_id (id DESC), -- 唯一索引（降序）INDEX index_name (name(10)), -- 普通索引（前缀10字符）INDEX index_t (type, address) -- 联合索引
);-- 验证索引
SHOW INDEX FROM workinfo \G;

8.2 练习 2：删除冗余索引

需求：删除workinfo表的index_id唯一索引。

DROP INDEX index_id ON workinfo;

---

下一篇预告：《MySQL 从入门到精通（七）：视图全面详解 —— 虚拟表的灵活运用》，将深入讲解视图的创建、更新、修改与删除，以及如何通过视图简化复杂查询、提升数据安全性。