数据库 - 深度探索 Mysql 的 bin log、redo log、undo log

想要深入 Mysql，其日志系统的底层原理必不可少，在面试中也经常遇到此类问题

谈谈：mysql 中 redo log 、undo log、 binlog 分别实现了事务ACID的那些特性？
谈谈：如何解决 bin log 与 redo log 的一致性问题？
谈谈：一条 SQL 更新语句是如何执行的？
谈谈：redo log / bin log 两阶段提交原理

今天做一下系统化、体系化的梳理，使得大家内力猛增，可以充分展示一下大家雄厚的 “技术肌肉”，让面试官爱到 “不能自已、口水直流”，然后实现”offer直提”。

基础知识：MySQL架构&SQL语句执行流程

先简单了解一下MySQL大概的架构：

第1层 - 连接层：

连接层对来自客户端的连接进行权限验证并将相关的连接信息维护到连接池中，以便于下次连接。

第2层 - 服务层：

提供NoSQL，SQL的API、SQL解析、SQL语句优化、SQL语句缓存等相关组件。

第3层 - 存储引擎层：

提供了一系列可插拔的存储引擎，我们可以通过存储引擎来进行数据的写入与读取，通过存储引擎，我们可以真正的与硬盘中的数据和日志进行交互，我们可以根据需求来选择合适的存储引擎进行使用。

第4层 - 文件系统层：

该层包含了具体的日志文件和表数据文件以及MySQL相关的程序。

上面的四个部分，MySQL 的架构核心两个部分：服务层（Server Layer）和存储引擎层（Storage Engine Layer）。

其中，服务层负责 MySQL 的核心逻辑处理，而存储引擎层负责实际的数据存取。

服务层（Server Layer）

主要负责以下功能：

查询解析与优化语法解析

将客户端发送的 SQL 查询转化为可理解的内部结构，检查 SQL 语句是否符合 MySQL 语法规则。查询重写：服务层会将一些查询优化为等效的、执行效率更高的形式，比如将子查询改写为 JOIN，或简化复杂的表达式。查询优化：MySQL 服务层有一个查询优化器，会基于数据统计信息，选择最优的执行计划。包括表的连接顺序、索引的选择等。其优化方式包括基于成本的优化器（CBO）。

查询缓存

查询缓存MySQL 服务器层可以将一些查询的结果缓存起来，尤其是频繁执行的查询。如果客户端请求的查询已经存在于缓存中，MySQL 可以直接从缓存中返回结果，而无需重新执行查询。

执行SQL语句

SQL 执行在查询解析和优化之后，服务层负责将 SQL 语句执行。服务层会将解析后的查询计划传递给存储引擎层，存储引擎负责实际的数据操作。

服务层中包含了三大组件

解析器（Parser）

解析器是 SQL 查询执行的第一步，它的职责是将用户发送的 SQL 语句解析为数据库能够理解的内部结构。

SQL 词法分析：解析器首先对 SQL 语句进行词法分析，将 SQL 语句分割成多个“单词”或“标记”，如表名、列名、关键字等。
语法分析：接着，解析器会根据 SQL 语法规则生成对应的解析树（Parse Tree），用来描述 SQL 语句的逻辑结构。这个过程检查 SQL 语句的语法是否正确。
语义分析：确认 SQL 语句中涉及的数据库对象是否存在（比如表名、字段名是否有效），并且检查权限。

解析完成后，生成一个中间表示结构，交由下一步进行处理。

优化器（Optimizer）

优化器负责选择最优的执行计划，使查询能够以最高效的方式运行。

逻辑优化：优化器会对 SQL 语句进行逻辑优化，比如 SQL 语句重写、消除冗余操作、合并重复条件、重新排列 WHERE 子句中的条件等。
物理优化：在物理优化阶段，优化器会选择最优的访问路径和执行顺序。例如，它会决定使用哪种索引（如果有多个索引可选），是否做全表扫描，如何连接多张表（选择嵌套循环、哈希连接或排序合并连接等）。
成本估算：优化器会基于数据库的统计信息（例如表的大小、索引的选择性等）来估算不同执行计划的成本，选择代价最低的执行方案。

经过优化后，优化器会生成一个查询执行计划，并交给执行器处理。

执行器（Executor）

执行器的任务是按照优化器生成的执行计划，逐步执行查询，访问数据并返回结果。

权限检查：在执行之前，执行器会首先检查用户是否有权限执行相应的操作。如果没有权限，则返回错误信息。
执行执行计划：执行器根据生成的执行计划，依次调用存储引擎的接口来执行具体的操作。例如，如果是查询操作，执行器会调用存储引擎来读取相应的数据；如果是插入操作，执行器则会调用存储引擎来插入数据。
结果返回：执行器根据查询的结果，将数据以合适的格式返回给客户端。如果涉及多个步骤（如 JOIN 操作），执行器会协调各个步骤的执行，并组合最终的结果集。

三个核心组件之间的交互流程

解析器：SQL 语句转换为解析树。
优化器：生成最优的执行计划。
执行器：根据计划调用存储引擎执行操作并返回结果。

这三个组件相互协作，完成从接收到 SQL 查询到返回结果的整个过程。

Mysql数据更新语句的流程

Mysql数据更新语句的流程, 大致如下图所示：

具体更新一条记录 update table set name=“李四” where id = 1000; 的流程如下:

执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id = 1 这一行记录：

如果 id=1000 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；

如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器。

执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样：

如果一样的话就不进行后续更新流程；

如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；

开启事务，记录 undo log

