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

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

news 2025/5/11 6:43:36

目录

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

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

http://www.xdnf.cn/news/376003.html

相关文章：

  • idea如何快速生成测试类
  • 【赵渝强老师】TiDB SQL层的工作机制
  • Yocto中`${B}`变量的作用
  • 论文图表自动编号与交叉引用
  • python中的继承和多态
  • FreeRTOS Queue消息队列-笔记
  • AlimaLinux设置静态IP
  • 护网HVV初级蓝队面试题总结
  • Axure ：基于中继器的列表删除 、 列表编辑
  • 自动语音拨号系统V2.6.0产品说明书
  • Dockers部署oscarfonts/geoserver镜像的Geoserver
  • BERT类模型
  • CenOS7切换使用界面
  • 推荐一款免费开源工程项目管理系统软件，根据工程项目全过程管理流程开发的OA 办公系统
  • 基于定制开发开源AI智能名片S2B2C商城小程序的公私域流量融合运营策略研究
  • 策略路由更改路径
  • Best Video下载器——抖音视频去水印工具
  • day21python打卡
  • 【Linux第三章】vim
  • HTTP/2概览及内核解析
  • excel大表导入数据库
  • comfyu BiRefNet-General模型下载及存放地方
  • JS正则表达式介绍（JavaScript正则表达式）
  • 《Python星球日记》 第51天：神经网络基础
  • 边缘计算从专家到小白
  • iperf3的介绍与舒勇
  • Kubernetes 生产实战(十五)：生产环境敏感信息纳入Secret管理指南
  • MLOps 详解
  • 汇编语言的温度魔法：单总线温度采集与显示的奇幻之旅
  • Java动态代理超详细解析：三步+内存图（堆栈分析）