MySQL日志篇

一共有三种日志

• undo log:回滚日志，是InnoDB存储引擎层生成的日志，实现了事务的原子性，主要用于事务回滚和MVCC。
• redo log:重做日志，也是InnoDB层，实现事务的持久性，主要用于断电等故障恢复。
• binlog:归档日志，是server层的日志，主要用于数据备份和主从复制。

这三种日志是如何工作的？

为什么需要undo log？

我们做增删改的时候，即使不输入begin和commit来进行事务，MySQL也会隐式开启事务来执行增删改。

那么考虑一个问题，一个事务在执行过程中，在还没有提交事务之前，MySQL崩溃了咋办？怎么回滚到事务之前的数据呢？

如果我们在每次事务执行过程中都记录下回滚时需要的信息到一个日志里，这样中途崩溃就可以通过日志回滚。

实现这个机制的就是undo log，它保证了事务的ACID特性中的原子性Atomicity

• 插入一条记录时，记录主键值，回滚时只需要把主键值对应的记录删掉就行了。
• 删除一条记录时，把记录中的内容都记下来，之后回滚时再把这些内容重新插入到表中。
• 更细一条记录时，记录下旧的值，回滚时写回旧的值。

针对delete和update操作有特殊处理：

• delete操作实际上不会立即删除，而是把delete对象打上delete的flag，删除操作是purge线程完成的.
• update分成两种情况，updated列是否是主键
• • 如果不是主键，在undo log 中直接反向记录如何update，update是直接进行的。
• • 如果是主键，update分成两部分执行，先删除该行，再插入一行目标行。

一条记录的每一次更新产生的undo log都有一个roll_pointer指针和一个trx_id事务id：

• 通过trx_id可以知道这个记录是被哪个事务修改的。
• 通过roll_pointer指针把undo log 串成链表，这个链表称为版本链。

• 在读提交隔离级别，每个select都会生成一个新的Read View，意味着事务期间多次读取一个数据，前后可能不一致。
• 可重复读，是在事务启动时生成一个Read View，整个事务其间都是这个Read View。

因此，undo log 的两大作用：

• 实现事务回滚，保障事务的原子性。
• 实现MVCC的关键因素之一。

那么undo log 是如何刷盘(持久化到磁盘)的？ undo log和数据的刷盘策略是一样的，都需要通过redo log保证持久化。

buffer pool中有undo页，对undo页的修改都会记录到redo log。redo log 会每秒刷盘，提交事务时也会刷盘，数据页和undo log页都是靠这个机制保证持久化的。

为什么需要buffer pool？

M有SQL的数据是存在磁盘中的，更新记录的时候，要从磁盘读取该记录，然后内存中修改记录。修改完后的记录是直接写回到磁盘，还是缓存起来？

当然是缓存，下次查询语句命中。直接读取缓存中的记录，就不需要从磁盘获取数据了。

为此，InnoDB设计了缓冲池(Buffer Pool)，提高数据库的读写性能。

有了buffer pool之后，读取数据，数据在缓冲池中就直接读取缓冲池里的。 修改数据的时候，数据在缓冲池中，就修改缓冲池内的数据，并标记为脏页。为了减少磁盘IO不会把脏页立即写入磁盘，后续由后台线程选择合适时机把脏页写入磁盘。

Buffer Pool 缓存什么？

InnoDB读取数据按页划分，buffer pool也是一样。

MySQL启动的时候，InnoDB会给buffer pool申请连续的内存空间，按照默认的16KB大小划分一个个的页。

Buffer Pool除了缓存索引页和数据页、还包括undo页、插入缓存、自适应哈希索引、锁信息等。

Undo 页是记录什么？

开启事务后，InnoDB更新记录前，首先要记录相应的undo log。如果是更新操作，记录字段的旧值，undo log 会写入Buffer Pool中的undo页面。

查询一条记录，就只需要缓冲一条记录吗？

不是，查询一条记录，InnoDB会把整页的数据加载到缓冲池中，再通过页牡蛎定位具体记录。

为什么需要redo log？

上文说道，利用Buffer Pool提高了读写效率，但是它是基于内存的，断电了数据就丢失了。

为了防止这个问题，有记录需要更新的时候，InnoDB先更新内存，标记为脏页，然后把本次对页的修改以redo log的形式记录下来，这个时候更新就算完成了。

