（超详细）五万字多图详解 MySQL 事务核心知识点

mooddance

已于 2022-07-17 18:06:34 修改

阅读量468

点赞数 2

分类专栏： MySQL 文章标签： mysql MVCC redo log bin log undo log

于 2022-06-29 21:48:11 首次发布

本文链接：https://blog.csdn.net/m0_51504545/article/details/125267886

版权

MySQL 专栏收录该内容

18 篇文章 2 订阅

订阅专栏

前言

本文将会对 MySQL 大部分核心的知识点进行详解，主要涉及到多种表空间、锁、隔离级别、redo log、binlog、undo log、双写（doublewrite）、两阶段提交、MVCC、B+Tree 等模块。

本文将在前面的章节讲解一些基础的知识点，在本文的后面将是文章的核心章节事务相关知识点。

一、表空间

1.1 共享表空间

共享表空间也叫系统表空间，用于存储数据字典、双写缓冲区（doublewrite buffer）、更改缓冲区（hange buffer）和重做日志（undo log）等。

可通过 SHOW VARIABLES LIKE ‘innodb_data_file_path’ 查看，若不设定则 Innodb 在初始化时候会创建一个稍大于12MB的单个自动扩展数据文件，并命名为 ibdata1。

mysql> SHOW VARIABLES LIKE 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+

共享表空间默认存放在 datadir 指定的目录下，可通过 innodb_data_home_dir 指定。如有下设置：

innodb_data_home_dir = /myibdata/
innodb_data_file_path=ibdata1:50M:autoextend

共享表空间完整的命名规范必须包括文件名、文件大小、autoextend 属性和 max 属性，且最后只能设置为： autoextend 或者 max。

其中文件大小以千字节（K）、兆字节(M)或千兆字节(G)为单位，且若以K为单位必须设定为1024的整数倍，否则将自动四舍五入到最接近的M值。

file_name:file_size[:autoextend[:max:max_file_size]]

e.g 1: 
# 指定autoextend属性后，数据文件的大小会随着空间的需要自动增加 64MB.
# 大小可由参数 innodb_autoextend_increment  控制。
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

e.g 2: 
# 若要指定自动扩展数据文件的最大大小，在 autoextend 属性后面增加 max 属性。
# 如下代表允许ibdata1增长到 500MB 大小。 
innodb_data_file_path=ibdata1:12M:autoextend:max:500M

若不开启独立表空间，那所有表的数据、索引等数据都存放在共享表空间中。这样做有好处也有缺点。

优点：

单表空间不受操作系统单文件大小限制，因为可以将一个表数据分布到不同的 ibddata 文件中。
方便管理，文件相对不多不会存在过多的句柄。

缺点:

磁盘空间使用效率低，如删数据后可能出现空间不能复用情况导致碎片化严重。
多表公用一个文件时并发时可能出现IO 瓶颈，所以需要频繁写入的场景并不适合共享表空间。

1.2 独立表空间

独立表空间是将InnoDB表及其索引都存储在一个单独的 you_table_name.ibd 数据文件中，并使用 you_table_name.frm 存放描述文件。

每个.ibd 文件代表一个单独的表空间。这个特性是由参数：innodb_file_per_table 配置选项控制的，在MySQL 5.6.6及更高版本中默认启用。

innodb_file_per_table=ON

独立表空间默认存放在 innodb_data_home_dir 下以具体数据库名为目录名的目录下，如在 Test 库下创建了 test 表：

drwx------  5 root  root    160 Jun 12 03:51 ./
drwxr-xr-x 16 mysql root    512 Jun 12 03:50 ../
-rw-r-----  1 root  root     67 Jun 12 03:50 db.opt
-rw-r-----  1 root  root   1966 Jun 12 03:50 test.frm
-rw-r-----  1 mysql mysql 98304 Jun 12 03:50 test.ibd

凡事有好坏，独立表空间也有着有缺点：

优点：

使用 truncate table 或 drop table 时候，可以释放、回收磁盘空间。而使用共享表空间的则仅在系统表空间数据文件(ibdata文件)内部创建空闲空间。
可以将表指定到单独的磁盘中，如将查询热点表单独存到一个磁盘中以优化查询；在如将一个大表单独享用一个独立磁盘等等。create table… data directory=absolute_path_to_directory（单独指定的磁盘路径）
当使用 optimize table 优化重建表时候，会新建一个只存放需要保留的数据的文件.ibd，并将新 .ibd 文件替换旧的 .ibd。不需要在共享表空间操作，不影响其他表的使用。
支持可迁移表空间特性，可以对单独表的迁移。如将一个 .ibd文件和.frm 复制到其他的数据库实例中就能使用。
可以在操作系统层面就能知道单表的使用大小，而不用通过mysql。
当 innodb_flush_method=O_DIRECT，Linux文件系统不允许并发写入单个文件。所以使用独立表空间就比使用共享表空间更具优势。

缺点：

当进行 fsync 时候是在独立表空间文件上进行，所以不能像使用共享表空间那样可以将多个 I/O 合并成一个进行操作。一定程度上会增加较多的 fsync 调用。
mysqld 会为每一个表保留一个文件句柄，若表过多的情况下可能会影响性能。
单表大小会受操作系统单文件大小限制。

可以结合共享表空间和独立表空间优缺点和具体的业务特点选择是否开启独立表空间，当然也可以将共享表空间中的表抽离成独立表空间。

一般有两种方式可以采用，在此之前都需要开启独立表空间。

innodb_file_per_table=ON

两种方式分别是：

逻辑备份后，重启数据库后导入。
重启数据库并将需要抽离的表执行 alter table you_table_name engine=innodb，该方法只会对新表使用独立表空间，旧表还是会使用共享表空间。

1.3 临时表空间

默认情况下 innodb 会在 innodb_data_home_dir目录创建一个 12MB 的 ibtmp1 文件，用于存放查询时候产生临时表。

可以使用 innodb_temp_data_file_path选项指定临时表空间数据文件的路径、文件名和文件大小。

其设置方式和共享表空间一致，如：ibtmp1:12M:autoextend。

在使用大型临时表或广泛使用临时表的环境中，自动扩展临时表空间数据文件可能会变得很大。
大型数据文件也可能来自使用临时表的长时间运行的查询。为防止临时数据文件变得过大，可配置 innodb_temp_data_file_path选项以指定最大数据文件大小。

二、锁

锁的作用是在并发情况下通过加锁的机制实现各种隔离级别，MySQL 有着多种锁的存在，可以按照锁粒度、锁机制、算法分类、模式、状态、特殊的自增锁等几种类型进行分类。

图 2-1：
图2-1

2.1 按照粒度分类

按锁粒度可分为：全局锁、表级锁、页级锁、行级锁。

2.1.1 全局锁：对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的 MDL、DDL、更新操作语句等事务提交将被阻塞。通常用于全库的逻辑备份。

# 可使用以下语句进行加锁
FLUSH TABLES WITH READ LOCK;

# 可使用以下语句进行释放锁
UNLOCK TABLES;

2.1.2 表级锁：对当前操作的整张表加锁，分为表锁、元数据锁（meta data lock，MDL)两种。

元数据锁是MySQL5.5版本引入，用于解决或者保证DDL操作与DML操作之间的一致性。当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL写锁。

# 表锁加锁
lock tables table_name read/write；

# 释放表锁
unlock tables;

# 可使用语句查看表是否加锁， In_use = 1时候代表该表上有锁存在。
SHOW OPEN TABLES;

e.g.

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
+----------------+
1 row in set (0.01 sec)
 
mysql> lock tables test read;
Query OK, 0 rows affected (0.01 sec)
 
