当前位置：首页 > news >正文

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

news 来源：原创 2025/8/3 1:44:46

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

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 从入门到精通（七）：视图全面详解 —— 虚拟表的灵活运用》，将深入讲解视图的创建、更新、修改与删除，以及如何通过视图简化复杂查询、提升数据安全性。

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

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：删除冗余索引

相关文章：