SQL更新语句

前言：

上一篇文章讲解了SQL查询语句执行的过程，并介绍了执行过程中涉及的处理模块。回顾一下，一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块，最后到达存储引擎。

一、执行过程

在SQL查询语句执行的过程中，我们学习过 SQL 语句基本的执行链路，这里我再把那张图拿过来，你也可以先简单看看这个图回顾下。首先，可以确定的说，查询语句的那一套流程，更新语句也是同样会走一遍。

你执行语句前要先连接数据库，这是连接器的工作。

使用数据库的第一步就是先连接到这个数据库上，这时候作为接待的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令：

mysql mysql -h主机地址 -u用户名 －p

输完命令之后，你就需要在交互对话里面输入密码。虽然密码也可以直接跟在 -p 后面写在命令行中，但这样可能会导致你的密码泄露，不建议直接跟着输入。

下图都是我的电脑操作（mac+ mamp）。

如果输入账号或密码错误会提示（1045）：

回归正题，我们还是从一个表的一条更新语句说起，下面是这个表的创建语句，这个表有一个主键 id 和一个字符串类型的字段 name 以及一个 decimal类型的字段score ：

CREATE TABLE `s_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` varchar(255) NOT NULL DEFAULT '' COMMENT '名字',
  `score` decimal(5,2) NOT NULL DEFAULT '0.00' COMMENT '分数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生分数表';

并加入几条测试数据：

INSERT INTO `test`.`s_info` ( `name`, `score`) VALUES ( '张一', 80.00), ( '赵二', 90.00),( '王三', 100.00), ( '李四', 98.00),( '马五', 87.00);

结果如下：

如果要将 id=4 这一行的值加 1，SQL 语句就会这么写：

update s_info set score=score+1 where id = 4;

执行结果如图：

在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表上所有缓存结果都清空。这也就是我们一般不建议使用查询缓存的原因。

分析器会通过词法和语法解析知道这是一条更新语句。

优化器决定要使用 ID 这个索引。

执行器负责具体执行，找到这一行，然后更新。

与查询流程不一样的是，更新流程还涉及两个重要的日志模块，redo log（重做日志，物理日志）和 binlog（归档日志，逻辑日志）。如果接触过 MySQL，这两个词肯定是绕不过的。

二、日志模块

在说日志模块前，先说一下什么是物理日志和逻辑日志。

物理日志：通俗的讲，就是只有"我"自己可以使用，别人无法共享我的"物理格式，私有化。

逻辑日志：可以给别的引擎使用，是所有引擎共享的。

1、物理日志redo log

redo log是 InnoDB 引擎特有的日志，又被称为重写日志， 用来记录事务操作的变化，记录的是数据修改之后的值，不管事务提交是否成功，都会被记录下来。它让MySQL拥有了崩溃恢复能力。

比如MySQL实例挂了或宕机了，重启时，InnoDB存储引擎会使用redo log恢复数据，保证数据的持久性与完整性。

以常见的古代酒馆掌柜记账举例：

酒馆掌柜有一个黑板，赊账的人少时就记在黑板上如果赊账人多的话，由于黑板的空间大小有限，所以他又需要额外准备一本账本，专门记录所有赊账的账目。 如果有人要赊账的话，一般老板有两种做法：

（1）、打开账本，找到赊账人的记录，进行追加赊账记录； （2）、先把赊账人的记录写到黑板上，待客流量少的时刻，再更新到赊账账目上。 如果掌柜使用第一种方法的话，每当有人要赊账的话，首先他需要打开厚厚的账本，一页一页查找该顾客的姓名，然后进行登记。你想一下，如果赊账的人不多，掌柜找赊账人的记录轻松点，如果赊账本有好几本的话，一本一本的找，掌柜看的都头疼。

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

而黑板和账本配合的整个过程，其实就是 MySQL 里经常说到的 WAL （Write Ahead Logging）技术,它的关键点就是先写日志，再写磁盘，

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（黑板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，这就像打烊以后掌柜做的事。

如果某天赊账的特别多，粉板写满了，又怎么办呢？这个时候掌柜只好放下手中的活儿，把黑板中的一部分赊账记录更新到账本中，然后把这些记录从黑板上擦掉，为记新账腾出空间。

与此类似，InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“黑板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，

如下图所示：

write pos是当前记录的位置，一边写一边后移，写到4号文件末尾就回到1号文件开头。check point是当前要把记录写入到数据文件的位置，也是后移并且循环的。

如果和上面老板黑板场景结合起来描述的话，write pos就是老板在黑板上顺序写入赊账人记录位置，对于mysql来说，write pos后移；而check point就是老板把黑板上记录写入到赊账本上的位置，当老板写入到赊账本上后，就会把粉板上该记录擦除掉，对于mysql来说，check point后移。

