《MySQL实战45讲》02 | 日志系统：一条SQL更新语句是如何执行的？

一条更新SQL

假设表T只有一个整形字段c和主键ID，当执行如下更新时发生了什么呢？

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

与查询过程类似，但更新操作涉及到两个日志：redo log和binlog。

---

redo log

redo log是InnoDB存储引擎特有的日志，每个InnoDB至少有一个重做日志文件组（group），且每个文件组下至少有两个重做日志文件，默认的两个重做日志文件为“ib_logfile0“和”ib_logfile1”。

• redo log条目结构：
  
  • redo_log_type：重做日志类型
  • space：表空间id
  • page_no：页偏移量
  • redo_log_body：重做日志记录的数据
• 写重做日志文件
  InnoDB采用了WAL(Write-Ahead-Logging)技术，即先写日志，再写磁盘。
  以一个重做日志文件组下有四个重做日志文件（ib_logfile0～ib_logfile3）为例，当有更新操作时，InnoDB会先把记录写到ib_logfile0，并更新到内存，当ib_logfile0写满后会继续写ib_logfile1，依此类推，当ib_logfile3被写满时，会先将记录更新到磁盘，再清空redo log。
  
• 写盘时机
  InnoDB 提供的innodb_flush_log_at_trx_commit参数，控制了触发写磁盘的时机：
  • 0：每次事物提交都是把redo log留在redo log buffer中；
  • 1：每次事物提交都是把redo log持久化到磁盘；
  • 2：每次事物提交都是把redo log写到page cache。
    InnoDB有一个后台线程，每隔一秒，就会把redo log buffer中的日志，调用write写到文件系统的page cache，再调用fsync持久化到磁盘。

---

binlog

逻辑日志binlog(归档日志)是Server层的日志。

• binlog的三种格式
  binlog_format参数控制binlog的三种格式：
  • statement：记录了binlog日志的原SQL语句。但statement格式可能会导致主从服务的数据不一致，如：行的索引被更改或在主服务器运行rand、uuid等函数，都有可能导致主从数据不一致。
  • row：记录了binlog日志的原SQL语句+表的行更改情况。记两条，更新前和更新后都有。
  • mixed：默认采用statement格式，但某些情况下(如：使用uuid()等不确定函数、使用insert delay语句等)会使用row格式。

---

redo log和binlog的区别

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

---

update内部流程

• 执行器通过引擎获取ID=2的行，若ID=2的行在数据页中，则直接返回给执行器，否则先从磁盘读入到内存，再返回给执行器。
• 执行器再对原值进行加1操作，得到新的数据行，再掉引擎写入新的数据行。
• 引擎将数据更新到内存，并将数据更新到redo log（此时redo log处于prepare状态）。
• 执行器生成binlog，并将binlog写入磁盘。
• 执行器掉引擎进行事物提交，引擎将redo log状态从prepare状态改为commit状态，完成更新。
  注：两阶段提交保证了redo log和binlog的一致性。
  

---

本文为《MySQL实战45讲》学习笔记