多版本并发控制（Multiversion concurrency control），通过数据行的多个版本管理来实现数据库的并发控制。这项技术使得在innodb的事务隔离级别下执行一致性读操作有了保证。换言之，就是为了查询一些正在被另外一个事务更新的行，并且可以看到他们被更新之前的值，这样在做查询的时候就不用等待另一个事务释放锁。 MVCC没有正式的标准，不同的DBMS中的实现方式也可能不一样。

如果面试的时候问到你是如何理解mvcc的，比较高质量的一句话就是：mvcc的实现原理依赖于隐藏字段、undo log、read view。

多版本并发控制，多版本就是通过undo log来体现，并发控制就是通过read view来体现。

关于四种隔离级别，之前说到串行化（最高级别）解决了幻读的问题，但是其实mysql在不可重复读这个级别就解决这个问题，原因就是通过mvcc来解决的。所以mysql在不需要串行化来解决的情况下，mvcc提升了并发性能，因为不用等待。

隐藏字段

这里会用到行格式中的下面两个隐藏字段

• trx_id: 每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的id复制给trx_id隐藏列。所以表示的就是最近更改该记录的事务id
• roll_pointer: 回滚指针，每次修改的时候，都会把旧版本写入到undo log中，通过这个字段来连接各个版本，这样在回滚的时候，就能找到该条记录修改之前的状态了。

undo log版本链

比如当两个事务（10，20）去操作（update）同一条数据会形成如下的版本链，注意10和20的版本记录不会交叉出现，因为update的时候会上锁，因此会排队执行。

read view

mvcc中多个事务对同一行记录进行更新会产生多个历史快照，这些历史快照保存在undo log里。如果一个事务想要查询这个记录，需要读取哪个版本的记录呢？这就需要用到readview了，他帮我们解决行的可见性问题。

readview就是某个事务在使用mvcc机制进行快照读操作时产生的读视图。

当事务启动时，会生成数据库系统当前的一个快照，innodb为每一个事务构造了一个数组，用来记录并维护系统当前活跃事务（启动但是未提交的事务）的id

对于read uncommitted，你都读到别人未提交的数据了，那么就是最新的数据，所以跟read view无关。

对于serializable，因为大家都是排队执行，所以你读的时候没有人在修改，所以也是最新的数据，所以也跟read view无关。

因此，readview 针对的是 read committed和repeatable read这两个隔离级别。

read view包含4个比较重要的内容：

• creator_trx_id：创建这个readview的事务id。只有在对表中记录做改动时才会为事务分配事务id，否则在一个只读事务中的事务id值都默认为0
• trx_ids：当前系统活跃事务的列表
• up_limit_id：活跃事务中最小的事务id
• low_limit_id：生成readview时系统应该分配给下一个事务的id。注意这里要区别于trx_ids，low_limit_id不是trx_ids中的最大值，而是系统中最大的事务id。

举例来说，假设现在student表中只有一条事务id为8的事务插入的一条记录，name=张三。

• read committed隔离级别下 每次读数据前都生成一个readview。 现在有两个事务id分别为10和20的事务都要去修改这条数据

  -- 事务10
  begin;
  update student set name = '李四' where id = 1;
  update student set name = '王五' where id = 1;
  
  -- 事务20
  begin;
  -- 操作一些其他的记录

  此时，id=1的这条记录得到的版本链就是下面这样：

  

  这时当有另一个事务开始执行，去读这条记录（此时事务10，20都没有提交）

  begin;
  select * from student where id = 1;

  因为，现在是在读已提交的级别下，所以这里显然读到的是张三，下面解释他是如何读到的。

  1. 每次执行select的时候就会产生一个read view，那么此时的就是

  trx_ids: [10, 20]
  up_limit_id: 10
  low_limit_id: 21
  creator_trx_id: 0

  2. 从版本链中，可以看到最新的是王五，但是trx_id还在trx_ids中，表示该事务还活跃着，那么就不能读他，然后顺着版本链往下找，下一条也同理不能读，最后一条记录trx_id为8，不在活跃列表中，且又比low_limit_id小，所以读到张三。

  然后事务10提交了，事务20开始操作，版本链变成这样：

  

  此时再去读一次

  begin;
  select * from student where id = 1;
  -- 再读一次
  select * from student where id = 1;

  此时会再生成一个readview，如下：

  trx_ids: [20]
  up_limit_id: 20
  low_limit_id: 21
  creator_trx_id: 0

  然后根据上面同样的逻辑，顺着版本链来找，找到王五这条发现事务已提交又比low_limit_id小，那么就决定读这条了。

