[MySQL面试题]-索引 4-12 B+树索引实现原理（数据结构）

bobacgo
155 阅读9分钟

4. B+树索引实现原理（数据结构）

B+树是一种 多路平衡查找树（Balanced Tree），是 B 树的变体。其结构主要有以下特点：

  • 所有数据存储在叶子节点
  • 非🍃叶子节点只存储键（用于导航🧭）
  • 所有🍃叶子节点安顺序通过链指针连接（用于范围查询）
  • 树高一般较低（通常不超过3-4层）

B+树节点结构

非叶子节点（内部节点）

+--------+--------+--------+
|  key1  |  key2  |  key3  |   ← 只存键，不存值
+---|----+---|----+---|----+
    ↓        ↓        ↓     ← 指向子节点的指针

叶子节点

+--------+--------+--------+--------+
| key1,v | key2,v | key3,v | next → |   ← 存键值对 + 指向下一个叶子节点
+--------+--------+--------+--------+

B+ 树的查找流程：

例如查找 key=42:

  • 从根节点开始，比较 key 值大小，找到适合的子节点
  • 沿树下降到叶子节点。
  • 在叶子节点中精确匹配key，找到value

范围查询的优势： 由于叶子节点通过链表连接，B+树可快速进行范围扫描

select * from users where age between 20 and 30;

B+树的插入与删除机制

  • 插入：若叶子节点满了，进行节点分裂，部分键上升到父节点
  • 删除：删除后节点太空，会进行合并或借位
  • 高度始终保持平衡，避免退化成链表

与B树的区别

特征B树B+树
数据存储位置所有节点都存储数据仅叶子节点存储数据
范围查询需中序遍历整棵树叶子节点顺序链表、快速
空间利用率较低较高

假设有一个表 index_demo

create table index_demo(
	c1 INT,
	c2 INT,
	c3 CHAR(1),
	primary key(c1)
);

image.png

  • record_type: 表示记录的类型

    • 0 - 普通记录
    • 1 - B+树非叶子节点记录
    • 2-最小记录
    • 3-最大记录

  • next_record: 下一条记录的相对位置

  • 其他信息：隐藏列的值已经记录的额外信息。

page 就是一个磁盘块，代表一次I/O

image.png

InnooDB 默认的页大小是 16kb

image.png

image.png

image.png

image.png

5. 聚簇索引 Vs 非聚簇索引（B+实现区别）

image.png

create table users(
	id int primary key,
	name varchar(100),
	email varchar(100),
	age int,
	index idx_email (email)
)

id 是主键 → 聚簇索引

→ B+Tree 的叶子节点 = id, name, email, age 全部字段

idx_email 是非聚簇索引

→ B+Tree 的叶子节点 = email，id（仅email字段和主键id）

-- 首先通过 idx_email 找到 email 对应的主键 id
-- 然后 “回表” 通过主键聚簇索引查找完整的一行数据
select * from users where email = "abc@example.com";

聚簇索引

特点：

  • 索引和数据保存在同一个B+树中
  • 页内的记录 是按照 主键 的大小顺序排出一个 单向链表.
  • 页和页之间 也是根据页中记录的 主键 的大小顺序排成一个 双向链表
  • 非叶子节点存储的是记录的 主键 + 页号
  • 叶子节点存储的是 完整的用户记录

优点：

  • 数据访问更快，因为索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。
  • 聚簇索引对于主键的排序查找和范围查找速度非常快。
  • 按照聚簇索引排序顺序，查询显示一定范围数据的时候，由于数据都是紧密相连的，数据库可以从更少的数据块中提取数据，节省了大量的I/O操作。

缺点：

  • 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能，
  • 更新主键的代价很高，因为将会导致被更新的行移动。

InnoDB中表的 主键应选择有序的id，不建议使用无序的Id，（如 UUID, MD5, HASH, 字符串作为主键）

一张表可以有多个非聚簇索引：

image.png

6. B+树中聚簇索引的查找逻辑

  • 叶子节点保存整行记录，即：（主键值， 所有字段…）
  • 聚簇索引即数据 → 查主键不需要回表

查找逻辑（以主键查询为例）

假设有一张表 users， 主键为Id

          [非叶子节点]
           [10, 20]
          /    |    \
[叶子节点]    [叶子节点]    [叶子节点]
[1,5,9] → [11,15,19] → [21,25,30]

7. B+树中非聚簇索引的查找（匹配）

非聚簇索引的B+树的叶子节点中不存储整行数据

索引列值 + 主键值（rowid）

数据本体仍在聚簇索引中，需要通过主键回表查找

8. 平衡二叉树、红黑树、B树、B+树的区别与应用场景

image.png

常见应用场景

AVL 树 - CPU 内部高速查找，适用于插入/删除不频繁、查找频繁的场景。例如：编译器语法树、内存索引表等。

红黑树 - map/set 等，性能均衡，插入/删除高效。

B 树 - 适合数据库，文件系统中的索引结构（适合磁盘存取）

B+ 树 数据库索引的主流结构（InnoDB）、文件系统支持高效范围查询、顺序遍历。

平衡二叉树（AVL）

动画 www.cs.usfca.edu/~galles/vis…

  • 基础数据结构
  • 左右平衡
  • 高度差大于 1 会自旋
  • 每个节点记录一个数据

