MySQL之事务一致性，日志，MVCC

目录

• 1 MySQL事务
  • 1.1 MySQL事务操作
  • 1.2 MySQL事务保证原子性
    • 1.2.1 原子性介绍和保证
    • 1.2.2 隔离性讲解
• 2 MySQL日志
  • 2.1 二进制日志(binlog)
    • 2.1.1 定义
    • 2.1.2 操作binlog
    • 2.1.3 无法写二进制日志
      • 2.1.3.1 报错现象
      • 2.1.3.2 清理二进制日志
    • 2.1.4 什么时候写入 Bin Log
  • 2.2 错误日志
  • 2.3 通用查询日志
  • 2.4 慢查询日志
    • 2.4.1 定义
    • 2.4.2 操作
    • 2.4.3 慢查询工具 pt-query-digest
  • 2.5 redo log
    • 2.5.1 为什么需要redo log
    • 2.5.2 redo log基本概念
    • 2.5.3 redo log记录形式
    • 2.5.4 脏页
      • 2.5.4.1 定义
      • 2.5.4.2 更新语句 执行顺序
      • 2.5.4.3 为什么会出现脏页
      • 2.5.4.4 什么时候会刷脏页（flush）
      • 2.5.4.5 为什么刷脏页会导致SQL变慢
    • 2.5.5 redo log与binlog区别
    • 2.5.6 什么时候写入 Redo log
    • 2.5.7 二阶段提交
  • 2.6 undo log
    • 2.6.1 定义
    • 2.6.2 undo log 作用
    • 2.6.3 Undo Log 如何回滚到上一个版本
  • 2.7 中继日志 relay log
    • 2.7.1 简介
    • 2.7.2 中继日志作用
    • 2.7.3 工作流程
  • 2.8 缓冲池
    • 2.8.1 buffer pool
    • 2.8.2 change buffer
  • 2.9 log总结
• 3 MVCC
  • 3.1 定义
  • 3.2 快照读和当前读
    • 3.2.1 Undo Log
      • 3.2.1.1 隐式字段
    • 3.2.2 Read View
    • 3.2.3 MVCC规则
  • 3.3 MVCC整体操作流程
  • 3.4 MVCC总结

1 MySQL事务

1.1 MySQL事务操作

MySQL事务控制语句：

• 开启事务：START TRANSACTION 或者begin
• 提交事务：COMMIT
• 回滚事务：ROLLBACK
• SET AUTOCOMMIT = 0关闭自动提交 | 1开启自动提交

MySQL事务隔离级别
查看事务隔离级别：

SHOW VARIABLES LIKE 'tx_isolation';

修改隔离级别：

• 设置未提交读
  SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
• 提交读
  SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
• 可重复读
  SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
• 可串行化
  SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

附录：MySQL默认事务隔离级别是REPEATABLE_READ(读已提交)

参考spring事务管理机制

1.2 MySQL事务保证原子性

1.2.1 原子性介绍和保证

MySQL 作为一个关系型数据库，以最常见的 InnoDB引擎来说，是如何保证 ACID 的:

• 原子性（Atomicity ） ： 事务是最小的执行单位，不允许分割。原子性确保动作要么全部完成，要么完全不起作用；
  如何保证：
  通过undo log，undo log记录了这些回滚需要的信息，当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子
• 一致性（Consistency）： 执行事务前后，数据保持一致；
  如何保证：
  数据备份、主备、主主、主从都离不开 Bin log，需要依靠 Bin log 来同步数据，保证数据一致性
• 隔离性（Isolation）： 并发访问数据库时，一个事务不被其他事务所干扰
  如何保证：
  通过给操作的对象加悲观锁或者乐观锁，MVCC(undo log)来保证，RC不满足隔离性，RR满足隔离性
• 持久性（Durability）: 一个事务被提交之后。对数据库中数据的改变是持久的，即使数据库发生故障。
  如何保证：
  是通过redo来保证的

1.2.2 隔离性讲解

MVCC(Multi Version Concurrency Control)即多版本并发控制，一个行记录数据有多个版本对快照数据，这些快照数据在undo log中。
如果一个事务读取的行正在做DELELE或者UPDATE操作，读取操作不会等行上的锁释放，而是读取该行的快照版本。

2 MySQL日志

对于mysql来讲，读写任何数据都是在内存中进行操作的；下图为mysql写入数据以及日志处理的详细流程：

• 写入undo log，为了实现回滚的功能，在写入真实数据前需要记录它的回滚日志，防止写入完数据后无法进行回滚；
• 写入buffer pool或change buffer，在缓存中记录下数据内容；
• 为了防止mysql崩溃内存中的数据丢失，此时会记录下redo log，记录redo log时也是写入它的buffer，通过不同的刷盘策略刷入到磁盘redo log文件中；
• 为了实现主从同步，数据恢复功能，mysql提供了binlog日志，写入完redo log后写binlog文件；
• 为了使binlog和redo log保持数据一致，这里采用的二阶段提交，写入binlog成功会再在redo log buffer中写入commit；
• 对redo log进行刷盘；
• 对buffer pool中的数据进行刷盘。

2.1 二进制日志(binlog)

