【MySQL 实战】02. 一条SQL 更新语句是如何具体执行的呢？

最新推荐文章于 2024-07-23 23:56:39 发布

置顶冯佳兴

最新推荐文章于 2024-07-23 23:56:39 发布

阅读量285

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/fjxcsdn/article/details/105480772

版权

MySQL 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

我们在 MySQL 实战 01 篇中讲到了一条SQL查询语句是如何执行的？首先经过连接器进行客户端的连接，权限认证等操作，其次先去缓存中查看，如果缓存中命中则直接返回，如果未命中，则进入分析器，分析器对SQL查询语句进行语法语义分析，优化器执行查询计划，选在最佳执行计划，执行器调用存储引擎的接口，返回要查询的结果集。

文章末尾我们讲到了，一条查询语句是这样进行的，那么一条更新语句是如何进行的呢? 更新语句涉及到数据的修改，当数据进行修改时，此时缓存中的数据必须全部清空，这也就意味着缓存对于增删改语句是失效的。更新语句和查询语句一样，同样是先经过连接器，分析器，优化器、执行器这几个步骤，但是更新流程中还多了两个重要的模块，分别是redo log （重做日志）和 bin log（归档日志）。

增删改语句导致数据表中的数据进行了变动，如果是误操作，此时需要恢复到一天或者一周或者两周前的任意一秒的状态该如何实现的呢？此时今天的角色闪亮登场了，它们分别是redo log （重做日志）和 bin log（归档日志）。这两个日志都做了什么，竟然有如此神奇的效果？

redo log (重做日志)

我们假设一个场景，有一张很大的数据表（大约有一亿条数据），我们执行一条更新语句，就需要表中遍历查询到这条语句，然后进行修改，如果更新2条就需要遍历将近2亿次，对磁盘的 I/O 操作的成本是相当高的，且效率非常低。

为了解决这个问题 MySQL 用到了WAL技术，WAL 技术全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

记不记得小时候通常去诊所，诊所医生有一个大大的账本（相当于数据库），因为要连续打针，所以不一次性结账（不止一次更新操作）。此时医生就在旁边的黑板上写上，某某某打了针，花了多少钱。等看病结束，医生在统一将黑板上的记录更新到自己的账本上。我们设想如果打一次针，医生就需要在账本上更新一条打针消费的记录（相当于对磁盘进行频繁的I/O操作）这样效率是特别的低下的。反而如果在这个黑板上记录（黑板就相当于 redo log 日志），效率就会有一定的提升，等病人少的时候，在将病人的看病消费记录更新到自己的账本上面。

此时产生了一个问题，医生做记录的黑板大小容量是有限的，当黑板的记录写满时，此时就需要将黑板上的记录更新到账本上，也就是将redo log 日志中更新到数据表中。
在这里插入图片描述
如果 MySQL数据库异常重启，以前提交的记录也不会丢失，这也就意味着即使老板大脑中忘了给谁看病，当看到黑板上的记录，也能明确给谁看病，花费了多少钱。这个超能级就是 crash-safe。

binlog （归档日志）

我们刚才说到了InnoDB 存储引擎中的redo log日志，它负责存储数据的更新记录。它可以提高更新的效率，那么Server层的 binlog （归档）又做了什么呢？为啥有有了redo log 还需要binog 日志呢？

出生年代不同

redo log （重做日志）是 InnoDB 存储引擎独有的日志，mysql 5.7.7 版本以后，MySQL 默认存储引擎才是 InnoDB ，5.7.7 版本以前默认用的是 MylSAM 存储引擎，且该引擎没有日志功能，没有crash -safe 超能力，但是还需要日志功能，此时server 层才有了binlog 日志，这也就意味着，无论什么存储引擎，都可以使用 binlog （归档日志）。

兼容性

不是所有的企业或者公司用的都是mysql5.7.7 以后的版本，或者用的是InnoDB存储引擎，导致该功能是不可抛弃的。

功能差异

redo log （重做日志）记录的是我做了什么事情，例如在某数据页上面修改了什么修改，它属于物理日志层面，而binlog （归档日志）记录的逻辑日志，例如给某一行字段设为 1.

空间差异

redo log 是循环写的，且空间有限，当黑板记录空间满时，需要更新数据表，清除黑板上的日志。而 binlog 是追加写的，当binlog 文件写满后，切换到另一个binlog 文件继续写。

执行流程

mysql> update T set c=c+1 where ID=2;

需要了解的是，数据库存储是以数据页为基本单位的。一个数据页里面有多行数据记录。
为防止频繁的对磁盘进行 I/O 操作，每次将磁盘中的一个或者多个数据页加入到内存中。

数据库是如何恢复到之前任意一秒时的状态呢？我们就需要对binlog进行日志备份，这个备份是根据数据重要性进行备份的，当然可以一天备份一次，一周备份一次。

上面我们说了，server层的 binlog 日志是不可或缺的，mysql InnoDB 存储引擎中的redo log 是该引擎特有的，有的企业不是使用该存储引擎。所以我们备份都是给binlog 日志进行备份的,所以binlog日志是不可获缺的。当某条数据误改，我们可以找到最近一次的全量备份，从备份的时间点开始，将备份的 binlog 依次取出来，恢复原有数据。

为什么将 redo log 进行prepare 和 commit 两阶段提交呢？
在这里插入图片描述