06-InnoDB 存储引擎

1. InnoDB 存储结构#

从 MySQL 5.5 版本开始默认使用 InnoDB 作为引擎，它擅长处理事务，具有自动崩溃恢复的特性，在日常开发中使用非常广泛。下面是官方的 InnoDB 引擎架构图，主要分为「内存结构」和「磁盘结构」两大部分。

1.1 内存结构#

内存结构主要包括 Buffer Pool、Change Buffer、Adaptive Hash Index 和 Log Buffer 四大组件。

a. Buffer Pool#

缓冲池，简称 BP。BP 以 Page 为单位，默认大小 16K，BP 的底层采用链表数据结构管理 Page。在 InnoDB 访问表记录和索引时会在 Page 页中缓存，以后使用可以减少磁盘 IO 操作，提升效率。

Page 根据状态可以分为 3 种类型

  • Free Page：空闲 Page，未被使用；
  • Clean Page：被使用 Page，数据没有被修改过；
  • Dirty Page：被使用 Page，数据被修改过，页中数据和磁盘的数据产生了不一致；

针对上述三种 Page 类型，InnoDB 通过 3 种链表结构来维护和管理。

  • Free List ：表示空闲缓冲区，管理 Free Page；
  • Flush List：表示需要刷新到磁盘的缓冲区，管理 Dirty Page，内部 Page 按修改时间排序。Dirty Page 既存在于 Flush 链表，也在 LRU 链表中，但是两种互不影响，LRU 链表负责管理 Page 的可用性和释放，而 Flush 链表负责管理 Dirty Page 的刷盘操作；
  • LRU List：表示正在使用的缓冲区，管理 Clean Page 和 Dirty Page，缓冲区以 midpoint 为基点，前面链表称为 new 列表区，存放经常访问的数据，占 63%；后面的链表称为 old 列表区，存放使用较少数据，占 37%。

改进型 LRU 算法维护

  • 普通 LRU：末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰；
  • 改性 LRU：链表分为 new 和 old 两个部分，加入元素时并不是从表头插入，而是从中间 midpoint 位置插入，如果数据很快被访问，那么 Page 就会向 new 列表头部移动，如果数据没有被访问，会逐步向 old 尾部移动，等待淘汰。

每当有新的 Page 数据读取到 Buffer Pool 时，InnoDb 引擎会判断是否有空闲页，是否足够，如果有就将 Free Page 从 Free List 列表删除，放入到 LRU 列表中。没有空闲页，就会根据 LRU 算法淘汰 LRU 链表默认的页，将内存空间释放分配给新的页。

Buffer Pool 配置参数

show variables like '%innodb_page_size%';     # 查看页大小
show variables like '%innodb_old%';           # 查看 LRU List 中 old 列表参数
show variables like '%innodb_buffer%';        # 查看 Buffer Pool 参数

将 innodb_buffer_pool_size 设置为总内存大小的 60%-80%，innodb_buffer_pool_instances 可以设置为多个，这样可以避免缓存争夺。

b. Change Buffer#

写缓冲区，简称 CB。在进行 DML 操作时，如果 BP 没有其相应的 Page 数据，并不会立刻将磁盘页加载到缓冲池，而是在 CB 记录缓冲变更，等未来数据被读取时，再将数据合并恢复到 BP 中。

ChangeBuffer 占用 BufferPool 空间，默认占 25%，最大允许占 50%，可以根据读写业务量来进行调整（参数 innodb_change_buffer_max_size）。

当更新一条记录时，若该记录在 BufferPool 存在，则直接在 BufferPool 修改，一次内存操作；如果该记录在 BufferPool 不存在（没有命中），会直接在 ChangeBuffer 进行一次内存操作，不用再去磁盘查询数据，避免一次磁盘 IO。当下次查询记录时，会先进行磁盘读取，然后再从 ChangeBuffer 中读取信息合并，最终载入 BufferPool 中。

Change Buffer 更新流程如下：

写缓冲区为什么仅适用于非唯一普通索引页？

如果设置唯一性索引，在进行修改时，InnoDB 必须要做唯一性校验，因此必须查询磁盘，做一次 IO 操作。会直接将记录查询到 BufferPool 中，然后在缓冲池修改，不会在 ChangeBuffer 操作。

c. Adaptive Hash Index#

自适应哈希索引，用于优化对 BP 数据的查询。InnoDB 存储引擎会监控对表索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为“自适应”。InnoDB 存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。

d. (Redo/Undo) Log Buffer#

日志缓冲区，用来缓存要写入磁盘 log 文件（Redo/Undo）的数据，日志缓冲区的内容定期刷新到磁盘 log 文件中。日志缓冲区满时会自动将其刷新到磁盘，当遇到 BLOB 或多行更新的大事务操作时，增加日志缓冲区可以节省磁盘 I/O。

LogBuffer 主要是用于记录 InnoDB 引擎日志，在 DML 操作时会产生 Redo 和 Undo 日志。

LogBuffer 空间满了，会自动写入磁盘。可以通过将 innodb_log_buffer_size 参数调大，减少磁盘 IO 频率。

1.2 磁盘结构#

InnoDB 磁盘主要包含 Tablespaces、InnoDB Data Dictionary、Doublewrite Buffer、Redo Log 和 Undo Logs。

a. Tablespaces#

表空间（Tablespaces）用于存储表结构和数据。表空间又分为系统表空间、独立表空间、通用表空间、临时表空间、Undo 表空间等多种类型。

系统表空间（The System Tablespace）

包含 InnoDB 数据字典、Doublewrite Buffer、Change Buffer、Undo Logs 的存储区域。系统表空间也默认包含任何用户在系统表空间创建的表数据和索引数据。系统表空间是一个共享的表空间因为它是被多个表共享的。

该空间的数据文件通过参数 innodb_data_file_path 控制，默认值是 ibdata1:12M:autoextend（文件名为 ibdata1、12MB、自动扩展）。

独立表空间（File-Per-Table Tablespaces）

独立表空间是一个单表表空间，该表创建于自己的数据文件中，而非创建于系统表空间中。当 innodb_file_per_table 选项开启时（默认开启），表将被创建于表空间中。否则，InnoDB 将被创建于系统表空间中。

每个表文件表空间由一个 .ibd 数据文件代表，该文件默认被创建于数据库目录中。表空间的表文件支持动态（dynamic）和压缩（commpressed）行格式。

通用表空间（General Tablespaces）

通用表空间为通过 CREATE TABLESPACE 语法创建的共享表空间。通用表空间可以创建于 MySQL 数据目录外的其他表空间，其可以容纳多张表，且其支持所有的行格式。