2.1.1 定义

binlog用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog是mysql的逻辑日志，并且由Server层进行记录，使用任何存储引擎的mysql数据库都会记录binlog日志。

• 逻辑日志：可以简单理解为记录的就是sql语句。
• 物理日志：因为mysql数据最终是保存在数据页中的，物理日志记录的就是数据页变更。

binlog是通过追加的方式进行写入的，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志

binlog 有三种格式：

• Statement（Statement-Based Replication,SBR）：每一条会修改数据的 SQL 都会记录在 binlog 中。
  • 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
  • 缺点：由于记录的只是执行语句，为了这些语句能在备库上正确运行，还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在备库得到和在主库端执行时候相同的结果
• Row（Row-Based Replication,RBR）：不记录 SQL 语句上下文信息，仅保存哪条记录被修改。
  • 优点：binlog 中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。
  • 缺点：可能会产生大量的日志内容。
• Mixed（Mixed-Based Replication,MBR）：Statement 和 Row 的混合体。
  • 一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式

binlog使用场景
在实际应用中，binlog的主要使用场景有两个，分别是主从复制和数据恢复

• 主从复制：在Master端开启binlog，然后将binlog发送到各个Slave端，Slave端重放binlog从而达到主从数据一致。
• 数据恢复：通过使用mysqlbinlog工具来恢复数据。

binlog刷盘时机
对于InnoDB存储引擎而言，只有在事务提交时才会记录biglog，此时记录还在内存中，那么biglog是什么时候刷到磁盘中的呢？mysql通过sync_binlog参数控制biglog的刷盘时机，取值范围是0-N：

• 0：不去强制要求，由系统自行判断何时写入磁盘；
• 1：每次commit的时候都要将binlog写入磁盘；
• N：每N个事务，才会将binlog写入磁盘。

从上面可以看出，sync_binlog最安全的是设置是1，这也是MySQL 5.7.7之后版本的默认值。但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能。

2.1.2 操作binlog

配置二进制日志，在启动日志文件的my.ini文件中添加log-bin,然后重启

[mysqld]
log-bin[=dir\[filename]]
若不设置文件名就使用主机名

查看日志
在重启后会有文件：cjgong-bin.000001
在mysql的bin文件夹中使用mysqlbinlog命令打开二进制文件查看：mysqlbinlog cjgong-bin.000001

停止日志文件；只用把如下注释调就可以了

[mysqld]
#log-bin[=dir\[filename]]

删除日志文件

PURGE MASTER LOGS TO filename.number 删除指定日志文件

或者执行如下命令

PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:MM:ss'
执行上述命令，可以删除指定时间（yyyy-mm-dd hh:MM:ss）之前所创建的所有二进行日志文件

刷新日志文件：

flush logs; 刷新当前日志文件并产生一个新的日志文件

2.1.3 无法写二进制日志

2.1.3.1 报错现象

Binlog是MySQL中一个很重要的日志，记录了对数据库进行变更的操作，
但是不包括 select操作以及 show 操作，因为这类操作对数据库本身没有没有修改。
如果想记录 select和 show 的话，那就需要开启全查询日志。
另外 binlog 还包括了执行数据库更改操作时间和执行时间等信息。
binlog 是 MySQL Server 层记录的二进制日志文件，逻辑层面

2.1.3.2 清理二进制日志

show variables like ‘%binlog_expire_logs_seconds%’ ;

mysql 8开始 expire_logs_days废弃
启用binlog_expire_logs_seconds设置binlog自动清除日志时间
保存时间 以秒为单位；默认2592000 30天
14400 4小时；86400 1天；259200 3天
自动删除

set global binlog_expire_logs_seconds=86400;
set global binlog_expire_logs_seconds=2592000;

手动删除
默认日志文件达到 1G 都会重新生成一个新的二进制日志文件

select @@max_binlog_size;

#binlog.000025 之前的日志都会被删除
PURGE BINARY LOGS TO ‘binlog.000025’;

#时间’2020-04-28 23:59:59’之前的日志都会被删除
PURGE BINARY LOGS BEFORE ‘2020-04-28 23:59:59’;

#清空历史二进制日志，从 000001 开始重新
RESET MASTER;
mysql> select @@binlog_format ;

2.1.4 什么时候写入 Bin Log

Bin Log 采用预写日志 WAL，即先写日志，再写磁盘。
写入 Bin Log 之后的事务流程：第5步箭头下是binlog buffer

先写处于 prepare 状态的 Redo Log，事务提交后，再写处于 commit 状态的 Redo Log，即二阶段提交。

那为什么不直接持久化到磁盘呢？

直接写入磁盘会严重影响到性能：
InnoDB 在磁盘中存储的基本单元是页，可能本次修改只变更一页中几个字节，但是需要刷新整页的数据，就很浪费资源。
一个事务可能修改了多页中的数据，页之间又是不连续的，就会产生随机IO，性能更差。

在使用 Bin log 时，需要注意：

• Bin log 的开启和配置：需要确保 Bin log 已经开启，并根据实际需求配置相关参数，如 Bin log 格式、Bin log 大小和保留时间等。
• 定期备份 Bin log ：为了保证数据恢复和故障恢复的能力，需要定期备份 Bin log 文件

