MySQL log

阅读数: 次 2022-07-19

MySQL 三大日志

redolog（事务日志、重做日志，在磁盘上）

redo log（重做日志）是InnoDB存储引擎独有的，它让MySQL拥有了崩溃恢复能力。比如 MySQL 实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。

MySQL是以页的形式存储数据的，查询一条数据，会从硬盘中把一页的数据加载出来，加载出来的数据叫做数据页（也叫脏页），会放到buffer poll中。然后下次再查询时，会先到缓冲池中查找，没有命中就再去硬盘加载，减少硬盘IO开销，提升性能。然后更新数据的时候，发现缓冲池中有需要更新的数据，就直接再缓冲池中更新。然后把“某个数据页上做了什么修改”记录到Redo log buffer中，接着刷盘到redo log中。

理想情况，事务一提交就会进行刷盘操作，但实际上，刷盘的时机是根据策略来进行的。

刷盘时机

InnoDB 存储引擎为 redo log 的刷盘策略提供了 innodb_flush_log_at_trx_commit 参数，它支持三种策略：

在计算机的操作系统中用户空间缓冲区的数据是无法直接写入磁盘的，中间必需经过操作系统缓冲区（OS Buffer）。因此，redo log buffer写入redo log file，实际上会先写入OS Buffer，然后再调用fsync()将其刷入到redo log file。

0：延迟写。表示每次事务提交时不进行刷盘操作，提交事务时不会将redo log buffer写入os buffer，而是每隔1秒将redo log buffer写入os buffer并调用fsync()刷入磁盘。系统崩溃会丢失一秒钟的数据。
1：实时写，实时刷。表示每次事务提交时都将进行刷盘操作（默认值）。每次提交事务都将redo log buffer写入os buffer并调用fsync()刷入磁盘。这种方式系统奔溃不会丢失数据，因每次提交事务都写入磁盘，性能比较差。
2：实时写，延时刷。表示每次事务提交时都只把 redo log buffer 内容写入 page cache。每次提交事务都将redo log buffer写入os buffer，但并不会马上调用fsync()刷入磁盘，而是间隔1秒调fsync()刷盘。相对于每次提交都写盘和每隔1秒写盘，实时写os buffer延时刷盘是一个数据一致性与性能的之间的折中方案。

innodb_flush_log_at_trx_commit 参数默认为 1 ，也就是说当事务提交时会调用 fsync 对 redo log 进行刷盘

除了后台线程每秒1次的轮询操作，还有一种情况，当 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动刷盘。

现在我们来思考一个问题： 只要每次把修改后的数据页直接刷盘不就好了，还有 redo log 什么事？

它们不都是刷盘么？差别在哪里？

1 Byte = 8bit
1 KB = 1024 Byte
1 MB = 1024 KB
1 GB = 1024 MB
1 TB = 1024 GB

实际上，数据页大小是16KB，刷盘比较耗时，可能就修改了数据页里的几 Byte 数据，有必要把完整的数据页刷盘吗？

而且数据页刷盘是随机写，因为一个数据页对应的位置可能在硬盘文件的随机位置，所以性能是很差。

如果是写 redo log，一行记录可能就占几十 Byte，只包含表空间号、数据页号、磁盘文件偏移量、更新值，再加上是顺序写，所以刷盘速度很快。

所以用 redo log 形式记录修改内容，性能会远远超过刷数据页的方式，这也让数据库的并发能力更强。

binlog（二进制日志、归档日志）

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

而 binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于MySQL Server 层。

不管用什么存储引擎，只要发生了表数据更新，都会产生 binlog 日志。

那 binlog 到底是用来干嘛的？

可以说MySQL数据库的数据备份、主备、主主、主从（(31条消息) 后端技术 - 主备、主从、主主的区别_穿素白衫的少年的博客-CSDN博客_主从和主主的区别）都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

主备：备机只用来同步主机的数据，不对外提供服务

主从：相较于备机，从机要对外提供读的操作

主主：两台都是主机，同时对外提供读写操作（涉及到分布式ID的问题）