# 创建表空间ts1
CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd Engine=InnoDB;
# 将表添加到ts1表空间
CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;

撤销表空间（Undo Tablespaces）

撤销表空间由一个或多个包含 Undo 日志文件组成。在 MySQL 5.7 版本之前 Undo 占用的是 System Tablespace 共享区，从 5.7 开始将 Undo 从 System Tablespace 分离了出来。

InnoDB 使用的 Undo 表空间由 innodb_undo_tablespaces 配置选项控制，默认为 0。参数值为 0 表示使用系统表空间 ibdata1；大于 0 表示使用 Undo 表空间 undo_001、undo_002 等。

临时表空间（Temporary Tablespaces）

分为 Session Temporary Tablespaces 和 Global Temporary Tablespace 两种。

  • Session Temporary Tablespaces 存储用户创建的临时表和磁盘内部的临时表；
  • Global Temporary Tablespace 存储用户临时表的回滚段（rollback segments ）。

MySQL 服务器正常关闭或异常终止时，临时表空间将被移除，每次启动时会被重新创建。

b. InnoDB Data Dictionary#

InnoDB 数据字典由内部系统表组成，这些表包含用于查找表、索引和表字段等对象的元数据。元数据物理上位于「InnoDB 系统表空间」中。由于历史原因，数据字典元数据在一定程度上与 InnoDB 表元数据文件（.frm文件）中存储的信息重叠。

c. Doublewrite Buffer#

位于「InnoDB 系统表空间」中，是一个存储区域。在 Buffer Pool 的 Page 刷新到磁盘真正的位置前，会先将数据存在 Doublewrite 缓冲区。如果在 Page 写入过程中出现操作系统、存储子系统或 mysqld 进程崩溃，InnoDB 可以在崩溃恢复期间从 Doublewrite 缓冲区中找到 Page 的一个好备份。

在大多数情况下，默认情况下启用双写缓冲区，要禁用 Doublewrite 缓冲区，可以将 innodb_doublewrite 设置为 0。使用 Doublewrite 缓冲区时建议将 innodb_flush_method 设置为 O_DIRECT。

// MySQL 的 innodb_flush_method 这个参数控制着 InnoDB 数据文件及 Redo Log 的打开、刷写模式，有 3 个值：fdatasync(默认)、O_DSYNC、O_DIRECT。

  • 设置 O_DIRECT 表示数据文件写入操作会通知操作系统不要缓存数据，也不要用预读，直接从 Innodb Buffer 写到磁盘文件。
  • 默认的 fdatasync 意思是先写入操作系统缓存，然后再调用 fsync() 函数去异步刷数据文件与 Redo Log 的缓存信息。

d. Redo Logs#

重做日志是一种基于磁盘的数据结构，用于在崩溃恢复期间更正不完整事务写入的数据。

MySQL 以循环方式写入重做日志文件，记录 InnoDB 中所有对 Buffer Pool 修改的日志。当出现实例故障（像断电），导致数据未能更新到数据文件，则数据库重启时须 redo，重新把数据更新到数据文件。

读写事务在执行的过程中，都会不断的产生 Redo Log。默认情况下，重做日志在磁盘上由两个名为 ib_logfile0 和 ib_logfile1 的文件物理表示。

e. Undo Logs#

撤消日志（回滚日志）是在事务开始之前保存的被修改数据的备份（记录数据被修改前的信息），用于例外情况时回滚事务。撤消日志属于逻辑日志，根据每行记录进行记录。撤消日志存在于系统表空间、撤消表空间和临时表空间中。

可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然；当 update 一条记录时，它记录一条对应相反的 update 记录。当执行 rollback 时，就可以从 undo log 中的逻辑记录读取到相应的内容并进行回滚。

Redo Log Undo Log
记录的是数据页的物理变化，服务宕机可用来同步数据。 记录的是逻辑日志，当事务回滚时可通过逆操作恢复原来的数据。
保证了事务的持久性 保证了事务的原子性和一致性

1.3 版本演变#

MySQL 5.7 版本

  • 将 Undo 日志表空间从共享表空间 ibdata 文件中分离出来，可以在安装 MySQL 时由用户自行指定文件大小和数量；
  • 增加了 Temporary 临时表空间，里面存储着临时表或临时查询结果集的数据；
  • Buffer Pool 大小可以动态修改，无需重启数据库实例。

MySQL 8.0 版本

  • 将 InnoDB 表的数据字典和 Undo 都从共享表空间 ibdata 中彻底分离出来了，以前需要 ibdata 中数据字典与独立表空间 ibd 文件中数据字典一致才行，8.0 版本就不需要了；
  • Temporary 临时表空间也可以配置多个物理文件，而且均为 InnoDB 存储引擎并能创建索引，这样加快了处理的速度；
  • 用户可以像 Oracle 数据库那样设置一些表空间，每个表空间对应多个物理文件，每个表空间可以给多个表使用，但一个表只能存储在一个表空间中；
  • 将 Doublewrite Buffer 从共享表空间 ibdata 中也分离出来了。

1.4 线程模型#

a. IO Thread#

在 InnoDB 中使用了大量的 AIO（Async IO）来做读写处理，这样可以极大提高数据库的性能。在 InnoDB 1.0 版本之前共有 4 个 IO Thread，分别是 Write、Read、Insert Buffer 和 Log Thread。后来版本将 Read Thread 和 Write Thread 分别增大到了 4 个，一共有 10 个了。

mysql> show engine innodb status \G;

