《MySQL实战45讲》数据库更新语句执行原理

版权声明:本文为博主原创文章,转载请注明出处。 https://blog.csdn.net/libra_ts/article/details/84331438

购买了林晓斌老师的这个课程,此文作为学习笔记

update T set c=c+2 where ID=2

这样一句更新的sql语句执行过程分为以下几个步骤:

  • 根据条件查询ID=2的记录(这里和查询语句的执行步骤是一样的)
  • 执行器执行更新
  • 记录日志redo log(重做日志、物理日志) binlog(归档日志、逻辑日志)

重点来了

redo log

redo log是InnoDB 独有的

先举个栗子来描述下这两个日志的场景

《孔乙己》这篇文章,酒店掌柜有一个粉板,专门用来记录客人的赊账记录。如果赊账的人不多,那么他可以把顾客名和账目写在板上。但如果赊账的人多了,粉板总会有记不下的时候,这个时候掌柜一定还有一个专门记录赊账的账本

如果有人要赊账或者还账的话,掌柜一般有两种做法:

  1. 直接把账本翻出来,把这次赊的账加上去或者扣除掉
  2. 先在粉板上记下这次的账,等打烊以后再把账本翻出来核算

人多的时候掌柜一定会选择第二种方式,因为前者操作实在是太麻烦了。首先,你得找到这个人的赊账总额那条记录。密密麻麻几十页,掌柜要找到那个名字,找到之后再拿出算盘计算,最后再将结果写回到账本上。相比之下,还是先在粉板上记一下方便。你想想,如果掌柜没有粉板的帮助,每次记账都得翻账本,效率很低。

同样,在 MySQL 里也有这个问题,如果每一次的更新操作都需要写进磁盘,然后磁盘也要找到对应的那条记录,然后再更新,整个过程 IO 成本、查找成本都很高。为了解决这个问题,MySQL 的设计者就用了类似酒店掌柜粉板的思路来提升更新效率。

而粉板和账本配合的整个过程,其实就是 MySQL 里经常说到的 WAL 技术,WAL 的全称是 Write-Ahead Logging,它的关键点就是先写日志,再写磁盘,也就是先写粉板,等不忙的时候再写账本

粉板就相当于redo log
有了 redo log,InnoDB 就可以保证即使数据库发生异常重启,之前提交的记录都不会丢失,这个能力称为crash-safe(崩溃安全机制)

要理解 crash-safe 这个概念,可以想想上面赊账记录的例子。只要赊账记录记在了粉板上或写在了账本上,之后即使掌柜忘记了,比如突然停业几天,恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。


binlog

binlog是Server层的日志

| mysql-bin.000001 | 1500 | Query | 195 | 1600 | use `hadoop`; DELETE FROM `user` WHERE (`id`='4') |  
| mysql-bin.000001 | 1600 | Query | 195 | 1700 | use `hadoop`; DELETE FROM `user` WHERE (`id`='5') |  
| mysql-bin.000001 | 1700 | Query | 195 | 1800 | use `hadoop`; DELETE FROM `user` WHERE (`id`='6') |  

我想你肯定会问,为什么会有两份日志呢?
最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM,但是 MyISAM 没有 crash-safe 的能力,binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的,既然只依靠 binlog 是没有 crash-safe 能力的,所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

redo log 和 binlog有以下三点不同

  • 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)状态,更新完成。

重点总结:

  • 两阶段提交更新操作记录到 redo log 里面,此时 redo log 处于 prepare 状态。然后告知执行器执行完成了,随时可以提交事务。执行器生成这个操作的 binlog,并把 binlog 写入磁盘。最后调用提交事务接口,把写入的 redo log 改成提交状态
  • 1.prepare阶段
    2.写binlog
    3.commit
    当在2之前崩溃时
    重启恢复:后发现没有commit,回滚。
    备份恢复:没有binlog 。一致
    当在3之前崩溃时
    重启恢复:虽没有commit,但满足prepare和binlog完整,所以重启后会自动commit。
    备份恢复:有binlog. 一致
  • redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候,表示每次事务的 redo log 都直接持久化到磁盘。这个参数建议设置成 1,这样可以保证 MySQL 异常重启之后数据不丢失
  • sync_binlog 这个参数设置成 1 的时候,表示每次事务的 binlog 都持久化到磁盘。这个参数也建议设置成 1,这样可以保证 MySQL 异常重启之后 binlog 不丢失
展开阅读全文

没有更多推荐了,返回首页