• repeatable read级别下

  只有第一次select会产生read view，可以对照着上面的流程再走一遍，版本链同样的变化，但是read view并不会继续更新了，事务10或者20都已经提交，但是这条select依然会认为他们作出的修改不能读，因为他们依然还在最初的trx_ids列表里。这样就实现了可重复读。

binlog

二进制日志文件，也称作变更日志（update log），记录了数据库执行的所有DDL和DML等数据库更新事件语句。 主要应用场景：数据恢复，数据复制

mysql> show variables like '%log_bin%'; +---------------------------------+-----------------------------+ | Variable_name | Value | +---------------------------------+-----------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/binlog | | log_bin_index | /var/lib/mysql/binlog.index | | log_bin_trust_function_creators | OFF | | sql_log_bin | ON | +---------------------------------+-----------------------------+ 5 rows in set (0.01 sec)

log_bin_trust_function_creators 表示要不要信任函数，比如说语句中用到了now()函数，从机去复制的时候，再执行now()肯定会有偏差，所以这里默认是off

/var/lib/mysql/ 下的binlog会有的很大有的很小，因为每次mysql服务器重启的时候都会创建一个，这个时候就很小。

配置文件中：

• log-bin 可以设置文件名，默认是binlog.000001,binlog.000002，比如设置了log-bin=mybin之后就会变成mybin.000001,mybin.000002
• binlog_expire_logs_seconds 控制binlog保存的时长，单位是秒，超过时间就会被删除掉
• max_binlog_size 控制单个binlog的大小，最大值和默认值是1GB。该参数并不能严格控制binlog的大小，比如遇到了一个较大的事务，为了保证事务的完整性，不会做切换日志的动作。一般情况下可以采取默认值。

show variables like 'binlog_format'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.01 sec)

可以通过mysqlbinlog命令来查看binlog文件，但是我目前用的docker版本里面没有这个命令，所以没有去测试了

写入机制

事务执行过程中，先把日志写到binlog cache，事务提交的时候再把binlog cache写到binlog文件中，因为一个事务的binlog不能分开，所以无论事务多大，也要确保一次性写入。

事务开始 ➡️ 写入binlog cache ➡️ 事务提交 ➡️ binlog cache写入到page cache（write操作） ➡️ 写入磁盘binlog文件（sync操作） write和sync的时机，可以由参数sync_binlog控制，默认是0，表示每次提交事务都只write，由系统决定什么时候sync。虽然性能得到了提升，但是机器宕机的时候，page cache里面的binlog会丢失。 为了安全起见，可以设置为1，表示每次提交事务都会sync，就如同redo log刷盘一样。 还有一种方式，可以设置为N（N>1），表示每次提交事务都write，但累积Nge事务后才sync。

在出现io瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。同样，如果宕机，会丢失最近N个事务的binlog日志。N越大风险越高。

与redo log对比

redo log是物理日志，记录内容是“在某个数据页做了什么修改“，属于innodb存储引擎成产生的。 binlog是逻辑日志，记录内容是语句的原始逻辑，比如比某个字段更新什么值，属于mysql server层。

功能侧重点不同 redo log让innodb存储引擎拥有崩溃恢复能力 binlog保证mysql集群架构的数据一致性

两阶段提交

redo log和binlog不一致的情况下会出现问题，比如写入了redo log，但是binlog没有来得及写，mysql崩溃了，这个时候，主机的数据已经更改，而从机因为是用binlog来更新，所以从机数据没有变化，这样导致数据不一致了。

mysql的方案是，加了一个叫做写入redo log准备阶段 redo log prepare ➡️ 写入binlog ➡️ redo log commit 用这种方式再来看这个问题，mysql重启来恢复数据的时候，发现binlog没有写，但是redo log prepare里面有，那么会执行回滚，数据不会更新，如果binlog有，那么就正常提交事务更新数据就好了。

中继日志（relay log）

