MySQL学习笔记Ⅲ-事务

事务

wiki：事务是数据库管理系统（DBMS）执行过程中的一个逻辑单位，有一个有限的数据库操作序列构成。InnoDB支持事务。

事务的四大特性

1. 原子性：Atomicity，不可再分，意味着对数据库的一系列操作要么全部成功要么全部失败，不会出现其他的情况。出错时需要进行回滚，在InnoDB中使用undo log来实现，记录了数据修改之前的值（逻辑日志）。
2. 一致性：Consistency，指数据库的完整性约束没有被破坏，事务执行前后都是合法的数据状态。比如主键必须是唯一的，字段长度符合要求。
3. 隔离性：Isolation，数据库中会有很多的事务同时去操作同一张表或者同一行数据，必然会出现一些并发或者干扰的操作，对隔离的定义就是说这些事务之间是透明的、相互不干扰的，借此保证数据的一致性。
4. 持久性：Durability，对数据库的任何操作，增删改查，只要事务提交成功，那么结果就是永久的，不可能因为系统的宕机或者重启而恢复到之前的状态。持久性通过redo log来实现，在操作数据时，会先写到内存的buffer pool中，同时记录redo log，如果在刷盘之前出现异常，在重启后就可以读取redo log的内容写到磁盘，保证数据的持久性。

InnoDB三大特性

1. 双写缓冲区（DoubleWrite Buffer）

   是一个存储区，InnoDB将页面写入InnoDB数据文件中的适当位置之前，先从缓冲池中刷新页面。如果在页面写入过程中存在操作系统、存储子系统或意外的misqld进程退出，则InnoDB可以在崩溃期间从doublewrite缓冲区找到页面的良好副本。在系统恢复后，MySQL可以根据redolog来恢复，而MySQL在恢复过程中是检查page的checksum，即page的最后事务号，发生partial page write（页断裂，页断裂是数据库宕机时，数据库页面只有部分写入磁盘，导致页面出现不一致的情况。）问题时，page已损坏，找不到该page中的事务号，就无法恢复。而双写缓冲区，在宏观上与事务的四特性有关，在底层则是为了解决partial page write问题。

   

   InnoDB会在磁盘上划分出连续的两个区的范围，一个区包含64页，一页16k，一个区为1M，因此一个双写缓冲区的大小为两个1M，共2M。

   • 我们写数据时，InnoDB会先调用memcopy函数把数据从内存的buffer pool中复制到双写缓冲区中，接着调用fsyc函数将其写入到磁盘的双写缓冲区中（完整页数据），最后再把数据写入到对应的数据页中。

   • 如果写页过程出现意外崩溃，InnoDB在稍后的恢复过程中在双写缓冲区中找到完好的page副本用于修复。redolog存储的是修改操作，只能恢复被修改过字段出现的损坏。

   • 为什么写数据要提前写个双写缓冲区？

     一页数据是16kb，磁盘写一次是4kb，因此一页数据写磁盘需要写四次。假如在写到8kb时数据库崩了，就会出现partial page write问题。而双写缓冲区作为预分配的磁盘位置，数据库在写磁盘双写缓冲区时是使用的顺序写，因此效率上比直接写数据的随机写快的多，出问题的概率系小。

2. AHI 自适应哈希

   InnoDB自行研发的，用于提高MySQL的查询效率。

   InnoDB内部会自己去监控索引表，如果监控到某个索引经常用，那么就认为是热数据，然后内部会自己创建一个hash索引，称之为自适应哈希索引（Adaptive Hash Index，AHI）。

   创建之后，若下次又查询到该索引，则可直接通过hash算法推导出记录地址一次查到数据，比查b+树更有效率。MySQL在哈希索引的设计上还采用了热点分散技术，这样的哈希索引在MySQL上默认是启动8个的，热点数据会分散到不同的哈希索引上，因此热数据访问时，能将请求分散到不同的哈希索引上，提高并发访问的性能。

