MySQL3_1日志

发表于 2020-05-26 更新于 2023-08-20 分类于 MySQL

redo log

实现持久性的方式：
1、在事务提交完成之前把该事务所修改的所有页面都刷新到磁盘；
但是只修改了一点数据也刷新整个页比较浪费，且一次写入可能涉及到很多不连续的页，这样随机 IO 效率比较低。
2、把修改了哪些东西记录一下就好，即使系统崩溃也能恢复
即 redo log，优点是占用空间小、顺序写入。

WAL（Write-Ahead Logging）

先写日志，再写磁盘，同时，InnoDB 引擎会在系统相对空闲的时候将操作记录同步到磁盘上。
WAL 保证了 InnoDB 是crash-safe的，因为即使数据库发生异常重启，之前提交的记录都不会丢失。

redo log 的结构

每条 redo log 会记录以下属性：

type：该条 redo 日志的类型。
space ID：表空间 ID。
page number：页号。
data：该条 redo 日志的具体内容。

对不同类型字段作修改时会记录不同类型的 redo log，比如：

表中没有主键时，会生成一个row_id隐藏列保存到 data 字段里；
涉及变长字符串类型的 redo log 因为不确定具体占用多少字段空间，因此 data 字段中还有一个len字段。

Mini-Transaction

在 MySQL 中对底层页面中的一次原子访问的过程称之为一个Mini-Transaction，简称mtr，一个 mtr 可以包含一组 redo log，在进行崩溃恢复时这一组 redo log 是一个不可分割的整体。
比如插入一条记录的时候，如果数据页的空闲空间不足，需要进行页分裂操作：新建一个叶子节点，然后把原先数据页中的一部分记录复制到这个新的数据页中，然后再把记录插入进去，把这个叶子节点插入到叶子节点链表中，最后还要在内节点中添加一条目录项记录指向这个新创建的页面。这个过程中需要对多个页面进行改改，因此会产生多条 redo log，这个过程必须是原子的，InnoDB 会以组的形式来记录 redo log，崩溃恢复时要么整组恢复、要么一条也不恢复，因此被称为悲观插入。

如果数据页的空闲空间充足则可以直接插入，这种方式被称为乐观插入。

redo 日志的存储结构

MySQL-redolog
MySQL 会向操作系统申请一块redo log buffer连续内存空间，这块内存空间之后被划分为若干连续 redo log block。
InnoDB 会维护一个全局变量buf_free，指示后续 redo 日志应该写入到 log buffer 中的哪个位置。
最终 redo log 会被刷新到磁盘中被称为block的页中，其中关键字段包括：

该 block 的唯一标识；
第一条 redo 日志偏移量：一个 mtr 会生成多条 redo 日志记录（redo log record），这些日志被统称为一个 redo 日志记录组（redo log record group），block 会记录这个 redo 日志记录组第一条记录的偏移量。
checkpoint 的序号。
lsn：每条日志都有一个序列号Log Sequence Number，简称为lsn，它的值是不断增长的，初始值为 8704，lsn 值越小，则说明该 redo log 生成的时间越早。

redo log 刷盘时机

当内存数据页和磁盘数据页内容不一致时，我们称这个内存页为”脏页”，内存数据写入到磁盘后，内存和磁盘上的数据页内容就一致了称为”干净页”，，redo log 就是内存数据页，而 B+树结构的聚簇索引就是磁盘数据页。
redo log 会被复制到 log buffer 中，但是 log buffer 的空间是有限的，当满足一定条件时需要被刷新到磁盘里：

log buffer 空间不足时
log buffer 的大小是有限的（通过系统变量innodb_log_buffer_size指定），当要读入的数据页没有在内存中的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页就必须将脏页先刷到磁盘，变成干净页后才能复用。
事务提交时
我们前边说过之所以使用 redo 日志主要是因为它占用的空间少，还是顺序写，在事务提交时可以不把修改过的 Buffer Pool 页面刷新到磁盘，但是为了保证持久性，必须要把修改这些页面对应的 redo 日志刷新到磁盘。
后台线程的执行
后台有一个线程，大约每秒都会刷新一次 log buffer 中的 redo 日志到磁盘。
正常关闭服务器时
做checkpoint时