mysql> show open tables;
+----------+---------------------------+--------+-------------+
| Database | Table                     | In_use | Name_locked |
+----------+---------------------------+--------+-------------+
| test     | test                      |      1 |           0 |
+----------+---------------------------+--------+-------------+
22 rows in set (0.00 sec)

2.1.3 页级锁：锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多；行级冲突少，但速度慢。因此采取了折中的页级锁，一次锁定相邻的一组记录。BDB 引擎支持页级锁。

2.1.4 行级锁：粒度最低的锁，发生锁冲突的概率也最低、并发度最高。但是加锁慢、开销大，容易发生死锁现象。只有 InnoDB 支持行级锁，分为共享锁和排他锁。

其中行级锁并不是直接锁记录，而是锁索引。索引分为主键索引（聚集（clustered）索引）和非主键索引（非聚集（unclustered）索引）两种，所以加锁分为两种情况：

使用主键索引，行级锁就会锁定这条主键索引；
使用非主键索引，行级锁先锁定该非主键索引，再锁定对应的主键索引。在UPDATE、DELETE操作时，行级锁不仅锁定 WHERE 条件扫描过的所有索引记录，而且会锁定相邻的键值，即临键锁（next-key locking）。

Innodb 是否使用行锁，需要注意以下几种情况：

不通过索引条件查询的时候，InnoDB使用的是表锁。
行锁是针对索引加的锁，所以尽管访问不同行的记录，如果使用了相同的索引键，也是会出现锁冲突的。
即便在条件中使用了索引字段，但是执行计划选择了全表扫描情况下，innodb 使用的是表锁而非行锁。

2.2 按机制分类

按属性分类，可分为共享锁（S 锁）和排他锁（X 锁）。

2.2.1 共享锁(Shared)

又称为S 锁、读锁，即多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。只有当数据上的读锁被释放后，其他事务才能对其添加写锁。

共享锁主要是为了支持并发的读取数据而出现的，读取数据时，不允许其他事务对当前数据进行修改操作，从而避免不可重读的问题的出现。

# 开启共享锁
select * from table  lock in share mode;

2.2.2 排它锁（Exclusive）
又称为X 锁、写锁、独占锁。当一个事务对数据加上写锁后，其他事务既不能对该数据添加读锁，也不能对该数据添加写锁，写锁与其他锁都是互斥的。

写锁主要是为了解决在修改数据时，不允许其他事务对当前数据进行修改和读取操作，从而可以有效避免脏读问题的产生。

innodb 在发生 DML(update,delete,insert)) 语句时默认会数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用 select …f or update语句。

2.3 按加锁方式分类

可分为隐式（自动）锁、显示锁。

2.3.1 隐式加锁：

InnoDB 自动加意向锁。
DML 语句自动添加排他锁。

2.3.2 显示加锁：

共享锁（S）：SELECT * FROM table WHERE … LOCK IN SHARE MODE。
排他锁（X) ：SELECT * FROM table WHERE … FOR UPDATE。

2.4 按算法分类

可分为记录锁、间隙锁、临键锁。

2.4.1 记录锁（Record Lock）：

A record lock is a lock on an index record.
For example, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;
prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.

记录锁也称之为行锁，锁住的是索引记录，如果使用索引作为条件命中了记录，那么就是记录锁。被锁住的记录不能被别的事务插入相同的索引键值，修改和删除。

2.4.2 间隙锁（Gap Lock）：

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.

锁定一段范围内（左开右开的区间）的索引记录。使用间隙锁锁住的是一个区间，不仅仅是这个区间中的每一条数据。本质上是用于阻止其他事务在该间隙内插入新记录，而自身事务是允许在该间隙内插入数据的。间隙锁只存在可重复读隔离。

间隙锁是不互斥的，故不区分共享间隙锁或互斥间隙锁。即两个事务可以同时持有包含共同间隙的间隙锁，不管两个间隙锁的间隙区间完全一样，还是一个间隙锁包含的间隙区间是另一个间隙锁包含间隙区间的子集。

图 2-2：
在这里插入图片描述
如上图中：1、5、6、9代表是已经有的记录主键，中间的部分是空着的。

如 select * from table where id = 3 或 select * from where id between 1 and 5，这时候其他事务不能在区间（1,5）之间插入新的记录。
如 select * from table where id = 20，这时区间（9，+∞）之间是被锁住的。如 insert id = 13 的语句将会被阻塞。

2.4.3 临键锁（Next-key Lock）：

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

临键锁相当于记录锁+间隙锁的组合。是为了解决幻读问题而出现的。其锁住的范围是索引记录，和索引区间。

临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

图 2-3：
在这里插入图片描述
如上图中：1、5、6、9代表是已经有的记录主键，中间的部分是空着的。

如 select * from table where id > 2 and id < 9, 该范围内包含了存在和不存在的记录，故会锁定（1, 6]、（6,9] 区间，其他事务不能在该区间插入数据。

2.5 按模式分类

可分为悲观锁和乐观锁。其中乐观锁比较特殊，是借助业务逻辑来实现的锁机制。

2.5.1 乐观锁

乐观锁一般会在数据表中增加一个字段来表示是否被其他事务更新，比如更新状态、版本号等等。乐观假想数据不会被更新，当此次事务需要提交时候判断该条数据是否在本次事务开始后提交前被其他事务更改了。

如被更改了，则返回给业务逻辑判断如何处理。这样的优点就是能减少锁使用，提供性能。

2.5.2 悲观锁

悲观锁可以看作乐观锁的相对面，基于一种悲观的态度类来防止一切数据冲突，它是以一种预防的姿态在修改数据之前把数据锁住，然后再对数据进行读写。

在它释放锁之前任何请求都不能对其数据进行操作，直到前面锁持有者把锁释放后。其他需要对数据加锁的才可以对数据进行加锁进行操作，通常情况数据库本身锁的机制都是基于悲观锁的机制实现的。

其优缺点都很明显：
优点：可以完全保证数据的独占性和正确性。
缺点：加锁释放锁的过程会造成消耗，所以性能相对不高，且会增加产生死锁的风险。

2.6 按状态分类

可分为意向共享锁(IS)、意向排它锁(IX)。

Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table。

Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table.
Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.

从上文不难得出：共享锁/意向排他锁属于表锁，且取得意向共享锁/意向排他锁是取得共享锁/排他锁的前置条件。

获得行的共享锁必须先获得表的意向共享锁。
获得行的排他锁。

共享锁/排他锁与意向共享锁/意向排他锁的兼容性关系：

	排他锁（X）	意向排他锁（IX）	共享锁（S）	意向共享锁（IS）
排他锁（X ）	互斥	互斥	互斥	互斥
意向排他锁（IX）	互斥	可并行	互斥	可并行
共享锁（S）	互斥	互斥	可并行	可并行
意向共享锁（IS）	互斥	可并行	可并行	可并行

从上表加粗部分可看出意向锁之间不是互斥的，可以并行进行。

意向锁的存在价值在于在定位到特定的行所持有的锁之前，提供一种更粗粒度的锁，可以大大节约引擎对于锁的定位和处理的性能，因为在存储引擎内部，锁是由一块独立的数据结构维护的，锁的数量直接决定了内存的消耗和并发性能。

如：事务 A 对表 T 的进行 DML 操作（加X锁）这时也会对表 T 加意向排它锁，在事务 A 提交之前，加入有事务 B 也进行 DML 操作，此时表级别的意向排它锁就能告诉事务 B 需要等待（表 T 上有意向排他锁），而不需要再去行级别判断。

2.7 自增锁

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns.The innodb_autoinc_lock_mode configuration option controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations.

从文中不难得出：自增锁是一种特殊的表级锁，由插入到带有AUTO_INCREMENT列的表中的事务使用。