完整的流程图：

2.2 错误日志

MySQL的错误日志主要记录MySQL服务实例每次启动、停止的详细信息，以及MySQL实例运行过程中产生的警告或者错误信息。和其他的日志不同，MySQL的error日志必须开启，无法关闭

配置错误日志，在启动日志文件的my.ini文件中添加如下log-error文件

[mysqld]
log-error[=dir\[filename]]
若不设置文件名就使用主机名

查看错误日志文件：
文本文件，可直接用文本工具查看
刷新日志文件：flush-logs
删除错误日志文件:
直接删除命令删除日志文件

2.3 通用查询日志

MySQL普通查询日志，记录MySQL服务实例所有的操作，如select、update、insert、delete等操作，无论该操作是否成功执行。还有MySQL客户机与MySQL服务端连接及断开的相关信息，无论连接成功还是失败。

配置通用查询日志，在启动日志文件的my.ini文件中添加如下log文件

[mysqld]
log [=dir\[filename]]
若不设置文件名就使用主机名

或者使用命令启动：set global general_log=on;
使用命令查看是否启动：show variables like '%general_log%' ;

查看日志文件；
文本文件，可直接用文本工具查看

停止日志文件；
在my.ini文件中注释如下log文件

[mysqld]
#log [=dir\[filename]]

或者使用命令停止：set global general_log=off;
删除日志文件：直接删除命令删除日志文件
刷新日志文件：flush-logs

2.4 慢查询日志

2.4.1 定义

使用MySQL慢查询日志可以有效的跟踪 执行时间过长 或者 没有使用索引的查询语句。这种包括select 语句，update语句，delete语句，以及insert语句，为优化查询提供帮助。与普通查询日志不同的另一个区别在于，慢查询日志只包含成功执行过的查询语句
其中：

• slow_query_log : 设置慢查询日志是否开启，例：show variables like 'slow_query_log';
• slow_query_log_file : 慢查询日志一旦开启，MySQL实例将自动创建慢查询日志文件。slowquerylog_file 所指定的文件，存放慢查询日志内容。
• long_query_time : 设置了慢查询的时间阈值。默认阈值是10s。
• log_quries_not_using_indexes : 是否将不使用索引的查询语句记录到慢查询日志中，无论查询速度有多快
• log_output : 参数可以设置慢查询日志的输出形式。默认为FILE,可以设置为TABLE

2.4.2 操作

配置通用查询日志在启动日志文件的my.ini文件中添加如下log-slow-queries文件

[mysqld]
log-slow-queries[=dir\[filename]]
若不设置文件名就使用主机名
long_query_time=n
long_query_time是设置超时时间的阈值单位为秒

通过命令设置环境变量slow_query_log进行慢查询日志的动态控制，on表示开启：set global slow_query_log=on;
通过命令设置慢查询日志最大允许的时间，单位为秒：set global long_query_time=3;
使用命令查看是否启动：show variables like '%slow_query%' ;

查看日志文件
文本文件，可直接用文本工具查看
分析慢查询日志文件
分析慢查询日志
对应的工具为mysqldumpslow.pl，常用参数如下：

• -s为分析慢查询日志时指定排序参数，可选的有：al表示平均锁定时间，ar表示平均返回记录数，at表示平均查询时间。
• -t参数表示只显示指定的行数

分析慢查询日志例子：C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqldumpslow.pl -s at -t 1 "C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.6\data\MHX0JCJYUC7PDAA-slow.log”

注意：在分析慢查询日志时，mysqldumpslow.pl为perl语言编写的脚本，执行该脚本需要对应的perl语言环境，perl环境的安装包可以在http://www.perl.org/下载。

停止慢查询日志
在my.ini文件中注释如下log文件

[mysqld]
#log-slow-queries[=dir\[filename]]

或者使用命令停止：set global slow_query_log=off;

删除慢查询日志文件，直接执行删除命令
重新刷新慢查询日志：flush-logs

2.4.3 慢查询工具 pt-query-digest

pt-query-digest 可以从 Percona Toolkit 官方网站（https://www.percona.com/software/database-tools/percona-toolkit）获取 pt-query-digest 工具。Percona Toolkit 是一组用于 MySQL 和 MongoDB 数据库的开源工具集，包含了许多用于数据库性能优化、监控和管理的工具。

使用pt-query-digest工具分析最近一周的mysql-slow.log

pt-query-digest --since=148h mysql-slow.log | less

结果第一部分图片

最近一个星期内，总共记录的慢查询执行花费时间为25403s，最大的慢sql执行时间为266s，平均每个慢sql执行时间5s，平均扫描的行数为1766万

结果第二部分图片

select arrival_record操作记录的慢查询数量最多有4万多次，平均响应时间为4s，delete arrival_record记录了6次，平均响应时间258s。

2.5 redo log

redo log 是重做日志

• 记录了数据页上的改动
• 指事务中修改了的数据，将会备份存储
• 发生数据库服务器宕机、或者脏页未写入磁盘，可以通过redo log恢复
• 它是Innodb存储引擎独有的

2.5.1 为什么需要redo log