批量从 flush 链表中刷出脏页

我们在介绍 Buffer Pool 的时候说过，一般情况下都是后台的线程在对 LRU 链表和 flush 链表进行刷脏操作，这主要因为刷脏操作比较慢，不想影响用户线程处理请求。但是如果当前系统修改页面的操作十分频繁，这样就导致写日志操作十分频繁，系统 lsn 值增长过快。如果后台的刷脏操作不能将脏页刷出，那么系统无法及时做 checkpoint，可能就需要用户线程同步的从 flush 链表中把那些最早修改的脏页（oldest_modification 最小的脏页）刷新到磁盘，这样这些脏页对应的 redo 日志就没用了，然后就可以去做 checkpoint 了。

查看各种 LSN 值

1	SHOW ENGINE INNODB STATUS

Log sequence number：代表系统中的 lsn 值，也就是当前系统已经写入的 redo 日志量，包括写入 log buffer 中的日志。
Log flushed up to：代表 flushed_to_disk_lsn 的值，也就是当前系统已经写入磁盘的 redo 日志量。
Pages flushed up to：代表 flush 链表中被最早修改的那个页面对应的 oldest_modification 属性值。
Last checkpoint at：当前系统的 checkpoint_lsn 值。

innodb_flush_log_at_trx_commit

为了保证事务的持久性，一般来说用户线程在事务提交时需要将该事务执行过程中产生的所有 redo 日志都刷新到磁盘上。
但是出于效率上的考虑，可以修改innodb_flush_log_at_trx_commit的取值来调整这个过程：

0：当该系统变量值为 0 时，表示在事务提交时不立即向磁盘中同步 redo 日志，这个任务是交给后台线程做的。
这样很明显会加快请求处理速度，但是如果事务提交后服务器挂了，后台线程没有及时将 redo 日志刷新到磁盘，那么该事务对页面的修改会丢失。
1：当该系统变量值为 1 时，表示在事务提交时需要将 redo 日志同步到磁盘，可以保证事务的持久性。1 也是innodb_flush_log_at_trx_commit的默认值。
2：当该系统变量值为 2 时，表示在事务提交时需要将 redo 日志写到操作系统的缓冲区中，但并不需要保证将日志真正的刷新到磁盘。
这种情况下如果数据库挂了，操作系统没挂的话，事务的持久性还是可以保证的，但是操作系统也挂了的话，那就不能保证持久性了。

崩溃恢复

1、确定恢复的起点
checkpoint_lsn之前的 redo 日志都可以被覆盖，也就是说这些 redo 日志对应的脏页都已经被刷新到磁盘中了，既然它们已经被刷盘，我们就没必要恢复它们了。对于checkpoint_lsn之后的 redo 日志，它们对应的脏页可能没被刷盘，也可能被刷盘了，我们不能确定，所以需要从checkpoint_lsn开始读取 redo 日志来恢复页面。
redo 日志文件组的第一个文件的管理信息中有两个 block 都存储了 checkpoint_lsn 的信息，我们当然是要选取最近发生的那次 checkpoint 的信息。衡量 checkpoint 发生时间早晚的信息就是所谓的 checkpoint_no，我们只要把 checkpoint1 和 checkpoint2 这两个 block 中的 checkpoint_no 值读出来比一下大小，哪个的 checkpoint_no 值更大，说明哪个 block 存储的就是最近的一次 checkpoint 信息。这样我们就能拿到最近发生的 checkpoint 对应的 checkpoint_lsn 值以及它在 redo 日志文件组中的偏移量 checkpoint_offset。
2、确定恢复的终点
普通 block 的 log block header 部分有一个称之为 LOG_BLOCK_HDR_DATA_LEN 的属性，该属性值记录了当前 block 里使用了多少字节的空间。

