🥂上一篇文章说完MySQL的事务和锁了，这次来详细介绍一下在MySQL中一条更新语句的详细执行流程 (本文无特殊说明均是采用Innodb存储引擎)。

前期准备

⭐⭐首先创建一张表，然后插入三条数据：

CREATE TABLE T(
	ID int(11) NOT NULL AUTO_INCREMENT,
	c int(11) NOT NULL,
	PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表';

INSERT INTO T(c) VALUES (1), (2), (3);

让后执行更新操作：

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

在说更新操作前，大家先来看一下sql语句在MySQL中的执行流程~

🍬SQL语句的执行过程

👩🏻‍🏫如图所示：MySQL数据库主要分为两个层级：服务层和存储引擎层服务层：server层包括连接器、查询缓存、分析器、优化器、执行器，包括大多数MySQL中的核心功能所有跨存储引擎的功能也在这一层实现，包括 存储过程、触发器、视图等。 存储引擎层：存储引擎层包括MySQL常见的存储引擎，包括MyISAM、InnoDB和Memory等，最常用的是InnoDB，也是现在MySQL的默认存储引擎。

server层中的组件介绍✨✨✨

1. 连接器： 需要MySQL客户端登录，需要一个 连接器 来连接用户和MySQL数据库，“mysql -u 用户名 -p 密码” 进行MySQL登录，在完成 TCP握手 后，连接器会根据输入的用户名和密码验证登录身份。
2. 查询缓存： MySQL在得到一个执行请求后，会首先去 查询缓存 中查找，是否执行过这条SQL语句，之前执行过得语句以及结果会以 key-value对的形式，放在内存中。key是查询语句，value是查询的结果。如果通过key能够查找到这条SQL语句，直接返回SQL的执行结果。若不存在缓存中，就会继续后面的执行阶段。执行完成后，执行结果就会被放入查询缓存中。优点是效率高。但是查询缓存不建议使用， 因为在MySQL中对某张表进行了更新操作，那么所有的查询缓存就会失效，对于更新频繁的数据库来说，查询缓存的命中率很低。需要注意：在MySQL8.0版本，查询缓存功能就删除了，不存在查询缓存的功能了
3. 分析器: 分为词法分析和语法分析
   • 词法分析： 首先，MySQL会根据SQL语句进行解析，分析器会先做 词法分析，你写的SQL就是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串是什么，代表什么。
   • 语法分析： 然后进行 语法分析， 根据词法分析的结果，语法分析器会根据语法规则，判断输入的这个SQL语句是否满足MySQL语法。如果SQL语句不正确，就提示：You have an error in your SQL suntax
4. 优化器： 经过分析器分析后，SQL就合法了，但在执行之前，还需要进行优化器的处理，优化器会判断使用了哪种索引，使用哪种连接，优化器的作用 就是确定效率最高的执行方案。
5. 执行器： 在执行阶段，MySQL首先会判断有没有执行语句的权限，若无权限，返回没有权限的错误；若有权限，就打开表继续执行。打开表时，执行器会根据标的引擎定义，去使用该引擎提供的接口，对于有索引的表，执行的逻辑类似。

了解完SQL语句的执行流程我们接下来详细分析一下上面update T set c=c+1 where ID=2;是如何执行的。

🏅Update语句分析

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

在执行update更新操作的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表 T 上所有缓存结果都清空。接下来，分析器会经过语法分析和词法分析，知道了这是一条更新语句后，优化器决定要使用哪一个索引，然后执行器负责具体的执行，先找到这一行，然后做更新。

按照我们平常的思路，就是 找出这条记录，把它的值改好，保存就OK了 。但我们追究一下细节，由于涉及到修改数据，所以涉及到日志了。更新操作涉及到两个重要的日志模块。redo log(重做日志)，bin log(归档日志)。MySQL中的这两个日志也是必学的。

redo log(重做日志)🍦

• 在 MySQL 里，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。
  MySQL里使用WAL(预写式日志)技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是 先写日志，再写磁盘。
• 具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。
• InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写。

听完上面对redo log日志的介绍后，小伙伴们可能会问：redo log日志存储在哪？， 数据库信息保存在磁盘上，redo log日志也保存在磁盘上，为什么要先写到redo log中再写到数据库中呢？，redo log日志如果存满数据了怎么办？等等。接下来就解答一下这些疑问。

redo log存储在哪里？🍦

InnoDB引擎先把记录写到redo log 中，redo log 在哪，它也是在磁盘上，这也是一个写磁盘的过程， 但是与更新过程不一样的是，更新过程是在磁盘上随机IO，费时。 而写redo log 是在磁盘上顺序IO。效率要高。

redo log 空间是固定，那它会不会用完呢？🍹

首先不用担心 redo log 会用完空间，因为它是循环利用的。例如 redo log 日志配置为一组4个文件，每个文件分别为1G。它写的流程如下图：

简单总结一下: redo log日志是Innodb存储引擎特有的机制，可以用来应对异常恢复，Crash-safe，redo可以保证mysql异常重启时，将未提交的事务回滚，已提交的事务安全落库。

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

binlog(归档日志)🙇🏻‍♀️

redo log是innoDB 引擎特有的日志。而binlog是mysql server层的日志。

其实bin log日志出现的时间比redo log早，因为最开始MySQL是没有InnoDB存储引擎的，5.5之前是MyISAM。但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

redo log和bin log的总结📍:

• redo log是为了保证innoDB引擎的crash-safe能力，也就是说在mysql异常宕机重启的时候，之前提交的事务可以保证不丢失；(因为成功提交的事务肯定是写入了redo log,可以从redo log恢复)
• bin log是归档日志，将每个更新操作都追加到日志中。这样当需要将日志恢复到某个时间点的时候，就可以根据全量备份+bin log重放实现。 如果没有开启binlog,那么数据只能恢复到全量备份的时间点，而不能恢复到任意时间点。如果连全量备份也没做，mysql宕机，磁盘也坏了，那就很尴尬了。。

redo log和bin log的区别📍:

• redo log 是 InnoDB 引擎特有的；bin log 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；bin log 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
• redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

👀InnoDB引擎部分在执行这个简单的update语句的时候的内部流程

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

🍋手动用begin开启事务，然后执行update语句，再然后执行commit语句，那上面的update更新流程之前 哪些是update语句执行之后做的，哪些是commit语句执行之后做的?

事实上，redo log在内存中有一个redo log buffer,binlog 也有一个binlog cache.所以在手动开启的事务中，你执行sql语句，其实是写到redo log buffer和binlog cache中去的(肯定不可能是直接写磁盘日志，一个是性能差一个是回滚的时候不可能去回滚磁盘日志吧)，然后当你执行commit的时候，首先要将redo log的提交状态游prepare改为commit状态，然后就要把binlog cache刷新到binlog日志(可能也只是flush到操作系统的page cache，这个就看你的mysql配置)，redo log buffer刷新到redo log 日志(刷新时机也是可以配置的)。 如果你回滚的话，就只用把binlog cache和redo log buffer中的数据清除就行了。

🎈在update过程中，mysql突然宕机，会发生什么情况?

1. 如果redolog写入了，处于prepare状态，binlog还没写入，那么宕机重启后，redolog中的这个事务就直接回滚了。
2. 如果redolog写入了，binlog也写入了，但redolog还没有更新为commit状态，那么宕机重启以后，mysql会去检查对应事务在binlog中是否完整。如果是，就提交事务；如果不是，就回滚事务。 (redolog处于prepare状态，binlog完整启动时就提交事务，为啥要这么设计? 主要是因为binlog写入了，那么就会被从库或者用这个binlog恢复出来的库使用，为了数据一致性就采用了这个策略)
   redo log和binlog是通过xid这个字段关联起来的。
   

Mysql的逻辑架构

Mysql的逻辑架构如下所示，整体分为两部分，Server层和存储引擎层。

与存储引擎无关的操作都是在Server层完成的，存储引擎层负责数据的存取。

下面将会按照上图的过程分别介绍每一步的作用，这里以查询一条记录为例。

连接器

这一步主要是管理连接和权限验证。

负责管理客户端的连接，比如mysql -u root -p，就是客户端与连接器之间完成的，连接分为长连接和短连接，建议使用长连接，因为建立连接是相对复杂的一个过程。但是长连接也有优化的空间，即长连接过多，随着执行大的查询操作，会占用较多的内存。

建立连接完成之后，连接器会判断该用户的权限，之后用户的操作都会基于权限来判定是否允许。

分析器

这一步主要是词法分析和语法分析。

词法分析主要是判定用户想做什么，比如select 就是想要查询。

语法分析主要是判定用户输入的SQL是否符合Mysql的语法。

优化器

经过分析器，Mysql已经知道用户想要干什么了，但是对于相同的一条SQL语句，对于Mysql具体实施的时候，可能有多种方法去实现，效率也就不一样。

在优化器这一步，mysql需要判定怎么样执行才是最优的效率。

执行器

这一步主要是操作引擎和返回结果。通过操作存储引擎层来遍历一张数据表，去寻找符合条件的数据，并且返回给客户端。

Mysql执行一条更新语句的过程

与一条SQL查询语句相同的是，在Mysql中同样要经过连接器、分析器、优化器、执行器，也要用到存储引擎来进行数据的存取。

不同的是，更新语句需要涉及到两个重要的日志模块，redo log和binlog

redo log

一家饭店生意红火，但作为一家饭店来说，免不了每天都有赊账和来还账的。

如果赊账和还账的人不多还好，老板可以直接用一块粉板来记录，如果人多，粉板根本写不下一个月的赊账记录。

所以老板想到可以把所有的赊账记录写到账本上，而粉板上写的都是短时间的，等下班了之后，拿粉板上的和账本进行对账。

在这个例子中，粉板就是redo log，账本就是mysql中的记录，我们用还账类比一下mysql的更新过程，如果每次有人更新，我们都去mysql中找到这条记录，效率很低，所以mysql的思路和这个老板一致，更新操作先放到redo log中，过一段时间再慢慢消化。

这种思路叫WAL技术，即Write Ahead Logging技术，先写日志，再写磁盘。

需要注意的是，粉板如果满了，老板还没下班，老板就必须停下来手中的活。mysql中的redo log总共可以记录4GB的操作

当write pos 追上check point，mysql这个老板就要去处理一下redo log了。

事实上，Redo log包括两部分，重做日志缓冲(redo log buffer)和重做日志文件(redo log file)，前者是易失的缓存，后者是持久化的文件。

也因此，有了redo log持久化，数据库即使异常重启也不会丢日志，这是crash safe机制，但仍需要注意的是，redo log是innodb存储引擎特有的。

bin log

binlog是Server层的日志，可以适用于所有存储引擎。

那既然有binlog了，前文为什么要搞一个innodb存储引擎专属的redo log呢？

因为mysql最开始没有innodb引擎，而是myisam引擎，使用的是binlog，但是binlog仅限于归档，没有crash safe机制，于是自己补充了一个redo log。

• redo log是innodb存储引擎特有的，而binlog是server层特有的
• redo log存储的是物理日志，binlog是逻辑日志
• redo log像上面讲的那样，支持4GB大小，多了就得处理并且去覆盖，binlog日志当写满一个日志文件之后，会新建一个新的日志文件。

下面以更新ID为2的一行数据为例：

图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的。

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

参考

极客时间 丁奇

一、总览

话不多说，直接上图，让大家理解一下一条sql语句执行的大概流程。

从上图中我们可以看到，这些东西大致分为三部分，分别是：客户端、server层和存储引擎。
1、Client层
我们使用mysql的自带的命令行工具，我们的springboot使用jdbc链接我们的数据库，或者sqlyog等连接工具都归纳为mysql客户端(Client)，主要用于发送执行sql语句的请求。
2、Server层
主要包括连接器、缓存查询、分析器、优化器、执行引擎等，涵盖mysql大部分功能以及所有内置函数（日期、数学和加密函数等），所有跨引擎的功能都在这一层实现，例如视图、存储过程、触发器等。
3、Store层
主要负责数据的存储和提取，支持InnoDB，MyISAM，Memory等多种存储引擎，其中InnoDB是目前大部分版本的默认存储引擎。

二、执行流程

在了解了大致流程后，现在来聊一下，一条sql语句在每个步骤执行的细节。
1、连接器
连接器位于Server层，作用是负责跟所有客户端的连接进行交互，并对连接进行鉴权管理连接等等，每来一个新连接，连接器都会为这个session开辟一个专属这个session的内存空间，来执行该session的后续操作。

[root@192 ~]# mysql ‐h localhost ‐uroot ‐p

在你敲下这个命令输入密码完成后，客户端就开始与服务端建立TCP长连接，服务端负责处理客户端连接的就是连接器。这时，连接器会校验你的host、username、password等信息，这些信息校验通过后，连接器会去权限表里面把你的权限拉出来，之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。这也就意味着一旦连接建议成功，不管管理员对权限表做什么样的修改，都不会影响该连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。用户的权限表在系统表空间的mysql的user表中。

连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist; 命令中看到它。文本中这个 图是 show processlist 的结果，其中的 Command 列显示为“Sleep”的这一行，就表示现在系统里面有一个空闲连接。

客户端如果长时间不发送command到Server端，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值 是 8 小时。 查看wait_timeout

show global variables like "wait_timeout";

2、缓存查询
当连接简历成功后，select语句就会来到缓存查询这一块，尝试从缓存中拿到数据。以前执行过的sql语句和结果会以K-V的形式存储，sql是K，查询的结果是V。如果拿到了结果会直接返回，如果没拿到结果则会继续走后面的流程拿到数据返回。
但是大多数情况下，该缓存很鸡肋，为什么这样说，假设我们缓存中一张表存在很多缓存，一旦该表执行了update语句，因此所有缓存数据都会失效，对于更新压力大的数据表命中率会很低。在mysql8.0版本已经将缓存功能移除。

一般建议大家在静态表里使用查询缓存，什么叫静态表呢？就是一般我们极少更新的表。比如，一个系统配置表、字典 表，那这张表上的查询才适合使用查询缓存。好在 MySQL 也提供了这种“按需使用”的方式。你可以将my.cnf参数 query_cache_type 设置成 DEMAND

 my.cnf  
 #query_cache_type有3个值 0代表关闭查询缓存OFF，1代表开启ON，2（DEMAND）代表当sql语句中有SQL_CACHE 关键词时才缓存 
 query_cache_type=2

这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下 面这个语句一样：

 select SQL_CACHE * from test where ID=5；

查看当前mysql实例是否开启缓存机制

show global variables like "%query_cache_type%";

3、分析器
如果没有命中缓存，那么就来到了分析器，mysql需要知道你想做什么。

分析器首先会进行词法分析，你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是 什么，代表什么。 MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符 串“ID”识别成“列 ID”。

然后进行语法分析，根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句 是否满足 MySQL 语法。 如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒。

语法分析结束后，会生成一棵语法树，大致如下：

4、优化器
经过了分析器，mysql就知道你需要做什么了，在开始执行之前，mysql还需要对它做一些优化。
优化器是在表里面有多个索引决定使用哪个索引，或者多表关联join的时候，是先执行左边的逻辑还是右边的逻辑等做一系列的处理。

select * from test1 join test2 using(ID) where test1.name="haha" and test2.name="hehe";

既可以先从表 test1 里面取出 name=haha的记录的 ID 值，再根据 ID 值关联到表 test2，再判断 test2 里面 name的 值是否等于hehe。

也可以先从表 test2 里面取出 name=hehe的记录的 ID 值，再根据 ID 值关联到 test1，再判断 test1 里面 name 的值是否等于 haha。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。优化器阶段 完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

5、执行引擎
开始执行的时候，要先判断一下你对这个表 T有没有执行查询的权限，如果没有，就会返回没有权限的错误，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限。

 select * from test where id=1;

如果有权限，就打开表继续执行。

打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。 比如我们这个例子中的表 test 中，ID 字段没有索引，那么执行器的执行流程是这样的：

1、调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 1，如果不是则跳过，如果是则将这行存在结果集中；
2、调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3、 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

至此，这个语句就执行完成了。对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。你会在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加 的。在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。

三、BinLog归档日志

删库是不需要跑路的哈，因为我们的SQL执行时，会将sql语句的执行逻辑记录在我们的bin-log当中，binlog是Server层实现的二进制日志,他会记录我们的cud操作。Binlog有以下几个特点：

1、Binlog在MySQL的Server层实现（引擎共用）
2、Binlog为逻辑日志,记录的是一条语句的原始逻辑
3、Binlog不限大小,追加写入,不会覆盖以前的日志

如果，我们误删了数据库,可以使用binlog进行归档!要使用binlog归档，首先我们得记录binlog，因此需要先开启MySQL的 binlog功能。
编辑my.cnf配置文件

# 配置开启binlog  
log‐bin=/usr/local/mysql/data/binlog/mysql‐bin 
#注意5.7以及更高版本需要配置本项：server‐id=123454（自定义,保证唯一性）
#binlog格式，有3种statement,row,mixed 
binlog‐format=ROW 
#表示每1次执行写入就与硬盘同步，会影响性能，为0时表示，事务提交时mysql不做刷盘操作，由系统决定 
sync‐binlog=1