数据库锁表、备份与快照保证数据一致性

一、锁表（Locking）

1. 什么是锁表？

• 锁表：数据库在执行某些操作（如 DDL、备份、查询）时，对表加锁，防止其他事务修改数据，确保操作的一致性和完整性。
• 常见场景：
  • DDL 操作（如 ALTER TABLE）：修改表结构时可能锁表。
  • 备份操作（如 mysqldump）：为保证一致性可能短暂锁表。
  • 高并发写入：事务未提交时，其他事务可能被阻塞。

2. 不同数据库的锁表行为

数据库	锁表行为	典型场景
MySQL	默认 DDL 操作锁表（如 ALTER TABLE），支持在线 DDL（ALGORITHM=INPLACE）	表结构变更、备份（无 --single-transaction）
PostgreSQL	DDL 操作几乎不锁表，仅需短暂锁（如 VACUUM FULL）	表结构变更、备份
Oracle	DDL 操作通常不锁表，但某些操作（如重建索引）可能锁表	表结构变更、备份

3. 锁表对生产的影响

• 写入阻塞：锁表期间，其他事务的 INSERT/UPDATE/DELETE 操作会被阻塞，可能导致服务延迟或超时。
• 解决方案：
  • 使用 在线 DDL 工具（如 MySQL 的 pt-online-schema-change、PostgreSQL 的 pg_repack）。
  • 在 低峰期执行 DDL 或备份。

二、数据一致性（Consistency）

1. 备份期间如何保证数据一致性？

• 核心机制：通过 事务快照 或 物理快照 技术，确保备份期间的数据一致性。
• 常见方法：
  1. 事务一致性：
     • MySQL：使用 --single-transaction 参数（InnoDB 支持），备份时启动一个事务，读取数据快照。
     • PostgreSQL：通过 BEGIN + SELECT ... FOR UPDATE 锁定表，或使用 pg_dump -Fc。
  2. 物理快照：
     • MySQL：XtraBackup 通过复制数据文件和 redo log 保证一致性。
     • Oracle：RMAN 通过 SCN（系统变更号）一致性。
     • 云数据库：使用底层存储快照（如 AWS RDS 快照）。

2. 备份期间写入数据是否导致不一致？

• 答案：不会，只要使用一致性备份工具（如 --single-transaction、XtraBackup），即使备份期间有写入操作，备份数据也会保持一致性。
• 原理：
  • 逻辑备份：通过事务快照隔离写入操作。
  • 物理备份：通过日志回放（如 redo log）还原到备份时刻的一致状态。

三、逻辑备份 vs 物理备份

1. 定义与对比

维度	逻辑备份	物理备份
备份内容	逻辑结构和数据（如表、SQL 语句）。	物理文件（如数据文件、日志文件）。
实现方式	通过 SQL 语句导出数据（如 mysqldump、pg_dump）。	直接复制数据库的物理文件（如 XtraBackup、RMAN）。
恢复方式	通过 SQL 语句导入数据（如 source backup.sql）。	直接替换物理文件（如恢复数据文件），需重启数据库服务。
是否依赖数据库	依赖数据库运行（需连接数据库执行导出）。	不依赖数据库运行（可离线操作）。
速度	较慢（需解析数据并生成 SQL）。	极快（直接复制文件）。
恢复速度	较慢（需解析 SQL 并重建索引）。	极快（直接覆盖文件，无需重建）。
灵活性	高（可选择特定表、过滤数据、跨平台恢复）。	低（需完全恢复整个数据库实例）。
典型工具	mysqldump、pg_dump、expdp	XtraBackup、RMAN、pg_basebackup

2. 适用场景

需求	推荐方案	原因
快速备份/恢复	物理备份	物理备份速度极快，适合生产环境。
部分数据恢复	逻辑备份	逻辑备份支持按表或条件筛选数据。
跨平台迁移	逻辑备份	逻辑备份可转换为其他数据库系统的格式（如 MySQL → PostgreSQL）。
最小化锁表时间	物理备份（如 XtraBackup）	物理备份几乎不锁表，不影响生产写入。
低成本存储	逻辑备份	逻辑备份文件通常更小，适合云存储。

---

四、逻辑数据库 vs 物理数据库

1. 定义与对比

维度	逻辑数据库	物理数据库
定义	用户和应用程序看到的数据库结构（如表、视图、索引）。	数据库在磁盘上的实际存储结构（如数据文件、日志文件）。
特点	与具体存储方式无关，通过 SQL 操作（如 SELECT * FROM table）。	与存储引擎和硬件相关（如 InnoDB 的 .ibd 文件、Oracle 的数据文件）。
可见性	对用户可见，应用程序直接操作逻辑数据库。	对用户不可见，通常由 DBA 管理。
关系	一个逻辑数据库可对应多个物理数据库（如主从复制）。	物理数据库是逻辑数据库的底层实现。

2. 典型示例

• MySQL：
  • 逻辑数据库：用户通过 SELECT * FROM users; 查询数据。
  • 物理数据库：数据存储在 /var/lib/mysql/users.ibd 文件中。
• PostgreSQL：
  • 逻辑数据库：用户通过 CREATE TABLE 定义表结构。
  • 物理数据库：数据存储在 pg_data/base/ 目录下的物理文件中。

---

五、总结与最佳实践

1. 核心总结

• 锁表：需谨慎操作，优先使用在线 DDL 工具或低峰期执行。
• 数据一致性：通过事务快照或物理快照技术保证备份一致性。
• 逻辑备份：适合灵活性高的场景，但速度较慢。
• 物理备份：适合生产环境，速度快但恢复灵活性低。
• 逻辑/物理数据库：逻辑是用户视角，物理是底层实现。

2. 实际应用建议

1. 生产环境备份策略：
   • 物理备份（如 XtraBackup、RMAN）作为主备份，快速恢复。
   • 逻辑备份（如 mysqldump --single-transaction）作为辅助备份，支持部分恢复。
2. 锁表规避：
   • 使用在线 DDL 工具（如 pt-online-schema-change）避免锁表。
   • 在业务低峰期执行 DDL 或备份操作。
3. 数据一致性验证：
   • 定期测试备份文件的恢复流程，确保一致性。
   • 使用一致性检查工具（如 mysqlcheck、pg_checksums）。