数据库核心术语知识库

定义

页合并 (Page Merge) 是 B+Tree 索引在删除数据时,当某个数据页的空间利用率低于阈值(通常为 50%)时,数据库引擎会将该页与相邻页进行合并,将数据集中到较少的页中,并释放空闲页以回收空间的过程。

页合并是页分裂的逆操作,两者共同维护 B+Tree 的空间效率和平衡性。

详细笔记

核心原理

页合并的触发条件

触发条件:
  页的空间利用率 < 填充因子阈值(通常 50%)
  
例如:
  - 页大小: 16KB
  - 当前数据: 6KB
  - 空间利用率: 6/16 = 37.5% < 50%
  - 触发页合并

页合并的执行流程

初始状态:
Page 1: [1, 2, ..., 40] (40% 满)
Page 2: [41, 42, ..., 70]  (30% 满)

删除操作:
DELETE FROM users WHERE id BETWEEN 20 AND 50;

步骤一:检测空间利用率
  → Page 1: 删除后剩 20 条记录 (20% 满)
  → Page 2: 删除后剩 20 条记录 (20% 满)

步骤二:判断是否需要合并
  → 两个页都低于 50% 阈值
  → 决定合并

步骤三:移动数据
  → 将 Page 2 的数据移动到 Page 1
  → Page 1: [1, 2, ..., 20, 51, 52, ..., 70] (40 条记录, 40% 满)

步骤四:释放空页
  → Page 2 变为空页,标记为可用
  → 更新父节点,移除指向 Page 2 的指针

最终状态:
Page 1: [1, 2, ..., 20, 51, 52, ..., 70]  (40% 满)
Page 2: [空闲,可重用]

页合并 vs 页分裂

特性	页分裂	页合并
触发时机	插入数据,页满	删除数据,页太空
操作方向	一页变两页	两页变一页
空间变化	分配新页	释放空页
性能影响	降低写入性能	提高空间利用率
发生频率	高频插入时常见	高频删除时常见
B+Tree 深度	可能增加	可能减少

页合并的性能影响

正面影响

1. 提高空间利用率

合并前:
  Page 1: 20% 满
  Page 2: 20% 满
  平均利用率: 20%
  浪费空间: 80%

合并后:
  Page 1: 40% 满
  Page 2: 空闲
  平均利用率: 40%
  浪费空间: 60%

空间节省: 20%

2. 减少 I/O 次数

扫描 1000 条记录:

合并前:
  - 需要读取 50 个页(每页 20 条)
  - I/O 次数: 50 次

合并后:
  - 需要读取 25 个页(每页 40 条)
  - I/O 次数: 25 次
  
I/O 减少: 50%

3. 提高 Buffer Pool 命中率

数据更紧凑 → 相同内存可缓存更多有效数据 → 命中率提升

负面影响

1. 合并操作的开销

页合并过程:
  - 读取两个页: 2 次 I/O
  - 移动数据: CPU 操作
  - 写回合并后的页: 1 次 I/O
  - 更新父节点: 1-2 次 I/O
  - 释放空页: 元数据操作
  
总计: 4-5 次 I/O + CPU 开销

2. 可能引发连锁反应

Page 1 和 Page 2 合并
  ↓
父节点的一个指针被移除
  ↓
父节点空间利用率也低于 50%
  ↓
触发父节点合并
  ↓
可能导致根节点合并,B+Tree 深度减 1

InnoDB 的页合并策略

合并阈值

sql

-- InnoDB 默认配置
SHOW VARIABLES LIKE 'innodb_fill_factor';
-- 默认值: 100 (表示不留空闲空间)

-- 实际合并阈值
-- InnoDB 内部使用固定阈值: 50%
-- 当页利用率 < 50% 时,尝试合并

合并算法

InnoDB 使用相邻页合并策略:

当前页: Page N
检查对象:
  - 左兄弟页: Page N-1
  - 右兄弟页: Page N+1

合并策略:
  1. 计算与左兄弟合并后的利用率
  2. 计算与右兄弟合并后的利用率
  3. 选择利用率更高的方向合并
  4. 如果都不合适,不合并

源码实现

InnoDB 页合并的核心函数:

// storage/innobase/btr/btr0btr.cc

/**
 * 尝试合并 B+Tree 页
 * 
 * @param cursor  B+Tree 游标
 * @param level 树层级(0=叶子节点)
 * @param mtr   Mini-transaction
 * @return    是否成功合并
 */