image.png

一个节点一次IO

磁盘的IO次数和索引树的高度是相关的，平衡二叉树由于树深度过大而造成磁盘IO读写过于频繁，近而导致效率低下。

image.png 红黑树

  • hashmap存储
  • 两次旋转达到平衡
  • 分为红黑节点

image.png

红黑树的长子树只要不超过短子树的两倍即可。

9. 一个 B+ 树中大概能存放多少条索引记录

阶数（m） ：每个节点最多有 m 个子节点（即 m 路 B+树）

树高度（h） ：从根节点到叶子节点的层数。

节点大小：通常与磁盘块（如 16KB）对齐。

树高度（h）最大记录数（k × m^(h-1)）示例（m=1171, k=16）
11616 条（仅根节点）
216 × 1171 ≈ 18,736约 1.8 万条
316 × 1171² ≈ 21.9M约 2194 万条
416 × 1171³ ≈ 25.6B约 256 亿条

10. 使用 B+ 树存储的索引在 CRUD 中的执行效率

  1. 查询（Query/Read）效率：非常高

✅ 精确查询

  • 时间复杂度为 O(logₘ N)，树高很低（一般2~3层） ，通常只需 1~2次磁盘IO。

  • 查询路径：

    • 对于主键查找：直接查聚簇索引（叶子节点存整行数据）
    • 对于二级索引查找：先在二级索引树中查，再通过主键 回表 查询数据

✅ 范围查询

  • B+ 树 所有数据存在叶子节点，并通过 双向链表连接，非常适合做范围查询、排序等操作。
  • 只需定位范围起点，然后顺着链表扫描，无需频繁跳跃。
  1. 插入（Insert）效率：（中等偏高）

  • 插入操作过程：

    1. 使用 B+ 树查找插入位置 （O(log N)）
    2. 插入页未满时，直接写入页中（代价低）
    3. 页满时，触发 页分裂（Page Split），同时影响父节点（代价高）

  • 写入优化：

    • InnoDB 使用 缓存冲池+重做日志（Redo Log）+ 页式缓存管理，提高写入性能。
    • 可结合 自增主键（索引尾插），避免频繁页分裂。

    若插入顺序不连续，可能频繁引发页分裂，影响性能

  1. 删除（Delete）效率：（中等）

    1. 删除操作也需要选查找到对应记录（O(log N)）
    2. 删除记录后若页太空，可能触发 页合并（Page Merge）
    3. 页合并会修改兄弟节点及父节点的指针，也可能牵动多个页。

    InnoDB 删除实际是标记为“删除”，真正清理由后台线程完成（称为 purge）

  2. 更新（Update）效率：视情况而定

  • 若更新字段 不涉及索引列：直接定位到页内修改，效率高

  • 若更新字段为索引列（如主键或唯一键）：

    • 本质上相当于先删除旧索引，再插入新索引，可能触发页合并/分裂。

避免频繁更新主键或唯一索引列

11. 什么是自适应哈希索引（AHI）

MySQL InnoDB 存储引擎中的一种优化机制

自适应哈希索引是 InnoDB 基于 B+ 树页中访问模式自动 构建的哈希表索引结构，用于加速热点数据页的精确查询，无须开发者手动维护。

-- SHOW ENGINE INNODB STATUS \G ;
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

为什么需要 AHI？

  • 普通 B+ 树索引查找是 O(log N) 的多层树结构，需要多次页访问。
  • 对于 频繁访问某些特定值的精确查找，B+ 树可能效率不够高。
  • 哈希表查找可实现 O(1) 时间复杂度，在命中时大大减少CPU和页访问成本。

工作原理

  1. 热数据页检测：InnoDB 监控 B+ 树索引页的访问模式。
  2. 触发条件满足时：如果某个索引页被频繁按特定键值访问（例如 where col = ‘abc’）, 就自动构建哈希条目。
  3. 构建哈希条目：InnoDB 会将该索引键值映射到该页的具体偏移，放入哈希表中。
  4. 后续命中查询：当再次访问相同键时，优先命中哈希表，跳过 B+ 树查找过程。

AHI 特性与优点

特性描述
自动构建不需要开发者干预，由 InnoDB 内部自动管理
精确匹配才可使用仅支持 等值查询（=） ，不支持范围、模糊匹配
基于访问频率判断热点页节省内存空间，只有热点数据页才会被哈希化
命中时加速明显减少 CPU 运算和磁盘访问次数（IO）

缺点：

  • 内存开销，缓存池中，哈希条目过的会占用比较多内存。
  • 加锁竞争：AHI 的哈希表全局加锁，在高并发场景下可能成为性能瓶颈

12. 什么是2-3树 2-3-4树

多叉树（multiway tre） 允许 每个节点可以有更多的数据项和更多的子节点。

2-3 树

image.png

  • 2-3 树的所有叶子节点都在同一层。
  • 有两个子节点叫二节点，二节点要么有没有子节点，要么有两个子节点。
  • 有三个子节点的节点加三节点，三节点要么没有子节点，要么有三个子节点。
  • 2-3 树 是由二节点和三节点构成的树。

image.png

2-3-4 树

image.png

avatar
文章
阅读
粉丝