Mysql底层原理超详细,一文速通

大致架构

连接层

连接层处理了客户端和服务器之间的通信细节,主要任务为

• 进行身份验证：验证客户端连接的身份，如用户名和密码。
• 权限确认：确认客户端是否有权限访问特定的数据库和表。
• 配置设置：设置连接参数，如字符集、事务处理模式等。

服务层

里面的三个核心组件, 解析器, 优化器, 执行器,负责完成服务层的核心功能, 其中三个核心组件的交互流程大致如下, 解析器将sql语句转换为解析树, 优化器选择最优的执行方案, 执行器根据计划调用存储引擎执行对应操作并返回结果, 有点类似于公司老总负责将公司战略拆分为一个个小目标分发给公司高管, 然后公司高管决策, 已最高效率达到目的,之后再将任务拆分,给我们打工人, 之后打工人来负责执行

解析器

• SQL 词法分析：解析器首先对 SQL 语句进行词法分析，将 SQL 语句分割成多个“单词”或“标记”，如表名、列名、关键字等。
• 语法分析：接着，解析器会根据 SQL 语法规则生成对应的解析树（Parse Tree），用来描述 SQL 语句的逻辑结构。这个过程检查 SQL 语句的语法是否正确。
• 语义分析：确认 SQL 语句中涉及的数据库对象是否存在（比如表名、字段名是否有效），并且检查权限。

优化器

• 逻辑优化：优化器会对 SQL 语句进行逻辑优化，比如 SQL 语句重写、消除冗余操作、合并重复条件、重新排列 WHERE 子句中的条件等。
• 物理优化：在物理优化阶段，优化器会选择最优的访问路径和执行顺序。例如，它会决定使用哪种索引（如果有多个索引可选），是否做全表扫描，如何连接多张表（选择嵌套循环、哈希连接或排序合并连接等）。
• 成本估算：优化器会基于数据库的统计信息（例如表的大小、索引的选择性等）来估算不同执行计划的成本，选择代价最低的执行方案。

执行器

• 权限检查：在执行之前，执行器会首先检查用户是否有权限执行相应的操作。如果没有权限，则返回错误信息。
• 执行执行计划：执行器根据生成的执行计划，依次调用存储引擎的接口来执行具体的操作。例如，如果是查询操作，执行器会调用存储引擎来读取相应的数据；如果是插入操作，执行器则会调用存储引擎来插入数据。
• 结果返回：执行器根据查询的结果，将数据以合适的格式返回给客户端。如果涉及多个步骤（如 JOIN 操作），执行器会协调各个步骤的执行，并组合最终的结果集。

存储引擎层

存储结构

注意:不同存储引擎在磁盘文件上的结构均不一致,这里以InnoDB为例

• 每生成一张新标,InnoDB都会创建.frm和.ibd文件
  • .frm存储了表的结构、列名、数据类型、索引等元数据信息,为所有引擎共有
  • .ibd是InnoDB存储引擎的数据文件，实际存储了表的数据和索引

• 64个页组成一个区,256个区组成一个组
• 频繁的读取某个区中的页,将会将整个区读取出来,放入BufferPool
• 表创建之初只有6个页,在这6个页中前四个分别记录表空间和区组条目信息,Change Buffer相关信息,段信息,索引根信息,会随着表的创建添加到到碎片区,当碎片区的页有32个之后之后就会创建完整的区
• 随着区的增多,达到256个之后就会创建组,只有第一组的前四页特殊,之后的页记录的为区组条目信息,Change Buffer相关信息
• 段为逻辑概念,可以看做页区的附加信息用来区分不同功能的"区"(对应B+树索引中的叶子结点)和在碎片区中的"页"(对应B+树索引中的非叶子结点),

文件系统层

写入流程

方便大家理解这三个文件的重要性, 我们先来简单看一下一条写入语句的执行流程,后面再做详细解说

1. 将数据记录到Undo Log日志文件,为了让其可回滚
2. 在Buffer Pool中进行逻辑处理和读取写入
3. 将更新写入信息存到内存中的Redo Log Buffer,记录的是相关操作语句
4. 刷盘策略可通过innodb_flush_log_at_trx_commit设置,默认为1,代表在事务提交前将Redo Log Buffer添加到操作系统内存并立刻进行刷盘,0和2策略的一致性不够强
5. BinLog可提供变更历史查询,数据库备份和恢复,主从复制等功能 ,当执行修改操作（如插入、更新、删除）时，相关的操作语句或事件信息会被记录到BinLog中。
6. 将该事务的提交状态变为Commit
7. 之后IO线程会在特定时机(事务提交,或者定时提交)从内存中读取需要更新写入的数据,将其写入磁盘,调用的操作系统的open+write该写入操作