ibool btr_compress(
  btr_cur_t* cursor,
  ulint  level,
  mtr_t*   mtr)
{
  // 1. 获取当前页和兄弟页
  page_t* page = btr_cur_get_page(cursor);
  page_t* left_sibling = btr_page_get_prev(page, mtr);
  page_t* right_sibling = btr_page_get_next(page, mtr);
  
  // 2. 检查是否可以合并
  if (!btr_can_merge_with_page(cursor, left_sibling, mtr) &&
    !btr_can_merge_with_page(cursor, right_sibling, mtr)) {
    return FALSE;  // 无法合并
  }
  
  // 3. 选择合并方向
  if (choose_left_sibling) {
    merge_with_left(page, left_sibling, mtr);
  } else {
    merge_with_right(page, right_sibling, mtr);
  }
  
  // 4. 更新父节点
  update_parent_node(cursor, mtr);
  
  // 5. 释放空页
  free_empty_page(mtr);
  
  return TRUE;
}

/**
 * 判断是否可以合并
 */
ibool btr_can_merge_with_page(
  const btr_cur_t* cursor,
  page_no_t    sibling_page_no,
  mtr_t*     mtr)
{
  // 获取兄弟页
  page_t* sibling = buf_page_get(sibling_page_no, mtr);
  
  // 计算合并后的记录数
  ulint current_records = page_get_n_recs(current_page);
  ulint sibling_records = page_get_n_recs(sibling);
  ulint total_records = current_records + sibling_records;
  
  // 检查是否能放入一个页
  if (total_records > page_max_records()) {
    return FALSE;  // 放不下,不能合并
  }
  
  // 计算合并后的空间利用率
  double fill_ratio = calculate_fill_ratio(total_records);
  
  // 如果利用率仍然太低,合并没有意义
  if (fill_ratio < 0.5) {
    return FALSE;
  }
  
  return TRUE;
}

监控页合并

Performance Schema

sql

-- 查看 InnoDB 页合并统计
SELECT 
  NAME,
  COUNT,
  STATUS
FROM performance_schema.innodb_metrics
WHERE NAME LIKE '%page_merge%' 
 OR NAME LIKE '%index_page_merge%';

-- 输出示例:
-- +----------------------------------+--------+----------+
-- | NAME           | COUNT  | STATUS |
-- +----------------------------------+--------+----------+
-- | index_page_merges      | 5678 | enabled  |
-- | index_page_merge_attempts    | 8901 | enabled  |
-- | index_page_merge_successful  | 5678 | enabled  |
-- +----------------------------------+--------+----------+

-- 计算合并成功率:
-- 成功率 = index_page_merge_successful / index_page_merge_attempts
--   = 5678 / 8901 = 63.8%

InnoDB Status

sql

SHOW ENGINE INNODB STATUS\G

-- 输出包含:
-- ----------
-- BUFFER POOL AND MEMORY
-- ----------
-- ...
-- Pages made young 12345, not-young 67890
-- Pages read 100000, created 50000, written 200000
-- ...
-- 页合并相关统计在内部计数器中

估算表的碎片率

sql

-- 页合并不足会导致碎片
SHOW TABLE STATUS LIKE 'users'\G

-- 输出:
-- Data_length: 16777216
-- Index_length:  8388608
-- Data_free:   4194304  -- 空闲空间(包括碎片)

-- 碎片率:
-- Fragmentation = Data_free / (Data_length + Index_length)
--      = 4194304 / (16777216 + 8388608)
--      = 16.7%

-- 碎片率高说明页合并不充分或页分裂频繁

页合并的场景分析

场景一:批量删除

sql

-- 创建测试表
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  status TINYINT,
  create_time DATETIME,
  INDEX idx_status (status)
);

-- 插入 100 万条数据
INSERT INTO orders VALUES (...);

-- 批量删除:删除已完成的历史订单
DELETE FROM orders 
WHERE status = 2  -- 已完成
  AND create_time < '2023-01-01';

-- 影响:
-- 1. 删除约 50 万条记录(50%)
-- 2. 大量页的空间利用率降至 50% 以下
-- 3. 触发频繁的页合并
-- 4. 合并过程可能需要几分钟

-- 监控合并进度
SELECT * FROM performance_schema.innodb_metrics
WHERE NAME LIKE '%merge%';

场景二:定期清理

sql

-- 日志系统:每天清理 7 天前的日志
CREATE TABLE logs (
  log_id BIGINT PRIMARY KEY,
  create_time DATETIME,
  message TEXT,
  INDEX idx_time (create_time)
);

