乐趣区

关于mysql:MySQL事务和持久化原理

jiezi

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<= 未提交事务的最大 id；事务 id 是否在未提交事务 id 数组中，若在则不可读（但能够读本人本事务的）。
  • 如果，以后记录的事务 id> 事务的最大 id；事务还未开始，不可读。

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

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

规定形容 2

1、如果落在绿色局部（trx_id<min_id），示意这个版本是曾经提交的事务生成的，这个数据是可见的；

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

3、如果落在黄色局部（min_id<=trx_id<=max_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<= 以后记录的事务 id<= 未提交事务的最大 id，1 在其中，所以不可读。持续向下。

​ trx_id=3，属于未提交事务的最小 id<= 以后记录的事务 id<= 未提交事务的最大 id，3 在其中，所以不可读。持续向下。

​ trx_id=2，属于未提交事务的最小 id<= 以后记录的事务 id<= 未提交事务的最大 id，2 不在其中，所以可读。实现，返回 name= B 这条记录。

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

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

RD：

trx_id=1，属于未提交事务的最小 id<= 以后记录的事务 id<= 未提交事务的最大 id，1 在其中，所以不可读。持续向下。

trx_id=3，属于未提交事务的最小 id<= 以后记录的事务 id<= 未提交事务的最大 id，3 不在其中，所以可读。返回后果 name= C 这条记录。（这就是为什么叫做读已提交）

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 操作进行一次缓存的刷新。
退出移动版