当发生类 insert 语句时候，大致上可分为几种情况分析：

2.7.1 Simple inserts
可以预先确定要插入的行数（当语句被初始处理时）的语句。这包括没有嵌套子查询的单行和多行 INSERT 和 REPLACE 语句，但不包括INSERT … ON DUPLICATE KEY UPDATE。

2.7.2 Bulk inserts
事先不知道要插入的行数（和所需自动递增值的数量）的语句。这包括INSERT … SELECT、REPLACE … SELECT和LOAD DATA语句，但不包括纯INSERT。

InnoDB 在处理每行时一次为 AUTO_INCREMENT 列分配一个新值。

2.7.3 Mixed-mode inserts

Simple inserts 中部分指定了自增值。
INSERT … ON DUPLICATE KEY UPDATE。其中 AUTO_INCREMENT 列的分配值不一定会在 UPDATE 阶段使用，所以此类语言比较浪费自增键。

innodb 提供了自增锁三种锁定模式，由参数 innodb_autoinc_lock_mode 控制。

innodb_autoinc_lock_mode=0 该模式下上面三种类型 insert 语句在开始时候都会得到一个表级别的 aotu_inc 锁，在语句结束时候才进行释放。注意是每条语句，一个事务中可能包含多条语句。基于语句复制(statement-based replication) 安全，自增值连续，并发不高。
innodb_autoinc_lock_mode=1 Innod 默认模式，该模式在兼用 0 模式下对 Simple inserts 类进行优化。Simple inserts 在插入前可以知道具体的条数，故直接生成对应记录数的连续值，语句得到了相应的值后就可以提前释放锁。基于语句复制(statement-based replication) 安全，自增值连续，并发中等。
innodb_autoinc_lock_mode=2 该模式下没有 auto_inc 锁，性能最好。但是基于二机制ROW语句复制或恢复是不安全的，mysql 推荐 binlog 为 row模式，故比较少用该模式。

三、隔离级别

隔离级别的出现是为了解决多事务并发情况下可能会产生的几种异常情况，分别是更新丢失(Lost Update)、脏读(Drity Read)、不可重复度(Non-repeatable Read)、幻读(Phantom Read)。

3.1 解决问题

更新丢失(Lost Update)
通常会出现于两种情况：回滚丢失和覆盖丢失。

脏读(Drity Read)：
又称无效数据读，即读取未提交数据，一个事务读取另外一个事务还没有提交的数据叫脏读。
总结为：读取未提交数据。

不可重复读(Non-repeatable Read)：
即在同一个事务内，相同的查询多次返回结果是不一致的。
总结为：事务中多次读取，数据内容不一致。

幻读(Phantom Read)：
总结为：事务中多次读取，数据总量不一致

其中不可重复度和幻读可能有些人认为是同样的问题，其实是不一样的。不可重复读侧重于记录的修改，幻读侧重于记录的新增或删除。

标准SQL定义了四种隔离级别，用于解决上面几种问题、支持事务的实现。分别是：读取未提交(Read Uncommitted)、读提交(Read Committed)、可重复读(Repeatable Read)、串行化(Serializable)。

其中各种隔离级别所能解决上述问题的支持情况如下表所示(Yes代表能解决；No代表不能解决)：

隔离级别	脏读	不可重复读	幻读
读取未提交(Read Uncommitted)	No	No	No
读提交(Read Committed)	Yes	No	No
可重复读(Repeatable Read)	Yes	Yes	No
串行化(Serializable)	Yes	Yes	Yes

其中可重复读(Repeatable Read) 可用了临键锁 + MVCC解决了幻读现象，下文在讲解。

3.2 浅谈隔离级别

Read Uncommitted（读未提交）
即事务间可相互读取其他事务没有提交的数据，往往会引发脏读问题。该隔离级别整体优势不是很明显，故并不常用于实际生产中。了解即可。

Read Committed（读提交）
即可读取其它事务提交的数据，部分关系型数据库默认的隔离界别（如：Oracle）。但是往往会出现 不可重复读 问题，即事务不同阶段对同一记录读取到的值是不相同的。

Repeatable Read（可重复读）
即使用视图保证事务过程中同一记录的一致，但是不能避免幻读问题的产生。该隔离级别是 MySQL （innodb、Falcon）默认隔离级别，通常使用MVCC + 临键锁来避免幻读产生。

Serializable（串行化）
即使用对数据加锁（共享、排他）实现是数据记录操作的串行执行，可解决脏读、不可重复的、幻读的问题。一致性最高但性能相对不高。

3.3 查看隔离级别

MySQL 默认的隔离级别是可重复读，可以使用以下语句进行查看。

select @@session.tx_isolation;
select @@global.tx_isolation;
 
# MySQL8 之后也可使用下面语句查看
select @@session.transaction_isolation;
select @@global.transaction_isolation;

3.4 设置隔离级别

# 可选级别：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE

# 会话级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

# 全局级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ

四、bin log

bin log 是 MySQL 的一种二进制日志，记录引起或可能（更新删除没有匹配的记录）引起数据库变动的事件信息。

bin log 以事件形式记录，不是事务日志。对于非事务表的操作，每当语句执行完成则直接写入；对于事务表的操作则会在事务提交时（先记录到缓存中）一次性写入。

4.1 用途

主要用于数据备份和恢复、主从同步、审计、数据异构、基于数据的任务分发等等。

主从同步

图 4-1：
在这里插入图片描述

slave 从服务器 I/O 线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后SQL线程会读取 relay log 日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。

数据异构
把数据按需（数据结构、存取方式、存取形式）异地构建存储。

图 4-2：
在这里插入图片描述

基于数据的任务分发
多服务依赖相同数据而不侵占其他服务的实现。

图 4-3:
在这里插入图片描述

4.1开启 bin log

MariaDB/MySQL默认没有启动二进制日志，要启用二进制日志使用 --log-bin=[on|off|file_name] 或配置文件中 log-bin=[on|filename]选项指定。

如果没有给定file_name，则默认为datadir下的主机名加"-bin"，并在后面跟上一串数字表示日志序列号，如果给定的日志文件中包含了后缀(logname.suffix)将忽略后缀部分。

mysqld 会创建一个二进制日志索引文件，当二进制日志文件滚动的时候会向该文件中写入对应的信息。所以该文件包含所有使用的二进制日志文件的文件名。

默认情况下该文件与二进制日志文件的文件名相同，扩展名为’.index’。要指定该文件的文件名使用 --log-bin-index[=file_name] 选项。

当 mysqld 在运行时不应手动编辑该文件，免得 mysqld 变得混乱或重启失败。

-rw-r-----  1 mysql mysql       340 Jun 26 09:25 bin-log.000003
-rw-r-----  1 mysql mysql        17 Jun 26 09:25 bin-log.index

最大值通过变量 max_binlog_size 设置(默认值为1G)。但由于二进制日志可能是基于事务来记录的(如innodb表类型)，而事务是绝对不可能也不应该跨文件记录的，如果正好二进制日志文件达到了最大值但事务还没有提交则不会滚动日志，而是继续增大日志，所以 max_binlog_size 指定的值和实际的二进制日志大小不一定相等。
出于安全和性能考虑，极不建议将二进制日志和datadir放在同一磁盘上。

4.2 查看 bin log

MySQL 中查看二进制日志的方法主要有几种。

1. mysqlbinlog 命令
格式：mysqlbinlog [option] log-file1 log-file2…

# 常用选项