undoLog(回滚日志)-原子性

数据库事务开启后才会将数据写入undolog, undoLog中记录的数据主要为以下几类

1. 旧值信息：记录事务修改数据前的原始值，以便在事务回滚时将数据恢复到之前的状态。
2. 事务标识：记录哪个事务对数据进行了修改，以确保在事务回滚时只撤销该事务的操作。
3. 指针信息：指向数据页的位置，用于定位和操作需要撤销的数据。
4. 操作类型：记录对数据的修改操作类型，如插入、更新、删除等。值得注意的是除了更新操作如果是更新的主键的话会记录两条日志信息, 因为mysql中主键是不能被修改的, 实际上是删除再新增, 更新非主键和其他类型一样都是一条日志信息
5. 回滚指针：记录了如何回滚操作的指针，使得在回滚时可以快速找到并执行相应的撤销操作。

当开启一段事务还未提交时，事务中的操作可能会出现错误异常，这时候就可以通过undo log将事务中的操作进行回滚（rollback），意思是回到事务开启前那个状态。

例如：开启事务后，对表中某条记录进行修改（将该记录字段值由value1 ——> value2 ——> value3 ），如果从整个修改过程中出现异常，事务就会回滚，字段的值就回到最初的起点（值为value1 ）。

事务如何通过undo log进行回滚操作呢？

这个很好理解，我们只需要在undo log日志中记录事务中的反向操作即可，发生回滚时直接通过undolog中记录的反向操作进行恢复，例如：

• 事务进行insert操作，undo log记录delete操作
• 事务进行delete操作，undo log记录insert操作
• 事务进行update操作（value1 改为value2 ），undolog记录update操作（value2 改为value3 ）

undo log保存的是一个版本链(还能用于MVCC, 解决事务隔离性)，也就是使用DB_ROLL_PTR这个字段来连接的。多个事务的 undo-log 日志副本 (数据快照)，组成了一个 副本链，

value1 ——> value2 ——> value3 的版本链， 如下图：

为了 提升 Undo Log 读写性能， Undo Log 也在内存中进行了缓存，所处的位置在 Buffer Pool 中，如下图所示：

Buffer Pool 中的 Undo Log 页 是 InnoDB 存储引擎中用于缓存 Undo Log 记录的内存区域。

Buffer Pool 是一个重要的内存结构，用于提高数据库的性能，通过在内存中存储频繁访问的数据页和日志记录，减少对磁盘的 I/O 操作。

以下是有关 Buffer Pool 中 Undo Log 页的详细信息：

• Buffer Pool 是 InnoDB 的内存缓存，用于存储数据页和索引页，以便快速访问。它通过减少磁盘 I/O，提高数据库的整体性能。
• 在 Buffer Pool 中，除了数据页外，还可以存储 Undo Log 页，这样可以加速事务的回滚和数据恢复。

buffer pool 中有数据页也有 undo 页，基于wal思想，不仅对buffer pool 中数据页修改操作会记录到redo log buffer，对 undo 页修改操作也会记录到 redo log buffer，这样就通过redo log保证了事务持久性。

当事务Commit之后，undo 页本身就没有利用价值了，此时通过后台线程中的Master Thread或Purge Thread进行 undo 页 的回收工作。

关于BufferPool

Buffer Pool通过缓存热点数据和索引，减少了磁盘I/O操作，大大提高了数据库的性能。 数据的加载和淘汰机制是维护和优化其性能的关键。 当数据库服务器收到一个查询请求时, 如果数据页在BufferPool中, 则直接返回, 不在则从磁盘中查找, 将数据存BufferPoll后返回

其中页被链表进行管理, 缓存页通过LRU链表管理, 空闲页通过free链管理, flush链表管理脏页(内存中的数据被修改过但尚未同步到磁盘上的数据文件中。)

索引页

存储InnoDB表的索引结构

数据页

存储实际数据行

Undo页

Undo页存储了旧版本的数据，用于支持事务的ACID属性中的原子性、隔离性（Isolation）。

redoLog(重做日志)-持久性

