Mysql-Mysql高级-MYSQL事务的高级_fio consistency-CSDN博客

本文链接：https://blog.csdn.net/jllion/article/details/118185376

MYSQL事务的高级

基本概念

事务的特点（ACID）
- Atomicity（原子性）：原子性表示mysql的事务是一个整体，一个事务要么全部完成，要么全部失败。innodb通过undo log来实现原子性。
- Consistency（一致性）：一致性表示mysql的事务是让数据库从一个一致性状态装换到另一个一致性状态。（mysql通过保证原子性，持久性，隔离性来最终保证数据库的一致性）
- Isolation(隔离性)：mysql的隔离性，表示一个事务内部的操作数据对其他并发的事务是隔离的，一个事务的执行不能被其他事务干扰，并发执行的各个事务之间不能相互干扰。（mysql通过锁机制和mvcc来实现隔离性）
- Durability（持久性）：持久性表示mysql的事务提交后，数据对于数据库是永久性的，接下来的其他事务的发生和数据库故障不应该对这个事务已经提交的数据有任何影响
事务要做的的效果
- 可靠性：数据库要保证insert，update，delete操作时抛异常或数据库crash的时候需要保证数据的操作前后一致。
- 并发处理：多个并发读写请求，相互之间的数据不影响，这需要事务的隔离性。

redo log 与 undo log

redo log
- redo log叫做重做日志，是用来实现事务的持久性的。该日志文件由两部分组成：重做日志缓存（redo log buffer）以及重做日志文件（redo log），前者存在内存中，后者存在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中。
  - ```
  start transaction;
  select balance from bank where name="zhangsan";// 生成 重做日志 balance=600
  update bank set balance = balance - 400; // 生成 重做日志 amount=400
  update finance set amount = amount + 400;
  commit;
```
- redo log的作用
  - mysql为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Buffer Pool缓存池里，把这个当做缓存来用。然后使用后台线程去做缓冲池和磁盘之间的同步
  - 问题：如果还没来得及同步的时候宕机或者断电了怎么办？还没来得及执行上面红色箭头的同步操作。这样会导致丢失部分已提交事务的修改信息。
  - mysql引入了redo log来记录已成功提交事务的修改信息，并且会把redo log持久化到磁盘，系统重启之后再读取redo log恢复最新的数据。
- 总结：redo log是用来恢复数据的，用于保障已提交事务的“持久化”特性。
undo log
- undo log叫做回滚日志，用于记录数据被修改前的信息。它正好跟redo log所记录的相反（重做日志记录的是数据被修改后的信息）。undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将操作之前的所有操作都记录下来，然后在发生错误时才可以回滚。
- 每次写入数据或修改数据之前都会改修改前的信息记录到undo log。
- undo log的作用
  - undo log记录事务修改之前版本的数据信息，因此加入由于系统错误或者rollback操作而回滚的话可以根据undo log的信息来进行回滚到没被修改前的状态。
- 总结
  - undo log作用是用来回滚数据，用于保障未提交事务的原子性。

Mysql锁技术以及MVCC基础

mysql锁技术
- 当有多个请求来读取表中的数据时可以不采用任何操作。但是多个请求中有读请求，又有修改请求时必须有一种措施来进行并发控制。不然可能会造成数据不一致。读写锁用于解决上述问题，只需要用两种锁的组合来对读写请求进行控制即可，这两种锁被称为
  - 共享锁（shared lock）：又叫做“读锁”，读锁是可以共享的，或者说多个读请求可以共享一把锁读数据，不会造成阻塞。
  - 排他锁（exclusive lock）：又叫做“写锁”，写锁会排斥其他所有获取锁的请求，未获取到锁的请求进入等待，一直阻塞，直到写入操作完成释放锁。
- 读锁写锁
  读锁可并行不可并行
  写锁不可并行不可并行
- 总结
  - 通过读写锁，可以做到读读并行，但做不到写读，写写并行，事务的隔离性就是通过读写锁来实现的
MVCC基础
- MVCC（MutiVersion Concurrency Control）多版本并发控制
  - InnoDB的MVCC，是通过在每行记录的后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存了行的过期时间，当然存储的并不是实际的时间值，而是系统版本号。
  - MVCC(MutiVersion Concurrency Control)多版本并发控制，只在（Read Commit）已提交读和（Repeatable Read）可重复读两种模式下有效。其通过Undo log中的版本链和ReadView一致性视图来实现的。
  - MVCC(MutlVersion Concurrency Control)就是在多个事务同时存在时，SELECT语句查询到的具体是版本链上的哪个版本，然后在找到版本上返回其中所记录的数据的过程