--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (read thread)
I/O thread 4 state: wait Windows aio (read thread)
I/O thread 5 state: wait Windows aio (read thread)
I/O thread 6 state: wait Windows aio (write thread)
I/O thread 7 state: wait Windows aio (write thread)
I/O thread 8 state: wait Windows aio (write thread)
I/O thread 9 state: wait Windows aio (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
415 OS file reads, 88 OS file writes, 7 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
  • Read Thread：负责读取操作，将数据从磁盘加载到缓存 Page；
  • Write Thread：负责写操作，将缓存脏页刷新到磁盘；
  • Log Thread：负责将日志缓冲区内容刷新到磁盘；
  • Insert Buffer Thread ：负责将写缓冲内容刷新到磁盘；

b. Purge Thread#

事务提交之后，其使用的 Undo Log 将不再需要，因此需要 Purge Thread 回收已经分配的 Undo Page。

mysql> show variables like '%innodb_purge_threads%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_purge_threads | 4     |
+----------------------+-------+

c. Page Cleaner Thread#

作用是将脏数据刷新到磁盘，脏数据刷盘后相应的 Redo Log 也就可以覆盖，即可以同步数据，又能达到 Redo Log 循环使用的目的。会调用 Write Thread 线程处理。

mysql> show variables like '%innodb_page_cleaners%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_page_cleaners | 1     |
+----------------------+-------+

d. Master Thread#

Master Thread 是 InnoDB 的主线程，负责调度其他各线程，优先级最高。

作用是将缓冲池中的数据异步刷新到磁盘 ，保证数据的一致性。包含：脏页的刷新（Page Cleaner Thread）、Undo Page 回收（Purge Thread）、Redo Log 刷新（Log Thread）、合并写缓冲等。内部有两个主处理，分别是每隔 1s 和 10s 处理。

每 1s 的操作：

  • 刷新日志缓冲区，刷到磁盘；
  • 合并写缓冲区数据，根据 IO 读写压力来决定是否操作；
  • 刷新脏页数据到磁盘，根据脏页比例达到 75% 才操作；
    mysql> show variables like '%innodb_max_dirty_pages_pct%';
+--------------------------------+-----------+
| Variable_name                  | Value     |
+--------------------------------+-----------+
| innodb_max_dirty_pages_pct     | 75.000000 |
| innodb_max_dirty_pages_pct_lwm | 0.000000  |
+--------------------------------+-----------+

mysql> show variables like '%innodb_io_capacity%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity     | 200   |
| innodb_io_capacity_max | 2000  |
+------------------------+-------+

每 10s 的操作：

  • 刷新脏页数据到磁盘；
  • 合并写缓冲区数据；
  • 刷新日志缓冲区；
  • 删除无用的 Undo Page；
    mysql> show variables like '%innodb_purge_batch_size%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_purge_batch_size | 300   |
+-------------------------+-------+

2. 日志#

2.1 Redo Log#

Redo，顾名思义就是“重做”。以恢复操作为目的，在数据库发生意外时重现操作。Redo Log 指事务中修改的任何数据，将最新的数据备份存储的位置（Redo Log），被称为“重做日志”。

a. 工作流程#

Redo Log 是如何保证数据不丢失的？

就是在修改之后，先将修改后的值记录到磁盘上的 Redo Log 中，就算突然断电了，Buffer Pool 中的数据全部丢失了，来电的时候也可以根据 Redo Log 恢复 Buffer Pool，这样既利用到了 Buffer Pool 的内存高效性，也保证了数据不会丢失。

我们通过一个例子说明，我们先假设没有 Buffer Pool，user 表里面只有一条记录，记录的 age = 1，假设需要执行一条SQL：update user set age = 2，执行过程如下：

img

如上图，有了 Redo Log 之后，将 age 修改成 2 之后，马上将 age = 2 写到 Redo Log 里面，如果这个时候突然断电内存数据丢失，在来电的时候，可以将 Redo Log 里面的数据读出来恢复数据，用这样的方式保证了数据不会丢失。

你可能会问，Redo Log 文件也在磁盘上，数据文件也在磁盘上，都是磁盘操作，何必多此一举？为什么不直接将修改的数据写到数据文件里面去呢？

因为 Redo Log 是磁盘顺序写，数据刷盘是磁盘随机写，磁盘的顺序写比随机写高效的多！这种先预写日志后面再将数据刷盘的机制，有一个高大上的专业名词 —— WAL（Write-ahead logging），翻译成中文就是预写式日志。

虽然磁盘顺序写已经很高效了，但是和内存操作还是有一定的差距。

那么，有没有办法进一步优化一下呢？

答案是可以。那就是给 Redo Log 也加一个内存 Buffer，也就是 Redo Log Buffer，用这种套娃式的方法进一步提高效率。

b. 刷盘策略#

Redo Log Buffer 具体是怎么配合刷盘呢？

在这个问题之前之前，我们先来捋一下 MySQL 服务端和〈操作系统〉的关系。

MySQL 服务端是一个进程，它运行于〈操作系统〉之上。也就是说，〈操作系统〉挂了 MySQL 一定挂了，但是 MySQL 挂了〈操作系统〉不一定挂。所以 MySQL 挂了分两种情况：

  1. MySQL 挂了，〈操作系统〉也挂了，也就是常说的服务器宕机了。这种情况 Redo Log Buffer 里面的数据会全部丢失，〈操作系统〉的 OS Cache 里面的数据也会丢失；
  2. MySQL 挂了，〈操作系统〉没有挂。这种情况 Redo Log Buffer 里面的数据会全部丢失，〈操作系统〉的 OS Cache 里面的数据不会丢失。

OK，了解了 MySQL 服务端和〈操作系统〉的关系之后，再来看 Redo Log 的落盘机制（也就是 Redo Log Buffer 持久化到 Redo Log 文件的策略）。Redo Log 的刷盘机制由参数 innodb_flush_log_at_trx_commit 控制，这个参数有 3 个值可以设置：

  • innodb_flush_log_at_trx_commit = 0：延迟写，延迟刷
  • innodb_flush_log_at_trx_commit = 1：实时写，实时刷
  • innodb_flush_log_at_trx_commit = 2：实时写，延迟刷

「写」可以理解成写到〈操作系统〉的缓存（OS Cache），「刷」可以理解成把〈操作系统〉里面的缓存刷到磁盘。

innodb_flush_log_at_trx_commit = 0：延迟写，延迟刷

这种策略在事务提交时，只会把数据写到 Redo Log Buffer 中，然后让后台线程定时去将 Redo Log Buffer 里面的数据刷到磁盘。

这种策略是最高效的，但是我们都知道，定时任务是有间隙的，但是如果事务提交后，后台线程没来得及将 Redo Log Buffer 刷到磁盘，这个时候不管是 MySQL 进程挂了还是操作系统挂了，这一部分数据都会丢失。

总结来说这种策略效率最高，丢数据的风险也最高。

img

innodb_flush_log_at_trx_commit = 1：实时写，实时刷

这种策略会在每次事务提交之前，每次都会将数据从 Redo Log 刷到磁盘中去，理论上只要磁盘不出问题，数据就不会丢失。

总的来说，这种策略效率最低，但是丢数据风险也最低。

img

innodb_flush_log_at_trx_commit = 2：实时写，延迟刷

这种策略在事务提交之前会把 Redo Log Buffer 写到 OS Cache 中，但并不会实时地将 Redo Log 刷到磁盘，而是会每秒执行一次刷新磁盘操作。

这种情况下如果 MySQL 进程挂了，操作系统没挂的话，操作系统还是会将 OS Cache 刷到磁盘，数据不会丢失，如下图：

img

但如果 MySQL 所在的服务器挂掉了，也就是操作系统都挂了，那么 OS Cache 也会被清空，数据还是会丢失。如下图：

img

所以，这种 Redo Log 刷盘策略是上面两种策略的折中策略，效率比较高，丢失数据的风险比较低，绝大多情况下都推荐这种策略。

最后总结一下，Redo Log 的作用是用于恢复数据，写 Redo Log 文件的过程是磁盘顺序写，有 3 种刷盘策略，用 innodb_flush_log_at_trx_commit 参数控制，推荐设置成 2。

c. 补充&小结#

【日志功能】Redo Log 是为了实现事务的持久性而出现的产物。防止在发生故障的时间点，尚有脏页未写入表的 IBD 文件中，在重启 MySQL 服务的时候，根据 Redo Log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。

【写入方式】Redo Log 文件内容是以顺序循环的方式写入文件，写满时则回溯到第一个文件，进行覆盖写。注意，Redo Log 不是记录数据页“更新之后的状态”，而是记录这个页 “做了什么改动”。

  • WritePos 是当前记录的位置，一边写一边后移，写到最后一个文件末尾后就回到 0 号文件开头；
  • CheckPoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件；

WritePos 和 CheckPoint 之间还空着的部分，可以用来记录新的操作。如果 WritePos 追上 CheckPoint，表示写满，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 CheckPoint 推进一下。

【相关配置】每个 InnoDB 存储引擎至少有 1 个重做日志文件组（group），每个文件组至少有 2 个重做日志文件，默认为 ib_logfile0 和 ib_logfile1。可以通过下面一组参数控制 Redo Log 存储：

mysql> show variables like '%innodb_log%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| innodb_log_Buffer_size      | 16777216 |
| innodb_log_checksums        | ON       |
| innodb_log_compressed_pages | ON       |
| innodb_log_file_size        | 50331648 |
| innodb_log_files_in_group   | 2        |
| innodb_log_group_home_dir   | .\       |
| innodb_log_write_ahead_size | 8192     |
+-----------------------------+----------+
7 rows in set, 1 warning (0.01 sec)

【刷盘策略】Redo Log Buffer 持久化到 Redo Log 文件的策略，可通过 Innodb_flush_log_at_trx_commit 设置：

一般建议选择取值 2，因为 MySQL 挂了数据没有损失，整个服务器挂了才会损失 1s 的事务提交数据。

2.2 Undo Log#

Undo Log 回滚数据，以行为单位，记录数据每次的变更，一行记录有多个版本并存。

MVCC 多版本并发控制，即快照读（也称为一致性读），让 select 查询操作可以去访问历史版本。

a. 工作流程#

我们都知道，InnoDB 是支持事务的，而事务是可以回滚的。

假如一个事务将 age=1 修改成了 age=2，在事务还没有提交的时候，后台线程已经将 age=2 刷入了磁盘。这个时候，不管是内存还是磁盘上，age 都变成了 2，如果事务要回滚，找不到修改之前的 age=1，无法回滚了。

那怎么办呢？

很简单，把修改之前的 age=1 存起来，回滚的时候根据存起来的 age=1 回滚就行了。

MySQL 确实是这么干的！这个记录修改之前的数据的过程，叫做记录 Undo Log。

“undo”翻译成中文是“撤销、回滚”的意思，以撤销操作为目的，返回指定某个状态的操作。数据库事务开始之前，会将要修改的记录存放到 Undo Log 里，当事务回滚时或者数据库崩溃时，可以利用 Undo Log 撤销未提交事务对数据库产生的影响。

如何回滚呢？

img

MySQL 在将 age = 1 修改成 age = 2 之前，先将 age = 1 存到 Undo Log 里面去，这样需要回滚的时候，可以将 Undo Log 里面的 age = 1 读出来回滚。

b. 补充&小结#

日志产生、销毁

Undo Log 在事务开始前产生；事务在提交时，并不会立刻删除 Undo Log，InnoDB 会将该事务对应的 Undo Log 放入到删除列表中，后面会通过后台线程 Purge Thread 进行回收处理。Undo Log 属于逻辑日志，记录一个变化过程。例如执行一个 delete，Undo Log 会记录一个 insert；执行一个 update，Undo Log 会记录一个相反的 update。

需要注意的是，Undo Log 默认存在 Undo Tablespaces 里面，你可以简单的理解成 Undo Log 也是记录在一个 MySQL 的表里面，插入一条 Undo Log 和插入一条普通数据是类似。也就是说，写 Undo Log 的过程中同样也是要写入 Redo Log 的。

Undo Log 采用 Segment 段的方式管理和记录

在 InnoDB 数据文件中包含一种回滚段（Rollback Segment），内部包含 1024 个 Undo Log Segment。可以通过下面一组参数来控制 Undo Log 存储。

mysql> show variables like '%innodb_undo%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_undo_directory    | .\    |
| innodb_undo_log_truncate | OFF   |
| innodb_undo_logs         | 128   |
| innodb_undo_tablespaces  | 0     |
+--------------------------+-------+
4 rows in set, 1 warning (0.03 sec)

日志的作用

  1. 实现事务的原子性：Undo Log 是为了实现事务的原子性而出现的产物。事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态；
  2. 实现多版本并发控制（MVCC）：Undo Log 在 MySQL InnoDB 存储引擎中用来实现多版本并发控制。事务未提交之前，Undo Log 保存了未提交之前的版本数据，Undo Log 中的数据可作为数据旧版本快照供其他并发事务进行快照读。

事务 A 手动开启事务，执行更新操作，首先会把更新命中的数据备份到 Undo Buffer 中。事务 B 手动开启事务，执行查询操作，会读取 Undo Log 数据返回，进行快照读。

c. MVCC#

全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突 MVCC 的具体实现，主要依赖于：数据库记录中的隐式字段、Undo Log、ReadView。

MySQL 隔离性就是靠 MVCC 机制来保证的，对一行数据的读和写两个操作默认是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥，而在串行化隔离级别为了保证较高的隔离性是通过将所有操作加锁互斥来实现的。

MVCC 最大的好处是读不加锁，读写不冲突。在读多写少的系统应用中，读写不冲突是非常重要的，极大的提升系统的并发性能，这也是为什么现阶段几乎所有的关系型数据库都支持 MVCC 的原因，MySQL 在〈读已提交隔离级别〉和〈可重复读隔离级别〉下都实现了 MVCC 机制。

如何生成的多版本？

每次事务修改操作之前，都会在 Undo Log 中记录修改之前的数据状态和事务号，该备份记录可以用于其他事务的读取，也可以进行必要时的数据回滚。

【MVCC 实现原理】记录的隐藏字段、Undo 日志版本链、Read-View 机制

记录隐藏字段（trx_id + roll_pointer）和 Undo Log 回滚日志构成一条记录多次修改的版本链，再根据 ReadView 包含信息和匹配规则来判断当前事务该访问哪个版本的数据。

  • 记录中的隐藏字段
    • DB_TRX_ID：最近修改事务 ID，记录插入这条记录或最后一次修改该记录的事务 ID。
    • DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，用于配合 Undo Log，指向上一个版本。
    • DB_ROW_ID：隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。
  • Undo 日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，MySQL 会保留修改前的数据 Undo 回滚日志，并且用两个隐藏字段 trx_id 和 roll_pointer 把这些 Undo 日志串联起来形成一个历史记录版本链；
    • 回滚日志，在 insert、update、delete 的时候产生的，便于数据回滚的日志。当 insert 的时候，产生的 Undo Log 只在回滚时需要，在事务提交后，可被立即删除。而 update、delete 的时候，产生的 Undo Log 不仅在回滚时需要，MVCC 版本访问也需要，不会立即被删除。
    • 不同事务或相同事务对同一条记录进行修改，会导致该记录的 Undo Log 生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。
  • Read View 是一个数据库的内部快照，该快照被用于 InnoDB 存储引擎中的 MVCC 机制。简单点说，Read View 就是一个快照，保存着数据库某个时刻的数据信息。
    • Read View 会根据事务的隔离级别决定在某个事务开始时，该事务能看到什么信息。就是说通过 Read View，事务可以知道此时此刻能看到哪个版本的数据记录（有可能不是最新版本的，也有可能是最新版本的）。
    • ReadView（读视图）是快照读 SQL 执行时 MVCC 提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id。

当前读与快照读

当前读

  • 读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。
  • 对于日常操作，如：select ... lock in share mode（共享锁），select ... for update、update、delete、insert（排他锁）都是一种当前读。

快照读

  • 简单的 select 就是快照读。读取某一个快照建立时的数据（有可能是历史数据，原理是回滚段）。无需加锁，是非阻塞读。
  • 快照读主要体现在 select 时，不同隔离级别下 select 的行为不同：
    隔离级别 select
    Serializable 普通 select 也变成「当前读」
    RC 每次 select 都会建立新的快照
    RR ① 事务启动后，首次 select 会建立快照 ② 如果事务启动选择了 with consistent snapshot，事务启动时就建立快照 ③ 基于旧数据的修改操作，会重新建立快照

Read View 设计思路

在实现上，数据库里面会创建一个视图（Read View），访问的时候以 Read View 的逻辑结果为准。在 REPEATABLE READ 隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在 READ COMMITTED 隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是， READ UNCOMMITTED隔离级别下直接返回记录上的最新值，没有视图概念；而 SERIALIZABLE 隔离级别下直接用加锁的方式来避免并行访问。

使用 READ COMMITTED 和 REPEATABLE READ 隔离级别的事务，都必须保证读到”已经提交了的“事务修改过的记录。假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问题就是需要判断一下版本链中的哪个版本是当前事务可见的，这是 Read View 要解决的主要问题。

这个 Read View 中主要包含 4 个比较重要的内容：

  1. creator_trx_id，创建这个 Read View 的事务 ID（只有在对表中的记录做改动时，如 INSERT、DELETE、UPDATE 这些语句时，才会为事务分配事务 ID，否则在一个只读事务中的事务 ID 值都默认为 0）；
  2. trx_ids，表示在生成 Read View 时当前系统中活跃的读写事务的事务 ID 列表；
  3. up_limit_id，活跃的事务中最小的事务 ID；
  4. low_limit_id，表示生成 Read View 时系统中应该分配给下一个事务的 ID 值（low_limit_id 是系统最大的事务 ID 值，这里要注意是系统中的自增事务 ID，需要区别于正在活跃的事务 ID）。

low_limit_id 并不是 trx_ids 中的最大值，事务 ID 是递增分配的。比如，现在有 ID 为 1、2、3 这三个事务，之后 ID 为 3 的事务提交了。那么一个新的读事务在生成 Read View 时，trx_ids 就包括 1、2，up_limit_id 的值就是 1，low_limit_id 的值就是 4（预分配事务 ID，当前最大事务 ID+1）。

有了 Read View，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见：

  • trx_id == createor_trx_id 如果被访问版本的 trx_id 属性值与 ReadView 中的 creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问；
  • trx_id < up_limit_id 如果被访问版本的 trx_id 属性值小于 ReadView 中的 up_limit_id 值，表明生成该版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当前事务访问；
  • trx_id >= low_limit_id 如果被访问版本的 trx_id 属性值大于或等于 ReadView 中的 low_limit_id 值，表明生成该版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问。
  • up_limit_id < trx_id < low_limit_id 如果被访问版本的 trx_id 属性值在 ReadView 的 up_limit_id 和 low_limit_id 之间，那就需要判断一下 trx_id 属性值是不是在 trx_ids 列表中：
    • 如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；
    • 如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。

了解了这些概念之后，我们来看下当查询一条记录的时候，系统如何通过 MVCC 找到它：

  1. 首先获取事务自己的版本号，也就是事务 ID；
  2. 获取 ReadView；
  3. 查询得到的数据，然后与 ReadView 中的事务版本号进行比较；
  4. 如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；
  5. 最后返回符合规则的数据。

在隔离级别为读已提交（Read Committed）时，一个事务中的每一次 SELECT 查询都会重新获取一次Read View（可能产生”不可重复读“或”幻读“的情况）。

在隔离级别为可重复读（REPEATABLE READ）的时候，就避免了不可重复读，这是因为一个事务只在第 1 次 SELECT 的时候会 获取一次 Read View，而后面所有的 SELECT 都会复用这个 Read View，如下表所示：

【举例说明 1】

从版本链的头开始，每个版本的 trx_id 都拿来比对 ReadView 规则，第一个匹配成功的版本，即为当前事务能看到的版本。

【举例说明 2】

假设现在有一个使用 READ COMMITTED 隔离级别的事务开始执行：

BEGIN;
# SELECT1：Transaction 10、20 未提交
SELECT * FROM student WHERE id = 1; # 得到的name的值为'张三'

之后，我们把事务 id=10 的事务提交一下：

# Transaction 10

BEGIN;
UPDATE student SET name="李四" WHERE id=1;
UPDATE student SET name="王五" WHERE id=1;
COMMIT;

然后再到事务 id=20 的事务中更新一下表 student 中 id 为 1 的记录：

# Transaction 20
BEGIN;
# 更新了一些别的表的记录
# ...
UPDATE student SET name="钱七" WHERE id=1; 
UPDATE student SET name="宋八" WHERE id=1;

此刻，student 表中 id=1 的记录的版本链就长这样：

然后再到刚才使用 READ COMMITTED 隔离级别的事务中继续查找这个 id 为 1 的记录，如下：

BEGIN;
# SELECT1：Transaction 10、20 均未提交
SELECT * FROM student WHERE id = 1; # 得到的name的值为'张三'
# SELECT2：Transaction 10 提交，Transaction 20 未提交
SELECT * FROM student WHERE id = 1; # 得到的name的值为'王五'

【如何解决幻读？】

假设现在 student 表中只有一条数据，数据内容中，主键 id=1，隐藏的 trx_id=10，它的 undo log 如下图所示。

假设现在有事务 A 和事务 B 并发执行，事务 A 的事务 id=20，事务 B 的事务 id=30。

（1）事务 A 开始第一次查询数据，查询的 SQL 语句：select * from student where id >= 1;；

在开始查询之前，MySQL 会为事务 A 产生一个 ReadView，此时 ReadView 的内容如下：trx_ids= [20,30]，up_limit_id=20，low_limit_id=31，creator_trx_id=20 。

由于此时 student 表中只有一条数据且符合 where id>=1 条件，因此会查询出来。然后根据 ReadView 机制，发现该行数据的 trx_id=10，小于事务 A 的 ReadView 里 up_limit_id，这表示这条数据是事务 A 开启之前，其他事务就已经提交了的数据，因此事务 A 可以读取到。

（2） 接着事务 B（trx_id=30），往 student 表中新插入两条数据，并提交事务；

insert into student(id,name) values(2, '李四');
insert into student(id,name) values(3, '王五');

此时 student 表中就有 3 条数据了，对应的 undo 如下图所示：

（3）接着事务 A 开启第 2 次同样的查询，根据可重复读隔离级别的规则，此时事务 A 并不会再重新生成 ReadView。

此时 student 表中的 3 条数据均满足 where id>=1 的条件，所以都会查出来，然后根据 ReadView 机制来判断每条数据是不是都可以被事务 A 看到。

  1. 首先 id=1 的这条数据，前面已经说过了，可以被事务 A 看到；
  2. 然后是 id=2 的数据，它的 trx_id=30，此时事务 A 发现，这个值处于 up_limit_id 和 low_limit_id 之间，因此还需要再判断 30 是否处于 trx_ids 数组内。由于事务 A 的 trx_ids=[20,30]，在数组内，这表示 id=2 的这条数据是与事务 A 在同一时刻启动的其他事务提交的，所以这条数据不能让事务 A 看到；
  3. 同理，id=3 的这条数据的 trx_id 也为 30，因此也不能被事务 A 看见。

最终事务 A 的第二次查询，只能查询出 id=1 的这条数据。这和事务 A 的第一次查询的结果是一样 的，因此没有出现幻读现象，所以说在 MySQL 的可重复读隔离级别下，不存在幻读问题。

对于删除的情况可以认为是 update 的特殊情况，会将版本链上最新的数据复制一份，然后将 trx_id 修改成删除操作的 trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上 true，来表示当前记录已经被删除，在查询时按照上面的规则查到对应的记录如果 delete_flag 标记位为 true，意味着记录已被删除，则不返回数据。

总的来说，MVCC 机制的实现就是通过 read-view 机制与 Undo 版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。

注意！begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作 InnoDB 表的语句，事务才真正启动，才会向 MySQL 申请事务 id，MySQL 内部是严格按照事务的启动顺序来分配事务 id 的。

你一定会问，回滚日志总不能一直保留吧，什么时候删除呢？

答案是，在不需要的时候才删除。

也就是说，系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除。

什么时候才不需要了呢？就是当系统里没有比这个回滚日志更早的 read-view 的时候。

基于上面的说明，我们来讨论一下为什么建议你尽量不要使用长事务。

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

在 MySQL 5.5 及以前的版本，回滚日志是跟数据字典一起放在 ibdata 文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。我见过数据只有 20GB，而回滚段有 200GB 的库。最终只好为了清理回滚段，重建整个库。

除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库。

2.3 binlog#

a. 日志作用#

Undo Log 记录的是修改之前的数据，提供回滚的能力。Redo Log 记录的是修改之后的数据，提供了崩溃恢复的能力。

那 binlog 是干什么的呢？

binlog 记录所有数据库表结构变更以及表数据修改的二进制日志，即 Binary Log（二进制日志），简称 binlog。

binlog 日志是以〈事件〉形式记录，还包含语句所执行的消耗时间。不会记录 SELECT 和 SHOW 这类操作。

开启 binlog 日志有以下两个最重要的使用场景。

  • 【主从复制】在主库中开启 binlog 功能，这样主库就可以把 binlog 传递给从库，从库拿到 binlog 后实现数据恢复达到主从数据一致性；
  • 【数据恢复】通过 mysqlbinlog 工具来恢复数据。

那么问题来了，binlog 和 Redo Log 都是记录的修改之后的值，这两者有什么区别呢？有 Redo Log 为什么还需要 binlog 呢？

首先看两者的一些区别：

  • binlog 是〈逻辑日志〉，记录的是“对哪一个表的哪一行做了什么修改”；Redo Log 是〈物理日志〉，记录的是“对哪个数据页中的哪个记录做了什么修改”；
  • binlog 是追加写；Redo Log 是循环写，日志文件有固定大小，会覆盖之前的数据；
  • binlog 是 Server 层的日志；Redo Log 是 InnoDB 的日志。如果不使用 InnoDB 引擎，是没有 Redo Log 的；

但说实话，我觉得这些区别并不是 Redo Log 不能取代 binlog 的原因，MySQL官方完全可以调整 Redo Log 让他兼并 binlog 的能力，但他没有这么做，为什么呢？

我认为不用 Redo Log 取代 binlog 最大的原因是“没必要”。

为什么这么说呢？

  1. binlog 的生态已经建立起来。MySQL 高可用主要就是依赖 binlog 复制，还有很多公司的数据分析系统和数据处理系统，也都是依赖的 binlog。取代 binlog 去改变一个生态费力了不讨好；
  2. binlog 并不是 MySQL 的瓶颈，花时间在没有瓶颈的地方没必要。

b. 存储机制#

binlog 文件记录模式

有 STATEMENT、ROW 和 MIXED 三种，具体含义如下：

  • ROW（Row-Based Replication, RBR）：日志中会记录每一行数据被修改的情况，然后在 Slave 端对相同的数据进行修改；
  • STATMENT（Statement-Based Replication, SBR）：每一条被修改数据的SQL都会记录到 master 的 binlog 中，Slave 在复制的时候 SQL 进程会解析成和原来 master 端执行过的相同的 SQL 再次执行；简称 SQL 复制；
  • MIXED（mixed-based replication, MBR）：以上两种模式的混合使用，一般会使用 STATEMENT 模式保存 binlog，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog，MySQL 会根据执行的 SQL 选择写入模式。

binlog 文件结构

binlog 文件中记录的是对数据库的各种修改操作，用来表示修改操作的数据结构是 Log Event。不同的修改操作对应的不同的 Log Event。比较常用的 Log Event 有：Query Event、Row Event、Xid Event 等。binlog 文件的内容就是各种 Log Event 的集合。

binlog 文件中 Log Event 结构如下图所示：

binlog 写入机制

  1. 根据记录模式和操作触发事件生成的 Log Event（事件触发执行机制）；
  2. 将事务执行过程中产生 Log Event 写入缓冲区，每个事务线程都有一个缓冲区，Log Event 保存在一个 binlog_cache_mngr 数据结构中，在该结构中有两个缓冲区，一个是 stmt_cache，用于存放不支持事务的信息；另一个是 trx_cache，用于存放支持事务的信息。
  3. 事务在提交阶段会将产生的 Log Event 写入到外部 binlog 文件中。不同事务以串行方式将 Log Event 写入 binlog 文件中，所以一个事务包含的 Log Event 信息在 binlog 文件中是连续的，中间不会插入其他事务的 Log Event 。

binlog 刷盘策略

和 Redo Log 日志类似，binlog 也有着自己的刷盘策略，通过 sync_binlog 参数控制：

  • sync_binlog = 0：每次提交事务前将 binlog 写入 OS Cache，由操作系统控制什么时候刷到磁盘；
  • sync_binlog = 1：采用同步写磁盘的方式来写 binlog，不使用 OS Cache 来写 binlog；
  • sync_binlog = N：当每进行 N 次事务提交之后，调用一次 fsync 将 OS Cache 中的 binlog 强制刷到磁盘；

c. binlog 操作#

（1）binlog 状态查看

若未开启 binlog 功能，则需要修改 my.cnf/my.ini 配置文件，在 [mysqld] 下面增加 log_bin=javaboy_logbin，重启 MySQL 服务。

# 这个参数表示启用 binlog 功能，并指定产生的 binlog 日志文件的名称前缀
log-bin=javaboy_logbin

# binlog 记录内容的方式，记录被操作的每一行
binlog_format=ROW

# 设置一个 binlog 文件的最大字节(设置最大 100MB)
max_binlog_size=104857600

# 设置了 binlog 文件的有效期（单位：天）
expire_logs_days = 7

# binlog 日志只记录指定库的更新（配置主从复制的时候会用到）
# binlog-do-db=znvr_base

# binlog 日志不记录指定库的更新（配置主从复制的时候会用到）
# binlog-ignore-db=nacos_config

# 刷盘策略
sync_binlog=0

# 为当前服务取一个唯一的 id（MySQL5.7 之后需要配置）
server-id=1

（2）查看所有 binlog

可以看到，我这里目前只有一个日志文件，文件名为 javaboy_logbin.000001，File_size 表示这个文件占用的字节大小是 154。而使用下面那个命令可以看到最新的 binlog 日志文件名称以及最后一个操作事件的 Position 值。

（3）刷新 binlog

正常来说，一个 binlog 写满之后，会自动切换到下一个 binlog 开始写，不过我们也可以执行一个 flush logs 命令来手动刷新 binlog，手动刷新 binlog 之后，就会产生一个新的 binlog 日志文件，接下来所有的 binlog 日志都将记录到新的文件中。如下：

由上图可以看到，我们刷新日志之后，再通过 show master logs 去查看日志，发现日志文件已经多了一个新产生的了，然后再通过 show master status 去查看最新的日志文件信息，发现也已经变为 javaboy_logbin.000002。

（4）重置 binlog

reset master 可以重置 binlog 日志文件，让日志重新从 000001 开始记录，不过如果当前主机有一个或者多个从机在运行，那么该命令就运行不了（因为从机是通过 binlog 来实现数据库同步的，主机把 binlog 清空了，从机会报找不到 binlog 的错误）。

（5）查看 binlog

由于 binlog 是二进制日志文件，所以要是直接打开，那肯定是看不了的。所以使用官方工具：mysqlbinlog 命令。

虽然看起来乱糟糟的，不过仔细看着其实都有迹可循。因为我这里是一个新安装的数据库，里边只是创建了一个名为 javaboy 的库，然后创建了一个名为 user 的表加了两条数据，其他什么事情都没做，所以创建库的脚本我们其实能够从纷杂的文件中找到。

产生的日志文件中有一个 end_log_pos 是日志文件的 pos 点，这个将来在数据恢复的时候有用。

不过这种查看方式不够人性化，我们说 binlog 是按照事件来记录日志的，所以如果我们能够按照事件的方式查看日志，就会好很多，我们再来看看下命令：show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]。

