MySQL的三种日志和MVCC原理

MySQL的在文件中是如何存储的？

答：数据是存在页中的，一页的大小是 16kb, 一个表由很多的页组成，这些页组成了 B+树。

img

MYSQL内存中，多个这样的数据结构组成一个双向链表

img

1. SQL语句是如何执行的呢？MySQL的逻辑架构图如下所示：

图片

1. 当我们需要更新一条数据时，是需要先从磁盘中取出来，更新后再持久化到磁盘中吗？ 答：不是的，如果这样的话，一条 SQL 的执行过程太慢了，因为对一个大磁盘文件的读写操作是要耗费大量时间的。 所以真正的执行过程是，当需要更新或者读取某条数据的时候，会把对应的页加载到内存中的 Buffer Pool 缓冲池中（默认为 128m 当然为了提高系统的并发度，你可以把这个值设置大一点） 图片

图片

当更新数据的时候，如果对应的页在 Buffer Pool 中，则直接更新 BP中的数据页即可，如果不在BP中，才会加载磁盘中对应的页到BP中，然后更新，此时BP中的页则跟磁盘中的页不一致，称为脏页。这些脏页是要被刷回磁盘中的。

①. BP不够用了，要给新加载的页腾位置，所以会利用改进的 LRU 算法，将最近最久未使用的脏页刷回磁盘。

②. 后台线程会在MySQL空闲的时候，将脏页刷回到磁盘中

③. redolog写满时

④. 数据库关闭时会将所有脏页刷回到磁盘中

redo log

问：如果脏页没有刷回，数据库宕机了怎么办？修改不就丢失了吗？

这就要说道 redo log了(重做日志文件，主要是记录数据物理页的修改)，内存中所做的修改都是写到 redo log buffer中的，这是内存中的一个缓冲区，用来存储redo 日志。

Redo log的大小是固定的，比如可以配置一组 4 个文件，每个文件的大小是 1G ，总大小就是 4 G ，从头开始写，写到末尾就从头再次开始写，循环顺序写的效率高于随机写。

图片

write pos是当前要写的位置，checkpoint是要擦除的位置，擦除前要把对应的脏页刷回到磁盘中。他两个之间的绿色区域是可以写的位置。当系统能支持的并发比较低的时候，可以看看对应的 redo log 是不是设置的太小了。太小的话会导致频繁的刷脏页，可以通过工具监控 redo log的大小。redo log的大小 = innodb_log_file_size * innodb_log_file_in_group

(默认为2)

redo log 是如何避免数据丢失的？

事务未提交，MySQL宕机，这种情况， Buffer Pool中的数据丢失，并且 redo log buffer中的日志也会丢失，不影响数据。

事务提交成功，redo log buffer中的数据没有刷到磁盘，此时会导致事务提交的数据丢失。

鉴于这种情况，我们可以设置 innodb_flush_log_at_trx_commit来决定redo log 的刷盘策略

# 查看 innodb_flush_log_at_trx_commit的配置
show global variables like 'innodb_flush_log_at_trx_commit'

innodb_flush_log_at_trx_commit值 作用 0 提交事务时，不会将redo log buffer中的数据写入os buffer，而是每秒写入os buffer并刷到磁盘 1 提交事务时，必须把redo log从内存刷入到磁盘文件中 2 提交事务时，将rodo log写入os buffer中，默认每隔1s将os buffer中的数据刷入磁盘

值为 0 或者 2 的时候都可能会造成事务更新丢失，所以一般系统中的 innodb_flush_log_at_trx_commit的值都会设置成 1

undo log

当我们修改一条数据的时候，会把原来的值写到 undo log 中，当这条更新语句在事务中执行的时候，事务回滚，就可以通过 undo log将数据恢复成原来的值。

图片

undo log在 MVCC 的实现中也扮演了重要的作用：

MVCC的实现

MVCC 多版本并发控制，通过读取指定版本的历史记录，并通过一些手段保证读取的记录符合事务所处的隔离级别，在不加锁的情况下解决读写冲突。

对于使用Innodb 存储引擎的表来说，聚集索引记录中都包含下面2个必要的隐藏列：

• trx_id:一个事务每次对某条聚集索引记录进行改动时，都会把该事务的事务id赋值给trx_id隐藏列
• roll_pointer:每次对某条聚集索引记录进行改动时，都会把旧版本写入undo日志中。这个隐藏列就相当于一个指针，通过它找到该记录修改前的信息。

例如：

一个记录name从貂蝉被依次修改成王昭君，西施，会有如下的记录，多个记录构成一个版本链

