【每日八股】复习 MySQL Day4：日志（一）

昨日内容复习

MySQL 有哪几种类型的锁？

按锁的粒度划分

全局锁

• FLUSH TABLES WITH READ LOCK（FTWRL）：锁定整个数据库，所有数据表进入可读状态，常用于数据库备份。

表级锁

• 普通表锁：LOCK TABLES ... READ/WRITE；
• 意向锁：进一步分为意向共享锁（IS）和意向排他锁（IX）；
• 元数据锁：读写数据表时，自动加锁，避免表结构的更改。

行级锁（InnoDB 特有）

• 记录锁：锁定索引中的单条记录；
• 间隙锁：锁定索引记录间的间隙；
• 临键锁：锁定当前记录以及当前记录与前一条记录之间的间隙，锁定的区间左开右闭，如果左侧没有记录，则锁定整个左侧区间。
• 插入意向锁：特殊的间隙锁，一个事务向表中插入数据之前需要先查看是否要插入的区间有间隙锁，如果有，那么当前插入操作阻塞，生成一个插入意向锁，代表该事务想要插入数据，但是目前处于等待状态。

按锁的模式分类

「注意」：意向锁和排他锁指的是锁的两种模式，任何确保数据表中数据不产生冲突的锁都具有意向锁和排他锁两种模式。比如普通表锁可以是共享锁，也可以是排他锁；行锁可以是共享锁，也可以是排他锁。在 InnoDB 数据引擎中，意向锁默认是行锁。

共享锁（S 锁）

• 又称为读锁，通过 SELECT .. LOCK IN SHARE MODE 加锁；
• 允许多个事务同时获取。

排他锁（X 锁）

• 又称为写锁，通过 SELECT ... FOR UPDATE 获取；
• 排他锁一次只能为一个事务持有。

特殊锁类型

自增锁

• 用于自增列的插入操作，是特殊的表级锁。

谓词锁

• 串行化隔离级别下生效，锁定满足特定搜索条件的行。

用一句话总结 MySQL 锁的分类

按照锁的模式划分，可以分为共享锁和排他锁。共享锁又称为读锁，通过 SELECT ... LOCK IN SHARE MODE 加锁，允许多个事务同时获取；排他锁又称为写锁，通过 SELECT ... FOR UPDATE 加锁，一次只允许一个事务持有。

按锁的粒度划分，锁可以细分为全局锁、表级锁和行级锁。全局锁通常用于数据库的备份，表级锁分为普通表锁、意向锁以及元数据锁，行锁分为记录锁、间隙锁、临键锁以及意向插入锁。

意向锁的作用是什么？什么时候需要加意向锁？

意向锁是 InnoDB 特有的表级锁，用于协调不同事务加行锁可能产生的冲突。

具体来说，意向锁进一步被划分为意向共享锁（IS）和意向排他锁（IX）。意向共享锁（IS）表示事务准备在数据表的某些行加共享锁，意向排他锁（IX）表示事务准备在数据表的某些行加排他锁。

事务在加共享锁或排他锁之前，必须先获取意向锁，获取意向锁这个表锁之后，才能加具体的行锁，因此意向锁可以避免死锁的情况产生，同时避免事务逐行到数据表中查看记录是否加锁。

MySQL 的全局锁有什么作用？

MySQL 的全局锁用于数据库备份场景下的数据保护，加全局锁后所有数据表只读。

可以通过 MySQL 的可重复读隔离级别下的 MVCC 来优化数据库非常大时的备份场景，避免只读带来的业务停滞。

MySQL 如何加锁？

锁的触发时机

• 自动加锁：DML 语句时自动获取；
• 手动加锁：SELECT ... FOR UPDATE 加排他锁，SELECT ... LOCK IN SHARE MODE 加共享锁；
• DDL 加锁：表结构变更时自动加锁。

加锁的基本步骤

1. 解析 SQL 语句确定需要加锁的表或行；
2. 加意向锁（表锁），是加 IS 锁还是 IX 锁根据 SQL 语句确定；
3. 根据隔离级别和查询条件确定锁的类型及加锁范围；
4. 到存储引擎层加锁；
5. 记录锁信息到内存结构。

不同操作的加锁规则
SELECT 语句

• 普通 SELECT：快照读，不加锁；
• SELECT ... FOR UPDATE：加行级排他锁；
• SELECT ... LOCK IN SHARE MODE：加行级共享锁。

「如何确定行级锁的类型」：当查询使用唯一索引且查询条件精准匹配时，使用记录锁；当查询使用非唯一索引或范围查询时，使用临键锁；当查询没有使用索引时，退化为表锁，对整张表进行锁定并遍历整张表进行查询。

DML 语句

