一条更新语句的执行流程又是怎样的呢?
之前你可能经常听DBA同事说,MySQL可以恢复到半个月内任意一秒的状态,惊叹的同时,你是不是心中也会不免会好奇,这是怎样做到的呢?
我们还是从一个表的一条更新语句说起,下面是这个表的创建语句,这个表有一个主键ID和一个整型字段c
mysql> create table T(ID int primary key, c int);
mysql> update T set c=c+1 where ID=2;
你执行语句前要先连接数据库
,这是连接器
的工作。
前面我们说过,在一个表上有更新
的时候,跟这个表有关的查询缓存会失效
,所以这条语句就会把表T上所有缓存结果都清空
。这也就是我们一般不建议使用查询缓存的原因。
接下来,分析器
会通过词法和语法解析
知道这是一条更新语句
。优化器
决定要使用ID这个索引。然后,执行器负责具体执行,找到这一行,然后更新
。
与查询流程不一样的是,更新流程还涉及两个重要的日志模块
,它们正是我们今天要讨论的主角:redo log
(重做日志)和 binlog
(归档日志)。如果接触MySQL,那这两个词肯定是绕不过的,我后面的内容里也会不断地和你强调。不过话说回来,redo log和binlog在设计上有很多有意思的地方,这些设计思路也可以用到你自己的程序里。
1.重要的日志模块 redo log
不知道你还记不记得《孔乙己》这篇文章,酒店掌柜有一个粉板,专门用来记录客人的赊账记录。如果赊账的人不多,那么他可以把顾客名和账目写在板上。但如果赊账的人多了,粉板总会有记不下的时候,这个时候掌柜一定还有一个专门记录赊账的账本。
如果有人要赊账或者还账的话,掌柜一般有两种做法:
- 一种做法是直接把账本翻出来,把这次赊的账加上去或者扣除掉;
- 另一种做法是先在粉板上记下这次的账,等打烊以后再把账本翻出来核算。
在生意红火柜台很忙时,掌柜一定会选择后者,因为前者操作实在是太麻烦了。首先,你得找到这个人的赊账总额那条记录。你想想,密密麻麻几十页,掌柜要找到那个名字,可能还得带上老花镜慢慢找,找到之后再拿出算盘计算,最后再将结果写回到账本上。
这整个过程想想都麻烦。相比之下,还是先在粉板上记一下方便。你想想,如果掌柜没有粉板的帮助,每次记账都得翻账本,效率是不是低得让人难以忍受?
同样,在MySQL里也有这个问题,如果每一次的更新操作都需要写进磁盘,然后磁盘也要找到对应的那条记录,然后再更新,整个过程IO成本、查找成本都很高。为了解决这个问题,MySQL的设计者就用了类似酒店掌柜粉板的思路来提升更新效率。
而粉板和账本配合的整个过程,其实就是MySQL里经常说到的WAL技术
,WAL的全称是Write-Ahead Logging
,它的关键点就是先写日志,再写磁盘,也就是先写粉板,等不忙的时候再写账本
。
InnoDB 的存储结构图
具体来说,当有一条记录需要更新的时候,InnoDB引擎就会先把记录写到redo log(粉板)里面,并更新内存,这个时候更新就算完成了
。同时,InnoDB引擎会在适当的时候,将这个操作记录更新到磁盘里面,而这个更新往往是在系统比较空闲的时候做,这就像打烊以后掌柜做的事。
1.1 redo log是什么
当数据库对数据做修改的时候,需要把数据页
从磁盘读
到 buffer pool
中,然后在 buffer poo
l 中进行修改。
那么这个时候 buffer pool 中的数据页就与磁盘上的数据页内容不一致,我们称buffer pool
的数据页
为 dirty page 脏数据
。
感觉就像先拷贝一份数据,对拷贝的数据进行修改,修改完毕后再覆盖到原数据
这里也可以看出,所有的更新操作都是现在dirty page
中进行的。
如果这个时候发生非正常的 DB 服务重启,那么这些数据还没在内存,并没有同步到磁盘文件中(注意,同步到磁盘文件是个随机 IO),也就是会发生数据丢失。
如果这个时候,能够在有一个文件,当 buffer pool 中的 dirty page 变更结束后,把相应修改记录记录到这个文件(注意,记录日志是顺序 IO)。
那么当 DB 服务发生 crash 的情况,恢复 DB 的时候,也可以根据这个文件的记录内容,重新应用到磁盘文件,数据保持一致。
这个文件就是redo log
,用于记录数据修改后的记录,顺序记录。
如果今天赊账的不多,掌柜可以等打烊后再整理。但如果某天赊账的特别多,粉板写满了,又怎么办呢?这个时候掌柜只好放下手中的活儿,把粉板中的一部分赊账记录更新到账本中,然后把这些记录从粉板上擦掉,为记新账腾出空间。
与此类似,InnoDB
的redo log
是固定大小
的,以 ib_logfile[number]
命名,并以顺序的方式写入文件文件比如可以配置为一组4个文件,每个文件的大小是1GB,那么这块“粉板”总共就可以记录4GB的操作。从头开始写,写到末尾就又回到开头循环写
,如下面这个图所示。
write pos
是当前记录的位置,一边写一边后移,写到第3号文件末尾后就回到0号文件开头。checkpoint
是当前要擦除的位置,也是往后推移并且循环的,擦除记录前要把记录更新到数据文件。
write pos和checkpoint之间的是“粉板”上还空着的部分,可以用来记录新的操作。如果write pos追上checkpoint,表示“粉板”满了,这时候不能再执行新的更新,得停下来先擦掉一些记录,把checkpoint推进一下。
有了redo log
,InnoDB
就可以保证即使数据库发生异常重启,之前提交的记录都不会丢失,这个能力称为crash-safe
。
要理解crash-safe这个概念,可以想想我们前面赊账记录的例子。只要赊账记录记在了粉板上或写在了账本上,之后即使掌柜忘记了,比如突然停业几天,恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。
1.2 Innodb_log_buffer 日志缓冲区
redo log 有一个缓存区Innodb_log_buffer
,默认大小为 8M,Innodb 存储引擎先将重做日志写入 innodb_log_buffer 中。
2.重要的日志模块 binlog
前面我们讲过,MySQL整体来看,其实就有两块:一块是Server层
,它主要做的是MySQL功能层面
的事情;还有一块是引擎层
,负责存储相关的具体事宜
。上面我们聊到的粉板redo log是InnoDB引擎特有的日志,而Server层也有自己的日志,称为binlog(归档日志)
。
我想你肯定会问,为什么会有两份日志呢?
因为最开始MySQL里并没有InnoDB引擎。MySQL自带的引擎是MyISA,但是MyISAM没有crash-safe的能力,binlog日志只能用于归档。而InnoDB是另一个公司以插件形式引入MySQL的,既然只依靠binlog是没有crash-safe能力的,所以InnoDB使用另外一套日志系统——也就是redo log来实现crash-safe能力。
这两种日志有以下三点不同。
- redo log是InnoDB引擎特有的;binlog是MySQL的Server层实现的,所有引擎都可以使用。
- redo log是物理日志,记录的是“在某个数据页上做了什么修改”;binlog是逻辑日志,记录的是这个语句的原始逻辑,比如“给ID=2这一行的c字段加1 ”.
- redo log是循环写的,空间固定会用完;binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。
有了对这两个日志的概念性理解,我们再来看执行器和InnoDB引擎在执行这个简单的update语句时的内部流程。
-
执行器先找引擎取ID=2这一行。ID是主键,引擎直接用
树搜索
找到这一行。如果ID=2这一行所在的数据页本来就在内存中,就直接返回给执行器;否则,需要先从磁盘读入内存,然后再返回
。 -
执行器拿到引擎给的行数据,把这个值加上1,比如原来是N,现在就是N+1,得到新的一行数据,再调用引擎接口写入这行新数据。
-
引擎将这行新数据
更新到内存
中,同时将这个更新操作记录到redo log
里面,此时redo log处于prepare
状态。然后告知执行器执行完成了,随时可以提交事务。 -
执行器
生成这个操作的binlog
,并把binlog写入磁盘
。 -
执行器调用引擎的
提交事务
接口,引擎把刚刚写入的redo log
改成提交(commit)
状态,更新完成。
这里我给出这个update语句的执行流程图,图中浅色框表示是在InnoDB内部执行的,深色框表示是在执行器中执行的。
你可能注意到了,最后三步看上去有点“绕”,将redo log的写入拆成了两个步骤:prepare和commit
,这就是"两阶段提交"
。
3.两阶段提交
为什么必须有“两阶段提交”呢?这是为了让两份日志之间的逻辑一致。要说明这个问题,我们得从文章开头的那个问题说起:怎样让数据库恢复到半个月内任意一秒的状态?
前面我们说过了,binlog会记录所有的逻辑操作
,并且是采用“追加写”
的形式。如果你的DBA承诺说半个月内可以恢复,那么备份系统中一定会保存最近半个月的所有binlog,同时系统会定期做整库备份。这里的“定期”取决于系统的重要性,可以是一天一备,也可以是一周一备。
当需要恢复到指定的某一秒时,比如某天下午两点发现中午十二点有一次误删表,需要找回数据,那你可以这么做:
- 首先,找到最近的一次全量备份,如果你运气好,可能就是昨天晚上的一个备份,从这个备份恢复到临时库;
- 然后,从备份的时间点开始,将备份的binlog依次取出来,重放到中午误删表之前的那个时刻。
这样你的临时库就跟误删之前的线上库一样了,然后你可以把表数据从临时库取出来,按需要恢复到线上库去。
好了,说完了数据恢复过程,我们回来说说,为什么日志需要“两阶段提交”。这里不妨用反证法来进行解释。
由于redo log和binlog是两个独立的逻辑,如果不用两阶段提交,要么就是先写完redo log再写binlog,或者采用反过来的顺序。我们看看这两种方式会有什么问题。
仍然用前面的update语句来做例子。假设当前ID=2的行,字段c的值是0,再假设执行update语句过程中在写完第一个日志后,第二个日志还没有写完期间发生了crash,会出现什么情况呢?
-
先写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,与原库的值不同
。
可以看到,如果不使用“两阶段提交”,那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致
。
你可能会说,这个概率是不是很低,平时也没有什么动不动就需要恢复临时库的场景呀?
其实不是的,不只是误操作后需要用这个过程来恢复数据。当你需要扩容的时候,也就是需要再多搭建一些备库来增加系统的读能力的时候,现在常见的做法也是用全量备份加上应用binlog来实现的,这个“不一致”就会导致你的线上出现主从数据库不一致的情况。
简单说,redo log和binlog都可以用于表示事务的提交状态,而两阶段提交就是让这两个状态保持逻辑上的一致。
4.小结
今天,我介绍了MySQL里面最重要的两个日志,即物理日志redo log和逻辑日志binlog。
redo log用于保证crash-safe能力。innodb_flush_log_at_trx_commit这个参数设置成1的时候,表示每次事务的redo log都直接持久化到磁盘。这个参数我建议你设置成1(性能最差),这样可以保证MySQL异常重启之后数据不丢失。
sync_binlog
这个参数设置成1的时候,表示每次事务的binlog都持久化到磁盘。这个参数我也建议你设置成1,这样可以保证MySQL异常重启之后binlog不丢失。
MySQL · 引擎特性 · InnoDB Buffer Pool
我还跟你介绍了与MySQL日志系统密切相关的“两阶段提交”。两阶段提交是跨系统维持数据逻辑一致性时常用的一个方案,即使你不做数据库内核开发,日常开发中也有可能会用到。
文章的最后,我给你留一个思考题吧。前面我说到定期全量备份的周期“取决于系统重要性,有的是一天一备,有的是一周一备”。那么在什么场景下,一天一备会比一周一备更有优势呢?或者说,它影响了这个数据库系统的哪个指标?
update 语句的执行过程:
1.首先客户端通过tcp/ip发送一条sql语句到server层的SQL interface
2.SQL interface接到该请求后,先对该条语句进行解析,验证权限是否匹配
3.验证通过以后,分析器会对该语句分析,是否语法有错误等
4.接下来是优化器器生成相应的执行计划,选择最优的执行计划
5.之后会是执行器根据执行计划执行这条语句。在这一步会去open table,如果该table上有MDL,则等待。
如果没有,则加在该表上加短暂的MDL(S)
(如果opend_table太大,表明open_table_cache太小。需要不停的去打开frm文件)
6.进入到引擎层,首先会去innodb_buffer_pool里的data dictionary(元数据信息)得到表信息
7.通过元数据信息,去lock info里查出是否会有相关的锁信息,并把这条update语句需要的
锁信息写入到lock info里(锁这里还有待补充)
8.然后涉及到的老数据通过快照的方式存储到innodb_buffer_pool里的undo page里,并且记录undo log修改的redo
(如果data page里有就直接载入到undo page里,如果没有,则需要去磁盘里取出相应page的数据,载入到undo page里)
9.在innodb_buffer_pool的data page做update操作。并把操作的物理数据页修改记录到redo log buffer里
由于update这个事务会涉及到多个页面的修改,所以redo log buffer里会记录多条页面的修改信息。
因为group commit的原因,这次事务所产生的redo log buffer可能会跟随其它事务一同flush并且sync到磁盘上
10.同时修改的信息,会按照event的格式,记录到binlog_cache中。(这里注意binlog_cache_size是transaction级别的,不是session级别的参数,
一旦commit之后,dump线程会从binlog_cache里把event主动发送给slave的I/O线程)
11.之后把这条sql,需要在二级索引上做的修改,写入到change buffer page,等到下次有其他sql需要读取该二级索引时,再去与二级索引做merge
(随机I/O变为顺序I/O,但是由于现在的磁盘都是SSD,所以对于寻址来说,随机I/O和顺序I/O差距不大)
12.此时update语句已经完成,需要commit或者rollback。这里讨论commit的情况,并且双1
13.commit操作,由于存储引擎层与server层之间采用的是内部XA(保证两个事务的一致性,这里主要保证redo log和binlog的原子性),
所以提交分为prepare阶段与commit阶段
14.prepare阶段,将事务的xid写入,将binlog_cache里的进行flush以及sync操作(大事务的话这步非常耗时)
15.commit阶段,由于之前该事务产生的redo log已经sync到磁盘了。所以这步只是在redo log里标记commit
16.当binlog和redo log都已经落盘以后,如果触发了刷新脏页的操作,先把该脏页复制到doublewrite buffer里,把doublewrite buffer里的刷新到共享表空间,然后才是通过page cleaner线程把脏页写入到磁盘中
老师,你看我的步骤中有什么问题嘛?我感觉第6步那里有点问题,因为第5步已经去open table了,第6步还有没有必要去buffer里查找元数据呢?这元数据是表示的系统的元数据嘛,还是所有表的?谢谢老师指正
2018-11-16 21:16
作者回复
其实在实现上5是调用了6的过程了的,所以是一回事。MySQL server 层和InnoDB层都保存了表结构,所以有书上描述时会拆开说。
参考文章:http://blog.itpub.net/31559358/viewspace-2221403/
本系列文章是学习 丁奇(林晓斌)老师的课程做的总结摘抄,以便自己查看学习。欢迎大家去学习老师的课程。