菜鸟笔记
提升您的技术认知

日志系统:一条更新sql是如何执行的?-ag真人游戏

首先,我们先创建一个表,拥有两个字段,id(主键,整形)和 c(整形)

mysql> create table t(id int primary key,c int);

接着,我们对于id=2的行进行更新

mysql> update t set c=c 1 where id=2;

然后,其实update的基本流程和select的基本流程相似,具体如下:

  1. 用户通过连接器建立起连接
  2. 分析器分析出来这个sql语句是一条更新语句
  3. 优化器决定要使用id这个索引
  4. 执行器找到这一行,进行更新。

而select语句和update语句不同的地方在于,update还会涉及两个重要的日志模块:redo log(重做日志)和 bin log(归档日志)。

小故事
   在孔乙己的故事里,酒店掌柜有一个粉板,经常会在白天用来记录顾客的赊账,然后到晚上空闲的时候
,才会将顾客的赊账记录转移到账本里面去。这是因为如果白天就直接记录到账本里面去,那么会耗费很多
时间,来不及招待顾客,所以就直接先写在粉版上。
   但是粉板有固定的大小,如果当天客户太多,那掌柜在白天就需要将粉板上的内容转移到账本上,这样
后面赊账的顾客才能记录到粉板上。
   此外,如果老板临时有事歇业两天,但是只要赊账记录记录在了粉板或者账本上,恢复生意后依然可以
通过账本和粉板上的数据明确赊账数目

在mysql中,和掌柜面临着同样的问题,如果每一次更新操作都写进磁盘,然后磁盘找到那条对应的记录,然后更新,整个过程io成本很高

因此,mysql中有一个write-ahead logging(wal)技术,它的关键点就是先写日志,再写入磁盘。(相当于先写粉板,再写入账本。)
具体来说,当一条记录需要更新的时候,innodb就会先把记录写道redo log(粉板)里面,并更新内存,这个时候更新就完成了。同时,innodb会在适当的时候,将这个操作记录更新到磁盘里面(写入账本)。

redo log时innodb引擎特有的日志。此外,innodb的redo log是固定大小的,比如可以配置为4个文件,每个文件1gb。从头开始写,写到末尾就循环开始写。如下图所示,write pos代表写入的位置,从logfile0一直写入到logfile3,再循环,即顺时针方向。check point代表当前要擦除的位置,也是顺时针方向。图中绿色部分是可以写入的位置,黄色部分是已经写入内容的位置。当write pos指针追上check point时就代码log已满

最后,因为有了redo log,innodb就可以保证数据库发生异常重启时,之前提交的记录不会丢失,这个能力称为crash-safe。(对比故事中的酒店停业后恢复)

bin log是server层的日志,无论使用什么存储引擎,都是会有bin log的。它也是用来保存用户对于数据库的修改操作的日志。bin log和redo log不太一样,它不是采用循环写入的方式,二是进行不断追加的。此外,bin log没有crash-safe能力,只能进行归档。

数据库的恢复通常就会用到bin log。如果想让数据库恢复到半个月内任意一秒的状态,那么就需要对于半个月前的数据进行一次全量备份,然后再根据bin log依次取出来操作,重放到想要恢复的时刻。

首先,两种日志都是用来记录对于数据库的修改,那么为什么需要两种日志呢?
理由:这主要是因为最开始mysql并没有innodb引擎,默认是使用myisam引擎的,但是myisam没有crash-safe能力,bin log只能用于归档。所以引入innodb的redo log来是实现crash-safe能力。

然后,两种日志的主要区别如下:

  1. redo log是innodb引擎特有的。而bin log是属于mysql的server,所有引擎都可以用。
  2. redo log是物理日志,记录的是"在某个数据页上具体做了什么修改"。例如:t表第100行、第100列从0修改为1.
    bin log是逻辑日志,记录的是这个句子的原始逻辑。例如:给id=2这一行的c字段加1
  3. redo log是循环写的,空间固定会用完。bin log则是采用追加写的方式,写到一定大小后会切换到写一个,不会覆盖之前的。

下面是执行和innodb引擎执行简单的update语句的流程:

  1. 执行引擎先找到id=2这一行,如果这一行本身就在内存中就直接返回。如果不在的话,就从磁盘读取到内存中。
  2. 执行器拿到引擎给的数据,更新这一行数据,再调用引擎接口写入这行数据。
  3. 引擎将这行数据更新到内存中,同时将这个更新操作记录到redo log里面,此时redo log处于prepare状态。然后就告诉执行器完成了,随时可以提交事务。
  4. 执行器生成这个操作的bin log,并把bin log写入到磁盘中
  5. 执行器调用引擎的事务提交接口,引擎把刚刚写入的redo log改成commit状态,更新完成

上面的流程也如下面这个图所示,深色的代表执行器,浅色的代表存储引擎:

从刚刚的执行步骤中可以看到,redo log的操作是分为两个阶段:prepare和commit阶段。那为什么要分为两个阶段呢?

我们假设不分为两个阶段,然后发生了这样的事情:假设存储引擎先写完了redo log,但是接着server在写入bin log时发生了异常重启。因为redo log具有crash-safe功能,所以重启后可以恢复。但是由于bin log没有crash-safe功能,就会丢失一部分日志。

这样就会出现一个问题,存储引擎中记录了这次修改,之前会写入磁盘中。但是server中的bin log没有这次修改。那么在利用bin log进行数据库恢复时,就会丢失一些操作。造成恢复的数据库和原来的数据库不一致的情况。

而两阶段提交中,当redo log进入prepare状态后,会等待bin log成功写入,再进行commit,这样就能保证数据的一致性。相当于是进行一个事务。

redo log的相关参数是innodb_flush_log_at_trx_commit,设置成1时,表示每次事务都持久化到磁盘。(建议这么做)
bin log的相关参数是sync-binlog,设置为1时,表示每次事务的binlog都会持久化到磁盘中。(建议这么做)

网站地图