-- 每天执行
DELETE FROM logs WHERE create_time < DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 问题:
-- 1. 每天删除约 10% 的数据
-- 2. 持续的页合并操作
-- 3. 可能影响查询性能

-- 优化方案:使用分区表
ALTER TABLE logs PARTITION BY RANGE (TO_DAYS(create_time)) (
  PARTITION p_old VALUES LESS THAN (TO_DAYS('2024-01-01')),
  PARTITION p_current VALUES LESS THAN MAXVALUE
);

-- 清理时直接删除分区(无需页合并)
ALTER TABLE logs DROP PARTITION p_old;

场景三:更新导致的空洞

sql

-- 用户表:VIP 用户有特殊标识
CREATE TABLE users (
  user_id INT PRIMARY KEY,
  is_vip TINYINT DEFAULT 0,
  vip_expire_date DATE,
  INDEX idx_vip (is_vip)
);

-- VIP 到期,批量更新
UPDATE users 
SET is_vip = 0 
WHERE vip_expire_date < CURDATE();

-- 影响:
-- 1. is_vip=1 的记录减少
-- 2. idx_vip 索引中出现大量空洞
-- 3. 页利用率下降
-- 4. 触发页合并

-- 优化:定期重建索引
ALTER TABLE users FORCE;  -- 重建表和索引

优化页合并的策略

策略一:批量操作代替逐行删除

sql

-- ❌ 低效:逐行删除
FOR i IN 1..10000 LOOP
  DELETE FROM orders WHERE order_id = :id;
END LOOP;

-- ✅ 高效:批量删除
DELETE FROM orders WHERE order_id IN (:id1, :id2, ..., :id10000);

-- 优点:
-- 1. 减少事务提交次数
-- 2. 页合并一次性完成
-- 3. 减少锁竞争

策略二:使用 TRUNCATE 代替 DELETE

sql

-- 清空整个表

-- ❌ 慢:逐行删除,触发大量页合并
DELETE FROM temp_table;

-- ✅ 快:直接释放所有页
TRUNCATE TABLE temp_table;

-- 性能差异:
-- DELETE: 可能需要几分钟(取决于数据量)
-- TRUNCATE: 几毫秒(无论数据量多大)

策略三:定期重建表

sql

-- 高频删除的表,定期重建

-- 方法一:OPTIMIZE TABLE
OPTIMIZE TABLE orders;

-- 效果:
-- 1. 重建表和索引
-- 2. 消除所有碎片
-- 3. 页利用率恢复到接近 100%
-- 4. 更新统计信息

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

-- 建议频率:
-- - 高频删除表:每周一次
-- - 中频删除表:每月一次
-- - 低频删除表:每季度一次

策略四:调整填充因子

sql

-- 对于频繁删除的表,预留更多空间

-- MySQL 8.0+
ALTER TABLE orders STATS_SAMPLE_PAGES = 50;

-- PostgreSQL
CREATE TABLE orders (...) WITH (FILLFACTOR = 70);
-- 留 30% 空间用于后续操作

-- 优点:
-- 1. 减少页分裂和页合并的频率
-- 2. 为删除操作预留缓冲空间

-- 缺点:
-- 1. 占用更多存储空间
-- 2. 扫描时需要读取更多页

策略五:使用软删除

sql

-- 避免物理删除,改用逻辑删除

-- 表结构
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  status TINYINT,  -- 0:正常, 1:已删除
  deleted_at DATETIME
);

-- 删除操作
UPDATE orders 
SET status = 1, deleted_at = NOW() 
WHERE order_id = 123;

-- 查询时过滤
SELECT * FROM orders WHERE status = 0;

-- 优点:
-- 1. 不触发页合并
-- 2. 可以恢复误删除的数据
-- 3. 便于审计

-- 缺点:
-- 1. 需要修改所有查询
-- 2. 数据量持续增长
-- 3. 仍需定期清理历史数据

页合并的实际案例

案例一:订单系统的性能波动

sql

-- 问题:每天凌晨 2 点系统变慢

-- 调查发现:
-- 1. 定时任务在凌晨 2 点清理 30 天前的订单
-- 2. 删除约 10 万条记录
-- 3. 触发大量页合并
-- 4. I/O 飙升,影响正常业务