InnoDB 层更新记录前，InnoDB 层更新记录前，首先要记录相应的 undo log，因为这是更新操作，需要旧值记下来，也就是要生成一条 undo log，undo log 不是之间写磁盘，而是会写入Buffer Pool ，只是会写入 Buffer Pool 中的 Undo 页面。

不过，这里也有WAL思想，在修改该 Undo 页面前，需要先记录对应的 redo log，所以，这里的流程是：先记录修改 Undo 页面的 redo log ，然后再真正的修改 Undo 页面。

提示

Undo Log 的第一个目标就是为了保证事务的原子性（Atomicity）。事务的原子性意味着事务中的所有操作要么全部成功，要么全部回滚到事务开始之前的状态，不会出现部分执行的情况。如果事务在执行过程中发生错误，或者用户明确发起了回滚操作（ROLLBACK），数据库会通过读取 Undo Log 中记录的旧值，恢复被修改的数据到事务开始前的状态。这种恢复操作确保了事务的原子性，即便事务中途失败，数据库也能够恢复到一致的状态。

当然，Undo Log 还用于实现 MVCC（Multi-Version Concurrency Control）。当不同事务同时读取数据时，Undo Log 提供了“旧版本”的数据视图，使得即使某个事务在修改数据，其他事务仍然能够读取到事务开始时的数据版本。这不仅提升了并发性能，也间接支持了原子性和隔离性的实现。所以，Undo Log 实现了对事务的原子性、隔离性的支持。

面试的时候，一般人都知道 undo log 实现了事务的原子性，如果把undo log的隔离性说出来，一定会惊讶到面试官的。

先记录 redo log ，再更新数据

InnoDB 层开始更新数据记录之前，根据 WAL 思想，先记录修改数据页面的 redo log ，然后再真正的修改数据页面。

修改数据页面的过程是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，而不是之间写磁盘。为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。至此，一条记录更新完了。

提示

Redo Log 是实现事务 持久性（Durability） 的关键机制之一。事务的持久性意味着一旦事务提交成功，即使数据库系统发生崩溃，系统也能够在重启后保证数据不会丢失。Redo Log 通过记录事务的修改并在崩溃后进行恢复，确保数据库的一致性和数据的持久性。

记录 binlog

在一条更新语句执行完成后，然后开始记录该语句对应的 binlog。此时记录的 binlog 会被保存到 binlog cache，并没有刷新到硬盘上的 binlog 文件。在事务提交时，才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。

提示

Binlog（Binary Log） 在 MySQL 中主要用于 主从复制 和 数据恢复，它通过协调 redo log 和 binlog 的写入，确保事务的一致性( 包括两个一致性：崩溃一致性&主从一致性)，特别是在分布式场景下的 主从一致性。

事务提交，以两阶段提交为例：

prepare 阶段：将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘；

commit 阶段：将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件）；

提示

原始的两阶段提交的性能比较低，更高性能的提交方式是组提交，说白了就是批量提交。但是原始的两阶段提交比较好理解，为了方便说明，这里不说组提交的过程，只说两阶段提交.至此，一条更新语句执行完成。

至此，一条更新语句执行完成。

解释器做了什么？

以下面一条简单的 SQL 语句为例，我们来解释下执行器和 InnoDB 存储引擎在更新时做了哪些事情

sql
update table set name=“李四”  where id = 1000;

执行器：找存储引擎取到 id = 1000 这一行记录
存储引擎：根据主键索引树找到这一行，如果 id = 1000 这一行所在的数据页本来就在内存池（Buffer Pool）中，就直接返回给
执行器；否则，需要先从磁盘读入内存池，然后再返回执行器：拿到存储引擎返回的行记录，把 name 字段设置为 “张三”，得到一行新的记录，然后再调用存储引擎的接口写入这行新记录
存储引擎：将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，为 redo log 中的事务打上 prepare 标识。然后告知执行器执行完成了，随时可以提交事务

注意不要把这里的提交事务和我们 sql 语句中的提交事务 commit 命令搞混了哈，我们这里说的提交事务，指的是事务提交过程中的一个小步骤，也是最后一步。当这个步骤执行完成后，commit 命令就执行成功了。

执行器：生成这个操作的 bin log，并把 bin log 写入磁盘
执行器：调用存储引擎的提交事务接口
存储引擎：把刚刚写入的 redo log 状态改成提交（commit）状态，更新完成

可以看到，所谓两阶段提交，其实就是 把 redo log 的写入拆分成了两个步骤：prepare 和 commit。

所以，为什么要这样设计呢？这样设计怎么就能够实现崩溃恢复呢？

根据两阶段提交，崩溃恢复时的判断规则是这样的：

如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交
如果 redo log 里面的事务处于 prepare 状态，则判断对应的事务 binlog 是否存在并完整
```
  如果 binlog 存在并完整，则提交事务；否则，回滚事务。
```

结合上面的两阶段提交，实现了事务的持久性和一致性。实际的场景中，上图片中的写入 redo log 和写入 bin log，并不等同于写入磁盘文件，可能仅仅写入内存了

undolog 原子性 + 隔离性

什么是undolog？

undolog一般叫回滚日志。

事务回滚rollback功能就是通过 undolog实现的，通过undolog保证了为事务的原子性，undolog主要功能如下：

事务回滚
MVCC

什么是事务回滚？

当开启一段事务还未提交时，事务中的操作可能会出现错误异常，这时候就可以通过undo log将事务中的操作进行回滚（rollback），意思是回到事务开启前那个状态。

