小伙伴想精准查找自己想看的MySQL文章?喏 → MySQL江湖路 | 专栏目录
??我有一个老朋友,我们叫他熊猫。发际线及将触碰到后脑勺,大框金丝眼镜也掩盖不住那黝黑的眼圈,显得格外的“程序员”;穿着也非常不拘一格,上半身是衬衣西服,下半身是牛仔裤配拖鞋~
??我和熊猫的感情很好,毕业后他去了上海而我开始北漂,但每次过节回老家我俩都会和朋友们一起吃饭,这次回家过年也不例外。这次,我们朋友几个去了枣庄出名的“好再来土菜馆”,点了特色的枣庄辣子鸡,超大盘那种。
??对了,去年我在写《听我讲完GET、POST原理,面试官给我倒了杯卡布奇诺》文章时我们去的那家“小板凳酱骨头”,已经被我们干黄了,哈哈哈!
??这次在饭桌上,我们聊到了疫情期间我们几个积极参加各大厂免费面试的一些有趣场景。熊猫说在面试一家数据存储的大厂时,深挖了一个MySQL问题,redo log
和 binlog
,很有意思。面试官也很客气,总有种莫名的亲切感。说着,翘起二郎腿喊道:“老板,再来一箱青岛”!我们几个都知道,熊猫又要开始回放了~~
以下是熊猫和面试官马经理的对话。
熊猫:马…小马哥好!
面试官:额…你好,小李啊,看你简历写着精通MySQL事务、日志原理,你认为精通应该是啥水平呢?
熊猫(老脸一红):emmm…精通就是,比面试官知道的多一点呗。。
面试官:(嗯。。。。是个老实人,我喜欢!)
面试官:那你先给我讲讲MySQL里有哪些比较重要的日志吧?
熊猫:(我只看了redo log、binlog面试题,咋整,多说会不会给自己挖坑?记得鲁迅大爷说过:别啥JB都说,最后坑自己)
熊猫:嗯。。其实MySQL中的日志有很多,但日常接触最多的是重做日志(redo log)、归档日志(binlog)这两种,当然还有回滚日志(undo log)等等,我接触的少一些。
MySQL日志主要包括六种:
- 重做日志(redo log)
- 回滚日志(undo log)
- 归档日志(binlog)
- 错误日志(errorlog)
- 慢查询日志(slow query log)
- 一般查询日志(general log)
- 中继日志(relay log)
面试官:好,那你先说一下你对 redo log
日志的理解吧。
熊猫:记得小时候看《武林外传》,吕秀才柜台下面有一个小黑板,当时不知道是干啥的,后来发现是专门用来记录客人的赊账记录。如果赊账的人不多,那么他可以把顾客名和账目写在板上。但如果赊账的人多了,小黑板没地儿了,这个时秀才一定还有一个专门记录赊账的账本。如果有人要赊账或者还账的话,秀才一般有两种做法:
??在生意火爆时,秀才肯定会选择后者,因为直接记账本太麻烦了。得先翻出赊账人“老钱”那条记录,账本密密麻麻几十页,找到后再拿出算盘计算,最后更新到账本上。想想都麻烦。相比之下,还是先在小黑板上记一下方便。你想想,如果秀才没有小黑板的帮助,每次记账都得翻账本,效率是不是低得让人难以忍受?还有时间泡小郭?想无双?
??同样,在 MySQL 里也有这个问题,如果每一次的更新操作都需要写进磁盘,然后磁盘也要找到对应的那条记录,然后再更新,整个过程 IO 成本、查找成本都很高。为了解决这个问题,MySQL 的设计者就用了类似秀才记小黑板的思路来提升更新效率。
??而小黑板和账本配合的过程,其实就是 MySQL 里经常说到的 WAL 技术
。
面试官:小伙子你这思路很奇特呀!那你再详细跟我说一下,啥是WAL技术?
熊猫:(小马哥对我有意思啊!)
WAL 的全称是 Write-Ahead Logging,它的关键点就是先写日志,再写磁盘,也就是先写小黑板,等不忙的时候再写账本。
??具体来说,当有一条update语句要执行的时候,InnoDB 引擎就会先把记录写到 redo log(小黑板)里面,并更新内存,这个时候更新就算完成了。
??同时,InnoDB引擎会在适当的时候,将这个操作记录更新到磁盘里面,而这个更新往往是在系统比较空闲的时候做,这就像打烊以后秀才做的事。如果今天赊账的不多,掌柜可以等打烊后再整理。但如果某天赊账的特别多,小黑板写满了咋办?这个时候秀才只好叫无双帮忙干自己的活儿,抓紧把小黑板中的一部分赊账记录更新到账本中,然后把这些记录从小黑板上擦掉,为记新账腾出空间。
??与此类似,InnoDB 的 redo log 是固定大小的,比如可以配置为一组 4 个文件,每个文件的大小是 100MB,那么这块“小黑板”总共就可以记录 400MB 的操作记录。从头开始写,写到末尾就又回到开头循环写,如下面这个图所示。
??write position 和 checkpoint 之间的是“小黑板”上还空着的部分,可以用来记录新的操作。
??如果 write pos 追上 checkpoint,表示“小黑板”满了,这时候不能再执行新的更新,得停下来先擦掉一些记录,把 checkpoint 推进一下。
??有了 redo log,InnoDB 就可以保证即使数据库发生异常重启,之前提交的记录都不会丢失,这个能力称为 crash-safe
。
crash-safe:
??可以对照前面赊账记录的例子。只要赊账记录记在了小黑板上或写在了账本上,即使秀才突然被老邢抓走几天,回来后依然可以通过账本和小黑板上的数据明确赊账账目。就是维护数据的持久性。
??本质上说,crash-safe 就是落盘处理,将数据存储到了磁盘上,断电重启也不会丢失。
面试官:不错,你这理解虽说听的我一愣一愣,但是话糙理不糙,确实说出了redo log的原理。那你再说说对binlog日志的理解吧。
熊猫: 嘿嘿,谢谢马经理夸奖。MySQL 其实是分为 server层 和 引擎层两部分。
??上面我们聊到的“小黑板” redo log 是 InnoDB 引擎特有的日志
,而 Server 层也有自己的日志,称为binlog(归档日志),其实就是用来恢复数据用的。
面试官:那MySQL为啥要有redo log 和 binlog两个日志呢?只留一个不香么?
熊猫:因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM,但是 MyISAM 没有 crash-safe 的能力,而 binlog 日志只用于归档。
??InnoDB 是另一个公司以插件形式引入 MySQL 的。我们知道,只依靠 binlog 是没有 crash-safe 能力的,所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。
面试官:那这两个日志主要有哪些区别?
熊猫:emmm…主要有几下几种区别:
面试官:好,基于你上面说的,那比如下面这条SQL,你来描述一下在MySQL内部的执行流程吧
update T set money = money + 500 where username = '陈哈哈';
熊猫:
两阶段提交
。面试官:那为啥必须要分成prepare和commit两个阶段进行提交呢?一块儿提交他不爽么。
熊猫:我举个现实生活中的栗子吧,一个完整的交易过程我认为应该这样:
比如你来我的小超市里买一瓶可乐:
事务实际是否完成的根本依据
,处于待标记commit阶段)到这里,代表一笔交易结束
。??可见,如果收钱之前(prepare阶段,步骤3)交易被打断,回过头来处理此次交易,发现只有记了小黑板但没有收钱,则交易失败,删掉小黑板上的记录(回滚);
??如果收了钱后(commit阶段 或 待commit阶段,步骤4 || 5)交易被打断,然后回过头发现系统上有记录(prepare)而且钱箱有本次收入(bin log),则说明本次交易有效,补充修改commit状态,更新到库存中。
??以上是人话,咱们再来看看MySQL层面的专业解释:
??这里我们用反证法来进行解释为何需要两阶段提交。由于 redo log 和 binlog 是两个独立的逻辑,如果不用两阶段提交,要么就是先写完 redo log 再写 binlog,或者采用反过来的顺序。我们看看这两种方式会有什么问题。
??仍然用前面的 update 语句来做例子。假设当前 username = ‘陈哈哈’ 的行,账户余额字段 money 的值是 100,再假设执行 update 语句过程中在写完第一个日志后,第二个日志还没有写完期间发生了 crash(异常宕机),会出现什么情况呢?
依旧以这条SQL为例:
update T set money = 0 + 500 where username = '陈哈哈';
??假设在 redo log 写完,binlog 还没有写完的时候,MySQL 进程异常重启。由于我们前面说过的,redo log 写完之后,系统即使崩溃,仍然能够把数据恢复回来,所以恢复后这一行 money 的值是 money + 500。
??但是由于 binlog 没写完就 crash 了,这时候 binlog 里面就没有记录这个语句。因此,之后备份日志的时候,存起来的 binlog 里面就没有这条语句。
??然后你会发现,如果需要用这个 binlog 来恢复临时库的话,由于这个语句的 binlog 丢失,这个临时库就会少了这一次更新,恢复出来的这一行 money 的值就是 0,与原库的值不同。
??如果在 binlog 写完之后 crash,由于 redo log 还没写,崩溃恢复以后这个事务无效,用户余额 money 的值应当是 0。但是 binlog 里面已经记录了“把 money 从 0 改成 500 这个日志。所以,在之后用 binlog 来恢复的时候就多了一个事务出来,恢复出来的这一行 money 的值就是 500,与原库的值不同。
??可以看到,如果不使用“两阶段提交”,那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。
??简单说,redo log 和 binlog 都可以用于表示事务的提交状态,而两阶段提交就是让这两个状态保持逻辑上的一致。
保证事务成功,日志必须落盘,这样,数据库crash后,就不会丢失某个事务的数据了
innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候,表示每次事务的 redo log 都直接持久化到磁盘。这样可以保证 MySQL 异常重启之后数据不丢失。
sync_binlog 这个参数设置成 1 的时候,表示每次事务的 binlog 都持久化到磁盘。这样可以保证 MySQL 异常重启之后 binlog 不丢失。
面试官:(老脸一红)
面试官:小李,坦白说我也很久没搞技术了,但我觉得你确实很懂这块儿,为什么?因为你把我都给我干困了。。我很欣赏你吹…讲故事的能力,你,你懂我意思吧。
面试官:小李啊,你等我一会儿。(马经理提着裤子走出了办公室)
熊猫:(尿遁了??不应该啊,我这波操作难道还不香么?难道,他也看了陈哈哈的博客,被识破了?? -_-’’|)
五分钟后HR来了。。
HR:小李,我就说你行吧!!领导很看好你,说你表达思路奇特,很符合这个岗位,并给你点了个赞,让我通知你下周来入职吧。
熊猫:好的好的,看来现在开发对表达能力要求还挺高啊~~
HR:??兄弟不是应聘产品么?
熊猫:😀😀🙃🙃你跟我俩搁这儿扯犊子呢?我应聘的软件开发工程师大哥?
HR:(嗯,看来果然是我打错面试电话了。。冷静冷静,小问题)
HR:好的,那今天就先这样,回去等通知吧😺😺 还有啥问题要问我么?
熊猫:。。。。。
??好了,今天咱们了解了 MySQL 里面最重要的两个日志,即物理日志 redo log 和逻辑日志 binlog。为该讲的内容总结了几个问题, 大家复习的时候可以先尝试回答这些问题检查自己的掌握程度。
急诊室外
妻子跪地为丈夫祈祷
神呐,如果您能听到,请保佑我丈夫平安!
医院的墙壁比教堂听过更多虔诚的祈祷
火车的站台比婚礼见证过更多真挚的拥吻
共勉
不知道各位友友们是否遇到这种情况我遇到很久了一直没有注意如何解决都直接是run...
ASP+Access2000 1.要获取的ID值字段属性必须设为:自动编号(我们假设字段名为rec...
刚开始接触PHP开发,搭建开发环境是第一步,网上下载PhpStorm和PhpStudy软件,怎...
我们通常衡量一个Web系统的吞吐率的指标是QPS(Query Per Second,每秒处理请求...
作者 | 中国农业银行艾明浩、矫宏鹤 责编 | Carol 封图 |? CSDN 下载自视觉中国 ...
前言 本文是橙子出于兴趣爱好对Java官方教程的尝试翻译几乎每日更新感兴趣的朋友...
历史拉链表是一种数据模型,主要是针对数据仓库设计中表存储数据的方式而定义的;...
jsp 显示springmvc modelmap传递的对象 最近在做一个小网站,功能非常基础,决定...
开源数据库架构设计原则 01. 技术选型 选择成熟的平台和技术,同时是最熟悉的,...
JSX 是什么 JSX 是一种 Javascript 的语法扩展,JSX = Javascript + XML,即在 J...