我们都知道，事务的四大特性里面有一个是持久性，具体来说就是只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态。那么mysql是如何保证一致性的呢？最简单的做法是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，主要体现在两个方面：

• Innodb是以页为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了
• 一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差

因此mysql设计了redo log，具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。
redo log进行刷盘的效率要远高于数据页刷盘，具体表现如下 :

• redo log体积小，只记录了哪一页修改的内容，因此体积小，刷盘快
• redo log是一直往末尾进行追加，属于顺序IO。效率显然比随机IO来的快
• redo log 主要用于 MySQL 异常重启后的一种数据恢复手段，确保了数据的一致性。其实是为了配合 MySQL 的 WAL 机制。因为 MySQL 进行更新操作，为了能够快速响应，所以采用了异步写回磁盘的技术，写入内存后就返回。但是这样，会存在 crash 后内存数据丢失的隐患，而 redo log具备 crash safe 的能力

redo log 为什么可以保证crash safe机制：

• 因为 redo log 每次更新操作完成后，就一定会写入的，如果写入失败，说明此次操作失败，事务也不可能提交。
• redo log 内部结构是基于页的，记录了这个页的字段值变化，只要crash后读取 redo log 进行重放，就可以恢复数据。

什么是WAL技术

• WAL，中文全称是 Write-Ahead Logging，它的关键点就是日志先写内存，再写磁盘。MySQL 执行更新操作后，在真正把数据写入到磁盘前，先记录日志。
• 好处是不用每一次操作都实时把数据写盘，就算 crash 后也可以通过redo log 恢复，所以能够实现快速响应 SQL 语句

2.5.2 redo log基本概念

redo log包括两部分：一个是内存中的日志缓冲(redo log buffer)，另一个是磁盘上的日志文件(redo log file)。mysql每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。这种先写日志，再写磁盘的技术就是MySQL里经常说到的WAL(Write-Ahead Logging) 技术。

在计算机操作系统中，用户空间(user space)下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统内核空间(kernel space)缓冲区(OS Buffer)。因此，redo log buffer写入redo log file实际上是先写入OS Buffer，然后再通过系统调用fsync()将其刷到redo log file中，过程如下：

mysql支持三种将redo log buffer写入redo log file的时机，可以通过innodb_flush_log_at_trx_commit参数配置，各参数值含义如下：

2.5.3 redo log记录形式

前面说过，redo log实际上记录数据页的变更，而这种变更记录是没必要全部保存，因此redo log实现上采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志。如下图：

图示说明：redo log buffer(内存中)是由首尾相连的四个文件组成的，它们分别是：ib_logfile_1、ib_logfile_2、ib_logfile_3、ib_logfile_4

同时我们很容易得知，在innodb中，既有redo log需要刷盘，还有数据页也需要刷盘，redo log存在的意义主要就是降低对数据页刷盘的要求。在上图中，write pos表示redo log当前记录的LSN(逻辑序列号)位置，check point表示数据页更改记录刷盘后对应redo log所处的LSN(逻辑序列号)位置。

write pos到check point之间的部分是redo log空着的部分，用于记录新的记录；check point到write pos之间是redo log待落盘的数据页更改记录。当write pos追上check point时，会先推动check point向前移动，空出位置再记录新的日志。

启动innodb的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。因为redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如binlog)要快很多。
重启innodb时，首先会检查磁盘中数据页的LSN，如果数据页的LSN小于日志中的LSN，则会从checkpoint开始恢复。

还有一种情况，在宕机前正处于checkpoint的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度，此时会出现数据页中记录的LSN大于日志中的LSN，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做

2.5.4 脏页

2.5.4.1 定义

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为脏页。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为干净页。一般有更新SQL才可能会导致脏页，我们回忆一下：一条更新语句是如何执行的

2.5.4.2 更新语句 执行顺序

以下的这个更新SQL，如何执行的呢？

update t set c=c+1 where id=666;

1. 对于这条更新SQL，执行器会先找引擎取id=666这一行。如果这行所在的数据页本来就在内存中的话，就直接返回给执行器。如果不在内存，就去磁盘读入内存，再返回。
2. 执行器拿到引擎给的行数据后，给这一行C的值加一，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，但是此时redo log 是处于prepare状态。
4. 执行器生成这个操作的binlog，并把binlog写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。
   

InnoDB 在处理更新语句的时候，只做了写日志这一个磁盘操作。这个日志叫作redo log（重做日志）。平时更新SQL执行得很快，其实是因为它只是在写内存和redo log日志，等到空闲的时候，才把redo log日志里的数据同步到磁盘中。

有些小伙伴可能有疑惑，redo log日志不是在磁盘嘛？那为什么不慢？其实是因为写redo log的过程是顺序写磁盘的。磁盘顺序写会减少寻道等待时间，速度比随机写要快很多的

2.5.4.3 为什么会出现脏页

更新SQL只是在写内存和redo log日志，等到空闲的时候，才把redo log日志里的数据同步到磁盘中。这时内存数据页跟磁盘数据页内容不一致,就出现脏页。

2.5.4.4 什么时候会刷脏页（flush）

