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

MySQL索引底层原理详解：B+树结构、存储与查询优化实战

news 2026/7/14 3:06:47

前言

日常开发中几乎所有业务SQL都会用到索引，很多开发者只会简单写create index创建索引，遇到慢SQL只会无脑加索引，出现索引失效、回表、索引覆盖等问题时无从排查。想要真正用好MySQL索引，必须吃透底层存储结构，InnoDB默认使用B+树作为索引载体，本文从磁盘存储特性、二叉树缺陷、B树对比、B+树完整结构、聚簇/二级索引、索引优化实战逐层拆解，全程结合真实业务场景，适合后端开发、DBA日常查阅学习。

一、为什么数据库不用二叉树、红黑树，选择多路平衡树？

1. 磁盘IO是数据库性能瓶颈

内存读写速度纳秒级，磁盘读写毫秒级，二者速度相差上万倍。数据库数据持久化在磁盘，查询时需要把磁盘页加载到内存，一次磁盘读取就是一次IO操作，索引设计核心目标：减少单次查询触发的磁盘IO次数。

磁盘数据按页存储，InnoDB默认一页大小16KB，一页是最小磁盘交互单元，一次IO读取完整一页数据。树结构高度决定IO次数，树越高，查询需要加载的磁盘页越多，性能越差。

2. 二叉树致命缺陷

二叉搜索树极端场景会退化成链表，例如主键自增1、2、3、4依次插入，树深度等于数据总量，查询最后一条数据需要N次IO，完全失去索引加速作用。

平衡二叉树（AVL树）通过旋转维持平衡，但每个节点仅两个子节点，数据量大时树高度依旧很高；插入、更新节点要频繁旋转平衡，写入性能损耗极大，不适合高并发写入场景。

3. 红黑树局限

红黑树降低平衡旋转频率，但本质仍是二叉树，节点分支最多2个，百万级数据下树高会达到20层以上，单次查询最少20次磁盘IO，查询效率达不到数据库要求。

4. 多路平衡树（B树）优势

B树单个节点可存储大量关键字与子节点指针，分支数量远大于二叉树，同等数据量下树高度大幅降低，IO次数显著减少，完美适配磁盘分页存储逻辑。

二、B树基础结构与读写逻辑

1. B树核心规则（m阶B树，m代表节点最大子节点数量）

根节点最少1个关键字，非根非叶子节点关键字数量：⌈m/2⌉ -1 ~ m-1
每个关键字对应一条数据记录，节点内关键字从小到大有序排列
n个关键字分割出n+1个子节点指针，左子树数值小于当前关键字，右子树大于当前关键字
所有叶子节点在同一层级，树天然平衡

2. B树查询流程

查找目标值时，加载根节点磁盘页到内存，遍历节点关键字匹配区间，找到对应子节点指针，加载下一页磁盘数据，循环直到匹配关键字，直接取出对应完整行数据。

3. B树的短板

范围查询效率差：查询id>10 and id<100时，匹配到10后需要逐层遍历各个分支叶子节点，频繁切换磁盘页，多次IO；
节点同时存储关键字+完整数据，单页能存放的索引键数量变少，树高度提升，IO次数增加。

三、InnoDB B+树完整结构（核心重点）

B+树是B树改良版本，也是InnoDB唯一索引存储结构，分为非叶子节点（索引层）和叶子节点（数据层）两层，二者职责完全分离。

1. B+树核心设计规则

非叶子节点只存索引键+子页指针，不存储真实行数据
单页16KB空间全部用来存放索引值，一页能存储上万条索引键，树高度极低，百万、千万级数据表树高通常仅3层：根页→中间索引页→叶子数据页，查询最多3次磁盘IO。
全部真实数据仅存放在叶子节点
每条叶子节点记录包含索引键、完整行数据（聚簇索引）或主键ID（二级索引）。
叶子节点用双向链表串联
所有叶子节点按索引值升序排列，相邻叶子节点保存前后页指针，范围查询无需回溯上层节点，找到区间起点后顺着链表向后遍历即可，极大提升between、>、<、like前缀匹配等范围查询性能。
关键字冗余：上层非叶子节点的索引键，都会在下层叶子节点重复出现，最终完整有序数据全部落在叶子链表。

2. 三层B+树完整查询流程（千万级数据表标准结构）

