目的

这片文章主要是为了自己能够理清mysql的运行原理写的，最近工作上有一些mysql的优化又来支撑更多的业务流量，了解清楚mysql的原理，更有利于技术选型。

1.mysql 基本的执行流程总结

1.1 mysql数据库构成

平时用的mysql其实基本上就是两个模块了来构成的,server层和存储引擎层。server层和存储引擎层。server层负责管理用户链接，语句验证和分析，优化执行计划，最后调用存储引擎接口进行数据的调用。而存储引擎则比较单一的负责数据的调用。两者各司其职。

• 连接器
  连接器其实主要负责的就是跟客户端建立链接，建立和验证用户权限，维持和管理用户链接。连接器在验证通过用户的连接合法之后，直接就把用户的权限获取过来了，可能是保存在当前的用户连接会话之中，这样就导致了，即使管理员更改了用户权限，也不会影响当前的用户会话，之后断开重连重新获取用户权限之后，修改的用户权限才会生效。客户端默认连接时常是八小时，八小时用户无动作之后会断开连接。
• 查询缓存（mysql8.0之后的版本将缓存层删除）
  查询缓存其实是将查询过的语句和结果集直接缓存在了内存中，但是如果查询条件或结果集字段变化就会无法命中，如果对缓存语句的表做了任何DML（增删改）操作，也会直接将这张表的所有缓存直接失效，所以基本上缓存命中比较低失效率比较高，8.0之后直也是直接把这个模块删掉了。
• 分析器
  分析器其实主要做了两个工作。
  • 词法分析：根据字符串构建语法树，识别字符串是什么，代表什么。
  • 语法分析：根据构建的语法树看看你的sql是不是合法的。
• 优化器（待补充）
  优化器其实是一个选择索引的过程，优化执行逻辑，多表关联的时候决定哪个表作为驱动表，或者连接顺序等。其实比较迷的一点是，并不清楚他到底是怎么进行索引选择的，所以这个待补充。
• 执行器
  首先会验证当前会话用户是不是对操作表具有操作权限，有的话会先进性打开表的操作。
  • 无索引读取：会一条一条的调用执行引擎的接口，获取数据到内存（其实是mysql的缓冲区），然后判断时候符合筛选条件，如果符合那么这一条会加入到结果集中，然后再取下一条，直到最后一条。
  • 有索引读取：应该是会将优化器选择出的索引和sql一并传入存储引擎，然后存储引擎通过指定的索引，找出对应的记录返回内存，然后执行器会判断是否符合条件，符合则加入结果集，否则继续调用下一条记录。

1.2 语句执行过程

• select 语句
  上面描述mysql各个功能模块的时候其实就已经把执行的流程说清楚了。

• update 语句
  update语句相对于select会复杂一些，主要设计到两个日志模块的操作binlog和redolog。但是，其实还会涉及到一个MVCC多版本并发控制的一个日志，也就是undolog。

  • redolog：
    上面描述的查询的过程，如果要更新的话，那么肯定要先找到需要更新的记录，那么如果每次都是查出来在内存中进行更改，然后立刻同步到磁盘的话，其实会降低执行的效率，需要等待IO；如果直接再内存中先更改数据，然后写入redolog的日志文件中，然后等到后面再去同步磁盘，会提高执行的效率。redolog其实是类似于一个环形队列的概念，是可以重复利用的。像下面这个图，绿色的部分就是可以写入区的大小，如果写满了，那么这个时候就需要根据redolog，将内存中的数据，同步到磁盘里面去，这样才可以继续利用redolog记录数据变更。redolog其实记录的是针对具体数据页的操作，而不是记录的执行的sql。这个其实就是WAL（write-ahead logging）技术，innodb的redolog是固定大小的，比如可一个配置4个日志文件，一个日志文件大小设置为1GB，那么整体就可以容纳4个GB的数据写入。有时数据库的抖动（sql比平时执行的慢），就有可能是redolog已经写满，从而进行内存数据的脏页写入磁盘的过程导致的。redolog是重做日志，提供前滚操作。这句话的意思其实是说可以通过redolog来恢复磁盘数据，保证数据的crash-safe能力。直接从物理层面恢复数据，而不是binlog通过记录sql，重新运行sql来进行的数据恢复。

    

    • InnoDB 有 buffer pool（简称bp）。bp 是 物理页 的缓存，对 InnoDB 的任何修改操作都会首先在 bp 的 page 上进行，然后这样的页面将被标记为 dirty 并被放到专门的flush list 上，后续将由专门的刷脏线程阶段性的将这些页面写入磁盘。这样的好处是避免每次写操作都操作磁盘导致大量的随机 IO，阶段性的刷脏可以将多次对页面的修改 merge 成一次IO 操作，同时异步写入也降低了访问的时延。

    • 然而，如果在 dirty page 还未刷入磁盘时，server非正常关闭，这些修改操作将会丢失，如果写入操作正在进行，甚至会由于损坏数据文件导致数据库不可用。为了避免上述问题的发生，Innodb 将所有对页面的修改操作写入一个专门的文件，并在数据库启动时从此文件进行恢复操作，这个文件就是 redo log file。这样的技术推迟了 bp 页面的刷新，从而提升了数据库的吞吐，有效的降低了访问时延。带来的问题是额外的写 redo log 操作的开销（顺序 IO，比随机 IO 快很多），以及数据库启动时恢复操作所需的时间。

  • undolog：
    和redolog一样其实也是存储引擎层维护的，undolog是回退日志，提供回滚操作。undo log 用来回滚行记录到某个版本。undo log 一般是逻辑日志，根据每行记录进行记录。保证事务的原子性，innodb使用它来实现MVCC.Undo 记录某 数据 被修改 前 的值，可以用来在事务失败时进行 rollback。Redo 记录某 数据块 被修改 后 的值，可以用来恢复未写入 data file 的已成功事务更新的数据。

    • Redo Log 保证事务的持久性
    • Undo Log 保证事务的原子性

    比如某一时刻数据库DOWN机了，有两个事务，一个事务已经提交，另一个事务正在处理。数据库重启的时候就要根据日志进行前滚及回滚，把已提交事务的更改写到数据文件，未提交事务的更改恢复到事务开始前的状态。即，当数据库 crash-recovery 时，通过 redo log将所有已经在存储引擎内部提交的事务应用 redo log 恢复，所有已经 prepared 但是没有 commit 的 transactions 将会应用 undo log 做 roll back。

    为什么不能只用redolog或者undolog？

    • 假设只有 undo-log：那么就必须保证提交前刷脏完成，否则宕机时有些修改就在内存中丢失了，破坏了持久性。（这样带来了一个问题，那就是前面提到的性能差）
    • 假设只有 redo-log：那么就不能随心所欲地在事务提交前刷脏，即无法支持大事务。（假如、某张表有 100 亿的 8 字节整数数据，就算不考虑其他东西带来的损耗，光 update 整张表至少要消耗 80G 的内存。如前所述，有了 undo-log，就可以随便刷脏。）

  • binlog：
    binlog 是 mysql server 层维护的，跟采用何种引擎没有关系，记录的是所有引擎的更新操作的日志记录。最开始没有第三方存储引擎开发的时候其实mysql只有binglog，这个历史原因导致了binlog其实只是逻辑层面的。redo/undo 记录的是 每个页/每个数据 的修改情况，属于物理日志+逻辑日志结合的方式（redo log 是物理日志，undo log 是逻辑日志）。binlog 记录的都是事务操作内容，binlog 有三种模式：Statement（基于 SQL 语句的复制）、Row（基于行的复制） 以及 Mixed（混合模式）。不管采用的是什么模式，当然格式是二进制的。redo/undo 在 事务执行过程中 会不断的写入，而 binlog 是在 事务最终提交前 写入的。也就是二阶段提交。

  了解了各个日志大概是干什么的 下面说说update流程:

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