InnoDB存储引擎的redo log大小是固定，且是环型写入的，如下图

那什么时候会刷脏页？有几种场景：

1. redo log写满了，要刷脏页。这种情况要尽量避免的。因为出现这种情况时，整个系统就不能再接受更新啦，即所有的更新都必须堵住
2. 内存不够了，需要新的内存页，就要淘汰一些数据页，这时候会刷脏页
   InnoDB 用缓冲池（buffer pool）管理内存,而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用
3. MySQL 认为系统空闲的时候，也会刷一些脏页
4. MySQL 正常关闭时，会把内存的脏页都 flush 到磁盘上

2.5.4.5 为什么刷脏页会导致SQL变慢

1. redo log写满了，要刷脏页，这时候会导致系统所有的更新堵住，写性能都跌为0了，肯定慢。一般要杜绝出现这个情况
2. 一个查询要淘汰的脏页个数太多，一样会导致查询的响应时间明显变长

2.5.5 redo log与binlog区别

由binlog和redo log的区别可知：binlog日志只用于归档，只依靠binlog是没有crash-safe(崩溃安全)能力的。但只有redo log也不行，因为redo log是InnoDB特有的，且日志上的记录落盘后会被覆盖掉。因此需要binlog和redo log二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失

binlog和redolog的不同点

	redo log	binlog
作用	用于崩溃恢复	主从复制和数据恢复
实现方式	InnoDb存储引擎实现	Server 层实现的，所有引擎都可以使用
记录方式	循环写的方式记录，写到结尾时，会回到开头循环写日志	通过追加的方式记录，当文件尺寸大于配置值后，后续日志会记录到新的文件上
文件大小	文件大小是固定的	通过配置参数max_binlog_size 设置每个binlog文件大小
crash-safe能力	具有	没有
日志类型	物理日志，记录的是“在某个数据页上做了什么修改”	逻辑日志，记录的是这个语句的原始逻辑

2.5.6 什么时候写入 Redo log

Redo log 同样采用预写日志 WAL 的方式。
在使用 Redo log 时，需要注意：

• Redo log 的大小和数量配置：根据实际需求和系统负载，配置合适的Redo log大小和数量，以确保足够的持久化能力和性能。
• 定期刷写 Redo log ：为了保证已提交的事务能够及时写入磁盘，需要配置合理的 Redo log 刷写策略，以避免数据丢失。
• 监控 Redo log 的使用情况：定期监控 Redo log 的使用情况，以便及时调整配置参数，并确保数据库的可靠性和性能。

2.5.7 二阶段提交

两阶段提交主要有三步曲：

• redo log 在写入后，进入prepare状态
• 执行器写入bin log
• 进入commit状态，事务可以提交。

为什么需要两阶段提交呢?

• 如果不用两阶段提交的话，可能会出现这样情况：bin log写入之前，机器 crash 导致需要重启。重启后 redo log 继续重放 crash 之前的操作，而当 bin log 后续需要作为备份恢复时，会出现数据不一致的情况。
• 如果是 bin log commit之前 crash，那么重启后，发现redo log是prepare状态且bin log完整（bin log写入成功后，redo log会有bin log的标记），就会自动commit，让存储引擎提交事务。
• 两阶段提交就是为了保证redo log和binlog数据的安全一致性。只有在这两个日志文件逻辑上高度一致了。才能放心的使用redo log 将数据库中的状态恢复成 crash 之前的状态，使用 binlog 实现数据备份、恢复、以及主从复制

如果不是两阶段提交, 先写 redo log 和先写 bin log 两种情况各会遇到的问题：

• 先写redo log，crash后 bin log 备份恢复时少了一次更新，与当前数据不一致。
• 先写bin log，crash后，由于redo log没写入，事务无效，所以后续bin log备份恢复时，数据不一致。

2.6 undo log

2.6.1 定义

undo log（回滚日志）：记录需要回滚的日志信息，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句
回滚日志主要记录已经部分完成并且写入硬盘的未完成事务，默认情况情况下，回滚日志的信息记录在表空间文件，共享表空间文件ibdata1或者独享表空间中。

数据库事务四大特性中有一个是原子性，具体来说就是 原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。

实际上，原子性底层就是通过undo log实现的。undo log主要记录了数据的逻辑变化，比如一条INSERT语句，对应一条DELETE的undo log，对于每个UPDATE语句，对应一条相反的UPDATE的undo log，这样在发生错误时，就能回滚到事务之前的数据状态

同时，undo log也是MVCC(多版本并发控制)实现的关键

2.6.2 undo log 作用

回滚数据：当数据发生异常错误时，根据执行 undo log 就可以回滚到事务之前的数据状态，保证原子性，要么全部成功，要么全部失败
MVCC 一致性视图：通过 undo log 找到对应的数据版本号，是保证 MVCC 视图的一致性的必要条件

2.6.3 Undo Log 如何回滚到上一个版本

首先，通过两个隐藏列 trx_id（最近一次提交事务的 ID）和 roll_pointer（上个版本的地址），建立一个版本链。然后，在事务中读取时生成一个 ReadView（读视图），在 Read Committed 隔离级别下，每次读取都会生成一个读视图，而在 Repeatable Read 隔离级别下，只会在第一次读取时生成一个读视图。