后续InnoDB引擎会在适当的时候由后台线程把缓冲池里面的脏页刷新到磁盘里，这个就是WAL(Write-Ahead Logging)技术。

WAL指的是，MySQL的写操作不是立即写在磁盘上，而是先写日志，在合适的时候再写到磁盘上。

什么是redo log？

redo log 是物理日志，记录某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据 页 ZZZ 偏移量的地方做了AAA 更新。

在事务提交时，只需要先将redo log 持久化到磁盘，可以不需要等到将缓存在buffer pool里的脏页数据持久化到磁盘。

假如系统崩溃了，buffer pool里的脏页还没刷盘也没关系，因为redo log 持久化了，MySQL重启之后根据redo log的内容来恢复数据。

redo log 和undo log的区别？

• redo log记录了这次事务修改后的数据状态，记录的是更新之后的值，主要用于事务崩溃回复，保证事务的持久性。
• undo log记录了此次事务修改前的数据状态，记录更新前的值，主要用于事务回滚，保证事务的原子性。

事务提交之后发生了宕机崩溃，MySQL重启之后会通过redo log恢复事务。

所以有了redo log之后，再通过WAL技术，InnoDB保证数据库发生异常重启，已提交的记录都不会丢失，这个能力称为crash-safe(崩溃恢复) 。可以看出，redo log保证了事务四大特性中的持久性。

redo log要写磁盘，数据本身也要写磁盘，多此一举？

写入redo log是追加操作，磁盘是顺序写，写入数据要先找到写入位置，是随机写。

而磁盘中，顺序写比随机写高效得多，redo log写入的磁盘开销更小。

至此，为什么要redo log？有两个答案。

• 实现事务的持久性，让MySQL有崩溃恢复的能力。
• 把随机写变成顺序写，提高MySQL写入磁盘的性能。

产生的redo log是直接写入磁盘的吗？

不是的，执行事务过程中如果直接把redo log写入磁盘也要大量磁盘io。实际上是先写入到 redo log buffer中。

redo log什么时候刷盘？

redo log是先缓存在内存中，什么时候刷新到磁盘呢？主要有以下几个时机

• MySQL正常关闭时
• redo log buffer记录写入量大于它最大内存空间的一半时，会触发落盘
• InnoDB后台线程每隔一秒刷盘一次(可以由innodb_flush_log_at_trx_commit参数控制)

innodb_flush_log_at_trx_commit参数

默认值是1. 默认时，每次事务提交，都会把redo log buffer里的redo log直接持久化到磁盘。 参数为0时，事务提交不会触发redo log buffer写入磁盘的操作。 参数为2时，表示每次事务提交时，把redo log buffer里的redo log写入到redo log文件(不意味着写入磁盘)。操作系统的文件系统中有个Page Cache，专门用来缓存文件数据。所以写入redo log 文件意味着写入到了操作系统的文件缓存。

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

redo log文件写满了怎么办？

默认情况下InnoDB有一个重做日志文件组，由两个redo log文件组成。叫ib_logfile0和ib_logfile1。 它是以循环写的方式工作，从头写，写到末尾回到开头，环形。

所以是先写ib_logfile0，写满了就切换成1，再写满切换成0。

如果 write pos 追上了 checkpoint，就意味着 redo log 文件满了，这时 MySQL不 能再执行新的更新操作，也就是说 MySQL 会被阻塞 因此所以针对并发量大的系 统，适当设置 redo log 的文件大小非常重要) ，此时会停下来将 Buffer Pool 中的 脏页刷新到磁盘中，然后标记redo log 哪些记录可以被擦除，接着对旧的 redo log 记录进行擦除，等擦除完旧记录腾出了空间，checkpoint 就会往后移动(图中 顺时针)，然后 MySQL恢复正常运行，继续执行新的更新操作。

所以，一次check point的过程，就是脏页刷新到磁盘中变成干净页，然后标记redo log 哪些记录可以被覆盖的过程。

为什么需要binlog？

前面介绍的redo log和undo log都是innodb存储引擎生成的。

MySQL在完成一条更新操作后，server层还会生成一条binlog，等之后事务提交的时候，会把事务执行过程中产生的所有binlog统一写入binlog文件。

