MySQL是如何保证数据的完整性

数据的一致性和完整性对于在线业务的重要性不言而喻，如何保证数据不丢呢？今天我们就探讨下关于数据的完整性和强一致性，MySQL做了哪些改进。

一. MySQL的二阶段提交

在Oracle和MySQL这种关系型数据库中，讲究日志先行策略（Write-Ahead Logging）,只要日志持久化到磁盘，就能保证MySQL异常重启后，数据不丢失。在MySQL中，提到日志不得不提的就是redo log和binlog。

1. redo log

redo log又称重做日志文件，详细的记录了对每一个数据页里面的数据行的修改，记录的是数据修改之后的值。Redo log是用来做数据库crash recovery的，是保证数据安全的非常重要的功能之一。

redo log的写入的方式是顺序写、循环写，通过innodb_log_file_size和innodb_log_files_in_group两个参数控制redo log的文件大小和个数。redo log在写入磁盘前会先写redo log buffer中，大小由innodb_log_buffer_size控制。日志在写入redo log buffer后是如何持久化到磁盘的呢？为了控制redo log的写入策略，Innodb根据innodb_flush_log_at_trx_commit参数不同的取值采用不同的策略，它有三种不同的取值：

• 1. 设置为 0 的时候：事务提交时由MySQL的后台Master线程每隔1秒将缓存区的文件刷新到日志文件中。
• 2. 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘，保证了事务日志不丢失，但会对数据库性能稍有影响。
• 3. 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 日志文件中，但不会刷盘，由文件系统自行刷磁盘。

三种模式下，0的性能最好，但是不安全，MySQL进程一旦崩溃会导致丢失一秒的数据。1的安全性最高，但是对性能影响最大，2的话主要由操作系统自行控制刷磁盘的时间，如果仅仅是MySQL宕机，对数据不会产生影响，如果是主机异常宕机了，同样会丢失数据。

2. binlog

binlog又称二进制日志，记录了对MySQL数据库执行更改的所有操作，不包含select和show操作，主要起到了恢复、复制、审计等功能。Binlog的格式主要有statement、row、mixed三种。

Statement：基于操作的SQL语句记录到binlog中，不建议使用。

Row：基于行的变更情况记录，会记录行更改前后的内容，row模式也是数据库不丢数据的重要保证，推荐使用。

Mixed：混合前两个模式，不建议使用。

Binlog的写入逻辑也比较简单：事务执行过程中，先写入binlog cache,事务提交时再写入binlog文件。binlog cache由binlog_cache_size和max_binlog_size参数控制，每个线程分配一个binlog cache,但是共用binlog文件。

Binlog的写入日志文件的机制由sync_binlog控制：

• 1. sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
• 2. sync_binlog=1 的时候，表示每次提交事务都会执行 fsync，将数据刷盘；
• 3. sync_binlog=N(N>1) 的时候，表示n次事务提交之后，MySQL才进行一次fsync动作，将binlog cache中的数据刷入磁盘。

innodb_flush_log_at_trx_commit和sync_binlog都设置为1是MySQL数据中经典的双一模式，是数据库不丢数据的保障。

MySQL数据采取WAL机制就是为了减少每次脏数据刷盘带来的性能影响，如果设置”双一”策略会不会影响数据库的性能呢？其实这主要得益于redo log和binlog都是顺序写，磁盘的顺序写比随机写的速度要快的多，加上MySQL内部的组提交机制，已经大幅降低了对磁盘的IOPS消耗了。

3. 两阶段提交

MySQL引入二阶段提交（two phase commit or 2pc），MySQL内部会将普通事务当做一个XA事务（内部分布式事务）来处理,会自动为每个事务分配一个唯一的ID（XID），COMMIT会被动的分成Prepare和Commit两个阶段。

第一阶段：Transaction Prepare Phase

此时SQL已经成功执行，并生成xid信息及redo和undo的内存日志。然后调用prepare方法完成第一阶段，将事务状态设为TRX_PREPARED，并将redo log刷盘。

第二阶段：Commit Phase

如果事务第一阶段进入prepare阶段，则将产生的binlog写入文件并刷盘，此时事务已经铁定要提交了。

具体异常场景分析：

1. 当事务在prepare阶段crash，数据库recovery的时候该事务未写⼊Binary log并且存储引擎未提交，则该事务rollback。

2. 当事务在binlog阶段crash，此时⽇志还没有成功写⼊到磁盘中，启动时会rollback此事务。3. 当事务在binlog⽇志已经fsync()到磁盘后crash，但是InnoDB没有来得及commit，此时MySQL数据库recovery的时候将会读出⼆进制⽇志的Xid_log_event，然后告诉InnoDB提交这些XID的事务，InnoDB提交完这些事务后会回滚其它的事务，使存储引擎和⼆进制⽇志始终保持⼀致。

MySQL的二阶段提交就保证了数据库在异常宕机重启后的数据不丢失。

二. Double Write

前面我们说了，redo log、binlog以及二阶段提交保证了数据在MySQL异常重启后能够通过前滚和回滚恢复数据。MySQL在recovery时通过redo log进行恢复，redo log记录的是页上的物理操作，但是这里有个问题，如果页本身就是错的，比如发生页的部分写问题(页大小是 16K，假设在把内存中的脏页写到数据库的时候，写了4K 突然掉电。也就是前两 4K 是新的，后 12K 是旧的，那么这个数据页就是不完整的，是一个坏掉的数据页), 这时redo恢复的时候会去校验数据页的完整性，此时数据页已经损坏了，故无法使用 redo log 进行恢复，这个数据就丢失了。

Double Write原理:

1、当刷新缓冲池脏页时，并不直接写到数据文件中，而是先拷贝至double write buffer。

2、然后从double write buffer分两次写入磁盘共享表空间中，每次写入 1MB。

3、最后再从double write buffer写入数据文件。虽然数据总是写入两次，但是由于double write 写入的时候是顺序写，实际上也就牺牲了系统性能的 10%左右。

这样就可以解决上文提到的部分写失效的问题，因为在磁盘共享表空间中已有数据页副本拷贝，如果数据库在页写入数据文件的过程中宕机，在实例恢复时，可以从共享表空间中找到该页副本，将其拷贝覆盖原有的数据页，再应用重做日志即可。

3. 小结

今天我们聊了MySQL的二阶段提交和double write机制，分别解决了在MySQL宕机重启以及发生页的部分写的场景下，MySQL是如何做到不丢失数据。那如果我们的操作系统宕机无法启动了，又该怎么办呢？MySQL在集群架构中又做了哪些优化来保证数据不丢失呢？我们下一章再来和大家分享MySQL在集群架构中的优化改进。