关于java:mysql从0到01SQL是怎么执行的下

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 存储引擎》