例如：开启事务后，对表中某条记录进行修改（将该记录字段值由value1 ——> value2 ——> value3 ），如果从整个修改过程中出现异常，事务就会回滚，字段的值就回到最初的起点（值为value1 ）。

事务如何通过undo log进行回滚操作呢？

这个很好理解，我们只需要在undo log日志中记录事务中的反向操作即可，发生回滚时直接通过undolog中记录的反向操作进行恢复，例如：

事务进行insert操作，undo log记录delete操作
事务进行delete操作，undo log记录insert操作
事务进行update操作（value1 改为value2 ），undolog记录update操作（value2 改为value3 ）

undo log保存的是一个版本链，也就是使用 DB_ROLL_PTR 这个字段来连接的。多个事务的 undo-log 日志副本 (数据快照)，组成了一个 副本链

undo log版本链，数据页中的每行数据都会分配两个字段：trx_id 和 roll_pointer。

trx_id代表事务id，记录了这一系列事务操作是基于哪个事务；
roll_pointer代表回滚指针，就是当要发生rollback回滚操作时，就通过roll_pointer进行回滚，这个链表称为版本链。

为了提升 Undo Log 读写性能， Undo Log 也在内存中进行了缓存，所处的位置在 Buffer Pool 中，如下图所示：

Buffer Pool 中的 Undo Log 页是 InnoDB 存储引擎中用于缓存 Undo Log 记录的内存区域。

Buffer Pool 是一个重要的内存结构，用于提高数据库的性能，通过在内存中存储频繁访问的数据页和日志记录，减少对磁盘的 I/O 操作。

以下是有关 Buffer Pool 中 Undo Log 页的详细信息：

Buffer Pool 是 InnoDB 的内存缓存，用于存储数据页和索引页，以便快速访问。它通过减少磁盘 I/O，提高数据库的整体性能。
在 Buffer Pool 中，除了数据页外，还可以存储 Undo Log 页，这样可以加速事务的回滚和数据恢复。

buffer pool 中有数据页也有 undo 页，基于wal思想，不仅对buffer pool 中数据页修改操作会记录到redo log buffer，对 undo 页修改操作也会记录到 redo log buffer，这样就通过redo log保证了事务持久性。

当事务Commit之后，undo 页本身就没有利用价值了，此时通过后台线程中的Master Thread或Purge Thread进行 undo 页的回收工作。

什么是MVCC和事务的隔离性？

那么，如果多个事务并行的读写操作，每一个事务应该使用那个版本呢？

MVCC 实现了自己 Copy-On-Write思想提升并发能力的时候，也需要数据的副本，这里既然undo-log 有了那么多副本，MVCC 就借鸡生蛋，复用这些数据副本。

所以，undo log 中的副本，可以用于实现多版本并发控制（MVCC），提升事务的并发性能，同时每一个事务操作自己的副本，实现事务的隔离性。

MVCC机制主要通过三个组件实现：

隐藏字段
undo log 日志
ReadView

基础知识 - Buffer Pool

nnoDB中的缓存区叫innodb_buffer_pool，当读取数据时，就会先从缓存中查看是否数据的页（page）存在，不存在的话去磁盘上检索，查到后缓存到innodb_buffer_pool中。

同理，插入、修改、删除也是先操作缓存里数据，之后再以一定频率更新到磁盘上，这个刷盘机制叫做Checkpoint。

InnoDB中的数据主要有数据页、索引页、插入缓存、自适应哈希索引、锁信息和数据字典信息。

提示

RedoLog不在缓存区中，有个专门的 redo log_buffer 重做日志缓冲池。

Buffer Pool是很经典的缓存池，其中又以Page为单位

空闲页
干净页
脏页【类似操作系统中，修改位为1】

下图是mysql官网原图，其展示了Buffer Pool在innodb引擎架构的组成

Buffer Pool（缓冲池）是MySQL数据库中InnoDB存储引擎的一个重要组成部分，它位于内存中，用于缓存数据库中经常被访问的数据页和索引页，以减少对磁盘的I/O操作，从而提高数据库的读写性能

Buffer Pool的工作原理基于“时间局部性”和“空间局部性”原则，即最近访问过的数据在未来很可能再次被访问，且一个数据项被访问时，与其相邻的数据项也很可能被访问。

Buffer Pool通过缓存热点数据和索引，减少了磁盘I/O操作，大大提高了数据库的性能。

在Buffer Pool中，数据的加载和淘汰机制是维护和优化其性能的关键。

当数据库服务器接收到一个查询请求时，它首先会在Buffer Pool中查找对应的数据页。如果数据页已经在内存中，服务器可以直接返回数据，而不需要等待磁盘IO操作。如果数据页不在内存中，服务器会从磁盘读取数据，并将其存储在Buffer Pool中，以便后续查询可以直接从内存中获取数据。

所以，Buffer Pool的大小对数据库性能有很大的影响。

一个大的Buffer Pool可以缓存更多的数据页，从而减少对磁盘的IO操作，提高查询性能。
但是过大的Buffer Pool可能会占用太多的服务器内存资源导致其他服务器的性能下降。

因此需要找到一个平衡点根据服务器的整体性能和数据库的特性来合理配置Buffer Pool的大小。

Buffer Pool中的核心组件包括索引页、数据页、Undo页等。索引页存储了InnoDB表的索引结构，数据页存储了InnoDB表的实际数据行，Undo页存储了旧版本的数据，用于支持事务的ACID属性中的原子性、隔离性（Isolation）。

LRU链表是Buffer Pool中最主要的链表，用于管理缓存页的访问顺序和淘汰策略。
free链表管理未被使用的空闲页，
flush链表管理需要被刷新到磁盘的脏页。