• INSERT 语句：首先在表级别加「意向排他锁」，之后尝试获取「插入意向锁」，最后执行插入操作，插入新的数据之后加「记录排他锁」；
• UPDATE 语句：首先在表级别加「意向排他锁」，对扫描到的索引记录加「临键排他锁」，如果使用唯一索引精准匹配，则退化为「记录排他锁」；
• DELETE 语句：首先在表级别加「意向排他锁」，对扫描到的索引记录加「临键排他锁」，如果使用唯一索引精准匹配，则退化为「记录排他锁」，整体流程与 UPDATE 语句相同。

INSERT 语句为什么要在数据插入之前加意向排他锁？数据插入之后为什么要对数据加记录排他锁？

在插入位置之前加插入意向锁
首先，插入意向锁是行锁，它是一种简化的「间隙锁」，插入意向锁首先需要检查请求插入数据的位置是否已经加了间隙锁，如果没有加间隙锁，那么成功地执行插入请求，否则插入操作会被阻塞。插入意向锁可以有效地避免幻读现象的发生。

对新插入的记录加排他锁
可以确保当前事务执行完成之前，新插入的记录对其他事务不可见，确保了事务的原子性。此外，排他锁确保了并发写场景下插入的主键唯一。

复习 MySQL Day4：日志（一）

简述 MySQL 的三种日志？

undo log（回滚日志）

undo log 是 InnoDB 存储引擎层生成的日志，实现了事务的原子性，主要用于事务回滚和 MVCC。（换句话说，如何确保事务的原子性？通过 InnoDB 存储引擎层生成的 undo log 来完成）

在事务还没有提交之前，InnoDB 会记录更新前的数据到 undo log，回滚时利用 undo log 来完成。每当进行一条记录的 DML 操作时，需要把回滚时必要的步骤都记录在 undo log 中，大致的原理是执行一条相反的操作。

InnoDB 也通过 undo log + Read View 来实现 MVCC 多版本控制。

redo log（重做日志）

redo log 是 InnoDB 存储引擎生成的物理日志，它的作用是确保事务的持久性。具体来说，当一个事务执行完毕时，会先写 redo log 再写数据，这样做可以让所有数据修改先在内存的缓冲池中完成，脏页（修改过的数据页）由后台线程异步刷盘；还可以确保在数据库因为故障宕机重启之后，可以根据 redo log 来重放未刷盘的操作，确保事务的持久性。

值得注意的是，redo log 是通过固定大小文件组循环使用的，日志大小的空间是固定的，如果写满了就从头开始重新写，其主要用途是记录事务提交但还没有刷盘的脏页数据。

bin log（归档日志）

bin log 是 Server 层生成的日志，它的作用是记录所有修改数据的 SQL 语句或数据变更，主要用于主从复制和备份恢复。

在完成一条更新操作之后，Server 层会生成一条 binlog，等事务提交之后，Server 会将事务执行过程中所有的 binlog 记录到 binlog 文件当中。binlog 文件当中记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类操作。

redo log 与 bin log 的区别？

• 设计层级与归属：redo log 属于 InnoDB 的数据引擎层（因此 redo log 是 InnoDB 数据引擎特有的，undo log 也是同理），而 bin log 属于 MySQL Server 层（因此 bin log 可以为任何数据引擎所用）；redo log 属于物理日志，而 bin log 是逻辑日志。
• 核心功能差异：redo log 用于确保事务的持久性，其核心功能是用于数据库异常重启后的数据恢复；bin log 用于数据复制以及按时间点恢复，其核心功能是主从复制和备份恢复。
• 物理特性对比：redo log 循环写入固定大小的文件；bin log 以追加的方式持续写入文件，如果文件过大就新建文件继续写入。redo log 记录的是物理页上的修改；bin log 记录的是 SQL 语句或行变更后的数据。redo log 在事务执行期间写入，而 bin log 在事务提交之后写入。

redo log 与 undo log 的区别？

• 核心功能差异：redo log 用于确保事务的持久性，而 undo log 用于确保事务的原子性；
• 记录内容差异：redo log 记录的是事务完成后的数据状态，而 undo log 记录的是事务开始前的数据状态。

undo log 如何实现 MVCC？

对于可重复读隔离级别，在事务开始时会生成一个 Read View，这个 Read View 记录了当前活跃的事务列表 m_ids、m_ids 当中的最小事务 id min_trx_id 、最大事务 id max_trx_id 以及创建当前事务的 id create_trx_id。

在事务执行期间，会根据可见性算法判断当前事务访问的数据是否满足可见性要求，如果不满足可见性要求，就需要通过 undo log 版本链找到满足可见性要求的记录。

具体的可见性要求如下：
InnoDB 为数据表中的每条记录添加了以下隐藏字段：

• db_trx_id：记录创建或最后一次修改这条记录的事务 id；
• db_roll_ptr：回滚指针，指向 undo log 记录；
• db_row_id：隐含的自增 id；
• delete bit：记录该行是否删除。

