MySQL的底层原理与架构

前言

了解MySQL的架构和原理对于很多的后续很多的操作会有很大的帮助与理解。并且很多知识都与底层架构相关联。

了解MySQL架构

通过上面的架构图可以得知，Server层中主要由 连接器、查询缓存、解析器/分析器、优化器、执行器 几部分组成的，下面将主要描述下这几部分。

1、连接器：

客户端想要对数据库进行操作时，前提是与数据库建立好连接；而连接器就是用来负责跟客户端建立连接、获取权限、维持和管理连接的。

（1）连接方式：

MySQL既支持短连接，也支持长连接。短连接就是操作完毕后，马上close关掉。长连接可以保持打开，减少服务端创建和释放连接的消耗，后续程序访问的时候还可以使用这个连接。

（2）连接池：

与客户端的连接池一样，为了减少频繁创建和销毁连接造成的不必要的性能损失，这里也采用了“池化”的思想，通过数据库连接池去管理连接。一般我们会在连接池中使用长连接，例如：druid、c3p0、dbcp等

2、查询缓存：

MySQL缓存是默认关闭的，也就是说不推荐使用缓存，并且在 MySQL 8.0 版本直接将查询缓存的整块功能删掉了。因为缓存中的格式是kye(sql),value(数据）的方式保存的，如果SQL只要有一点不同那么就不会走缓存。其次是表中的数据很多时候都是会变化的，而数据变化了那么缓存也就没有意义。

3、分析/解析器：

分析器的工作主要是对要执行的SQL语句进行解析，最终得到抽象语法树，然后再使用预处理器判断抽象语法树中的表是否存在，如果存在的话，在接着判断select投影列字段是否在表中存在等。

（1）词法分析：

词法分析用于将SQL拆解为不可再分的原子符号，称为Token。并根据不同数据库方言所提供的字典，将其归类为关键字，表达式，字面量和操作符。

（2）语法分析：

语法分析就是根据词法分析拆解出来的Token（原子符号）将SQL语句转换为抽象语法树。

（3）预处理器：

预处理是用来对生成的 抽象语法树 进行语义校验，语义校验就是对查询的表、select投影列字段进行校验，判断表、字段是否存在等；

SELECT id, name FROM t_user WHERE status = ‘ACTIVE’ AND age > 18

以上述SQL为例，经过词法分析与语法分析后会得到的抽象语法树如下图。

4、优化器：

优化器的作用主要是将SQL经过词法解析/语法解析后得到的语法树，通过MySQL的数据字典和统计信息的内容，经过一系列运算 ，最终得出一个执行计划，包括选择使用哪个索引。

在优化过程中，经过的一系列运算是什么呢？

（1）逻辑变换：例如SQL的where条件中存在 8>9，那逻辑转换就是将语法树中存在的这种常量表达式直接进行化简，化简为 false；除了化简还有常量表达式计算等。

（2）代价优化：就是通过付出一些数据统计分析的代价，来得到这个SQL执行是否可以走索引，以及走哪些索引；除此之外，在多表关联查询中，确定最终表join的顺序等

在分析是否走索引查询时，是通过进行动态数据采样统计分析出来；只要是统计分析出来的，那就可能会存在分析错误的情况，所以在SQL执行不走索引时，也要考虑到这方面的因素

5、执行器：

MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。执行器最终就是根据一系列的执行计划去调用存储引擎提供的API接口去调用操作数据，完成SQL的执行。

开始执行的时候，要先判断一下建立连接的对象对这个表有没有执行操作的权限，如果没有，就会返回没有权限的错误；如果有，就按照生成的执行计划进行执行。

InnoDB存储引擎：

存储引擎是对底层物理数据执行实际操作的组件，为Server服务器层提供各种操作数据的 API，数据是被存放在内存或者是磁盘中的。MySQL 支持插件式的存储引擎，包括 InnoDB 、MyISAM、Memory 等。一般情况下，MySQL默认使用的存储引擎是 InnoDB。如下图所示，InnoDB存储引擎整体分为内存架构（Memory Structures）和磁盘架构（Disk Structures）

1、Buffer Pool：

Buffer Pool （缓冲池）是 InnoDB 存储引擎中非常重要的内存结构，类似 Redis 一样的作用，起到一个缓存的作用。MySQL 的数据最终是存储在磁盘中的，如果没有 Buffer Pool，那么每次的数据库请求都会磁盘中查找，这样必然会存在 IO 操作。但是有了 Buffer Pool，在第一次查询时就会将查询的结果保存到 Buffer Pool 中，这样后面再有请求时就会先从缓冲池中去查询，如果没有再去磁盘中查找，然后在放到 Buffer Pool 中，如下图：

UPDATE students SET stuName = ‘小强’ WHERE id = 1

以上述SQL为例，那么它的执行顺序大致如下

• （1）innodb 存储引擎先在缓冲池中查找 id=1 的这条数据是否存在
• （2）如果缓存不存在，那么就去磁盘中加载，并将其存放在缓冲池中
• （3）该条记录会被加上一个独占锁

**??**buffer pool 和 前面提到的查询缓存的区别：

（1）查询缓存：查询缓存位于Server层，MySQL Server首选会从查询缓存中查看是否曾经执行过这个SQL，如果曾经执行过的话，之前执行的查询结果会以Key-Value的形式保存在查询缓存中。key是SQL语句，value是查询结果。我们将这个过程称为查询缓存！

（2）Buffer Pool位于存储引擎层。Buffer Pool就是MySQL存储引擎为了加速数据的读取速度而设计的缓冲机制

2、undo log日志文件（回滚日志）：记录数据被修改前的样子

Innodb 存储引擎的最大特点就是支持事务，如果事务提交失败，那么该事务中所有的操作都必须回滚到执行前的样子，而这个回滚的操作，就是利用 undo log文件完成的。

• Undo Log用于实现事务的原子性和一致性，用于撤销事务对数据的修改。

上面我们介绍了，在准备更新一条SQL语句的时候，该条语句对应的数据已经被加载到 Buffer pool 中了，实际上这里还有这样的操作，就是在将该条语句加载到 Buffer Pool 中的时候同时会往 undo log 日志文件中插入一条日志，也就是将 id=1 的这条记录的原来的值记录下来，便于事务失败后进行回滚：

到这一步，我们执行的 SQL 语句对应的数据已经被加载到 Buffer Pool 中了，然后开始更新这条语句，更新的操作实际是在Buffer Pool中执行的。那问题来了，更新完数据之后，Buffer Pool缓冲池中的中的数据就会和数据库中的数据库不一致，那就是说Buffer Pool 中的数据成了脏数据？没错，目前这条数据就是脏数据，Buffer Pool 中的记录是“小强”数据库中的记录是“旺财” ，这种情况 MySQL是怎么处理的呢？我们接着往下看

3、redo log 日志文件（重做日志）：记录数据被修改后的样子

redo 日志文件是 InnoDB 特有的，他是存储引擎级别的

• Redo Log用于实现事务的持久性，用于在数据库异常崩溃时恢复数据的一致性。MySQL通过Undo Log和Redo Log的配合，确保了数据库的事务安全性和持久性。

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file），前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘，发生错误时，供数据恢复用。

除了从磁盘中加载文件和将操作前的记录保存到 undo 日志文件中之外，其他的操作是在内存中完成的，内存中的数据的特点就是：断电丢失。如果此时 MySQL 所在的服务器宕机了，那么 Buffer Pool 中的数据会全部丢失的。这个时候 redo log 日志文件就需要来大显神通了

redo 就是准备去做、将要去做的意思，redo log 记录的是将要做的一些操作。例如，此时将要做的是 update students set stuName=‘小强’ where id=1; 那么这条操作就会被记录到 redo log buffer 中，redo log buffer 是 MySQL 为了提高效率，所以将这些操作都先放在内存中去完成

这时候假设服务器宕机了，那么缓存中的数据还是丢失了。那能不能不要放在内存中，直接保存到磁盘呢？很显然不行，在内存中操作目的是为了提高效率。如果此时 MySQL 真的宕机了，那么没关系的，因为 MySQL 会认为本次事务是失败的，会利用 undo log 的日志信息进行回滚，并不会有任何的影响。

到了这里，SQL语句也更新好了，那么需要将更新的值提交了，也就是需要提交本次的事务，只要事务成功提交了，才会将最后的变更保存到数据库。在提交事务前会将 redo Log Buffer 中的数据写入到磁盘文件 redo log 中，进行持久化，而刷入磁盘的时机则是根据 innodb_flush_log_at_trx_commit 参数来决定的：

0：表示每次事务提交时都只是把redo log留在redo log buffer中，数据库宕机有可能会丢失数据。

1（默认值）：每次事务提交时，都将redo log直接持久化到磁盘，数据最安全，不会因为数据库宕机丢失数据，但是效率稍微差一点，线上系统推荐设置

2：每次事务提交时都只把redo log写到操作系统的缓存page cache里，这种情况如果数据库宕机是不会丢失数据的（os cache里还有数据）,但是如果操作系统宕机了，那么page cache里的数据还没来得及写入磁盘文件就有可能会丢失数据。

InnoDB有一个后台线程，每隔1秒就会把redo log buffer中的日志，调用操作系统函数write写到文件系统page cache，然后调用操作系统函数fsync持久化到磁盘文件中。

关于这个参数的设定，需要根据具体的项目以及业务场景来定，如果设置合理那么可以带来很大的性能提升。

redo log Buffer 刷入磁盘后，就完成了数据的持久化了，就算数据库宕机了，我们也可以再下次重启的时候将 redo log 日志文件的内容恢复到 Buffer Pool 中。

前面提到，如果每次修改都直接以落盘的形式处理，那么必定有大量的随机IO，如果使用机械磁盘更会存在大量的disk seek时间，数据库性能极低，那为什么写入 redo log 就能提高性能呢？这是因为写 redo log 至磁盘属于磁盘顺序写，只需 append log 到下一位置，所以 redo log 的落盘速度是非常快的

到此SQL语句的执行就如下

1.准备SQL语句

2.MySQL会先在Buffer pool(缓冲池)中进行查找需要的数据，如果没有那么就从磁盘中取出然后放到Buffer pool中进行操作

3.在加载到Buffer pool的同时，会将这条数据的原始记录加载到undo日志文件中。

4.在Buffer Pool中完成数据操作

5.更新后的数据会记录在Redo Log Buffer中，然后根据 innodb_flush_log_at_trx_commit 参数来决定什么时候将数据刷入磁盘文件redo log进行持久化

6.MySQL重启时会将redo log恢复到缓冲池中。

4、bin log日志文件：记录整个操作过程

前言：bin log 和 redo log 有些相似，两者的主要区别有：

（1）redo log是 InnoDB 存储引擎特有的日志文件，而 bin log属于是 MySQL 级别的日志

（2）redo log 适用于崩溃恢复，bin log 适用于主从复制和数据恢复

（3）redo log 记录的东西是偏向于物理性质的，如：“对什么数据，做了什么修改”。bin log是偏向于逻辑性质的，类似于：“对 students 表中的 id 为 1 的记录租了更新操作” 。

bin log文件是如何刷入磁盘的 bin log的刷盘策略可以通过 sync_bin log 来修改，默认为0，表示先写入 os cache，也就是说在提交事务的时候，数据不会直接到磁盘中，这样如果宕机 bin log 数据仍然会丢失。所以建议将sync_bin log设置为 1，表示直接将数据写入到磁盘文件中。

既然 bin log 也是日志文件，那它是在什么时候记录数据的呢？其实 MySQL 在提交事务时，不仅会将 redo log buffer 中的数据写入到 redo log 文件中，也会将本次修改的数据记录到 bin log 文件中，同时会将本次修改的 bin log 文件名和修改的内容在 bin log 中的位置记录到 redo log 中，最后还会在 redo log 最后写入 commit 标记，这样就表示本次事务被成功的提交了

如果在数据在写入 bin log文件时，刚写完，数据库宕机了，数据会丢失吗？首先可以确定的是，只要 redo log 最后没有 commit 标记，说明本次的事务一定是失败的。但是数据是没有丢失的，因为已经被记录到 redo log 的磁盘文件了，在 MySQL 重启的时候，就会将 redo log 中的数据恢复（加载）到Buffer Pool中

到目前为止，一个更新操作基本介绍得差不多，但是你有没有感觉少了哪件事情还没有做？是不是你也发现这个时候被更新记录仅仅是在内存中执行的，哪怕是宕机又恢复了也仅仅是将更新后的记录加载到 Buffer Pool 中，这个时候 MySQL 数据库中的这条记录依旧是旧值，也就是说内存中的数据在我们看来依旧是脏数据，那这个时候怎么办呢？

其实 MySQL 会有一个后台线程，它会在某个时机将我们 Buffer Pool 中的脏数据刷到 MySQL 数据库中，这样就将内存和数据库的数据保持统一了。刷新脏页的时机如下：

（1）redo log 写满时，停止所有更新操作，将 checkpoint 向前推进，推进那部分日志的脏页更新到磁盘；

（2）需要将一部分数据页淘汰，如果是干净页，直接淘汰就行了，脏页的话，需要全部同步到磁盘；

（3）mysql自认为空闲时

（4）mysql正常关闭之前

关于图中的OS cache是操作系统中内存中的一块区域，为了提升应用的写入磁盘的性能解决内存和磁盘交互的性能瓶颈。很多应用如kafka、MQ等写入硬盘的操作那么就把os cache当做硬盘来写入，提升性能。

总结

（1）首先 MySQL 执行器根据执行计划调用存储引擎的API查询数据

（2）存储引擎先从缓存池 buffer pool 中查询数据，如果没有就会去磁盘中查询，如果查询到了就将其放到缓存池中

（3）在数据加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo log 日志文件中

（4）innodb 会在 Buffer Pool 中执行更新操作

（5）更新后的数据会记录在 redo log buffer 中

（6）提交事务在提交的同时会做以下三件事

（7）（第一件事）将 redo log buffer 中的数据刷入到redo log文件中

（8）（第二件事）将本次操作记录写入到 bin log 文件中

（9）（第三件事）将bin log文件名字和更新内容在 bin log 中的位置记录到redo log中，同时在 redo log 最后添加 commit 标记

（10）使用一个后台线程，它会在某个时机将我们Buffer Pool中的更新后的数据刷到 MySQL 数据库中，这样就将内存和数据库的数据保持统一了