- 在Mysql中，会默认在表后面添加三个隐藏字段
  - DB_ROW_ID：行ID，MYSQL的B+树索引特性要求每个表必须有一个主键。如果没有设置的话，mysql会自动寻找一个不包含NULL的唯一列作为主键。如果找不到一个不包含NUll的唯一列，mysql会在DB_ROW_ID上自动生成一个唯一值，以此来作为主键（该列和MVCC的关系不大）；
  - DB_TRX_ID：事务ID，记录的是当前事务在做INSERT或UPDATE语句操作时的事务ID(DELETE语句被当做是UPDATE语句的特殊情况)
  - DB_ROLL_PTR：回滚指针，通过这个字段可以将不同的版本串联起来，形成版本链。相当于链表的next指针
- ReadView
  - ReadView一致性视图主要是由两部分组成：所有未提交事务的ID数组和已经创建的最大事务ID。比如：[100,200],300。事务100和200是当前未提交事务，而事务300是当前创建的最大事务（已经提交了）。当执行SELECT语句的时候会创建ReadView，但是在读取已提交和可重复读两个事务级别下，生成ReadView的策略是不一样的：读取已提交级别是每执行一次select语句就会生成一份ReadView。而可重复读是只会在事务开启时第一次select语句执行的时候会生成一份ReadView，后续的Select语句会沿用之前生成的readView（即使后面有更新语句的话，也会继续沿用）。
- 什么是当前读和快照读？
  - 当前读：像select lock in share mode（共享锁），select for update；update，insert ，delete（排他锁）这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
- MVCC的主要思想是通过数据多版本控制和读写分离，从而实现不加锁读，进而做到读写并行。
- MVCC在mysql中的实现依赖的是undo log 和read view
  - undo log：undo log中记录某行数据的多个版本的数据。
  - read view ：用来判断当前版本数据的可见性。

	读锁	写锁
读锁	可并行	不可并行
写锁	不可并行	不可并行

MySQL事务的实现

重做日志，回滚日志以及锁技术就是实现事务的基础
- 事务的原子性是通过undo log来实现的
- 事务的持久性是通过redo log来实现的
- 事务的隔离性是通过mysql锁机制（读写锁）和MVCC来实现的
- 事务的一致性，mysql是通过原子性，持久性，隔离性来实现的
- ACID只是概念，最终是要保证数据库的数据的可靠性，一致性。
原子性的实现
- 事务的原子性：一个事务必须被视为不可分割的最小单元，一个事务中的所有操作要么全部成功提交，要么全部失败回滚，对于一个事务来说不可能只执行其中的部分操作。
- mysql通过回滚操作保证了事务的原子性，所谓回滚操作就是当发生错误异常或者显式的执行rollback语句时需要把数据还原到事务开始前的样子，这时候就需要使用undo log来进行回滚。
- undo log在实现原子性时的步骤
  - 假设有两张表bank和balance，表中原始数据如图所示，当进行插入，删除以及更新操作时生成undo log
  - 从上图可以看到数据变更都伴随着回滚日志undo log 的产生
    - （1）产生了被修改前数据表bank（zhangsan，1000）的回滚日志
    - （2）产生了被修改前数据表finance（zhangsan，0）的回滚日志
  - 根据上面流程可以得出如下结论
    - 每条数据变更（insert/delete/update）操作都伴随着一条undo log的生成，并且回滚日志undo log必须先于数据持久化到磁盘上
    - 所谓的回滚就是根据回滚日志undo log逆向操作，比如delete的逆向操作为insert，insert的逆向操作是delete，update的逆向操作是update等
  - 根据undo log进行回滚
    - 为了做到同时成功或同时失败，当系统发生错误时或者执行rollback操作时需要根据undo log进行回滚
    - 回滚操作就是要将表还原到事务之前的状态，undo log记录了数据被修改前的信息以及新增和被删除的数据信息，根据undo log生成回滚语句，比如
      - 如果在回滚日志里有新增数据记录，则生成删除该条记录的sql语句
      - 如果在回滚日志里有删除数据记录，则生成增加该条记录的sql语句
      - 如果在回滚日志里有修改数据记录，则生成修改到原先数据的sql语句
