MySQL事务和持久化原理

数据库小白一枚，正在不断学习积累知识，现将学习到的知识记录一下，也是将我的所得分享给大家！而今天这篇文章《MySQL事务和持久化原理》带大家来了解一下MySQL事务和持久化原理，希望对大家的知识积累有所帮助，从而弥补自己的不足，助力实战开发！

MySQL事务和持久化原理

基础概念篇

1、事务特性ACID

• 原子性A：要么成功，要么失败，不可分割。
• 一致性C：事务执行前后，数据库处于一致性状态，事务成功变化正确。事务失败返回原始阶段。
• 隔离性I：并发下，不同事务操作相同数据，并发事务所做的修改隔离，要么是另一个事务修改前没要么是另一个事务修改后；不存在中间状态。
• 持久性：事务结束后，对数据库的操作必须要永久保存下来（保存在磁盘中）。

2、事务的隔离级别

• ISOLATION_DEFUALT：后端数据库默认隔离级别。
• ISOLATION_READ_UNCOMMITED：最低级别，允许读尚未提交的数据变更，可能会出现脏读、幻读、不可重复读。
• ISOLATION_READ_COMMITED：RC，允许读取并发事务已提交的数据，可以阻止脏读，但可能会出现幻读、不可重复读。
• ISOLATION_REPEATABLE_READ：RR，同一字段，多次读取结果都是一致的，除非数据本身被修改，可阻止脏读、不可重复读，但仍有幻读。
• ISOLATION_SERIALIZABLE：最高隔离级别，完全服从ACID，没有脏读、幻读、不可重复读，但速度慢，完全锁定事务。

3、脏读、幻读、不可重复读

• 脏读：一个事务读取了被另一个事务改写但尚未提交的数据，如果数据改变后被回滚，第一个事务读取的数据就会无效。
• 幻读：当事务T1读取几行数据后，另外一个并发事务T2插入了一些记录，幻读就发生了，第一个事务T1发现了一些原来没有的额外数据记录（新增、或删除）。
• 不可重复读：不可重复读发生在一个事务执行多次查询，但每次查询的结果都不同，通常由于另外一个事务在中途做了更新。

4、MySQL事务的实现

MySQL的事务的四个特性（ACID），是通过InnoDB日志和锁来保证的。

• 事务的隔离性是通过数据库锁的机制实现。
• 事务的持久性是通过Redo Log来实现。
• 事务的原子性和一致性是通过Undo Log实现的。

实现过程：

• 在操作任务数据之前，首先将数据备份到Undo Log中，然后再进行数据的修改操作；
• 出现错误时执行Roll Back，系统可以利用Undo Log恢复到事务开始之前的状态。
• Redo Log是记录新数据的备份，事务提交之前，只将Redo Log持久化即可。
• 系统崩溃时，数据库未持久化，但Redo Log已经持久化，系统可以根据Redo Log将数据恢复并提交。

MVCC篇

1、MVCC简介

​ MVCC：Multi-Version Concurrency Control多版本并发控制，不仅用于MySQL，分布式事务也可以使用；是一种乐观锁，用于RR(可重复读)、RC(读已提交)隔离级别。使用了行级锁。

​ 当执行查询sql时会生成一致性视图read-view，它由执行查询时所有未提交事务id数组（数组里最小id为min_id）和已创建的最大事务id(max_id)组成，查询的数据结果需要跟read-view做比对从而得到快照结果。

​ MVCC通过保存数据在某个时间点的快照来实现的，基本特征如下：

• 每行数据都存在一个版本，每次数据更新时都更新该版本。
• 修改时Copy出当前版本随意修改，各个事务之间互不干扰。
• 保存时比较版本号，如果成功commit则覆盖原记录；失败则放弃copy。

2、InnoDB引擎的MVCC策略

​ 每行数据额外保存两个隐藏列（当前行创建时的版本号和删除时的版本号，另外还有一列称为回滚指针，用于事务回滚）；

​ InnoDB内部为每一行添加了两个隐藏列：DB_TRX_ID版本号和DB_ROLL_PTR回滚指针（MySQL另外还有一个隐藏列DB_ROW_ID，这是在InnoDB表没有主键的时候会用来作为主键）。

• DB_TRX_ID：长度为6字节，存储了插入或更新语句的最后一个事务的事务ID。
• DB_ROLL_PTR：长度为7字节，称之为：回滚指针。回滚指针指向写入回滚段的undo log记录，读取记录的时候会根据指针去读取undo log中的记录。
• DB_ROW_ID： 行标识（隐藏单调自增 ID ），大小为 6 字节，如果表没有主键， InnoDB 会自动生成一个隐藏主键，因此会出现这个列。另外，每条记录的头信息（ record header ）里都有一个专门的 bit （ deleted_flag ）来表示当前记录是否已经被删除。

