数据库核心术语知识库

定义

页分裂 (Page Split) 是 B+Tree 索引在插入数据时,当某个数据页已满且需要插入新的键值时,数据库引擎会分配一个新的数据页,并将原页中约一半的数据移动到新页中,以维持 B+Tree 的平衡和有序性。

页分裂是一种代价较高的操作,会导致额外的 I/O、产生碎片、降低写入性能,是数据库性能优化中需要重点关注的现象。

详细笔记

核心原理

B+Tree 的基本结构

InnoDB 使用 B+Tree 作为索引结构:

页 (Page):最小的 I/O 单位,默认 16KB
填充因子 (Fill Factor):页的空间利用率,通常不是 100%
有序存储:数据按索引键值顺序存储

根页(Root Page)
  ↓
内部页(Internal Page)
  ↓
叶子页(Leaf Page):存储实际数据

页分裂的触发条件

sql

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  INDEX idx_name (name)
) ENGINE=InnoDB;

-- 假设每个页可以存储 100 条记录

-- 初始状态:一个页,已有 100 条记录
Page 1: [记录1, 记录2, ..., 记录100]  (100% 满)

-- 插入新记录:需要插入到中间位置
INSERT INTO users VALUES (50, '新记录');

-- 触发页分裂!

页分裂的详细过程

步骤分解

原始状态:
Page 1: [1, 2, 3, ..., 98, 99, 100]  (已满)

插入操作:
INSERT INTO users VALUES (50, '新记录');

步骤一:检测空间不足
  → Page 1 已满,无法直接插入

步骤二:分配新页
  → 从表空间分配 Page 2

步骤三:选择分裂点(通常选择中间位置)
  → 分裂点:记录 50

步骤四:移动数据
  → Page 1 保留: [1, 2, ..., 49]
  → Page 2 移动: [50, 51, ..., 100]

步骤五:插入新记录
  → Page 2: [50(新), 51, ..., 100]

步骤六:更新父节点
  → 根页或内部页添加指向 Page 2 的指针

最终状态:
Page 1: [1, 2, ..., 49]    (49% 满)
Page 2: [50(新), 51, ..., 100] (51% 满)

图解页分裂

分裂前:
┌─────────────────────────────┐
│  Page 1 (100% 满)    │
│  [1, 2, 3, ..., 98, 99, 100] │
└─────────────────────────────┘

插入 50.5 (需要在 50 和 51 之间插入):

分裂后:
┌──────────────────┐  ┌──────────────────────┐
│  Page 1 (49%)   │  │  Page 2 (51%)   │
│  [1, 2, ..., 49]  │───→│  [50, 50.5, 51, ..., 100] │
└──────────────────┘  └──────────────────────┘
        ↑
        新记录插入这里

页分裂的性能影响

负面影响

1. 额外的 I/O 操作

正常插入:
  - 读取页到内存:1 次 I/O
  - 修改页
  - 写回磁盘:1 次 I/O(异步)
  总计:1-2 次 I/O

页分裂:
  - 读取原页:1 次 I/O
  - 分配新页:1 次 I/O
  - 移动数据(写新页):1 次 I/O
  - 更新原页:1 次 I/O
  - 更新父节点:1-2 次 I/O
  总计:5-6 次 I/O

性能差异:页分裂比普通插入慢 3-5 倍

2. 产生碎片

页分裂后:
  - Page 1: 49% 满
  - Page 2: 51% 满
  
空间利用率:平均 50%
浪费空间:约 50%

长期运行后:
  - 表中存在大量半满的页
  - 需要更多 I/O 才能扫描相同数量的数据
  - Buffer Pool 效率降低

3. 锁竞争加剧

sql

-- 页分裂过程中需要锁定:
-- 1. 原页
-- 2. 新页
-- 3. 父节点(可能还有兄弟节点)

-- 并发插入时:
-- 线程 A:正在分裂 Page 1
-- 线程 B:需要插入到 Page 1
-- 结果:线程 B 等待,并发度下降

4. 索引深度增加

