mysql mvcc实现可重复读和读已提交

MYSQL 日志

• binlog
  • binlog用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog是mysql的逻辑日志，并且由Server层进行记录，使用任何存储引擎的mysql数据库都会记录binlog日志
  • binlog是通过追加的方式进行写入的，可以通过max_binlog_size参数设置每个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之后版本的默认值。但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能。
  • binlog日志有三种格式，分别为STATMENT、ROW和MIXED。
    • STATMENT。基于SQL语句的复制(statement-based replication, SBR)，每一条会修改数据的sql语句会记录到binlog中。
      • 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO, 从而提高了性能；
      • 缺点：在某些情况下会导致主从数据不一致，比如执行sysdate()、slepp()等。
    • ROW。基于行的复制(row-based replication, RBR)，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了。
      • 优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题；
      • 缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨
    • MIXED。基于STATMENT和ROW两种模式的混合复制(mixed-based replication, MBR)，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog
• redolog
  •  mysql是如何保证一致性的呢？最简单的做法是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，主要体现在两个方面
    • 因为Innodb是以页为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！
    • 一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！
  • 因此mysql设计了redo log，具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)
  • 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参数配置，各参数值含义如下：
    • 0（无延迟）事务提交不会将redo log buffer写入os buffer，而是每秒写入os buffer并调用fsync写入 redo log file中，也就是设置为零时每秒刷新写入磁盘，当系统崩溃，会丢失一秒钟的数据
    • 1（实时写，实时刷）事务每次提交都将redo log buffer中的日志写入os buffer并调用fsync刷到redo log file中，这种方式及时系统崩溃，因为每次提交都写入磁盘，IO性能较差。
    • 2（实时写，延迟刷）每次提交都写入os buffer，然后每秒调用fsync()将os buffer中的日志写入redo log file中。
• undolog

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

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

  • 它的主要作用是将事务恢复到执行修改之前的样子，但是，恢复的情况一般分为两种，一种是逻辑恢复，一种是物理恢复，这里需要非常强调的是，undo的恢复是逻辑恢复，也就是说，如果你插入了100w条数据，导致innodb分配了一个新的数据页来存储这些数据，那么在事务进行回滚的时候，undo的功能并不是回收这个数据页，而是将这些insert的操作，改变成delete的操作从而执行回滚。在这个过程中，共享表空间的大小并不会发生改变。除此之外，undo日志会将delete操作转化为insert操作，update操作转化为反向的update操作

mysql mvcc

• MVCC（Multi-Version Concurrency Control）多版本并发控制，是用来在数据库中控制并发的方法，实现对数据库的并发访问用的。在MySQL中，MVCC只在读取已提交（Read Committed）和可重复读（Repeatable Read）两个事务级别下有效。其是通过Undo日志中的版本链和ReadView一致性视图来实现的。MVCC就是在多个事务同时存在时，SELECT语句找寻到具体是版本链上的哪个版本，然后在找到的版本上返回其中所记录的数据的过程。
• 在MySQL中，会默认为我们的表后面添加三个隐藏字段
  • DB_ROW_ID：行ID，MySQL的B+树索引特性要求每个表必须要有一个主键。如果没有设置的话，会自动寻找第一个不包含NULL的唯一索引列作为主键。如果还是找不到，就会在这个DB_ROW_ID上自动生成一个唯一值，以此来当作主键（该列和MVCC的关系不大）
  • DB_TRX_ID：事务ID，记录的是当前事务在做INSERT或UPDATE语句操作时的事务ID（DELETE语句被当做是UPDATE语句的特殊情况）
  • DB_ROLL_PTR：回滚指针，通过它可以将不同的版本串联起来，形成版本链。相当于链表的next指针。
• Read View(读视图)
  • Read View就是事务进行快照读(select * from)操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成事务系统当前的一个快照，记录并维护系统当前活跃事务(未提交事务)的ID(当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大)。
  •  Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据
  • ReadView中主要包含4个比较重要的内容：
    • m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
    • 表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。
    • max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。
    • creator_trx_id：表示生成该ReadView的快照读操作产生的事务id。
  • 注意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。
  • 有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见：
    • 如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
    • 如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
    • 如果被访问版本的trx_id属性值大于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
    • 如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问
  • 当一个事务第一次执行查询sql时，会生成一致性视图 read-view（快照），它由执行查询时所有未提交事务 id 数组（数组中最小的 id 为 min_id）和已创建的最大事务 id（max_id）组成，查询时从 undo log 中最新的一条记录开始跟 read-view 做对比，如果不符合比较规则，就根据回滚指针回滚到上一条记录继续比较，直到得到符合比较条件的查询结果