文章

MySQL UPDATE语句执行全流程：从SQL到磁盘的旅程

Posted 2025-03-26 Updated 2025-04- 26
By Shawn
44~57 min read

引言

当我们执行一条简单的UPDATE语句时，MySQL内部会经历一系列复杂的处理流程。本文将以一条UPDATE语句为例，详细解析MySQL各核心组件的工作原理及其协同方式，帮助您深入理解MySQL的内部机制。

示例UPDATE语句

UPDATE users SET balance = balance - 100 WHERE id = 1;

1. MySQL架构与执行流程

当MySQL接收到这条UPDATE语句后，会经历以下组件处理：

  1. 连接器（Connection）：

    • 负责客户端连接认证

    • 进行用户身份验证

    • 检查用户权限（权限验证在这一阶段完成）

    • 维护连接状态

  2. 解析器（Parser）：

    • 将SQL语句解析成语法树

    • 检查SQL语法是否正确

  3. 预处理器（Preprocessor）：

    • 检查表和列是否存在

    • 将表名、列名等转换为内部数据结构

  4. 优化器（Optimizer）：

    • 决定使用哪个索引

    • 决定连接顺序

    • 生成执行计划

  5. 执行器（Executor）：

    • 调用存储引擎API执行SQL

    • 返回结果给客户端

2. Buffer Pool的详细工作机制

Buffer Pool是InnoDB的内存缓冲区，用于缓存表数据和索引。它通过精心设计的多个链表来管理数据页：

2.1 Buffer Pool的链表结构

LRU链表（Least Recently Used）

  • 结构：分为young区域（热数据，约占5/8）和old区域（冷数据，约占3/8）

  • 作用：管理缓存页的访问频率和淘汰策略

  • 特点：

    • 新读入的页不会直接放入young区域头部，而是放入old区域头部

    • 只有在old区域停留超过innodb_old_blocks_time（默认1秒）后被访问，才会移到young区域头部

    • young区域的前1/4是不移动区域：当这部分页被访问时不会移动位置

    • 只有位于young区域后3/4部分的页被访问时才会被移动到链表头部

    • 这种设计避免了全表扫描等操作污染Buffer Pool和减少链表维护开销

Flush链表（脏页链表）

  • 作用：跟踪所有脏页（已修改但未写入磁盘的页）

  • 特点：

    • 页被修改后同时存在于LRU链表和Flush链表中

    • 按照修改时间排序，便于批量刷新

    • 脏页刷新到磁盘后，会从Flush链表移除，但仍保留在LRU链表中

Free链表

  • 作用：管理空闲页（未被使用的缓冲页）

  • 特点：

    • 当需要读取新页时，首先从Free链表获取空闲页

    • 如果Free链表为空，则从LRU链表尾部淘汰页

2.2 数据页查找与加载流程

  1. 数据页查找：

    • 执行器首先在Buffer Pool中查找id=1的数据页

    • 通过页表（Page Table）快速定位页是否在内存中

    • 页表是一个哈希表，键是表空间ID和页号的组合，值是页在内存中的地址

  2. 缓存命中：

    • 如果找到（缓存命中），直接在内存中操作

    • 根据页在LRU链表中的位置决定是否需要移动：

      • 如果在young区域前1/4，不移动

      • 如果在young区域后3/4，移到链表头部

      • 如果在old区域且停留时间超过阈值，移到young区域头部

  3. 缓存未命中：

    • 如果未找到，从Free链表获取空闲页

    • 如果Free链表为空，从LRU链表尾部淘汰页

    • 从磁盘读取数据页到Buffer Pool

    • 将新页放入LRU链表的old区域头部

2.3 脏页管理与刷盘机制

  1. 脏页产生：

    • 当我们执行UPDATE语句修改balance值时，对应的数据页被标记为"脏页"

    • 同时加入Flush链表

  2. 脏页刷盘时机：

    • 后台线程定期刷新：Master Thread每秒和每10秒的刷新操作

    • Redo Log写满：当redo log写满时，会触发脏页刷新

    • 系统空闲时：系统空闲时会刷新一部分脏页

    • Buffer Pool空间不足：当需要新页但无空闲页且LRU尾部都是脏页时

    • 脏页比例过高：由innodb_max_dirty_pages_pct参数控制

    • 实例关闭时：正常关闭实例会刷新所有脏页

  3. 刷新策略：

    • LRU刷新：从LRU尾部开始刷新

    • Flush List刷新：根据脏页产生时间刷新

    • 邻近页刷新：尝试刷新物理位置相邻的脏页，减少随机I/O

3. 事务开始与隔离级别

在执行UPDATE前，MySQL会：

  1. 开启事务：显式或隐式（autocommit=1）

  2. 设置隔离级别：默认为REPEATABLE-READ

  3. 获取锁：根据隔离级别获取相应的锁（如行锁）

4. Undo Log：实现MVCC与回滚

