MySQL InnoDB 存储引擎:B+树叶子节点能存多少数据?
前言
很多人知道 MySQL 使用 B+ 树作为索引结构,但对于一个叶子节点到底能存多少行数据这个问题,往往只知道一个模糊的概念。本文将详细解析这个问题,并通过实际计算帮助你理解。
一、叶子节点存什么?
1.1 叶子节点 vs 非叶子节点
在 InnoDB 的 B+ 树中,节点分为两类:
非叶子节点(包括根节点):
解析 MySQL InnoDB 存储引擎 B+ 树叶子节点的数据存储容量。通过页大小(默认 16KB)除以每行数据大小计算单页行数,并分析不同表结构对 B+ 树高度及总容量的影响。提供了实际计算示例、查看行大小的方法以及优化建议,如减小行大小、拆分大字段和选择合适数据类型,旨在帮助开发者理解索引性能原理并进行数据库设计优化。
很多人知道 MySQL 使用 B+ 树作为索引结构,但对于一个叶子节点到底能存多少行数据这个问题,往往只知道一个模糊的概念。本文将详细解析这个问题,并通过实际计算帮助你理解。
在 InnoDB 的 B+ 树中,节点分为两类:
非叶子节点(包括根节点):
叶子节点:
叶子节点(16KB 的页) ┌──────────────────────────────────────────────┐ │ 第 1 行:id=1 | name=张三 | age=25 | email=... │ │ 第 2 行:id=2 | name=李四 | age=30 | email=... │ │ 第 3 行:id=3 | name=王五 | age=28 | email=... │ │ ... │ │ 第 N 行:能存多少行,取决于每行大小 │ └──────────────────────────────────────────────┘
关键点:
每个叶子节点能存的行数 = 页大小 ÷ 每行数据大小 = 16384 字节 ÷ 每行大小
一行数据的大小包括:
DB_TRX_ID(事务 ID,6 字节)DB_ROLL_PTR(回滚指针,7 字节)总开销约:20-30 字节
表结构:
CREATE TABLE users (
id BIGINT, -- 8 字节
name VARCHAR(50),-- 假设平均 20 字节
age INT, -- 4 字节
email VARCHAR(100),-- 假设平均 50 字节
created_at DATETIME,-- 8 字节
status TINYINT, -- 1 字节
PRIMARY KEY(id)
);
计算过程:
字段数据大小:8 + 20 + 4 + 50 + 8 + 1 = 91 字节
行格式开销:约 30 字节
每行总大小:91 + 30 = 121 字节
为了方便计算,估算为:200 字节/行
每个叶子节点能存:16384 ÷ 200 = 81.92 ≈ 80 行
图示:
叶子节点(16KB) ┌────────────────────────────────────────┐ │ 第 1 行(200 字节) │ ├────────────────────────────────────────┤ │ 第 2 行(200 字节) │ ├────────────────────────────────────────┤ │ ... │ ├────────────────────────────────────────┤ │ 第 80 行(200 字节) │ └────────────────────────────────────────┘
表结构:
CREATE TABLE articles (
id BIGINT, -- 8 字节
title VARCHAR(200),-- 假设平均 100 字节
content TEXT, -- 假设平均 800 字节
author VARCHAR(50),-- 假设平均 20 字节
created_at DATETIME,-- 8 字节
PRIMARY KEY(id)
);
计算过程:
字段数据大小:8 + 100 + 800 + 20 + 8 = 936 字节
行格式开销:约 88 字节
每行总大小:1024 字节 = 1KB
每个叶子节点能存:16384 ÷ 1024 = 16 行
结论:包含大字段(如 TEXT)的表,每页只能存储很少的行数。
表结构:
CREATE TABLE simple (
id INT, -- 4 字节
value INT, -- 4 字节
flag TINYINT,-- 1 字节
PRIMARY KEY(id)
);
计算过程:
字段数据大小:4 + 4 + 1 = 9 字节
行格式开销:约 30 字节
每行总大小:约 40 字节
估算为:100 字节/行
每个叶子节点能存:16384 ÷ 100 = 163 行
| 表类型 | 每行大小 | 每页行数 | 典型场景 |
|---|---|---|---|
| 极简表 | 50 字节 | 327 行 | 配置表、字典表 |
| 小表 | 100 字节 | 163 行 | 简单业务表 |
| 普通表 | 200 字节 | 80 行 | 用户表、订单表 |
| 中等表 | 500 字节 | 32 行 | 包含多字段的业务表 |
| 大表 | 1KB | 16 行 | 文章表、带 TEXT 字段 |
| 超大表 | 5KB | 3 行 | 包含大字段的表 |
最常见:每行 200 字节,每页 80 行
假设:
2 层 B+ 树:
第 1 层:1 个根节点(1170 个指针)
第 2 层:1170 个叶子节点
总数据量 = 1170 × 80 = 93,600 行
3 层 B+ 树:
第 1 层:1 个根节点(1170 个指针)
第 2 层:1170 个中间节点(每个 1170 个指针)
第 3 层:1170 × 1170 = 1,368,900 个叶子节点
总数据量 = 1,368,900 × 80 = 109,512,000 行 ≈ 1 亿行
| 每行大小 | 每页行数 | 2 层容量 | 3 层容量 |
|---|---|---|---|
| 100 字节 | 163 行 | 19 万 | 2.2 亿 |
| 200 字节 | 80 行 | 9 万 | 1 亿 |
| 500 字节 | 32 行 | 3.7 万 | 4300 万 |
| 1KB | 16 行 | 1.8 万 | 2100 万 |
结论:行越大,B+ 树能存储的总行数越少,树的高度增加越快。
SELECT table_name,
table_rows, -- 总行数
data_length, -- 数据大小(字节)
ROUND(data_length / table_rows, 2) AS avg_row_length -- 平均每行
FROM information_schema.tables
WHERE table_schema = 'your_database'
AND table_name = 'users';
示例输出:
table_name: users
table_rows: 1000000
data_length: 200000000
avg_row_length: 200.00
SHOW TABLE STATUS LIKE 'users'\G
示例输出:
*************************** 1. row ***************************
Name: users
Engine: InnoDB
Version: 1
Row_format: Dynamic
Rows: 1000000
Avg_row_length: 200
Data_length: 200000000
不推荐:
CREATE TABLE articles (
id BIGINT,
title VARCHAR(200),
content TEXT, -- ❌ 大字段放在主表
PRIMARY KEY(id)
);
推荐:拆分表
-- 主表:常用字段
CREATE TABLE articles (
id BIGINT,
title VARCHAR(200),
author VARCHAR(50),
created_at DATETIME,
PRIMARY KEY(id)
);
-- 扩展表:大字段
CREATE TABLE article_content (
article_id BIGINT,
content TEXT,
PRIMARY KEY(article_id)
);
好处:
不推荐:浪费空间
age BIGINT -- 8 字节,但年龄只需要 0-200
status VARCHAR(20) -- 变长,且占用大
推荐:精确类型
age TINYINT UNSIGNED -- 1 字节,范围 0-255
status TINYINT -- 1 字节,用枚举值(0=待审核,1=已发布...)
节省效果:
不推荐:
name VARCHAR(500) -- 实际很少超过 50 字符
推荐:
name VARCHAR(100) -- 足够用,减少空间浪费

微信公众号「极客日志」,在微信中扫描左侧二维码关注。展示文案:极客日志 zeeklog
使用加密算法(如AES、TripleDES、Rabbit或RC4)加密和解密文本明文。 在线工具,加密/解密文本在线工具,online
在线格式化和美化您的 SQL 查询(它支持各种 SQL 方言)。 在线工具,SQL 美化和格式化在线工具,online
解析 INSERT 等受限 SQL,导出为 CSV、JSON、XML、YAML、HTML 表格(见页内语法说明)。 在线工具,SQL转CSV/JSON/XML在线工具,online
CSV 与 JSON/XML/HTML/TSV/SQL 等互转,单页多 Tab。 在线工具,CSV 工具包在线工具,online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具,Base64 字符串编码/解码在线工具,online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具,Base64 文件转换器在线工具,online