持久性的实现
- 事务一旦提交，其所做的修改会永久保存到数据库，即使此时系统崩溃修改的数据也不会丢失。
- 先了解一下mysql的数据存储机制，mysql的表数据是存放在磁盘上的，因此想要存取的时候都要经历磁盘IO，然而即使是使用SSD磁盘，它的IO操作也是非常消耗性能的。为此，为了提升性能InnoDB提供了缓冲池（Buffer Pool），Buffer Pool中包含了磁盘数据页的映射，可以当做缓存来使用：读数据，会首先从缓冲池（Buffer Pool）中读取，如果缓冲池中没有，则从磁盘读取再放入缓冲池；写数据，会首先将数据写入缓冲池（Buffer Pool），后台有线程会定期将缓冲池的数据同步到磁盘中。
- 注意：缓冲池是在内存中的，虽然缓冲池的措施在性能方面带来了质的飞跃。但是也带来了问题，当mysql系统宕机，断电的时候可能会丢失数据！！！
  - 因为数据已经提交了，但是此时的数据还在缓冲池（Buffer Pool）中（也就是说还在内存中，还没来得及写入磁盘），还没来得及在磁盘持久化，所以需要一种机制存储已提交事务的数，为恢复数据使用。
- 于是redo log就派上用了。下面看下redo log何时产生的
- 既然redo log也需要存储，也涉及磁盘IO为什么还是要使用redo log？
  - redo log的存储是顺序存储，而缓存同步是随机操作
  - 缓存同步是以数据页为单位的，每次传输的数据大于redo log。
隔离性的实现
- 隔离性是事务ACID特性里最复杂的一个。在SQL标准里定义了四种隔离级别，每一种级别都规定一个事务中的修改，哪些是事务之间可见的，哪些是事务之间不可见的。
- 级别越低的隔离级别可以执行越高的并发，但是同时实现复杂度以及开销也越大
- Mysql隔离级别有以下四种（级别由低到高）
  - READ UNCOMMITTED（读未提交 RU）
  - READ COMMITTED(读已提交 RC)
  - REPEATABLE READ(可重复读 RR)
  - SERIALIZABLE(串行化)
- 只要彻底理解了隔离级别以及它的实现原理就相当于理解了ACID的隔离性。原子性，隔离性，持久性的目的都是为了做到一致性，但隔离性跟原子性和持久性有所区别。原子性和持久性是为了要实现数据的可靠性保障，比如做到系统宕机后，以及错误后的回滚。
- 那么隔离性要做到什么？隔离性是要管理多个并发读写请求的访问顺序。这种顺序包括串行或者是并行说明这一点，写请求不仅仅是指inserr操作，又包括update操作。
- 总之，从隔离性的实现可以看出这是一场数据的可靠性和性能之间的权衡
  - 可靠性高，并发性能低（比如 Serializable）
  - 可靠性低，并发性能高（比如 Read Uncommitted）
- READ UNCOMMITTED（读未提交）
  - 在READ UNCOMMITTED隔离级别下，事务中的修改即使还没提交，对其他事务是可见的。事务可以读取未提交的数据，造成脏读。因为读不加任何锁，所以写操作在读的过程中修改数据，会造成脏读。好处是可以做到提升并发性能，可以做到读写并行。
  - 换句话说，读操作不能排斥写操作
  - 优点：读写并行，缺点：造成脏读，不可重复读，幻读
- READ COMMITTED(已提交读)
  - 一个事务的修改在它提交之前的所有修改，对其他事务都是不可见的。其他事务能读到已提交的修改变化。在很多场景下这种逻辑是可以接受的。
  - InnoDB在READ COMMITTED，使用排它锁，读取数据不加锁而是使用MVCC机制，或者换句话说READ COMMITTED采用了读写分离。但是该级别会产生不可重复读和幻读的情况。
  - 什么是不可重复读？
    - 在一次事务中多次读取的结果不一样
  - 为什么会产生不可重复读？
    - 这跟READ COMMITTED级别下的MVCC机制有关，在该隔离级别下每次select的时候新生成一个版本号，所以每次在select的时候读的不是同一个副本而是不同的副本。
    - 在每次select之间有其他事务更新了当前事务所要读取的数据并提交了，那就出现了不可重复读
