news 2026/7/14 19:29:01

一文吃透 MySQL 索引【基础篇】：B + 树入门、简易页存储、索引分类与索引覆盖实战

作者头像

张小明

前端开发工程师

1.2k 24
文章封面图
一文吃透 MySQL 索引【基础篇】：B + 树入门、简易页存储、索引分类与索引覆盖实战

📑 MySQL索引目录

  • 1. 索引简介
    • 1.1 索引是什么？
  • 2. 索引可选数据结构对比
    • 2.1 N叉树
    • 2.2 B+树
  • 3. MySQL数据页
    • 3.1 为什么要使用页
    • 3.2 页基础组成简要说明
  • 4. 索引全分类详解
    • 4.1 主键索引
    • 4.2 普通索引
    • 4.3 唯一索引
    • 4.4 全文索引
    • 4.5 聚簇索引
    • 4.6 非聚簇索引
    • 4.7 索引覆盖
  • 5. 索引实操：创建、查看、删除
    • 5.1 索引自动创建规则
    • 5.2 手动创建单值索引
    • 5.3 复合索引创建
    • 5.4 删除索引
    • 5.5 创建索引避坑要点
  • 6. B+树在MySQL索引中的应用

前言:
MySQL 索引是数据库查询优化的核心手段，合理使用索引能大幅降低磁盘 IO、成倍提升查询速度，是后端开发、面试必掌握的核心知识点。本文为索引基础入门篇，重点讲解索引概念、分类与实操，InnoDB 页底层完整细节将在以后文章单独深挖。

1. 索引简介

1.1 索引是什么？

索引本质是一种用于优化查询效率的数据结构，它可以高效协助数据库完成数据表的查找与更新操作；通过预设规则有序排列表中记录，查询时依托索引检索，能够大幅提升查询速度;
简单理解：没有索引时数据库会逐行遍历整张表；建立索引后，数据库可通过索引树直接跳转匹配目标数据，是解决慢SQL最核心的手段。
类比书籍目录，通过提前有序存储字段值，快速定位磁盘数据，避免全表扫描，大幅减少IO开销。

类比新华字典偏旁目录：目录代替逐页翻书，索引代替全表扫描，快速定位数据，大幅减少磁盘IO，是优化慢SQL的核心方案。
当然新华字典肯定是不止于只有一个目录,拼音,偏旁等等目录来加快我们查找字,这一个个目录也相当于索引,能加快我们查询速度;
同理:索引也是一样的;

🔙 返回目录

2. 索引可选数据结构对比

我们之前学习过:顺序表,链表,栈和队列,优先级队列,二叉树,哈希表等等这些数据结构,以及后面会学到的红黑树,AVL树等等,有没有适合作为数据库的索引的呢?
我们之前学习的数据结构,都无法适配数据库磁盘读写场景,存在明显的短板,因此MySQL最终选用改良过的B+树作为索引的底层结构;
简单说明：磁盘IO开销远大于内存操作，索引结构的核心目标是尽可能降低树的高度、减少磁盘读取次数，传统树结构很难满足该需求。
在了解B+树之前,我们首先先来了解N叉树;

🔙 返回目录
2.1 N叉树

我们常说的B树,就是路平衡N叉树（B树）,它在二叉树的基础上做了优化,单个节点可以存放多个关键字与节点的分支,能够有效降低树的高度,减少磁盘IO次数;

但B树存在明显缺陷：根节点、分支节点、叶子节点都会保存完整数据；如果执行范围查询，需要多次上下跳转多层节点读取磁盘，IO开销偏高，因此MySQL没有直接选用B树作为索引底层结构。

🔙 返回目录
2.2 B+树

B+树结构:

非叶子索引节点:

  • 关键字数量范围：(1 ≤ k ≤ m-1)，4 阶最多存放 3 个分界 key
  • 关键字数量 = 子指针数量 - 1

叶子数据节点:

  • 关键字下限：[ m/2]-1，4 阶最少 1 个 key，最多 3 个 key
  • 节点内关键字升序排列，节点间双向指针串联