-d,--database=name：只查看指定数据库的日志操作
-o,--offset=#：忽略掉日志中的前n个操作命令
-r,--result-file=name：将输出的日志信息输出到指定的文件中，使用重定向也一样可以。
-s,--short-form：显示简单格式的日志，只记录一些普通的语句，会省略掉一些额外的信息如位置信息和时间信息以及基于行的日志。可以用来调试，生产环境千万不可使用
--set-charset=char_name：在输出日志信息到文件中时，在文件第一行加上set names char_name
--start-datetime,--stop-datetime：指定输出开始时间和结束时间内的所有日志信息
--start-position=#,--stop-position=#：指定输出开始位置和结束位置内的所有日志信息
-v,-vv：显示更详细信息，基于row的日志默认不会显示出来，此时使用-v或-vv可以查看

在一个新开的实例中创建一个 test 数据库、test 数据表并插入一条数据为例子查看 bin log 记录情况。

root@565bf1f8399a:/var/lib/mysql# mysqlbinlog bin-log.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;

1. 初始信息，可以看到记录了时间和位置信息(at 4)。

# at 4
#220626  9:43:32 server id 1  end_log_pos 256 CRC32 0x1485ea02 	Start: binlog v 4, server v 10.5.12-MariaDB-1:10.5.12+maria~focal-log created 220626  9:43:32 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
RCq4Yg8BAAAA/AAAAAABAAABAAQAMTAuNS4xMi1NYXJpYURCLTE6MTAuNS4xMittYXJpYX5mb2Nh
bC1sb2cAAAAAAAAAAABEKrhiEzgNAAgAEgAEBAQEEgAA5AAEGggAAAAICAgCAAAACgoKAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAEEwQADQgICAoKCgEC6oUU
'/*!*/;
# at 256
#220626  9:43:32 server id 1  end_log_pos 299 CRC32 0x6468dccc 	Gtid list [0-1-6]
# at 299
#220626  9:43:32 server id 1  end_log_pos 340 CRC32 0x418a9234 	Binlog checkpoint bin-log.000002
# at 340
#220626  9:44:47 server id 1  end_log_pos 382 CRC32 0x5ad3b3b6 	GTID 0-1-7 ddl
/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=1*//*!*/;
/*!100001 SET @@session.gtid_seq_no=7*//*!*/;
# at 382
#220626  9:44:47 server id 1  end_log_pos 471 CRC32 0xd1fa3319 	Query	thread_id=4	exec_time=0	error_code=0
SET TIMESTAMP=1656236687/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;

2. 创建 test 数据库。
CREATE DATABASE `test`
/*!*/;
# at 471
#220626  9:45:20 server id 1  end_log_pos 513 CRC32 0x54820548 	GTID 0-1-8 ddl
/*!100001 SET @@session.gtid_seq_no=8*//*!*/;
# at 513
#220626  9:45:20 server id 1  end_log_pos 711 CRC32 0x408eb615 	Query	thread_id=4	exec_time=0	error_code=0

3. 创建 test 数据表。
use `test`/*!*/;
SET TIMESTAMP=1656236720/*!*/;
CREATE TABLE `test`.`test`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
)
/*!*/;
# at 711
#220626  9:45:46 server id 1  end_log_pos 753 CRC32 0x29248341 	GTID 0-1-9 trans
/*!100001 SET @@session.gtid_seq_no=9*//*!*/;

开启事务
START TRANSACTION
/*!*/;
# at 753
#220626  9:45:46 server id 1  end_log_pos 785 CRC32 0x1538c98f 	Intvar

自增锁
SET INSERT_ID=1/*!*/;
# at 785
#220626  9:45:46 server id 1  end_log_pos 903 CRC32 0xb1a7f07c 	Query	thread_id=4	exec_time=0	error_code=0
SET TIMESTAMP=1656236746/*!*/;
4. 插入一条数据
INSERT INTO `test`.`test`(`name`) VALUES ('测试')

/*!*/;
# at 903
#220626  9:45:46 server id 1  end_log_pos 934 CRC32 0x503162f1 	Xid = 51
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

2. show binary logs
用于查看当前使用了哪些二进制日志文件。

cat mysql-bin.index
show {binary | master} logs

3. show binlog events
用于查看日志中进行了哪些操作，show binlog events in ‘bin-log’;

mysql> show binlog events in 'bin-log.000002';
+----------------+-----+-------------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name       | Pos | Event_type        | Server_id | End_log_pos | Info                                                                                                                                            |
+----------------+-----+-------------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| bin-log.000002 |   4 | Format_desc       |         1 |         256 | Server ver: 10.5.12-MariaDB-1:10.5.12+maria~focal-log, Binlog ver: 4                                                                            |
| bin-log.000002 | 256 | Gtid_list         |         1 |         299 | [0-1-6]                                                                                                                                         |
| bin-log.000002 | 299 | Binlog_checkpoint |         1 |         340 | bin-log.000002                                                                                                                                  |
| bin-log.000002 | 340 | Gtid              |         1 |         382 | GTID 0-1-7                                                                                                                                      |
| bin-log.000002 | 382 | Query             |         1 |         471 | CREATE DATABASE `test`                                                                                                                          |
| bin-log.000002 | 471 | Gtid              |         1 |         513 | GTID 0-1-8                                                                                                                                      |
| bin-log.000002 | 513 | Query             |         1 |         711 | use `test`; CREATE TABLE `test`.`test`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) |
| bin-log.000002 | 711 | Gtid              |         1 |         753 | BEGIN GTID 0-1-9                                                                                                                                |
| bin-log.000002 | 753 | Intvar            |         1 |         785 | INSERT_ID=1                                                                                                                                     |
| bin-log.000002 | 785 | Query             |         1 |         903 | use `test`; INSERT INTO `test`.`test`(`name`) VALUES ('测试')                                                                                 |
| bin-log.000002 | 903 | Xid               |         1 |         934 | COMMIT /* xid=51 */                                                                                                                             |
+----------------+-----+-------------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------+

# 可以指定起始位置。同样，起始位置必须指定正确，不能指定不存在的位置。

mysql> show binlog events in 'bin-log.000002' from 711;
+----------------+-----+------------+-----------+-------------+-----------------------------------------------------------------+
| Log_name       | Pos | Event_type | Server_id | End_log_pos | Info                                                            |
+----------------+-----+------------+-----------+-------------+-----------------------------------------------------------------+
| bin-log.000002 | 711 | Gtid       |         1 |         753 | BEGIN GTID 0-1-9                                                |
| bin-log.000002 | 753 | Intvar     |         1 |         785 | INSERT_ID=1                                                     |
| bin-log.000002 | 785 | Query      |         1 |         903 | use `test`; INSERT INTO `test`.`test`(`name`) VALUES ('测试') |
| bin-log.000002 | 903 | Xid        |         1 |         934 | COMMIT /* xid=51 */                                             |
+----------------+-----+------------+-----------+-------------+-----------------------------------------------------------------+

4. show master status
用于显示主服务器中的二进制日志信息。如果是主从结构，它只会显示主从结构中主服务器的二进制日志信息。

可以查看到当前正在使用的日志及下一事件记录的开始位置，还能查看到哪些数据库需要记录二进制日志，哪些数据库不记录二进制日志。

mysql> show master status;    
+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| bin-log.000002 |      934 |              |                  |
+----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

4.3 刷新 bin log

可使用命令：mysqladmin -uroot -p refresh

4.4 删除 bin log

1. reset master
删除所有日志，并让日志文件重新从000001开始。

2. PURGE { BINARY | MASTER } LOGS { TO ‘log_name’ | BEFORE datetime_expr }
purge master logs to “binlog_name.00000X” 将会清空00000X之前的所有日志文件，正在使用中的日志文件中，将无法进行 purge。

1.例如删除000004之前的日志文件：

mysql> purge master logs to "mysql-bin.000004";

2.删除指定日期之前的所有日志：

