关键词：

1、日志系统：SQL更新语句的执行

在上篇文章中我们了解了一个查询语句是怎么执行的，并了解了执行过程中涉及到的一些处理模块。一条查询 SQL 的执行过程需要经过连接器、分析器、优化器、执行器等模块，最后到达存储引擎。

一条更新语句的执行流程是怎样的呢？我们还是从一个更新 SQL 说起，如下是一个表的创建语句，表有一个主键 ID 和一个 int 类型字段 num：

CREATE TABLE test(ID int primary key, num int 10);

如果要将 ID 为 1 的数据 num 加 1，SQL 语句如下：

UPDATE test SET num = num + 1 WHERE ID = 1;

首先，可以肯定的说，查询 SQL 语句的流程，更新语句同样会走一遍，如下图

• 执行语句前要先连接数据库

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

• 接下来，分析器会通过分析这条 SQL 了解到这是一条更新语句
• 优化器决定要用索引 ID
• 执行器负责具体执行，找到这一行，然后更新

与查询不同的是，更新涉及了两个重要的日志模块 redo log 和 bin log

1.1 存储引擎层日志：redo log

redo log 使用了 WAL（Write-Ahead-Logging） 技术，也就是先写日志，然后再写磁盘。

redo log 用来保证 crash-safe 能力，innodb_flush_log_at_trx_commit 参数为 1 的时候，每次事务的 redo log 都会持久化到磁盘中

在一条记录需要更新的时候，InnoDB 引擎会先将记录写到 redo log 中，并更新内存，这个时候就已经更新完成了。同时，InnoDB 会在适当时间将 操作记录更新到磁盘中

当然，redo log 也不可能无限制的追加，它的大小是固定的，例如可以配置为一组文件 innodb_log_file_in_group=4, innodb_log_file_size=4294967292，每个文件大小为 1 GB，总共可以记录 4 GB 的操作，从头开始循环写，如下图所示:

write pos 为当前记录的位置，边写边向后移动，写到最后一个文件后就回到第一个文件开头。check point 是当前要擦除的位置，移动顺序与写一致，擦除前需要把记录文件更新到数据文件中。

write pos 和 check point 中间部分可以用来记录新的操作。如果 write 追上了 check 的话，不能再执行新的更新操作，需要先等待擦除掉一些记录。

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

1.2 server 层日志：bin log

redo log 是 InnoDB 引擎特有的日志，server 层的日志是 bin log（归档日志）

sync_binlog 参数设置为 1，每次事务的 bin log 都会持久化到磁盘

1.2.1 两种日志的来源

MySQL 开始是没有 InnoDB 引擎的，自带的引擎是 MyISAM，MyISAM 没有 carsh-safe 的能力，bin log 日志只能用于归档。InnoDB 是另一个公司以插件形式引入 MySQL 的，所以 InnoDB 使用另外一套日志系统 redo log 来实现 crash-safe 能力

1.2.2 update 执行流程

UPDATE test SET num = num + 1 WHERE ID = 1;

SQL 执行流程如下图，浅色框表示在 InnoDB 中执行，深色表示在执行器中执行的

• 首先执行器先找引擎获取 ID = 1 的这行
• 如果 ID = 1 的这行所在的数据页在内存中，直接返回给执行器，否则先从磁盘读取后再返回
• 执行器拿到引擎给的数据，将 这个值加上 1，得到一行新的数据
• 执行刑期调用引擎接口写入这行新的数据
• 引擎将这行数据更新到内存中，并将操作记录到 redo log 中，此时 redo log 处于 prepare 状态
• 引擎告诉执行器执行完成可提交事务
• 引擎生成操作的 bin log，将 bin log 写入磁盘
• 执行器调用引擎的提交事务接口，将写入的 redo log 改为提交（commit）状态，更新完成

1.3 两阶段提交

1.3.1怎么将数据库内数据恢复到某个时间段

bin log 会记录所有的逻辑操作，并且采用追加写的形式，如果要将数据恢复到指定的时间，可以这么做：

• 首先，找到最近的一次全量备份，从这个备份恢复到临时库
• 然后，从备份的时间点开始，将备份的 bin log 依次取出来，重放到想要恢复的那个时间
• 这样临时库和之前时间的库就一样了，然后把这个表数据从临时库取出来，按照需要恢复到线上库即可

1.3.2 为什么日志需要两段式提交

redo log 和 bin log 是两个独立的逻辑，我们来看看如果先写一个再写另一个可能会出现什么问题

SQL 语句如下：

UPDATE test SET num = num + 1 WHERE ID = 1;

先写 redo log 后写 bin log