平衡约束:

  • 所有叶子节点处于同一层，树永远平衡


B+树特点 :

  1. 整树天然平衡，数据全局有序，增删改操作时间复杂度稳定为 (O(log n))
  2. 非叶子索引节点仅存储分界关键字，不存放完整数据记录；全部真实业务数据仅保存在叶子节点
  3. 所有叶子节点通过双向有序链表串联，可顺着链表直接遍历全量数据，支持连续范围查询
  4. 索引节点占用空间更小，内存可缓存更多路由关键字，减少磁盘 IO 次数
  5. 所有叶子节点高度完全一致，查找任意 key 的磁盘访问次数固定，性能均衡无波动

B树和B+树对比:

  1. B + 树叶子节点有序链表相连，区间查询、分页、排序查询效率远高于 B 树；B 树区间查询需要多次回溯上层节点
  2. B + 树非叶子节点的分界关键字，全部重复存在于下层叶子节点；B 树关键字只出现一次，节点自带数据
    相同阶数、相同数据量下，B + 树高度更低，磁盘 IO 更少；B 树节点同时存索引 + 数据，单节点容纳关键字更少
  3. B + 树无论精确查找还是范围查找，都必须走到叶子节点，查找耗时稳定；B 树可能在中层节点就命中数据，查询耗时不固定
  4. 数据库索引优先选用 B + 树，文件系统索引多采用 B 树
🔙 返回目录

3. MySQL数据页

在.ibd文件中最重要的结构体就是page(页);
页是InnoDB引擎磁盘存储的最小单位，默认大小16KB，表数据、索引都会存放在数据页内;

🔙 返回目录
3.1 为什么要使用页

依托计算机局部性原理：空间局部性说明相邻数据大概率会被访问，一次性载入一页数据到缓冲池，后续查询同页数据直接读内存，大幅减少磁盘I/O。
每次内存与磁盘的交互至少读取一页，所以在磁盘中每个页内部的地址都是连续的;
同时B+树索引的每一个节点本质就是数据页；可通过系统变量innodb_page_size查看页大小;

showvariableslike'innodb_page_size';


换算成字节:16字节

局部性原理:

  1. 时间局部性：刚访问过的数据，短时间内大概率会再次访问；
  2. 空间局部性： 当前访问的数据，相邻位置的数据大概率很快会用到。
  3. MySQL 数据页就是利用空间局部性，一次性加载相邻多条数据到内存，减少磁盘 IO。
🔙 返回目录
3.2 页基础组成简要说明

InnoDB 16KB数据页分为7个固定模块，各司其职：
1. 文件头File Header：记录页号、前后页关联等全局信息；
2. 页头Page Header：记录本页记录数量、空闲空间等页内状态；
3. 最小/最大虚拟记录：界定页内记录边界，方便有序遍历；
4. 用户记录User Records：存放真实表行数据，按主键有序排列；
5. 空闲空间Free Space：页内未使用区域，用于新增记录；
6. 页目录Page Directory：记录行偏移，实现页内快速二分查找；
7. 文件尾File Trailer：校验页完整性，防止数据损坏。

页的结构我们基础阶段只需要知道页有:页头 数据行 页尾结构就行,后续学习MySQL更深入知识之后,会具体学习页的结构;

🔙 返回目录

4. 索引全分类详解

4.1 主键索引

主键索引(primary key) ,inoDB中也叫聚簇索引,数据库中唯一并且非空的核心索引,可以通过primary key约束进行创建;

  1. 唯一性约束：整张表只能存在 1 个主键索引，主键字段值绝对不允许重复；
  2. 非空约束：主键字段所有行数据不能为 NULL；
  3. 聚簇存储（InnoDB）：主键索引的 B + 树叶子节点直接存储完整行数据，表数据本身就是主键索引树；
  4. 自动生成：建表时指定 primary key 会自动创建主键索引；若未指定主键，InnoDB 会选唯一非空字段充当聚簇索引，无则内置隐藏 6 字节 rowid 作为主键。
🔙 返回目录
4.2 普通索引