从服务器读取到主服务器的binlog，保存到自己的磁盘上的文件上，这个文件就是relay log，然后再去读relay log来同步数据 所以relay log只存在于从服务器上。文件名格式是：从服务器名-relay-bin.序号。从服务器名-relay.index用来定位当前正在使用的中继日志。 日志格式与binlog一致，可以通过mysqlbinlog命令查看

一个细节 如果从服务器崩溃了，而是比较严重的导致系统重装了，那么在通过relay log恢复数据的时候可能会有问题，一个容易忽略的原因是此时从服务器名和之前的名不一样了，此时将名称改回来即可。

主从复制

原理

三个线程

• 主机上log dump线程，用来将binlog发给从机
• 从机io线程，用来读主机发来的binlog，并写入到relay log中
• sql线程，读取relay log更新数据

binlog格式设置

配置名：binlog_format

• statement 基于sql语句的复制（statement-based replication，简称SBR）

优点：

• 记录的数据量小，文件较小
• 包含了所有数据库更改的信息，可以据次来审核数据库的安全等问题
• binlog可以用于实时的还原，而不仅仅用于复制

缺点：

• 不是所有update语句都能被复制，尤其是包含不确定操作的时候。比如一些函数，如uuid()

• 会产生更多的行级锁

• 对于auto_increment字段的innodb表而言，insert语句会阻塞其他的insert语句

• 数据表必须几乎和主机一致才行，否则可能会导致复制出错

• row 基于行的格式（row-based replication，RBR） 如果执行一条update语句，他可能会影响多个行比如100行，如果使用statement的话，记录的就只是这一条sql语句，但是row就会记录每个行修改的行为。这种时候，row记录的数据量就显然会更大。 而比如update语句中设置某个字段值为@@hostname，那么statement因为只是记录语句，所以每个从机拿过去更新数据的时候，这个值就会变成自己当前服务器的hostname值，而row的话，会直接记录更新的值，所以从机拿过去会设置该字段和主机一样的值。

优点：

• 任何情况下都可以复制，这对复制来说是最安全可靠的
• 复制比如insert ...select、包含auto_increment字段的insert等情况下，行级锁更少
• 执行insert、update、delete时锁更少
• 从服务器上采用多线程执行复制称为可能。（因为已经知道要做哪些事情了）

缺点：

• binlog大很多

• 无法从binlog中看到执行了什么语句

• 主机执行update操作时，所有变化的记录都会写到binlog中，而SBR只会写一次，RBR会导致频繁发生binlog并发写的问题

• mixed 5.1.8开始提供了mixed格式，实际就是row和statement的结合 mysql会去判断每一条语句该用什么格式的日志

主从延迟问题

从机中执行show slave status，结果中有一个seconds_behind_master，用于表示当前主从延迟的值，单位是秒。

可能的原因

整个过程可以设置三个时间点： T1: 主机写入binlog的时间 T2: 从机接收完binlog的时间 T3: 从机执行完复制操作的时间 通常认为T2-T1这个时间是比较短的，也就是网络传输的时间是很短的，时间主要花费在T3-T2这段 那么从机的压力较大可能是一个原因，更常见的是大事务的执行，比如一个事务在主机上花费10秒钟执行完然后写入binlog，然后从机再去执行这个事务的时候又要花这么久，然后加上网络传输时间。

• 一次性删除大量数据 不敢在主机上删，怕造成负担，去从机上删，导致从机压力大，发生延迟
• 主机执行了一条insert ... select非常大的插入操作，该操作产生了一个巨大的binlog文件
• 大表DDL 比如主机对一张500w的表增加一个字段耗费了大量时间，那么从机也同样需要花费这么多时间

如何减少延迟

• 优化sql，避免慢sql，减少批量操作。
• 提高从库机器配置
• 实时性要求高的业务强制走主库读取

几种复制方案

• 异步复制 也就是默认场景，主机写完直接返回客户端，然后从机开始复制操作
• 半同步复制 主机在写完后，先不急着返回客户端，而是等待从机的答复后再返回。 有一个参数叫rpl_semi_sync_master_wait_for_slave_count，可以设置有几个从机完成了复制就可以返回客户端了。 比如设置为1的时候，那么只要有一个从机完成了复制主机就会返回客户端。
• 组复制 Mysql Group Replication，简称MGR，是在5.7中推出的一个新的数据复制技术，基于paxos协议的状态机复制。