深入理解MySQL执行流程

MySQL是一种关系型数据库，因为是开源且是免费的，因此在企业级开发中被广泛的使用。许多开发者都能够很熟练的在业务中使用的CRUD操作，只关心操作语句产生的结果，忽略语句的执行流程，这篇文章我们会讲解MySQL的基础架构都包含什么，每个部分有什么样的作用，语句是怎么执行的。

MySQL架构

总体架构
先看一张图

由上图可知，MySQL可以主要分为连接层、服务层、引擎层和存储层，我们来详细讲解下每层的作用及特点
连接层：理解为客户端，主要完成一些类似于连接处理、授权等操作
服务层：完成大部分的核心服务功能，包括查询解析、分析、缓存、优化等，所有的跨存储引擎的功能也都是在这一层实现，包括触发器、存储过程和视图等
引擎层：负责MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信，不同的存储引擎功能有所不同。
存储层：负责在设备上存储数据，完成与存储引擎的交互。
这张图呈现的是MySQL的总体架构，在现实开发中，我们真正需要了解的是MySQL中服务层和引擎层上的知识点，因此，我们可以将总体架构修改为基础架构来更加细粒度的学习知识点。

基础架构

从图中可以看出，MySQL的基础架构主要可以分为Service层和引擎层，Service层包括连接器、查询缓存、分析器、优化器和执行器等，而引擎层负责数据的存储和提取，采用插件式的模式，可以包含多个引擎，如InnoDB、MyISAM等。
Service层
连接器：用于管理连接、权限的验证
查询缓存：如果要查询的数据在缓存中存在，则直接返回结果
分析器：对输入的SQL语句进行语法、词法的分析
优化器：SQL语句执行计划的生成、索引的选择等
执行器：操作引擎、返回结果等。
引擎层
引擎层的主要作用就是存储数据，提供读写的接口，现在最常用的引擎是InnoDB，在MySQL5.5.5版本后就成为了默认引擎，当然，我们也可以通过指定存储引擎的类型来选择别的引擎，在创建表的时候使用engine=memory来指定。

下面使用查询语句的方式详细讲解每个部分的作用。

查询语句的流程

连接器

连接器的作用是负责跟客户端建立连接、获取权限、维持和管理连接，连接的命令可以这么写

mysql -h$ip -P$port -u$user -p

连接的命令中的mysql是客户端工具，是用于跟服务建立连接的，在完成TCP握手后，连接器就会验证你身份，即命令中的用户名和密码。
NOTE：当与MySQL成功建立连接后，即使使用管理员账号重新对这个用户权限做出修改，也不会影响已经存在的连接权限，修改完成后，只有再次新建连接才会使用新的权限配置。
如果用户建立了连接，但是长时间未使用，连接器就会自动断开，时间参数是由wait_timeout控制的，默认为8小时。
如果用户建立连接后，客户端一直使用同一个连接进行持续的请求，这种连接称为长连接。与之相对的是短连接，短连接是每次执行很少的操作就断开连接，下次操作时就新建立一个连接。
那么，我们在连接时，是应该选择长连接还是短连接呢？，我们先看看它们各自的特点

长连接

客户端连接–创建socket认证连接–维护连接–数据传输–维护连接–数据传输…-关闭连接

短连接

客户端连接–创建socket认证连接–维护连接–数据传输–关闭连接
这两种连接方式都会占用系统的开销，所以说没有那种连接可以适合全部的场景，主要还是看客户端的行为，需要视情况而定，默认情况下可以是①在频繁与数据库通信，并且又非高并发的情况下，使用长连接更合适②大多是持久连接，大部分是sleep状态，或者系统是高并发的，可以选用短连接。

查询缓存

建立连接之后，就可以执行Select语句了。当MySQL拿到一个查询请求后，会先去缓存中查找，如果缓存中存在，则可以直接返回给客户端。
NOTE：数据在缓存中的是以key-value的形式存储的，在查询时查找key，返回value。但是在MySQL8.0之后，已经没有缓存了，这是因为缓存非常容易失效，只要对一个进行更新，这个表上的在缓存中的数据就会失效，在下次查询时才会重新进入缓存。
如果不在缓存中，怎进入分析器。

分析器

分析器对输入的语句进行词法分析，根据分析器的语法规则判断输入的语句是否满足MySQL语法，如果不满足，则会报错“You have an error in your SQL syntax”，如果满足，在进入优化器。

优化器

经过分析器，MySQL知道了你要做什么，但是在真正执行之前，该查询语句还需要经过优化器的处理，优化器的目的是生成一种合适的优化方案，如帮你选择合适的索引，决定连接表的使用顺序等。

