MySQL | 一条更新SQL的前世今生

MySQL | 一条更新SQL的前世今生

01

提交一条更新SQL背后发生了什么？

基本逻辑和查询SQL也是一致的，也就是说，它也要经过解析器、优化器的处理，最后交给执行器。

那么和查询SQL的区别主要在哪里呢？

区别就在于拿到符合条件的数据之后的操作，查询SQL拿到符合条件的操作直接返回，而更新SQL还要涉及两个日志：redo log（重做日志）和binlog（归档日志）

02

一条更新SQL的执行过程

以MySQL为例

执行流程如图所示：（图片来源与网络）

如图所示：

MySQL 分为 Server 层和存储引擎层两部分。

Server 层包括连接器、查询缓存、分析器、执行器等，以及所有的内置函数（如日期、时间、数学和加密函数等）和跨存储引擎的功能（如存储过程、触发器、视图）。

存储引擎层负责数据的存储和提取。

SQL语句的执行过程

建立连接：

首先要建立与 MySQL 的连接，来连接用户和MySQL数据库，在完成 TCP握手 后，连接器会根据输入的用户名和密码验证登录身份。

连接到MySQL之后，连接器会首先判断当前用户拥有的权限，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

查询缓存：

在建立连接后，就要开始执行 update语句了。

拿到查询请求后，会先查询缓存。执行过的语句及其结果会以 key-value 对的形式保存在一定的内存区域中，key是查询语句，value是查询的结果。

需要注意：在MySQL8.0版本，查询缓存功能就删除了，不存在查询缓存的功能了。

分析器:

如果查询缓存未命中，就要开始执行语句了。首先需要对 SQL 语句进行硬解析。

分析器先会做词法分析。分析SQL 语句的组成成分。之后就要做语法分析。根据词法分析的结果，语法分析器会根据语法规则，判断输入的 SQL 语句是否满足语法。

优化器：

经过分析器的词法分析和语法分析后，SQL合法后还要经过优化器的处理。

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

执行器：

在执行阶段，MySQL首先会判断有没有执行语句的权限，若无权限，返回没有权限的错误；若有权限，就打开表继续执行。

存储引擎提供数据读取和记录的接口。

03

更新SQL语句的日志记录

日志记录：先写日志，再写磁盘

redo log

重做日志是InnoDB引擎特有的，是物理日志，记录在某个数据页上做了什么修改。

大小是固定，可以进行配置大小。

checkpoint是当前要擦除的位置，移动规律和前者一样。两者之间的位置可以记录新的操作。

如果write pos 追上checkpoint，就移动checkpoint擦除一些记录。所以即使数据可以发生异常重启，InnoDB也可以保证之前提交的记录不会丢，这就是MySQL的crash_safe能力。

binlog

binlog日志是MySQL的server层的实现的，所有引擎都可以使用。

binlog记录的是sql语句的原始逻辑，相比redo log的循环写入，binlog是追加写的，binlog文件写到一定大小后会切换到下一个，不会覆盖以前的日志。

Binlog有两种模式，statement 格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。

以此SQL为例，执行流程图如下

update test set i=i+1 where ID=1;

若在redolog后写入binlog阶段出问题，现在这条数据是prepare状态，然后我们恢复数据库的时候这条数据的更新操作就会回滚，不产生变更，

若在commit出了问题，也会进行回滚，这样可以保证数据的一致性。