02、SQL的执行过程 - 图2查询SQL的执行流程

sql查询过程

基本流程

  1. 通过连接器连接到数据库（验证身份、表权限）
  2. 查询缓存，去缓存中查看之前是不是执行过这个sql
    1. 查询缓存：key-value形式，key：sql语句、value：执行结果
    2. 任何对表的更新操作（update、delete），这个表相关的查询缓存都会失效
  3. 分析器，解析sql字符串、识别关键字，知道这个sql想干什么，分析是否有语法错误
  4. 优化器，优化sql语句执行效率，关联查询用哪个表做主表，使用哪个索引
  5. 执行器，通过分析器知道sql想做什么操作，执行器执行sql
    1. 判断是否有表查询权限
    2. 根据表存储引擎定义，调用存储引擎定义的接口
    3. 从执行引擎中获取结果集，返回给客户端

更新SQL的执行过程

更新语句还涉及两个重要的日志模块：redo log（重做日志）和binlog（归档日志）

crash-safe原理解析

redo log

为什么需要redo log

  1. 如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高。为了解决这个问题，MySQL就采用了redo log来提升更新效率
  2. WAL，Write-Ahead Logging，先写日志，再写磁盘

redo log的作用

  1. 具体来说，当有一条记录需要更新时，InnoDB存储引擎就会先把记录写到redo log里面，并更新内存，这个时候更新就算完成了
  2. 同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候完成的

redo log原理

重做日志，InnDB存储引擎特有的日志，记录的是未刷盘的日志

InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件大小1G，那么这个redo log总共可以记录4GB的文件。从头开始写，写到末尾就又回到开头循环写，如下图所示
02、SQL的执行过程 - 图3

  • write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。check point是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件
  • write pos和check point之间的表示的是还空着的部分，可以用来记录新的操作。如果write pos追上check point，表示redo log已经记满了，这时候不能再执行新的操作，需要停下来擦除一些记录，把check point推进一下

有了redo log，InnoDB就能保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe

binlog

redo log是InnoDB存储引擎层特有的日志，而Server层也有自己的日志，就是binlog（归档日志），binlog记录的是全量日志

为什么有了redo log,还需要binlog

最开始MySQL里没有InnoDB存储引擎。MySQL自带的存储引擎是MyISAM，但是MyISAM没有crash-safe能力，binlog只用于归档。而InnoDB是另一家公司以插件的形式引入MySQL的，既然只依靠binlog是没有crash-safe能力的，所以InnoDB使用另一套日志系统——redo log实现crash-safe能力

为什么binlog不能实现crash-safe？

  1. binlog中加入check point，数据库故障重启后，binlog的check point之后的sql都重新执行一遍，这样binlog耦合的功能太多
  2. binlog是追加写，crash时不能判断binlog中哪些内容已经写入到磁盘、哪些还没写入，没办法判断哪些内容需要恢复，并且binlog记录的是逻辑日志，重复执行会导致数据错误；redo log是循环写，check point和write pos之间的内容就是还未写入磁盘的

binlog和redo log的不同

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

基本流程

1、连接器

连接数据库

2、查询缓存

清空这个表的全部查询缓存

3、分析器

分析sql，知道这是一条更新语句

4、优化器

决定使用哪个索引、哪张表做主表

5、执行器具体操作

调用存储引擎接口执行具体更新逻辑

现在再看执行器和InnoDB执行update T set c=c+1 where ID=2;语句的过程（图中浅色表示在InnoDB引擎内部执行，深色表示在执行器中执行）：
02、SQL的执行过程 - 图4

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

两阶段提交

执行器最后三步操作有点绕，将redo log的写入拆成了两个步骤：prepare和commit，这就是两阶段提交

MySQL的全量备份和增量备份

为什么需要两阶段提交

为了让两份日志之间的逻辑一致
案例：

怎样让数据库恢复到半个月内任意一秒的状态？ 我们知道，binlog会采用“追加写”的方式记录所有的逻辑操作，如果DBA承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有binlog，同时系统会定期做整库备份（定期：取决于系统重要性，可以一天一备，也可以一周一备） 当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：

  • 首先，找到最近一次的全量备份，从这个备份恢复到临时库
  • 然后，从备份时间点开始，将备份的binlog依次取出来，重放到中午误删表之前的那个时刻
  • 这时候，临时库数据和误删操作前的线上库数据就一致了，再按需要恢复线上库即可

以update T set c=c+1 where ID=2;为例，假设ID=2的行，字段c的值为0，执行update语句过程中在写完第一个日志后，第二个日志还没写完就发生了crash，会出现什么情况呢？

  1. 先写redo log再写binlog。redo log写完，binlog还没写完，MySQL异常重启
    1. redo log写完之后，即使系统崩溃，数据仍能恢复，恢复后这一行c=1
    2. binlog没写完就crash了，这是binlog里并没记录这个语句。后面去备份日志时，存起来的binlog里没有这个加1的逻辑。使用该binlog来恢复临时库（扩容，增加备份库）的话，恢复出来的这一行c=0，与原库的值不同
  2. 先写binlog后写redo log。binlog写完crash
    1. redo log还没写，crash恢复后这个事务无效，这一行c=0
    2. binlog记录了“把c的值从0变成1”这个日志，后面用binlog来恢复的时候就多出一个事务来，恢复后这一行c=1，与原库值不同

所以，如果不使用“两阶段提交”，可能就会导致数据库的状态和使用它的日志恢复出来的库状态不一致

恢复临时库场景

  1. 误操作恢复临时库
  2. 扩容时，新增备用库增加系统读能力（全量备份+binlog），这时候的不一致会导致线上主从库不一致

所以，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致