这个表示以事件的方式来查看 binlog，这里涉及到几个参数：

  • log_name：可以指定要查看的 binlog 日志文件名，如果不指定的话，表示查看最早的 binlog 文件；
  • pos：从哪个 pos 点开始查看，凡是 binlog 记录下来的操作都有一个 pos 点，这个其实就是相当于我们可以指定从哪个操作开始查看日志，如果不指定的话，就是从该 binlog 的开头开始查看；
  • offset：这是是偏移量，不指定默认就是 0；
  • row_count：查看多少行记录，不指定就是查看所有。

我们来看一个简单的例子：

这下就清晰多了，我们可以看到之前的所有操作，例如：

  • 在 Pos 219-322 之间创建了一个库
  • 在 Pos 387-537 之间创建了一张表
  • 在 Pos 677-780 之间添加了一条记录
  • ...

这其实就是 Row 格式的 binlog。

（6）删除 binlog

-- 删除指定文件
purge binary logs to 'mysqlbinlog.000001';
-- 删除指定时间之前的文件
purge binary logs before '2022-10-18 00:00:00';
-- 清除所有文件
reset master;

可以通过设置 expire_logs_days 参数来启动自动清理功能。

mysql> show variables like '%expire_logs_days%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set, 1 warning (0.00 sec)