最基本的索引类型,没有唯一性约束;一张表可以创建多个普通索引;

  1. 字段允许存在多条重复数据；
  2. 支持 NULL：索引字段可以插入 NULL，NULL 值不参与索引排序；
  3. 属于二级索引：InnoDB 中普通索引 B + 树叶子节点仅存储主键值，查询命中索引后需要回表获取完整行数据；
  4. 索引数量没有限制,但普通索引过多 会影响查询速率;
🔙 返回目录
4.3 唯一索引

属于二级索引:当表中定义一个 唯一键unique时自动创建;

  1. 索引字段的所有值不能重复;
  2. 可以存在单条null(null不参与与等值对比,多条null会重复判定);
  3. InnoDB 底层 B + 树叶子节点存储「索引列 + 主键 ID」，查询存在回表逻辑;
  4. 没有数量限制,单张表能存在多个唯一索引;
🔙 返回目录
4.4 全文索引

仅支持文本类型:char,varchar,text,专门用于关键词全文检索 , 区别于普通 B + 树等值 / 前缀匹配索引。
InnoDB、MyISAM 两大引擎支持；生产环境主流使用 InnoDB
作用: 加速文本关键词模糊查询,替代低效的like ,提升海量文本的搜索性能;

🔙 返回目录
4.5 聚簇索引

聚簇索引（Clustered Index）是 InnoDB 引擎默认主键索引，它把整行完整数据和索引存放在同一棵 B + 树叶子节点，数据行按主键顺序物理排列
叶子节点 = 完整行数据
非叶子节点只存主键值 :仅用于索引跳转，不存完整数据。
优点:

1. 查询极快
2. 主键查询范围高效
3. 数据物理上紧挨在一起,降低IO次数

缺点:

1. 每一条二级索引都要存储主键,主键越长,二级索引占用磁盘越多,
2. 删除大量数据会产生数据页空洞,需要定期整理碎片
3. 主键无序的时候会造成页分裂,随机 UUID 作为主键时，新数据插入中间，频繁拆分数据页，写入性能降低

🔙 返回目录
4.6 非聚簇索引

聚簇索引以外的索引;
一张表可以有多个非聚簇索引;
非叶子节点：存储索引字段的值，用于快速二分查找；
叶子节点：存储「索引列值 + 主键 ID」，不存完整行数据；
回表查询:

  1. 执行普通索引查询时，先遍历二级索引 B + 树，拿到匹配记录的主键；
  2. 再拿着主键去聚簇索引 B + 树，查找完整行数据；
  3. 两次 B + 树 IO 访问，这个过程叫回表，会增加查询开销
🔙 返回目录
4.7 索引覆盖

当一个select语句使用了普通索引并且查询列表中刚好是创建普通索引时候的部分或者全部列,这时就可以直接返回数据,不需要再一次进行回表查询;

🔙 返回目录

5. 索引实操：创建、查看、删除

5.1 索引自动创建规则

当我们创建一张表的时候主动加上:主键约束primary key , 外键约束:foreign key , 唯一约束:unique 时,MySQL自动给对应的列创建一个索引;
但是如果我们不指定任何约束时,MySQL会默认为每一列生成一个索引并用ROW_ID进行标识;
InnoDB 自动生成隐藏ROW_ID6个字节;

🔙 返回目录
5.2 手动创建单值索引

primary key:

createtableifnotexiststest(idintprimarykey,namevarchar(20));

当然我们也可以通过show keys from 表名或者show index from的方式进行查看主键情况;

字段截图值完整含义说明
Tabletest索引所属数据表名
Non_unique00=索引唯一（主键/唯一索引）；1=允许重复（普通索引）
Key_namePRIMARY索引名称；主键索引固定为 PRIMARY，不可自定义
Seq_in_index1联合索引内字段顺序，单字段索引恒为1
Column_nameid创建索引绑定的字段名
CollationAA=字段升序存储；NULL=无排序（HASH索引）
Cardinality0索引基数，代表字段不重复值预估数量；空表默认0，影响优化器索引选择
Sub_part(Null)前缀索引截取长度，NULL代表完整字段索引
Packed(Null)字段压缩存储信息，NULL表示未压缩
Null(Null)字段是否允许NULL；主键字段不允许为空，故此处无值
Index_typeBTREEInnoDB默认B+树索引；MEMORY引擎支持HASH
Comment空索引注释，建索引时可通过COMMENT指定
Index_comment空MySQL8.0新增，索引独立注释
VisibleYES索引是否对优化器可见；INVISIBLE隐藏索引，用于灰度测试
Expression(Null)函数索引表达式，普通列索引为NULL