-- 原始清理脚本
DELETE FROM orders 
WHERE create_time < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 解决方案一:分批删除
DELIMITER $$
CREATE PROCEDURE cleanup_orders()
BEGIN
  DECLARE rows_affected INT DEFAULT 1;
  
  WHILE rows_affected > 0 DO
    DELETE FROM orders 
    WHERE create_time < DATE_SUB(NOW(), INTERVAL 30 DAY)
    LIMIT 1000;  -- 每次只删除 1000 条
    
    SET rows_affected = ROW_COUNT();
    
    DO SLEEP(0.1);  -- 暂停 100ms,降低对业务的影响
  END WHILE;
END$$
DELIMITER ;

-- 解决方案二:使用分区表
ALTER TABLE orders PARTITION BY RANGE (TO_DAYS(create_time)) (
  PARTITION p_202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
  PARTITION p_202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
  -- ...
  PARTITION p_current VALUES LESS THAN MAXVALUE
);

-- 清理时直接删除分区
ALTER TABLE orders DROP PARTITION p_202401;
-- 瞬间完成,无页合并开销

案例二:日志系统的空间管理

sql

-- 问题:日志表增长过快,磁盘空间不足

-- 原始设计
CREATE TABLE application_logs (
  log_id BIGINT PRIMARY KEY,
  create_time DATETIME,
  level TINYINT,
  message TEXT
);

-- 每天删除 7 天前的日志
DELETE FROM application_logs 
WHERE create_time < DATE_SUB(NOW(), INTERVAL 7 DAY);

-- 问题:
-- 1. 每天删除约 100 万条记录
-- 2. 页合并持续数小时
-- 3. 磁盘空间未有效释放

-- 优化方案:按日期分区
CREATE TABLE application_logs_v2 (
  log_id BIGINT,
  create_time DATETIME,
  level TINYINT,
  message TEXT,
  PRIMARY KEY (log_id, create_time)
) PARTITION BY RANGE (TO_DAYS(create_time)) (
  PARTITION p_today VALUES LESS THAN (TO_DAYS(CURDATE() + INTERVAL 1 DAY)),
  PARTITION p_yesterday VALUES LESS THAN (TO_DAYS(CURDATE())),
  PARTITION p_2days_ago VALUES LESS THAN (TO_DAYS(CURDATE() - INTERVAL 1 DAY)),
  -- ...
  PARTITION p_7days_ago VALUES LESS THAN (TO_DAYS(CURDATE() - INTERVAL 6 DAY))
);

-- 清理:直接删除最旧的分区
ALTER TABLE application_logs_v2 DROP PARTITION p_7days_ago;

-- 效果:
-- 1. 清理时间:从数小时降至毫秒级
-- 2. 无页合并开销
-- 3. 磁盘空间立即释放
-- 4. 不影响正常写入

最佳实践

监控碎片率:定期检查表的碎片率,超过 30% 考虑优化
批量删除:避免逐行删除,使用批量操作
定期重建:高频删除的表定期执行 OPTIMIZE TABLE
使用分区表:大表按时间分区,清理时直接删除分区
软删除:重要数据使用逻辑删除,避免频繁物理删除
错峰维护:在业务低峰期执行大规模删除和重建操作
监控性能:关注页合并期间的 I/O 和 CPU 使用情况

关联术语

[[页分裂]]
[[碎片]]
[[OPTIMIZE TABLE]]
[[堆表]]

参考资料

MySQL 官方文档: InnoDB Physical Structure
InnoDB 源码: storage/innobase/btr/btr0btr.cc 中的 btr_compress() 函数
《高性能 MySQL》第 3 章:Schema 优化
Percona Blog: InnoDB Page Merging

定义 ​

详细笔记 ​

核心原理 ​

页合并的触发条件 ​

页合并的执行流程 ​

页合并 vs 页分裂 ​

页合并的性能影响 ​

正面影响 ​

负面影响 ​

InnoDB 的页合并策略 ​

合并阈值 ​

合并算法 ​

源码实现 ​

监控页合并 ​

Performance Schema ​

InnoDB Status ​

估算表的碎片率 ​

页合并的场景分析 ​

场景一:批量删除 ​

场景二:定期清理 ​

场景三:更新导致的空洞 ​

优化页合并的策略 ​

策略一:批量操作代替逐行删除 ​

策略二:使用 TRUNCATE 代替 DELETE ​

策略三:定期重建表 ​

策略四:调整填充因子 ​

策略五:使用软删除 ​

页合并的实际案例 ​

案例一:订单系统的性能波动 ​

案例二:日志系统的空间管理 ​

最佳实践 ​

关联术语 ​

参考资料 ​

定义