0x02-日志系统-一条SQL更新语句是如何执行的?

/ MySQL / 0 条评论 / 296浏览

通过一条更新语句学习MySQL的日志模块

简述执行过程


image

--建表
mysql> create table T(ID int primary key, c int);

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

执行更新的过程和查询过程是类似的,也会经过包括连接器、清空跟这个表有关的缓存、分析器、优化器、执行器。不同的是,更新涉及两个重要的日志模块。redo log(重做日志) 、binlog(归档日志)

MySQL的两大日志模块

  1. redo log

    • WAL(Write-Ahead-Logging): 先记录日志(redo log),再写入磁盘。redo log大小固定的。比如可以配置为一组4个文件,每个大小是1gb。
    • write pos 是当前记录的位置,一边写一边后移
    • checkpoint 是当前要擦除的位置,往后移动且循环,擦除前更新记录到数据文件
    • checkpoint和write pos之前是可以用来记录新的操作,如果write pos追上checkpoint,则需要等待擦除一些空间才能继续执行更新操作
    • crash-safe redo log可以保证数据库发生异常重启也不会丢失数据
    • redo log是InnoDB引擎特有的日志

image

  1. binlog

    • Server层日志,binlog是归档日志
    • MyISAM没有crash-safe能力,binlog日志只能用于归档
  2. redo log VS binlog

    • redo log是InnoDB引擎特有的;binlog是Server层实现的,所有引擎都可以使用
    • redo log是物理日志,记录的是“在某个数据页上做了什么修改”;binlog是逻辑日志,记录的是这个语句的原始逻辑,比如“给ID=2这一行的c字段加1”
    • redo log是循环写的,固定4g空间;binlog是可以追加写入的

Update执行流程图

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

浅色标识InnoDB内部执行,深色为执行器内支线

image

  1. 执行器先找引擎取ID=2这一行。引擎通过树索引找到该行。如果在内存中,返回给执行器;否则先从磁盘读入,然后返回
  2. 执行器进行逻辑运算+1,调用引擎执行,写入新的数据
  3. 引擎将数据更新到内存中,将操作记录写入redo log,此时redo log为prepare状态,通知执行器
  4. 执行器生成这个操作的binlog,写入磁盘
  5. 执行器调用引擎的提交事务接口,引擎将redo log改成commit状态

两阶段提交

更细流程里出现了redo log两阶段提交,原因呢?考虑服务crash情况,假设服务只能写完一个log

  1. 如果先写redo log后写binlog

    binlog没有记录,但是服务重启后通过redo log恢复了数据。如果后续需要binlog恢复其他库,则redo log记录了,但是binlog没记录的部分将还原失败

  2. 先写binlog 后写入redo log

    服务记录了binlog,但是重启后没有恢复数据

MySQL的时光机

binglog会记录所有的逻辑操作,并且采用“追加”方式。通过binlog可以恢复数据

  1. 找到最近的一次全量备份,恢复到临时库
  2. 从备份的时间点开始,将备份的binlog依次取出还原

小结

  1. redo log保证服务crash-safe能力,innodb_flush_log_at_trx_commit=1表示redo log持久化到硬盘
  2. sync_binlog=1表示每次事务的binlog都会持久化到磁盘,服务重启后不会丢失

问题

redo log如何设置大小和文件个数