binlog会记录所有涉及更新数据的逻辑操作，并且是顺序写。

记录格式

binlog 日志有三种格式，可以通过binlog_format参数指定。

statement
row
mixed

指定statement，记录的内容是SQL语句原文，比如执行一条update T set update_time=now() where id=1，记录的内容如下。

同步数据时，会执行记录的SQL语句，但是有个问题，update_time=now()这里会获取当前系统时间，直接执行会导致与原库的数据不一致。

为了解决这种问题，我们需要指定为row，记录的内容不再是简单的SQL语句了，还包含操作的具体数据，记录内容如下。

row格式记录的内容看不到详细信息，要通过mysqlbinlog工具解析出来。

update_time=now()变成了具体的时间update_time=1627112756247，条件后面的@1、@2、@3 都是该行数据第 1 个~3 个字段的原始值（假设这张表只有 3 个字段）。

这样就能保证同步数据的一致性，通常情况下都是指定为row，这样可以为数据库的恢复与同步带来更好的可靠性。

但是这种格式，需要更大的容量来记录，比较占用空间，恢复与同步时会更消耗IO资源，影响执行速度。

所以就有了一种折中的方案，指定为mixed，记录的内容是前两者的混合。

MySQL会判断这条SQL语句是否可能引起数据不一致，如果是，就用row格式，否则就用statement格式。

写入机制

binlog的写入时机也非常简单，事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。

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

我们可以通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）。

binlog日志刷盘流程如下

上图的 write，是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快

上图的 fsync，才是将数据持久化到磁盘的操作

write和fsync的时机，可以由参数sync_binlog控制，默认是0。

为0的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync。

虽然性能得到提升，但是机器宕机，page cache里面的 binlog 会丢失。

为了安全起见，可以设置为1，表示每次提交事务都会执行fsync，就如同 redo log 日志刷盘流程 一样。

最后还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync。

在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。

同样的，如果机器宕机，会丢失最近N个事务的binlog日志。

两阶段提交

redo log（重做日志）让InnoDB存储引擎拥有了崩溃恢复能力。

binlog（归档日志）保证了MySQL集群架构的数据一致性。

虽然它们都属于持久化的保证，但是侧重点不同。

在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样。

redo log与binlog两份日志之间的逻辑不一致，会出现什么问题？

我们以update语句为例，假设id=2的记录，字段c值是0，把字段c值更新成1，SQL语句为update T set c=1 where id=2。

假设执行过程中写完redo log日志后，binlog日志写期间发生了异常，会出现什么情况呢？

由于binlog没写完就异常，这时候binlog里面没有对应的修改记录。因此，之后用binlog日志恢复数据时，就会少这一次更新，恢复出来的这一行c值是0，而原库因为redo log日志恢复，这一行c值是1，最终数据不一致。

为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。

原理很简单，将redo log的写入拆成了两个步骤prepare和commit，这就是两阶段提交。

使用两阶段提交后，写入binlog时发生异常也不会有影响，因为MySQL根据redo log日志恢复数据时，发现redo log还处于prepare阶段，并且没有对应binlog日志，就会回滚该事务。

再看一个场景，redo log设置commit阶段发生异常，那会不会回滚事务呢？

并不会回滚事务，它会执行上图框住的逻辑，虽然redo log是处于prepare阶段，但是能通过事务id找到对应的binlog日志，所以MySQL认为是完整的，就会提交事务恢复数据。

undo log（回滚日志）

undo log是mysql中比较重要的事务日志之一，顾名思义，undo log是一种用于撤销回退的日志，在事务没提交之前，MySQL会先记录更新前的数据到 undo log日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 undo log来进行回退。

undo log的两个功能：

提供数据回滚

我们在进行数据更新操作的时候，不仅会记录redo log，还会记录undo log，如果因为某些原因导致事务回滚，那么这个时候MySQL就要执行回滚（rollback）操作，利用undo log将数据恢复到事务开始之前的状态。

如我们执行下面一条删除语句：

delete from user where id = 1;
那么此时undo log会记录一条对应的insert 语句【反向操作的语句】，以保证在事务回滚时，将数据还原回去。