在配置Buffer Pool时，核心的参数如下：

可以通过调整innodb_buffer_pool_size参数来配置Buffer Pool的大小。
此外，innodb_buffer_pool_instances参数用于将InnoDB Buffer Pool分割成多个实例，每个实例拥有自己的LRU算法和hash索引，这有助于提高并发性能。
innodb_buffer_pool_chunk_size参数用于设置每个Buffer Pool实例的块大小，这有助于减少内存碎片。

监控Buffer Pool的使用情况对于性能调优至关重要。

可以使用SHOW STATUS命令查看Buffer Pool的使用情况，如Innodb_buffer_pool_read_requests表示从Buffer Pool中读取的次数，Innodb_buffer_pool_reads表示直接从磁盘读取的次数等。

通过这些监控信息，可以了解Buffer Pool的使用情况，从而进行针对性的优化。

总结来说，Buffer Pool是MySQL中一个重要的组件，通过合理地加载和淘汰数据页，Buffer Pool能够有效地减少磁盘IO操作，提高查询效率。同时，监控和维护Buffer Pool的运行情况也是优化数据库性能的关键步骤。

redo log 实现 ACID 中的持久性

redo log 的意思是重做日志，redo log 是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎。

什么是 redo log ？

redo log 保证了事务的持久性，当我们对缓冲池中的数据页进行了修改（修改后变成脏页），但是脏页数据是存在于Buffer Pool缓冲池，缓冲池占用的就是操作系统内存空间，所以数据页本质也是存在内存中的，内存有个特点就是断电即失。

所以当脏页数据还没有刷入磁盘，此时数据库服务发生宕机，那么脏页数据就会因为宕机而丢失，如何恢复这些没刷盘得脏页数据呢？

这时候redo log就派上了用场.以一个更新事务为例， redo log 流转过程，如下图所示：

第 1 步：先将原始数据从磁盘中读入buffer pool 内存中来，修改数据的内存拷贝，数据变成脏页
第 2 步：生成一条重做日志并写入 redo log buffer ，记录的是数据被修改后的值
第 3 步：当事务 commit 时，将 redo log buffer 中的内容刷新到 redo log file ，对 redo log file 采用追加写的方式
第 4 步：定期将内存中修改的数据刷新到磁盘中

redo log通过WAL（Write-Ahead Logging）来进行故障恢复（crash-safe），所谓WAL简单来说就是先写日志，后写磁盘。

当缓存页被修改后（变成脏页），我们就将本次操作写入到redo log buffer中，当事务Commit时，就先将redo log buffer中记录通过后台线程刷到磁盘中（事务提交是redo log默认刷盘时机），此时脏页还没有刷入磁盘，但是，只要redo log成功刷盘，就可以认为本次的修改操作完成了，因为就算发生了故障导致脏页数据丢失，也可以通过磁盘redo log恢复，所以，redo log 和 undo log 配合起来的作用就是：

事务提交前崩溃，通过 undo log 回滚
事务事务提交后崩溃，通过 redo log 恢复事务

redo log buffer的进行刷盘

redo log记录先写入到redo log buffer中，什么时候刷盘？也就是说, 最后还是从 redo log buffer 同步到硬盘中，那么redo log buffer何时进行刷盘操作呢？

主要是以下几种情况，默认情况下，redo log在事务提交时，就会进行一次redo log刷盘：

Master Thread每秒刷盘一次
redo log buffer 剩余空间 < 1/2，刷盘一次

通过innodb_flush_log_at_trx_commit参数控制


  0：有事务提交的情况下，每秒刷盘一次
  1：每次提交事务，刷盘一次（默认值，性能差）
  2：每次提交事务，把日志记录放到OS内核缓冲区，刷盘时机交给OS（性能好，低可靠）

再啰嗦一下这个核心的innodb_flush_log_at_trx_commit 参数，这个超参数有3个值，支持三种策略：

：设置为 0 的时候，表示每次事务提交时不进行刷盘操作。有事务提交的情况下，每秒刷盘一次。（性能好，低可靠）
1 ：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作（默认值）。（高可靠，低性能）
2 ：设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache，刷盘时机交给OS

也就是说, 一个没有提交事务的redo log 记录, 也可能会刷盘.因为在事务执行过程 redo log 记录是会写入redo log buffer 中, 这些redo log 记录会被后台线程刷盘

不同刷盘策略的流程演示

innodb_flush_log_at_trx_commit=1 流程图

因为在事务执行过程 redo log 记录是会写入redo log buffer 中, 这些redo log 记录会被后台线程刷盘

innodb_flush_log_at_trx_commit=1（默认值）：

含义：每当一个事务提交时，InnoDB 会将日志缓冲区的内容写入文件系统缓存，并立即将该日志从文件系统缓存刷新到磁盘（fsync）。
持久性：提供最强的持久性，保证每次事务提交后日志都已经持久化到磁盘。如果数据库崩溃，最多丢失未提交的事务。
性能：性能最差，因为每次事务提交都会执行磁盘写操作，尤其在高并发写入的场景下，可能会成为性能瓶颈。

注意，这个不仅仅写入page cache，而是写入磁盘的 redo.file

所以，为1时, 只要事务提交成功, redo log 记录就一定在硬盘里, 不会有任何数据丢失。数据绝对不会丢失, 但是效率最差的

如果事务执行期间MySQL 挂了或宕机, 这部分日志刷盘了，但是事务并没有提交, 可以使用redo log 进行重做，所以日志丢了也不会有损失. 可以保证ACID的D(持久性),