如果 redo log 写完了，bin log 还没有写完的话，MySQL 进程异常重启，虽然系统崩溃了我们仍然能用 redo log 把数据恢复过来，但是由于 bin log 还没有写就崩了，里面没有记录这个语句，导致备份日志里面没有这个语句。如果我们要用 bin log 来恢复临时库的话，就会少了这一次更新操作，恢复的数据会与源数据库的值不同

先写 bin log 再写 redo log

如果在 bin log 写完了就崩了的话，因为 redo log 还没写，崩溃恢复后这个事务没生效，这一行的值还是原来的值没有变化，但是 bin log 里面已经记录了更新的日志，以后用 bin log 来恢复的话就多了一个事务，恢复的这行与源数据库的值不同。

redo log 和 bin log 都用来记录事务的提交状态，两阶段提交就是为了保持这两个状态的一致。

如果不使用两阶段提交的话，数据库状态就可能与用日志恢复的库状态不一致。我们对数据库进行扩容用 bin log 来进行备库的搭建的时候就会导致主从数据库不一致的情况发生

提交过程中 MySQL 崩溃

• 如果在写入 redo log 的 prepare 阶段崩溃，重启后还没有 commit，回滚。备份恢复没有 bin log
• 如果在写入 bin log 的阶段崩溃，重启后没有 commit，但是 redo log 和 bin log 完整的话，会自动 commit。

1.4 redo log 和 bin log 的不同

1. redo log 是物理日志，是 InnoDB 引擎特有的，bin log 是 MySQL 的 server 层实现的，所有存储引擎均可使用
2. 保障数据库的一致性，必须要保证两份日志的一致，使用两段式提交，必须全部成功或失败，与事务相似。
3. redo log 是物理日志，记录的是 做的什么修改，bin log 是逻辑日志，记录的是 SQL 的原始逻辑
4. redo log 是循环写的，空间固定会用完，bin log 则是追加写入，文件到一定大小后会切换另外一个文件，不会覆盖以前的日志

1.5 undo log

1.5.1 undo log 作用：

提供回滚和多个行版本控制（MVCC）

数据修改的时候不仅记录了 redo log，还有相对应的 undo log，如果某些原因导致事务失败或者回滚了，可以借助 undo log 来回滚

辅助 redo log 实现事务持久性

1.5.2 undo log 做了什么？

和 redo log 不同的是，undo log 是逻辑日志，当修改一条记录的时候，undo log 会记录一条相反的原来数据的 update 记录，删除操作则会记录一条原来数据的 insert 记录

当 rollback 的时候，可以从 undo log 的记录中读取到响应内容并进行回滚。

undo log 采用 segment 方式记录，事务提交时，InnoDB 不会立即删除 undo log，如果隔离级别为 repeatable read （可重复读）时，事务读取的都是开启事务时的最新提交行版本，事务不结束，就不能删除。事务提交时会将事务对应的 undo log 放到删除列表中，未来通过 purge 删除

delete 操作

• delete 删除操作实际上不是直接删除，而是将 删除对象打上一个删除的标志，标记为删除，最后删除操作由 purge 线程完成

update 操作

• update 修改操作如果是主键列的话，会先删除该行再插入一行目标行

• 如果不是主键列，会在 undo log 中直接记录原来的 update 数据行

1.6 数据库备份

数据库备份周期的长短我们需要仔细进行考虑

1. 如果实际数据存量巨大，一天无法备份完成，只能一周一次或者更久
2. 增量很大的话，如果一周备份一次，成功率比较难保障，恢复时长也比较困难，最好一天一备份
3. 业务比较重要并且需要尽快恢复的，增量还不小的，一天备份一次

从头开始搞懂mysql（08）count(*)很慢怎么办(代码片段)

在开发系统的时候，我们可能经常需要计算一个表的行数，我们一般都是用selectcount(*)fromtable来查询，如果系统中记录越来越多，这条SQL也越来越慢。为什么MySQL不直接记录一个总数，每次要查询的时候直接读... 查看详情

从头开始搞懂mysql（03）事务隔离性(代码片段)

1、什么是事务？提到事务，我们肯定不陌生，和数据库打交道的时候，我们肯定会用到事物经典案例就是转账，小孙要给小白转100块钱，现在小孙的卡里只有100块钱。转账过程具体到程序里会有一系列操... 查看详情

从头开始搞懂mysql（05）行锁表锁全局锁(代码片段)

1、行锁行锁是针对数据表中行记录的锁，MySQL的行锁是在引擎层实现的，并不是所有的引擎都支持行锁，比如MyISAM就不支持，InnoDB支持行锁，避免了并发控制时使用表锁1.1两阶段锁在InnoDB事务中，行锁是在... 查看详情

mysql之sql语句执行过程(代码片段)

...SQL的执行命令之后底层是如何执行的呢，从这篇文章开始我们开始揭开MySQL底层执行过程的面纱。首选创建表createtab 查看详情

mysql之sql语句执行过程(代码片段)