默认值为 0 表示没启用。设置为 1 表示超出 1 天 binlog 文件会自动删除掉。

（7）mysqldump 定期全部备份数据库数据，mysqlbinlog 可以做增量备份和恢复操作。

# 按指定时间恢复
mysqlbinlog --start-datetime="2022-10-18 00:00:00" --stopdatetime="2022-10-18 23:59:59" mysqlbinlog.000002 | mysql -uroot -p1234
# 按事件位置号恢复
mysqlbinlog --start-position=154 --stop-position=957 mysqlbinlog.000002 | mysql -uroot -p1234

d. 两阶段提交#

有了对这两个日志的概念性理解，我们再来看执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程。

mysql> update T set c=c+1 where ID=2;
  1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
  2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
  3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
  4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
  5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

这里我给出这个 update 语句的执行流程图，图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的。

你可能注意到了，最后三步看上去有点“绕”，将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。

为什么必须有“两阶段提交”呢？

这是为了让两份日志之间的逻辑一致。

要说明这个问题，我们得从文章开头的那个问题说起：怎样让数据库恢复到半个月内任意一秒的状态？前面我们说过了，binlog 会记录所有的逻辑操作，并且是采用“追加写”的形式。如果你的 DBA 承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有 binlog，同时系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。

当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：

  • 首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；
  • 然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。

