MySQL存储引擎如何完成一条更新语句的执行

假设我们有一条SQL语句是这样的：

update t_user set name='月伴飞鱼' where id=1;

那么我们先想一下这条SQL语句是如何执行的?

首先肯定是我们的系统通过一个数据库连接发送到了MySQL上，然后肯定会经过SQL接口、解析器、优化器、执行器几个环节，解析SQL语句，生成执行计划，接着去由执行器负责这个计划的执行，调用InnoDB存储引擎的接口去执行。

大致会走下图的这个流程

我们就来探索一下这个存储引擎里的架构设计，以及如何基于存储引擎完成一条更新语句的执行

缓冲池

InnoDB存储引擎中有一个非常重要的放在内存里的组件，就是缓冲池(Buffer Pool)，这里面会缓存很多的数据， 以便于以后在查询的时候，万一你要是内存缓冲池里有数据，就可以不用去查磁盘了

所以当我们的InnoDB存储 引擎要执行更新语句的时候 ，比如对“id=1”这一行数据，他其实会先将“id=1”这一行数据看看是否在缓冲池里，如果不在的 话，那么会直接从磁盘里加载到缓冲池里来，而且接着还会对这行记录加独占锁。

因为我们想一下，在我们更新“id=1”这一行数据的时候，肯定是不允许别人同时更新的，所以必须要对这行记录加 独占锁

undo日志文件

如何让你更新的数据可以回滚?

接着下一步，假设“id=1”这行数据的name原来是“周星星”，现在我们要更新为“月伴飞鱼”，那么此时我们得先 把要更新的原来的值“周星星”和“id=1”这些信息，写入到undo日志文件中去。

数据库中，如果我们执行一个更新语句，要是他是在一个事务里的话，那么事 务提交之前我们都是可以对数据进行回滚的，也就是把你更新为“月伴飞鱼”的值回滚到之前的“周星星”去。

所以为了考虑到未来可能要回滚数据的需要，这里会把你更新前的值写入undo日志文件，我们看下图。

更新buffer pool中的缓存数据

这里所谓的更新内存缓冲池里的数据，意思就是把内存里的“id=1”这行数据的name字段修改为“月伴飞鱼”

当我们把要更新的那行记录从磁盘文件加载到缓冲池，同时对他加锁之后，而且还把更新前的旧值写入undo日志文件 之后，我们就可以正式开始更新这行记录了，更新的时候，先是会更新缓冲池中的记录，此时这个数据就是脏数据 了。

那么为什么说此时这行数据就是脏数据了呢?

因为这个时候磁盘上“id=1”这行数据的name字段还是“周星星”，但是内存里这行数据已经被修改了，所以 就会叫他是脏数据。

redo log

接着我们来思考一个问题，按照上图的说明，现在已经把内存里的数据进行了修改，但是磁盘上的数据还没修改

那么此时万一MySQL所在的机器宕机了，必然会导致内存里修改过的数据丢失，这可怎么办呢?这个时候，就必须要把对内存所做的修改写入到一个Redo Log Buffer里去，这也是内存里的一个缓冲区，是用来存 放redo日志的

所谓的redo日志，就是记录下来你对数据做了什么修改，比如对“id=1这行记录修改了name字段的值为“月伴飞鱼”，这 就是一个日志。我们先看下图

这个redo日志其实是用来在MySQL突然宕机的时候，用来恢复你更新过的数据的

提交事务的时候将redo日志写入磁盘中

接着我们想要提交一个事务了，此时就会根据一定的策略把redo日志从redo log buffer里刷入到磁盘文件里去。

此时这个策略是通过innodb_flush_log_at_trx_commit来配置的，他有几个选项。当这个参数的值为0的时候，那么你提交事务的时候，不会把redo log buffer里的数据刷入磁盘文件的，此时可能你都 提交事务了，结果mysql宕机了，然后此时内存里的数据全部丢失。相当于你提交事务成功了，但是由于MySQL突然宕机，导致内存中的数据和redo日志都丢失了

当这个参数的值为1的时候，你提交事务的时候，就必须把redo log从内存刷入到磁盘文件里去，只要事务提交成功，那么redo log就 必然在磁盘里了

那么只要提交事务成功之后，redo日志一定在磁盘文件里，此时你肯定会有一条redo日志说了，“我此时对哪个数据做了一个什么修 改，比如name字段修改为月伴飞鱼了”。

然后哪怕此时buffer pool中更新过的数据还没刷新到磁盘里去，此时内存里的数据是已经更新过的“name=月伴飞鱼”，然后磁盘上的数 据还是没更新过的“name=周星星”。

此时如果说提交事务后处于上图的状态，然后mysql系统突然崩溃了，此时会如何?会丢失数据吗?

肯定不会啊，因为虽然内存里的修改成name=月伴飞鱼的数据会丢失，但是redo日志里已经说了，对某某数据做了修改 name=月伴飞鱼。

所以此时mysql重启之后，他可以根据redo日志去恢复之前做过的修改

最后来看看，如果innodb_flush_log_at_trx_commit参数的值是2呢?

他的意思就是，提交事务的时候，把redo日志写入磁盘文件对应的os cache缓存里去，而不是直接进入磁盘文件，可 能1秒后才会把os cache里的数据写入到磁盘文件里去。

这种模式下，你提交事务之后，redo log可能仅仅停留在os cache内存缓存里，没实际进入磁盘文件，万一此时你要 是机器宕机了，那么os cache里的redo log就会丢失，同样会让你感觉提交事务了，结果数据丢了

三种redo日志刷盘策略到底选择哪一种?

innodb_flush_log_at_trx_commit=0 提交事务的时候，不会将内存中的redo log刷入磁盘

优点，纯内存操作速度快，缺点，redo日志没有落地磁盘，如果提交事务的一瞬间，MySQL宕机，那么如果是修改数据，内存数据没了，磁盘也没来的及更新，就丢失了本次修改操作。

”innodb_flush_log_at_trx_commit=1，提交事务之前一定会将redo log 刷入磁盘

优点，事务提交之前，事务操作log一定刷入磁盘，事务成功，磁盘一定有redo日志，如果事务提交成功，内存修改，磁盘还没有更新，完全可以读取redo日志恢复数据。缺点，写磁盘确实会消耗很多性能，如果是高并发，大量写入，一定会影响写入性能，吞吐量和处理时间都会影响到。

”innodb_flush_log_at_trx_commit=2，将redo日志刷入OS cache，间隔可能一秒写入磁盘。方案鉴于一和二方案之间。

优点，利用OS cache去缓存部分日志，可以提高吞吐量，间隔时间，异步刷入磁盘。缺点，提交事务之后，可能redo日志还在cache中。此时，日志存在丢失的风险。

”三种方案，第一种方案适用于，允许不重要的数据，但是大批量插入的场景，可能丢失，比如一些大批量的任务执行日志上报的数据。

方案二适用于数据不可丢失的插入更新，比如订单，用户等核心数据。

方案三，适用于高并发插入，允许一定数据丢失，但是大部分可靠的场景，比如用户行为日志，APP异常上报等。

一般建议redo日志刷盘策略设置为1，保证事务提交之后，数据绝对不能丢失，MySQL中这个参数默认值为1。

本文转载自微信公众号「月伴飞鱼」

原文链接：https://mp.weixin.qq.com/s/nRd1P5T9Gpy5FvZGmUYFWA。