write pos 和 checkpoint 之间的是“黑板”上还空着的部分，可以用来记录新的操作。如果 write pos 追上 check point，表示“黑板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 check kpoint 推进一下。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

redo log的使用场景

用于系统奔溃恢复。

redolog配置

（1）、缓存大小

innodb_log_buffer_size 默认大小 16MB。 查看相关配置sql：

SHOW GLOBAL VARIABLES LIKE '%innodb_log%';

结果如图所示：

（2）、刷盘策略

提交事物写入磁盘中，会根据这个配置的策略进行同步。

• 0 提交事物的时候不会把redo log buffer 里的数据刷入磁盘。
• 1 提交事物的时候，必须把日志刷入磁盘中，可以严格保证数据不丢失 （默认且推荐策略）。
• 2 提交事物的时候，先把日志刷入磁盘文件对应的 os cache 缓存里，隔一段时间再把数据刷入磁盘。

查看相关参数SQL：

SHOW GLOBAL VARIABLES LIKE '%sync_binlog%';

2、逻辑日志binlog

MySQL 整体来看，其实就有两块：一块是 Server层，它主要做的是 MySQL 功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。 redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。

为什么会有两份日志呢？

最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

bin log是mysql数据库service层的，是所有存储引擎共享的日志模块，它用于记录数据库执行的写入性操作，也就是在事务commit阶段进行记录，以二进制的形式保存于磁盘中。

这两种日志有以下不同：

对这两个日志的有了概念性理解，我们再来看执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程。

• （1）、执行器先找引擎取 ID=4 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=4 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回，并且对这行记录加独占锁，把更新行记录的旧值写入 undo log（以便回滚）。
• （2）、执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
• （3）、引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare状态。然后告知执行器执行完成了，随时可以提交事务。
• （4）、执行器生成这个操作的 binlog，再按策略刷到 binlog文件（磁盘中）。
• （5）、执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

update 语句的执行流程图如下，其中图中黄色框表示是在 InnoDB 内部执行的，绿色框表示是在执行器中执行的。

重点看下最后三步，将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。

两阶段提交

为什么必须有“两阶段提交”呢？这是为了让两份日志之间的逻辑一致。

binlog 会记录所有的逻辑操作，并且是采用“追加写”的形式。如果你的 DBA 承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有 binlog，同时系统会定期做整库备份。

当需要恢复到指定的某一秒时，比如某天下午点发现上午11点有一次误删表，需要找回数据，那我们可以这么做：

首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库； 然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。 这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。

为什么日志需要“两阶段提交”？

由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。

仍然用前面的 update 语句来做例子。假设当前 ID=4 的行，字段 score 的值是 98.00 ，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

(1) 、先写 redo log 后写 binlog。

假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 score 的值是 99.00。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 的值就是 98.00，与原库的值不同。

（2）、先写 binlog 后写 redo log

如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 score 的值是 98.00。但是 binlog 里面已经记录了“把 score 从 98.00 改成 99.00”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 score 的值就是 99.00，与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

疑问：这样的操作概率是不是很低，平时也没有什么动不动就需要恢复临时库的场景呀？

不只是误操作后需要用这个过程来恢复数据。当你需要扩容的时候，也就是需要再多搭建一些备库来增加系统的读能力的时候，现在常见的做法也是用全量备份加上应用 binlog 来实现的，这个“不一致”就会导致你的线上出现主从数据库不一致的情况。

简单来说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

对于InnoDB引擎而言，在每次事务commit提交时才会记录binlog日志，此时记录仍然在内存中，那么什么时候存储到磁盘中呢？mysql通过 sync_binlog 参数控制binlog刷盘时机，取值范围：0～N：

• 0：不去强求，由系统自行判断何时写入磁盘；
• 1：每次事务commit的时候都要将bin log写入磁盘；
• N：每N个事务commit，才会将bin log写入磁盘；

备注：该值默认为0，采用操作系统机制进行缓冲数据同步。 sync_binlog 参数建议设置为1，这样每次事务commit时就会把bin log写入磁盘中，这样也可以保证mysql异常重启之后bin log日志不会丢失。

 SHOW GLOBAL VARIABLES LIKE '%innodb_flush%';

如图所示：

binlog使用场景

在实际场景中， bin log 的主要场景有两点，一点是主从复制，另一点是数据恢复。

• （1）、主从复制：在master端开启 bin log ，然后将 binlog 发送给各个slaver端，slaver端读取 binlog 日志，从而使得主从数据库中数据一致。
• （2）、数据恢复：通过 binlog 获取想要恢复的时间段数据