🎬 个人主页:艾莉丝努力练剑
❄专栏传送门:《C语言》《数据结构与算法》《C/C++干货分享&学习过程记录》 《Linux操作系统编程详解》《笔试/面试常见算法:从基础到进阶》《Python干货分享》
⭐️为天地立心,为生民立命,为往圣继绝学,为万世开太平
🎬 艾莉丝的简介:

文章目录
- MySQL 索引思维导图
- 1 ~> 索引底层核心原理
-
- 1.1 磁盘 IO 与 Page 机制
-
- 1.1.1 核心概念
- 1.2 单页与多页查找优化
-
- 1.2.1 单页内部优化:页目录
- 1.2.2 多页场景的原生瓶颈
- 1.3 B + 树索引结构
-
- 1.3.1 结构定义
- 1.3.2 核心优势
- 1.4 候选数据结构对比
-
- 1.4.1 线性结构与二叉树族
- 1.4.2 Hash 索引
- 1.4.4 B 树 vs B + 树
- 2 ~> 聚簇索引与非聚簇索引
-
- 2.1 核心定义
- 2.2 InnoDB 聚簇索引
-
- 2.2.1 主键索引(聚簇索引)
- 2.2.2 普通(辅助)索引
- 2.2.3 回表查询
- 2.3 MyISAM 非聚簇索引
-
- 2.3.1 结构特征
- 2.3.2 文件形式
- 2.4 引擎索引文件对比
- 3 ~> 索引分类与 SQL 操作
-
- 3.1 主键索引
-
- 3.1.1 核心特点
- 3.1.2 创建语法
- 3.1.3 查看索引
- 3.1.4 删除语法
- 3.2 唯一键索引
-
- 3.2.1 核心特点
- 3.2.2 创建语法
- 3.2.3 删除语法
- 3.3 普通索引
-
- 3.3.1 核心特点
- 3.3.2 创建语法
- 3.3.3 删除语法
- 3.4 复合索引
-
- 3.4.1 定义与创建
- 3.4.2 最左匹配原则
- 3.4.3 索引覆盖
- 3.4.4 设计原则
- 4 ~> 索引设计原则
-
- 4.1 推荐创建索引的场景
- 4.2 不推荐创建索引的场景
- 4.3 补充:常见索引失效场景(面试高频)
- 5 ~> 全文索引(选学)
-
- 5.1 基础概念
-
- 5.1.1 **全文索引支持情况**
- 5.1.2 作用
- 5.1.3 创建语法
- 5.2 使用与验证
-
- 5.2.1 查询语法
- 5.2.2 执行计划验证
- 6 ~> 性能复盘与优化体系
-
- 6.1 索引的价值与代价
- 6.2 数据库性能优化体系
- 结尾