在使用 Undo log 时，需要注意：

• Undo log 的管理和清理：由于 Undo log 会占用存储空间，需要定期清理不再需要的 Undo log，以释放存储空间。
• 配置 Undo log 的大小：根据数据库的并发事务量和数据修改量，合理配置 Undo log 的大小，以确保足够的空间来存储 Undo log 信息。
• 定期备份 Undo log：为了避免数据丢失，定期备份 Undo log 文件，以便在需要恢复数据时使用。

2.7 中继日志 relay log

MySQL 集群中从库同步主库数据的方式可以是异步模式或半同步模式。具体使用哪种方式取决于集群的配置和需求。

• 异步模式：在异步同步模式下，主库将变更操作写入二进制日志（binlog），而从库通过异步方式从主库读取并应用这些变更。主库不会等待从库确认接收到变更，因此主库的写操作不会受到从库的影响。这种方式下，从库的数据可能存在一定程度的延迟，即主从复制延迟。
• 半同步模式：在半同步模式下，主库在执行写操作后会等待至少一个从库确认接收到变更，然后才会继续处理下一个写操作。这样可以确保至少有一个从库与主库保持一致性。如果等待超时或没有可用的从库，则切换回异步模式。半同步复制提供了更高的数据一致性和可靠性，但也会引入一定程度的延迟。

需要注意的是，无论是异步模式还是半同步模式，在网络不稳定或者高负载情况下都可能导致从库与主库之间出现延迟。因此，在读取数据时需要考虑主从复制延迟带来的影响。
MySQL 集群中从库同步主库数据可以是异步同步或半同步同步的方式。异步同步模式下从库的数据可能存在一定的延迟，半同步模式提供了更高的数据一致性和可靠性，但也会引入延迟。在读取数据时需要注意主从复制延迟对数据一致性的影响

2.7.1 简介

中继日志（relay log）只在主从服务器架构的从服务器上存在。从服务器（slave）为了与主服务器(Master)保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步。

搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录下。
文件名的格式是：从服务器名 - relay-bin.序号。中继日志还有一个索引文件：从服务器名 - relay-bin.index，用来定位当前正在使用的中继日志。

2.7.2 中继日志作用

中继日志用于主从服务器架构中，从服务器用来存放主服务器二进制日志内容的一个中间文件。从服务器通过读取中继日志的内容，来同步主服务器上的操作。
中继日志是连接mastert(主服务器)和slave(从服务器)的信息，它是复制的核心，I/O线程将来自master的binlog存储到中继日志中，中继日志充当缓冲，这样master不必等待slave执行完成就可以发送下一个binlog

作用主要有以下几点：

• 缓冲区：Relay log 在从服务器上充当一个缓冲区，存储从主服务器接收到的 binlog 事件。这样，在从服务器繁忙或者主服务器产生 binlog 事件的速度远大于从服务器应用 binlog 事件的速度（例如，在高写入负载的情况下）时，从服务器仍然可以保持与主服务器的数据同步。
• 容错：Relay log 可以在从服务器宕机或连接中断情况下提供容错能力。当从服务器恢复正常工作或重新连接到主服务器时，从服务器可以从 Relay log 的末尾处继续应用未处理的 binlog 事件，从而确保数据的一致性。
• 多从服务器支持：主服务器可以将 binlog 事件发送给多个从服务器。这些从服务器可以独立地根据自己的 Relay log 应用这些事件，从而实现多个从服务器与主服务器数据的同步。

2.7.3 工作流程

Relay log 的工作流程如下：

• 从服务器上运行的 IO 线程负责连接到主服务器，并从主服务器请求 binlog 事件。
• IO 线程将接收到的 binlog 事件写入从服务器上的 Relay log。
• 从服务器上运行的 SQL 线程负责读取 Relay log 中的 binlog 事件，并根据这些事件应用更改到从服务器的数据文件。
• 当从服务器成功应用了 Relay log 中的所有事件，从服务器会删除已处理的 Relay log 文件，以节省磁盘空间

2.8 缓冲池

2.8.1 buffer pool

innodb中无论是查询还是写绝大部分都是在buffer pool中进行操作的，它相当于innodb的缓存区，可以通过show engine innodb status来查看buffer pool的使用情况；可以通过innodb_buffer_pool_size来设置buffer pool的大小，线上几个G内存都是正常的，但无论给多大内存都会有不够的时候，innodb采用了变种的LRU算法对数据页进行淘汰；如下图：

传统的LRU算法当碰到扫描一张大表时可能会直接把buffer pool中的所有页都更换为该表的数据，但这张表可能就使用一次，并不是热点数据；
innodb为了避免这种场景发生，会把整个buffer pool按照 5:3 分成了young区域和old区域；绿色区域就是young区域也就是热点数据区域，紫色区域就是old区域也就是冷数据区域；
整体的淘汰流程为：

