mysql从0到0.1系列：SQL是怎么执行的(下)

1.update/insert 语句的执行流程

1.1 流程

1.1.1 insert 流程

在上一篇我们已经说过，一条SELECT语句执行需要的几个步骤，实际上update/insert语句也差不多，不同的是多出了redo log和bin log两个重要的部分。
这里再写一下整个流程：

1. 创建连接
   通过TCP/IP连接Mysql
2. 解析器
   这里mysql通过词法和语法解析会知道这里是update语句。
3. 优化器
   生成相应的执行计划，选择最优的执行计划
4. 执行器
   在这一步会去open table,如果该table上有MDL写锁，则等待。如果没有，则加在该表上加MDL读锁。

ps: 这里涉及到一个参数open_tables如果open_tables接近table_cache并且Opened_tables在增加，就代表mysql打开新表的时候会从磁盘读取，无法从缓存拿，也就是会重复的打开.frm文件

以上是mysql server层的执行步骤，我们主要关注引擎层做的事情。

1. 获取锁信息
   通过元数据信息(open_table的时候获取到的),去lock info里查出是否会有相关的锁信息，并把这条insert语句锁信息写入到lock info里
   

2. 判断数据页是否在innodb buffer中
   不在的话需要从磁盘中加载到innodb buffer中

3. 分配undo段，记录undo log

4. 记录undo log 产生的redo log
   这里产生的redo log 其实就是undo segment的改动

5. 对inndb buffer中的数据页进行更新
   这里是直接在内存中更新而不是磁盘。

6. 记录redolog
   inndb buffer中的修改记录到redo log buffer里

7. 插入binlog cache
   修改的信息，会按照event的格式,记录到binlog cache中。

8. 插入change buffer
   只有涉及到非聚簇索引的唯一索引并且数据页不在innodb_buffer中才会插入change buffer

9. 二阶段提交过程
   此时在sql中可以看成已经commit

   • prepare
     将binlog_cache里的进行flush以及sync操作
   • commit
     由于之前该事务产生的redo log已经sync到磁盘了（这里根据innodb_flush_log_at_trx_commit 设置的不同情况会不一样）。所以这步只是在redo log里标记commit。

以上就是整个插入流程,这里仅在mysql 5.6以及innodb引擎的环境下

1.1.2 update 流程

这里update 的流程和insert流程基本一样，暂不赘述，如果有疑问可以留言讨论。

1.1.3 整体流程图

2 change buffer

从1.0.x 的版本innodb 引入了change buffer 在之前的版本也称为insert buffer,change buffer 可以看作insert buffer 的升级。

2.1 作用

change buffer 最主要的功能就是加速非聚簇索引的操作，如果我们之前对数据库有了解就知道，其实影响数据库性能的很关键的一个因素就是磁盘的随机读，而mysql中大部分的优化其实都是在减少磁盘的随机读,这里的change buffer 也是这样。以一个插入过程为例，涉及到两个部分，聚簇索引的插入和非聚簇索引的插入

• 聚簇索引
  这里一般来说如果聚簇索引是自增主键的话，这里插入就是一个顺序写，就不会涉及到磁盘的随机读，因此很快的。当然如果你这里不设置为自增主键（UUID）就会产生磁盘的随机读，指定值也是。
• 非聚集索引
  非聚簇索引这里也涉及了两种情况，唯一索引和非唯一索引。
• 唯一索引
  唯一索引会回表判断是否冲突，所以这里一定是一个随机读的操作。
• 非唯一索引
  由于数据页的存放其实是按主键顺序存放的，所以当我插入非唯一索引的时候，其实也会产生随机读。不过有时候非唯一索引也可以是顺序的。

在某些情况下，辅助索引的插入依然是顺序的，或者说是比较顺序的，比如用户购买表中的时间字段。在通常情况下，用户购买时间是一个辅助索引，用来根据时间条件进行查询。但是在插入时却是根据时间的递增而插入的，因此插入也是"较为"顺序的。——《MySQL 技术内幕 innodb 引擎》

这里我们不妨思考一下，哪个步骤产生的随机读可以被优化呢，唯一索引由于需要判断是否冲突需要回表，好像并不能优化，而非唯一索引只是因为B+树的特性导致会产生随机读，这里也许可以优化。
change buffer 正是这样做的，对于非唯一索引的操作其实是先放在change buffer中而不是直接进入数据页。这里的过程是这样的。判断目标页是否在innodb buffer中，如果在，直接操作innodb buffer，否则就直接放在change buffer 中，这样就不需要从磁盘随机读页数据到innodb buffer中。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。

2.2 原理

首先，我们需要知道的是，change buffer 的结构实际上是一颗B+树，并且是存放在共享表空间ibdata1中，这里有个容易混淆的概念，change buffer 也是一个数据页，所以也会被加载到inndb_buffer中，并且持久化在ibdata1，所以会存在一部分数据在inndb_buffer中的情况，同时这个change buffer页的改动记录在redo log里。

2.2.1 结构

由于change buffer 是一个B+树的结构，所以这里也区分了叶子节点和非叶子节点。

• 子节点
  由space,marker,offset三个字段组成

  1. space
     记录的是表空间id,每个表都会有一个唯一的space。
  2. marker
     保留字段，兼容老版本的insert buffer。
  3. offset
     数据所以在页的偏移量。
     

