详解MySQL更新语句的执行流程


    目录
  • 前言
  • SQL 的更新流程
  • Redo log 重做日志
  • Binlog 归档日志
    • redo log 和 binlog 设置

    前言
    在这篇文章中，小鱼将介绍更新语句的执行流程，从中我们又能学到什么呢？
    SQL 的更新流程
    我们先创建一张表作为演示表，作为演示表只需要一个主键、一个额外字段就可以了。下面是演示表的创建语句：
    
CREATE TABLE test(ID int primary key, age int);

    如果我们需要将 ID=2 目标值自增 1，更新的 SQL 语句如下。
    
UPDATE test SET age=age+1 WHERE ID=2;

    更新目标值时，得先查找的该行数据，所以也会执行SQL查询语句的流程。
    
  • 在执行语句前，连接上数据库（连接器）。
  • 因为是更新语句，涉及到更新目标表的查询缓存会失效，所以该语句会把 test 表所有缓存结果都清空。
  • 分析器通过词法分析和语法分析知道该语句为更新语句。
  • 优化器决定要使用 ID 索引。
  • 最后，执行器负责更新的具体执行，根据索引找到目标行，再执行更新。

    更新语句流程与查询语句流程不一样的地方在于日志模块，更新语句涉及到两个十分重要的日志模块——redo log（重做日志）和 binlog（归档日志）。
    Redo log 重做日志
    ??Redo Log??称为重做日志，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性。
    mysql 数据是被持久化写进磁盘的，每次更新也需要找到目标数据，在进行修改，每次更新都执行一遍该操作，这个过程的 IO 成本是比较高的。
    为了解决这个问题，MySQL 采用了先写日志，空余时间再写磁盘的思路来提升更新效率。即是 WAL 技术（预写式日志，WAL 的全称是 Write-Ahead Logging）。
    具体来说，当有更新语句执行的时候，InnoDB 引擎会先把更新记录写到 redo log 日志里，并更新内存，这个时候已经完成更新（内存上），实际磁盘上的数据尚未更新。等适当的时候（通常是系统空闲的时候），InnoDB 引擎会将这个操作记录（redo log 中记录的更新语句）更新到磁盘。
    写 redo log 的流程如下：
    
  • 先将目标原始数据从磁盘中读入内存中来，修改数据的内存拷贝。
  • 生成一条重做日志并写入 Redo Log Buffer，记录的是数据被修改后的值。
  • 当事务 commit 时，将 Redo Log Buffer 中的内容采用追加写的方式刷新到 Redo Log File。
  • 定期将内存中修改的数据刷新到数据文件（磁盘）中。

    这样做还有一个问题，InnoDB 的 redo log 日志的大小是固定的，它设计的是循环的，即日志文件写满后会覆盖掉最先的记录（从头开始写，写到末尾就又回到开头循环写）。
    
    
  • write pos：当前记录的位置，一边写一边后移，当写到第 3 号文件末（末尾）时会回到 0 号文件（开头）开头。
  • checkpoint：当前要擦除的位置，同样是往后推移并且循环的，擦除记录前要把记录更新到数据文件（更新到磁盘里）。
  • write pos 和 checkpoint 之间：redo log 日志文件还空着的部分，可以用来记录新的操作。
    • 如果 write pos 追上 checkpoint，表示 redo log 日志文件写满了，此时不能再执行新的更新操作，会将记录写入数据文件，并执行擦除记录，推进 checkpoint 位置。

    试想：对于已经写入 redo log 的记录，在数据库异常重启后，能否恢复？
    mysql 重启后，已经写入 redo log 的记录不会丢失，这个能力也称为 crash-safe。
    crash-safe 还有个重要的日志——Binlog 日志。
    Binlog 归档日志
    MySQL 架构分为 Server 层和存储引擎层，redo log 是存储引擎层产生的日志，而 server 层也有日志——Binlog 归档日志。
    两者的区别在于以下几点：
    
  • Redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层产生的，任何引擎都存在该日志。
  • Redo log 是循环写的，空间固定会用完，用完即从头开始写。binlog 是追加写，即 binlog 文件写到一定大小后会新建日志文件，不会覆盖掉以前的日志。
  • redo log 会不断记录，而 binlog 只有在事务提交的时候才记录。Redo log 是物理日志，详细记录了“在某个数据页上做了什么修改”（包含事务的过程操作）；binlog 是逻辑日志，记录的是语句的原始逻辑（对数据最终的影响）。

    譬如：一个事务对表做10万行的记录插入，在事务执行过程中，会一直不断的往 Redo Log 顺序写，而这个过程 Binlog 不会记录，直至这个事务提交的时候，才会写入到  Binlog 文件中。
    这两份日志存在的意义就是实现 crash-safe 能力。
    这两个日志文件结合起来，才真正实现了 crash-safe 能力，让 MySQL 既能保证事务的 ACID 属性，又能支持高效的数据复制和恢复能力。
    redo log 和 binlog 设置
    查看 redo log 和 binlog 设置
    
show variables like 'innodb_flush_log_at_trx_commit';
show variables like 'sync_binlog'

    
  • innodb_flush_log_at_trx_commit：
    • 设置成 0，在提交事务时，InnoDB 不会立即触发将缓存日志写到磁盘文件的操作，而是每秒触发一次缓存日志回写磁盘操作，并调用操作系统 fsync 刷新 IO 缓存。
    • 设置成 1 ，每次事务的 redo log 都直接持久化到磁盘，如此可以保证 MySQL 在异常重启后数据不会丢失。
    • 设置成 2，在每个事务提交时，InnoDB 立即将缓存中的 redo 日志回写到日志文件，但并不马上调用 fsync 来刷新 IO 缓存，而是每秒只做一次磁盘 IO 缓存刷新操作。
  • sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。对于需要高度数据持久性和不能承受数据丢失的系统，建议将 sync_binlog设置为 1。

    设置 redo log 和 binlog 配置
    可以在 MySQL 配置文件（通常是 my. Cnf 或 my. Ini）中设置这个变量。设置好后需要重启 mysl，使得配置生效。
    
[mysqld]
sync_binlog=1
innodb_flush_log_at_trx_commit = 1

    或者，也可以在 MySQL 运行时动态设置，但是这种变更只对新的会话有效，对于已经存在的会话，该设置直到会话结束才会生效。
    
SET GLOBAL sync_binlog=1;
SET GLOBAL innodb_flush_log_at_trx_commit = 1;
相关文章！