innodb_flush_log_at_trx_commit=2 流程图

设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache，刷盘时机交给OS（性能好，低可靠）

innodb_flush_log_at_trx_commit=2：

含义：在每次事务提交时，InnoDB 会将日志缓冲区的内容写入操作系统的文件系统缓存page cache（不执行 fsync），由操作系统负责将日志写入磁盘。
持久性：如果数据库崩溃，而如果服务器没有崩溃，数据不会丢失。如果服务器也崩溃，page cache 默认是缓存5s的数据，最多丢失最近5 秒内的事务数据，但与 innodb_flush_log_at_trx_commit=0 不同的是，日志至少会被写入文件系统缓存，这在某些情况下可以提供更好的安全性。
性能：性能较高，因为事务提交时只需要将日志写入内存中的文件系统缓存，而不需要立即进行磁盘 I/O。

性能：性能较高，因为事务提交时只需要将日志写入内存中的文件系统缓存，而不需要立即进行磁盘 I/O。

如果仅仅只是MySQL挂了不会有任何数据丢失, 但是操作系统宕机可能会有5秒数据的丢失, 这种情况下无法满足ACID中的D.但数值2 肯定是效率更高的

Page Cache 刷盘的默认周期

在操作系统中，Page Cache 是用于缓存文件系统数据的内存区域，用来减少频繁的磁盘 I/O 操作。当数据写入文件时，实际上首先被写入到 Page Cache，而不立即写入到磁盘。

刷盘（即将 Page Cache 中的数据写入磁盘）的时机受多种因素影响，操作系统会定期将 Page Cache 中的数据刷新到磁盘。

Page Cache 刷盘周期由操作系统的默认行为和配置参数控制，一般来说：

操作系统定期启动后台线程（如 Linux 的 pdflush 或 flush 线程）将脏页（dirty pages，即修改过但尚未刷到磁盘的页）从 Page Cache 刷写到磁盘。
默认情况下，Linux 系统大约每 5 秒启动一次刷盘操作。该周期由内核参数 dirty_writeback_centisecs 控制，默认值为 500，即 5 秒。

innodb_flush_log_at_trx_commit=0 流程图

设置为 0 的时候，表示每次事务提交时不进行刷盘操作。有事务提交的情况下，每秒刷盘一次

innodb_flush_log_at_trx_commit=0：

含义：InnoDB 每秒将日志缓存（Redo Log Buffer）刷新到磁盘，但不会在每次事务提交时刷新。即使事务提交，也只是将日志写到内存中的日志缓冲区，1 秒后由后台线程将其写入磁盘。
持久性：如果数据库崩溃，可能会丢失最近 1 秒内的事务数据。
性能：性能最高，因为写磁盘的频率最小，但持久性最弱。

如何选择 redo log 参数刷盘？

innodb_flush_log_at_trx_commit 设置为不同值时，分别是什么时候将 redo log 写入磁盘？

参数0：

事务提交时不刷盘。靠后台线程每秒刷盘一次。

后台线程把缓存在 redo log buffer 中的 redo log ，通过调用 write() 写到操作系统的 Page Cache，然后调用 fsync() 持久化到磁盘。

所以参数为 0 的策略，MySQL 进程的崩溃会导致上一秒钟所有事务数据的丢失

参数1：

只要事务提交成功，redo log记录就一定在硬盘里，不会有任何数据丢失。

如果事务执行期间MySQL挂了或宕机，日志丢了，但是事务也不可能成功提交，所以日志丢了也不会有损失！！

参数2：

事务提交成功，redo log buffer中的内容都会写入文件系统缓存（page cache），此时没有刷盘。

操作系统负责把 Page Cache 里的 redo log 持久化到磁盘。如果仅仅只是MySQL挂了，但是服务器没挂，不会有任何数据丢失。只有服务器崩溃的情况下，上5秒钟所有事务数据才可能丢失。

对比如下：

数据安全性：参数 1 > 参数 2 > 参数 0
写入性能：参数 0 > 参数 2> 参数 1

参数 1 最安全，在一些对数据安全性要求比较高的场景中，显然 innodb_flush_log_at_trx_commit 参数需要设置为 1。

在一些可以容忍数据库崩溃时丢失 1s 数据的场景中，我们可以将该值设置为 0，这样可以明显地减少日志同步到磁盘的 I/O 操作。

安全性和性能折中的方案就是参数 2，虽然参数 2 没有参数 0 的性能高，但是数据安全性方面比参数 0 强，因为参数 2 只要操作系统不宕机，即使数据库崩溃了，也不会丢失数据，同时性能方便比参数 1 高。

Checkpoint 机制

InnoDB的 Checkpoint 机制是数据库的一种自动保存技术，用于定期将内存中的脏数据（修改过的数据）同步到磁盘，确保数据一致性和持久性。

它的核心作用包括：

数据安全：定时将缓冲池（Buffer Pool）里的脏页写入磁盘，防止系统崩溃时数据丢失。
性能优化：通过批量刷盘减少频繁I/O操作，提升数据库整体性能。
日志管理：与Redo Log配合，在崩溃恢复时快速定位需要重做的数据位置。

Checkpoint 触发时机包括：日志文件写满、系统空闲时或后台线程定期执行。它类似于游戏中的“自动存档”，既保证数据安全，又避免实时刷盘的开销。

crash-safe 机制

Crash-safe 是指数据库系统在发生崩溃（例如服务器宕机、硬件故障、操作系统崩溃等）后，能够通过适当的恢复机制，确保数据的一致性和持久性，并保证已提交的事务不会丢失，未提交的事务会被回滚。这种机制的核心是事务的原子性、一致性、隔离性和持久性（ACID）特性，尤其是持久性。

