DBA 同事说,MySQL 可以恢复到半个月内任意一秒的状态,惊叹的同时也好奇,到底是怎么做到的呢?
一条查询SQL语句的执行过程是经过连接器(查询缓存不做考虑)——>分析器——>优化器——>执行器后到达存储引擎调用接口。那么,一条更新语句是如何执行的呢?
连接器连接到数据库server,由于要更新所以查询缓存会被清空(这也是我们不用查询缓存的原因),然后到分析器分析语法词法,再到优化器,优化器决定索引的选择(这个地方很有趣,优化器会根据统计信息来选择索引,所以这个地方不一定会使用我们给的索引字段,当然我们也可以强制使用某个索引),然后到执行器执行,找到这一行进行更新。
与查询流程不一样的是,更新流程涉及到两个重要的日志:redo log 和 binlog
1.redo log
随机磁盘IO是数据库开销最大的块之一,每次更新纪录到磁盘都是一次随机IO,为什么随机IO会造成性能问题这里不多说。为了避免每次更新都去写磁盘,InnoDB会把每次操作的纪录先写到log文件里,因为是顺序写,所以性能上收益了很多。然后在空闲时再按照日志的逻辑顺序更新磁盘。整个过程,其实就是 MySQL 里经常说到的 WAL 技术,WAL 的全称是 Write-Ahead Logging,它的关键点就是先写日志,再写磁盘。而这个日志就是redo log
InnoDB的redo log固定大小,可设置为一组四个文件,每个文件1G,然后循环写,如图:
writ pos是当前纪录的位置一边写一边后移,知道写到3号文件末尾就又回到0号文件开头check point是擦除纪录的当前位置,writ pos和check point之间绿色的空间就是可以写日志的地方。当writ pos赶上了check point的时候说明redo log已经写满,这个时候就是停下来不更新了,等check point擦除一段。
有了redo log,InnoDB异常关闭时就不用怕关闭之前更新的数据丢失,这个能力叫做crash safe
2.binlog
- binlog叫做归档日志,是MySQL Sever自己的日志。在没有InnoDB的时候,MySQL 的存储引擎是MyISAM,而MyISAM没有crash-safe的能力。
- binlog是MySQL Sever自己的日志,所有存储引擎都可以用,redo log是InnoDB的日志。
- redo log是物理日志,纪录的是在某个数据页上做了什么修改,binlog是逻辑日志,纪录的是语句的原始逻辑比如给某行某个字段加一
- redo log是循环写的,有写满的时候,binlog可追加写,一个文件写到一定大小的时候就换下一个继续写。
update语句的流程:
上面说更新语句的更新流程,最后执行器找到要修改的行,然后执行修改。具体流程如上图所示。
- 执行器先找到ID=2这一行,ID是主键索引,如果ID=2的数据页在内存中就返回给执行器,如果不在内存就将该数据页加载到内存中然后返回给执行器。
- 执行器拿到存储引擎返回的行数据,执行更新,得到新的行数据,然后调存储引擎接口写入。
- 引擎将新得到的行数据写入内存,并更新redo log,此时redo log是Prepare阶段,,告知执行器随时可以提交事务。
- 执行器生成这个操作语句的执行逻辑写binlog到磁盘
- 执行器调用存储引擎的接口提交事务,存储引擎将写好的redo log改成commit状态,从此更新完成。
3.两段提交
由上面的步骤可以看出,存储引擎将redo log的写入拆成两个步骤,Prepare阶段和commit阶段。这样做的目的是保证redo log和binlog的逻辑一致。由于两个日志拥有独立的逻辑,那么如果不是两段提交,逻辑不一致会导致什么情况发生呢?
先写redo log 后写binlog
假设redo log写完了,binlog还没写完的时候发生了crash,这个时候binlog还没来得及写这个更新语句的逻辑,就是压根没这个纪录,因此备份库恢复的时候发现还是修改之前的纪录与原库不一致。
先写binlog 后写redo log
binlog写完,redo log写的时候crash了,按照binlog恢复的时候发现多了一条操作。由于原库的事务还没提交,也跟原库不一致。