第一次IO：读取根节点磁盘页，对比目标索引值，定位对应中间层子页指针；
第二次IO：读取中间非叶子节点页，再次匹配索引区间，定位目标叶子页；
第三次IO：读取叶子节点页，遍历链表找到精确匹配数据，返回结果。

无论查询哪条数据，最多3次磁盘IO，性能稳定可控。

四、InnoDB两种B+树索引：聚簇索引 vs 二级索引

（一）聚簇索引（主键索引）

定义：数据表数据行和主键B+树叶子节点绑定，整张表本身就是一棵主键B+树，数据物理存储顺序跟随主键排序。
叶子节点存储内容：主键值 + 完整一行所有字段数据。
特性：
- 一张表仅有一棵聚簇索引树，必须有主键，无主键时InnoDB自动生成隐藏6字节rowid作为聚簇索引；
- 主键推荐自增整型：自增主键插入数据时，新增记录直接追加到叶子链表末尾，不会触发节点分裂；随机字符串主键会导致插入时频繁拆分叶子页，大量磁盘碎片，写入性能暴跌。
主键查询优势：通过主键检索，命中叶子节点直接拿到完整数据，无需额外回表操作，即索引覆盖。

（二）二级索引（普通索引、唯一索引、联合索引）

定义：独立于聚簇索引之外的B+树，树排序依据创建索引的字段。
叶子节点存储内容：索引字段值 + 对应数据行的主键ID，不存储完整行数据。
回表查询原理：
使用二级索引查询时，先扫描二级索引B+树，叶子节点拿到主键值，再拿着主键去聚簇索引B+树重新检索完整数据，两次B+树遍历，多出一次磁盘IO，这个过程称为回表。
避免回表：索引覆盖（覆盖索引）
查询字段全部包含在二级索引中，不需要通过主键查找聚簇索引，直接从二级索引叶子节点取出所有需要字段，消除回表开销，是日常SQL优化最常用手段。

示例：

-- 建立联合索引 idx_name_age(name,age)createindexidx_name_ageonuser(name,age);-- 覆盖索引，无需回表selectname,agefromuserwherename='张三';-- 需要回表，sex不在索引内selectname,age,sexfromuserwherename='张三';

（三）联合索引底层存储逻辑

联合索引本质是复合排序的B+树，索引节点按创建索引字段顺序依次排序，遵循最左匹配原则。
以idx_a_b_c(a,b,c)为例：

先按字段a排序；
a值相同时，按字段b排序；
a、b均相同，按字段c排序。
叶子节点存储(a,b,c)+主键ID。
失效场景：查询条件跳过最左前列，直接使用后段字段，无法走索引，例如where b=1无法使用idx_a_b_c。

五、B+树节点分裂、页合并（写入更新底层逻辑）

InnoDB一页固定16KB，插入、更新数据时页面填满会触发页分裂，删除数据页面空间过低会触发页合并，直接影响写入性能。

页分裂
叶子页面数据存满16KB后，新增数据会拆分出新叶子页，原页面保留一半数据，新页面存放另一半，同时更新上层非叶子节点索引指针。随机主键会频繁触发页分裂，产生大量磁盘碎片，降低读写效率。
页合并
删除数据后，相邻两个叶子页面剩余空间均低于阈值，数据库会合并两个页面，释放空闲磁盘页，减少IO读取数量。批量删除大量数据后，可通过optimize table整理碎片，优化索引树结构。

六、B+树索引常见性能问题与优化方案

1. 范围查询导致索引失效/全索引扫描

条件中出现>、<、between、like %xxx，联合索引中范围字段右侧索引失效。
例：索引idx_a_b，where a>10 and b=20，b字段无法走索引过滤。
优化：将范围条件放在联合索引末尾。

2. 隐式转换破坏索引

字符串字段不加引号、数字与字符串对比，MySQL自动转换字段类型，索引键匹配逻辑失效，无法走B+树检索。

-- phone为varchar字符串索引，隐式转换失效select*fromuserwherephone=13800138000;-- 正确写法，加引号select*fromuserwherephone='13800138000';

3. 函数操作索引字段

对索引列使用substr、date_format、ifnull等函数，B+树有序结构失效，无法利用索引。

-- 索引字段执行函数，不走索引select*fromuserwheredate(create_time)='2026-07-01';-- 优化写法，函数作用于常量select*fromuserwherecreate_time>='2026-07-01'andcreate_time<'2026-07-02';