redoLog是物理日志, 记录的是"在某个数据页上进行了修改", 上文提到的脏页, 当内存中的数据修改后, 需要写到磁盘中, 而IO线程不是实时将数据刷入磁盘的, 如果发生非正常的DB重启, 导致内存中的数据丢失, 那么导致数据丢失, 而redoLog就是应对这种情况而生的, 当服务器进行DB恢复时, 会根据redoLog中的数据, 重新持久化刷新磁盘文件

值得注意的是,内存到磁盘是随机IO, 而修改记录到redoLog是顺序IO

具体恢复步骤

1. 先将原始数据从磁盘中读入buffer pool 内存中来，修改数据的内存拷贝，数据变成脏页
2. 生成一条重做日志并写入 redo log buffer ，记录的是数据被修改后的值
3. 当事务 commit 时，将 redo log buffer 中的内容刷新到 redo log file ，对 redo log file 采用追加写的方式
4. 定期将内存中修改的数据刷新到磁盘中

redo log通过WAL（Write-Ahead Logging）来进行故障恢复（crash-safe），所谓WAL简单来说就是先写日志，后写磁盘。

当缓存页被修改后（变成脏页），我们就将本次操作写入到redo log buffer中，

当事务Commit时，就先将redo log buffer中记录通过后台线程刷到磁盘中的redoLog（事务提交是redo log默认刷盘时机）

此时脏页还没有刷入磁盘，但是，只要redo log成功刷盘，就可以认为本次的修改操作完成了，

因为就算发生了故障导致脏页数据丢失，也可以通过磁盘redo log恢复，

所以，redo log 和 undo log 配合起来的作用就是：

• 事务提交前崩溃，通过 undo log 回滚事务
• 事务提交后崩溃，通过 redo log 恢复事务

redoBuffer到redoLog的刷盘策略

这个上面有简略提到过, 通过innodb_flush_log_at_trx_commit 控制, 具体策略如下

• 0 ：设置为 0 的时候，表示每次事务提交时不进行刷盘操作。有事务提交的情况下，每秒刷盘一次。（性能好，低可靠）
• 1 ：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作（默认值）。（高可靠， 低性能）
• 2 ：设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache，刷盘时机交给OS

redoLog的写入方式-循环写入

两个指针, write pos表示最新写入的位置, checkpoint表示要擦除的位置, 数据结构采用循环队列

其中write pos - checkpoint区域表示待写入的部分(下图绿色部分), checkpoint - write pos表示还未刷入磁盘的记录

redo log 满了，在擦除之前，要确保这些要被擦除记录都已经刷到磁盘中了。在擦除旧记录释放新空间期间，不能再接收新的更新请求，此时 MySQL 性能会下降。因此高并发情况下，合理调整 redo log 大小很重要。

binLog(二进制文件)-一致性

位于存储引擎外,为二进制文件, 像redoLog, undoLog等文件都在InnoDB中, 程序猿没法直接使用,当执行错了一些语句没法恢复如初, 因此才有了binLog

binlog以事件形式记录，不仅记录了操作的语句，同时还记录了语句所执行的消耗的时间. 主要的作用为备份恢复和主从复制

刷盘时机

类似于redoLog一样, 都是先写到缓存, binLog是写到servic层的bingLogCache, 然后提交的时候,再写道binLog文件中, 并将bingLogCache清空, 其中刷盘频率可以通过sync_binlog 参数来设置, 具体参数如下

• sync_binlog = 0 的时候，表示每次提交事务都只 write，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘；
• sync_binlog = 1 的时候，表示每次提交事务都会 write，然后马上执行 fsync；
• sync_binlog =N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

事务提交时对应的操作

实际上是组提交, 也就是批量提交, 但是两阶段提交便于理解, 这里以两阶段为例

prepare 阶段：将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘；

commit 阶段：将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件）；

当事务进行Commit操作时，redo log 和 bin log都会被刷盘持久化保存，但是可能会出现以下两种情况：

• bin log刷盘后，redo log还未来得及刷盘，数据库宕机，数据不一致。
• redo log刷盘后，bin log还未来得及刷盘，数据库宕机，数据不一致。

双1 配置 (重点)：

• sync_binlog = 1 表示每次提交事务都会 write binlog，然后马上执行 fsync；
• innodb_flush_log_at_trx_commit=1（默认值）：设置为 1 的时候，表示每次事务提交时都将进行redo log刷盘操作（默认值），所以默认是高可靠， 低性能

这个两阶段提交机制保证了 binlog 和 redo log 的协调一致。基本上会出现下面是几种情况：

