【面试题】MySQL B&#x2B；树索引高度计算

转载于 2025-12-26 12:00:09 发布 · 10 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://www.cnblogs.com/sun-10387834/p/19381299

文章标签：

#it

MySQL B+树索引高度计算与性能阈值探讨

一、MySQL B+树索引高度计算

MySQL中InnoDB的主键索引采用B+树结构，索引高度（树的层数）决定了查询时磁盘IO的次数（高度=IO次数），核心计算逻辑围绕B+树的节点容量和数据行数展开。

1. 核心前提（InnoDB默认配置）

页大小：默认16KB（16384字节），B+树的每个节点对应一个InnoDB页。
主键类型：影响索引项大小（如INT=4字节，BIGINT=8字节，VARCHAR(32)=32+2字节）。
指针大小：InnoDB中页指针固定为6字节（指向子节点页的地址）。
B+树结构：
- 非叶子节点：仅存储「主键值 + 页指针」，按主键排序，无数据行；
- 叶子节点：存储「完整主键 + 行数据（或行数据指针）」，且叶子节点通过双向链表连接。

2. 计算步骤

步骤1：计算非叶子节点的单页容量（能存多少个索引项）

非叶子节点的索引项大小 = 主键字节数 + 指针字节数

单页可存储索引项数 = 页大小 / 索引项大小（向下取整，需预留少量空间给页头/页尾，实际按90%可用计算）

示例：主键为INT（4字节），指针6字节 → 索引项=10字节

单页可用空间≈16384 * 90% = 14745字节

单页索引项数≈14745 / 10 ≈ 1474个

步骤2：计算叶子节点的单页容量（能存多少行数据）

叶子节点行大小 = 主键字节数 + 其他列总字节数（或行指针大小，InnoDB聚簇索引直接存数据）

单页可存储行数 = 页大小 / 行大小（向下取整，同样预留页结构空间）

示例：主键INT（4字节），行数据总大小≈100字节 → 单行大小≈104字节

单页行数≈14745 / 104 ≈ 141行

步骤3：计算B+树高度对应的总数据量

B+树是多叉树，高度h的总数据量公式：

总行数 = 非叶子节点分支数^(h-1) * 叶子节点单页行数

高度1：仅根节点（叶子节点）→ 行数≈141行
高度2：根节点（非叶子）+ 叶子节点 → 1474 * 141 ≈ 20.8万行
高度3：根→中间节点→叶子 → 1474 * 1474 * 141 ≈ 3060万行
高度4：1474³ * 141 ≈ 45亿行

3. 实际验证方式

可通过InnoDB的系统表查询索引高度：

/* by 01022.hk - online tools website : 01022.hk/zh/formathtml.html */
-- 查询表的主键索引高度（TABLE_ID需先查）
SELECT 
  b.name AS index_name,
  a.HEIGHT AS index_height
FROM 
  information_schema.INNODB_SYS_INDEXES a
JOIN 
  information_schema.INNODB_SYS_TABLES b ON a.TABLE_ID = b.TABLE_ID
WHERE 
  b.NAME = '数据库名/表名' -- 如test/user
  AND a.NAME = 'PRIMARY'; -- 主键索引

生产环境中，99%的表索引高度为3（少量小表为2），高度4极少（超亿级数据才会出现）。

二、MySQL单表不影响性能的最大记录数

结论先行：没有绝对数值，但业界通用经验是「千万级（1000万~1亿行）」，核心影响因素不是行数，而是索引高度、数据页缓存命中率、磁盘IO能力。

1. 性能阈值的核心逻辑

索引高度≤3时：查询只需2~3次磁盘IO（根节点、中间节点常驻内存），性能基本无衰减；
索引高度=4时：需4次IO，且中间节点可能无法全部缓存，性能开始明显下降；
数据页缓存命中率：InnoDB缓冲池能缓存的热数据页越多，性能越好（千万级数据的热页基本可全缓存，亿级后缓存命中率骤降）。

2. 不同场景的阈值参考


场景	不影响性能的最大行数	核心限制因素
主键查询+热数据	1亿行	缓冲池大小（≥32GB）
普通索引查询+分页	1000万行	索引回表IO、分页排序开销
频繁更新+多索引	500万行	索引维护开销、锁竞争
机械硬盘（HDD）	500万行	随机IO速度慢（≈100 IOPS）
固态硬盘（SSD）	1亿行	随机IO速度快（≈10万 IOPS）