MySQL 更新执行流程

MySQL 更新执行流程

上篇讲完了查询流程，再讲讲更新流程

在数据库里面，我们说的update操作其实包括了更新、插入和删除。如果大家有看 过MyBatis的源码，应该知道Executor里面也只有doQuery()和doUpdate。的方法， 没有 doDelete()和 dolnsert()

更新流程和查询流程有什么不同呢？

基本流程也是一致的，也就是说，它也要经过解析器、优化器的处理，最后交给执行器。
区别就在于拿到符合条件的数据之后的操作。

一：缓冲池 Buffer Pool

首先，对于InnoDB存储引擎来说，数据都是放在磁盘上的，存储引擎要操作数据, 必须先把磁盘里面的数据加载到内存里面才可以操作。

这里就有个问题，是不是我们需要的数据多大，我们就一次从磁盘加载多少数据到 内存呢？

磁盘I/O的读写相对于内存的操作来说是很慢的。如果我们需要的数据分散在磁盘 的不同的地方，那就意味着会产生很多次的I/O操作。

对数据进行操作的时候，每次都需要从磁盘中读取数据到内存，在内存中计算完成后再写回到磁盘，这个过程的效率比较低。

有没有什么办法可以提高效率?

还是缓存的思想。把读取过的数据页缓存起来。 于是，在InnoDB中引入了缓存的设计理念（Buffer Pool），设计了一个内存的缓冲区。读取数据的时候，先判断是不是在这个内存区域 里面，如果是，就直接读取，然后操作，不用再次从磁盘加载。如果不是，读取后就写 到这个内存的缓冲区。这个内存区域有个专属的名字，叫Buffer PooL

1. 读取数据的时候，先判断数据是否存在内存的缓冲区中，如果存在，则直接从缓冲区读取，否则，从磁盘读取数据，再写入到内存缓冲区。
2. 修改数据时，数据先写入到Buffer Pool，而不是直接写到磁盘，InnoDB专门有一个后台线程把Buffer Pool中的数据写入到磁盘，内存的数据页和磁盘数据不一致的时候，我们把它叫做脏页。

所以，无论是操作系统也好，还是存储引擎也好，都有一个预读取的概念。也就是 说，当磁盘上的一块数据被读取的时候，很有可能它附近的位置也会马上被读取到，这 个就叫做局部性原理。那么这样，我们干脆每次多读取一点，而不是用多少读多少。

局部性原理：很有可能它附近位置的数据马上也会被用到，于是，一次性多读取一些数据保存到Buffer Pool中，通过空间换时间的设计思想，提升数据的IO效率

InnoDB设定了一个存储弓I擎从磁盘读取数据到内存的最小的单位，叫做页。操作系 统也有页的概念。操作系统的页大小一般是4K,而在InnoDB里面，这个最小的单位默 认是16KB大小。也就是说一次数据读取操作，会从磁盘上加载16KB的数据保存到Buffer Pool中如果要修改这个值的大小，需要清空数据重新初始化服务。

每个缓存页会对应一个描述数据，这个描述数据本身也是一块数据，它包含数据页所属的表空间、数据页编号、数据页在Buffer Pool种的地址等信息。在Buffer Pool中，每个缓存页的描述数据放在最前面，然后各个缓存页放在后面。

二：缓存池的空间管理（LRU）

缓存池的空间默认是128M，当然我们可以根据服务器的配置来调整缓存池的大小，官方建议是，实际情况中可以配置机器内存的50%~75%左右。

即便是这样，缓存池空间大小总是有限制的，如果缓存页中加载了非常多的数据导致缓存池耗尽了怎么处理呢？

缓冲池种中的LRU链表，LRU链表会被拆分成为两部分，

1. 一部分为热数据，叫New Sublist
2. 另一部分为冷数据，叫Old Sublist

1. 所有新的数据页加入到Buffer Pool时，一律先放到冷数据区的head位置，不管是预读数据还是普通的读操作。
2. 如果是Old Sublist的数据被访问，就会移动到New Sublist中，没有被访问的数据页会移动到Old Sublist，而在实现数据淘汰时，会直接从Old Sublist中进行淘汰（淘汰tail部分的数据）
3. 默认情况下，热数据区占5/8，冷数据区占3/8 ，innodb_old_blocks_pct由这个属性控制，它表示old区的空间大小，这个值可以修改，修改区间为5%~95%，值越小，就使得Old区没有被访问的数据的淘汰速度更快

有一个很重要的问题，假如：

select * from table;

由于没有使用索引，所以会进行全表扫描，这种查询属于短时间内访问一次，但是后面基本上都不会用到了。如果被访问了一次导致它从冷数据区移动到热数据区，使得热数据区的热点数据被移动到冷数据区从而被淘汰。

这种情况下，导致BufferPool中全是低频的数据页，使得缓冲命中率大大降低，那这种情况改怎么处理呢？

于是InnoDB指定了一个冷数据区移动到热数据区的规则：如果这个数据页在LRU链表中冷数据区存在的时间超过了1秒，就把它移动到热数据区这个存在时间由innodb_old_blocks_time控制，默认值是1秒。

