[小技巧55]深入解析数据库日志机制：逻辑日志、物理日志与物理逻辑日志在 MySQL InnoDB 中的实现

一、引言：日志是数据库可靠性的基石

在现代数据库系统中，日志（Logging） 是实现 原子性（Atomicity） 和 持久性（Durability） 的核心机制。根据记录内容的抽象层次，数据库日志通常分为三类：

• 逻辑日志（Logical Logging）
• 物理日志（Physical Logging）
• 物理逻辑日志（Physiological Logging）

这三种日志在粒度、语义、恢复方式和适用场景上存在显著差异。

二、三种日志格式详解

1. 逻辑日志（Logical Logging）

• 定义：记录事务的高层操作语义，如 SQL 语句或行级变更。
• 特点：
  • 与存储结构无关；
  • 可跨版本、跨引擎重放；
  • 适用于复制、逻辑备份。
• MySQL 实现：Binary Log（binlog）
  • STATEMENT 格式：记录原始 SQL；
  • ROW 格式：记录主键 + 列值变更（仍属逻辑层，因不涉及页/偏移）。
  • mixed格式：混合模式

示例（ROW 格式）：

Table_map: test.T
Write_rows: (c1=1, c2='abc')

2. 物理日志（Physical Logging）

• 定义：记录底层存储的字节级变化，如“将页 X 偏移 Y 处的 Z 字节从 A 改为 B”。
• 特点：
  • 高度依赖页格式；
  • 恢复时直接 memcpy 覆盖内存；
  • 无法支持页结构演进。
• MySQL 实现：InnoDB 未使用纯物理日志。
• 典型系统：LevelDB WAL、早期 Berkeley DB。

InnoDB 不采用此模式，因其无法兼容压缩页、加密页等高级特性。

3. 物理逻辑日志（Physiological Logging）

• 定义：以 物理页为作用域，记录 页内的逻辑操作，在页 X 上插入记录 Y。
• 特点：
  • 日志 = <page_id, operation, args>；
  • 恢复时调用存储引擎函数重做操作（非字节覆写）；
  • 兼顾效率与灵活性。
• MySQL 实现：InnoDB Redo Log

✅ 示例：

<MLOG_REC_INSERT, space=1, page_no=100, record=(1,'abc'), index_id=PRIMARY>
<MLOG_REC_INSERT, space=1, page_no=200, record=(1,ptr), index_id=key_c1>

三、以一条 INSERT 为例：三种日志的生成对比

假设执行：

CREATE TABLE T (c1 INT, c2 VARCHAR(10), KEY(c1)) ENGINE=InnoDB;
INSERT INTO T VALUES (1, 'abc');

插入一行 (1, 'abc')，涉及：

• 聚簇索引（主键索引，默认基于 rowid 或隐式主键）
• 二级索引 key_c1

通常至少修改 两个 B+ 树页（聚簇索引页 + 二级索引页）。

1. 逻辑日志（Logical Log）记录情况

• 记录内容：事务的高层语义
• 示例：<INSERT, table=T, values=(1, 'abc')>
• MySQL 对应：binlog（ROW 格式）Table_map: T
  Write_rows: (1, 'abc')

• 数量：1 条（整个 INSERT 操作）

2. 物理逻辑日志（Physiological Log）记录情况

• 记录内容：页 ID + 页内逻辑操作
• 示例：<MLOG_REC_INSERT, space_id=1, page_no=100, record=(1, 'abc', DB_ROW_ID=…), index_id=PRIMARY>
  <MLOG_REC_INSERT, space_id=1, page_no=200, record=(1, ptr_to_clustered), index_id=key_c1>

• 特点：
  • 每条日志绑定一个 page_no；
  • 记录的是 “在页 X 上插入记录 Y”，而非字节；
  • 恢复时需调用 InnoDB 的 page_cur_insert_rec_low() 等函数重做。
• MySQL 对应：InnoDB Redo Log
• 数量：≥2 条（每个被修改的页至少一条）

特殊情况
只有聚簇索引（无二级索引）：插入数据

InnoDB 内部操作：

即使只有 1 条用户记录插入，也可能因 B+ 树结构维护产生多条 redo log。

3. 物理日志（Physical Log）记录情况

• 记录内容：字节级变更（offset + old/new value）
• 示例（针对一个页的多次修改）：<space=1, page=100, offset=24, new_value=0x0005> // 页头：记录数从 4→5
  <space=1, page=100, offset=16380, new_value=0x0064> // Slot 数组更新
  <space=1, page=100, offset=200, new_value=…> // 新记录内容
  <space=1, page=100, offset=prev_rec+2, new_value=…> // 前一条记录的 next 指针

• 数量：每个页可能产生 N 条（N ≥ 3~5 很常见）
• MySQL 是否使用？：否。InnoDB 不使用纯物理日志。

对比表

关键洞察：
InnoDB 跳过纯物理日志，直接从逻辑操作生成 physiological redo log，避免了字节日志的冗余与脆弱性。

四、InnoDB 为何选择 Physiological Logging？

五、日志协同：Redo Log + Binlog + Doublewrite Buffer

InnoDB 的可靠性依赖三大组件协同：

• Redo Log（Physiological）：保证页级操作可重做；
• Doublewrite Buffer：保证页本身完整（防 partial page write）；
• Binlog（Logical）：保证事务可复制、可 PITR（时间点恢复）。

注意：只有当 innodb_flush_log_at_trx_commit=1 且 sync_binlog=1 时，才能实现真正的 crash-safe replication。

六、三种日志核心特性

MySQL 通过 分层日志设计 实现了高可靠与高性能的统一：

• 逻辑日志（binlog） 面向应用与复制；
• 物理逻辑日志（redo log） 面向存储引擎崩溃恢复；
• Doublewrite Buffer 作为最后一道防线，保障页完整性。

七、面试题