MySQL架构与执行流程（一）

1.查询SQL是怎么执行的

• 客户端跟服务端建立连接，先进行身份认证（如账户、IP、密码是否正确），身份认证通过后，会从权限表中查出用户拥有的权限。
• 如果查询缓存配置状态开启，则先检查查询缓存，如果命中了缓存，并且验证用户权限满足，则直接返回缓存结果，否则进行下一阶段。
  MySQL内部自带了一个缓存模块，默认关闭，不推荐使用，且在8.0彻底移除，主要是因为MySQL自带的缓存的应用场景有限。
• 分析器进行 “词法分析” 和 “语法分析” ，生成 “语法树” ，对语法规则校验通过后，再进行权限验证，权限满足后，进行下一阶段。
• 优化器根据分析器生成的 “语法树” ，通过一系列优化策略，生成一个最优的执行计划。
• 执行器根据优化器生成的执行计划，调用存储引擎的API来执行查询。
• 将查询结果返回给客户端。如果查询可以被缓存，那么MySQL会同时将结果存放到查询缓存中。

2 MySQL架构体系总结

2.1 架构分层

总体上，我们可以把MySQL分为三层，跟客户端对接的连接层，真正执行操作的服务层，跟硬件打交道的存储引擎层。

2.1.1连接层

客户端要连接到MySQL服务器3306端口，必须要跟服务端建立连接，那么管理所有的连接，验证客户端的身份和权限，这些功能都在连接层完成。

2.1.2 服务层（Server）

主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog日志模块。

2.1.3 存储引擎

主要负责数据的存储和读取，采用可以替换的插件式架构，支持InnoDB、MyISAM、Memory等多个存储引擎，其中InnoDB引擎有自有的日志模块redolog 模块。

3 一条更新SQL是如何执行的

当我们执行一条 sql 更新语句时，你有没有想过，它的在 MySQL 系统内部是如何去执行的？当我们购买 MySQL 服务器时，你有没有考虑过为什么 MySQL 服务器内存大，硬盘为 SSD 的话比较好？（当然，这是废话，什么服务器，内存大，硬盘为 SSD 都好，但 MySQL 服务器要求还是比业务代码服务器要求高些）
以下我们以表 T 的 update sql 为例进行分析（默认InnoDB引擎）：

create table T(ID int primary key, c int);
update T set c=c+1 where ID=2; 

3.1 执行逻辑

一个 update 语句的执行逻辑为：
1、通过连接器连接 MySQL Server层；
2、连接器阶段初步分析为 update 交给分析器处理；
3、分析器详细分析 update 语句，进行词法、语法、语义检查，生成解析树以供优化器使用，并在交给优化器之前进行权限检查（precheck）；
4、优化器将解析树优化，生成最优执行计划，交给执行器执行；
5、执行器在执行 sql 时会再次检查权限，并调用引擎层提供的数据接口，对数据进行更新处理。

更新语句和查询语句不同之处在于，当我们执行更新时，对数据的敏感性是比较强的，我们需要保证数据更新正确，且万一的万一，MySQL 崩溃了，我们也能恢复数据，所以更新操作涉及到记录日志，这里的日志就是我们熟知的 MySQL Server层 的 binlog日志 和InnoDB引擎层的 redolog日志 。

所以，update时，在上面执行逻辑中的第5步执行器执行阶段的处理过程为：
1、执行器取ID=2的数据；
2、引擎判断数据页是否在内存中，没有则从磁盘读取到内存中，从内存中返回行数据；（内存大的服务器的好处）
3、执行器将该数据值加1，之后写入新行，通知引擎
4、引擎将新数据更新到内存中
5、引擎此时开始记录redolog，并将该记录置为prepare状态
6、执行器写binlog
7、引擎提交事务，并更新此行数据的redolog状态为commit
8、当MySQL空闲时，会将内存中的数据落盘。
流程图：【深色为执行器执行，浅色为引擎处理】

以上就是更新数据时，执行器和引擎做的一系列操作。

整个更新过程其实是AWL技术，即Write-Ahead Logging预写式日志。先写日志，再更新。

3.3 分析

我们可能会有疑问：为什么在binlog存在的情况下，我们还要额外引入redolog呢？

我们详解之前，先讨论下binlog和redolog是什么。
binlog是MySQL Server层提供的日志记录功能，所有引擎都能用，它是记录sql执行逻辑的，如给ID=2这一行的c字段+1，且binlog日志是追加记录的，当文件写到一定大小会新增一个新文件继续记录，属于全量日志。