在修改数据前，InnoDB会：

  1. 记录Undo Log：

    • 记录修改前的数据（balance的原值）

    • 每个事务有自己的Undo Log链表

  2. MVCC实现：

    • 通过Undo Log构建不同版本的记录

    • 不同事务根据隔离级别和事务ID读取对应版本

  3. 回滚机制：

    • 如果事务需要回滚，通过Undo Log恢复原始数据

    • Undo Log存储在系统表空间或独立Undo表空间

  4. Undo Log刷盘时机：

    • Undo Log的修改先写入redo log

    • 随redo log的刷盘策略一起持久化

    • 真正的Undo页作为脏页，遵循Buffer Pool的脏页刷新机制

对于我们的UPDATE语句：

记录原始数据：id=1, balance=原值

5. 修改Buffer Pool中的数据

  1. 内存中修改：

    • 在Buffer Pool中找到对应记录

    • 将balance减去100

    • 标记数据页为"脏页"，加入Flush链表

  2. Change Buffer：

    • 如果是非唯一二级索引，可能使用Change Buffer延迟更新

    • 主键索引和唯一索引的修改不会使用Change Buffer

6. Redo Log：保证持久性

为确保数据修改的持久性，即使系统崩溃：

  1. 生成Redo Log：

    • 记录"将id=1的用户balance减少100"这个物理操作

    • Redo Log是物理日志，记录数据页的变化

  2. Redo Log Buffer：

    • 首先写入内存中的Redo Log Buffer

    • 降低磁盘I/O开销

  3. Redo Log文件与循环写入机制：

    • 循环写入方式：有固定大小，空间用完后从头开始覆盖

    • write pos：当前记录的位置，一边写一边后移

    • checkpoint：当前要擦除的位置，也是往后推移

    • write pos到checkpoint之间的部分是"活跃"的redo log

    • 如果write pos追上checkpoint，表示redo log已满，必须停下来先推进checkpoint（通过刷新脏页）

  4. checkpoint触发条件：

    • Sharp Checkpoint：实例正常关闭时，会将所有脏页刷新到磁盘

    • Fuzzy Checkpoint：运行过程中的部分刷新，包括：

      • Master Thread Checkpoint：主线程周期性刷新

      • FLUSH_LRU_LIST Checkpoint：LRU列表不足时刷新

      • Async/Sync Flush Checkpoint：redo log空间不足时刷新

      • Dirty Page too much Checkpoint：脏页比例过高时刷新

  5. Redo Log刷盘时机：

    • 事务提交时：由innodb_flush_log_at_trx_commit参数控制

      • 0：每秒刷新一次，不保证持久性

      • 1：每次事务提交都刷新，完全持久化

      • 2：每次提交写入OS缓存，每秒刷盘，折中方案

    • 每秒刷新一次：即使没有事务提交

    • Redo Log Buffer使用超过一半

    • 执行CHECKPOINT时

    • 实例正常关闭时

对于我们的UPDATE语句：

Redo Log内容：页号X，偏移量Y，修改前值Z，修改后值(Z-100)

7. Binlog：复制与恢复

Binlog是MySQL Server层的日志：

  1. 生成Binlog：

    • 记录更新操作的逻辑变化

    • 根据格式不同，可能记录SQL语句(statement)、行变化(row)或混合模式(mixed)

  2. Binlog格式：

    • ROW格式：记录行的变化前后的值

    • STATEMENT格式：记录原始SQL

    • MIXED格式：智能选择上述两种格式

  3. Binlog的追加写入特性：

    • Binlog是追加写入的，不会覆盖已有内容

    • 当文件大小达到max_binlog_size（默认1GB）时，会创建新文件

    • 文件名按序号递增，如mysql-bin.000001、mysql-bin.000002等

    • 与Redo Log的循环写入不同，Binlog永远是追加写入

  4. Binlog的保留策略：

    • 由expire_logs_days或binlog_expire_logs_seconds参数控制

    • 超过保留时间的Binlog文件会被自动删除

    • 也可以通过PURGE BINARY LOGS命令手动删除

  5. Binlog刷盘时机：

    • 由sync_binlog参数控制：

      • 0：由操作系统决定何时刷新

      • 1：每次事务提交都刷新

      • N：每N个事务刷新一次

    • 在组提交中，可能多个事务的Binlog一起刷盘

对于我们的UPDATE语句（ROW格式）：

Binlog内容：表users，id=1，balance从X变为X-100

8. 二阶段提交：保证数据一致性

为了保证Redo Log和Binlog的一致性，MySQL使用二阶段提交：

  1. 准备阶段(Prepare)：

    • 将Redo Log写入磁盘

    • 标记为prepare状态

    • 此时事务还未提交

  2. 提交阶段(Commit)：

    • 将Binlog写入磁盘

    • 在Redo Log中写入commit标记

    • 事务完成提交

这确保了即使在任何阶段崩溃，都能通过崩溃恢复保持一致性：

  • 崩溃点在prepare前：都未持久化，回滚事务

  • 崩溃点在prepare后commit前：通过Binlog判断是否需要提交或回滚

  • 崩溃点在commit后：已完成提交，无需特殊处理

9. 组提交（Group Commit）优化