Innodb 引擎有 crash-safe 能力，即事务提交过程中任何阶段，MySQL 宕机重启后都能保证事务的完整性，已提交的数据不会丢失。

这种能力是通过redo log保证的，MySQL 宕机重启，系统将自动检查 redo log，将修改还未写入磁盘的数据从 redo log 恢复到 MySQL 中。

binlog 实现 ACID 中的一致性

什么是binlog ？

binlog是binary log的缩写，即二进制日志。

binlog 是作为mysql操作记录归档的日志，这个日志记录了所有对数据库的数据、表结构、索引等等变更的操作。也就是说只要是对数据库有变更的操作都会记录到binlog里面来，可以把数据库的数据当成我们银行账户里的余额，而binlog就相当于我们银行卡的流水。账户余额只是一个结果，至于这个结果怎么来的，那就必须得看流水了。而同样在mysql里我们就是通过binlog来归档、验证、恢复、同步数据。

bin log该日志主要有两个功能：

备份恢复，实现崩溃一致性（Crash Consistency）
主从复制，实现主从复制的一致性

每次事务进行提交时，都会将增、删、改操作以追加的方式记录到bin log文件中。为什么该日志具备备份恢复功能，就是根据bin log日志中记录的二进制操作记录恢复即可；主从复制常用于MySQL主从集群搭建，MySQL从节点通过监听主节点bin log日志进行同步即可。

事务提交，以两阶段提交为例：

prepare 阶段：将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘；
commit 阶段：将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件）；

binlog 的二进制日志格式

binlog应该说是Mysql里最核心的日志，它记录了除了查询语句(select、show)之外的所有的 DDL 和 DML 语句，也就意味着我们基本上所有对数据库的操作变更都会记录到binlog里面。

实际上，binlog以事件形式记录，不仅记录了操作的语句，同时还记录了语句所执行的消耗的时间。

关于bin log的二进制日志格式，有以下三种类型：

STATEMENT：增删改SQL语句，存储空间要求小
ROW：记录表行的更改情况，存储空间要求大
MIXED：混合场景，默认情况下STATEMENT，少数情况下ROW

msql 5.6默认 statement 类型的bin log，语句模式原封不动的记录当前DML，可读性高，用户能看懂。msql 5.7默认 ROW类型的bin log，记录数据行的变化(用户看不懂，需要工具分析)。

statement 与ROW 模式的对比

STATEMENT：可读性较高，日志量少，但是不够严谨
ROW ：可读性很低，日志量大，足够严谨

binlog 刷盘时机

事务执行过程中，先把日志写到 binlog cache（Server 层的 cache），事务提交的时候，再把 binlog cache 写到 binlog 文件中。

因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。

提示

参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。

如果超过了这个参数规定的大小，就要暂存到磁盘（Swap）。

什么时候 binlog cache 会写到 binlog 文件？

在事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 文件中，并清空 binlog cache。

虽然每个线程有自己 binlog cache，但是最终都写到同一个 binlog 文件：

图中的 write，指的就是指把日志写入到 page cache ，但是并没有把数据持久化到磁盘，因为数据还缓存在文件系统的 page cache 里，write 的写入速度还是比较快的，因为不涉及磁盘 I/O。
图中的 fsync，才是将数据持久化到磁盘的操作，这里就会涉及磁盘 I/O，所以频繁的 fsync 会导致磁盘的 I/O 升高。

MySQL提供一个 sync_binlog 参数来控制数据库的 binlog 刷到磁盘上的频率：

sync_binlog = 0 的时候，表示每次提交事务都只 write，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘；
sync_binlog = 1 的时候，表示每次提交事务都会 write，然后马上执行 fsync；
sync_binlog =N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

在MySQL中系统默认的设置是 sync_binlog = 0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦主机【指的是操作系统】发生异常重启，还没持久化到磁盘的数据就会丢失。

而当 sync_binlog 设置为 1 的时候，是最安全但是性能损耗最大的设置。因为当设置为 1 的时候，即使主机发生异常重启，最多丢失一个事务的 binlog，而已经持久化到磁盘的数据就不会有影响，不过就是对写入性能影响太大。

如果能容许少量事务的 binlog 日志丢失的风险，为了提高写入的性能，一般会 sync_binlog 设置为 100~1000 中的某个数值。

在MySQL中，binlog（二进制日志）是在事务的提交阶段被刷入磁盘的。当一个事务执行并准备提交时，MySQL先生成binlog日志，这些日志记录了事务所做的所有修改。

注意

在事务的两阶段提交过程中，首先会将binlog日志写入到磁盘，然后才提交事务，确保了数据的一致性和持久性。

bin log属于MySQL体系架构的Server层，事务操作进行过程中，会把日志信息先记录到bin log cache中，等到事务提交后会将bin log cache中记录刷盘到bin log 文件中。

这里需要注意，无论一个事务中包含了多少个增删改操作，都要一次性写入，不可拆分。

总的来说，binlog在事务的提交阶段被刷入磁盘，这是MySQL保证数据一致性和支持数据恢复的重要机制。通过合理配置sync_binlog参数，可以在数据安全性和性能之间取得平衡。

binlog 与两阶段提交

bin log刷盘后，redo log还未来得及刷盘，数据库宕机，数据不一致。
redo log刷盘后，bin log还未来得及刷盘，数据库宕机，数据不一致。

说到这里大概知道两阶段提交其实就是为了防止这两个日志不一致，它将事务Commit操作分为两个阶段：