执行器

执行器的目的就是执行语句，当开始执行的时候，会先判断用户对表T是否有查询权限，如过没有，则会返回没有权限的错误，如果有权限，则会根据选用的引擎提供的接口去执行语句，并将结果 返回给客户端。

更标题新语句执行

我们利用查询语句的流程学习了MySQL中的连接器、分析器、优化器、执行器和执行引擎等功能模块，如果我们我们的操作是更新，是否还是按照这样的流程来进行的呢？如果不同，有哪些不同点呢？
首先，可以肯定的是，更新的流程依旧会使用查询流程中的模块，但是与查询流程不同的是，更新流程会涉及到两个重要的日志模块，即redo log(重做日志)，binlog(归档日志)

redo log

在更新数据时，MySQL会存在这样一个问题，如果每一次更新操作都需要写进磁盘，然后磁盘找到相应的记录进行更新，这个过程中非常消耗IO资源，为了解决这样的问题，MySQL提出了WAL(Write-Ahead Logging)技术，即先写日志，在写磁盘。
具体更新操作为，InnoDB将操作记录写入到redo log，并更新到内存，最后InnoDB会在合适的时间将操作记录更新到磁盘。其中，上面的操作记录实际上是存放到redo log中的redo log buffer中，存入内存是buffer pool。
InnoDB中的redo log是固定大小的，比如可以分配一组4个文件，每个文件的大小都是1GB，那么redo log就可以记录4GB的数据操作，从同开始写，写到末尾后就开始循环。
write pos是记录的当前位置，边写边后移，目的是写入操作记录，当写到末尾处(3号末尾时)继续循环从0处开始写。check point是当前要擦除的位置，同样也是不断向后推移且循环的，目的是擦除操作记录。而write pos和check ponit之间的位置是表示可以写入记录的空间大小。
redo log有个非常重要的功能crash-safe，该功能保证了数据库发生异常重启后，可以根据redo log恢复到重启前的时刻，以此保证提交的记录不会丢失。
将记操作录写入redo log，实际上是写入redo log buffer中，那么每次生成写入的记录是否都立刻持久化磁盘呢？显然是不需要的，因为如果事务执行期间 MySQL 发生异常重启，那这部分日志就丢了。由于事务并没有提交， 所以这时日志丢了也不会有损失。

binlog

在MySQL，有两部分日志，一部分日志就是上文讲解的redo log(引擎层)，另一部分是binlog(业务层)，binlog被称为归档日志，我们可能会有疑问，为什么需要两份日志呢？
因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。
相比较redo log，binlog的写入逻辑比较简单，它是先将日志写入到binlog cache，当事务提交的时候，再把binlog cache写入到binlog中。因为一个事务的binlog是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。
系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。
从图中可以看到，每个线程都有自己的binlog cache，这些线程共享同一份binlog文件，图中的 write，指的就是指把日志写入到文件系统的binlog files，并没有把数据持久化 到磁盘，所以速度比较快。此内存是磁盘中文件系统申请的内存。图中的 fsync，才是将数据持久化到磁盘的操作。

我们比较下这两种日志有什么不同

①redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
②redo log 是物理日志，记录的是在某个数据页上做了什么修改；binlog 是逻辑日志，记录的是这个语句的原始逻辑。
③redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。追加写是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

下面我们以update操作来学习更新的流程
首先我们创建一个表T，主键为id，创建语句为

CREATE TABLE `T` (
  `ID` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARYq KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入一条语据后，并更行

INSERT INTO (id,c) VALUES ('2', '1')
UPDATE T SET c = c + 1 WHERE ID = 2;

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

图中将redo log拆成了两步，分别为prepare和commit，这就两阶段提交。
两阶段提交的目的是为了保证两份日志之间的逻辑一致

阶段1：InnoDB redo log 写盘，InnoDB 事务进入 prepare 状态
阶段2：如果前面prepare成功，binlog 写盘，那么再继续将事务日志持久化到binlog，如果持久化成功，那么InnoDB
事务则进入 commit 状态(实际是在redo log里面写上一个commit记录)

总结

通过一种特殊的xid，可以在两阶段提交的不同时刻发生异常重启时，将redo log和binlog联系起来，具体而言，崩溃恢复在两阶段提交不同时刻的判断规则如下

• 如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交
• 如果 redo log 里面的事务只有完整的 prepare ，则判断对应的事务 binlog是否存在并完整：通过xid来联系起来,如果是，则提交事务，否则回滚事务