SQL更新语句执行流程

SQL 的更新执行流程跟查询流程差不多，假设现在有一条更新语句：UPDATE T SET c = 1 WHERE ID = 1

• 客户端连接到 MySQL 服务，通过连接器创建连接，权限验证
• 在更新语句时候，会清空该表的所有查询缓存器数据
• 之后就是通过分析器进行词法分析和语法分析，查看更新语句是否存在问题
• 在通过优化器选择索引，找到最合理的执行计划
• 执行器负责执行，在存储引擎中找到 ID=1 这一行，然后更新

与查询流程不一样的是，更新流程还涉及到两个重要的日志模块：redolog（重做日志）和 binlog（归档日志），这两个日志也是这篇文章重点讨论的。

先来一张图整体看下流程是怎么样的：

一、redolog

1. 什么是 redolog

redolog 是 InnoDB 存储引擎独有的，是保证事物持久性的重要机制。当 mysql 意外宕机或崩溃时，保证已提交的事物，确定持久化到磁盘的一种措施，防止数据丢失；

2. redolog 的作用

Mysql 的数据是以页为单位(16KB)，查询一条数据，会从硬盘中把一页的数据都加载出来，加载出来的数据叫做数据页，会放入 Buffer Pool 中，后续的查询都是从 Buffer Pool 中找，如果没有命中再从磁盘中加载，减少硬盘 IO 开销，更新数据也是如此，发现 Buffer Pool 有需要更新的数据，则直接在 Buffer Pool 中进行更新

假设我们只在内存的 Buffer Pool 中更新了页数据，事物提交后突然数据库崩溃，导致内存中的数据丢失了，那么这个更新后的数据也一起丢失了，这肯定是不能接受的。那我们要怎么保证数据的持久性呢？有一个方法就是每次事物提交的时候把该次事物提交修改的所有数据页都更新到磁盘中，但是这样做有一个问题：

• 仅仅修改了一条记录，却要把整个数据页更新回去，有点浪费
• 一个事物中可能会包含多条 sql 语句，这些语句可能会对 Buffer Pool 中不相邻的数据页进行操作。当把该事物修改的数据页刷新到磁盘中会产生很多随机 IO，随机 IO 的开销要大于顺序 IO

由于以上种种原因，我们的想法就是想保存事物提交后的数据，即使在数据库崩溃，重启后也能恢复事物提交后的数据；

所以我们可以在事物提交的时候，只需要把这次事物提交修改的数据记录一下，比如：

将第 0 号表空间第 100 号页面中偏移量为 1000 处的值更新为 2。

这就是我们所说的redolog日志，即使数据库崩溃了，重启后只要根据redolog日志恢复即可

innodb_flush_log_at_trx_commit这个参数设置成1的时候，
表示每次事务的redo log都直接持久化到磁盘。这个参数设置成1，这样可以保证MySQL异常重启之后数据不丢失

二、binlog

binlog记录的是语句的原始逻辑,insert inot value(1,2,3)这种语句，所以Mysql数据的数据备份，主备等都离不开binlog日志，需要通过binlog日志来实现数据一致性。

• binlog是MySQL的Server层实现的，所有引擎都可以使用。
• binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
• binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
• sync_binlog参数设置为1的时候，提交事物的时候就会写入到binlog日志，跟redolog日志刷盘时机是一样的

三、二阶段提交

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

1. 不使用二阶段提交

如果只写一次的话，那到底先写bin-log还是redo-log呢？

• 先写bin-log，再写redo-log：当事务提交后，先写bin-log成功，结果在写redo-log时断电宕机了，再重启后由于redo-log中没有该事务的日志记录，因此不会恢复该事务提交的数据。但要注意，主从架构中同步数据是使用bin-log来实现的，而宕机前bin-log写入成功了，就代表这个事务提交的数据会被同步到从机，也就意味着从机会比主机多出一条数据。
• 先写redo-log，再写bin-log：当事务提交后，先写redo-log成功，但在写bin-log时宕机了，主节点重启后，会根据redo-log恢复数据，但从机依旧是依赖bin-log来同步数据的，因此从机无法将这个事务提交的数据同步过去，毕竟bin-log中没有撒，最终从机会比主机少一条数据。

2. 使用二阶段提交

为了避免以上的数据不一致问题，Innodb存储引擎使用的两阶段提交方案。
把redolog的写入拆成了两个步骤prepare和commit

• 如果在binlog日志写入过程中发生了异常，因为mysql通过redolog回复数据时，发现redolog还在prepare阶段，并且没有对应的binlog日志，就会回滚；
• 如果在redolog提交commit时异常时，虽然redolog处于prepare阶段，但是能通过事物id找到对应的binlog日志，所以mysql认为数据时完整的，就会提交redolog日志，恢复数据；

3.两阶段提交有什么问题

binlog 和 redolog 在内存中都对应的缓存空间，binlog 会缓存在 binlog cache，redolog 会缓存在 redolog buffer，它们持久化到磁盘的时机分别由下面这两个参数控制。
一般我们为了避免日志丢失的风险，会将这两个参数设置为 1：

• 当 sync_binlog = 1 的时候，表示每次提交事务都会将 binlog cache 里的 binlog 直接持久到磁盘；
• 当 innodb_flush_log_at_trx_commit = 1 时，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘；

可以看到，如果 sync_binlog 和 当 innodb_flush_log_at_trx_commit 都设置为 1，那么在每个事务提交过程中， 都会至少调用 2 次刷盘操作，一次是 redo log 刷盘，一次是 binlog 落盘，所以这会成为性能瓶颈。

---

我是一零贰肆，一个关注Java技术和记录生活的博主。

欢迎扫码关注“一零贰肆”的公众号，一起学习，共同进步，多看路，少踩坑。