Prepare：XID（内部 XA 事务的 ID）写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log 持久化到磁盘（默认redo log刷盘策略）；
Commit：XID 写入到 bin log，马上将 bin log 刷盘（sync_binlog = 1），接着调用引擎的提交事务接口，将 redo log 状态设置为 commit，只要 bin log 写磁盘成功，就算 redo log 的状态还是 prepare 也没有关系，一样会被认为事务已经执行成功。

这个两阶段提交机制保证了 binlog 和 redo log 的协调一致。

总结起来说就是如果一个事务在prepare阶段中落盘成功，并在MySQL Server层中的binlog也写入成功，那这个事务必定commit成功。

通过两阶段提交协议，MySQL 在事务提交过程中可以确保在发生崩溃时：

事务的一致性：当事务提交后，binlog 和 redo log 保持一致。如果事务提交成功，则 binlog 和 redo log 都会反映出相同的操作；如果失败，则事务会被回滚，binlog 也不会记录到这个事务。
数据恢复：当 MySQL 恢复时，系统可以根据 redo log 和 binlog 的状态，确保事务的一致性，避免部分提交或丢失。

binlog 日志文件结构

数据恢复：当 MySQL 恢复时，系统可以根据 redo log 和 binlog 的状态，确保事务的一致性，避免部分提交或丢失。

日志文件是以.NNNNNN结尾的。索引文件以.index结尾。所有的文件有个相同的前缀。默认的binlog文件的前缀名是“HOSTNAME-bin”。

text
...
HOSTNAME-bin.0000101
HOSTNAME-bin.0000102
HOSTNAME-bin.0000103
...
HOSTNAME-bin.index
...

主从复制一致性与中继日志 relay-log

主从复制的一致性是 MySQL 等数据库系统在主从复制架构中的核心概念。

relay-log 的作用是实现主从复制的一致性，对于确保高可用性和数据同步至关重要。

在 MySQL 主从复制中，从库（slave）会从主库（master）读取其执行的二进制日志（Binlog），并将这些日志写入自己的中继日志（Relay Log）。

Relay Log 是从库用于保存从主库传输过来的数据变更操作的日志。

mysql主从复制流程

mysql主从复制流程的过程大概是这个样子：

master 写入binlog：master 服务器会将 SQL 记录通过多 dump 线程写入到 binary log 中。
连接master：slave 服务器连接master。开启一个 io thread 线程向服务器发送请求，向 master 服务器请求 binary log。
推送binlog：master 服务器在接收到请求之后，根据偏移量将新的 binary log 发送给 slave 服务器。
写入中继日志：slave 服务器收到新的 binary log 之后，写入到自身的 relay log 中，这就是所谓的中继日志。
执行中继日志：slave 服务器，单独开启一个 sql thread 读取 relay log 之后，写入到自身数据中。

中继日志 relay-log 的文件结构

relay-log中继日志是连接master和slave的核心，我们来深入了解一下它的结构和使用。

relay 日志文件的命名类似，只不过文件的前缀是“HOSTNAME-relay”。

text
HOSTNAME-relay.0000101
HOSTNAME-relay.0000102
HOSTNAME-relay.0000103
...
HOSTNAME-relay.index

relay-log的结构和binlog非常相似，只不过他多了一个master.info和relay-log.info的文件。

master.info记录了上一次读取到master同步过来的binlog的位置，以及连接master和启动复制必须的所有信息。
relay-log.info记录了文件复制的进度，下一个事件从什么位置开始，由sql线程负责更新。

中继日志 relay-log 的回放流程

知道binlog和relay-log的结构之后，我们重新梳理一下整个链路的流程，这里我们假定master.info和relay-log.info都是存在的情况：

Master收到客户端请求语句，在语句结束之前向二进制日志写入一条记录，可能包含多个事件。
此时，一个Slave连接到Master，Master的dump线程从binlog读取日志并发送到Slave的IO线程。
IO线程从master.info读取到上一次写入的最后的位置。
IO线程写入日志到relay-log中继日志，如果超过指定的relay-log大小，写入轮换事件，创建一个新的relay-log。
更新master.info的最后位置
SQL线程从relay-log.info读取进上一次读取的位置
SQL线程读取日志事件
在数据库中执行sql
更新relay-log.info的最后位置
Slave记录自己的binlog日志

但是在这里IO和SQL线程有会产生重复事件的问题，举一个场景：

先记录中继日志，然后更新master.info位置
此时服务器崩溃，写入master.info失败
服务器恢复，再次同步从master.info获取到的是上一次的位置，会导致事件重复执行

既然会有这个问题还为什么要这样做呢？假设反过来，先更新master.info再记录中继日志，这样带来的问题就是丢失数据了。而mysql认为丢失比重复更严重，所以要先刷新日志，保大还是保小mysql帮你做了决定。

Binlog 与两大数据一致性

1. 崩溃一致性（Crash Consistency）

Binlog 记录事务提交的 SQL 操作，通常与 Redo Log 协同工作，实现崩溃一致性（Crash Consistency）

MySQL 的 InnoDB 存储引擎和 MySQL Server 层的 binlog 通过两阶段提交来保证事务的一致性，确保在系统崩溃或主从复制时，事务的逻辑和物理数据保持一致。具体流程如下：

第一阶段（Prepare 阶段）：

当一个事务执行完所有操作并准备提交时，InnoDB 首先会将修改操作写入到 redo log，并将其标记为 prepare 状态。这意味着事务的物理修改操作已经记录到了 redo log 中，但事务尚未真正提交。
此时，如果系统崩溃，事务可以通过 undo log 回滚，保持一致性。