再比如我们执行一条update语句：

update user set name = “李四” where id = 1; —修改之前name=张三
此时undo log会记录一条相反的update语句，如下：

update user set name = “张三” where id = 1;
如果这个修改出现异常，可以使用undo log日志来实现回滚操作，以保证事务的一致性。
提供MVCC多版本控制

MVCC，即多版本控制。在MySQL数据库InnoDB存储引擎中，用undo Log来实现多版本并发控制(MVCC)。当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据版本是怎样的，从而让用户能够读取到当前事务操作之前的数据【快照读】。

快照读：

SQL读取的数据是快照版本【可见版本】，也就是历史版本，不用加锁，普通的SELECT就是快照读。

当前读：

SQL读取的数据是最新版本。通过锁机制来保证读取的数据无法通过其他事务进行修改UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE都是当前读。

在InnoDB存储引擎中，undo log分为：

insert undo log：指在insert 操作中产生的undo log，因为insert操作的记录，只对事务本身可见，对其他事务不可见。故该undo log可以在事务提交后直接删除，不需要进行purge操作。
update undo log：记录的是对delete 和update操作产生的undo log，该undo log可能需要提供MVCC机制，因此不能再事务提交时就进行删除。提交时放入undo log链表，等待purge线程进行最后的删除。

另外，MVCC 的实现依赖于：隐藏字段、Read View、undo log。在内部实现中，InnoDB 通过数据行的 DB_TRX_ID 和 Read View 来判断数据的可见性，如不可见，则通过数据行的 DB_ROLL_PTR 找到 undo log 中的历史版本。每个事务读到的数据版本可能是不一样的，在同一个事务中，用户只能看到该事务创建 Read View 之前已经提交的修改和该事务本身做的修改

三大日志总结

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。

MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

MVCC（多版本并发控制）

MVCC是一种

控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。如果有人从数据库中读数据的同时，有另外的人写入数据，有可能读数据的人看到“半写（脏读）”或者“不一致”（不可重复读）的数据，所以需要并发控制方法来解决这个问题。最简单的方法是我们可以通过加锁，让所有的读者等待写者工作完成，但是这个效率会很差。而MVCC的读操作是快照读，写操作在事务提交前对其他的读者是不可见的。当一个MVCC数据库需要更新一条数据记录时，他不会直接用新数据去覆盖旧数据，而是将旧数据记为过时数据并将新数据记在别处。这样就会有存储多个版本的数据，但只有一个是最新的。这种方式允许读者读取在他读之前已经存在的数据。

undolog版本链

InnoDB会在每行数据后增加隐藏字段：
1. DB_ROW_ID：行id，如果有主键就没有这一列；
2. DB_TRX_ID：记录插入或者更新该行数据的事务ID
3. DB_ROLL_PTR：回滚指针，指向undo log记录；通过回滚指针连接同一条数据的多个版本，形成一个版本链；
undo log 版本链是基于 undo log 实现的。undo log 中主要保存了数据的基本信息，比如说日志开始的位置、结束的位置，主键的长度、表id，日志编号、日志类型

此外，

例子

执行

1	INSERT INTO student VALUES (1, '张三');

产生：

继续执行：

1	UPDATE student SET name='李四' WHERE id=1;

产生：

继续执行：

1	UPDATE student SET name='王五' WHERE id=1;

1产生：

为了保证事务并发操作时，在写各自的undo log时不产生冲突，InnoDB采用回滚段的方式来维护undo log的并发写入和持久化。回滚段实际上是一种Undo文件组织方式。

ReadView 机制

可重读是事务启动的时候就生成read view整个事务结束都一直使用这个read view，而在读已提交中则是每执行一条语句就重新生成最新的read view。

ReadView 其实就是一个保存事务ID的list列表。记录的是本事务执行时，MySQL还有哪些事务在执行，且还没有提交。(当前系统中还有哪些活跃的读写事务)

它主要包含这样几部分：