...SQL的执行命令之后底层是如何执行的呢，从这篇文章开始我们开始揭开MySQL底层执行过程的面纱。首选创建表createtab 查看详情

从头开始搞懂mysql（04）索引(代码片段)

...下，我们想要找到一本书里面的内容，可能需要从头开始翻很久才能找到，有了目录之后，我们才能轻易找到想要的内容1.1常见索引结构哈希哈希是一种以（key-value）键值对存储的数据结构，哈希结构... 查看详情

一条查询sql是如何执行的(代码片段)

@toc执行如下SQL,我们看到的只是输入一条语句，返回一个结果，却不知道这条语句在MySQL内部的执行过程。select*fromwhereid=1;上图给出的是MySQL的基本架构示意图，从中你可以清楚地看到SQL语句在MySQL的各个功能模块中的执行过程。... 查看详情

mysql之sql语句执行过程(代码片段)

...SQL的执行命令之后底层是如何执行的呢，从这篇文章开始我们开始揭开MySQL底层执行过程的面纱。首选创建表createtableuser_info( idintnotnullprimarykey,usernamevarchar(16)notnull,cityvarchar(16)notnull,salaryintnotnulldefault0,createtimetimestampnotnulldefaul... 查看详情

一条sql查询语句是如何执行的(代码片段)

一条SQL查询语句是如何执行的下面是MySql的基本架构示意图，从图中可以清楚地看到SQL语句在MySQL的各个功能模块中的执行过程。大体来讲，MySQL可以分为Server层和存储引擎层两部分。Server层Server层包括连接器、查询缓存、分析器... 查看详情

mysql一条sql的执行过程详解(代码片段)

...交道，一天能写上几十条SQL语句，但你知道我们的系统是如何和数据库交互的吗？MySQL如何帮我们存储数据、又是如何帮我们管理事务？....是不是感觉真的除了写几个「select*fromdual」外基本脑子一片空白？这篇文章就将带你走进... 查看详情

如何从头开始获得更小的工具链？

】如何从头开始获得更小的工具链？【英文标题】：Howtogetasmallertoolchainfromscratch?【发布时间】：2017-06-2017:32:49【问题描述】：我使用gcc-6.3.0、musl-1.1.16和binutils-2.27从头开始​​为MIPS24kc(dragino)目标平台构建了一个自定义工具链。... 查看详情

201|基础架构：一条sql查询语句是如何执行的？(代码片段)

你好，我是林晓斌。这是专栏的第一篇文章，我想来跟你聊聊MySQL的基础架构。我们经常说，看一个事儿千万不要直接陷入细节里，你应该先鸟瞰其全貌，这样能够帮助你从高维度理解问题。同样，对于MySQL的学习也是这样。平... 查看详情

mysql数据库详解一条sql更新语句是如何执行的？(代码片段)

?前面我们系统了解了一个查询语句的执行流程，并介绍了执行过程中涉及的处理模块。相信你还记得，一条查询语句的执行过程一般是经过连接器、分析器、优化器、执行器等功能模块，最后到达存储引擎。那么，一条更新语... 查看详情

输入的查询sql语句，是如何执行的？

...的执行过程。本文分享自华为云社区《​​一条查询SQL是如何执行的​​》，作者：共饮一杯无。执行如下SQL，我们看到的只是输入一条语句，返回一个结果，却不知道这条语句在MySQL内部的执行过程。select*fromwhereid=1;上图给出... 查看详情

一条sql语句是如何执行的

一条SQL语句是如何执行的SQL查询语句select*fromuserwhereID=10;MySQL的基本架构可以分为Server层和存储引擎两部分。Server层又包含连接器、（查询缓存）、分析器、优化器和执行器。**连接器：**连接器负责和客户端建立连接、获取权限 查看详情

一条sql查询语句是如何执行的？(代码片段)

MySQL是典型的C/S架构（客户端/服务器架构），客户端进程向服务端进程发送一段文本（MySQL指令），服务器进程进行语句处理然后返回执行结果。问题来了。服务器进程对客户端发送的请求究竟做了什么处... 查看详情

mysql之系列

MySQL之如何在Linux环境安装配置。MySQL之零碎知识点。MySQL之常见面试题分析。01基础架构：一条SQL查询语句是如何执行的。02日志系统一条SQL更新语句是如何执行的？。03事务隔离：为什么你改了我还看不见？。04MyS... 查看详情

SQL QUERY - 从头开始​​过滤日期

】SQLQUERY-从头开始​​过滤日期【英文标题】：SQLQUERY-Filterdatefromthebeginning【发布时间】：2018-11-1305:23:33【问题描述】：如何编写将前几天/几年的金额相加的SQL查询。从一开始就喜欢。场景我想计算商店从开业那天起的累计销售... 查看详情