这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。

好了，说完了数据恢复过程，我们回来说说，为什么日志需要“两阶段提交”。这里不妨用反证法来进行解释。

由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。

仍然用前面的 update 语句来做例子。假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

  • 先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
  • 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

你可能会说，这个概率是不是很低，平时也没有什么动不动就需要恢复临时库的场景呀？

其实不是的，不只是误操作后需要用这个过程来恢复数据。当你需要扩容的时候，也就是需要再多搭建一些备库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用 binlog 来实现的，这个“不一致”就会导致你的线上出现主从数据库不一致的情况。

简单说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

小结一下：

  1. Buffer Pool 是 MySQL 进程管理的一块内存空间，有减少磁盘 IO 次数的作用；
  2. MySQL 挂了有两种情况：操作系统挂了 MySQL 进程跟着挂了；操作系统没挂，但是 MySQL 进程挂了。
  3. Redo Log 是 InnoDB 存储引擎的一种日志，主要作用是崩溃恢复；
  4. Undo Log 是 InnoDB 存储引擎的一种日志，主要作用是事务回滚；
  5. binlog 是 MySQL Server 层的一种日志，主要作用是归档；

3. InnoDB 逻辑存储结构#

从 InnoDB 存储引擎的逻辑结构看，所有数据都被逻辑地存放在一个空间内，称为表空间（tablespace），而表空间由段（sengment）、区（extent）、页（page）组成。 在一些文档中 extend 又称块（block）。