binlog文件是记录了所有数据库表结构变更和表数据修改的日志，不记录查询类操作，如select和show。

为什么有了binlog还要有redo log？

跟MySQL的时间线有关系。

最开始MySQL没有InnoDB引擎，自带的是MyISAM，但是MyISAM没有crash-safe能力，binlog日志只能用于归档。

而 InnoDB 是另一个公司以插件形式引入 MySQL的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用 redo log 来实现 crash-safe 能力。

redo log 和binlog 有什么区别？

这两个日志有四个区别。

• 适用对象不同 binlog是MySQL server层的日志，不限存储引擎，redo log是InnoDB存储引擎实现的日志。

• 文件格式不同 binlog有三种格式类型，默认是STATEMENT、ROW、MIXED。区别如下：

  • STATEMENT: 每一条修改数据的SQL都会记录到binlog，相当于逻辑操作，这种格式可以称为逻辑日志。主从复制中slave端再根据SQL语句重现。但是STATEMENT有动态函数的问题，比如用了uuid或者now这些函数，在主库上执行的结果和从库不同，会导致复制的数据不一致。
  • ROW:记录行数据最终被修改成什么样。没有动态行数问题，但缺点是每行数据的变化结果都会被记录，批量update，就会有批量记录，使得binlog文件过大。而如果使用STATEMENT，只有一个update语句而已。
  • MIXED:包含了STATEMENT和ROW模式，它会根据不同的情况自动使用ROW模式或STATEMENT模式。

• redo log是物理日志，记录在某个数据页做了什么修改。

• 写入方式不同

  • binlog是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。
  • redo log是循环写，日志空间大小是固定，全部写满从头开始，保存未被刷入磁盘的脏页日志。

• 用途不同

  • binlog用于备份恢复、主从复制；
  • redo log用于掉电等故障恢复。

如果不小心整个数据库的数据被删除了，能使用redo log文件恢复数据吗？

不可以，只能使用binlog文件恢复。 因为redo log文件是循环写，会边写边擦除日志，只记录未被刷入磁盘的物理日志，已经刷入磁盘的数据都会从redo log文件里面擦除。 binlog文件保存的是全量日志，理论上只要记录了都可以恢复，所以不小心整个数据库的数据被删除了，得用binlog文件恢复数据。

主从复制是怎么实现的？

MySQL的主从复制依赖于binlog，也就是记录MySQL上的所有变化并以二进制保存在磁盘上。复制的过程就是把binlog中的数据从主库传输到从库上。

这个过程一般是异步的，也就是主库上执行事务操作的线程不会等待复制binlog的线程同步完成。

MySQL集群的主从复制梳理成三个阶段：

• 写入binlog：主库写binlog日志，提交事务，更新本地存储数据
• 同步binlog：把binlog复制到所有从库上，每个从库把binlog写道暂存日志中
• 回放binlog：回放binlog，并更新存储引擎的数据。

具体过程：

• MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事 务，更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响 应。
• 从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制 成功”的响应。
• 从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新存储引擎中的数据，最终实现主从的数据一致性。

从库是不是越多越好？

不是的。从库数量增加。从库跟主库的I/O线程也比较多，对主库资源消耗比较高，同时还受限于主库的网络带宽。

MySQL主从复制还有哪些模型？

• 同步复制:MySQL主库提交事务的线程要等待所有从库的复制成功响应，才返 回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个:一是性能 很差，因为要复制到所有节点才返回响应;二是可用性也很差，主库和所有从库 任何一个数据库出问题，都会影响业务。
• 异步复制(默认模型):MySQL主库提交事务的线程并不会等待 binlog 同步到 各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。
• 半同步复制:MySQL5.7 版本之后增加的一种复制方式，介于两者之间，事务线 程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如 一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以 返回给客户端。这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即 使出现主库容机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

binlog什么时候刷盘？

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

一个事务的binlog是不能被拆开的，必须保证一次性写入。因为MySQL有一个线程同时只能有一个事务在执行的设定。如果一个事务的binlog被拆开、备库执行的时候就会被当作多个事务分段执行，破坏了原子性。

MySQL给每个线程分配内存来缓存binlog、参数binlog_cache_size控制单个线程内binlog_cache占内存的大小。超过这个参数规定的大小，就要暂存到磁盘。

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

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

