Oracle/MySQL/PostgreSQL的MVCC实现及redo/undo/binlog相关概念

最近好像突然有点回归到传统的关系型/集中式数据库原理上面来了也可能是之前一直对这些概念印象不太深刻。今天再总结一下好让记忆深刻一些。本文主要的目的是把Oracle、MySQL、PostgreSQL三大主流的关系型数据库中关于MVCC实现及redo log、undo log及binlog的关系描述清楚。

Oracle

Oracle中只有REDO日志和UNDO日志没有BINLOG日志的概念。
REDO日志是Oracle在线或归档重做日志文件中记录的信息可以用于重放事务信息。REDO日志在语句执行时生成并写入到SGA中的Redo log Buffer内存中然后在COMMIT提交时刷新到磁盘上进行持久化保存。如果正好事务提交但数据库发现故障此时REDO日志已经落盘但实际的数据还没来得及刷新到磁盘可以在实例恢复的时候从REDO日志中进行重做把数据进行恢复。
UNDO日志是Oracle在UNDO段中记录的信息用于撤销或回滚事务。当对数据进行修改时同时也会生成UNDO日志UNDO日志中记录了数据被修改前的值因此当事务回滚或某种原因执行失败时可以利用UNDO日志来将数据恢复到之前的状态。
MVCC实现简单来说Oracle的MVCC是利用UNDO回滚段机制。由于UNDO段中保存了某个数据修改前的映像数据。当在查询数据时首先会在UNDO段中查找该数据块的前映像然后把前映像和current块合并形成一个CR BLOCK通过查询CR BLOCK来满足查询数据一致性。

MySQL

MySQL中REDO、UNDO和BINGLOG日志都存在但是只有BINLOG是Server层的日志REDO和UNDO日志是引擎层InnoDB的日志如果换成是其他引擎就未必有REDO和UNDO日志了。
BINLOG日志作为MySQL操作记录归档的日志记录了所有对数据库的数据、表结构、索引等变更的操作也就是说只要对数据库有变更的操作都会记录到BINLOG中。BINLOG以事件形式记录不仅记录操作的语句还记录了语句执行的耗时。BINLOG有三种记录格式分别是ROW、STATEMENT、MIXED。

• ROW基于变更的数据行进行记录如果一个UPDATE语句修改一百行数据就会记录100行对应的记录日志。
• STATEMENT基于SQL语句级别的记录日志相对于ROW模式只会记录这个update语句。所以此模式非常节省日志空间避免大量的IO操作。
• MIXED混合模式。一般语句修改使用STATEMENT格式保存如一些函数或语句无法完成主从复制的操作则采用ROW格式保存。

BINLOG写入有以下几种策略

• sync_binlog=0每次提交事务binlog不会马上写入磁盘而是先写到page cache有丢失日志的风险。
• sync_binlog=1每次提交都会执行fsync将binlog写入到磁盘。
• sync_binlog>1每次提交事务先写到page cache只有等到积累了N个事务之后才fsync写入到磁盘此设置下会有丢失N个事务日志的风险。

REDO日志属于InnoDB层的日志用来支持InnoDB的事务特性。REDO日志的功能与上述一致。

UNDO日志也属于InnoDB层的日志功能与上述一致。

MVCC实现由于 InnoDB具有记录UNDO日志的能力因此MySQL的MVCC也是通过UNDO来实现的不过与Oracle不同的是MySQL的MVCC是基于记录级的它通过UNDO来形成行的版本链。

PostgreSQL

和Oracle、MySQL不同PostgreSQL中首先没有UNDO的概念因此不存在UNDO日志。它也没有BINLOGBINLOG是MySQL特有的日志功能。
PostgreSQL中只有预写日志WAL它的功能跟REDO差不多。
WAL日志WAL与REDO差不多当事务提交时WAL会先落盘。当数据库崩溃恢复时使用WAL日志来恢复数据。

MVCC实现PostgreSQL中的多版本并发是通过在表中数据行的多个版本来实现的比如我们在更新一条记录PostgreSQL并不是直接修改该数据而是通过插入一条全新的数据同时对老的数据加以标识。因此PostgreSQL中通常需要定期对数据进行清理动作否则会有数据膨胀的问题。