MySQL架构与SQL执行流程

MySQL语句执行流程

这里需要注意的是在MySQL 8中，查询缓存功能已经被移除。在之前的MySQL版本中，查询缓存允许将查询结果缓存在内存中，以便在后续相同的查询请求中直接返回缓存结果，从而提高查询性能。
主要的原因有以下几点：

具体执行流程图如下：

存储引擎

存储引擎（Storage Engine）是数据库管理系统（DBMS）中负责数据存储和访问的组件。它负责将数据持久化到磁盘上，并提供数据的读取、写入、更新和删除等操作。

在MySQL中，存储引擎是可插拔的，这意味着可以根据需求选择适合的存储引擎来管理数据。MySQL提供了多种存储引擎，每个存储引擎都有其独特的特点、性能特征和适用场景。

以下是一些常见的MySQL存储引擎：

具体如何选择这存储引擎就需要根据自己的业务来选择，一般情况下我们都使用的是InnoDB

MySQL的架构与内部模块

根据上文的描述我们大致可以将MySQL的架构分为三层

InnoDB存储引擎的磁盘与内存结构

上文中说到的流程仅仅是一个查询语句的流程，如果是更新呢？大体上的流程都是差不多的区别就在于拿到符合条件数据之后的操作这里我们就要首先说一下缓冲池（Buffer Pool）

缓冲池 Buffer Pool

首先，对于 InnoDB 存储引擎来说，数据都是放在磁盘上的，存储引擎要操作数据，必须先把磁盘里面的数据加载到内存里面才可以操作。这里就有个问题，是不是我们需要的数据多大，我们就一次从磁盘加载多少数据到内存呢？比如我要读6个字节。
磁盘I/O的读写相对于内存的操作来说是很慢的。如果我们需要的数据分散在磁盘的不同的地方，那就意味着会产生很多次的I/O操作。所以，无论是操作系统也好，还是存储引擎也好，都有一个预读取的概念。也就是说，当磁盘上的一块数据被读取的时候，很有可能它附近的位置也会马上被读取到，这个就叫做局部性原理。那么这样，我们干脆每次多读取一点，而不是用多少读多少。InnoDB 设定了一个存储引擎从磁盘读取数据到内存的最小的单位，叫做页。操作系
统也有页的概念。操作系统的页大小一般是4K，而在InnoDB 里面，这个最小的单位默认是16KB 大小。如果要修改这个值的大小，需要清空数据重新初始化服务。

读取数据的时候，先判断是不是在这个内存区域里面，如果是，就直接读取，然后操作，不用再次从磁盘加载。如果不是，读取后就写到这个内存的缓冲区。如下图所示

既然有了这个缓存区那就会有数据同步的问题。修改数据的时候，也是先写入到 buffer pool，而不是直接写到磁盘。内存的数据页和磁盘数据不一致的时候，我们把它叫做脏页。那脏页什么时候才同步到磁盘呢？
InnoDB 里面有专门的后台线程把 Buffer Pool 的数据写入到磁盘，每隔一段时间就一次性地把多个修改写入磁盘，这个动作就叫做刷脏。

Redo log

因为刷脏不是实时的，如果 Buffer Pool里面的脏页还没有刷入磁盘时，数据库宕机或者重启，这些数据就会丢失。所以数据库必须有个持久化措施为了解决这个问题InnoDB 把所有对页面的修改操作专门写入一个日志文件。
如果有未同步到磁盘的数据，数据库在启动的时候，会从这个日志文件进行恢复操作（实现 crash-safe)。我们说的事务的ACID 里面D（持久性），就是用它来实现的。

注意点：

undo log

除了 redo log 之外，还有一个跟修改有关的日志，叫做 undo log.redo log 和 undo
log 与事务密切相关，统称为事务日志。undo log(撤销日志或回滚日志)记录了事务发生之前的数据状态，如果数据出现异常可以用undo log进行回滚

更新语句的具体更新过程

InnoDB总体架构

内存结构：

Buffer pool

Buffer Pool缓存的是页面信息，包括数据页、索引页。
内存的缓冲池写满了怎么办？（Redis 设置的内存满了怎么办？）InnoDB 用 LRU
算法来管理缓冲池（链表实现，不是传统的 LRU，分成了 young 和 old)，经过淘汰的
数据就是热点数据、

LRU

传统 LRU，可以用 Map+链表实现。value 存的是在链表中的地址。
如果写 buffer pool的时候发现没有空闲页了,就要从 buffer pool 中淘汰数据页了，
它要根据 LRU 链表的数据来操作。