小结，update语句的执行过程

当优化器分析出成本最小的执行计划后，执行器就按照执行计划开始进行更新操作。

假如查询UPDATE t_user SET name = 'xiaolin' WHERE id = 1;

1. 执行器负责具体执行，会调用存储引擎的接口，通过主键索引搜索Id=1的这行记录。 如果id=1的行所在的数据页本来就在buffer pool中，就直接返回给执行器更新。否则就把数据页从磁盘读取到buffer pool中，返回记录给执行器。

2. 执行器得到聚簇索引记录之后，检查更新前后记录，如果一致，就不进行后续更新流程的。不一致的话，把更新前的记录和更新后的记录都当作参数传给InnoDB层，让InnoDB真正执行更新记录的操作。

3. 开启事务。InnoDB层更新记录之前，首先要记录undo log。又因为是更新操作，要把被更新的列的旧值记录下来。undo log会写入buffer pool的undo页面。不过在内存修改该undo页面后，需要记录对应的redo log。

4. InnoDB层开始更新记录，会先更新内存(同时标记为脏页)，将记录写到redo log里面。这时候更新就算完成了。结合上面说的，此时并不是直接写入磁盘，而是后台线程在合适的时间写入。

5. 至此，一条记录更新完了。

6. 一条更新语句执行完后，然后开始记录改语句对应的binlog。此时binlog会保存到binlog cache，并不会马上刷新到影片上的binlog文件。在事务提交的时候才会统一把事务运行过程中的所有binlog刷新到硬盘。

7. 事务提交，剩下的就是两阶段提交！

为什么需要两阶段提交？

事务提交之后，redo log和binlog都要持久化到磁盘，但这是两个独立的逻辑，可能出现半成功的状态，这样就造成两份日志之间的逻辑不一致。

举个例子，假设 id = 1这行数据的字段 name 的值原本是'jay'，然后执行 UPDATE t_user SET name ='xiaolin'WHERE id = 1;如果在持久化 redo log 和 binlog 两 个日志的过程中，出现了半成功状态，那么就有两种情况:

• 如果在将redo log 刷入到磁盘之后，MySQL宕机了，而binlog还没来得及写入。 MySQL重启后，通过redo log可以把buffer pool中id=1的这行数据的name字段恢复到新的值xiaolin。但是binlog里没有这条记录，从而会导致从库的这一行name字段是旧的值jay，跟主库不一致。
• 如果在将binlog刷入到磁盘后，MySQL突然宕机了，而redo log还没来得及写入。 由于redo log还没写入，重启后这个事务无效，所以id=1数据实际上还是旧的值jay，但是binlog里面记录了，又导致主从不一致。

可以看到，在持久化redo log和binlog这两份日志的时候，如果出现半成功的状态，就会导致主从不一致，根本原因是因为redo log影响的是主库的数据，binlog影响的是从库的数据。这两个log必须保持一致，才能保证主从一致。

MySQL为了避免出现两份日志之间逻辑不一致的问题，使用了两阶段提交来解决。两阶段提交其实是分布式事务一致性协议，保证多个逻辑操作要不全部成功，要不全部失败，不会出现半成功的状态。

两阶段提交把单个事务提交拆分成两个阶段，分别是准备阶段prepare、和提交阶段commit。

每个阶段都由协调者coordinator和参与者participant共同完成。不要把提交Commit阶段和commit语句混淆。commit语句执行的时候，会包含提交Commit阶段。

MySQL的InnoDB存储引擎中，为了维护两个日志的一致性，MySQL使用了内部XA事务，binlog作为协调者，存储引擎是参与者。

时间线如下：

图中可以看出事务提交有两个阶段。九十八redo log的写入拆分成prepare和commit，中间穿插写入binlog。

• prepare阶段：将XID，也就是内部XA事务的ID写入到redo log，同时redo log对应的事务状态设置为prepare。然后把redo log持久化到磁盘。
• commit阶段：XID写入binlog，然后binlog持久化到磁盘。然后调用引擎的提交事务接口，把redo log状态设置为commit。这个操作不需要持久化到磁盘，只需要写到文件系统的page cache中就够了。因为只要binlog写磁盘成功，就算redo log的状态还是prepare也没关系，一样会认为事务已执行成功。