purge master logs before '2022-06-25 12:36:40';

3. --expire_logs_days=N
使用–expire_logs_days=N选项指定过了多少天日志自动过期清空。

4.5 记录格式

MySQL 5.1之前，只有一种基于语句 statement 形式的日志记录格式。即将所有的相关操作记录为SQL语句形式。

但是这样的记录方式对某些特殊信息无法同步记录，例如uuid，now()等这样动态变化的值。

MySQL 5.1开始，MySQL支持 statement、row、mixed 三种形式的记录方式，由变量 binlog_format 来指定。

row 形式是基于行来记录，也就是将相关行的每一列的值都在日志中保存下来，这样的结果会导致日志文件变得非常大，但是保证了动态值的确定性。 innodb引擎的创始人之一推荐使用row格式。
mixed 形式，如何记录日志由MySQL自己来决定。

以本节第二小节内容为例，各种格式如下所示：

4.5.1 statement

图 4-4:
在这里插入图片描述

4.5.2 row格式

记录内容不可直接阅读，需要加 -vv 选项。记录的非常详细，所以日志文件相对大。

图 4-5:
在这里插入图片描述

4.5.3 mixed格式

默认会采用 statement 的方式记录，部分特殊情况会采用row的形式来记录日志。

存储引擎为NDB，对表的DML操作都会以row的格式记录。
使用了uuid()、user()、current_user()、found_rows()、row_count()等不确定函数。
使用了insert delay语句。
用了临时表。

mysqlbinlog mysql-bin.000001 -vv

4.6 刷新时机

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

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

图 4-6:
在这里插入图片描述

需要注意上图中标红的两个写操作，设置不当可能导致日志丢失。两个写入时机由参数：sync_binlog 控制。

sync_binlog=0 ：每次提交事务都只写到 page cache，由系统自行判断什么时候执行fsync 到 binlog 文件。若机器宕机，page cache 里面的 binglog 会丢失。
sync_binlog=1：每次提交事务都会执行 fsync，要保证事务的一致性和持久性的时候，必须将sync_binlog的值设置为1。
sync_binlog=N(N >= 1)：每次提交事务都只写到 page cache，但累积N个事务后才fsync。若机器宕机，page cache 里面的 N个事务的 binglog 会丢失。

4.7 相关参数

#指定是否启用记录二进制日志或者指定一个日志路径(路径不能加.否则.后的被忽略).
log_bin = {on | off | base_name} 

#指定是否启用记录二进制日志，只有在log_bin开启的时候才有效
sql_log_bin ={ on | off } 

#指定自动删除二进制日志的时间，即日志过期时间
expire_logs_days = 

#明确指定要记录日志的数据库
binlog_do_db = 

#指定不记录二进制日志的数据库
binlog_ignore_db = 

#指定mysql-bin.index文件的路径
log_bin_index = 

#指定二进制日志基于什么模式记录
binlog_format = { mixed | row | statement } 

# MySQL5.6.2添加了该变量，当binlog format为row时，默认不会记录row对应的SQL语句，设置为1或其他true布尔值时会记录，但需要使用mysqlbinlog -v查看，这些语句是被注释的，恢复时不会被执行。
binlog_rows_query_log_events = { 1|0 }

#指定二进制日志文件最大值，超出指定值将自动滚动。但由于事务不会跨文件，所以并不一定总是精确。
max_binlog_size = 

#基于事务类型的日志会先记录在缓冲区，当达到该缓冲大小时这些日志会写入磁盘。
binlog_cache_size = 32768 

#指定二进制日志缓存最大大小，硬限制。默认4G，建议不要改。
max_binlog_cache_size = 

#一般等同于且决定binlog_cache_size大小，所以修改缓存大小时只需修改这个而不用修改binlog_cache_size
binlog_stmt_cache_size = 32768

#binlog_cache_size的值
binlog_stmt_cache_disk_use: 

#使用缓存写二进制日志的次数(这是一个实时变化的统计值)
binlog_cache_use：

#使用临时文件写二进制日志的次数，当日志超过了binlog_cache_size的时候会使用临时文件写日志，如果该变量值不为0，则考虑增大binlog_cache_size的值。
binlog_cache_disk_use:

#使用缓存写二进制日志的次数
binlog_stmt_cache_use：

#使用临时文件写二进制日志的次数，当日志超过了binlog_cache_size的时候会使用临时文件写日志，如果该变量值不为0，则考虑增大

#这个参数直接影响mysql的性能和完整性
sync_binlog = { 0 | n } 
	sync_binlog=0: 不同步，日志何时刷到磁盘由FileSystem决定，这个性能最好。
	sync_binlog=n: 每写n次事务(注意，对于非事务表来说，是n次事件，对于事务表来说，是n次事务，而一个事务里可能包含多个二进制事件)，MySQL将执行一次磁盘同步指令fdatasync()将缓存日志刷新到磁盘日志文件中。Mysql中默认的设置是sync_binlog=0，即不同步，这时性能最好，但风险最大。一旦系统奔溃，缓存中的日志都会丢失。

要保证事务的一致性和持久性的时候，必须将sync_binlog的值设置为1。
因为每次事务提交都会写入二进制日志，设置为1就保证了每次事务提交时二进制日志都会写入到磁盘中，从而立即被从服务器复制过去。

要保证事务的一致性和持久性的时候，必须将sync_binlog的值设置为1。
因为每次事务提交都会写入二进制日志，设置为1就保证了每次事务提交时二进制日志都会写入到磁盘中，从而立即被从服务器复制过去。

4.8 恢复重做

指定二进制日志的起始位置（可指定终止位置）并将其保存到 sql 文件中，由 mysql 命令来载入恢复即可。当然也可以直接通过管道送给mysql。

基于位置来恢复还是基于时间点来恢复，这两种行为都可以。选择时间点来恢复比较直观些，并且跨日志文件恢复时更方便。

mysqlbinlog --stop-datetime="2022-06-25 12:17:44" /tmp/mysql-bin.000005 | mysql -u user -p password

# 恢复多个二进制日志文件时：
mysqlbinlog mysql-bin.[*] | mysql -uroot -p password


# 入到一个文件中后恢复。
mysqlbinlog mysql-bin.000001 > /tmp/1.sql
mysqlbinlog mysql-bin.000002 >>/tmp/2.sql
mysql -u root -p password -e "source /tmp/1.sql"

五、redo log

和 bin log 不同，redo log 不是二进制日志。它是物理日志，记录数据页的物理修改。用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。

redo log 是 innodb 为了支持崩溃恢复而出现的，只记录 innodb 存储引擎中表的修改。

bin log 和 inndb 总的来说有如下不同：

层面不同 bin log MySQL 本身实现的日志模块，而 redo log 是 innodb 引擎层实现的。
记录内容不同 bin log 记录的是逻辑性语句，即便是行格式形式。而 redo log 记录在物理上更改的日志，它记录的是数据库中每个页的修改。
写入形式不同 bin log 每次(看具体写入时机)事务提交的时候一次性写入缓存中，而 redo log 是分为两阶段写入：1. 准备阶段写如 redo log 缓存中。2. 然后当 innodb buffer 数据更新完成且事务提交后，再向 redo log 缓存中写入提交动作。也就是两阶段提交，后续章节会详解。
写入时机不同 bin log 在事务提交的时候一次性写入，故日志中的记录方式和提交顺序有关，且一次提交对应一次记录。而 redo log 记录的是物理页的修改，同一个事务中可能有多次记录。最后提交的事务会覆盖所有未提交的事务记录。如有事务A 有多个版本的操作记录 v-1、v-2、v-3，v-3 代表的记录是最后的操作结果，所以数据页最终的结果就是 v-3 操作的。redo log 是并发写入的，不同事务之间的不同版本的记录会穿插写入到 redo log文件中。
redo log 具有幂等性，而 bin log 是记录所有影响数据的操作，记录的内容较多。