第二阶段（Commit 阶段）：

写入 binlog：将 binlog 刷新到磁盘。这时，binlog 确保了主从库在复制时具有相同的操作逻辑。
提交 redo log：InnoDB 会将 redo log 的状态修改为 commit，表示事务已经正式提交。

这个两阶段提交机制保证了 binlog 和 redo log 的协调一致。

总结起来说就是如果一个事务在prepare阶段中落盘成功，并在MySQL Server层中的binlog也写入成功，那这个事务必定commit成功。

通过两阶段提交协议，MySQL 在事务提交过程中可以确保在发生崩溃时：

事务的一致性：当事务提交后，binlog 和 redo log 保持一致。如果事务提交成功，则 binlog 和 redo log 都会反映出相同的操作；如果失败，则事务会被回滚，binlog 也不会记录到这个事务。
数据恢复：当 MySQL 恢复时，系统可以根据 redo log 和 binlog 的状态，确保事务的一致性，避免部分提交或丢失。

2. 主从复制的一致性

在主从复制的场景下，binlog 的一致性尤为重要。

主库提交事务时会先写入 binlog
从库则通过读取主库的 binlog 重新执行相同的操作。

MySQL 确保：

只有当事务在主库提交成功（即 binlog 和 redo log 一致）后，主库才会将 binlog 传递给从库。
从库应用 binlog 以确保主库和从库的数据保持一致。

通过这种机制，MySQL 保证了主从复制环境中的一致性，不会出现主库和从库不同步的情况。

崩溃恢复时的判断规则

根据两阶段提交，崩溃恢复时的判断规则是这样的：

如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交
如果 redo log 里面的事务处于 prepare 状态，则判断对应的事务 binlog 是否存在并完整
```
  a. 如果 binlog 存在并完整，则提交事务；
  b. 否则，回滚事务。
```
commit阶段写入 binog 之前发生了崩溃，事务回滚 - commit阶段写入 binlog 之后发生了崩溃，事务恢复

总结

Mysql 逻辑架构

Mysql 整体架构上分为四层，分别是：连接层、服务层、存储引擎层、文件系统层。其中最核心的就是服务层和存储引擎层。

当一个SQL执行时，到服务层回显经过 解释器 对执行的SQL进行语法的解析验证，通过后会交给 优化器 对SQL进行优化，例如SQL语句的重写或者是索引的命中优化，最终会生成一个最优的执行计划，交给 执行器，执行器会按照优化器生成的执行计划，逐步执行查询，访问数据并返回结果。

Buffer Pool

Buffer Pool 是 InnoDB 中的缓存池，内部包含数据页、索引页、undo页、锁信息等等。

Buffer Pool（缓冲池）是MySQL数据库中InnoDB存储引擎的一个重要组成部分，它位于内存中，用于缓存数据库中经常被访问的数据页和索引页，以减少对磁盘的I/O操作，从而提高数据库的读写性能。

Buffer Pool通过缓存热点数据和索引，减少了磁盘I/O操作，大大提高了数据库的性能。所以，Buffer Pool的大小对数据库性能有很大的影响。

当读取数据时，就会先从缓存中查看是否数据的页（page）存在，不存在的话去磁盘上检索，查到后缓存到innodb_buffer_pool中的缓存页、数据页中。
同理，插入、修改、删除也是先操作缓存里数据，将操作数据的所在页更新为脏页，之后再通过 Checkpoint 刷盘机制以一定频率更新到磁盘上。

既然是缓存，就避免不了内存的淘汰，Buffer Pool 采用最近最少使用的 LRU 算法进行内存淘汰。

undo log

回滚日志，核心就是回滚事务与MVCC中的快照版本控制，保证了原子性与一致性。

在事务执行的过程中操作任何数据之前先将数据备份到undolog中。事务失败时可根据undo log进行回滚。用来保证事务的一致性。还可以用来实现多版本并发控制MVCC。

多个事务的 undo-log 日志副本 (数据快照)，组成了一个 副本链，由回滚指针指向了上一个版本的事务数据，其事务数据的可见性由 ReadView 来保证。

redo log

重做日志，在事务执行的过程中不断记录事务操作的变化，记录的是数据页的物理修改。恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。用来保证事务的持久性。

当我们对缓冲池中的数据页进行了修改，会将修改后的数据页变成脏页，生成一条重做日志并写入 redo log buffer，当事务 commit 时，将 redo log buffer 中的内容刷新到 redo log file ，对 redo log file 采用追加写的方式，定期将内存中修改的数据将脏页数据刷入磁盘。如果此时数据库服务发生宕机，那么脏页数据就会因为宕机而丢失，但是 redo log 默认刷盘时机是在事务提交，只要redo log成功刷盘，就可以认为本次的修改操作完成了，这就是 redo log 能恢复这些没刷盘得脏页数据原因。

binlog

二进制日志，在事务提交后进行记录。用来备份，通过主从复制来实现数据同步、读写分离和数据库的崩溃恢复，保证了数据的一致性。

事务的 Prepare、Commit 两阶段提交：

Prepare：XID（内部 XA 事务的 ID）写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log 持久化到磁盘（默认redo log刷盘策略）；
Commit：XID 写入到 binlog，马上将 binlog 刷盘（sync_binlog = 1），接着调用引擎的提交事务接口，将 redo log 状态设置为 commit，只要 binlog 写磁盘成功，就算 redo log 的状态还是 prepare 也没有关系，一样会被认为事务已经执行成功。