• 如果想访问绿色区域内的数据，会把访问页直接放在young head处；
• 如果想访问一个不存在的页，会把tail页淘汰掉，并且把新访问的数据页插入在old head处；
• 如果访问old区域的数据页，并且这个数据页在LRU链表中存在的时间超过了innodb_old_blocks_time(默认1000毫秒)，就把它移动到yound head处；
• 如果访问old区域的数据页，并且这个数据页在LRU链表中存在的时间短于innodb_old_blocks_time，把该页移动到old head处。

在上图中可以看到除了LRU链表还有一个Flush链表，它是用来管理脏页的；在写入数据时绝大部分都会先写入buffer pool中，再更改buffer pool中的页数据时，该页就变成了脏页，此时就会被加入到flush链表中，定时会把flush中的脏页刷到.idb数据文件中。

2.8.2 change buffer

在介绍buffer pool时用的是绝大部分操作，是因为在innodb中还存在change buffer，还有一部分操作是写入change buffer的。
change buffer的定义是当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，innodb会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中加载这个数据页了，如果有查询需要访问这个数据页的时候，再将数据页读到内存中，然后执行change buffer中与这个页有关的操作，这样就能保证这个数据的正确性。
change buffer用的是buffer pool中的内存，可以通过innodb_change_buffer_max_size来设置它占用buffer pool的内存比例。使用change buffer的前提条件是该数据页还没被加载到buffer pool中，并且如果是根据唯一索引进行更新，由于要检查数据的唯一性，必须把数据页加载到buffer pool中是无法享受change buffer带来的收益的。

2.9 log总结

binlog是属于mysql Server层的，属于整个mysql，而redo log、undo log是属于innodb存储引擎独有的，redo log、undo log是事务日志，binlog是二进制日志负责记录对mysql数据库有修改的sql操作，Relay log 是 MySQL 服务器的一种日志，用于主从复制

数据记录方式：

• Bin log：以二进制格式记录数据库的修改操作。
• Redo log：以固定大小的物理日志文件记录数据库页的物理修改。
• Undo log：以逻辑方式记录事务执行过程中旧值的备份。
• Relay log：二进制日志格式记录

3 MVCC

3.1 定义

一想到并发控制，很多人第一反应就是加锁，的确，加锁确实是解决并发问题最常见的方案。但是，其实除了加锁以外，在数据库领域，还有一种无锁的方案可以来实现并发控制，那就是大名鼎鼎的MVCC，点击了解MySQL各种锁分析

MVCC，是Multiversion Concurrency Control的缩写，翻译过来是多版本并发控制，也是一种并发控制的解决方案。

我们知道，在数据库中，对数据的操作主要有2中，分别是读和写，而在并发场景下，就可能出现以下三种情况：

读-读并发
读-写并发
写-写并发

我们都知道，在没有写的情况下发读-读并发不会出现问题的，而写-写并发这种情况比较常用的就是通过加锁的方式实现。那么，读-写并发则可以通过MVCC的机制解决

3.2 快照读和当前读

要想搞清楚MVCC的机制，最重要的一个概念那就是快照读。

• 快照读，是指读取的是快照数据，即快照生成的那一刻的数据，像我们常用的普通的SELECT语句在不加锁情况下就是快照读。如：SELECT * FROM xx_table WHERE ...
• 当前读，是指读取最新数据，所以，加锁的SELECT，或者对数据进行增删改都会进行当前读，比如：

SELECT * FROM xx_table LOCK IN SHARE MODE;
SELECT * FROM xx_table FOR UPDATE;
INSERT INTO xx_table ...
DELETE FROM xx_table ...
UPDATE xx_table ...

可以说快照读是MVCC实现的基础，而当前读是悲观锁实现的基础。
那么，快照读读到的快照是从哪里读到的的呢？换句话说，快照是存在哪里的呢？

3.2.1 Undo Log

undo log是Mysql中比较重要的事务日志之一，顾名思义，undo log是一种用于回退的日志，在事务没提交之前，MySQL会先记录更新前的数据到 undo log日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 undo log来进行回退。

这里面提到的存在undo log中的更新前的数据就是我们前面提到的快照。所以，这也是为什么很多人说UndoLog是MVCC实现的重要手段的原因。

那么，一条记录在同一时刻可能有多个事务在执行，那么，undo log会有一条记录的多个快照，那么在这一时刻发生SELECT要进行快照读的时候，要读哪个快照呢？

这就需要用到另外几个信息了。

3.2.1.1 隐式字段

其实，数据库中的每行记录中，除了保存了我们自己定义的一些字段以外，还有一些重要的隐式字段的：

• db_row_id：隐藏主键，如果我们没有给这个表创建主键，那么会以这个字段来创建聚簇索引。
• db_trx_id：对这条记录做了最新一次修改的事务的ID
• db_roll_ptr：回滚指针，指向这条记录的上一个版本，其实指向的就是Undo Log中的上一个版本的快照的地址。

因为每一次记录变更之前都会先存储一份快照到undo log中，那么这几个隐式字段也会跟着记录一起保存在undo log中，就这样，每一个快照中都有一个db_trx_id字段记录了本次变更的事务ID，以及一个db_roll_ptr字段指向了上一个快照的地址

这样，就形成了一个快照链表：

有了undo log，又有了几个隐式字段，我们好像还是不知道具体应该读取哪个快照，那怎么办呢？