MySQL 索引思维导图
MySQL索引体系
├─ 一、索引底层核心原理
│ ├─ 1. 磁盘IO与Page机制
│ ├─ 2. 单页与多页查找优化
│ │ ├─ 页目录(单页内优化)
│ │ └─ 多页链表的性能瓶颈
│ ├─ 3. B+树索引结构
│ │ ├─ 结构特征
│ │ └─ 核心优势
│ └─ 4. 候选数据结构对比
│ ├─ 线性结构 / 二叉搜索树 / 平衡二叉树
│ ├─ Hash索引
│ └─ B树 vs B+树
├─ 二、聚簇索引与非聚簇索引
│ ├─ 1. InnoDB 聚簇索引
│ │ ├─ 主键索引(聚簇索引本体)
│ │ ├─ 普通辅助索引
│ │ └─ 回表查询
│ ├─ 2. MyISAM 非聚簇索引
│ │ ├─ 主键索引
│ │ └─ 普通索引
│ └─ 3. 文件存储形式对比
├─ 三、索引分类与SQL操作
│ ├─ 1. 主键索引(创建/查看/删除)
│ ├─ 2. 唯一键索引(创建/查看/删除)
│ ├─ 3. 普通索引(创建/查看/删除)
│ └─ 4. 复合索引
│ ├─ 最左匹配原则
│ └─ 索引覆盖
├─ 四、索引设计原则
│ ├─ 推荐建索引的场景
│ └─ 不推荐建索引的场景
├─ 五、全文索引(选学)
│ ├─ 基础概念与引擎支持
│ └─ 查询语法与验证
└─ 六、性能复盘与优化体系
├─ 索引的收益与代价
└─ 高阶数据库优化方向
1 ~> 索引底层核心原理
1.1 磁盘 IO 与 Page 机制
1.1.1 核心概念
MySQL(InnoDB 引擎)与磁盘进行 IO 交互的最小单位是Page(页),默认大小为 16KB,而非按单条记录交互。
- 设计依据:局部性原理—— 单次加载整页数据,后续访问同页内的相邻数据无需再次发起磁盘 IO,大幅减少 IO 次数。
- 性能本质:磁盘 IO 的核心瓶颈是IO 次数,而非单次 IO 传输的数据量。
1.2 单页与多页查找优化
1.2.1 单页内部优化:页目录
- 页内数据按主键有序排列,通过双向链表组织;数据有序是引入页目录的前提。
- 页目录是典型的空间换时间方案:将页内数据划分为多个分组,目录项存储每组的起始主键值。
- 查找流程:先通过目录定位分组,再在分组内线性遍历,避免全页扫描。
1.2.2 多页场景的原生瓶颈
- 多个 Page 通过page_prev/page_next指针构成双向链表。
- 无索引时,多页查找为线性遍历,需依次加载 Page 到内存,数据量越大,IO 次数越多,性能越低。
- 单页的页目录优化无法解决页间遍历的 IO 瓶颈。
1.3 B + 树索引结构
1.3.1 结构定义
B + 树是一种多叉平衡查找树,是 InnoDB 索引的底层实现,构建逻辑为对 Page 层级也建立目录:
1.3.2 核心优势
- 非叶子节点不存数据 → 单页容纳更多目录项 → 树高更低 → 磁盘 IO 次数更少。
- 叶子节点双向链表串联 → 天然支持高效范围查询,无需重复回溯遍历树结构。
1.4 候选数据结构对比
1.4.1 线性结构与二叉树族
| 普通链表 | 纯线性遍历,时间复杂度 O (n),海量数据下 IO 次数不可接受 |
| 二叉搜索树 | 存在退化问题,极端情况退化为链表,性能骤降 |
| AVL 树 / 红黑树 | 虽为平衡 / 近似平衡结构,但本质是二叉树,树高过高;千万级数据树高约 20 + 层,对应 20 + 次 IO,远高于 B + 树 |
1.4.2 Hash 索引
- 优势:等值查询时间复杂度接近 O (1)。
- 劣势:不支持范围查询、排序、前缀模糊匹配;存在哈希冲突开销。
- 引擎支持:MEMORY 引擎支持显式 HASH 索引;InnoDB 不支持用户手动创建,仅内部实现自适应哈希索引(由引擎自动根据热点数据构建,用户无法干预)。
1.4.4 B 树 vs B + 树
| 数据存储 | 所有节点都存储键值 + 完整行数据 | 仅叶子节点存储完整数据,非叶子节点仅存键值 + 页指针 |
| 叶子节点连接 | 叶子节点之间无链表串联 | 所有叶子节点通过双向链表级联 |
| 树高特征 | 单节点目录项少,树更高,IO 次数多 | 单节点目录项多,树更矮,IO 次数少 |
| 范围查询 | 需多次回溯遍历树,效率低 | 遍历叶子链表即可,天然支持范围查询 |
2 ~> 聚簇索引与非聚簇索引
2.1 核心定义
- 聚簇索引:索引结构与数据存储在一起,叶子节点保存完整行数据,索引即数据。
- 非聚簇索引:索引结构与数据文件分离,叶子节点保存数据行的物理地址。
2.2 InnoDB 聚簇索引
2.2.1 主键索引(聚簇索引)
- 一张表有且仅有一个聚簇索引,默认以主键构建。
- 无显式主键时,InnoDB 会选择第一个唯一非空索引作为聚簇索引键;若均无,则生成隐藏的 6 字节ROWID作为聚簇索引键。
- 叶子节点存储完整行数据,主键查询无需回表,性能最高。
- 文件形式:表数据 + 索引统一存储在.ibd文件中。
2.2.2 普通(辅助)索引
- 基于非主键列构建的 B + 树,一张表可存在多个普通索引,对应多棵 B + 树。
- 叶子节点仅存储索引列的值 + 对应行的主键值,不存储完整行数据。
2.2.3 回表查询
通过普通索引定位到主键后,再通过主键索引查询完整行数据的过程,称为回表。本质是两次 B + 树查找,性能低于主键直接查询。
2.3 MyISAM 非聚簇索引
2.3.1 结构特征
- 主键索引与普通索引结构无本质差异,叶子节点均存储数据行的物理地址。
- 主键索引仅约束键值唯一,普通索引允许键值重复。
- 不存在回表概念:查到索引后直接通过物理地址读取数据,仅一次定位。
2.3.2 文件形式
- .frm:表结构文件(MySQL 5.x,8.0 已废除)
- .MYD:数据文件(My Data)
- .MYI:索引文件(My Index)
2.4 引擎索引文件对比
| InnoDB | 聚簇索引 | 索引与数据合一 | .ibd(数据 + 索引) |
| MyISAM | 非聚簇索引 | 索引与数据分离 | .MYD(数据) + .MYI(索引) |
3 ~> 索引分类与 SQL 操作
3.1 主键索引
3.1.1 核心特点
- 单表最多一个,支持复合主键。
- 列值非空、不重复,查询效率最高。
- InnoDB 下主键索引即聚簇索引。
3.1.2 创建语法
— 方式1:建表时在字段后指定
CREATE TABLE user1(id INT PRIMARY KEY, name VARCHAR(30));
— 方式2:建表末尾统一指定
CREATE TABLE user2(id INT, name VARCHAR(30), PRIMARY KEY(id));
— 方式3:建表后追加
CREATE TABLE user3(id INT, name VARCHAR(30));
ALTER TABLE user3 ADD PRIMARY KEY(id);
3.1.3 查看索引
— 方式1(最详细,推荐)
SHOW INDEX FROM 表名;
— 方式2
SHOW KEYS FROM 表名;
— 方式3(简略查看)
DESC 表名;
字段说明:Key_name为索引名,主键索引默认名为PRIMARY;Index_type: BTREE表示 B + 树索引(MySQL 统一标识为 BTREE,InnoDB/MyISAM 底层为 B + 树实现)。
3.1.4 删除语法
ALTER TABLE 表名 DROP PRIMARY KEY;
3.2 唯一键索引
3.2.1 核心特点
- 单表可创建多个。
- 索引列值不可重复,允许存在 NULL(可多个 NULL)。
- 若列同时指定NOT NULL,则约束等价于主键索引。
3.2.2 创建语法
— 方式1:字段后指定
CREATE TABLE user4(id INT PRIMARY KEY, name VARCHAR(30) UNIQUE);
— 方式2:建表末尾指定
CREATE TABLE user5(id INT PRIMARY KEY, name VARCHAR(30), UNIQUE(name));
— 方式3:建表后追加
CREATE TABLE user6(id INT PRIMARY KEY, name VARCHAR(30));
ALTER TABLE user6 ADD UNIQUE(name);
3.2.3 删除语法
— 统一使用 DROP INDEX,不能用 DROP UNIQUE
ALTER TABLE 表名 DROP INDEX 索引名;
— 等价写法
DROP INDEX 索引名 ON 表名;
3.3 普通索引
3.3.1 核心特点
- 单表可创建多个,是业务开发中最常用的索引类型。
- 允许列值重复、为 NULL,仅用于提升查询效率。
3.3.2 创建语法
— 方式1:建表末尾指定
CREATE TABLE user8(
id INT PRIMARY KEY,
name VARCHAR(20),
email VARCHAR(30),
INDEX(name)
);
— 方式2:建表后追加
CREATE TABLE user9(id INT PRIMARY KEY, name VARCHAR(20), email VARCHAR(30));
ALTER TABLE user9 ADD INDEX(name);
— 方式3:自定义索引名称
CREATE TABLE user10(id INT PRIMARY KEY, name VARCHAR(20), email VARCHAR(30));
CREATE INDEX idx_name ON user10(name);
3.3.3 删除语法
ALTER TABLE 表名 DROP INDEX 索引名;
— 或
DROP INDEX 索引名 ON 表名;
3.4 复合索引
3.4.1 定义与创建
在多个列上共同构建的索引,也叫联合索引。索引键由多列组合而成,B + 树按最左列优先排序。
— 创建复合索引(默认以首列名为索引名)
ALTER TABLE test1 ADD INDEX(name, email);
— 自定义索引名
CREATE INDEX idx_name_email ON test1(name, email);
3.4.2 最左匹配原则
查询条件必须从索引的最左前列开始匹配,不可跳过前列。
- 可命中:WHERE name='xxx'、WHERE name='xxx' AND email='yyy'
- 不可命中:WHERE email='yyy'
3.4.3 索引覆盖
若查询返回的字段全部包含在复合索引中,无需回表即可直接返回结果,称为索引覆盖(覆盖索引),可大幅提升查询性能。
3.4.4 设计原则
优先使用列数少的窄索引,避免过多列的宽索引,减少索引空间占用与写入维护成本。
4 ~> 索引设计原则
4.1 推荐创建索引的场景
- 频繁作为WHERE查询条件的字段。
- 区分度(唯一性)高的字段,如用户 ID、手机号、订单号。
- 读取频率高、更新频率低的字段。
4.2 不推荐创建索引的场景
- 区分度极低的字段,如性别、状态枚举,索引收益远低于维护成本。
- 更新非常频繁的字段,索引维护开销会抵消查询收益。
- 不会出现在WHERE、JOIN、ORDER BY子句中的字段。
- 数据量过小的表,全表扫描成本低于索引查找。
4.3 补充:常见索引失效场景(面试高频)
- 索引列参与运算、函数操作。
- 发生隐式类型转换。
- 模糊查询使用前缀通配符%xxx。
- 违反复合索引最左匹配原则。
- 使用OR连接非索引列。
- 数据量过小,优化器选择全表扫描。
5 ~> 全文索引(选学)
5.1 基础概念
5.1.1 全文索引支持情况
MySQL 5.6 及以上版本,InnoDB 引擎也支持全文索引;原生全文索引对中文支持不佳,中文全文检索通常借助 Sphinx/Coreseek 或 Elasticsearch 等中间件实现。
5.1.2 作用
解决大文本内容的检索问题,替代低效的LIKE '%xxx%'全表扫描。
5.1.3 创建语法
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title, body)
) ENGINE=MyISAM;
— MySQL 5.6+ 可改为 ENGINE=InnoDB
5.2 使用与验证
5.2.1 查询语法
— 使用全文索引检索
SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('database');
5.2.2 执行计划验证
— 命中全文索引:type 为 fulltext
EXPLAIN SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('database');
— 未命中索引(全表扫描):type 为 ALL
EXPLAIN SELECT * FROM articles WHERE body LIKE '%database%';
6 ~> 性能复盘与优化体系
6.1 索引的价值与代价
- 价值:典型的空间换时间方案,在海量数据下可将查询性能从秒级提升至毫秒级。
- 代价:增加磁盘存储空间;降低 INSERT、UPDATE、DELETE 性能(需同步维护索引树结构)。
6.2 数据库性能优化体系
索引是最基础的优化手段,高阶优化方案还包括:
- 分库分表
- 读写分离
- 引入缓存中间件
- SQL 语句优化
- 数据库参数调优
结尾
uu们,本文的内容到这里就全部结束了,艾莉丝在这里再次感谢您的阅读!
|
艾莉丝努力练剑
C/C++ & Linux 底层探索者 | 一个正在努力练剑的技术博主 👀 ❤️ ⭐ 💬 不要忘记给博主“一键四连”哦! “今日练剑达成!” “技术之路难免有困惑,但同行的人会让前进更有方向。” |
结语:希望对学习Linux相关内容的uu有所帮助,不要忘记给博主“一键四连”哦!
往期回顾:
【MYSQL】MYSQL学习的一大重点:索引(上)- 从磁盘存储到缓冲池机制
🗡博主在这里放了一只小狗,大家看完了摸摸小狗放松一下吧!🗡
૮₍ ˶ ˊ ᴥ ˋ˶₎ა





