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里

  1. 判断数据页是否在innodb buffer中
    不在的话须要从磁盘中加载到innodb buffer中
  2. 调配undo段，记录undo log
  3. 记录undo log 产生的redo log
    这里产生的redo log 其实就是undo segment的改变
  4. 对inndb buffer中的数据页进行更新
    这里是间接在内存中更新而不是磁盘。
  5. 记录redolog
    inndb buffer中的批改记录到redo log buffer里
  6. 插入binlog cache
    批改的信息，会依照event的格局,记录到binlog cache中。
  7. 插入change buffer
    只有波及到非聚簇索引的惟一索引并且数据页不在innodb_buffer中才会插入change buffer
  8. 二阶段提交过程
    此时在sql中能够看成曾经commit
  9. prepare
    将binlog_cache里的进行flush以及sync操作
  10. 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存储引擎》