那脏页什么时候才同步到磁盘呢？

1. InnoDB里面有专门的后台线程把Buffer Pool的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。
2. Buffer Pool内存不足
3. Redo Log写满
4. 数据库正常关闭

但是又有一个问题，就是最终要把哪些数据刷新到磁盘呢？

不可能所有的缓存页都刷回磁盘的，因为有的缓存页可能是因为查询的时候因为预读取机制加载到buffer pool中的，可能根本没修改过，如果也同步一次，那显然也不合理。
因此，我们需要做的是：把修改过的数据刷新到磁盘。

所以引入了 一个Flush链表，这个Flush链表本质上也是通过缓存页的描述数据块中的两个指针，让被修改过的缓存页的描述数据块，组成一个双向链表，如下图所示。

总结一下：Buffer Pool的作用是为了提高读写的效率。

三：Redo log

有了缓冲池之后，更新数据的执行过程如下。

就是我们对数据库中的内存完成了一系列的增删改操作，虽然内存数据更新了，单是内存到磁盘的刷新是异步的，刷脏不是实时的，如果Buffer Pool里面的脏页还没有刷入磁盘 时，数据库宕机或者重启，这些数据就会丢失 。

为了避免这种问题的出现，InnoDB对所有数据页的修改操作，都记录到了一个日志文件中（这个日志文件叫Redo Log）

如果有未同步到磁盘的数据，数据库在启动的时候，会从这个日志文件进行恢复操 作（实现crash-safe） ，从而实现数据的恢复，这就是事务ACID特性中D(持久性)的保障机制。

同样是写磁盘，为什么不直接写到dbfile里面去？为什 么先写日志再写磁盘？

写日志文件和和写到数据文件有什么区别？

因为磁盘的构造，如果要读写数据, 必须找到数据对应的扇区，这个过程就叫寻址。如果我们所需要的数据是随机分散在磁盘上不同页的不同扇区中，磁盘的盘片不停地旋转这个就是随机IO，读取数据速度较慢，
所以假设我们已经找到了第一块数据，并且其他所需的数据就在这一块数据后边，那么 就不需要重新寻址，可以依次拿到我们所需的数据，这个就叫顺序IO

所以在看为什 么先写日志再写磁盘这个问题，答案就是：

刷盘是随机I/O,而记录日志是顺序I/O （连续写的），顺序I/O效率更高，本质上 是数据集中存储和分散存储的区别。因此先把修改写入日志文件，在保证了内存数据的 安全性的情况下，可以延迟刷盘时机，进而提升系统吞吐。

redo log 位于/var/lib/mysql/目录下的 ib logfile0 和 ib logfile1 默认 2 个文件，每个48M。

特点：

• 支持崩溃恢复crash-safe
• 不是记录数据页更新之后的状态，而是记录的是"在某个数据页上做了 什么修改”。属于物理日志
• 大小是固定的，它采用循环写的方式记录，当写到结尾时，会回到开头循环写日志，前面的内容会被覆盖
  -

3.1、redo log buffer

在上图中我们发现写Redo Log时，是先写到Redo Log Buffer种，然后再刷新到Redo Log文件？

为什么不直接写磁盘，而是又要增加一个缓冲区呢？官网的描述。

翻译：日志缓冲区是存储要写入磁盘上日志文件的数据的内存区域。日志缓冲区大小由innodb_Log_buffer_size变量定义。默认大小为16MB。日志缓冲区的内容会定期刷新到磁盘。大型日志缓冲区使大型事务能够运行，而无需在事务提交之前将重做日志数据写入磁盘。因此，如果有更新、插入或删除多行的事务，增加日志缓冲区的大小可以节省磁盘I/O。

在计算机操作系统中，用户空间(user space)下的缓冲区数据，一般是无法直接写入磁盘的，必须经过操作系统内核空间缓冲区(即OS Buffer)。

• 日志最开始会写入位于存储引擎Innodb的redo log buffer，这个是在用户空间完成的。
• 然后再将日志写到操作系统内核空间的缓冲区(OS buffer)中。
• 最后，通过系统调用fsync()，从OS buffer写入到磁盘上的redo log file中，完成写入操作。这个写入磁盘的操作，就叫做刷盘。

Log Buffer刷盘机制：

可以通过参数innodb_flush_log_at_trx_commit进行配置，参数值含义如下：

• 0：称为延迟写，事务提交时不会将redo log buffer中日志写入到OS buffer，而是每秒写入OS buffer并调用写入到redo log file中。
• 1：称为实时写，实时刷”，事务每次提交都会将redo log buffer中的日志写入OS buffer并保存到redo log file中。
• 2：称为实时写，延迟刷。每次事务提交写入到OS buffer，然后是每秒将日志写入到redo log file。

redo log 为什么可以保证crash safe机制呢？

• 因为redo log每次更新操作完成后，就一定会写入的，如果写入失败，说明此次操作失败，事务也不可能提交。
• redo log内部结构是基于页的，记录了这个页的字段值变化，只要crash后读取redo log进行重放就可以恢复数据。