对于已经填满的 block 来说，该值就是 512，也就是说如果该值不是 512，那么它就是这次崩溃恢复中需要扫描的最后一个 block 了。
3、恢复
确定起点和终点后，我们就可以按照 redo log 的顺序依次扫描checkpoint_lsn之后的各条 redo 日志来执行恢复了。

redo log flush

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。
MySQL 偶尔的抖动，很有可能就是在刷脏页（flush）。
有以下几种情况都会引起 flush：

redo log 写满了，需要释放一些空间，将 checkpoint 往前推进，并将之间的日志对应的脏页都 flush 到磁盘上。
系统内存不足，需要新的内存页时内存不够用了，需要淘汰一些数据页，空出内存来给其他数据页使用，如果淘汰的是脏页，则需要先将脏页写入到磁盘。
空闲期间，MySQL 会自动用过剩的计算能力执行 flush 任务。
正常关闭时，MySQL 会将内存的脏页都 flush 到磁盘。

bin log

WAL 机制保证了 MySQL 数据不会丢失，WAL 的核心是 bin log 和 redo log。

bin log 与 redo log 区别

1、redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
2、redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
3、redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

bin log 只能用于归档，没有 crash-safe 能力，而 redo log 是 InnoDB 的，可以提供 crash-safe 能力。

binlog 写入机制

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

binlog 写入的关键是要保证原子性：
MySQL-binlog写盘

每个线程有自己的 binlog cache，但是共用同一份 binlog 文件；
上图的write指的是把日志写入文件系统的page cache，并没有把数据持久化到磁盘，所以速度较快；
上图的fsync才是将数据持久化到磁盘的操作。

redo log 会被先写入到 redo log buffer 内，分以下几种情况：
MySQL-redolog存储状态

存在 redo log buffer 中，物理上是在 MySQL 进程内存中，就是图中的红色部分；
写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面，也就是图中的黄色部分；
持久化到磁盘，对应的是 hard disk，也就是图中的绿色部分。

redo log buffer 写入磁盘的时机：

后台线程每秒轮询，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘；
redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。
并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘。

bin log 格式

bin log 有 3 种格式：

statement: 存的是语句原文，可能会导致主备不一致，比如在主库和之后在备库上执行时选取的索引不一样；
row: 会记录具体作用的目标数据，比较占用空间、且会消耗大量 IO 资源；
比如一条 delete 语句，statement 格式的 bin log 会直接记录该语句，而 row 格式会记录具体删除的记录的 ID。
mixed: 自动判断 SQL 语句是否可能导致主备不一致，若有可能则采用 row，否则 statement。

查看 bin log

本地创建配置文件：

[mysql]
[mysqld]
log-bin=mysql-bin
expire-logs-days=14
server-id=1
binlog_format=statement

使用 Docker 启动 MySQL 进程，注意-v 前面是宿主机的配置文件所在目录，后面是容器内的配置文件目录：

1	sudo docker run -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=123456 -v /Users/huanggaochi/Downloads/mysql:/etc/mysql/conf.d -d mysql:5.7

连接时如果遇到文件，可以使用docker logs [CONTAINER ID]查看容器启动日志。

连接 MySQL 后查看 bin log 是否有被开启：

1	show variables like 'log_bin';