频繁页分裂可能导致:
  - B+Tree 层级增加
  - 查询时需要访问更多层
  - 查询性能下降

示例:
  - 分裂前:3 层 B+Tree,查询需 3 次 I/O
  - 分裂后:4 层 B+Tree,查询需 4 次 I/O
  - 性能下降:33%

页分裂的场景分析

场景一:顺序插入(主键自增)

sql

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  amount DECIMAL(10, 2)
);

-- 插入顺序:1, 2, 3, 4, 5, ...

-- 页分裂情况:
-- ✅ 很少发生页分裂!

-- 原因:
-- 1. 新记录总是插入到最后一个页
-- 2. 只有最后一个页满时才分裂
-- 3. 分裂后,新记录继续插入到新页
-- 4. 不会产生随机插入导致的频繁分裂

-- 性能特点:
-- - 写入性能高
-- - 碎片少
-- - 空间利用率高(约 90%+)

场景二:随机插入(UUID 主键)

sql

CREATE TABLE users (
  user_id VARCHAR(36) PRIMARY KEY,  -- UUID
  name VARCHAR(50)
);

-- 插入顺序:uuid_5, uuid_2, uuid_9, uuid_1, uuid_7, ...

-- 页分裂情况:
-- ❌ 频繁发生页分裂!

-- 原因:
-- 1. UUID 无序,新记录可能插入到任意位置
-- 2. 任何页都可能满,都可能分裂
-- 3. 分裂后,下次插入可能又在另一个位置
-- 4. 导致大量随机 I/O 和页分裂

-- 性能特点:
-- - 写入性能低(比自增主键慢 5-10 倍)
-- - 碎片严重(空间利用率可能低于 50%)
-- - 缓存命中率低

对比测试:

sql

-- 测试一:自增主键
CREATE TABLE test_sequential (
  id INT AUTO_INCREMENT PRIMARY KEY,
  data VARCHAR(100)
);

INSERT INTO test_sequential (data) 
SELECT CONCAT('data_', n) 
FROM generate_series(1, 1000000) AS n;

-- 执行时间:约 30 秒
-- 表大小:约 100MB
-- 碎片率:约 10%

-- 测试二:UUID 主键
CREATE TABLE test_uuid (
  id VARCHAR(36) PRIMARY KEY,
  data VARCHAR(100)
);

INSERT INTO test_uuid (id, data)
SELECT UUID(), CONCAT('data_', n)
FROM generate_series(1, 1000000) AS n;

-- 执行时间:约 180 秒(慢 6 倍!)
-- 表大小:约 200MB(大一倍!)
-- 碎片率:约 50%

场景三:更新导致的增长

sql

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  description VARCHAR(500)
);

-- 初始插入:description 较短
INSERT INTO products VALUES (1, '短描述');

-- 后续更新:description 变长
UPDATE products 
SET description = REPEAT('很长的描述...', 50)  -- 从 10 字节增长到 500 字节
WHERE product_id = 1;

-- 如果页内空间不足,可能触发:
-- 1. 行溢出(Row Overflow)
-- 2. 页分裂(如果多个行都增长)

监控页分裂

MySQL Performance Schema

sql

-- 查看 InnoDB 页分裂统计
SELECT 
  NAME,
  COUNT
FROM performance_schema.innodb_metrics
WHERE NAME LIKE '%index_page_split%';

-- 输出:
-- +----------------------------------+--------+
-- | NAME           | COUNT  |
-- +----------------------------------+--------+
-- | index_page_splits      | 12345  |
-- | index_page_split_attempts    | 15678  |
-- +----------------------------------+--------+

-- 计算分裂率:
-- 分裂率 = index_page_splits / 总插入次数

InnoDB Status

sql

SHOW ENGINE INNODB STATUS\G

-- 输出包含:
-- ----------
-- INSERT BUFFER AND ADAPTIVE HASH INDEX
-- ----------
-- Ibuf: size 1, free list len 100, seg size 102, 
--   12345 merges, merged operations 67890
--   页分裂相关统计...

估算表的碎片率