5.1 用途

如名所示，redo log 是重做日志，提供前滚操作。让 inndb 具有崩溃恢复能力从而支持事务，保证数据的持久性与完整性。

5.2 redo log 构成

redo log 由两部分组成：1. 处于内存中的日志缓存（redo log buffer）; 2. 位于磁盘中的重做日志文件（redo log file）。

如下图所示：

图 5-1:
在这里插入图片描述

redo log 以块为单位进行存储的，每个块占512字节，称之为 redo log block。不管是存于缓存中 redo log 还是位于磁盘文件中的 redo log 都是以512字节为一块存储的。

日志块（log block）
每个redo log block由3部分组成：日志块头、日志块尾和日志主体。

其中日志块头占用12字节，日志块尾占用8字节，所以每个 redo log block 的日志主体部分只有512-12-8=492字节。

图 5-2:
在这里插入图片描述

日志块头包含4部分：

log_block_hdr_no：(4字节) 记录该日志块在redo log buffer中的位置ID。
log_block_hdr_data_len：(2字节) 记录该log block中已使用的log大小。写满该log block时为0x200，表示512字节。
log_block_first_rec_group：(2字节) 记录该log block中第一个log的开始偏移位置。
lock_block_checkpoint_no：(4字节) 记录写入检查点信息的位置。

日志组（log group）

log group 表示的是 redo log group，一个组内由多个大小完全相同的 redo log file 组成。

组是一个逻辑的概念，并没有真正的文件来表示这是一个组，但是可以通过变量 innodb_log_group_home_dir 来定义组的目录，redo log file 都放在这个目录下，默认是在datadir下。

组内 redo log file 的数量由变量 innodb_log_files_group 决定，默认值为2，即两个 redo log file。

-rw-r-----  1 mysql mysql 100663296 Jun 26 09:45 ib_logfile0
-rw-r-----  1 mysql mysql 100663296 Jun 26 09:45 ib_logfile1

在 innodb_log_group_home_dir 配置的路径下可以看到两个 ib_logfile 开头的文件，它们就是 log group 中的 redo log file。大小完全一致且等于变量 innodb_log_file_size 定义的值。

innodb 将 log buffer 中的 redo log block 刷到 log file 中时，会以追加写入的方式循环轮询写入。

即先在第一个log file（即ib_logfile0）的尾部追加写，直到满了之后向第二个log file（即ib_logfile1）写。当第二个log file满了会清空一部分第一个log file继续写入。

一个完整的 redo log block 如下图所示：

每个组的第一个 redo log file 前 2KB 不用来存储 log block, 而是用于记录一些特定的信息。
除了第一个 redo log file 会记录之外，同组的其他 file 会空出这部份空间不做使用。

图 5-3:

在这里插入图片描述

5.3 redo log 格式

众所周知 Innodb 是以页为单位存储数据，redo log 也是一样的（buffer 和磁盘中均是如此）。 innodb 默认页大小是 16 KB , 所以是一个页是可以存储很多 log block（图 5-2 呈现 log block 的结构）。

每个 log block 中有 492 字节是可以用来存储数据页变化信息的 body 部分，其可分为 4 部分：

redo_log_type：（1字节）日志类型。
space ：（4字节）表空间ID。
page_no：页偏移量。
redo_log_body： redo log 真正存储数据部分。

尽管大致上结构是一致的，但是如 insert 和 delete 还是有着些差异：

图 5-4:
在这里插入图片描述

5.3 刷盘时机

我们都知道 redo log 是先写到 redo log buffer 中，由参数 innodb_log_buffer_size 控制 redo log buffer 大小，默认 16 MB。这个值其实已经够大了，毕竟是记录页的物理修改信息。

未刷到磁盘的日志称为脏日志(dirty log)，满足一些时机就会触发持久化到磁盘中。其中以下几种时机会触发写入到磁盘中：

由参数 innodb_flush_log_at_trx_commit 设置，如 =1 代表事务每次提交的时候都会刷事务日志到磁盘中。
由参数 innodb_flush_log_at_timeout 设置，默认 1 秒刷新一次到磁盘中，该频率不受事务是否提交的影响。
已使用的内存超过 redo log buffer 一半时。
checkpoint 动作发生时，该内容较为重要将在下一小节详细介绍。
MySQL 正常关机时。

5.4 checkpoint

checkpoint 是一个将 buffer pool 中脏数据页和脏日志页刷到磁盘的动作，因为 buffer pool 的容量是有限的，不可能将所有 redo log 存放在缓冲池中。

分为两种类型：

sharp checkpoint ：重用redo log文件(例如切换日志文件)的时候，将所有已记录到 redo log 中对应的脏数据刷到磁盘。
fuzzy checkpoint ：每次只刷部分到磁盘，其中又分为几种情况：
2.1 master thread checkpoint：master线程控制，每秒或每10秒刷入一定比例的脏页到磁盘。
2.2 flush_lru_list checkpoint：MySQL5.6开始可通过参数 innodb_page_cleaners 指定脏页刷盘的 page cleaner 线程的个数，该线程的目的是为了保证 lru 列表有可用的空闲页。
2.3 async/sync flush checkpoint：异步/同步刷盘。
2.4 dirty page too much checkpoint：过多的脏页触发刷盘，由参数 innodb_max_dirty_pages_pct 控制。如：MariaDB-10 值为 90 ，代表脏页使用缓冲池 90% 将强制将部分脏页刷到磁盘中。

可以用下图来描述 redo log 和 checkpoint 动作是如何循环写的。假如启用了 4个 redo log，从 0 号文件向 3 号文件方向写，其中：

write pos 是当前记录的位置，一边写一边后移。
check point 是当前要刷盘的位置，也是往后推移并且循环的。
write pos 和 checkpoint 之间绿色部分是代表空闲的，黄色部分代表已经写满了脏页，等待刷入磁盘。

图 5-5:

在这里插入图片描述
write pos 表示 redo log 当前记录的 LSN (逻辑序列号) 位置，一边写一遍后移。
check point 表示数据页更改记录刷盘后对应 redo log 所处的 LSN (逻辑序列号) 位置，也是往后推移并且循环的。

5.5 LSN 分析

LSN (log sequence number) 日志的逻辑序列号，占用 8 个字节，其值会随日志的不断写入而增加。

用于实现 crash-save ，如 innodb 重启时会检查磁盘中数据页的 LSN，如小于日志中 check point （见图 5-5）的 LSN 。则将会重 check point 点开始重放恢复数据。

LSN 存在于数据页（包括 buffer pool 和磁盘中）、redo log（包括 buffer pool 和磁盘中）。

使用 show engine innodb status 查看当前各种 LSN 情况：

mysql> show engine INNODB STATUS;
...
# 省略一些与本节不相关的的信息

LOG
---
Log sequence number 59816
Log flushed up to   59816
Pages flushed up to 56169
Last checkpoint at  53898
0 pending log flushes, 0 pending chkp writes
17 log i/o's done, 0.40 log i/o's/second

从上面信息可直观的到有 4 种 LSN：

Log sequence number： redo log 当前在 buffer 中的 LSN。
Log flushed up to：redo log 当前刷到磁盘中的 LSN。
Pages flushed up to：数据页当前刷到磁盘中的 LSN。
Last checkpoint at：上一次检查点所在位置的 LSN。

为了便于直观了解上述请在在一个事务过程中的情况，下面将定义几种 LSN 并体现在一个时序图中情况。