InnoDB 把数据保存在表空间内，表空间可以看作是 InnoDB 存储引擎逻辑结构的最高层。本质上是一个由一个或多个磁盘文件组成的虚拟文件系统。InnoDB 用表空间并不只是存储表和索引，还保存了回滚段、双写缓冲区等。

3.1 Tablespace#

表空间（Tablespace）是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间等。

在 InnoDB 中存在两种表空间的类型：共享表空间和独立表空间。如果是共享表空间就意味着多张表共用一个表空间。如果是独立表空间，就意味着每张表有一个独立的表空间，也就是数据和索引信息都会保存在自己的表空间中。独立的表空间可以在不同的数据库之间进行迁移。可通过命令

mysql> show variables like 'innodb_file_per_table';

查看当前系统启用的表空间类型。目前最新版本已经默认启用独立表空间。

InnoDB 把数据保存在表空间内，表空间可以看作是 InnoDB 存储引擎逻辑结构的最高层。本质上是一个由一个或多个磁盘文件组成的虚拟文件系统。InnoDB 用表空间并不只是存储表和索引，还保存了回滚段、双写缓冲区等。

3.2 Segment#

段（Segment）由一个或多个区组成，区在文件系统是一个连续分配的空间（在 InnoDB 中是连续的 64 个页），不过在段中不要求区与区之间是相邻的。段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。当我们创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段。