• 当事务在prepare阶段crash，数据库recovery的时候，该事务未写入binlog log并且存储引擎未提交，将该事务rollback。
• 当事务在Commit 阶段 写 binlog阶段crash，此时日志还没有成功写入到磁盘中，启动时会rollback此事务。
• 当事务在Commit 阶段 写 binlog阶段 并且已经fsync()到磁盘后crash，但是InnoDB没有来得及commit，此时MySQL数据库recovery的时候将会读出binlog日志的Xid_log_event，然后告诉InnoDB提交这些XID的事务，InnoDB提交完这些事务后会回滚其它的事务，使存储引擎和二进制日志始终保持一致。

总结起来说就是如果一个事务在prepare阶段中落盘成功，并在MySQL Server层中的binlog也写入成功，那这个事务必定commit成功。

通过两阶段提交协议，MySQL 在事务提交过程中可以确保在发生崩溃时：

• 事务的一致性：当事务提交后，binlog 和 redo log 保持一致。如果事务提交成功，则 binlog 和 redo log 都会反映出相同的操作；如果失败，则事务会被回滚，binlog 也不会记录到这个事务。
• 数据恢复：当 MySQL 恢复时，系统可以根据 redo log 和 binlog 的状态，确保事务的一致性，避免部分提交或丢失。

两大一致性

第一个数据一致性： 崩溃一致性（Crash Consistency）

Binlog 记录事务提交的 SQL 操作，通常与 Redo Log 协同工作，实现崩溃一致性（Crash Consistency）。

MySQL 的 InnoDB 存储引擎 和 MySQL Server 层的 binlog 通过两阶段提交来保证事务的一致性，确保在系统崩溃或主从复制时，事务的逻辑和物理数据保持一致。

具体流程如下：

• 第一阶段（Prepare 阶段）：
  1. 当一个事务执行完所有操作并准备提交时，InnoDB 首先会将修改操作写入到 redo log，并将其标记为 prepare 状态。这意味着事务的物理修改操作已经记录到了 redo log 中，但事务尚未真正提交。
  2. 此时，如果系统崩溃，事务可以通过 undo log 回滚，保持一致性。
• 第二阶段（Commit 阶段）：
  1. 写入 binlog：将 binlog 刷新到磁盘。这时，binlog 确保了主从库在复制时具有相同的操作逻辑。
  2. 提交 redo log：InnoDB 会将 redo log 的状态修改为 commit，表示事务已经正式提交。

这个两阶段提交机制保证了 binlog 和 redo log 的协调一致。基本上会出现下面是几种情况：

• 当事务在prepare阶段crash，数据库recovery的时候，该事务未写入binlog log并且存储引擎未提交，将该事务rollback。
• 当事务在Commit 阶段 写 binlog阶段crash，此时日志还没有成功写入到磁盘中，启动时会rollback此事务。
• 当事务在Commit 阶段 写 binlog阶段 并且已经fsync()到磁盘后crash，但是InnoDB没有来得及commit，此时MySQL数据库recovery的时候将会读出binlog日志的Xid_log_event，然后告诉InnoDB提交这些XID的事务，InnoDB提交完这些事务后会回滚其它的事务，使存储引擎和二进制日志始终保持一致。

总结起来说就是如果一个事务在prepare阶段中落盘成功，并在MySQL Server层中的binlog也写入成功，那这个事务必定commit成功。

通过两阶段提交协议，MySQL 在事务提交过程中可以确保在发生崩溃时：

• 事务的一致性：当事务提交后，binlog 和 redo log 保持一致。如果事务提交成功，则 binlog 和 redo log 都会反映出相同的操作；如果失败，则事务会被回滚，binlog 也不会记录到这个事务。
• 数据恢复：当 MySQL 恢复时，系统可以根据 redo log 和 binlog 的状态，确保事务的一致性，避免部分提交或丢失。

第2个数据一致性：中继日志与主从复制的一致性

在主从复制的场景下，binlog 的一致性尤为重要。

• 主库提交事务时会先写入 binlog
• 从库则通过读取主库的 binlog 重新执行相同的操作。

MySQL 确保：

• 只有当事务在主库提交成功（即 binlog 和 redo log 一致）后，主库才会将 binlog 传递给从库。
• 从库应用 binlog 以确保主库和从库的数据保持一致。

通过这种机制，MySQL 保证了主从复制环境中的一致性，不会出现主库和从库不同步的情况。