m_ids，当前有哪些事务正在执行，且还没有提交，这些事务的 id 就会存在这里；
min_trx_id，是指 m_ids 里最小的值；
max_trx_id，是指下一个要生成的事务 id。下一个要生成的事务 id 肯定比现在所有事务的 id 都大；
creator_trx_id，每开启一个事务都会生成一个 ReadView，而 creator_trx_id 就是这个开启的事务的 id。
这样在访问某条记录时，只需要按照下边的步骤判断该记录在版本链中的某个版本（trx_id）是否可见：
1、trx_id < m_ids列表中最小的事务id
表明生成该版本的事务在生成ReadView前已经提交，所以该版本可以被当前事务访问。
2、trx_id > m_ids列表中最大的事务id
表明生成该版本的事务在生成ReadView 后才生成，所以该版本不可以被当前事务访问。
3、m_ids列表中最小的事务id < trx_id < m_ids列表中最大的事务id
此处比如m_ids为[5,6,7,9,10]
①、若trx_id在m_ids中，比如是6，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问。
②、若trx_id不在m_ids中，比如是8，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。

例子

（个人感觉这个例子针对可重复读是错的，事务A当不是只读事务时，它的creator_trx_id等于０；而在其他情况creator_trx_id应该大于当前所有数据行的创建id，以及所有数据行的修改id，比如说creator_trx_id=20，m_id=[15,18,19,20]，当快照读的时候，查看快照的trx_id，如果小于m_id的最小值，说明在创建该事务时，该快照已经提交；但如果大于m_id的最大值，说明在创建该事务的时候，这个快照还没有创建）

在查询一个数据的时候，也就是开启一个事务进行查询的时候，我们要查询的这个数据行可能会有多个row_trx_id，然后判断row_trx_id与m_ids的关系。

事务是可以并发执行的，现在有事务 A、事务 B 这两个事务，且这两个都没有提交。事务 A 将会执行多次读操作，来模拟可重复读中多次读取同一行数据的场景。事务 B 则会修改这一行数据。

事务 A 开启事务的时候会生成一个 ReadView，所以说这个 ReadView 的创建者就是事务 A，事务 A 的事务 id 是 10，所以 creator_trx_id 就是 10。（在一个只读事务中creator_trx_id 为0）

此时，总共就只有事务 A、事务 B 这两个事务，而且它们都还没有提交，所以说 m_ids 会把这两个事务 id，10、18 都记录下来。min_trx_id 是 m_ids 里面的最小值，10、18 中最小的显然是 10。当前最大的事务 id 是 18，那么下一个事务的 id 就是 19，max_trx_id 就是 19。

ReadView 生成之后，事务 A 就要去 undo log 版本链中读取值了。

现在只有一条 undo log 日志，但这并不意味着事务 A 就能读到这条日志的值 X。它要先判断这行日志的 trx_id 是否小于当前事务的 min_trx_id。看图我们可以很轻松地发现，日志的 trx_id = 8 小于 ReadView 中 min_trx_id = 10。

这就意味着，这个事务 A 开始执行之前，修改这行数据的事务已经提交了，所以事务 A 是可以查到值 X 的。

在此基础上再增添一点操作，实现可重复读

我们继续看，事务 A 第一次读完之后，事务 B 要修改这行数据了。undo log 会为所有写操作生成日志，所以就会生成一条 undo log 日志，并且它的 roll_pointer 会指向上一条 undo log 日志。

紧接着，事务 A 第二次去读这行数据了，情况如下图所示：

第一次读的时候，开启事务 A 的时候就生成了一个 ReadView

此时事务 A 第二次去查询的时候，先查到的是 trx_id = 18 的那条数据，它会发现 18 比最小的事务编号 10 大。那就说明事务编号为 18 的事务，有可能它是读不到的。

接着就要去 m_ids 里确认是否有 18 这条数据了。发现有 18，那就说明在事务 A 开启事务的时候，这个事务是没有提交的，它修改的数据就不应该被读到。

事务 A 就会顺着 roll_pointer 指针继续往下找，找到了 trx_id = 8 这条日志，发现这条能读，读到的值任然是 x，与第一次读到的结果一致。实现可重复读。