3.3 Extent#

在 InnoDB 存储引擎中，一个区（extent）会分配 64 个连续的页。因为 InnoDB 中的页大小默认是 16KB，所以一个区的大小是 64*16KB=1MB。在任何情况下每个区大小都为 1MB，为了保证页的连续性，InnoDB 存储引擎每次从磁盘一次申请 4-5 个区。默认情况下，InnoDB 存储引擎的页大小为 16KB，即一个区中有 64 个连续的页。

3.4 Page#

页是 InnoDB 存储引擎磁盘管理的最小单位，每个页默认 16KB；InnoDB 存储引擎从 1.2.x 版本开事，可以通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K。若设置完成，则所有表中页的大小都为 innodb_page_size，不可以再次对其进行修改，除非通过 mysqldump 导入和导出操作来产生新的库。

页类型：

  • 数据页（B-tree Node）
  • Undo页（undo Log Page）
  • 系统页（System Page）
  • 事务数据页（Transaction system Page）
  • 插入缓冲位图页（Insert Buffer Page）
  • 未压缩的二进制大对象页（Uncompressd BLOB Page）
  • 压缩的二进制大对象页（compressd BLOB Page）

Page 是文件最基本的单位，无论何种类型的 Page，都是由 Page Header、Page Trailer 和 Page Body 组成。

A page contains records, but it also contains headers and trailers. An InnoDB page has 7 parts:

名称 中文名 占用空间 简单描述
File Header 文件头部 38 字节 页的一些通用信息
Page Header 页面头部 56 字节 数据页专有的一些信息
Infimum + Supremum Records 最小记录和最大记录 26 字节 两个虚拟的行记录
User Records 数据行记录 不确定 不确定
Free Space 空闲空间 不确定 页中尚未使用的空间
Page Directory 页面目录 不确定 页中的某些记录的相对位置
File Trailer 文件尾部 8 字节 校验页是否完整

3.5 Row#

InnoDB 存储引擎是按行（Row）进行存放的，每个 Page 存放的行记录也是有硬性定义的，最多允许存放 16KB/2-200，即 7992 行记录。

行包含了记录的字段值、事务ID（Trx id）、滚动指针（Roll Pointer）、字段指针（Field Pointers）等信息。

posted @   tree6x7  阅读(110)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛
主题色彩