​ 快照读：在RR隔离级别下，在不加锁的情况下MySQL会根据回滚指针选择从undo log记录中获取快照数据，而不总是获取最新的数据，这也就是为什么另一个事务提交了数据，在当前事务中看到的依然是另一个事务提交之前的数据。RR隔离级别快照并不是在BEGIN就开始产生了，而是要等到事务当中的第一次查询之后才会产生快照，之后的查询就只读取这个快照数据。

3、版本链比对规则

规则描述1

事务规则：

从最新记录开始查找：

• 如果，当前记录的事务id可读。
• 如果，未提交事务的最小id
• 如果，当前记录的事务id>事务的最大id；事务还未开始，不可读。

RR（可重复读）：返回的readview是第一条记录的，在事务中不会重复生成。

RD（读已提交）：每次查询都生成最新的readview。

规则描述2

1、如果落在绿色部分（trx_id

2、如果落在红色部分（trx_id>max_id），表示这个版本是由将来启动的事务生成的，是肯定不可见的；

3、如果落在黄色部分（min_id

• 若row的trx_id在数组中，表示这个版本是由还没有提交的事务生成的，不可见，当前自己的事务是可见的。
• 若row的trx_id不再数组中，表示这个版本是已经提交了的事务生成的，可见。

删除的实现

​ 对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的trx_id，同时在该条记录的头信息（record header）里的（delete_flag）标记位写上true，来表示当前记录已经被删除，在查询时按照上面的规则查找到对应的记录，如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。

​ 版本链生成是全局的不是单一表的，这些版本链记录在undo日志中。rc隔离级别下是多个select是中途更新read-view快照的。而RR隔离级别是不更新read-view的，因此可重复读。

4、案例分析

事务过程：

事务4的分析过程：

1、select name from table where id=1; readview:[1,3] 3

从undo日志的首行开始：

​ trx_id=1，属于未提交事务的最小id

​ trx_id=3，属于未提交事务的最小id

​ trx_id=2，属于未提交事务的最小id

2、select name from table where id=1; readview:[1] 3

RR：沿用上个readview:[1,3] 3；查询结果仍旧不变（这就是为什么叫做可重复读）。

RD：

trx_id=1，属于未提交事务的最小id

trx_id=3，属于未提交事务的最小id

WAL和持久化篇

1、页和脏页

页：InnoDB是B+树结构，树的每个节点是一个页，在MySQL中，页的大小是16kb，ORA中是8kb。

脏页：内存数据跟磁盘数据页不一致的时候称为这个内存页为“脏页”，一致的称为“干净页”。

正常SQL操作都是写内存和日志，并不会立即同步到磁盘数据，这时候内存和磁盘数据页内容会产生不一致，即脏页；当SQL执行较慢，可能是将脏页同步到磁盘中。

2、脏页同步

脏页同步实际时机

1、redo log写满时，系统就会停止所有的更新操作，将更新的这部分日志对应的脏页同步到磁盘中，此时所有的更新全部停止，此时写的性能为0，必须等待刷盘一部分脏页后才能更新，这就导致了SQL执行慢，应避免此类情况；

2、系统内存不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，则需要先将脏页同步到磁盘，空出来的给其他数据页使用。当淘汰的脏页过多时。会导致查询的响应时间边长。

3、MySQL认为系统空闲时，则会同步一些数据到磁盘。无性能问题。

4、MySQL正常关闭时，会把内存脏页都同步到磁盘中。无性能问题。

刷脏页策略策略

• innodb_io_capacity：redo log中的剩余空间。
• innodb_max_dirty_pages_pct脏页比例上限，默认值是75% 通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到的。

3、redo log（重做日志）

​ InnoDB存储引擎层的日志，redo log是用来实现事务的持久性，即当事务在提交时，必须先将该事务的所有操作日志写到磁盘上的 redo log file进行持久化，这也就是我们常说的 Write Ahead Log 策略（先日志后写数据）。有了redo log，在数据库发生宕机时，即使内存中的数据还没来得及持久化到磁盘上，我们也可以通过redo log完成数据的恢复，这样就避免了数据的丢失。

​ 在一条更新语句进行执行的时候，InnoDB引擎会把更新记录写到redo log日志中，然后更新内存，此时算是语句执行完了，然后在空闲的时候或者是按照设定的更新策略将redo log中的内容更新到磁盘中。可以根据redo log日志进行恢复，也就达到了crash-safe。

​ 即WAL即Write Ahead logging技术，是先写日志，再写磁盘。

​ redo log是循环写的，有write pos和checkpoint两个指针，当write pos追上checkpoint时，没有空间记录redo log，checkpoint就向前推进将脏页刷入磁盘。checkpoint之前表示擦除完了的，即可以进行写的，擦除之前会更新到磁盘中，write pos是指写的位置，当write pos和checkpoint相遇的时候表明redo log已经满了，这个时候数据库停止进行数据库更新语句的执行，转而进行redo log日志同步到磁盘中。

