深入理解MVCC与Buffer Pool缓存机制

MVCC多版本并发控制机制
Mysql在可重复读隔离级别下如何保证事务较高的隔离性，同样的sql查询语句在一个事务里多次执行查询结果相同，就算其它事务对数据有修改也不会影响当前事务sql语句的查询结果。
这个隔离性就是靠MVCC(Multi-Version Concurrency Control)机制来保证的，对一行数据的读和写两个操作默认是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥，而在串行化隔离级别为了保证较高的隔离性是通过将所有操作加锁互斥来实现的。
Mysql在读已提交和可重复读隔离级别下都实现了MVCC机制。

undo日志版本链与read view机制详解

在可重复读以及读已提交隔离级别都实现了MVCC机制，当开启事务后，执行的第一条sql语句会记录read view数据，为当前所有的未提交事务ID以及最大的事务ID，如[10,20,30]30，而undo日志中记录了所有修改的sql信息，可重复读级别下，生成了readview后，在下次进行别的操作不会进行更改，读取信息的时候会读取undo日志，判断事务ID是否小于最小的未提交事务ID，如果小于代表为已提交，则可以直接读取，如果大于最大事务ID，则代表事务还未开启，则不读取，如果在都不是的话代表事务为未提交或者已提交，判断事务ID是否在readView中，如果在的话代表未提交则不读取，如果不在代表已提交，则读取数据返回。而读已提交级别下，每次执行sql都会生成全新的readView
undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链

在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束之前都不会变化(如果是读已提交隔离级别在每次执行查询sql时都会重新生成)，这个视图由执行查询时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。
版本链比对规则：

1. 如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的；
2. 如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若row 的 trx_id 就是当前自己的事务是可见的）；
3. 如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况
   a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自
   己的事务是可见的)；
   b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。

对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。
注意：begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作InnoDB表的语句，事务才真正启动，才会向mysql申请事务id，mysql内部是严格按照事务的启动顺序来分配事务id的。
总结：
MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。

Innodb引擎SQL执行的BufferPool缓存机制

1.如下图执行sql会先把满足条件的数据从磁盘文件中读取到bufferPool中
2.将原本的数据写入undo日志文件中(便于回滚)
3.将bufferpool中的数据修改为新值
4.将数据写入到Redo log buffer中
5.准备提交事务，将redo log buffer中的数据写入磁盘的redo文件中(如果事务提交了，但是bufferpool中的数据还没来得及写入磁盘，服务宕机了，那么下次服务启动的时候，mysql会将redo的数据重新写入到bufferPool中)
6.准备提交事务，将binlog日志写入磁盘，便于数据恢复
7.提交事务，写入commit标记到redo文件中，主要用于保持binlog与redo文件的一致
8.IO随机将bufferpool中的数据，按页写入磁盘

为什么Mysql不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行SQL了？
因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能可能相当差。因为磁盘随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住很高并发的。
Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性。
更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。
正是通过这套机制，才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干的读写请求。

redo log重做日志关键参数
innodb_log_buffer_size：设置redo log buffer大小参数，默认16M ，最大值是4096M，最小值为1M。
show variables like ‘%innodb_log_buffer_size%’;
innodb_log_group_home_dir：设置redo log文件存储位置参数，默认值为"./"，即innodb数据文件存储位置，其中的 ib_logfile0 和 ib_logfile1 即为redo log文件。
show variables like ‘%innodb_log_group_home_dir%’;
innodb_log_files_in_group：设置redo log文件的个数，命名方式如: ib_logfile0, iblogfile1… iblogfileN。默认2个，最大100个。
show variables like ‘%innodb_log_files_in_group%’;
innodb_log_file_size：设置单个redo log文件大小，默认值为48M。最大值为512G，注意最大值指的是整个 redo log系列文件之和，即(innodb_log_files_in_group * innodb_log_file_size)不能大于最大值512G。
show variables like ‘%innodb_log_file_size%’;

redo log 写入磁盘过程分析：
redo log 从头开始写，写完一个文件继续写另一个文件，写到最后一个文件末尾就又回到第一个文件开头循环写，如下面这个图所示。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。
checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件里。
write pos 和 checkpoint 之间的部分就是空着的可写部分，可以用来记录新的操作。如果 write pos 追上checkpoint，表示redo log写满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

innodb_flush_log_at_trx_commit：这个参数控制 redo log 的写入策略，它有三种可能取值：
设置为0：表示每次事务提交时都只是把 redo log 留在 redo log buffer 中，数据库宕机可能会丢失数据。
设置为1(默认值)：表示每次事务提交时都将 redo log 直接持久化到磁盘，数据最安全，不会因为数据库宕机丢失数据，但是效率稍微差一点，线上系统推荐这个设置。
设置为2：表示每次事务提交时都只是把 redo log 写到操作系统的缓存page cache里，这种情况如果数据库宕机是不会丢失数据的，但是操作系统如果宕机了，page cache里的数据还没来得及写入磁盘文件的话就会丢失数据。
InnoDB 有一个后台线程，每隔 1 秒，就会把redo log buffer中的日志，调用操作系统函数 write 写到文件系统的page cache，然后调用操作系统函数fsync持久化到磁盘文件。
redo log写入策略参看下图：

查看innodb_flush_log_at_trx_commit参数值：

show variables like ‘innodb_flush_log_at_trx_commit’;

设置innodb_flush_log_at_trx_commit参数值(也可以在my.ini或my.cnf文件里配置)：

set global innodb_flush_log_at_trx_commit=1;