3.mysql更新语句执行过程详解

故事是这样开始的，很久很久以前，在一个月结(喝酒的人一个月结一次账)酒店的老板是这样记账的：每次一个客户进来买酒，老板都会找出账本，然后呢再找到这个人，在这个人的名字下面记一下买酒的金额、日期，记完之后把酒给到客户。后来酒店的生意越来越好，老板发现每次客户来都找到账本再找到这个人名字，记录赊账信息，效率十分低下，忙不过来。于是老板想到一个新的办法：他找来一个黑板，每次客户来呢，先把这个人的赊账信息记录在黑板上，等到自己空闲的时候再把赊账信息一条一条更新到账本上。老板这么一搞，一天接待的客户也多了，这真是一个好办法。``

mysql更新语句会涉及到写磁盘的过程，如果每次更新语句都去写磁盘就像酒店老板每次找到账本写赊账信息一样，那必然很影响mysql处理速度，更不可能在现在高并发的场景下满足要求。为了提高处理速度，mysql实际上也是采用了先写黑板再写账本的方法，黑板和账本的配合过程，就是mysql 中常说的WAL(Write-Ahead Logging)技术。其实就是先写日志再写磁盘。这里的日志在mysql中叫redo log，对应的就是酒店老板的小黑板，磁盘对应的就是酒店老板的账本。其实呢mysql 的更新过程不仅有redo log还涉及到binlog，那下面我们先介绍一下 redo log 。

redo log

redo log记录的是物理日志，是对数据页某个位置的修改，所以说redo log 会记修改的数据页的编号(page no)。这里插一句 redo log 也会记录LSN(log sequence number)，LSN 是单调递增的，每次写入长度为length的redo log，LSN就会加length，来标识每次redo log 的写入位点，数据页也会记录当前页最后一次修改的LSN，它记录在数据页的头部，它的主要目的是用于在恢复数据时对比redolog日志的LSN号决定是否对该页进行恢复数据，LSN把一个事务开始到恢复的过程串联起来了。前面说的LSN，checkpoint也是有记录的，checkpoint位于redo log file 文件file_header 里面 。innodb 引擎在写redo log 的时候先把redo log 写到 redo log buffer 中（redo log buffer 的大小由），写的时候是一个一个的redo log block ，redo log block每个大小是512字节，其结构如下：

redo buffer 结构.png

其中log block中492字节的部分是log body，该log body的格式分为4部分：

• redo_log_type：redo log的日志类型，占用1个字节。
• space：空间的ID，采用压缩的方式后，占用的空间可能小于4字节。
• page_no：页的偏移量
• redo_log_body 重做日志的数据部分，恢复时会调用相应的函数进行解析。例如insert语句和delete语句写入redo log的内容是不一样的。

在刷盘的时候，会将 redo log buffer (大小由innodb_log_buffer_size控制)中的日志块写入redo log file 中 就是我们经常在/data 目录中看到的以ib_logfile开头的文件，ib_logfile 文件的大小由innodb_log_file_size 控制，个数由innodb_log_files_in_group 控制，ib_logfile 文件之间的关系是他们是同属于一个组，文件之间通过链表链接 在组内形成一个环，就这样覆盖写，实际上是这样存在的：

redo log物理结构

逻辑上是这样存在的：

redo log 组逻辑结构

这里也要说一下图中 write pos和check point的含义：

• write pos 它表示的是日志的当前写入位置，一边写一边后移，当写到ib_logfile_3末尾的时候，再继续写到ib_logfile_0
• checkpoint 是当前要擦除的位置，也是往后移动的，擦除前要把日志更新到数据文件中（就是磁盘中的数据页）

如果checkpoint 追上write pos ，那么表示已经没有地方来写日志了，这个时候不能再执行更新，需要将checkpoint 往后移动，移动的部分就是刷脏页（这个过程在下一节讲），有了 redo log，mysql就有了crash_safe 的能力，就是说innodb 能够保证数据库发生异常重启，数据不会丢失。