​ 作用：确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。

4、bin log（归档日志）

​ 数据库的（和引擎无关）bin log记录了数据库系统所有的更新操作，主要是用来实现数据恢复和主从复制的。一方面，主从配置的MySQL集群可以利用bin log将主库中的更新操作传递到从库中，以此来实现主从数据的一致性；另一方面，数据库还可以利用bin log来进行数据的恢复。没有crash-safe能力。

​ 作用：用于复制。在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。 用于数据库的基于时间点的还原。

5、redo log和binlog的区别

• redo log和bin log的产生方式不同。redo log是在物理存储引擎层产生，而bin log是在MySQL数据库的Server层产生的，并且bin log不仅针对InnoDB存储引擎，MySQL数据库中的任何存储引擎对数据库的更改都会产生bin log。
• redo log和binlog的记录形式不同。MySQL Server层产生的bin log记录的是一种逻辑日志，即通过SQL语句的方式来记录数据库的修改；而InnoDB层产生的redo log是一种物理格式日志，其记录的是对于磁盘中每一个数据页的修改。
• redo log和bin log记录的时间点不同。bin log只是在事务提交完成后进行一次写入，而redo log则是在事务进行中不断地被写入，redo log并不是随着事务提交的顺序进行写入的，这也就是说在redo log 中针对一个事务会有多个不连续的记录日志。
• redo log是循环写，日志空间大小固定；binlog是追加写，是指一份写到一定大小的时候会更换下一个文件，不会覆盖。
• binlog可以作为恢复数据使用，主从复制搭建，redo log作为异常宕机或者介质故障后的数据恢复使用。

​ 注：数据库数据存放的文件称为data file；日志文件称为log file；数据库数据是有缓存的，如果没有缓存，每次都写或者读物理disk，那性能就太低下了。数据库数据的缓存称为data buffer，日志（redo）缓存称为log buffer。

6、bin log和redo log的一致性问题

​ 在MySQL内部，在事务提交时利用两阶段提交（内部XA的两阶段提交）解决了上面提到的bin log和redo log的一致性问题。

（redo log记录事务Prepare，bin log写入并持久化、redo log增加commit 标签）

（先写redo log再写bin log）

• 第一阶段： InnoDB Prepare阶段。此时SQL已经成功执行，并生成事务ID(xid)信息及redo和undo的内存日志。此阶段InnoDB会写事务的redo log，但要注意的是，此时redo log只是记录了事务的所有操作日志，并没有记录提交（commit）日志，因此事务此时的状态为Prepare。此阶段对binlog不会有任何操作。
• 第二阶段：commit 阶段，这个阶段又分成两个步骤。第一步写bin log（先调用write()将bin log内存日志数据写入文件系统缓存，再调用fsync()将bin log文件系统缓存日志数据永久写入磁盘）；第二步完成事务的提交（commit），此时在redo log中记录此事务的提交日志（增加commit 标签）。

在第一阶段并没有记录完整的redo log（不包含事务的commit标签）。

在第二阶段记录完binlog后再写入redo log的commit 标签。

以第二阶段中bin log的写入与否作为事务是否成功提交的标志。

6、崩溃恢复过程

​ 如果数据库在记录此事务的binlog之前和过程中发生crash。数据库在恢复后认为此事务并没有成功提交，则会回滚此事务的操作。与此同时，因为在binlog中也没有此事务的记录，所以从库也不会有此事务的数据修改。

​ 如果数据库在记录此事务的binlog之后发生crash。此时，即使是redo log中还没有记录此事务的commit 标签，数据库在恢复后也会认为此事务提交成功（因为在上述两阶段过程中，binlog写入成功就认为事务成功提交了）。它会扫描最后一个binlog文件，并提取其中的事务ID（xid），InnoDB会将那些状态为Prepare的事务（redo log没有记录commit 标签）的xid和bin log中提取的xid做比较，如果在binlog中存在，则提交该事务，否则回滚该事务。这也就是说，bin log中记录的事务，在恢复时都会被认为是已提交事务，会在redo log中重新写入commit标志，并完成此事务的重做（主库中有此事务的数据修改）。与此同时，因为在binlog中已经有了此事务的记录，所有从库也会有此事务的数据修改。

7、Checkpoint机制

​ InnoDB引擎通过LSN(Log Sequence Number)来标记版本，LSN是日志空间中每条日志的结束点，用字节偏移量来表示。每个page有LSN，redo log也有LSN，Checkpoint也有LSN。

​ Checkpoint机制每次刷新多少页，从哪里取脏页，什么时间触发刷新？这些都是很复杂的。有两种Checkpoint，分别为：