普通索引:
方式1:建表时直接定义:

createtableifnotexiststest_index(idint,namevarchar(20),ageint,index(age));

方式二:已有表追加普通索引

createtableifnotexiststest_index(idint,namevarchar(20),ageint);altertabletest_indexaddindexidx_age(age);

方式三:create index 单独创建

createtableifnotexiststest_index(idint,namevarchar(20),ageint);createindexidx_ageontest_index(age);

表结构:

唯一索引:unique
方式一:建表时直接定义

createtableifnotexiststest_index(idintprimarykey,namevarchar(20)unique,ageint);

方式二: 已有表追加唯一索引

createtableifnotexiststest_index(idintprimarykey,namevarchar(20),ageint);altertabletest_indexadduniqueidx_age(name);

方式三:create unique index 单独创建

createtableifnotexiststest_index(idintprimarykey,namevarchar(20),ageint);createuniqueindextest_nameontest_index(name);

表结构:

🔙 返回目录
5.3 复合索引创建
方式一:创建表时指定;
createtableifnotexiststest_index1(idintprimarykey,namevarchar(20),ageint,index(name,age));

方式二:修改表中的列

createtableifnotexiststest_index1(idintprimarykey,namevarchar(20),ageint);altertabletest_index1addindex(name,age);

方式三:单独创建并指定索引名字

createtableifnotexiststest_index1(idintprimarykey,namevarchar(20),ageint);createindexindex_1ontest_index1(name,age);

查看表索引:

🔙 返回目录
5.4 删除索引

删除主键

createtableifnotexiststest_index1(idintprimarykeyauto_increment,namevarchar(20),ageint);showkeysfromtest_index1;

我们先创建一个自增主键;

语法:

altertable表名dropprimarykey;

当我们执行的时候:

报错了,为什么呢?
1075 - 表定义错误；只能有一个自动列，并且它必须被定义为键
意思就是当我们定义主键为自增主键的时候,我们要先删除自增的属性:auto_increment;

altertabletest_index1modifyidint;altertabletest_index1dropprimarykey;

删除其他索引:
语法:

dropindex索引名on表名或者altertable表名dropindex索引名;

创建一个测试表:

createtableifnotexiststest_index1(idintprimarykey,namevarchar(20),ageint,index(name,age));

进行删除;

dropindexnameontest_index1;-- 或者 : alter table test_index1 drop index name;

查看表结构:

🔙 返回目录
5.5 创建索引避坑要点
  1. 复合索引：等值在前、范围在后，遵循最左匹配；
  2. 字段：高区分度、少更新字段建索引；低基数、频繁修改不建；
  3. 查询：禁止索引列运算、隐式转换、前置 % 模糊匹配；
  4. 工程：大表低峰建索引，小表不建冗余索引，多用覆盖索引；
  5. 主键：自增数字最优，避免超长字符串做主键。
🔙 返回目录

6. B+树在MySQL索引中的应用

计算三层B+树可以放多少条记录:
这里的计算并不需要我们具体的计算出能存放多少条数据,主要考我们的估算能力;
索引页一条数据大小:
我们知道页默认大小为:16KB = 16384 Byte
例如: 当主键类型为bigint类型时,主键大小为8Byte,下一页地址为6Byte
因此非叶子索引项单条占用空间：占用：8 + 6 = 14 Byte。
那么剩余2KB用来干嘛了呢?

  1. 页基础元数据（文件头、页头、校验尾、虚拟记录）：合计仅 128Byte，几乎可忽略；
  2. 页目录 Page Directory：存储页内每条记录的偏移地址，最多占用约 2KB（2048Byte）
    注意：这 2KB不是用来存储字段长度，字段长度信息分散在每一行记录头部，和页目录无关。
    那么我们有效存储空间:16384 - 2048 ≈ 14336 Byte
    假设⼀条用户数据大小为1KB，在忽略数据⻚中数据页自身属性空间占用的情况下