redolog则是InnoDB独有的日志，其他引擎不可用，且redolog记录的是在某个数据页上做了什么修改，属于物理日志，只有InnoDB才能用，且是固定大小，循环记录的。

现在我们来分析为什么binlog和redolog并存的问题。
binlog和redolog是通过事件ID去关联的。
简单来说，binlog和redolog并存可以保证数据一致性，以及意外宕机时的数据恢复问题。也就是MySQL的crash-safe。

• 如果我们只记录binlog，那么我们只有操作记录，如果日志记录了，但是数据没更新到表里，当我们使用binlog恢复表时，会出现数据不一致。
• 单独记录redolog是不行的，因为redolog是循环记录的，会抹去旧的日志，不像binlog一样，是全量的。（资料显示，现在已经有在做redolog全量存储的第三方团队了，基于数据页的数据恢复一致性更强，且不用记两份日志）
• 如果先记录binlog再记录redolog的话，若写完binlog数据库崩了，此时没有记录redolog，当重启数据库时，因为redolog没记录，所以此事务操作无效，库中值不会改变，但是binlog中又有此条记录，导致使用binlog恢复数据时，会更新值，导致数据不一致。
• 如果先记录redolog再记录binlog的话，若写完redolog数据库崩了，此时没有记录binlog，重启数据库，虽然能通过redolog正确恢复到之前的数据更新，但是因为binlog没记录，之后备份恢复时，还是会导致数据不一致。

4 redo log、undo log、binlog

重做日志（redo log）、回滚日志（undo log）、二进制日志（binlog），其中，redo log 是物理日志，undo log 和 binlog 是逻辑日志，物理日志的恢复速度远快于逻辑日志。

4.1 redo log、undo log

innodb 事务日志包括 redo log 和 undo log，redo log 是重做日志，提供前滚操作，undo log 是回滚日志，提供回滚操作，其中前者保证事务的持久性，后者保证事务的原子性，两者可以统称为事务日志。

redo log：

• 生命周期：
  事务开始之后，就开始产生 redo log 日志了，在事务执行的过程中，redo log 开始逐步落盘，当对应事务的脏页写入到磁盘之后，redo log 的使命就完成了，它所占用的空间也就可以被覆盖了。

• 存储内容
  redo log 包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的，redo log 存储的是物理格式的日志，记录的是物理数据页面的修改信息，它是顺序写入 redo log file 中的https://qimok.cn。

• 落盘方式（将 innodb 日志缓冲区的日志刷新到磁盘）
  1，Master Thread 每秒一次执行刷新 Innodb_log_buffer 到重做日志文件
  2，每个事务提交时会将重做日志刷新到重做日志文件
  3，当重做日志缓存可用空间少于一半时，重做日志缓存被刷新到重做日志文件

undo log

• 生命周期
  事务开始之前，将当前事务版本生成 undo log，undo log 也会产生 redo log 来保证 undo log 的可靠性。当事务提交之后，undo log 并不能立马被删除，而是放入待清理的链表，由 purge 线程判断是否有其它事务在使用 undo 段中表的上一个事务之前的版本信息，从而决定是否可以清理 undo log 的日志空间。
• 存储内容
  undo log 存储的是逻辑格式的日志，保存了事务发生之前的上一个版本的数据，可以用于回滚。当一个旧的事务需要读取数据时，为了能读取到老版本的数据，需要顺着 undo 链找到满足其可见性的记录。
• 存储位置
  默认情况下，undo 文件是保存在共享表空间的，也即 ibdatafile 文件中，当数据库中发生一些大的事务性操作的时候，要生成大量的 undo log 信息，这些信息全部保存在共享表空间中，因此共享表空间可能会变得很大，默认情况下，也就是 undo log 使用共享表空间的时候，被“撑大”的共享表空间是不会、也不能自动收缩的。因此，MySQL5.7 之后的“独立 undo 表空间”的配置就显得很有必要了。

4.2 binlog

binlog 用于主从复制中，从库利用主库上的 binlog 进行重播，实现主从同步。用于数据库的基于时间点、位点等的还原操作。binlog 的模式分三种：Statement、Row、Mixed。

事务提交的时候，一次性将事务中的 sql 语句（一个事务可能对应多个 sql 语句）按照一定的格式记录到 binlog 中，这里与 redo log 很明显的差异就是 redo log 并不一定是在事务提交的时候才刷新到磁盘，而是在事务开始之后就开始逐步写入磁盘。binlog 的默认保存时间是由参数 expire_logs_days 配置的，对于非活动的日志文件，在生成时间超过 expire_logs_days 配置的天数之后，会被自动删除。

总之，redo log 和 undo log 是保证本地事务的，binlog 是用于主从复制的。