• Sharp Checkpoint
• Fuzzy Checkpoint

Sharp Checkpoint发生在关闭数据库时，将所有脏页刷回磁盘。在运行时使用Fuzzy Checkpoint进行部分脏页的刷新。部分脏页刷新有以下几种：

• Master Thread Checkpoint：Master Thread以每秒或每十秒的速度从缓冲池的脏页列表中刷新一定比例的页回磁盘。这个过程是异步的，不会阻塞查询线程。
• FLUSH_LRU_LIST Checkpoint：InnoDB要保证LRU列表中有100左右空闲页可使用。在InnoDB1.1.X版本前，要检查LRU中是否有足够的页用于用户查询操作线程，如果没有，会将LRU列表尾端的页淘汰，如果被淘汰的页中有脏页，会强制执行Checkpoint刷回脏页数据到磁盘，显然这会阻塞用户查询线程。从InnoDB1.2.X版本开始，这个检查放到单独的Page Cleaner Thread中进行，并且用户可以通过innodb_lru_scan_depth控制LRU列表中可用页的数量，默认值为1024。
• Async/Sync Flush Checkpoint：是指重做日志文件不可用时，需要强制将脏页列表中的一些页刷新回磁盘。这可以保证重做日志文件可循环使用。在InnoDB1.2.X版本之前，Async Flush Checkpoint会阻塞发现问题的用户查询线程，Sync Flush Checkpoint会阻塞所有查询线程。InnoDB1.2.X之后放到单独的Page Cleaner Thread。
• Dirty Page too much Checkpoint：脏页数量太多时，InnoDB引擎会强制进行Checkpoint。目的还是为了保证缓冲池中有足够可用的空闲页。

8、例子：更新语句的执行顺序

​ 示例：update T set c=c+1 where ID=2;

​ 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

​ 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

​ 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

​ 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

​ 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

9、InnoDB刷盘策略

​ MySQL的innodb_flush_method参数控制着innodb数据文件及redo log的打开、刷写模式。有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT。

• fdatasync模式：写数据时，write这一步并不需要真正写到磁盘才算完成（可能写入到操作系统buffer中就会返回完成），真正完成是flush操作，buffer交给操作系统去flush,并且文件的元数据信息也都需要更新到磁盘。
• O_DSYNC模式：写日志操作是在write这步完成，而数据文件的写入是在flush这步通过fsync完成。
• O_DIRECT模式：数据文件的写入操作是直接从mysql innodb buffer到磁盘的，并不用通过操作系统的缓冲，而真正的完成也是在flush这步,日志还是要经过OS缓冲。
  

性能比较：

​ O_DSYNC对CPU的压力最大，datasync次之，O_DIRECT最小；整体SQL语句处理性能和响应时间看，O_DSYNC较差；O_DIRECT在SQL吞吐能力上较好（仅次于datasync模式），但响应时间却是最长的。

​ 默认datasync模式，整体表现较好，因为充分利用了操作系统buffer和innodb_buffer_pool的处理性能，但带来的负面效果是free内存降低过快，最后导致页交换频繁，磁盘IO压力大，这会严重影响大并发量数据写入的稳定性。

​ 注：redo log并没有打开O_DIRECT选项，所以redo log buffer只是先刷入redo log file，此时刷入的数据并没有落到磁盘上，而是放在文件系统的缓存中。之后为了确保redo log写入磁盘，就通过fsync操作将数据写入磁盘。

innodb_flush_log_at_trx_commit参数：

​ redo log日志持久化：每秒刷盘、事务提交强制刷盘、事务提交由系统每秒刷盘

• 0：由mysql的main_thread每秒将存储引擎log buffer中的redo日志写入到log file，并调用文件系统的sync操作，将日志刷新到磁盘。
• 1：每次事务提交时，将存储引擎log buffer中的redo日志写入到log file，并调用文件系统的sync操作，将日志刷新到磁盘。
• 2：每次事务提交时，将存储引擎log buffer中的redo日志写入到log file，并由存储引擎的main_thread 每秒将日志刷新到磁盘。

sync_binlog参数：

​ bin log日志持久化：系统控制、每次事务提交、日志组数量达到n

• 0 ：存储引擎不进行binlog的刷新到磁盘，而由操作系统的文件系统控制缓存刷新。
• 1：每提交一次事务，存储引擎调用文件系统的sync操作进行一次缓存的刷新，这种方式最安全，但性能较低。
• n：当提交的日志组=n时，存储引擎调用文件系统的sync操作进行一次缓存的刷新。

以上就是本文的全部内容了，是否有顺利帮助你解决问题？若是能给你带来学习上的帮助，请大家多多支持golang学习网！更多关于数据库的相关知识，也可关注golang学习网公众号。