针对事务要访问的数据，判断记录的 db_trx_id 与 Read View 中 m_ids 的关系。如果 db_trx_id 在 m_ids 当中，说明修改这条记录的事务当前活跃，通过 db_roll_ptr 回滚到可见版本。如果 db_trx_id 小于 min_trx_id，说明修改当前记录的事务已经提交，当前记录对该事务可见；如果 db_trx_id 大于 max_trx_id，说明修改这条记录的事务在当前事务之后创建，当前版本的记录对当前事务不可见，需要通过 db_roll_ptr 通过 undo log 回滚到可见版本。如果 db_trx_id 等于 create_trx_id，说明当前记录由当前事务创建，对当前事务可见。

有了 bin log，为什么还需要 redo log？

bin log 与 redo log 的用途不同。bin log 用于「主从复制」与「备份恢复」，而 redo log 用于「事务持久化」以及「数据库宕机重启之后的数据恢复」。

只有 redo log 能保证数据页的物理持久化，一个典型的例子是 bin log 写入后数据库系统崩溃，如果此时缓冲区中的数据还没有刷盘，就需要依赖事先写入到 redo log 中的数据来进行恢复。

redo log 和 bin log 同时存在并非冗余设计，二者各有着不可替代的作用。redo log 确保数据引擎层的持久化，而 bin log 提供服务器层的逻辑日志。

简述 MySQL 事务执行过程中三种日志所扮演的角色

一次 MySQL 事务的执行需要三种日志协同工作，缺一不可。

一次事务执行到提交的过程

1. 在事务刚刚开始时，需要分配事务的 ID，并创建 undo log（用于 MVCC 多版本控制当中的事务可见性判断）；
2. 在事务中进行数据修改时，需要先记录 undo log（用于可能的事务回滚），然后修改 buffer pool 当中的数据页，最后记录 redo log 到 log buffer；
3. 在事务最终提交时，需要首先将 redo log 刷盘，并将该事务标记为 prepare 状态，然后 bin log 才刷盘，bin log 刷盘后当前事务标记为 commit 状态；
4. 后台持久化阶段：方才已经提到，MySQL InnoDB 首先将数据的修改记录在缓冲区，再由后台线程择机批量将修改通过刷盘持久化到磁盘当中。后台 I/O 线程刷盘之后，清理已提交事务的 undo log。

三大日志的核心作用

redo log

• 即使事务提交之后，后台 I/O 线程还没来得及刷盘，数据库服务就崩溃了，事务提交的结果仍然可以通过 redo log 来恢复，因为 redo log 已经落盘了。

bin log

• 可用于搭建 MySQL 的主从集群；
• 执行 mysqlbinlog 恢复误删数据；
• 可用于实现按时间点恢复。

undo log

• 执行事务回滚时恢复数据到上一个版本；
• 配合 Read View 实现 MVCC。

redo log 与 bin log 协同工作原理

• prepare 状态的作用：标记事务已经执行完成，准备进入提交阶段，此时事务已经持久化到了 redo log 中，但尚未确认。prepare 状态是「安全声明」，表示事务所作出的修改已记录但尚未最终确认；
• commit 状态的作用：表示事务已经完全提交，所有修改可被其他事务看见。commit 状态是「完全确认」，表示事务已持久化到所有「日志系统」。

可能的崩溃场景

1. prepare 之后崩溃：redo log prepare 成功，但写 bin log 时崩溃。数据库服务会发现 redo log 处于 prepare 状态但是没有 bin log，此时会回滚事务，即事务执行失败；
2. commit 标记未持久化：redo log prepare 成功，bin log 写入成功，但 redo log commit 失败。数据库服务发现 redo log prepare 之后会去 bin log 查找，发现有完整的 bin log 就会将 redo log 标记为 commit。

redo log 的 commit 状态不能保证数据页已经刷盘
它保证的是：

1. 事务的修改已完整的记录到持久化日志中（redo log + bin log）；
2. 即使系统崩溃，这些修改都可以通过 redo log 恢复；

WAL 技术详解

MySQL InnoDB 在处理事务提交时，会进行立即持久化和异步持久化，也就是 WAL 机制，WAL 机制指的是 MySQL 的写操作不立刻写到磁盘上，而是先写日志，再于合适的时机写到磁盘上，具体来说：

立即持久化（同步）

1. redo log 刷盘：事务提交时，redo log 就要刷盘；
2. bin log 刷盘：双阶段提交的第二阶段，bin log 刷盘，并将 redo log 中的事务标记为 commit；

异步持久化
针对的就是数据页刷盘，它由 InnoDB 的后台线程负责完成。触发的条件如下：

• 脏页比例超过阈值 innodb_max_dirty_pages_pct；
• redo log 空间不足（因为 redo log 循环写入，日志文件的空间大小是固定的）；
• 系统空闲时；
• 检查点（Checkpoint）推进。