Mysql更新语句执行流程

Mysql整理记录Day2

假设现在有一张表T，ID为主键和一个整型字段c，如果要将ID=2这一行的值加1，建表及更新语句如下：

create table T(ID int primarykey, c int);

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

与查询流程不同的是，更新还涉及到两个重要的日志：redo log 和 binlog。

为什么会有两份日志呢？
因为最开始 Mysql 自带的引擎是 MyISAM，并没有 InnoDB 引擎，但是 MyISAM 没有 crash-safe 能力，binlog 属于server层的日志，用于归档（备份）。InnoDB是以插件的形式引入Mysql，并使用redo log实现crash-safe。

redo log 和 binlog区别：

1. redo log是InnoDB引擎特有的；binlog 是 Mysql server 层实现的，所有的引擎都可以使用；
2. redo log固定大小，循环写，空间会用完；binlog是可以追加写的，“追加写”指的是binlog文件写到一定大小会切换到另一个文件继续写，不会覆盖之前的记录；

补充：binlog 有两种格式：statement 和 row，statement 格式记录的是 sql 语句的原始逻辑，raw 格式记录的是更新前后的两条原始数据记录， 因此 row 格式的 binlog 体积大些。

试想一下，Mysql 如果每一次的更新都需要写进磁盘，磁盘需要找到对应的那条记录，然后更新，整个过程IO成本很高。

Mysql 是怎么做的呢？WAL（write ahead logging），也就是先写日志，再写磁盘。具体来说，需要更新一条记录的时候，InnoDB 引擎会先把记录写到 redo log，并更新内存，更新完成。引擎会在合适的时候将这个操作记录更新到磁盘。

有了上面对两个日志的了解，下面看看这条更新语句的执行流程：

• 执行器先找引擎去ID=2这行数据。ID是主键，引擎直接通过树搜索找到这一行。此时有两种情况：如果这行记录的数据页刚好在内存中，则直接返回给执行器；否则需要先从磁盘找并读到内存，再返回。
• 执行器拿到引擎给的数据，对c进行加1，调用引擎接口写入这行数据
• 引擎将这行新数据更新到内存，同时记录到redo log，此时redo log处于prepare状态，然后告诉执行器执行完成，随时可以提交事务。
• 执行器生成改操作的binlog，并把binlog写入到磁盘。
• 执行器调用引擎的提交事务接口，引擎把刚写入的redolog改成commit状态

细心的同学可能注意到了，redolog的写入被拆成了两个步骤：prepare 和 commit，这就是两阶段提交。
为什么需要两阶段提交呢？为了让两份日志之间的逻辑一致。

redo log用于保证 crash-safe 能力。建议将 innodb_flush_log_at_trx_commit 这个参数设置为1，表示每次事务的 redo log 都写入到磁盘。
sync_binlog这个参数也设置为1，表示每次事务的binlog都持久化到磁盘，保证Mysql异常重启，binlog不丢失。

笔记参考于极客时间《MySQL实战45讲》