一、MySQL 逻辑结构

首先 MySQL 整体结构上分为 server层、存储引擎层。其中 Server 层包含如下组件：

连接器：管理客户端连接，权限认证
查询缓存：缓存查询的语句和结构
分析器：分析查询SQL语句，包括词法分析、语法分析
优化器：生成执行计划，选择更高效查询方式的索引
执行器：调用存储引擎接口，查询数据并返回结果集给客户端

总体上来讲，一条查询SQL 语句会经过以上各组件，各组件会按照其作用对SQL语句处理。值得注意的是查询缓存组件。

二、SQL查询过程中的查询缓存

MySQL 拿到一个查询请求后会优先查询本地内存是否有对应的SQL语句，之前执行过的SQL语句会以 key-value 的格式存在于内存中，其中 key 为查询语句、value 为查询结果。但是查询缓存一般是弊大于利的，因为查询缓存的失效非常频繁，如果对于一个表有更新操作，那么这个表上所有的查询缓存都会清空失效，这就导致查询缓存命中率非常低，在使用时可以根据实际情况自定义是否使用查询缓存，在 MySQL8.x 版本中已经移除了查询缓存。

-- 使用查询缓存，默认不使用
mysql> select SQL_CACHE * from T where ID = 10;

三、SQL更新语句执行过程

SQL更新语句也会走一遍查询语句的全过程，不同的是它还涉及到两个重要的日志模块：redo log、bin log。

3.1、redo log

redo log 的作用：MySQL 如果频繁执行更新/插入/删除操作，那么将每一次更新操作都写入磁盘是很低效的方式。因此 MySQL 通过预先记录操作到 redo log 中，并更新内存，同时在 Innodb 引擎适当时候将这个操作更新到磁盘中。

redo log 大小固定，比如可以配置为一组 4个文件，每个文件大小为 1GB，那么总共可以记录 4GB文件，从头开始写，写到文件末尾就循环回到开头继续写。在 redo log 执行写操作时包含 checkpoint、writepos 两个字段，checkpoint 记录了当前要擦除的位置，擦除前需要将数据刷盘；wirtepos 记录当前写入数据的位置，如果 writepos 跟上了 checkpoint，Mysql就不能再执行更新操作，需要停下来等待 checkpoint 向前推进一下。

有了 redo log 同时还可以保证 crash-safe ，因为数据记录在本地日志中，即使 MySQL 宕机后恢复，依旧可以读取上一次的状态。

3.2、bin log

redo log 作为 Innodb 引擎特有的日志，MyISAM 等存储引擎不具备。最开始MySQL 并没有 Innodb 存储引擎，它具有 bin log日志，但是该日志没有 crash-safe 的能力。redo log 和 bin log有以下不同:

redo log 是 Innodb 引擎特有的，bin log 是 MySQL 的 Server 层实现的，所有引擎都可以使用；
redo log 是物理日志，记录的是在某个数据页上做了什么修改；bin log 是逻辑日志，记录的是这个语句的原始逻辑，比如 给ID=2这行的c字段加1；
redo log 是循环写的，空间固定会用完；bin log 是可以追加写入的，当文件写到一定大小后会切换下一个文件，而不是覆盖写；

3.3、执行 update 语句的全过程

执行引擎首先找到 ID=2 的这行数据。如果 ID=2 这行数据本来就在内存中，直接返回给执行器，否则先从磁盘读入到内存再返回；
执行器拿到该行数据把值加1，得到新的一行数据，再调用引擎接口写入这行新数据；
引擎将新行数据更新到内存中，同时将更新操作记录到 redo log 中，此时 redo log 处于 prepare 状态，然后告知执行器执行完成了，可以提交；
执行器生成该操作的 bin log，并将 bin log 写入磁盘；
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交状态 commit，更新完成。

3.4、两阶段提交

首先考虑怎么让数据库恢复到半个月内任意一秒的状态？

bin log 会记录所有的逻辑操作，当需要恢复到指定某一秒的数据时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，可以这样做：

首先，找到最近一次全量备份，如果运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时表；
然后，从备份的时间开始，将备份的 bin log 依次取出来，重放到中午误删表之前的那个时刻。

为什么需要两阶段提交呢？利用反证法说明，假设先写 redo log 再写 bin log 有什么问题？先写 bin log 再写 redo log 会有什么问题？仍然以前面 update 语句作为例子，假设当前 ID=2 的行，字段 c的值为0：

先写 redo log 后写 bin log：