3.2、双写缓冲（InnoDB的一大特性）:

innoDB的页和操作系统的页大小不一致，InnoDB页大小一般为16K,操作系统页 大小为4K, InnoDB的页写入到磁盘时，一个页需要分4次写。如果存储引擎正在写入页的数据到磁盘时发生了宕机，可能出现页只写了一部分的 情况，比如只写了 4K,就宕机了，这种情况叫做部分写失效(partial page write),可 能会导致数据丢失。

我们不是有redo log吗？但是有个问题，如果这个页本身已经损坏了，用它来做崩 溃恢复是没有意义的。

所以在对于应用redolog之前，需要一个页的副本。如果出现了 写入失效,就用页的副本来还原这个页，然后再应用redo log。这个页的副本就是double write, InnoDB的双写技术。通过它实现了数据页的可靠性。

跟redo log —样,double write由两部分组成，一部分是内存的double write, —个部分是磁盘上的double write0因为double write是顺序写入的，不会带来很大的开销。

四：Undo log

undo log（撤销日志或回滚日志）记录了事务发生之前的数据信息、状态，分为insert undo log和update undo log如果修改数据时出现异常，可以用undo log来实现回滚操作 （保持原子性）

可以理解为undo log记录的是反向的操作，比如insert会记录delete, update 会记录update原来的值，它跟redo log重做日志所记录的相反，重做日志记录数据被修改后的信息。undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，这样发生错误时才可以回滚。

show global variables like '%undo%‘;

五：Binlog

• bin log是归档日志，属于MySQL Server层的日志。可以实现主从复制和数据恢复两个作用
• 跟redolog不一样，它的文件内容是可以追加的，没有固定大小限制。
• 开启了binlog功能的情况下，我们可以把binlog导出成SQL语句，把所有的操作重放一遍，来实现数据的恢复
• 另一个功能就是用来实现主从复制，它的原理就是从服务器读取主服务器 的binlog,然后执行一遍

binlog日志有三种格式：

• Statement：基于SQL语句的复制((statement-based replication,SBR))
  优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
  缺点：由于记录的只是执行语句，为了这些语句能在备库上正确运行，还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在备库得到和在主库端执行时候相同的结果。
• Row:基于行的复制。(row-based replication,RBR)
  优点：binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。
  缺点:可能会产生大量的日志内容
• Mixed:混合模式复制。(mixed-based replication,MBR)
  实际上就是Statement与Row的结合。一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式

5.1 binlog刷盘机制

所有未提交的事务产生的binlog，都会被先记录到binlog的缓存中。等该事务提交时，再将缓存中的数据写入binlog日志文件中。缓存的大小由参数binlog_chache_size控制。
binlog什么时候刷新到磁盘呢？由参数sync_binlog控制

• 当sync_binlog为0时，表示MySQL不控制binlog的刷新，而是由系统自行判断何时写入磁盘。选这种策略，一旦操作系统宕机，缓存中的binlog就会丢失。
• sync_binlog为N时，每N个事务，才会将binlog写入磁盘。
• 当sync_binlog为1时，则表示每次commit，都将binlog 写入磁盘。
  

六：到目前为止再来看下一条更新语句是怎么执行的
例如一条语句：update teacher set name='dog' where id=1;

• 1、 先查询到这条数据，如果有缓存，也会用到缓存。
• 2、 把name改成dog，然后调用引擎的API接口，写入这一行数据到内存， 同时记录redo logo这时redolog进入prepare状态，然后告诉执行器，执行完成了，可以随时提交。
• 3、 执行器收到通知后记录binlog,然后调用存储引擎接口，设置redolog为 commit 状态。
• 4、 更新完成， 后续InnoDB会在合适的时候把此次操作的结果写回到磁盘（刷脏）。

6.1、为什么需要两阶段提交呢?

• 如果不用两阶段提交的话，可能会出现这样情况：bin log写入之前，机器crash导致需要重启。重启后redo log继续重放crash之前的操作，而当bin log后续需要作为备份恢复时，会出现数据不一致的情况。
• 如果是bin log commit之前crash，那么重启后，发现redo log是prepare状态且bin log完整（bin log写入成功后，redo log会有bin log的标记），就会自动commit，让存储引擎提交事务。

两阶段提交就是为了保证redo log和binlog数据的安全一致性。只有在这两个日志文件逻辑上高度一致了。你才能放心的使用redo log帮你将数据库中的状态恢复成crash之前的状态，使用binlog实现数据备份、恢复、以及主从复制。

6.2、在崩溃恢复时，判断事务是否需要提交：
1、 binlog无记录，redolog无记录：在redolog写之前crash,恢复操作：回滚事务
2、 binlog无记录，redolog状态prepare:在binlog写完之前的crash,恢复操 作：回滚事务
3、 binlog有记录，redolog状态prepare:在binlog写完提交事务之前的crash, 恢复操作：提交事务
4、 binlog有记录，redolog状态commit:正常完成的事务，不需要恢复