data_lsn_in_buffer : buffer 中数据页 LSN。事务开始修改 buffer 中数据页，并在 buffer 中记录数据页 LSN。
data_lsn_on_disk: 磁盘中数据页 LSN。
redo_log_lsn_in_buffer：buffer 中 redo log LSN。修改数据页的同时，innodb 往 redo log in buffer 中写入redo log，并记录下对应的LSN 。
redo_log_lsn_on_disk：磁盘中 redo log LSN。触发刷盘时机并将 buffer 中 redo log 刷到磁盘中。
check_point_lsn：触发 checkpoint 将内存中的脏页(数据脏页和日志脏页)刷到磁盘，并在完成时在 redo log 中记住 checkpoint LSN 。

图 5-6:

在这里插入图片描述

分析上图：
事务开始前，假设此时日志页和数据页都是全部刷到磁盘中，此时上面 4 种 LSN 的值都是相同的并等于 9 。

注意一个知识点： log flushed up to 和 pages flushed up to 的大小无法确定，所以 redo log 和数据页刷盘快慢是不确定的。但是 checkpoint 机制会控制数据页刷盘速度慢于 redo log ，当数据页刷盘速度超过 redo log 时，将会暂时停止数据页刷盘，等待 redo log 刷盘进度超过数据刷盘。

事务开始并执行了一个 insert 操作，此时在 buffer 中的数据页和 redo log 都记录了插入后的新值 10。这时 4 种 LSN 的关系如下：

log sequence number(10) > log flushed up to(9) = pages flushed up to(9) = last checkpoint at(9)

在时间点-1(00:00:01) 和时间点-2(00:00:02) 间执行了更新操作，一秒后的时间点-2(00:00:02)触发了 redo log 一个刷盘机制（假设 innodb_flush_log_at_timeout=1），此时 redo log 在 buffer 和磁盘中 LSN 是一致的，数据页还没刷盘故是小于 redo log 中。这时 4 种 LSN 的关系如下：

log sequence number(11) = log flushed up to(11) > pages flushed up to(10) = last checkpoint at(10)

在时间点-2(00:00:02) 和时间点-3(00:00:03) 间执行了删除操作，LSN 增加到 12。在时间点-4位置发生了 check point ，此时会将数据页和 redo log 进行刷盘。时间点-4～时间点-5之间是刷盘所需要的时间，在此期间 checkpoint 的 LSN 还是上一次的，但此时磁盘中数据页和 redo log 中 LSN 已经是新的 12。这时 4 种 LSN 的关系如下：

log sequence number > log flushed up to  # 在此期间 redo log 在 buffer 中 LSN 是大于磁盘中的。
pages flushed up to > last checkpoint at # 在此期间 数据页的 LSN 是大于 checkpoint LSN (还停留在上一次的)。

在时间点-5(00:00:05) 时数据页和 redo log 页刷盘完毕，所有 LSN 都是一致的。
在时间点-6(00:00:06) 时发生了插入操作，LSN 增加为 13。接下来的时间点-7时的各种 LSN 状态和在时间点-1时一致。
在时间点-8(00:00:08) 时事务提交了，默认情况下会触发 redo log 刷盘，但是不会触发数据页刷盘。这时 4 种 LSN 的关系如下：

log sequence number(13) = log flushed up to(13) > pages flushed up to(12) = last checkpoint at(12)

5.6 基于 checkpoint 恢复

需要知道的是：innodb 在每次启动时，不管上次关闭是正常还是异常的，都会进行尝试恢复操作。

innodb 启动时，磁盘中 checkpoint 代表已经完整刷到磁盘中数据页的 LSN，所以恢复时仅需从 checkpoint 开始的部分。

如：上次的 checkpoint LSN 为 100，且事务是已经提交状态（没有提交就没有必要恢复，不过也要看位于两阶段提交的那个时段）。此时发生宕机，在启动时候数据库会检查数据页中的 LSN，若小于 redo log 中的 LSN，则会从 checkpoint 进行重放 redo log 进行恢复。

如：checkpoint 时发生宕机且数据页刷盘进度大于 redo log 刷盘进度，此时数据页中 LSN 必然是大于 redo log 中 LSN。此种情况在恢复时候判断超过 redo log LSN 的部分不会进行重做（没有必要重放）。

5.7 相关参数

innodb_flush_log_at_trx_commit={0|1|2} 指定何时将事务日志刷到磁盘，默认为1。
0 表示每秒将 log buffer 同步到 os buffer 且从 os buffer 刷到磁盘日志文件中。
1 表示每个事务提交都将 log buffer 同步到 os buffer 且从 os buffer 刷到磁盘日志文件中。
2 表示每个事务提交都将 log buffer 同步到 os buffer，但每秒才从 os buffer 刷到磁盘日志文件中。
innodb_log_buffer_size： log buffer 的大小，默认 16M。
innodb_log_file_size：事务日志的大小，默认 10M
innodb_log_files_group =2：事务日志组中的事务日志文件个数，默认2个
innodb_log_group_home_dir =./：事务日志组路径，当前目录表示数据目录

六、两阶段提交

6.1 背景

两阶段提交涉及到前两小节bin log、redo log，之所以叫两阶段提交是因为在事务发生的期间需要对两个 log 进行写操作。

redo log 拥有 crash-safe 能力，bin log 则只有能提供如恢复到某个时刻的能力（不小心删了一写数据，则可以使用 binlog 进行恢复。）

为了便于理解，假如进行一个数据更新的操作，两阶段提交则体现于下图所示：

图 6-1：

在这里插入图片描述

6.2 单独写不行吗

要验证这个问题，可以先看看单独分开写会发生什么问题。我们模拟一个主从场景：主库A、从库B。

先写 bin log 再写 redo log

假如主库A bin log 写完发生 crash，redo log 还没写入。但是从库B 可能已经同步到主库A 的 bin log ，此时主从就不一致了；主库A 若之后使用 bin log 来恢复也会多了一个记录。

先写 redo log 再写 bin log

假如主库A redo log 写完发生 crash，bin log 还没写入，但是主库A 可以在重启时候使用 redo log 进行恢复。

此时主库A bin log 和从库B 相对主库A 的redo log 来说就少了一个事务记录，所以数据也是不一致的。

6.3 两次写就能解决问题吗

是的，分两次写就能解决数据不一致问题。如下图，我们分析在两个阶段发生异常时，怎么保证数据一致性。

假设位置 1 发生 crash：即写入 redo log，此时 bin log 还没写且 redo log 处于 prepare 状态。此时启动数据库崩溃恢复时会回滚事物，保证了数据的一致性。

假设位置 2 发生 crash：即 redo log 和 bin log 都写入完成，此时启动数据库崩溃恢复时会检查 bin log 中事务是否完整（ 页断裂 partial write，该问题将会在双写章节讲解），若完整继续提交事务，否则进行回滚。

可能有人会想 redo log commit 时发生 crash，怎么处理？其实和位置 2 情况一样。

图 6-2：
在这里插入图片描述

6.4 基于两阶段恢复原理

启动时扫描最后一个 bin log 文件，提取其中的 xid_log_event 位于 redo log check point 点后的 redo log，读取事务的 undo 段信息，搜集处于 prepare 阶段的事务链表。

将事务的 xid 与 bin log 中的 xid 对比，若存在，则提交事务，否则回滚事务。

七、undo log

7.1 用途

undo log 是 innodb 实现，总的来说提供两个作用：回滚和多版本控制(MVCC)。是事务特性的重要组成部分，在数据发生更新操作时候（INSERT、DELETE、UPDATE）时会产生 undo 记录。 先于 redo log 被记录。