• 叶子节点
  除了非叶子节点的三个字段之外，还多了metadata,以及实际插入的字段。

  1. metadata
     保存了三个字段分别是：
     IBUF_REC_OFFSET_COUNTER：数器，用来排序记录，以进入insert buffer的顺序
     IBUF_REC_OFFSET_TYPE：操作类型(ibuf_op_t)
     IBUF_REC_OFFSET_FLAGS：标志位，当前只有IBUF_REC_COMPACT

  

2.2.2 merge过程

那么通过上面的内容我们可以知道，change buffer，其实就相当于一个缓冲池的概念，那么既然是缓冲池就有一个向数据页merge的过程，也就是合并到真正的非聚簇索引中来。
一般来说，触发merge过程主要有这几种情况：

1. 加载非聚簇索引页到innodb buffer中
2. Master Thread定时merge
3. buffer bitmap 判断辅助索引页空间不足1/32

其中第三点涉及到一个buffer bitmap的概念，这是用来标记辅助索引页空间的。建议大家可以直接去看看《MySQL 技术内幕 innodb 引擎》，这里不赘述了。

2.3 一些问题

2.3.1 如何设置 change buffer

change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为50的时候，表示 change buffer 的大小最多只能占用innodb buffer的50%。

2.3.2 change buffer的应用场景

由于读操作会触发change buffer的merge过程，所以当读很多的时候，change buffer的效果不会很明显。并且会产生change buffer 的维护代价。因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

3 redo log

前面我们多次提到redo log,可以发现，基本上所以会写磁盘的操作都会先写redo log,这是因为，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。

IO成本就是寻址时间和上线文切换所需要的时间，最主要是用户态和内核态的上下文切换。我们知道用户态是无法直接访问磁盘等硬件上的数据的，只能通过操作系统去调内核态的接口，用内核态的线程去访问。 这里的上下文切换指的是同进程的线程上下文切换，所谓上下文就是线程运行需要的环境信息。 首先，用户态线程需要一些中间计算结果保存CPU寄存器，保存CPU指令的地址到程序计数器（执行顺序保证），还要保存栈的信息等一些线程私有的信息。 然后切换到内核态的线程执行，就需要把线程的私有信息从寄存器，程序计数器里读出来，然后执行读磁盘上的数据。读完后返回，又要把线程的信息写进寄存器和程序计数器。 切换到用户态后，用户态线程又要读之前保存的线程执行的环境信息出来，恢复执行。这个过程主要是消耗时间资源。

3.1 原理

redo log由两部分组成：

1. redo log buffer
2. redo log file

其中redo log buffer 是在内存中的，如果redo log 在没有写入file中的时候断电，其实redo log buffer的数据就会丢失。但是这种情况其实极少发生，redo log buffer是接近实时地写入磁盘，当会话发出commit语句时，会实时执行redo log buffer写操作。（注意，这里并不是说把更新的数据写入磁盘了，而是把redo log 写入了）

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面。

由于redo log是固定大小的，所以实际上在redo log 快要写满时也会对前面的数据进行刷盘。并且 redo log 是循环写的，所以是一个向后写入，向前刷盘的过程。这个也称作checkpoint 技术。

3.2 配置

innodb_flush_log_at_trx_commit={0|1|2} ， 指定何时将事务日志刷到磁盘，默认为1。

• 0表示每秒将"redo log buffer"同步到"os buffer"且从"os buffer"刷到磁盘日志文件中。
• 1表示每事务提交都将"redo log buffer"同步到"os buffer"且从"os buffer"刷到磁盘日志文件中。
• 2表示每事务提交都将"redo log buffer"同步到"os buffer"但每秒才从"os buffer"刷到磁盘日志文件中。

3.3 WAL

WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。不仅仅是MySQL，很多涉及到写磁盘的系统都会使用这一技术包括，zookeeper，ES等。

3.3.1 WAL的优点

1. WAL 的优点读和写可以完全地并发执行，不会互相阻塞（但是写之间仍然不能并发）。
2. WAL 在大多数情况下，拥有更好的性能（因为无需每次写入时都要写两个文件）。
3. 磁盘 I/O 行为更容易被预测。使用更少的 fsync()操作，减少系统脆弱的问题。提升性能

4 undo log

上面我们说到的redo log 可以称为重做日志，因为redo log 是记录数据页的改动，所以可以根据redo log 重做数据页。而undo log则是回滚日志，当一个事务失败之后，就可以通过undo log 进行回滚。在事务篇中再详细说。

5 bin log

除了redo log 之外，mysql还有一个比较重要的日志，bin log(归档日志)。
redo log与bin log的不同点：

1. redo log 是innodb特有的日志，而bin log 是mysql server层的日志。
2. redo log 记录的是数据页的修改，而bin log 记录的是SQL 的逻辑。
3. redo log 是循环写，而bin log 是追加写。

binlog有两种模式，statement 格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。

6 二阶段提交

两阶段提交主要是为了保证crash-safe，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

---

个人博客

西西弗的石头

作者水平有限，若有错误遗漏，请指出。

参考文章

1.详细分析MySQL事务日志(redo log和undo log)

2.MySQL 45讲

参考书籍

1. 《MySQL 技术内幕 innodb存储引擎》