图片

首先，复习一下事务的隔离界别：

图片

建表数据如下：

CREATE TABLE `account` (
  `id` int(2) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `balance` int(3) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

设置表的隔离级别为读已提交，下面看一下不可重复读的例子：

不可重复读是指在事务1内，读取了一个数据，事务1还没有结束，事务2也访问了这个数据，修改了这个数据并提交，然后事务一又重复读取了这个数据，由于事务2修改了这个数据，所以，事务1两次读取的结果不一致，因此称为不可重复读。

图片

下面设置隔离级别为可重复读：

图片

这里设置成可重复读之后，当前事务就不受其他事务影响了。

MySQL是如何支持这两种隔离级别的呢？MVCC只对这两种隔离级别而言。下面看一下MVCC是如何实现的。

为了判断版本链中哪个版本对当前事务时可见的，MySQL设计出了ReadView的概念。4个重要的内容如下：

• m_ids:在生成ReadView时，当前系统中活跃的事务id列表
• min_trx_id：在生成ReadView时，当前系统中活跃的最小的事务id,也就是m_ids中的最小值
• max_trx_id:在生成ReadView时，系统应该分配给下一个事务的事务id值
• creator_trx_id:生成该ReadView的事务的事务id

当对表中的数据进行改动时，执行 insert/update/delete这些语句的时候，才会为事务分配唯一的事务id,否则一个事务的事务id值默认为0

max_trx_id并不是m_ids中的最大值，事务id是递增分配的。比如现在有事务id为1，2，3这三个事务，之后事务id为3的事务提交了，当有一个新的事务生成ReadView时，m_ids的值就包括1和2，min_trx_id的值就是1，max_trx_id的值就是4

MVCC判断版本链中哪个版本对当前事务是可见的过程如下：

图片

解释：

1. 如果被访问版本的 trx_id = creator_id,意味着当前事务在访问他自己修改过的记录，所以该版本可以被当前事务访问。
2. 如果被访问版本的 trx_id < min_trx_id,表明生成该版本的事务在当前事务生成 ReadView前已经提交，所以该版本可以被当前事务访问。
3. 被访问版本的 trx_id >= max_trx_id,表明生成该版本的事务在当前事务生成 ReadView之后才开启，该版本不可以被当前事务访问
4. 被访问版本的trx_id 是否在 m_ids列表中 是：创建 ReadView 时，该版本还是活跃的，该版本不可以被访问。顺着版本链找到下一个版本的数据，继续执行上面的步骤判断可见性，如果最后一个版本还不可见，意味着记录对当前事务不可见否：创建 ReadView时，生成该版本的事务已经被提交，该版本可以访问

下面结合例子理解一下：

Read Committed(读已提交)，每次读取数据前都生成一个 ReadView

图片

下面是三个事务的执行过程，一行代表一个时间点：

图片

分析一下5这个时间点的select 的执行过程：

1. 系统中有两个事务id分别是100和200的事务正在执行
2. 执行 select 语句时生成一个 ReadView, mids=[100,200], min_trx_id = 100, max_trx_id = 201, creator_trx_id = 0(select 这个事务没有执行更改操作，事务id默认是0)
3. 最新版本的name = 西施，该版本的trx_id值100， 在 mid 列表中，不符合可见性的要求，根据 roll_pointer 跳到下一个版本
4. 下一个版本的name = 王昭君，该版本的trx_id = 100 ,也不符合可见性要求，继续跳到下一版本
5. 下一版本name = 貂蝉，该版本的 trx_id = 10(已经提交) ,小于min_trx_id,因此最后返回的name = 貂蝉

图片

下面再分析一下8这个时间点的select 的执行过程：

1. 系统中有一个事务id为200的事务正在执行（事务id=100的事务已经提交）
2. 执行select 语句时生成一个ReadView, mids=[200], min_trx_id = 200, max_trx_id = 201, creator_trx_id = 0 (因为是查询，不涉及修改数据库，默认是0)
3. 最新版本的name = 杨玉环，该版本的trx_id = 200, 在mids列表中，不符合可见性要求，根据 roll_pointer 寻找下一版本
4. 下一版本name = 西施，该版本的trx_id = 100， 小于min_trx_id,因此最后返回的name = 西施

Repeatable Read（可重复读），只在第一次读取数据时生成一个 ReadView

图片

可重复读只在第一次读取时生成一次ReadView,所以每次读到的是相同的版本，所以name一直会是貂蝉。

分析5， 8， 10时间点的select查询操作

1. 系统中有两个正在执行的事务100， 200
2. 第一次执行select 语句生成ReadView, mids=[100,200], min_trx_id = 100, max_trx_id = 200, creator_trx_id = 0 (因为查询不涉及修改数据库，所以默认事务id = 0)
3. 最新版本name = 西施，该版本的trx_id = 100,在mids列表中，不可见，根据 roll_pointer查找下一版本
4. 下一版本是王昭君，trx_id = 100, 在mids中，不可见，下一版本
5. 下一版本是貂蝉，trx_id =10（已经提交），trx_id < min_trx_id,所以最后查询到的结果是貂蝉

分析 8 时间点的select查询

1. 因为可重复读只在事务的第一次查询时候生成 ReadView
2. 最新版本name = 杨玉环， trx_id = 200,在mids中不可见，roll_pointer下一版本
3. 下一版本name=西施，trx_id = 100,在mids中不可见，下一版本
4. name = 王昭君，trx_id = 100, 不可见，下一版本
5. name = 貂蝉，trx_id = 10, trx_id < min_trx_id,可见,结果是貂蝉

分析10 时间点的select查询

同上面的结果一样，需要推到name = 貂蝉才满足 trx_id < (min_trx_id = 100),所以最后的结果是貂蝉。

bin log 主从库之间如何同步数据？

当我们把MySQL的主库数据同步到从库，或者其他数据源时，例如 es, bi库时，只需要订阅主库的binlog即可。

图片

• 和redo log 的区别： redo log是Innodb 存储引擎特有的，binlog 是MySQL的 server 层实现的，所有引擎都可以使用redo log 是物理日志，记录的是数据页上的修改， bin log 是逻辑日志，记录的是语句的原始逻辑。redo log 是固定空间，循环写。 Binlog 是追加写，当binlog 文件写到一定的大小后会切换到下一个，并不会覆盖以前的日志。 sync_binlog值 作用 0 不立即刷盘，将binlog写入os buffer，由操作系统决定何时刷盘 ，有可能会丢失多个事务的数据 1 将binlog写入os buffer，每n个事务提交后，将os buffer的数据刷盘 一般来说，将binlog 的刷盘策略设置为 1即可 接下来看一下将 id=2的行 c 字段加一的操作执行流程 图片

1. 引擎将新数据更新到内存中，将操作记录到 redo log中，此时redo log 处于 prepare状态，然后告知执行器执行完成了，可以提交事务
2. 执行器生成操作的binlog,并把binlog写入磁盘
3. 引擎将写入的redo log 改为提交状态，更新完成。

为什么把redo log的写入拆成 2 个步骤，即 prepare和commit两段提交？

因为不管先写redo Log还是binlog ，崩溃发生后，最终其实都有可能造成原库和用日志恢复的库不一致，而两段提交可以解决这个问题。redo log 和 binlog 具有并行关联，在恢复数据时，redo log 用于恢复主机故障时未更新的物理数据，binlog 用于备份操作，每个阶段的log 操作都是记录在磁盘的，在恢复数据时，redo log状态为 commit则说明binlog也成功，直接恢复数据；如果redo log是 prepare状态，则需要查询对应的binlog 事务是否成功，决定回滚还是执行。

经验之谈：

「1. 数据库支持的并发度不高」

在一些并发要求高的系统中，可以调高Buffer Pool和redo log，这样可以避免频繁的刷脏页，提高并发

「2. 事务提交很慢」

原来我负责的一个系统跑的挺正常的，直到上游系统每天2点疯狂调我接口，然后我这边都是事务方法，事务提交很慢。监控到Buffer Pool和redo log的设置都很合理，并没有太小，所以问题出在哪了？我也不知道

「后来dba排查到原因，把复制方式从半同步复制改为异步复制解决了这个问题」

「异步复制」：MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从库上，如果此时，强行将从提升为主，可能导致新主上的数据不完整

「半同步复制」：是介于全同步复制与全异步复制之间的一种，主库只需要等待至少一个从库节点收到并且 Flush Binlog 到 Relay Log 文件即可，主库不需要等待所有从库给主库反馈。同时，这里只是一个收到的反馈，而不是已经完全完成并且提交的反馈，如此，节省了很多时间

「全同步复制」：指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响

「3. 在一个方法中，我先插入了一条数据，然后过一会再查一遍，结果插入成功，却没有查出来」

这个比较容易排查，如果系统中采用了数据库的读写分离时，写插入的是主库，读的却是从库，binlog同步比较慢时，就会出现这种情况，此时只需要让这个方法强制走主库即可