事务回滚：这里的回滚不仅代表程序进行的主动回滚，也包括比如事务进行中数据库宕机后恢复时对部分不满足继续提交事务条件的事务进行回滚。
MVCC：提供快照读条件，以实现非锁定一致性读。如一个事务读被其他事务占用的记录时候，可以通过 undo log 链拿到之前的数据。

与 redo log 不同的是 undo log 是逻辑日志，可以勉强理解为 redo log 的相反操作。例如 redo log 的 insert 对应着 undo log delete 操作。

7.2 相关参数

不同于之前章节将参数部分放到小节最后，本节需要先了解部分相关参数后会对后面的内容有一定的理解帮助。

与之相关的参数大致如下所示（5.6 之前版本有些差异，不在本文讨论范围了）：

mysql> show variables like '%undo%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_max_undo_log_size | 10485760 |
| innodb_undo_directory    | ./       |
| innodb_undo_log_truncate | OFF      |
| innodb_undo_logs         | 128      |
| innodb_undo_tablespaces  | 0        |
+--------------------------+----------+

mysql> show global variables like '%truncate%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| innodb_purge_rseg_truncate_frequency | 128   |
+--------------------------------------+-------+

参数解答：

innodb_undo_tablespaces：是否启用独立 undo log 表空间。
= 0：开启，此时 undo log 存放于数据文件 ibdata 中。
>= 1：不开启，此时 undo log 存放于 innodb_undo_directory 目录下如：undo001、undo002 的独立文件中。其大小由参数 innodb_max_undo_log_size 控制，truncate 默认 10MB。
注意：该参数必须在实例初始化时候指定，之后不能更改。
何时启用：比如实例写压力大，此时可以考虑和数据文件分离出来。
innodb_max_undo_log_size：设置单个独立 undo log 表空间大小。
innodb_undo_directory：设置独立 undo log 表空间文件目录。
innodb_undo_logs：自定义 rollback segment（段）数量。
innodb_undo_log_truncate：开关状态控制 purge 线程进行空间回收和 undo file 的重新初始化，该线程触发会受 innodb_max_undo_log_size 、truncate 频率等影响。
必要条件：已设置独立表空间且独立表空间个数大于等于2个。
innodb_purge_rseg_truncate_frequency：控制 purge 回滚段的频度，默认为128。代表 innodb Purge 操作协调线程 purge事务128次时，就会触发一次 History purge，检查当前的 undo log 表空间状态是否会触发 truncate。

truncate undo log file 过程：

purge 线程进行 truncate undo log file 时，会检查该文件上是否还有活动事务。

如果没有，将 undo log file 标记为不可分配状态，此时新产生的 undo log 将记录到其他文件上，所以至少需要2个独立表空间文件，才能进行 truncate 操作。

当标注不可分配后，会创建一个独立的文件 undo_<space_id>_trunc.log，记录现在正在 truncate 某个 undo log文件。

然后开始初始化 undo log file 到10MB，truncate 完成后删除表示 truncate 动作的 undo_<space_id>_trunc.log 文件。

该文件保证了即使在 truncate 过程中发生了故障宕机重启数据库服务，重启后数据库会继续完成 truncate 操作。

完成 truncate 后删除文件，标识该 undo log file 可分配。

7.3 存储位置

和 redo log 类似，undo log 也是分布在 buffer 和磁盘文件中。磁盘中 undo log 默认存放在共享表空间中，但是开启独立表空间（innodb_file_per_table）则会放到对应的各个独立表空间中。

当然也可以使用 innodb_undo_tablespaces 将 undo log 从数据文件中抽离出来，单独存放于 undo 独立表空间，当然 mysql 不建议这样操作。

图 7-1:
在这里插入图片描述

7.4 DELETE / INSERT操作机制

数据的变更分为两类：1. INSERT 操作在事务提交前只对当前事务可见，所以产生的Undo日志可以在事务提交后直接删除；2. UPDATE、DELETE 操作需要维护多版本信息，两着被归成一类，即 update_undo。

7.4.1 TRX_UNDO_INSTER_REC

insert 操作对应的 undo 日志类型，进行 insert 操作时，InnoDB会向聚簇索引和所有二级索引都插入一条记录。但是 undo 日志只会记录一条针对聚簇索引的日志。因为聚簇索引记录和二级索引记录是一样的，所以在进行回滚时会根据主键值把聚簇索引和二级索引中相应的记录都删掉。

图 7.2:
在这里插入图片描述

7.4.2 TRX_UNDO_DEL_MARK_REC

delete 操作对应的 undo 日志类型，删除记录需要进行两个阶段：

delete mark 阶段：将需要删除记录的 deleted_ flag 标识位设置为 1，此时该记录是处于一个中间状态，既不是正常可用记录也不是已删除记录。在事务提交之前都是处于这种状态，用于实现 MVCC。
purge 阶段：在事务提交之后，会有一个 purge 线程将数据删除掉。将该记录从正常记录链表中移除，并移入垃圾链表中。

在两个阶段完成后，一条记录才算真正删除，且占用的空间将可能在之后被重新使用。事务被提交后就不需要回滚了，所以回滚只会存在 delete mark 阶段。

TRX_UNDO_DEL_MARK_REC 类型 undo log 结构如下：

图 7.3:
在这里插入图片描述
从上图中得出：和 TRX_UNDO_INSERT_REC 类型不同点中多了一个 索引列各列信息的内容（len of index_col_info），即某个列被包含在某个索引中，则相关信息就会被记录到这个索引列各列的信息部分。

此处的信息即为：列在记录中的位置（pos）、列占用的存储空间大小（len）、列实际值（value），用于在事务提交后，purge 阶段进行数据删除。

7.4.3 TRX_UNDO_UPD_EXIST_REC

update 操作需要根据是否更新主键分为不同的情况处理，两种方式的实现是不一样的。

7.4.3.1 不更新主键

就地更新（in-place update）

就地更新即被更新的记录在更新前后占用的存储空间一样大，则可以在原记录上修改。只要有一列更新前后不一样大都不能进行就地更新。

先删再增

当不满足就地更新条件就需要先将该记录删掉，然后在由更新后的值创建一条新的记录。当然主键值是不变的。

需要注意的是：这里的删除不是上面 TRX_UNDO_DEL_MARK_REC 情况，没有两个阶段。而是在更新当前线程进行真正删除。

若更新后的记录大小不超过删掉的原有记录大小，则直接复用原有记录的存储空间。
若更新后的记录大小超过删掉的原有记录大小，则需要申请一块新的空间。若本数据页没有可用的空间，则进行页分裂，在插入更新后的记录。

TRX_UNDO_UPD_EXIST_REC 类型 undo log 结构如下：

图 7.4:

在这里插入图片描述
n_updated：代表被更新的列信息，＜pos,old_len,old_value＞列表中的 pos、old_len 和 old_value 分别表示被更新列在记录中的位置、更新前该列占用的存储空间大小、更新前该列的真实值。
len of index_col_info：若被更新的列包含索引列，则会被添加到此部分。

7.4.3.2 更新主键

如更新语句涉及到主键，则将会进行两个阶段：

对旧记录进行 delete mark 操作：在事务提交前对旧记录进行且只能执行一个 delete mark 操作，事务提交后在由 purge 线程删除。可能其他事务会使用到改行数据，索引和 TRX_UNDO_DEL_MARK_REC 一样，为了支持 MVCC 是先标记为 delete mark 状态。
新增新记录：根据更新后的值创建一条记录，因为主键更改了，所以将会根据新的主键值找到应该在聚簇索引中的位置并插入进去。

需要注意的是：更新主键的情况会记录两条 undo log，即 delete mark 操作时记录一条类型为 TRX_UNDO_DEL_MARK_REC 的 undo log；插入新记录时记录一条类型为 TRX_UNDO_INSERT_REC 的 undo log。