异常重启现象？

不管是时刻A还是时刻B崩溃，redo log 都是prepare状态。

MySQL重启后会按顺序扫描redo log文件，碰到处于prepare状态的redo log，就会拿着其中的XID，到binlog内查找有没有同样的XID。

• 如果没有，说明redo log刷盘，binlog没有刷盘，则回滚事务。
• 如果有，说明均已刷盘，则提交事务。

所以两阶段提交是以binlog写成功为事务提交成功的标识。因为binlog写成功了，意味着能在binlog中查找到与redo log相同的XID。

两阶段提交的问题？

两阶段提交保证了日志文件的数据一致性，但是性能很差、主要影响在两方面。

• 磁盘I/O次数高。对于双1配置，每个事务提交都会进行两次fsync(刷盘)，一次是redo log，另一次是binlog。
• 锁竞争激烈。两阶段提交能保证单个事务两个日志的内容一致，多事务情况下却不能保证两者提交顺序一致，因此，两阶段提交的基础上，还需要一个锁保证提交的原子性，从而保证多事务情况下两个日志的提交顺序一致。

无锁冲突的例子： 血泪场景再现： (掏出两个会爆炸的毛线球) 事务A：给主人账户+100元 事务B：给主人账户-50元 初始余额：0元

无锁交叉提交时间线： 1️⃣ 事务A

prepare redo → 写binlog → 卡在commit redo前

2️⃣ 事务B

prepare redo → 写binlog → commit redo → 完成

3️⃣ 系统崩溃！

(此时事务A的binlog已写，但commit redo未写)

数据库恢复时的死亡操作：

🔍 发现事务A的binlog存在 → 强制提交（但实际没写commit redo）

🔍 事务B正常提交

最终余额： 0元 +100元(A) -50元(B) = 50元

但真实物理日志： 只有事务B的修改真正落盘！ (主人账户凭空多出50元！数据库变成许愿池啦！)

为什么两阶段提交磁盘I/O高？

主要是binlog和redo log的刷盘，由参数sync_binlog和innodb_flush_log_at_trx_commit控制

为什么锁竞争激烈？

加锁解决顺序一致性问题，并发较大的时候导致对锁的争用，性能不佳。

binlog组提交

MySQL 引入了 binlog 组提交(group commit)机制，当有多个事务提交的时候，会将多个 binlog 刷盘 操作合并成一个，从而减少磁盘 I/O 的次数，如果说 10 个事务依次排队刷盘的时间成本是 10，那么将 10 个事务一次性一起刷盘的时间成本则近似于 1。

引入组提交机制之后，prepare阶段不变，只针对commit阶段，commit拆分成三个过程：

• flush 阶段:多个事务按进入的顺序将 binlog 从 cache 写入文件(不刷盘)
• sync 阶段:对 binlog 文件做 fsync 操作(多个事务的 binlog 合并一次刷盘)
• commit 阶段:各个事务按顺序做InnoDB commit 操作;

上面的每个阶段都有一个队列，每个阶段有锁进行保护，因此保证了事务写入的顺序，第一个进入队列 事务会成为 leader，leader领导所在队列的所有事务，全权负责整队的操作，完成后通知队内其他事务工作结束。

对每个阶段引入队列之后，锁只对每个队列进行保护，不再锁住事务提交的整个过程，锁粒度减小了，可以并发执行，提高效率。

redo log 组提交

这个要看 MySQL 版本，MySQL 5.6 没有 redo log 组提交，MySQL 5.7 有 redo log 组提交. 在 MySQL 5.6 的组提交逻辑中，每个事务各自执行 prepare 阶段，也就是各自将 redo log 刷盘，这样就 没办法对 redo log 进行组提交。 所以在 MySQL 5.7 版本中，做了个改进，在 prepare 阶段不再让事务各自执行 redo log 刷盘操作，而是 推迟到组提交的 flush 阶段，也就是说 prepare 阶段融合在了 flush 阶段。 这个优化是将 redo log 的刷盘延迟到了 flush 阶段之中，sync 阶段之前。通过延迟写 redo log 的方式 为 redo log 做了一次组写入，这样 binlog 和 redo log 都进行了优化。