下面是测试用 SQL 语句：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `t_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `t_modified`(`t_modified`)
) ENGINE=InnoDB;

insert into t values(1,1,'2018-11-13');
insert into t values(2,2,'2018-11-12');
insert into t values(3,3,'2018-11-11');
insert into t values(4,4,'2018-11-10');
insert into t values(5,5,'2018-11-09');

delete from t /*comment*/  where a>=4 and t_modified<='2018-11-10' limit 1;

运行后，查看 bin log：

1	show binlog events in 'mysql-bin.000001';

undo log

需要回滚的情况：
1、事务执行过程中可能遇到各种错误，比如服务器本身的错误，操作系统错误，甚至是突然断电导致的错误。
2、程序员可以在事务执行过程中手动输入 ROLLBACK 语句结束当前的事务的执行。
为了有需要时能够回滚，每当我们要对一条记录做改动时都需要将回滚时所需的东西都记录一下，包括：
1、插入记录时，记录主键，这样回滚时直接删除这条记录即可；
2、删除记录时，将这条记录的内容记录下来，回滚时重新插入即可；
3、修改记录时，将旧值记录下来，回滚时重新更新回旧值。

事务 ID（trx_id）

分配时机

如果某个事务执行过程中对某个表执行了增、删、改操作，那么 InnoDB 存储引擎就会给它分配一个独一无二的事务 id。
1、只读事务（START TRANSACTION READ ONLY）
只读事务中不可以对普通的表进行增删改操作，但可以对临时表做增、删、改操作。
对于只读事务来说，只有在它第一次对某个用户创建的临时表执行增、删、改操作时才会为这个事务分配一个事务 id，否则的话是不分配事务 id 的。
2、读写事务（START TRANSACTION READ WRITE、BEGIN、START TRANSACTION）
在读写事务中可以对表执行增删改查操作。
对于读写事务来说，只有在它第一次对某个表（包括用户创建的临时表）执行增、删、改操作时才会为这个事务分配一个事务 id，否则的话也不会分配事务 id

总而言之，只有在事务对表中的记录做改动时才会为这个事务分配一个唯一的事务 id。

生成方式

和 row_id 的生成方式类似：

服务器会在内存中维护一个全局变量，每当需要为某个事务分配一个事务 id 时，就会把该变量的值当作事务 id 分配给该事务，并且把该变量自增 1。
每当这个变量的值为 256 的倍数时，就会将该变量的值刷新到系统表空间的页号为 5 的页面中一个称之为 Max Trx ID 的属性处，这个属性占用 8 个字节的存储空间。
当系统下一次重新启动时，会将上边提到的 Max Trx ID 属性加载到内存中，将该值加上 256 之后赋值给我们前边提到的全局变量（因为在上次关机时该全局变量的值可能大于 Max Trx ID 属性值）。

undo log 格式

1、查看 table id
记录 undo log 时会使用到表的 table id，这个值可以通过SELECT * FROM information_schema.innodb_sys_tables WHERE name = 'database_name/undo_demo';这条命令来查看
2、INSERT 操作的 undo log

undo no 在一个事务中是从 0 开始递增的，也就是说只要事务没提交，每生成一条 undo 日志，那么该条日志的 undo no 就增 1。
主键可能是有多个列组成的，如果有多个列，则每个列占用的存储空间大小和对应的真实值都需要记录下来。
比如对下面这条插入了两条记录的 SQL：

BEGIN;  # 显式开启一个事务，假设该事务的id为100

# 插入两条记录
INSERT INTO undo_demo(id, key1, col) 
    VALUES (1, 'AWM', '狙击枪'), (2, 'M416', '步枪');

针对这两条数据生成的 undo log 如下所示：

3、DELETE 操作对应的 undo log
插入到页面中的记录会通过头信息中的next_record属性组成一个单向链表，而被删除的记录则会组成另一个链表，Page Header中的PAGE_FREE属性指向了这个链表的头节点。

删除时会先将记录的delete_mask标识位设置为 1，表示已经被逻辑删除了。

当该删除语句所在的事务提交之后，会有专门线程将记录真正地清除掉：将该记录从链表中移除并移入自由链表中。

由上可知，在事务执行完毕之前，记录并不会被真正地清除，所以回滚时也只需要将这个删除标识清除即可。
3、UPDATE 操作的 undo log
不更新主键的情况下，如果更新后的列和更新前的列值占用的存储空间一样大，那么就可以执行就地更新，直接在原记录上修改对应列的值；但是如果有任何一个列更新后占用的存储空间大小有变化，那么就需要将旧的记录从聚簇索引页面中删除（这是真正的删除，不是逻辑删除），然后创建一条新的记录插入到页面中。
更新主键的情况下，旧记录会执行delete mark操作，由一个后台线程做 purge 操作，将其加入到垃圾链表中。