为什么需要binlog和redolog的两阶段提交？

假如中午十二点误删了一个表，假如前一天备份了全量库，那么可以现在测试环境恢复备份库，然后把生产的binlog拿下来，重放到十二点的状态，然后再把这个库同步到线上就完成了。假如binlog不一致，binlog比redolog超前，那么其实相当于多运行了一个sql，如果redolog比binglog超前，那么其实相当于少运行了一个sql，因为redolog在物理层面上已经进行数据页的更新，所以需要进行二阶段提交保证数据的一致性。

1.3 MVCC多版本并发控制

上面已经说清楚各种日志以及语句执行流程了，现在来看看innodb如何通过undolog来实现事务并发控制的。下面的说法是基于可重复读隔离级别的。

对于数据库事务来说，新开启一个事务，数据库会维护一个递增的事务id，tx_id。每一条数据上其实是有隐藏字段会记录更新了这条数据的最新的id。如图假如k=1这条记录最新的tx_id=10。15这个事务，将k更新成10，17将k更新成11，25将k更新成22。中间这个U1，U2，U3的过程其实就是undolog记录的过程，更新前的值是什么。
按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。

• 活跃事务组：用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。
• 高水位：当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。
• 低水位：数组里面事务 ID 的最小值记为低水位。
• 一致性视图: 活跃事务组和高水位就构成了一致性视图（read-view）

如上图所示，假如有一条线程tx_id为19的事务开始了，这个时候数据库会创建一个活跃数组，假如现在活跃未提交的事务tx_id为18 20 21，那么当前线程事务的低水位就是18，而此时假如线程最新创建的一条事务的id为25，也就是把k变成22的已提交事务id，那么当前线程事务的高水位就是26，那么18之前的所有事务id都已经提交。 所以现在tx_id=19的事务，看到的这条数据发现它的row_tx_id=25在未提交事务集合里（也就是说未提交事务集合包含了活跃事务数组，也包含了从低水位到高水位之间已经提交的的事务），那其实对于tx_id=19的事务来说，其实这条数据的值是22。 刚才说的是tx_id=25在tx_id=19启动的时候已经提交了，还有一种情况就是，假如tx_id=25在tx_id=19启动的时候还没创建，后面才创建的，那当前这条数据的tx_id=25其实是红色区域的，也就是高水位之上，那么tx_id=19也就是当前线程事务，看到这条数据的版本，需要根据undolog的记录，向前回滚，往前回滚一个，发现前面的版本的tx_id=17,处于低水位之下，是可见的，那么对于tx_id=19看到的k值就是11，一切的操作也都是在这个版本上进行的。

1. 版本未提交，不可见；
2. 版本已提交，但是是在视图创建后提交的，不可见；
3. 版本已提交，而且是在视图创建前提交的，可见。

可重复读和读提交

1. 在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；
2. 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。