为提高性能，MySQL引入了组提交机制：

  1. 原理：

    • 多个事务的Binlog一起刷盘

    • 减少磁盘I/O次数

  2. 实现：

    • binlog_group_commit_sync_delay：等待延迟（微秒）

    • binlog_group_commit_sync_no_delay_count：等待事务数量

  3. 执行步骤：

    • Flush阶段：将事务的Binlog写入内存

    • Sync阶段：将内存中的Binlog刷新到磁盘

    • Commit阶段：提交事务

组提交大幅提高了高并发下的性能，特别是在磁盘I/O是瓶颈的情况下。

10. 完整流程总结

以我们的UPDATE语句为例，完整执行流程是：

前置阶段

  1. 客户端连接 MySQL，连接器进行身份验证和权限检查

  2. 客户端发送 UPDATE users SET balance = balance - 100 WHERE id = 1;

  3. 解析器解析 SQL 并生成语法树

  4. 预处理器检查表和列是否存在

  5. 优化器生成执行计划

事务执行阶段

  1. 开始事务，设置隔离级别

  2. 执行器调用 InnoDB 引擎 API 执行更新

  3. 从 Buffer Pool 中查找 id=1 的数据页：

    • 如果不在内存中，从磁盘读取并放入 LRU 链表

    • 如果在内存中，根据其在 LRU 链表中的位置决定是否移动

  4. 获取相应的行锁

  5. 生成 Undo Log 记录：

    • 在 undo tablespace 中分配空间

    • 在 undo log 页中写入原始数据（balance=1000）

    • 更新 undo log 页的元数据（如事务ID、回滚指针等）

  6. 为 Undo Log 的变更生成 Redo Log 记录：

    • 生成记录 undo log 页修改的 redo log 条目

    • 这些条目描述了如何重建 undo log 页的变更

    • 将这些 redo log 条目写入 redo log buffer

  7. 为数据变更生成 Redo Log 记录：

    • 生成描述实际数据修改的 redo log 条目（balance 1000→900）

    • 将这些 redo log 条目追加写入 redo log buffer

  8. 在 Buffer Pool 中修改数据：

    • 修改 undo log 页（已经在步骤10完成）

    • 修改实际数据页中的 balance 值

    • 将数据页标记为脏页并加入 Flush 链表

事务提交阶段

  1. 准备提交事务：

    • 将 redo log buffer 中的内容刷新到 redo log 文件（磁盘）

    • 在 redo log 中写入 prepare 标记并确保持久化（两阶段提交第一阶段）

  2. 生成 Binlog：

    • 生成包含此次更新操作的 binlog 记录

    • 将 binlog 记录写入 binlog 文件并确保持久化

  3. 完成提交：

    • 在 redo log 中写入 commit 标记

    • 确保 commit 标记持久化到磁盘（两阶段提交第二阶段）

  4. 事务提交完成，返回结果给客户端

后台刷新阶段

  1. Buffer Pool 中的脏页最终由后台线程或其他触发条件刷新到磁盘

    • Master Thread 定期刷新

    • Redo Log 空间不足（write pos 接近 checkpoint）时刷新

    • Buffer Pool 空间不足时刷新

    • 系统空闲时刷新

    • 脏页比例过高时刷新

    • 实例关闭时刷新

11. 性能优化参数

几个关键参数及其影响：

  1. innodb_flush_log_at_trx_commit：

    • 0：每秒刷新Redo Log到磁盘，性能最好但不保证持久性

    • 1：每次事务提交都刷新，持久性最好但性能最差

    • 2：每次提交写入OS缓存，每秒刷新，平衡性能和持久性

  2. sync_binlog：

    • 0：由操作系统决定何时刷新Binlog

    • 1：每次事务提交都刷新Binlog

    • N：每N个事务刷新一次

  3. innodb_buffer_pool_size：

    • Buffer Pool大小，通常设置为系统内存的50%-80%

  4. innodb_old_blocks_time：

    • 控制新读取的页在old区域停留的最小时间

    • 防止全表扫描等操作污染Buffer Pool

  5. innodb_max_dirty_pages_pct：

    • 控制Buffer Pool中脏页的最大比例

    • 超过此比例会触发更积极的刷新

结论

一条简单的UPDATE语句背后，MySQL执行了复杂的操作流程，涉及多个关键组件的协同工作。理解这些组件的工作原理和交互方式，对于优化数据库性能、排查问题和设计高效的数据库应用至关重要。

通过Buffer Pool的精心设计（LRU链表、Flush链表、Free链表），MySQL实现了高效的内存管理。通过Undo Log实现事务回滚和MVCC，通过Redo Log确保数据持久性，通过Binlog支持复制和恢复，通过二阶段提交保证数据一致性，MySQL构建了一个可靠而高效的数据库系统。

Redo Log的循环写入（write pos和checkpoint）与Binlog的追加写入是两种不同的日志管理机制，它们各自发挥着重要作用，共同保障了MySQL的数据安全和性能。

知识笔记
MySQL
License:  CC BY 4.0