假设在 redo log 写完，bin log 还未写完的时候，MySQL 进程异常重启。由于 redo log 写完了，重启后仍然能够将数据恢复，所以恢复后这一行 c的值为1。但是 bin log 还未写完就宕机了，此时 bin log 中就没有该操作的记录，因此之后备份日志的时候存起来的 bin log 中就没有这条语句。那么在你需要通过 bin log 恢复临时库的时候，由于 bin log 中这条语句的确实，恢复出来 c 的值为0，和原库值不同。

先写 bin log 后写 redo log：

假设 bin log 写完后宕机，此时 redo log 还未写，崩溃恢复以后这个事务无效，所以这一行 c 的值为0。但是 bin log 里面已经记录了把 c 从0改为1 这个日志。所以之后用 bin log 来恢复时就多出一个事务来，恢复的结果是这一行c 的值为1，与原库不同。

四、redoLog、binLog的写入机制

https://www.cnblogs.com/wkynf/p/15855079.html
https://blog.csdn.net/qq_42604176/article/details/115441896

基础概念

write：并不是刷盘操作，而是将数据写入到文件的 PageCache中；
fsync：刷盘操作，将数据持久化到磁盘；

4.1、binLog 的写入机制

事务执行时先把日志写入到 binLog cache 中，然后在事务提交时将 binLog cache 内容写入 binlog 文件中；
系统为每个线程分配了一个 binLog cache 内存，如果超过了 binLog_cache 容量就需要暂存磁盘；
事务提交时，执行器将 binLog cache 里完整的事务写入到 binLog 文件中，并且清空 binLog cache；
每个线程都有自己的一份 binLog cache，并且共用一份 binLog 文件；
write 操作是指将 binLog cache 的内容写入到文件系统 page cache 内存中，并没有持久化到磁盘。真正持久化到磁盘是在 fsync 操作执行。

注意区分 write 和 fsync 区别，write 时数据并未持久化到磁盘，而是处于文件系统 page cache 中。
何时 write、fsync 是由参数 sync_binlog 控制的：

sync_binlog = 0 ， 每次提交事务都只会 write 而不是 fsync；
sync_binlog = 1 ， 每次提交事务都都会 fsync；
sync_binlog = N ， 初始提交事务只执行 write，累积 N 个事务后执行 fsync。

sync_binlog 设置为 N 可以提高性能，但由于数据只保存到 pageCache，如果主机发生异常重启会丢失近 N 个事务的 binlog 日志。

4.2、redolog 的写入机制

redoLog 区别于 binLog，在事务执行过程中生成的 redoLog 先写入到 redoLog Buffer中，在事务提交时再将 redoLog Buffer 持久化到磁盘。但是注意 即使事务未提交，redoLog 也是有可能持久化到磁盘的。

区别于 binLog，系统并没有为 redoLog 的每个线程分配单独的 redoLog Buffer，而是多个线程共享同一个 redoLog Buffer。

InnoDB 有一个后台线程，每间隔 1s，就会把 redoLog Buffer 中的日志调用 write系统调用写入到 redoLog 中；然后调用 fsync 系统调用持久化到磁盘文件中，所以在事务执行过程未提交时，是可能将数据持久化到磁盘文件的。

何时将 redoLog Buffer 数据刷盘有如下相关配置：

innodb_flush_log_at_trx_commit = 0 ， 每次事务提交都只是把数据写入到 redoLog Buffer中；
innodb_flush_log_at_trx_commit = 1 ， 每次事务提交都将数据写入到磁盘中（执行 fsync）；
innodb_flush_log_at_trx_commit = 2 ， 每次事务提交都只是把数据写入到 pageCache 中。

4.3、事务未提交执行刷盘操作

由于 redoLog 后台线程的存在，每间隔 1s 会执行 redoLog Buffer——>pageCache——>hardDisk 操作，事务未提交，数据已刷盘；
redoLog Buffer 占用的空间达到 buffer pool 一半的时候，后台线程会主动刷盘，此时由于事务没有提交，所以只是将 redoLog Buffer 的内容写入到 pageCache 中，并没有执行刷盘操作；
多事务并行提交，多个事务存在时，如果事务A未提交，事务B此时提交事务，由于 redoLog Buffer是多线程共享的，所以会导致事务B提交时将所有数据全部刷盘，进而导致事务A的数据提交。所以真正的两阶段提交操作实际如下图。

如果配置 innodb_flush_logs_at_trx_commit = 1 操作，数据库就会在两阶段提交的 prepare 阶段执行 fsync 操作刷盘，如果再加上每 1s 后台刷盘操作，innodb 会认为 redoLog 在提交的时候就不需要 fsync 操作了。所以真正的两阶段提交操作流程如下：