作者：bytehouse Oracle ACE、PostgreSQL ACE
10+年数据库架构与运维实战经验
公众号：bytehouse
墨天轮专栏：bytehouse
CSDN：Young DBA
物理备份 vs 逻辑备份 · 锁机制 · 原理深析
文档概要：本文从底层原理出发，详细对比 Oracle、MySQL、PostgreSQL 三大数据库的物理备份与逻辑备份方案，深入分析"锁表"与"不锁表"的根本原因，解释 MVCC、UNDO、WAL、Redo Log 等核心机制如何影响备份行为，并给出生产环境的最佳实践建议。
第一章：备份基础概念
1.1 什么是物理备份？
物理备份直接复制数据库底层的物理文件——数据文件（.dbf、.ibd、.frm）、控制文件、日志文件（Redo Log、WAL）、表空间文件等。它是"文件级别的拷贝"。
物理备份视角：
┌─────────────────────────────────────────────┐
│ 磁盘上的物理文件 │
│ │
│ ├── /data/mysql/ibdata1 (InnoDB 数据) │
│ ├── /data/mysql/mydb/user.ibd (用户表数据) │
│ ├── /data/oracle/orcl/system01.dbf │
│ ├── /data/pg/14/base/12345/ (PG 数据文件) │
│ └── /data/pg/14/pg_wal/ (WAL 日志) │
│ │
│ 备份 = 复制这些物理文件 │
└─────────────────────────────────────────────┘
物理备份的特点
| 特性 | 说明 |
|---|---|
| 速度 | 恢复快（文件拷贝 + 少量日志重放即可） |
| 大小 | 较大（包含索引页、碎片空间、undo 段等） |
| 粒度 | 粗粒度（通常整个表空间或实例） |
| 跨平台 | 不可跨平台（字节序、文件系统布局不同） |
| 跨版本 | 通常不可跨大版本（文件格式变化） |
| 典型工具 | RMAN、xtrabackup、pg_basebackup |
1.2 什么是逻辑备份？
逻辑备份将数据库中的数据转换为可读的逻辑格式——通常是 SQL 语句（INSERT INTO ...）、CSV、XML 或自定义格式。它备份的是"数据内容"而非"数据文件"。
-- 逻辑备份的本质：导出 SQL
-- 示例：mysqldump 导出的内容
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100)
);
INSERT INTO users VALUES (1, '张三');
INSERT INTO users VALUES (2, '李四');
逻辑备份的特点
| 特性 | 说明 |
|---|---|
| 速度 | 较慢（逐条读取数据并生成 SQL） |
| 大小 | 较小（只有纯数据，无索引、空页等） |
| 粒度 | 细粒度（可精确到单个表甚至行） |
| 跨平台 | 可以（SQL 是标准语言） |
| 跨版本 | 通常可以（只要 SQL 语法兼容） |
| 典型工具 | expdp/exp、mysqldump、pg_dump |
1.3 两种备份的核心差异
flowchart TD
A[数据库备份] --> B[物理备份]
A --> C[逻辑备份]
B --> B1[拷贝物理文件]
B --> B2[需处理文件不一致性]
B --> B3[恢复快: cp + replay log]
C --> C1[导出 SQL/CSV 数据]
C --> C2[需处理读一致性]
C --> C3[恢复慢: 逐条执行 SQL]
B2 -.-> D{如何保证一致?}
C2 -.-> D
D --> E[方案A: 锁表 — 简单粗暴]
D --> F[方案B: 利用 MVCC/UNDO/WAL — 无锁]
第二章：Oracle 数据库备份深度分析
2.1 Oracle 物理备份 — RMAN
工具概览
| 项目 | 值 |
|---|---|
| 工具 | Recovery Manager (RMAN) |
| 备份类型 | 物理文件级别（数据文件、控制文件、归档日志、spfile） |
| 备份模式 | 热备份（online）或冷备份（offline） |
| 是否需要目录 | 可选（使用控制文件或恢复目录） |
锁表分析：✅ 不锁表
RMAN 在热备份模式下备份不锁表。原因如下：
原理 1：热备份模式（BEGIN BACKUP）
Oracle 在 ALTER TABLESPACE BEGIN BACKUP 后会：
- 冻结数据文件头的 SCN — 记录备份开始时的检查点 SCN
- 记录完整的前镜像到 Redo Log — 备份期间修改的数据块，Redo Log 中会记录该块修改前的完整内容（完整块映像），而不仅仅是变更向量
- 允许正常读写 — 事务继续执行，DML 完全不受影响
正常写入时 Redo：
Block 42: 将 byte 100-120 从 'A' 改为 'B'
BEGIN BACKUP 后 Redo（块级日志）：
Block 42: 完整前镜像 (8192 bytes) + 变更后的完整块 (8192 bytes)
↑ 这叫 "Whole Block Logging" — 为了 RMAN 恢复时能重建任何被部分写入的块
原理 2：块级重做恢复（Block Media Recovery）
RMAN 备份时，数据文件正在被修改，备份出来的文件不一定在某个一致时间点上。RMAN 的恢复过程：
备份文件（可能不一致）
│
▼
应用备份期间记录的 Redo Log（归档日志 + Online Redo）
│
▼
前滚到一致状态（所有块都在同一 SCN）
│
▼
应用归档日志继续前滚到目标时间点
由于 BEGIN BACKUP 模式保证了 Redo Log 中有备份期间每个被修改块的完整前镜像，RMAN 可以精确重建任何一个在备份时被部分写入（fractured block）的数据块。
关键术语：分裂块（Fractured Block / Split Block）
操作系统通常在 512 字节扇区级别写入，但 Oracle 数据块通常是 8KB。如果备份进程在 OS 正在写入 8KB 块中间的某个 512 字节扇区时读取该块——就会读到"半个旧数据 + 半个新数据"的分裂块。
8KB Oracle 块 ┌────┬────┬────┬────┬────┬────┬────┬────┐
│ S1 │ S2 │ S3 │ S4 │ S5 │ S6 │ S7 │ S8 │ ← 512B 扇区
└────┴────┴────┴────┴────┴────┴────┴────┘
↑ 备份进程此时读取
读到的内容：S1~S2 是旧数据，S3~S8 是新数据 → ❌ 块损坏
BEGIN BACKUP 时的 Whole Block Logging 就是为了解决这个问题——Redo Log 中有完整块内容，RMAN 恢复时可以识别并重建任何损坏的块。
2.2 Oracle 逻辑备份 — expdp / Data Pump
工具概览
| 项目 | 值 |
|---|---|
| 工具 | expdp（Data Pump Export） |
| 备份类型 | 逻辑备份（SQL DDL + DML 语句或 Oracle 专有格式） |
| 导出格式 | 目录对象中的转储文件（.dmp） |
锁表分析：✅ 默认不锁表
原理：Flashback Query（闪回查询 / 一致性快照）
Oracle 的 Multi-Version Read Consistency（多版本读一致性）基于 UNDO 表空间：
时间线 →
[expdp 启动时刻]
│
▼
SCN=1000
│
┌─────────────────────────┼─────────────────────────┐
│ │ │
事务A: UPDATE 事务B: INSERT 事务C: DELETE
SCN=1002 SCN=1005 SCN=1008
❌ 不读 ❌ 不读 ❌ 不读
│ │ │
▼ ▼ ▼
UNDO 段保留事务A前镜像 UNDO 段保留事务B前镜像 UNDO 段保留事务C前镜像
expdp 始终读 SCN=1000 时的数据版本
所有修改都从 UNDO 中回滚回 SCN=1000 的快照
Data Pump 默认使用 FLASHBACK_TIME = SYSTIMESTAMP 或 FLASHBACK_SCN = CURRENT_SCN。这相当于告诉 Oracle：“给我看备份开始那一瞬间的数据，之后的所有修改我都看不见”。
关键参数：
FLASHBACK_TIME— 备份到指定时间的快照FLASHBACK_SCN— 备份到指定 SCN 的快照FLASHBACK=NO— 不使用一致性快照，需要应用 CONSISTENT=Y 参数（可能锁表）
何时会锁表？
| 场景 | 锁类型 | 说明 |
|---|---|---|
FLASHBACK=NO + CONSISTENT=Y |
短暂表锁 | 保证导出数据跨表一致性，但非常短暂 |
TABLE_EXISTS_ACTION=REPLACE |
表锁 | 导入时替换表结构需要的排他锁 |
REFRESH 模式（物化视图） |
表锁 | 完全刷新物化视图时 |
2.3 Oracle 备份锁表总结
| 备份方式 | 工具 | 锁表？ | 原因 |
|---|---|---|---|
| 物理-热备份 | RMAN | ❌ | Whole Block Logging + 块级恢复 |
| 物理-冷备份 | 手动 cp | ❌ | 数据库已关闭，无锁问题 |
| 逻辑-默认 | expdp | ❌ | Flashback Query + UNDO 快照 |
| 逻辑-强制一致 | expdp CONSISTENT=Y | ⚠️ 短暂 | 没有 FLASHBACK 时需锁保证一致性 |
第三章：MySQL 备份深度分析
3.1 MySQL 的特殊性：存储引擎架构
MySQL 的锁表问题必须从存储引擎说起：
MySQL 架构
┌──────────────────────────────────────┐
│ SQL Layer (解析器/优化器/缓存) │
├──────────────────────────────────────┤
│ 存储引擎层 │
│ │
│ ┌─────────┐ ┌─────────┐ │
│ │ InnoDB │ │ MyISAM │ ... │
│ │ 支持MVCC │ │ 无MVCC │ │
│ │ 行级锁 │ │ 表级锁 │ │
│ │ 事务支持 │ │ 无事务 │ │
│ └─────────┘ └─────────┘ │
└──────────────────────────────────────┘
关键点：MySQL 的备份工具必须同时处理多种引擎。MyISAM 没有 MVCC，无法实现无锁一致性备份。
3.2 MySQL 物理备份 — XtraBackup
工具概览
| 项目 | 值 |
|---|---|
| 工具 | Percona XtraBackup |
| 备份类型 | 物理备份（InnoDB 数据文件） |
| 支持引擎 | InnoDB（完全支持）、MyISAM（需短暂锁） |
锁表分析（InnoDB）：✅ 不锁表
XtraBackup 的工作原理分为三个阶段：
阶段一：文件复制（后台运行）
┌────────────────────────────────────────────┐
│ xtrabackup --backup │
│ │
│ 1. 启动后台 redo log 追踪线程 │
│ └── 持续读取 InnoDB 的 Redo Log │
│ │
│ 2. 逐块复制 InnoDB 数据文件 (.ibd) │
│ └── 此时数据库正常读写，文件一直在变 │
│ │
│ 3. 复制完成后，记录当前 Redo Log LSN │
└────────────────────────────────────────────┘
阶段二：应用 Redo Log
┌────────────────────────────────────────────┐
│ xtrabackup --prepare │
│ │
│ 1. 重做（Redo）：将 Redo Log 中已提交 │
│ 但尚未刷入数据文件的事务前滚 │
│ │
│ 2. 回滚（Undo）：将 Redo Log 中未提交 │
│ 的事务回滚 │
│ │
│ → 得到完全一致的数据文件 │
└────────────────────────────────────────────┘
为什么 InnoDB 模式下不锁表？
InnoDB 的 Redo Log（重做日志/事务日志）机制是核心：
InnoDB 写入流程：
┌─────────────┐
事务提交 ─────────→ │ Redo Log │ (持久化到磁盘，确保不丢)
└──────┬──────┘
│ 异步/后台
▼
┌─────────────┐
│ Data File │ (.ibd 文件，延迟写入)
└─────────────┘
因为 Redo Log 始终先于数据文件持久化（WAL — Write-Ahead Logging 原则），所以 XtraBackup 即使复制了不一致的数据文件（阶段一），也能通过 Redo Log 重放（阶段二）将其恢复到一致状态。
这就是 InnoDB 的 Crash Recovery 能力——XtraBackup 只是利用了 InnoDB 内置的恢复机制。
MyISAM 的特殊处理
XtraBackup 在备份 MyISAM 表时，会在阶段一结束时短暂加 FLUSH TABLES WITH READ LOCK（FTWRL）：
- 将 MyISAM 表的数据和索引刷到磁盘
- 加全局读锁
- 复制 MyISAM 文件
- 释放锁
这个锁通常只持续几秒钟。
3.3 MySQL 逻辑备份 — mysqldump
工具概览
| 项目 | 值 |
|---|---|
| 工具 | mysqldump |
| 备份类型 | 逻辑备份（SQL 语句） |
| 输出 | CREATE TABLE + INSERT INTO 语句 |
锁表分析：⚠️ 取决于参数和存储引擎
场景 A：默认参数（无 --single-transaction）
mysqldump -u root mydb -- 默认行为
默认行为：
- 逐表执行
LOCK TABLES <table> READ（加读锁） - 导出表数据
UNLOCK TABLES
问题：
- MyISAM 表：✅ 必须加锁，否则无法保证一致性
- InnoDB 表：❌ 也被加了锁，虽然 InnoDB 不需要锁
不加 --single-transaction 时：
╔══════════════════════════════════════╗
║ mysqldump 默认行为 ║
║ ║
║ 表A: LOCK → 导出 → UNLOCK ║
║ 表B: LOCK → 导出 → UNLOCK ║
║ 表C: LOCK → 导出 → UNLOCK ║
║ ║
║ ⚠️ 表A和表C不在同一个时间点 ║
║ ⚠️ 跨表引用可能不一致 ║
╚══════════════════════════════════════╝
场景 B：使用 --single-transaction（推荐）
mysqldump -u root mydb --single-transaction
新增行为：
- 在事务开始时执行
START TRANSACTION WITH CONSISTENT SNAPSHOT - 利用 InnoDB 的 MVCC 创建读一致性快照
- 从此所有读操作都在这个快照上进行
- 无需任何 LOCK TABLES（对 InnoDB 表）
使用 --single-transaction 时：
╔══════════════════════════════════════╗
║ START TRANSACTION WITH ║
║ CONSISTENT SNAPSHOT (SCN=1000) ║
║ ║
║ 表A: 直接导出 ← 读 SCN=1000 的快照 ║
║ 表B: 直接导出 ← 读 SCN=1000 的快照 ║
║ 表C: 直接导出 ← 读 SCN=1000 的快照 ║
║ ║
║ ✅ 所有表在同一时间点 ║
║ ✅ 跨表引用完全一致 ║
║ ✅ 不锁任何 InnoDB 表 ║
╚══════════════════════════════════════╝
重要限制：
--single-transaction只对 InnoDB 表有效- 如果数据库中有 MyISAM 表，仍然需要加锁（FTWRL）
- 通常配合
--lock-tables=false和--all-databases使用
InnoDB MVCC 的工作机制（核心原理）
为什么 InnoDB 能在不加锁的情况下看到"过去"的数据？
InnoDB 数据行结构（内部）：
┌──────────────────────────────────────────────────┐
│ Row Header │
│ ├── DB_TRX_ID (6 bytes) — 最后修改该行的事务ID │
│ ├── DB_ROLL_PTR (7 bytes) — 指向 UNDO 段中前镜像 │
│ └── DB_ROW_ID (6 bytes) — 行ID（单调递增） │
├──────────────────────────────────────────────────┤
│ Row Data (实际数据列) │
└──────────────────────────────────────────────────┘
Read View（读视图）结构：
┌────────────────────────────────────────────┐
│ Read View │
│ ├── low_limit_id — 当前最大事务ID+1 │
│ ├── up_limit_id — 活跃事务中最小ID │
│ ├── creator_trx_id — 创建该视图的事务ID │
│ └── m_ids[] — 活跃事务ID列表 │
└────────────────────────────────────────────┘
可见性判断规则：
行的 DB_TRX_ID < up_limit_id → ✅ 可见（已提交的旧事务）
行的 DB_TRX_ID 在 m_ids[] 中 → ❌ 不可见（活跃事务，需回滚）
行的 DB_TRX_ID = creator_trx_id → ✅ 可见（自己的修改）
行的 DB_TRX_ID >= low_limit_id → ❌ 不可见（未来事务）
当 mysqldump 开启 --single-transaction：
- 创建 Read View（记录当前所有活跃事务列表）
- 读取行数据时，如果行的
DB_TRX_ID在活跃事务列表中，通过DB_ROLL_PTR找到 UNDO 段中的前镜像 - 返回事务开始时的旧版本数据
- 整个过程不需要锁——所有新事务继续写新版本，备份进程读旧版本
3.4 MySQL 备份锁表全表总汇
| 备份方式 | 工具 | 存储引擎 | 锁表？ | 原因 |
|---|---|---|---|---|
| 物理 | XtraBackup | InnoDB | ❌ 不锁 | Redo Log 重放保证一致性 |
| 物理 | XtraBackup | MyISAM | ✅ 短时锁 | 需 FLUSH TABLES 确保一致 |
| 逻辑-默认 | mysqldump | InnoDB | ✅ 锁表 | 默认 LOCK TABLES |
| 逻辑-优化 | mysqldump + --single-transaction | InnoDB | ❌ 不锁 | MVCC 快照读 |
| 逻辑 | mysqldump | MyISAM | ✅ 必须锁 | MyISAM 无 MVCC |
| 逻辑 | mysqlpump | InnoDB | ❌ 不锁 | 支持并行 + MVCC |
| 物理 | MySQL Enterprise Backup | InnoDB | ❌ 不锁 | 类似 XtraBackup 机制 |
第四章：PostgreSQL 备份深度分析
4.1 PostgreSQL 的架构特点
PostgreSQL 的备份机制与 Oracle 和 MySQL 有本质不同，源于其进程架构和 WAL 系统：
PostgreSQL 架构
┌──────────────────────────────────────────────┐
│ Postmaster（主进程） │
├──────────────────────────────────────────────┤
│ ┌─────────┐ ┌─────────┐ ┌─────────┐ │
│ │ Backend │ │ Backend │ │ Backend │ ... │
│ │ Process │ │ Process │ │ Process │ │
│ │ (会话1) │ │ (会话2) │ │ (会话3) │ │
│ └────┬────┘ └────┬────┘ └────┬────┘ │
│ │ │ │ │
│ ▼ ▼ ▼ │
│ ┌──────────────────────────────────┐ │
│ │ 共享缓冲区 (Shared Buffers) │ │
│ └──────────────┬───────────────────┘ │
│ │ │
│ ▼ │
│ ┌──────────────────────────────────┐ │
│ │ WAL Writer (写 WAL 日志) │ │
│ └──────────────┬───────────────────┘ │
│ │ │
│ ┌──────────────▼───────────────────┐ │
│ │ 磁盘数据文件 + WAL 段 │ │
│ └──────────────────────────────────┘ │
└──────────────────────────────────────────────┘
PG 的核心思想：每一次数据修改都必须先写入 WAL（Write-Ahead Log），然后才能刷新数据文件。 这就是著名的 WAL 原则（Write-Ahead Logging，或叫"先写日志"）。
4.2 PostgreSQL 物理备份 — pg_basebackup
工具概览
| 项目 | 值 |
|---|---|
| 工具 | pg_basebackup |
| 备份类型 | 物理备份（整个实例数据目录） |
| 输出 | 压缩的 tar 包或目录拷贝 |
| 是否需要 WAL 归档 | 建议打开，否则需将 WAL 纳入备份 |
锁表分析：✅ 不锁表
原理：基础备份 + WAL 重放
pg_basebackup 的工作流程：
步骤 1：开始备份
┌────────────────────────────────────────────────┐
│ SELECT pg_start_backup('my_backup'); │
│ │
│ 1. 强制做一次 Checkpoint │
│ └── 将所有脏页刷到磁盘 │
│ │
│ 2. 在数据目录中写入 backup_label 文件 │
│ └── 记录检查点位置（WAL 位置） │
│ │
│ 3. 开启 Full Page Writes（如果未开启） │
│ └── 备份期间每个修改的页面都完整记录到WAL │
│ │
│ ⚠️ 不加任何业务表锁！ │
└────────────────────────────────────────────────┘
步骤 2：复制文件（数据库正常运行）
┌────────────────────────────────────────────────┐
│ rsync/cp 整个 PG 数据目录 │
│ │
│ ├── base/ — 数据库数据文件 │
│ ├── global/ — 全局系统表 │
│ ├── pg_wal/ — WAL 日志（可选） │
│ ├── pg_xact/ — 事务提交状态 │
│ └── ... │
│ │
│ ⚠️ 拷贝期间数据文件不断变化 │
│ ⚠️ 可能出现分裂块（partial page write） │
└────────────────────────────────────────────────┘
步骤 3：结束备份
┌────────────────────────────────────────────────┐
│ SELECT pg_stop_backup(); │
│ │
│ 1. 切换 WAL 段（生成新的 WAL 文件） │
│ 2. 更新备份历史信息到 pg_backup_history │
│ 3. 记录备份结束时的 WAL 位置 │
└────────────────────────────────────────────────┘
恢复过程：
┌────────────────────────────────────────────────┐
│ 1. 将备份的数据文件拷贝到目标位置 │
│ 2. 从 backup_label 获取起始 WAL 位置 │
│ 3. 启动 PostgreSQL（进入恢复模式） │
│ 4. 从备份时的 WAL 位置开始重放 WAL │
│ └── 前滚到一致状态 │
│ └── 回滚未提交的事务 │
│ 5. 数据库可正常使用 │
└────────────────────────────────────────────────┘
关键机制：Full Page Writes（全页写入）
这是 PG 解决"分裂块"问题的方案，与 Oracle 的 Whole Block Logging 异曲同工：
正常运行时，WAL 记录的是"变更向量"：
UPDATE users SET name='张三' WHERE id=1
→ WAL: "在 page 42 的 offset 100 处，将 6 字节从'李四'改为'张三'"
启用 Full Page Writes 后，第一次修改某个页面时：
→ WAL: "完整 page 42 内容 (8192 bytes)" + 变更向量
↑ 这样即使备份时读取了分裂的 page 42，恢复时也有完整的副本
pg_start_backup() 强制开启 Full Page Writes，确保备份期间每个被修改的数据页都有完整镜像在 WAL 中。
4.3 PostgreSQL 逻辑备份 — pg_dump
工具概览
| 项目 | 值 |
|---|---|
| 工具 | pg_dump |
| 备份类型 | 逻辑备份（SQL 语句或自定义格式） |
| 输出格式 | 纯 SQL、自定义格式（.dump）、目录格式、tar |
锁表分析：✅ 不锁表（ACCESS SHARE 锁，不阻塞读写）
原理：MVCC + Serializable Snapshot
pg_dump 使用 Repeatable Read 事务隔离级别：
-- pg_dump 内部执行：
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 或者使用 Serializable 快照：
-- (PG 9.1+ 使用 Serializable Snapshot Isolation)
ACCESS SHARE 锁详解
pg_dump 会对正在导出的表加 ACCESS SHARE 锁：
锁级别冲突矩阵（简化）：
┌──────────────────────────────────────────────────────────────────────┐
│ ACCESS SHARE ROW SHARE ... ACCESS EXCLUSIVE │
│ (pg_dump) (SELECT) (DROP/TRUNCATE) │
├──────────────────────────────────────────────────────────────────────┤
│ ACCESS SHARE ✅ 兼容 ✅ 兼容 ❌ 冲突 │
│ ROW SHARE ✅ 兼容 ✅ 兼容 ❌ 冲突 │
│ ROW EXCLUSIVE ✅ 兼容 ✅ 兼容 ❌ 冲突 │
│ ... ✅ 兼容 ✅ 兼容 ❌ 冲突 │
│ ACCESS EXCLUSIVE ❌ 冲突 ❌ 冲突 ❌ 冲突 │
└──────────────────────────────────────────────────────────────────────┘
结论：
- ACCESS SHARE 锁 ✅ 允许 SELECT、INSERT、UPDATE、DELETE
- ACCESS SHARE 锁 ✅ 允许 CREATE INDEX CONCURRENTLY
- ACCESS SHARE 锁 ❌ 不允许 DROP TABLE、TRUNCATE、ALTER TABLE
（这些需要 ACCESS EXCLUSIVE 锁）
所以 pg_dump 不阻塞任何 DML（增删改查），只阻塞 DDL（ALTER/DROP/TRUNCATE）。
PG MVCC 实现（与 InnoDB 的差异）
PG 的 MVCC 实现方式与 MySQL InnoDB 有重要区别：
PG 数据行结构（内部）：
┌──────────────────────────────────────────┐
│ Heap Tuple Header │
│ ├── t_xmin — 插入该行的事务 ID │
│ ├── t_xmax — 删除/锁定该行的事务 ID │
│ ├── t_cid — 命令计数器 │
│ ├── t_ctid — 指向新版本或自身 │
│ └── ... │
├──────────────────────────────────────────┤
│ Data (实际数据) │
└──────────────────────────────────────────┘
可见性判断（基于 SnapshotData）：
Snapshot.xmin: 所有 < 此值的事务都已完成
Snapshot.xmax: 所有 >= 此值的事务都不可见
Snapshot.xip[]: 活跃事务列表
如果行的 t_xmin >= xmax → ❌ 不可见（未来插入）
如果行的 t_xmin 在 xip[] 中 → ❌ 不可见（插入事务未提交）
如果行的 t_xmax 不在 xip[] 中 → ❌ 不可见（已被删除/更新）
其他情况 → ✅ 可见
关键差异：PG 是追加式更新（append-only update）
PG 的 UPDATE 不是"原地修改"，而是：
UPDATE users SET name='张三' WHERE id=1;
物理上等价于：
1. 将旧行标记为已删除 (t_xmax = 当前事务ID)
2. 插入新行 (t_xmin = 当前事务ID)
所以同一行可能有多个版本分布在数据文件中！
这意味着 PG 的 MVCC 需要定期清理过期版本（VACUUM），这是 PG 运维中非常重要的维护任务。
4.4 PostgreSQL 备库备份
PG 还有一个独特优势——可以从备库（Standby） 进行备份，完全零影响：
# 在备库上执行，主库完全无感知
pg_basebackup -h standby_host -D /backup/dir
# 备库上的 pg_dump
pg_dump -h standby_host -d mydb > mydb.sql
4.5 PostgreSQL 备份锁表总结
| 备份方式 | 工具 | 锁表？ | 锁类型 | 阻塞哪些操作 |
|---|---|---|---|---|
| 物理 | pg_basebackup | ❌ 不锁 | 无业务表锁 | 无 |
| 物理-快照 | 文件系统快照 | ❌ 不锁 | 短暂关闭备份模式 | 无（快照在秒级完成） |
| 逻辑 | pg_dump | ❌ 不锁 | ACCESS SHARE | DROP/TRUNCATE/ALTER TABLE |
| 逻辑 | pg_dumpall | ❌ 不锁 | ACCESS SHARE | DROP/TRUNCATE/ALTER |
| 逻辑-自定义格式 | pg_dump -Fc | ❌ 不锁 | ACCESS SHARE | DROP/TRUNCATE/ALTER |
| 逻辑-并行 | pg_dump -j N | ❌ 不锁 | ACCESS SHARE | 同 pg_dump |
第五章：三库对比总表
5.1 备份锁表对比
| 数据库 | 物理备份工具 | 锁表？ | 原理 | 逻辑备份工具 | 锁表？ | 原理 |
|---|---|---|---|---|---|---|
| Oracle | RMAN | ❌ 不锁 | Whole Block Logging + Redo 恢复 | expdp | ❌ 不锁 | Flashback Query + UNDO |
| MySQL | XtraBackup | ❌ 不锁(InnoDB) | Redo Log Crash Recovery | mysqldump | ⚠️ 分情况 | MVCC(–single-transaction) 或锁表 |
| PostgreSQL | pg_basebackup | ❌ 不锁 | Full Page Writes + WAL 恢复 | pg_dump | ❌ 不锁 | MVCC + ACCESS SHARE |
5.2 底层机制对比
| 机制 | Oracle | MySQL (InnoDB) | PostgreSQL |
|---|---|---|---|
| 事务日志 | Redo Log + Undo 表空间 | Redo Log + Undo 段 | WAL (Write-Ahead Log) |
| MVCC 实现 | UNDO 段中存储前镜像 | UNDO 段中存储前镜像 | 数据文件中保留多版本（追加更新） |
| 快照机制 | Flashback Query (SCN) | Read View (事务ID) | SnapshotData (xmin/xmax) |
| 分裂块防护 | Whole Block Logging | Doublewrite Buffer | Full Page Writes |
| 恢复方式 | Redo 前滚 + Undo 回滚 | Redo 前滚 + Undo 回滚 | WAL 前滚 + 回滚未提交事务 |
| 真空维护 | 自动 UNDO 管理 | 自动 UNDO 清理 | VACUUM 回收死元组 |
5.3 备份性能与影响对比
| 维度 | Oracle RMAN | MySQL XtraBackup | PostgreSQL pg_basebackup |
|---|---|---|---|
| 备份对性能影响 | 低（增量块追踪） | 中（全量文件IO） | 中（全量文件IO） |
| 增量备份 | ✅ 块级增量 | ✅ 页级增量 | ✅ WAL 归档增量 |
| 压缩 | ✅ 内置 | ✅ 可选 | ✅ 可选（pg Compact） |
| 并行 | ✅ 多通道 | ✅ 多线程 | ✅ -j 参数 |
| 最小恢复时间 | 分钟级 | 分钟级 | 分钟级 |
| 云端支持 | AWS RDS 不支持 | 支持 | RDS 不支持物理备份 |
| 逻辑备份对大表 | 慢（expdp 单进程） | 慢（mysqldump 单线程） | 慢（pg_dump -j 可加速） |
第六章：为什么有些备份"锁表"而另一些不锁？
6.1 核心问题：什么是一致性备份？
一致性备份意味着：备份的数据集在某个时间点上逻辑上是自洽的。
不一致的例子：
导出表A (时间T1): ID=1 的用户有订单
导出表B (时间T2): 订单表的 ID=1 的订单已被删除
→ 如果表A引用表B，这个备份就是不一致的
6.2 实现一致性的三种策略
策略一：锁表（冻结世界）
一切操作暂停 ──→ 数据静止 ──→ 备份 ──→ 解锁
原理：最简单，但影响最大
适用：MyISAM、小数据库
代价：写入完全中断
策略二：事务日志重放（物理恢复）
拷贝不一致文件 + 记录变更日志 → 恢复时通过日志重放达到一致
原理：利用数据库的 Crash Recovery 能力
适用：RMAN、XtraBackup、pg_basebackup
代价：恢复时需要额外步骤（apply log）
策略三：MVCC 快照（逻辑读一致性）
在事务开始时刻固定一个"数据库快照"
后续读写都在这个快照上进行
原理：利用数据库的多版本能力
适用：expdp、mysqldump --single-transaction、pg_dump
代价：读操作可能变慢（需遍历 UNDO/死元组）
6.3 为什么 MyISAM 必须锁表？
因为 MyISAM 不存在以上任何一种无锁策略：
| 策略 | MyISAM 支持？ | 原因 |
|---|---|---|
| 锁表 | ✅ | 唯一选择 |
| 事务日志重放 | ❌ | 无事务、无 crash recovery 能力 |
| MVCC 快照 | ❌ | 无 MVCC、无事务隔离级别概念 |
MyISAM 的插入和读取之间没有任何隔离机制。如果在备份时允许写入，可能读到半写行（Partial Row）：
写入线程正在写入：
行数据: '张' 已写入，'三' 尚未写入
备份线程此时读取：
读到: '张\x00\x00...' ← ❌ 损坏数据
6.4 本质总结：答案在一张表里
数据库能无锁备份吗？
│
▼
数据库存储引擎支持 MVCC 吗？
┌─────┴─────────┬─────────┐
│ │ │
是 否 │
│ │ │
▼ ▼ │
┌─────────────┐ ┌─────────────┐ │
│ 逻辑备份: │ │ 逻辑备份: │ │
│ 利用快照读 │ │ 必须锁表 │ │
│ ──不锁表 │ │ (MyISAM) │ │
└──────┬──────┘ └─────────────┘ │
│ │
┌──────▼──────┐ │
│ 物理备份: │ │
│ 利用 Redo │ │
│ ──不锁表 │ │
│ (InnoDB/ │ │
│ Oracle/PG) │ │
└─────────────┘ │
│
备份工具不利用 MVCC 吗？
│
┌───┴───┐
│ │
是 否
│ │
▼ ▼
┌────┐ ┌────┐
│不锁│ │锁表│
│表 │ │ │
└────┘ └────┘
第七章：生产环境最佳实践
7.1 Oracle 最佳实践
# RMAN 增量备份策略（无锁）
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;
# expdp 无锁导出
expdp user/password DIRECTORY=backup_dir \
DUMPFILE=full_$(date +%Y%m%d).dmp \
LOGFILE=expdp_$(date +%Y%m%d).log \
FULL=Y \
FLASHBACK_TIME="SYSTIMESTAMP" # 无锁！！！！
7.2 MySQL 最佳实践
# XtraBackup 物理备份（InnoDB 无锁）
xtrabackup --backup --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/full
# mysqldump 逻辑备份（InnoDB + --single-transaction 无锁）
mysqldump -u root --all-databases \
--single-transaction \ # ← 关键：开启 MVCC 快照
--routines \
--triggers \
--events \
--hex-blob \
--master-data=2 \
| gzip > backup.sql.gz
MySQL 备份决策树
你的表全是 InnoDB 吗？
├── 是 → 用 mysqldump --single-transaction ✅ 无锁
│ 或用 XtraBackup ✅ 无锁
│
└── 否（有 MyISAM）→ 两种选择：
├── XtraBackup ✅ 短暂锁 MyISAM 表
└── mysqldump ✅ 锁 MyISAM 表读
7.3 PostgreSQL 最佳实践
# pg_basebackup 物理备份（完全无锁）
pg_basebackup -h localhost -D /backup/full \
--format=tar --gzip \
--wal-method=stream # 不依赖 WAL 归档
# pg_dump 逻辑备份（ACCESS SHARE，不阻塞读写）
pg_dump -h localhost -d mydb \
--format=custom \ # 自定义格式，支持并行恢复
--file=/backup/mydb.dump
# pg_dump 并行备份
pg_dump -h localhost -d mydb \
-j 4 \ # 4个并行工作线程
--format=directory \
--file=/backup/mydb_dir/
7.4 综合策略建议
| 场景 | 推荐方式 | 原因 |
|---|---|---|
| 日常全量备份 | 物理备份 | 恢复快，数据完整 |
| 表级别恢复 | 逻辑备份 | 粒度细，灵活 |
| 跨版本迁移 | 逻辑备份 | SQL 兼容性 |
| 大数据量（TB 级） | 物理增量 + WAL 归档 | 时间窗口小 |
| 开发/测试环境 | 逻辑备份 | 灵活可编辑 |
| 灾备/异地容灾 | 物理备份 + 流复制（日志传输） | 最小 RPO/RTO |
附录：常见问题 FAQ
Q1: --single-transaction 和 --lock-all-tables 能同时用吗？
不能。 --single-transaction 只对 InnoDB 表起作用，会自动禁用 --lock-tables。如果同时指定，--lock-tables 被忽略。
Q2: pg_dump 的 ACCESS SHARE 锁会影响建索引吗？
不影响 CREATE INDEX CONCURRENTLY（该命令只加 SHARE UPDATE EXCLUSIVE 锁）。但会阻塞 CREATE INDEX（非并发，需要 SHARE 锁）。
Q3: XtraBackup 备份 MyISAM 表时锁多久？
通常在 1-5 秒。XtraBackup 在备份结束前统一加 FTWRL（FLUSH TABLES WITH READ LOCK），刷 MyISAM 表到磁盘并复制文件，然后立即释放。
Q4: RMAN 能在备库上执行吗？
可以。 Oracle Active Data Guard 允许在备库上运行 RMAN 备份，主库完全无影响。这是 Oracle 零影响备份的最佳实践。
Q5: pg_dump 对大表 (100GB+) 性能如何？
默认顺序扫描，可能很慢。使用 -j (jobs) 参数并行备份多个表。或者对超大表使用 --table 单独导出 + --exclude-table-data 排除数据后再用 COPY 命令单独导出。
Q6: 逻辑备份 vs 物理备份，恢复时间差距多大？
| 数据量 | 物理恢复 | 逻辑恢复 |
|---|---|---|
| 10GB | 1-3 分钟 | 10-30 分钟 |
| 100GB | 5-15 分钟 | 1-3 小时 |
| 1TB | 30-90 分钟 | 5-24 小时 |
物理恢复通常比逻辑恢复快 5-10 倍，且差距随数据量增大而扩大。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