- REPEATABLE READ（可重复读）（Mysql默认隔离级别）
  - 在一个事务内的多次读取结果是一样的。这种隔离级别下可以避免，脏读，不可重复读的问题。mysql有两种机制可以达到这种隔离级别的效果，分别采用读写锁以及MVCC
  - 采用读写锁实现
    - 上面的读写锁为什么能可重复读？在读写锁实现REPEATABLE READ的情况下，只要读操作没释放读锁，在第二次读的时候还是可以读到第一次读的数据
      - 优点：实现起来简单
      - 缺点：无法做到读写并行
  - 采用MVCC实现
    - MVCC为什么能实现可重复读？因为多次读取只生成一个版本，读取到的自然是相同数据
      - 优点：读写并行
      - 缺点：实现的复杂度高
    - 但是在该隔离级别下仍然会出现幻读的问题。
- SERIABLIZABLE（串行化）
  - 该隔离级别理解起来特别简单，实现也最简单。所有的操作都加上排他锁，在Serializable事务隔离级别下，除了不会造成数据不一致的问题，没有其他优点
  - 隔离级别脏读可能性不可重复读可能性幻读可能性加锁读
    READ COMMITTED Yes Yes Yes No
    READ UNCOMMITTED No Yes Yes No
    REPEATABLE READ No No Yes No
    SERIALIZABLE No No No Yes
一致性的实现
- 数据库总是从一个一致性的状态转换到另一个一致性的状态
- 下面举个例子：zhangsan从银行卡转400到理财账户
  - ```
  start transaction;
  select balance from bank where name="zhangsan";// 生成 重做日志 balance=600
  update bank set balance = balance - 400; // 生成 重做日志 amount=400
  update finance set amount = amount + 400;commit;
```
- 假如执行完update bank set balance = balance - 400; 发生异常了，银行卡的钱不能平白无故的减少，而是回滚到最初的状态。
- 又或者事务提交之后，缓冲池还没同步到磁盘的时候宕机了，这也是不能接受的，应该在重启的时候恢复并持久化。
- 假如有并发事务请求的时候也应该做好事务之间的可见性问题，避免造成脏读，不可重复读，幻读等。在涉及并发的情况下往往在性能和一致性之间做平衡，做一定的取舍，所以隔离性也是对一致性的一种破坏。

隔离级别	脏读可能性	不可重复读可能性	幻读可能性	加锁读
READ COMMITTED	Yes	Yes	Yes	No
READ UNCOMMITTED	No	Yes	Yes	No
REPEATABLE READ	No	No	Yes	No
SERIALIZABLE	No	No	No	Yes

MYSQL的WAL（Write-Ahead Logging）机制

概念
- WAL，全称是Write-Ahead Logging，预写日志系统。指的是MYSQL的写操作并不是立刻更新到磁盘上，而是先记录到日志上，然后在合适的时间再更新到磁盘上。这样的好处是错开高峰期。日志主要分为undo log，redo log，binlog，这三种作用分别是“undo log完成MVCC从而实现MYSQL的隔离级别”，“降低随机写的性能消耗（转成顺序写），同时防止写操作因为宕机而丢失”，“写操作的备份，保证主从一致”。
- 存在的问题，wal对数据也进行修改时，通过将“修改了什么”这个操作记录在日志中，而不必马上将修改内容刷新到磁盘上，从而将随机写转换为顺序写，提高了性能。
- 但是由此带来了问题，内存中的数据页和磁盘上的数据页（如果在服务器宕机，mysql程序异常的情况下）内容不一致，此时将内存中的这种数据页称为脏页。
脏页
- 当内存数据页和磁盘数据页内容不一致的时候，将内存页称为“脏页”
- 内存数据页写入磁盘后，两边内容一致，此时称为“干净页”
- 将内存数据页写入磁盘的这个操作叫做“刷脏页”
- InnoDB是以缓冲池（Buffer Pool）来管理内存的，缓冲池中的内存页有3种状态
  - 未被使用
  - 已被使用，并且是干净页
  - 已被使用，并且是脏页
- 由于InnoDB的策略通常是尽量使用内存，因此长时间运行的数据库中的内存基本都是已被使用的，未被使用的内存页很少。
刷脏页（Flush）
- 刷新脏页时机
  - Redo log写满了，需要将check point向前推进，以便继续写日志，checkpoint向前推进时，需要将推进区间涉及的所有脏页刷新到磁盘。
  - 内存不足，需要淘汰一些内存页（最久未使用）给别的新的数据页使用。此时如果是干净页，则可以直接拿来使用覆盖。如果是脏页，则需要先刷新到磁盘（直接写入磁盘，不用管Redo log，后续Redo Log刷脏页时会判断对应数据页是否已刷新到磁盘），操作之后脏页成为干净页，此时可以拿来使用覆盖。
  - 数据库系统空闲时会刷新脏页，（数据库繁忙的时候也会尽量刷新脏页）
  - 数据库关闭的时候，此时需要将所有脏页刷新到磁盘