sql

-- 方法一:通过表状态
SHOW TABLE STATUS LIKE 'users'\G

-- 输出:
-- Data_length:  16777216  (实际数据占用)
-- Index_length: 8388608
-- Data_free:  8388608 (空闲空间,包括碎片)

-- 碎片率估算:
-- 碎片率 = Data_free / (Data_length + Index_length)
--   = 8388608 / (16777216 + 8388608)
--   = 33%

-- 方法二:通过分析表
ANALYZE TABLE users;

SELECT 
  TABLE_NAME,
  DATA_LENGTH,
  INDEX_LENGTH,
  DATA_FREE,
  ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH) * 100, 2) AS fragmentation_pct
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY fragmentation_pct DESC;

减少页分裂的策略

策略一:使用顺序主键

sql

-- ✅ 推荐:自增主键
CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  ...
);

-- ✅ 推荐:时间戳 + 自增序列
CREATE TABLE logs (
  log_id BIGINT PRIMARY KEY,  -- 基于雪花算法生成的 ID
  ...
);

-- ❌ 避免:UUID 主键
CREATE TABLE users (
  user_id VARCHAR(36) PRIMARY KEY DEFAULT UUID(),
  ...
);

-- ✅ 替代方案:UUID 作为普通列,另加自增主键
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id VARCHAR(36) NOT NULL,
  UNIQUE KEY uk_user_id (user_id)
);

策略二:调整填充因子

sql

-- MySQL:通过 innodb_fill_factor 控制
SET GLOBAL innodb_fill_factor = 90;  -- 默认 100,设置为 90 表示留 10% 空间

-- 效果:
-- - 页只填充到 90%,预留 10% 空间用于后续插入
-- - 减少页分裂概率
-- - 但会增加存储空间

-- PostgreSQL:通过 FILLFACTOR 参数
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50)
) WITH (FILLFACTOR = 90);

策略三:定期重建表

sql

-- 方法一:OPTIMIZE TABLE(MySQL)
OPTIMIZE TABLE users;

-- 效果:
-- 1. 重建表和索引
-- 2. 消除碎片
-- 3. 更新统计信息
-- 4. 释放未使用的空间

-- 方法二:ALTER TABLE
ALTER TABLE users ENGINE=InnoDB;

-- 方法三:pg_repack(PostgreSQL)
-- 需要安装扩展
pg_repack -d your_database -t users

注意事项:

OPTIMIZE TABLE 会锁表,建议在低峰期执行
大表重建可能需要很长时间
重建后,Buffer Pool 需要重新预热

策略四:分区表

sql

-- 将大表按范围分区,减少单个索引的大小
CREATE TABLE orders (
  order_id INT,
  create_time DATETIME,
  ...
) PARTITION BY RANGE (YEAR(create_time)) (
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025)
);

-- 优点:
-- 1. 每个分区的索引更小
-- 2. 页分裂影响范围更小
-- 3. 可以单独优化某个分区

策略五:批量插入排序

sql

-- ❌ 无序批量插入
INSERT INTO users (user_id, name) VALUES
  ('uuid_5', '用户5'),
  ('uuid_2', '用户2'),
  ('uuid_9', '用户9'),
  ('uuid_1', '用户1');

-- ✅ 排序后批量插入
INSERT INTO users (user_id, name) VALUES
  ('uuid_1', '用户1'),
  ('uuid_2', '用户2'),
  ('uuid_5', '用户5'),
  ('uuid_9', '用户9');

-- 对于大数据量导入:
-- 1. 先导入到临时表
-- 2. 排序
-- 3. 再插入到目标表

CREATE TEMPORARY TABLE tmp_users AS SELECT * FROM source_users ORDER BY user_id;
INSERT INTO users SELECT * FROM tmp_users;

页分裂 vs 页合并

特性	页分裂	页合并
触发条件	页满时插入	页删除后空间过多
操作	分配新页,移动数据	合并相邻页,回收空间
性能影响	降低写入性能	提高空间利用率
频率	高频插入时常见	高频删除时常见
优化方向	减少分裂	定期重组