3.2.2 Read View

这时候就需要Read View登场了，Read View 主要来帮我们解决可见性的问题的, 即它会来告诉我们本次事务应该看到哪个快照，不应该看到哪个快照。

在 Read View 中有几个重要的属性：

• trx_ids，系统当前未提交的事务ID的列表
  表示在生成readview时当前系统中活跃的读写事务的事务id列表
• low_limit_id，未提交的事务中最大的事务ID
  表示生成的readview时系统中应该分配给下一个事务的id值。其实是系统最大的事务id值，这里要注意是系统中的事务id，需要区别与正在活跃的事务id
注意：low_limit_id并不是trx_ids中的最大值，事务id是递增分配的。比如，现在有id为1，2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时，trx_ids就包括1和2，up_limit_id的值就是1，low_limit_id的值就是4
• up_limit_id，未提交的事务中最小的事务ID
• creator_trx_id，创建这个 Read View 的事务ID
注意：只有在对表中的记录做改动是（执行insert,update,delete时）才会为事务分配事务id，否则在一个只读事务中的事务id值都默认为0

每开启一个事务，我们都会从数据库中获得一个事务ID，这个事务ID 是自增长的，通过 ID 大小，我们就可以判断事务的时间顺序。
那么，一个事务应该看到哪些快照，不应该看到哪些快照该如何判断呢？
其实原则比较简单，那就是事务ID大的事务应该能看到事务ID小的事务的变更结果，反之则不能

3.2.3 MVCC规则

事务ID大的事务应该能看到事务ID小的事务的变更结果，反之则不能
MVCC规则：

• 如果被访问版本的db_trx_id与readview中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
• 如果被访问版本的db_trx_id属性值小于reacview中的up_limit_id的值，表明生成该版本的事务在当前事务生成readview前已经提交，所以该版本可以被当前事务访问。
• 如果当前访问版本的db_trx_id属性值大于或等于readview中的low_limit_id值，表明生成该版本的事务在当前事务生成readview后才开启，所以该版本不可以被当成事务访问。
  也就是说，在当前事务开启之后，有别的事务修改了数据并作了提交。所以，这个记录对于当前事务来说应该就是不可见的
• 如果被访问版本的db_trx_id属性值在readview的up_limit_id和low_limit_id之间，那就需要判断db_trx_id属性值是不是在trx_ids列表中
  • 如果在，说明创建readview时生成该版本的事务还是活跃的。该版本不可被访问
    即：在当前事务开启时，并未提交的某个事务在修改数据之后提交了，那么这个记录对于当前事务来说应该是不可见的
  • 如果不存在，说明创建的readview时生成的该版本的事务已经被提交，该版本可以被访问。
    即：在当前事务开启之前，其他事务对数据进行修改并提交了，所以，这条记录对当前事务就应该是可见的

所以，当读取一条记录的时候，经过以上判断，发现记录对当前事务可见，那么就直接返回就行了。那么如果不可见怎么办？没错，那就需要用到undo log了。
当数据的事务ID不符合Read View规则时候，那就需要从undo log里面获取数据的历史快照，然后数据快照的事务ID再来和Read View进行可见性比较，如果找到一条快照，则返回，找不到则返回空

所以，总结一下，在InnoDB中，MVCC就是通过Read View + Undo Log来实现的，undo log中保存了历史快照，而Read View用来判断具体哪一个快照是可见的

3.3 MVCC整体操作流程

当查询一条记录时，系统是如何通过MVCC找到它的：

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

如果某个版本的数据对当前事务不可见的话，就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，以此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结构就不包含该记录。innoDB中，MVCC是通过undolog+readview进行数据读取，undolog保存了历史快照，而readview规则帮我们判断当前版本的数据是否可见。

3.4 MVCC总结

MVCC和隔离级别：
核心点在于readview的原理，read committd、repeatable read这两种隔离级别的很大不同就是生成readview的时机不同

• 在RC(read committd)下，一个事务中的每一次SELECT都会重新获取一次Read View
• 在RR(repeatable read)下，一个事务中只在第一次SELECT的时候会获取一次Read View，之后的查询操作都重复使用
• 所以，可重复读这种事务隔离级别之下，因为有MVCC机制，就可以解决不可重复读的问题，因为只有在第一次SELECT的时候才会获取一次Read View，天然不存在重复读的问题了
• 注意：执行delete语句或者更新主键的update语句并不会立刻把对应的记录完全从页面删除，而是执行一个所谓的delete mark操作，相当于只是对记录打上了一个删除标识，这主要就是为MVCC服务的

MVCC可解决的问题：

• 读写之间阻塞的问题。通过MVCC可以让读写互相不阻塞，即读不堵塞写，写不阻塞读，这样就可以提升事务并发处理能力。
• 降低了死锁的概率。这是因为MVCC采用了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁必要的行。
• 解决快照读的问题。当查询数据库在某个时间点的快照时，只能看到这个时间点之前事务提交更新的结果，而不能看到这个时间点之后事务提交的更新结果

转载于：
https://mp.weixin.qq.com/s/wDDxros4K8CVJw74SrRFjw
https://www.jianshu.com/p/331f1d95505a