【MySQL】运行原理（三）：增删改 SQL 执行过程分析

小咪咪 2022-11-20 09:32 194阅读 0赞

在[上一篇][Link 1]文章，我们介绍了查询 SQL 的执行流程，所以，是不是再讲讲更新流程、插入流程和删除流程？

首先，要明确一点，在数据库里面，我们说的 update 操作其实包括了更新、插入和删除。如果大家有看过 MyBatis 的源码，应该知道 Executor 里面也只有 doQuery() 和 doUpdate() 的方法，没有 doDelete() 和 doInsert()。

更新流程和查询流程有什么不同呢？基本流程也是一致的，也就是说，它也要经过解析器、优化器的处理，最后交给执行器。区别就在于拿到符合条件的数据之后的操作（需要记录日志文件）。

> InnoDB 存储引擎分析：
> 
>  *  [【MySQL】存储引擎（一）：存储引擎介绍][MySQL]
>  *  [【MySQL】存储引擎（二）：InnoDB 内存结构][MySQL_InnoDB]
>  *  [【MySQL】存储引擎（三）：InnoDB 磁盘结构（表空间）][MySQL_InnoDB 1]
>  *  [【MySQL】存储引擎（四）：InnoDB 逻辑存储结构][MySQL_InnoDB 2]
>  *  [【MySQL】存储引擎（五）：重做日志（redo log），回滚日志（undo log），二进制日志（binlog）][MySQL_redo log_undo log_binlog]

### 1.Update 示例分析 ###

在明白 InnoDB 的重做日志（redo log），回滚日志（undo log），二进制日志（binlog）前提下，我们来总结一下一个更新操作的流程，这是一个简化的过程（name原值是张三）。

update teacher set name='彭于晏' where id=1

语句的执行过程如下：

1.  客户端（通常是你的服务）发出更新语句” update teacher set name=‘盆鱼宴’ where id=1 “ 并向MySQL服务端建立连接
2.  MySQL 连接器负责和客户端建立连接，获取权限，维持和管理连接
3.  MySQL 拿到一个查询请求后，会先到查询缓存看看（默认关闭，MySQL8.x已经废弃了查询缓存），看之前是否已经执行过
    
    1.  如果执行过，执行语句及结果会以 key-value 形式存储到内存中，如果命中缓存会返回结果
    2.  如果没命中缓存，就开始真正执行语句。分析器会先做词法分析，识别出关键字 update，表名等等；之后还会做语法分析，判断输入的语句是否符合MySQL语法
4.  经过分析器，MySQL 已经知道语句是要做什么。优化器接着会选择使用哪个索引（如果多个表，会选择表的连接顺序）
5.  MySQL 服务端最后一个阶段是执行器会调用引擎的接口去执行语句
6.  事务开始（任何一个操作都是事务），写 undo log ，记录记录上一个版本数据（**name=张三**），并更新记录的回滚指针和事务 ID
7.  执行器先调用引擎取 id=1 这一行。id 是主键，引擎直接用树搜索找到这一行；
    
    1.  如果 id=1 这一行所在的数据页本来就在内存中，就直接返回给执行器更新；
    2.  如果记录不在内存，接下来会判断索引是否是唯一索引
        
        1.  如果是唯一索引，InnoDB 会将更新操作缓存在 change buffer 中；
        2.  如果不是唯一索引，就只能将数据页从磁盘读入到内存，返回给执行；
8.  执行器拿到引擎给的行数据，修改这一行数据的值为彭于晏，再调用引擎接口写入这行新数据
9.  引擎将这行数据更新到内存中，同时将这个更新操作记录到 redo log（**name=彭于晏**） 里面，这时 redo log 进入 prepare 状态，相当于告诉执行器，执行完成了，可以随时提交
10. 执行器收到通知后，生成这个操作的 binlog（注：binlog也有自己的刷盘策略，属于 MySQL 的 Server 层）
11. 执行器调用引擎的提交事务接口，并将 redo log 的状态改成 commit（注：redo log 有三种刷盘策略，一般是实时刷）
12. 更新完成

下面列出了核心部分的执行过程

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center]  
需要特别注意的几点：

1.  先记录到内存，再写日志文件。
2.  redo log 的写入拆成了两个步骤： prepare 和 commit，这就是"两阶段提交"。
3.  存储引擎和 Server 记录不同的日志，先记录 redo，再记录binlog。

> 另外，内存和磁盘之间，工作着很多后台线程。后台线程的主要作用是负责刷新内存池中的数据和把修改的数据页刷新到磁盘。后台线程分为：master thread，IO thread，purge thread，page cleaner thread。
> 
>  *  master thread：负责刷新缓存数据到磁盘并协调调度其它后台进程
>  *  IO thread：分为 insert buffer、 log、 read、 write进程。分别用来处理 insert buffer、重做日志、读写请求的IO回调
>  *  purge thread：用来回收undo 页
>  *  page cleaner thread：用来刷新脏页

### 2.两阶段提交 ###

> 以下内容出自：[https://segmentfault.com/a/1190000021354720][https_segmentfault.com_a_1190000021354720]

为什么必须有“两阶段提交”呢？这是为了让两份日志之间的逻辑一致。要说明这个问题，我们得从文章开头的那个问题说起：怎样让数据库恢复到半个月内任意一秒的状态？

前面我们说过了，binlog 会记录所有的逻辑操作，并且是采用“追加写”的形式。如果你的 DBA 承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有 binlog，同时系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。

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

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

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

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

**1.先写 redo log 后写 binlog**

假如在引擎 写完 redo log 后，bin log 没有写完，异常重启，依然可以根据 redo log 日志把数据恢复，但是 binlog 没有记录这个语句。 所以从库 通过 binlog 同步数据就导致没有把这个这行数据同步过来，丢失了这个事务操作造成数据不一致。

**2.先写 binlog 再写 redo log**

如果写完 binlog 后 崩溃，由于 redo log 还没有写，崩溃恢复后这个事务无效，但是 binlog 却有记录。从库根据 这个 binlog 日志就会导致多处一个事务，与主库不一致。

**简单说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。**（敲黑板了同学们）

[Link 1]: https://blog.csdn.net/weixin_43935927/article/details/109324057
[MySQL]: https://blog.csdn.net/weixin_43935927/article/details/113983566
[MySQL_InnoDB]: https://blog.csdn.net/weixin_43935927/article/details/113982766
[MySQL_InnoDB 1]: https://blog.csdn.net/weixin_43935927/article/details/113983374
[MySQL_InnoDB 2]: https://blog.csdn.net/weixin_43935927/article/details/109344931
[MySQL_redo log_undo log_binlog]: https://editor.csdn.net/md/?articleId=109324415
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/4a3f7056b91a4f279ec758ffe90beef0.png
[https_segmentfault.com_a_1190000021354720]: https://segmentfault.com/a/1190000021354720