但是这里MySQL不是单纯的使用LRU算法就把尾部的指针对应的数据删除，而是在LRU的基础上加了热区和冷区靠近 head 的叫做 new sublist,用来放热数据(我们把它叫做热区）。靠近 tail 的叫做 old sublist，用来放冷数据(我们把它叫做冷区）。中间的分割线叫做 midpoint。也就是对 buffer pool 做
一个冷热分离。如下图所示:

所有新数据加入到 buffer pool 的时候，一律先放到冷数据区的 head，不管是预读的，还是普通的读操作。所以如果有一些预读的数据没有被用到，会在 old sublist(冷区）直接被淘汰。
放到 LRU List 以后，如果再次被访问，都把它移动到热区的 head。
如果热区的数据长时间没有被访问，会被先移动到冷区的 head 部，最后慢慢在 tail 被淘汰。

这样做解决的问题就是防止一次读取大量数据将很多热点数据都从内存删除掉了（因为我们读数据不是说精准的读取某一条而是读取一页的数据，所以就有很多数据是预读出来的但是不用的，这样的设计最后就会把预读的数据慢慢剔除掉）

change Buffer写缓冲

Change Buffer 是 Buffer Pool的一部分。
如果这个数据页不是唯一索引，不存在数据重复的情况，也就不需要从磁盘加载索引页判断数据是不是重复（唯一性检查）。这种情况下可以先把修改记录在内存的缓冲池中，从而提升更新语句 (Insert、 Delete、 Update）的执行速度。这一块区域就是 Change Buffer。5.5之前叫 Insert Buffer 插入缓冲，现在也能支
持 delete 和 update。最后把 Change Buffer 记录到数据页的操作叫做 merge。什么时候发生 merge?
有几种情况：在访问这个数据页的时候，或者通过后台线程、或者数据库 shut down、redo log 写满时触发。
如果数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立刻读取，就可以使用 Change Buffer（写缓冲）。

redo log buffer

Redo log 也不是每一次都直接写入磁盘，在 Burter Pool 里面有一块內存区城 (Log
Buffer） 专门用来保存即将要写入日志文件的数据，默认16M，它一样可以节省磁盘 IO。

既然有Log Buffer那就又带来问题了什么时候持久化呢 也就是写入磁盘呢
log buffer 写入磁盘的时机，由一个参数控制，默认是1。

磁盘结构

系统表空间(system tablespace)

在默认情况下 InnoDB 存储引擎有一个共享表空间（对应文件/var/lib/mysql/ibdata1），也叫系统表空间。

InnoDB 系统表空间包含 InnoDB 数据字典和双写缓冲区，Change Buffer 和 Undo Logs)
如果没有指定 file-per-table，也包含用户创建的表和索引数据。

InnoDB 的页和操作系统的页大小不一致，InnoDB 页大小一般为16K，操作系统页
大小为 4K，InnoDB 的页写入到磁盘时，一个页需要分 4次写。

如果存储引擎正在写入页的数据到磁盘时发生了宕机，可能出现页只写了一部分的情况，比如只写了 4k，就宕机了，这种情况叫做部分写失效 (partial page write)能会导致数据丢失。
虽然说我们有redo log可以恢复但是问题是redolog里面这一页的数据是已经损坏的，所以用它恢复没有意义，所以在对于应用 redo log 之前，需要一个页的副本。如果出现了写入失效，就用页的副本来还原这个页，然后再应用 redo log.这个页的副本就是 double write, InnoDB 的双写技术。通过它实现了数据页的可靠性。

跟 redo log一样，double write 由两部分组成，一部分是内存的 double write,
一个部分是磁盘上的 double write。因为 double write 是顺序写入的，不会带来很大的开销。

独占表空间(system tablespace)

我们可以让每张表独占一个表空间。这个开关通过 innodb_file_per_table 设置，默认开启。

开启后，则每张表会开辟—个表空间，这个文件就是数据目录下的 ibd 文件（例如
Nar/lib/mysql/gupao/user innodb.ibd)，存放表的索引和数据。但是其他类的数据，如回滚（undo）信息，插入缓冲索引页、系统事务信息，二次写缓冲(Double write buffer）等还是存放在原来的共享表空间内。

还有redo log、 undo log、临时表空间、通用表空间这些就不一一列举了

binlog

binlog 以事件的形式记录了所有的 DDL和 DML 语句（因为它记录的是操作而不是数据值，属于逻辑志），可以用来做主从复制和数据恢复。

跟 redo log 不一样，它的文件内容是可以追加的，没有固定大小限制。

在开启了 binlog 功能的情况下，我们可以把 binlog 导出成 SQL 语句，把所有的操跟 redo log 不一样，它的文件内容是可以追加的，没有固定大小限制。
作重放一遍，来实现数据的恢复。

一条update语句的完整执行流程

还有一个注意点是redo log是分两次写入的分别有两种状态

只有事务提交了才会将redo log中的记录状态改成commit

崩溃恢复时如何判断事务是否需要提交