单页存储容量估算

  • 非叶子索引页：若简化忽略页目录开销，16384 / 14 ≈ 1170条索引分叉；
  • 叶子数据页：假设单条完整业务数据1KB，一页16KB可存放约16条完整行记录。
  1. 第 1 层（根节点）：1 个页，约 1170个分叉；
  2. 第 2 层（中间非叶子层）：1170个页，每个页再分出 1170个分叉；
  3. 第 3 层（叶子数据层）：总数据页数量 = 1170 * 1170

总可存储行数 : 1170 * 1170 * 16 = 21,902,400条记录约2100W条数据;
哪怕单条行数据更大、索引项更少，三层 B + 树也足以支撑千万级数据表；这也是 MySQL 默认只需要 3 层 B + 树索引，就能覆盖绝大多数业务场景的根本原因。

🔙 返回目录
版权声明: 本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若内容造成侵权/违法违规/事实不符，请联系邮箱：809451989@qq.com进行投诉反馈，一经查实，立即删除！
网站建设 2026/7/14 19:28:05

Seedance 2.0 时间分段提示掌握

掌握 Seedance 2.0 的 15 秒限制。将提示信息拆分成单核片段，以减少抖动、提高清晰度，并应用 5 步 TIMES 框架。 前言 在人工智能视频生成（AI Video: All-in-one AI Video Generator）行业，通用且模糊的提示往往会导致…

作者头像 李华
网站建设 2026/7/14 19:27:59

Navicat Premium 17图文安装教程｜数据库管理神器详细安装激活步骤

摘要：Navicat Premium 17 是一款老牌数据库管理工具，支持 MySQL、Oracle、SQL Server 等十余种主流数据库，同时兼容各大云平台。无论你是刚入门数据库的新手，还是每天和 SQL 打交道的开发工程师，这款工具都能帮你大幅提…

作者头像 李华
网站建设 2026/7/14 19:21:09

果蔬视觉识别数据集全量使用指南｜计算机视觉图像分类工程落地｜PyTorch迁移学习训练全流程

果蔬视觉识别数据集全量使用指南｜计算机视觉图像分类工程落地｜PyTorch迁移学习训练全流程 生鲜零售分拣、智慧农业采摘、商超智能称重、生鲜APP视觉识别四大场景中，95%研发团队会遇到公开果蔬图像样本量不足、品类覆盖单一、图像尺寸不统一、…

作者头像 李华
网站建设 2026/7/14 19:19:22

C++继承机制全解析：从语法到实战避坑指南

1. 项目概述：为什么继承是C面向对象的基石如果你刚开始接触C的面向对象编程，可能会觉得“继承”这个概念有点抽象，甚至有点绕。但我想告诉你，一旦你真正理解了它，你的代码世界会立刻打开一扇新的大门。继承&#xff0c…

作者头像 李华
网站建设 2026/7/14 19:17:11

Debian 12移动硬盘安装避坑与实战优化指南

1. 移动硬盘安装Debian 12前的准备工作在Windows环境下通过VMware虚拟机将Debian 12安装到移动硬盘，首先需要做好以下准备工作。我强烈建议准备一个容量不小于64GB的USB 3.0移动硬盘，实测下来USB 2.0的传输速度会严重影响系统体验。关键工具清单&#xf…

作者头像 李华
网站建设 2026/7/14 19:16:23

滑动窗口算法详解：从暴力搜索到高效优化的完整指南

那天下午，一个刚学算法的朋友发来消息：“这道题我暴力解法超时了，但看题解都说用‘滑动窗口’，这窗口到底怎么滑？”他说的正是 LeetCode 上那道经典的“无重复字符的最长子串”。表面看是找最长子串，但真正…

作者头像 李华