MySQL的 redo log、write-ahead logging strategy

解释 MySQL 的 write-ahead logging strategy

MySQL 的 write-ahead logging (WAL) 策略是一种事务日志记录机制，用于确保数据库的持久性和一致性。

在 MySQL 中，当执行事务性操作（如插入、更新、删除）时，WAL 策略会先将相关的变更操作记录到事务日志中，然后再将这些操作应用到数据库中。这个事务日志被称为重做日志（redo log）。

【这时数据的更新写到了内存中，没有直接写到磁盘，因为随机写的IO速度很慢】

重做日志采用顺序写入的方式，将事务中的操作追加到日志文件中，而不是直接写入磁盘。这样做的好处是减少了磁盘随机写入的开销，提高了数据库的写入性能。

当系统崩溃或发生故障时，MySQL 可以利用重做日志来恢复数据库的一致性。在重启数据库时，MySQL 会检查重做日志，将未完成的事务重新应用到数据库中，以确保数据库的完整性。

此外，WAL 策略还能保证事务的原子性。在将事务操作应用到数据库之前，相关的重做日志记录已经被写入磁盘，即使在写入磁盘之前发生了崩溃，MySQL 也可以利用重做日志来进行恢复，使得事务的原子性得到保证。

综上所述，MySQL 的 write-ahead logging 策略通过将事务操作先记录到重做日志，然后再将其应用到数据库，以提高写入性能和确保数据库的一致性、持久性和原子性。

 

参考：

In fact, in addition to allowing writes to be delayed, caching can permit them to be grouped together in two important ways:
Many writes, one flush
A single piece of data can be changed many times in memory without all of the new values being written to disk. When the data is eventually flushed to disk, all the modifications that happened since the last physical write are permanent. For example, many statements could update an in-memory counter. If the counter is incremented one hundred times and then written to disk, one hundred modifications have been grouped into one write.
I/O merging
Many different pieces of data can be modified in memory, and the modifications can be collected together, so the physical writes can be performed as a single disk operation.

This is why many transactional systems use a write-ahead logging strategy. Writeahead logging lets them make changes to the pages in memory without flushing the changes to disk, which usually involves random I/O and is very slow. Instead, they write a record of the changes to a sequential logfile, which is much faster. A back‐ground thread can flush the modified pages to disk later; when it does, it can optimize the writes