实际案例分析

案例一:UUID 主键导致的性能问题

sql

-- 问题系统:用户反馈插入越来越慢

-- 检查表结构
SHOW CREATE TABLE users;

-- 发现:使用 UUID 作为主键
CREATE TABLE users (
  user_id VARCHAR(36) PRIMARY KEY DEFAULT UUID(),
  ...
);

-- 检查碎片率
SHOW TABLE STATUS LIKE 'users';
-- Data_free / (Data_length + Index_length) = 55%

-- 检查页分裂统计
SELECT * FROM performance_schema.innodb_metrics
WHERE NAME LIKE '%page_split%';
-- index_page_splits: 500000+ (非常高)

-- 解决方案:重构表结构
CREATE TABLE users_new (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id VARCHAR(36) NOT NULL,
  UNIQUE KEY uk_user_id (user_id)
);

-- 迁移数据
INSERT INTO users_new (user_id, ...) SELECT user_id, ... FROM users;

-- 替换表
RENAME TABLE users TO users_old, users_new TO users;

-- 效果:
-- - 插入性能提升 5 倍
-- - 存储空间减少 40%
-- - 碎片率降至 10%

案例二:订单系统的优化

sql

-- 原始设计
CREATE TABLE orders (
  order_id VARCHAR(32) PRIMARY KEY,  -- 业务订单号(无序)
  user_id INT,
  create_time DATETIME,
  ...
);

-- 问题:
-- 1. 订单号无序,频繁页分裂
-- 2. 每天新增 100 万订单,性能持续下降

-- 优化方案
CREATE TABLE orders_v2 (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- 代理主键
  order_id VARCHAR(32) NOT NULL,    -- 业务订单号
  user_id INT,
  create_time DATETIME,
  UNIQUE KEY uk_order_id (order_id),
  INDEX idx_user_time (user_id, create_time)
) PARTITION BY RANGE (TO_DAYS(create_time)) (
  PARTITION p_current VALUES LESS THAN MAXVALUE
);

-- 效果:
-- - 插入 TPS: 5000 → 25000 (提升 5 倍)
-- - 查询性能:提升 30%
-- - 存储空间:减少 35%

最佳实践

优先使用顺序主键:AUTO_INCREMENT、雪花算法 ID 等
避免 UUID 作为主键:如必须使用,作为唯一索引而非聚集索引
监控碎片率:定期检查,超过 30% 考虑优化
合理安排维护窗口:定期执行 OPTIMIZE TABLE 或重建索引
批量插入前排序:减少随机插入导致的页分裂
预留空间:设置合适的 fillfactor,为后续更新预留空间
分区大表:减少单个索引的大小和维护成本

关联术语

[[页合并]]
[[碎片]]
[[聚集索引]]
[[行溢出]]
[[预读]]

参考资料

MySQL 官方文档: InnoDB Physical Structure
《高性能 MySQL》第 3 章:Schema 优化
InnoDB 源码:btr0btr.cc 中的页分裂实现
Use The Index, Luke: Clustered Index

定义 ​

详细笔记 ​

核心原理 ​

B+Tree 的基本结构 ​

页分裂的触发条件 ​

页分裂的详细过程 ​

步骤分解 ​

图解页分裂 ​

页分裂的性能影响 ​

负面影响 ​

页分裂的场景分析 ​

场景一:顺序插入(主键自增) ​

场景二:随机插入(UUID 主键) ​

场景三:更新导致的增长 ​

监控页分裂 ​

MySQL Performance Schema ​

InnoDB Status ​

估算表的碎片率 ​

减少页分裂的策略 ​

策略一:使用顺序主键 ​

策略二:调整填充因子 ​

策略三:定期重建表 ​

策略四:分区表 ​

策略五:批量插入排序 ​

页分裂 vs 页合并 ​

实际案例分析 ​

案例一:UUID 主键导致的性能问题 ​

案例二:订单系统的优化 ​

最佳实践 ​

关联术语 ​

参考资料 ​

定义