- Redo Log写满
  - 这种情况尽量避免，因为此时数据库就不接受更新，所有更新语句都会被阻塞，此时更新数为0。
  - 这种情况对于敏感业务是不能接受的
  - 此时需要将write pos向前推进，推进范围内Redo Log涉及的所有脏页都需要将flush到磁盘中。
  - Redo Log设置过小或写太慢的问题：此时由于Redo Log频繁写满，会导致频繁触发flush脏页，影响tps。
- 内存不足
  - 这种情况其实是常态
  - 当从磁盘读取的数据页在内存中没有内存时，就需要到缓冲区中申请一个内存页，这时候根据LRU（最久不使用）就需要淘汰掉一个内存页来使用。
  - 此时淘汰的是脏页，则需要将脏页刷新到磁盘，变成干净页后才能复用。（注意，这个过程Write Pos位置不会向前推进的）
  - 当一个查询要淘汰的脏页数太多，会导致查询的响应时间明显变长。
- 策略
  - InnoDB控制脏页的策略主要参考：
    - 脏页比例：当脏页比例接近或超过参数innodb_max_dirty_pages_pct时，则会全力刷新，否则按照百分比。innodb_max_dirty_pages_pct默认值为75
    - redo log写盘速度：N=(wirte pos 位置的日志序号 - check point对应序号)，当N越大，则刷盘速度越快。
  - 最终刷盘速度取上述两者最快的
- 参数 innodb_io_capacity
  - InnoDB有一个关键参数：innodb_io_capacity，该参数用于告知InnoDB系统的磁盘能力，该值通常建议设置为磁盘的IOPS。（该参数在mysql5.5后才可以调整）
  - 测试磁盘IOPS
    - ```
    fio -filename=/data/tmp/test_randrw -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
```
- innodb_io_capacity一般参考写能力IOPS
- innodb_io_capacity设置过低导致性能问题案例
  - MYSQL写入速度很慢，TPS很低，但数据库的IO压力并不大
- 当 innodb_io_capacity设置过小时，InnoDB会认为磁盘性能差，导致刷新脏页很慢，甚至比脏页生成速度还慢，就会造成脏页的积累，影响查询和更新性能
- innodb_io_capacity大小设置
  - 配置小，此时由于InnoDB认为磁盘性能差，因此脏页刷新效率会更高，以此来确保内存中的脏页比例较少。
  - 配置大，InnoDB任务磁盘性能好，因此刷新脏页频率会降低，抖动的频率也会降低。
- 参数innodb_max_dirty_pages_pct
  - innodb_max_dirt_pages_pct指的是脏页比例上限（默认值是75%），内存中脏页比例越是接近innodb_max_dirt_pct，则InnoDB刷盘速度越会接近全力
  - 如何计算内存中的脏页比例:
    - ```
    show global status like 'Innodb_buffer_pool_pages%';
```
- 脏页比例 = 100 * Innodb_buffer_pool_pages_dirty / Innodb_buffer_pool_pages_total的值
- 参数innodb_flush_neighbors
  - 当刷新脏页时，若脏页旁边的数据页也是脏页，则会连带刷新（注意这个机制是会蔓延的）
  - 当innodb_flush_neighbors = 1时开启该机制，默认是1，但在MYSQL8.0中默认值是0
  - 由于机械硬盘时代IOPS一般只有几百，该机制可以有效减少很多随机IO，提高系统性能
  - 但在固态硬盘时代，此时IOPS高达几千，此时IOPS往往不是瓶颈，“只刷自己”可以更快执行完查询操作，减少SQL语句的响应时间
为什么要使用WAL，包含redo和undo信息？
- 如果一个系统直接将变更应用到系统状态中，那么在机器掉电重启之后系统需要知道操作是成功了，还是只是部分成功或者是失败了（为了恢复状态）。如果使用了WAL，那么在重启之后系统可以通过比较日志和系统状态是继续完成操作还是撤销操作。