这里还有一个问题 relog buffer 里面的日志块什么时候写入 redo log 文件呢（既ib_logfile文件）？

这里要注意的是没有提交事务的redo log 也是可能写入到磁盘的，所以说我们分两大类来讨论写盘问题

• 事务已提交

  为了控制 redo log 的写入策略，InnoDB 通过 innodb_flush_log_at_trx_commit 参数，来控制redo log的写入策略，它有三种可能取值：

  1. 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
  2. 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
  3. 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

  InnoDB，后台有一个线程每1s 就会把redo buffer 里面的日志调用write写到文件系统的page cache 里面，然后调用fsyn持久化到磁盘，因为事务在执行过程中写的日志都在 redo buffer 里面，所以所一个未完成（未提交）的事务的日志是可能持久化到磁盘的

• 事务未提交

  除了上面说的定时线程会将未提交的事务的日志持久化到磁盘外，还有两种情况也会将未提交事务的日志持久化到磁盘

  1. 当redo log buffer 的已用空间超过 innodb_log_buffer_size 规定空间一半的时候，后台线程会主动写盘，但是这里注意的是这个写盘只是调用了write 没有调用 fsync，所以说只是写到了 page_cache 里面。
  2. 并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘。

binlog

binlog 也是日志，它是server 层记录的日志。我们来比较一下它和redo log 日志文件的不同

1. redo log 是 引擎层产生的，binlog 是由server 层产生的，所有引擎共用
2. redo log 它是循环写，binlog 是追加写，它不会覆盖数据，写完之后再换到写一个问价写
3. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

binlog 记录有三种格式：statement、row、mixed

1. statement 记录的是执行语句 主从复制时可能会出现问题
2. row 记录要修改的数据 缺点就是 日志文件比较大， 优点就是 数据恢复
3. mixed mysql 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，也就是在 statement 和 row 之间选择一种

binlog 刷盘过程：

binlog 的写入逻辑是这样的：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。

每个线程有自己 binlog cache，但是共用同一份 binlog 文件。

binlog刷盘的时机是由参数 sync_binlog 控制的：

1. sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；

2. sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；

3. sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。

在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。

mysql 更新流程

讲完了 redo log 和 binlog ,现在我们有了基础知识，那我们现在就来看看更新mysql的更新过程是怎样的？

未避免流程差异化太大，这里我们设置一个前提，mysql 的版本是5.7，非自动提交（与自动提交差别不大 主要是为了更清楚的描述整个过程），sync_binlog =1，innodb_flush_log_at_trx_commit = 1，binlog 是打开的，现在我们拿语句UPDATE t set c= 20 where id =2;来说明过程

• 执行器首先调用引擎层的接口获得 id = 2 的数据，引擎层的内存如果存在id = 2 这一行的页，那么直接返回给执行器 如果不存在那么 从磁盘中加载该页 并放在内存中 然后再返回给server 层

• 执行器获得数据后，对字段C 设置为20 ，并再次调用引擎层接口，引擎层先将本次要修改数据的原始数据写到undo log 中，以防回滚，然后将本次修改记录在 redo log 中 并存于buffer 中

• 事务进入提交阶段（这里用到了两阶段提交），首先将redo buffer 里面的日志写到磁盘，并标记为prepare状态，并告诉执行器 我已经提交了，你也可以提交了，这个时候提交的第一阶段完成

• 开始第二阶段的提交，执行器生成这个操作的binglog，并将binglog 也写到磁盘 这个时候xid 也写入到了binlog，binglog 落盘后，执行器再调用引擎层的提交事务接口，将redo log 标记为commit 状态，注意的是这个时候redo log 数据不用落盘

• 更新完成

为什么是两阶段提交

• 先写 redo log 后写 binlog

  假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。

• 先写 binlog 后写 redo log。

  如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

系统重启，数据恢复过程

数据库恢复后会判断redo log的事务是不是完整的，如果不是则根据undo log回滚；如果是完整的并且是prepare状态，则进一步判断对应的事务binlog是不是完整的，如果不完整则一样根据undo log进行回，如果是binlog是完整的就进行提交