3. Buffer Pool

   • 什么是Buffer Pool

     InnoDB为了缓存磁盘中的页，在MySQL服务器启动的时候就向操作系统申请了一片连续的内存，叫做Buffer Pool。默认大小只有128M大小，启动服务器时可以通过配置innodb_buffer_pool_size参数的值来指定大小。

   • Buffer Pool内部组成

     bufferpool中默认的缓存页大小和在磁盘上默认的页大小是一样的，都是16KB。InnoDB为每一个其中的缓存页都创建了控制信息，这些控制信息包括该页所属的表空间编号、页号、缓存页在bufferpool中的地址，链表节点信息、一些所信息以及LSN信息等，称之为控制块。

     MySQL大部分缓存的数据都在bufferpool中，缓存页就是数据，数据库查询出来的数据都会缓存到缓存页中，便于下次快速查询，控制块保存了缓存页的各种信息地址等，用来查找缓存页。

   • Buffer Pool中的链表结构

     • Free 链：是将所有空闲的缓存页串起来，也就是将空闲的缓存页对应的控制块地址用一个链表来维护，在新数据到来时，可以直接在Free链上找到空闲缓存页。

     • Flush链：结构与Free链一样，MySQL把缓冲区上面这种被修改过的数据的控制块也用一个链表来进行维护，以此来快速定位被修改过的数据，也被称之为脏数据（未落盘的数据），因此Flush链也被称为脏链。

       MySQL数据提交之后并不是立刻落盘的，而是依然在缓冲区里，最后会统一落盘，数据提交后会有redo日志，即使没有落盘，在数据库挂掉的时候也依然可以恢复数据，读取的时候也可以正常读取。

     • LRU链表：是一种最近最少使用淘汰链表，使用过的数据会被维护到这个链表上，新用提到表头，当链表满的时候就从链表尾开始淘汰不常使用的数据。

       MySQL对LRU的改进措施：

       1. 将LRU链表分为两部分，前面为热数据区（Young区），后面为冷数据区（Old区，占37%）。排除了全表扫描等类似的大数据量查询直接把热门数据淘汰出缓冲区的情况。

       2. 对冷链数据移动到热链上做了时间限定。限定时间内对冷链上数据的访问不会移动到热数据区，只有超过这个时间，再次访问冷链上的数据才会被移动到热数据区。避免了短时间内全表扫描等大数据量频繁刷新导致热数据被移出热链的情况。

       3. 热链部分并非每次访问都会向前移动，只有在热点的后四分之一内的数据才会在访问时移动到热链头部，减少移动带来的资源消耗，提升性能。

       4. 参数：

          innodb_old_blocks_pct: 调整冷热数据区域占比，默认37%
          innodb_old_blocks_time: 调整限定间隔时间，默认1秒

     • MySQL刷新数据落盘的途径

       MySQL后台会有一个定时器，定时将数据进行统一落盘，以不影响用户线程处理正常的请求。

       • BUF_FLUSH_LIST：从flush链表中刷新一部分页面到磁盘，刷新速率取决于当时系统是不是很繁忙。
       • BUF_FLUSH_LRU：从LRU链表的冷数据中刷新一部分页面到磁盘。后台线程会定时从LRU链表尾部开始扫描一些页面，扫描的页面数量可以通过系统变量innodb_lru_scan_depth来指定，如果从里面发现脏页，会将其刷新到磁盘。
       • BUF_FLUSH_SINGLE_PAGE：有时候后台线程刷新脏页的进度比较慢，导致用户线程在准备加载一个磁盘页到BufferPool时没有可用的缓存页，这时就会尝试看LRU链表尾部有没有可以直接释放掉的未修改页面，如果没有的话，会不得不将LRU链表尾部的一个脏页同步刷新到磁盘（会降低处理用户请求的速度）。

事务并发带来的问题

1. 脏读：事务中，修改了数据但没提交，从而导致前后两次读取数据不一致的情况。由未提交修改而产生的不一致结果称之为脏读。

   

2. 不可重复读：发生在update和delete情形中。第一个事务查询到了一个数据，然后第二个事务进行了一个update操作，并通过commit提交了修改，之后第一个事务又进行了一次查询，出现了前后两次读取数据不一致的情况。由提交修改造成的不一致称为不可重复读。

   

3. 幻读：发生在INSERT情形。第一个事务执行了一个范围查询，然后第二个事务执行了一个插入操作并提交修改。第一个事务再去执行查询时，结果不一致。由插入数据造成的称为幻读。

隔离级别

SQL92标准

1. Read Uncommitted，未提交读

   一个事务可以读取到其他事务未提交的数据，出现脏读。没有解决任何问题。

2. Read Committed，已提交读

   一个事务只能读取到其他事务已提交的数据，不能读取到未提交的数据，解决了脏读的问题，但会出现不可重复读的问题。

3. Repeatable Read，可重复读

   解决了不可重复读问题，即在同一个事务里面多次读取同样的数据结果是一样的，但在这个级别下没有解决幻读的问题。

4. Serializable，串行化

   所有事务都是串行执行的，也就是对数据的操作需要排队，已经不存在事务的并发操作了，所以解决了所有的问题。

MySQL InnoDB对隔离级别的支持

事务隔离级别	脏读	不可重复读	幻读
未提交读	可能	可能	可能
已提交读	不可能	可能	可能
可重复读	不可能	不可能	对InnoDB不可能
串行化	不可能	不可能	不可能

两大实现方案

1. LBCC

   读取数据的时候，锁定要操作的数据，不允许其他事务修改。这种方案称之为基于锁的并发控制（Lock Based Concurrency Control，LBCC）。

2. MVCC

   在修改数据的时候给他建立一个备份或者快照，后面再来读取这个快照就行了，这种方案称为多版本的并发控制（Multi Version Concurrency Control，MVCC）。其核心思想为：我可以查到我这个事务开始之前已经存在的已提交的数据，即使它在后面被修改或者删除了，在我这个事务之后新增的数据我是查不到的。

   InnoDB为每行记录都实现了两个隐藏字段，还要加上一个ROWID：

   • DB_TRX_ID：6字节，插入或更新行的最后一个事务的事务ID，事务编号是自动递增的。我们将其理解为创建版本号，在数据新增或者修改为新数据的时候，记录当前事务ID，即创建版本号被修改为当前事务ID。

   • DB_ROLL_PTR：7字节，回滚指针。将其理解为删除版本号，数据被删除或记录为旧数据时，记录当前事务ID，即删除版本号被修改为当前事务ID。

   • 将这两个事务ID理解为版本号。后面事务的创建版本号比当前事务ID大的修改无法被当前事务查询出，但是删除版本号大于当前ID的事务的修改操作会被查出来。查询操作的事务可被执行多次，且事务创建版本号不变。