一. MVCC多版本并发控制机制
二. Innodb引擎SQL执行的BufferPool缓存机制
一. MVCC多版本并发控制机制
MySQL在读已提交和可重复读事务隔离级别上可以保证事务较高的隔离性,同样的SQL查询在同一个事务中多次查询的结果是一致的,就算其他事务对数据有修改也不会影响当前事务SQL语句查询的结果。这种隔离性是使用MVCC(Multi-Version Concurrency Control)机制来实现的。
在读已提交和可重复读事务隔离级别上, 对一行的数据的读和写操作默认不会通过加互斥锁来保证隔离性,避免了频繁的加锁互斥。而在串行化隔离级别为了保证较高的隔离性是通过将所有操作加锁互斥来实现的。
MVCC是通过undo日志版本链与read view机制来实现。
undo日志版本链
undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，MySQL会保留修改前的数据undo回滚日志,并且用两个隐藏字段 trx_id(事务id) 和 roll_pointer(回滚标记点) 把这些undo日志串联起来形成一个历史记录版本链。
问: trx_id(事务id)何时生成?
begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作InnoDB表的语句，事务才真正启动，才会向MySQL申请事务id，MySQL内部是严格按照事务的启动顺序来分配事务id的。
一致性视图(read-view)
该视图由执行查询时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。
事务隔离级别读已提交和可重复读的一致性视图是不一样的, 读已提交在每次执行查询SQL时都会生成新的一致性视图,所以会读到其他事务修改提交的数据, 这就是为什么读已提交无法解决可重复读和幻读的现象。 而可重复读生成的一致性视图在事务结束之前都不会变化, 这就势必造成不管其他事务对数据的修改,当前事务读到的数据总是一致的,所以可重复读隔离级别可以解决脏读,可重复读的现象。
版本链比对规则:
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)，那就包括两种情况
① 若row的trx_id在视图数组中,表示这个版本是由还没提交的事务生成的,不可见(若 row 的 trx_id 就是当前自己的事务是可见的)
② 若row的trx_id不在视图数组中,表示这个版本是已经提交了的事务生成的,可见。
(1) 事务隔离级别 - 读已提交
第一步: 事务A,事务B,事务C 都分别对数据进行修改,分别生成对应的trx_id,但是都未提交, 事务D读数据,此时事务D的read-view 为[100,200,300], 事务A,事务B,事务C虽然都对数据进行了update操作,但是事务未提交,根据版本链比对规则,三个事务更改的数据为不可见,所以事务D读到的就是数据库原本的数据,如下图红框标记
第二步: 根据图表格上的事务执行过程可知, 事务A执行了commit命令提交事务了, 此时事务D读取数据, 一致性视图read-view变成了[200,300] 100了,根据版本链比对规则, 从最新的数据记录开始向上比对,事务A(100)提交了事务, 所以数据是可见的, 所以事务D的查询结果如下图红框标记。
第三步: 事务C执行删除delete操作, 然后事务B执行commit命令提交了事务, 此时事务D读取数据,一致性视图read-view变成了[300] 100,200了, 根据版本链比对规则, 从最新的数据记录开始向上比对,事务B(200)提交了事务, 所以数据是可见的, 所以事务D的查询结果如下图红框标记。
第四步: 事务C执行commit命令提交了事务,此时事务D读取数据,一致性视图read-view变成了[] 100,200,300了, 根据版本链比对规则, 从最新的数据记录开始向上比对,事务C(300)提交了事务, 但是delete_flag删除标识为true,所以数据是不可见的, 所以事务D的查询结果为空
(2) 事务隔离级别 - 可重复读
第一步: 事务A,事务B,事务C 都分别对数据进行修改,分别生成对应的trx_id,但是都未提交, 事务D读数据,此时事务D的read-view 为[100,200,300], 事务A,事务B,事务C虽然都对数据进行了update操作,但是事务未提交,根据版本链比对规则,三个事务更改的数据为不可见,所以事务D读到的就是数据库原本的数据,如下图红框标记
第二步: 根据图表格上的事务执行过程可知, 事务A执行了commit命令提交事务了, 此时事务D读取数据, 一致性视图read-view依旧是[100,200,300],根据版本链比对规则, 从最新的数据记录开始向上比对,虽然事务A提交事务了,但是一致性视图没有变化,所以事务D的查询结果如下图红框标记。
第三步: 事务C执行删除delete操作, 然后事务B执行commit命令提交了事务, 此时事务D读取数据,一致性视图read-view仍然是[100,200,300], 根据版本链比对规则, 从最新的数据记录开始向上比对,虽然事务B(200)提交了事务, 但是一致性视图没有变化,所以事务D的查询结果如下图红框标记。
第四步: 事务C执行commit命令提交了事务,此时事务D读取数据,一致性视图read-view还是[100,200,300], 根据版本链比对规则, 从最新的数据记录开始向上比对,虽然事务C(300)提交了事务, 但是一致性视图没有变化, 所以事务D的查询结果如下图红框标记。
读已提交和可重复读示例总结:
① 读已提交隔离级别下, 一致性视图总是在变化,所以每次读到的数据会因其他事务的影响而发生变化,这就是为什么读已提交无法处理可重复读和幻读的问题了
② 可重复读隔离级别下,一致性视图不会发生变化,第一次读到什么结果,后面读到的结果总是和第一次读到的完全一致,哪怕中间数据被其他事务修改,也不受影响,保证了数据的完全隔离性,这就是可重复读隔离级别下,利用MVCC机制实现数据的隔离性。
③ 对于删除的情况下,读已提交和可重复读处理逻辑是一致的, 可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。
二. Innodb引擎SQL执行的BufferPool缓存机制
1. 执行步骤
客户端执行SQL : update t_account set balance = 100 where id = 2
① 先将磁盘ibd文件中的id为2的数据,整页加载到Buffer Pool缓存中
② 在更新数据前,将Buffer Pool缓存中的id为2的旧数据balance=999 写入undo日志中, 便于回滚
③ 执行器执行最优SQL,更新Buffer Pool缓存中的数据,将balance = 999 改成balance = 100
④ 执行器将更新SQL操作写入redo日志中
⑤ 准备提交事务,并将redo日志写入磁盘
⑥ 准备提交事务,将更新SQL的操作写入binglog日志,并将binglog日志写入磁盘
⑦ redo磁盘日志中写入commit标记,表示提交事务完成,该标记为了保证事务提交完成后,redo与binlog日志保持一致
⑧ 将Buffer Pool缓存中的balance=100 写入磁盘中,将磁盘中的balance=999更新成100
2. 各组件详解
① Buffer Poll缓存池 : 数据库的CURD都是直接操作的Buffer Pool,一般设置为机器内存的60%
② undo日志的作用 : 如果事务提交失败要回滚数据,可以用undo日志中的数据来恢复Buffer Pool中的数据
③ redo日志的作用 : 如果事务提交成功,Buffer Pool中的数据还没有来得急写入磁盘,此时数据库宕机了,可以用redo日志中的数来
恢复Buffer Pool中的数据
④ binglog日志的作用 : 主要是用来恢复数据库磁盘中的数据, 如果不小心误删数据,或者需要将磁盘的数据恢复到修改操作之前的,
就可以通过binglog操作日志记录来恢复
3. 疑问解答
① 事务提交, binglog日志先记录事务提交的命令, 此时为什么需要同步到redo日志中?
确保操作日志记录的一致性, 如果事务提交失败, 是需要redo日志的记录来恢复Buffer Pool缓存中的数据
② 为什么MySQL不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行SQL了？
首先Buffer Pool是通过随机IO将数据写入磁盘中的, 如果来一个操作请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据,这个性能可能相当差。因为磁盘随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住很高并发的。
Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性。更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。正是通过这套机制，才能让MySQL数据库在较高配置的机器上每秒可以抗下几干的读写请求。