02 | 日志系统：一条SQL更新语句是如何执行的？

此系列文章为极客时间课程《MySQL 实战 45 讲》的学习笔记！

引言

上一篇文章中，我们介绍了在 MySQL 中，一条查询语句是如何执行的。今天我们聊聊一条更新语句是如何执行的。其他更新语句和查询语句的大致流程是差不多的。同样先建立连接，因为涉及到了表数据的更新，所以这个表上的缓存会被清空。分析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用哪个索引。然后，执行器负责具体执行，找到这一行，然后更新。
但是更新语句还涉及到两个重要的概念：redo log（重做日志）和 binlog（归档日志）。

redo log

我们知道，MySQL 的数据是存储在磁盘，每次更新数据都需要写入磁盘，然后返回给客户端，表明更新成功，在这个过程中，就会存在 IO 操作，磁盘的读写和内存的读写相比是十分慢的，所以 MySQL 有了 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。
也就是说当执行一个更新语句时，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。当 redo log 日志写满了的时候，就会停下当前的任务，将日志中的数据写入到磁盘。
一般来说，redo log 是有固定大小的并且是可配置的。

write pos 是写入的位置，随着不断的更新，wirte pos 指针不断的向后移动，并且是循环使用的。check point 是当前擦除的位置，也是不断的向后移动，在移动前，要保证数据写入到磁盘了。当 write pos 追上 check point 时，就说明日志文件写满了，需要停止更新操作，执行擦除，推进 check point 的位置。
有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

binlog

上面我们聊到 redo log，它是 InnoDB 引擎层的日志，而 Server 层也有自己的日志文件，那就是 binlog。

两种日志的区别：

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

执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程。

1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

因为binlog 会记录所有的逻辑操作，并且是采用“追加写”的形式，所以我们在实际应用中，要想恢复数据，都会使用到 binlog 日志。

两阶段提交

在上面的更新语句执行流程中，我们发现 redo log 的写入是两阶段提交的。为什么要用两阶段提交呢？

我们假设要更新的操作是id=1 这一行的字段 a，原始值是 0，更新成 1。
情况 1：先写 redo log 后写 binlog
假设 redo log 写完，在准备写入 binlog 是，数据库服务器宕机了。当服务重启后，数据库的恢复后的数据是 1，但是 binlog 中并没有记录这个数据，所以后面使用 binlog 恢复数据时，字段 a 就被恢复成了 0，导致数据不一致。
情况 2：先写 binlog 后写 redo log
同样的道理，写完 binlog 后，服务器宕机，恢复后，数据库的数据是 0，但是用 binlog 恢复后，数据又变成了 1，数据又不一致了。

所以简单总结就是：采用两阶段提交是为了保证 redo log 和 binlog 日志数据保持一致。

总结

最后提供一些两个日志参数的配置：

Redo Log（重做日志）

Redo log 用于记录事务的变化，以便在系统崩溃后进行恢复。

主要参数

1. innodb_log_file_size：每个 redo log 文件的大小。

   SET GLOBAL innodb_log_file_size = 512M;
   

2. innodb_log_files_in_group：redo log 文件的数量。

   SET GLOBAL innodb_log_files_in_group = 2;
   

3. innodb_flush_log_at_trx_commit：控制日志刷新到磁盘的频率。

   • 0：每秒刷新一次。
   • 1：每次提交事务时刷新。
   • 2：每次提交事务时写入文件，但每秒刷新一次。

   SET GLOBAL innodb_flush_log_at_trx_commit = 1;
   

4. innodb_flush_method：控制 InnoDB 如何刷新数据和日志。

   • O_DSYNC：使用 fsync() 刷新日志。
   • O_DIRECT：使用直接 I/O。

   SET GLOBAL innodb_flush_method = O_DIRECT;
   

Binlog（二进制日志）

Binlog 用于记录所有对数据库进行更改的 SQL 语句，主要用于复制和数据恢复。

主要参数

1. log_bin：启用或禁用二进制日志。

   SET GLOBAL log_bin = ON;
   

2. binlog_format：设置 binlog 的格式。

   • ROW：记录行级别的变化。
   • STATEMENT：记录 SQL 语句。
   • MIXED：混合模式。

   SET GLOBAL binlog_format = ROW;
   

3. sync_binlog：控制 binlog 刷新到磁盘的频率。

   • 0：由操作系统决定何时刷新。
   • 1：每次提交事务时刷新。

   SET GLOBAL sync_binlog = 1;
   

4. expire_logs_days：设置 binlog 的过期时间（天数）。

   SET GLOBAL expire_logs_days = 7;
   

5. max_binlog_size：设置单个 binlog 文件的最大大小。

   SET GLOBAL max_binlog_size = 1G;
   

配置示例

以下是一个示例配置，展示如何在 MySQL 配置文件（my.cnf 或 my.ini）中设置这些参数：

[mysqld]
# Redo log settings
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

# Binlog settings
log_bin = /var/lib/mysql/mysql-bin
binlog_format = ROW
sync_binlog = 1
expire_logs_days = 7
max_binlog_size = 1G

配置文件修改后，需重启 MySQL 服务以使更改生效。