1 前言
对于一个服务端开发来说 MYSQL 可能是他应用最相熟的数据库工具，然而，大部分的 Java 工程师对 MySQL 的理解和把握水平，大抵就停留在这么一个阶段: 它能够建库、建表、建索引，而后就是对外面的数据进行增删改查，语句性能有点差？没关系，在表里建几个索引或者调整一下查问逻辑就能够了，一条 sql，MYSQL 是如何解决的，为咱们做了什么，齐全是个黑盒。本文次要通过 sql 执行的过程突破这样一个黑盒的认知，来理解 MYSQL 的逻辑架构。
MYSQL 的逻辑架构可分为 3 层: 应用层、服务层、存储引擎层。其中存储引擎是 MYSQL 最有特色的中央，MySQL 区别于其余数据库的最重要特点是其插件式的表存储引擎，本文也将着重聊聊最罕用的 innoDB 存储引擎的架构设计原理，假如现有如下 sql：
update users set name=’zhangsan’where id = 10
作为一个 java 服务端工程师，见到这样一个 sql，本能的脑海中立即就浮现出如下信息：
一个表名为 users 的表
有两个字段 id、name，id 是主键
把 users 表里的 id=10 的这个用户名批改为“zhangsan”
那么 MYSQL 是如何解决这样一个 sql 呢？带着这个问题，咱们来看一下 MYSQL 是如何通过一个个组件来解决这个 sql，来理解 MYSQL 的整体架构
2 应用层
2.1 连接线程解决
当 MYSQL 面对下面的 sql，首先应该做什么呢？是如何解析？如何抉择索引？如何提交事务？当然不是，首先应该解决的是怎么把 sql 语句传给它。大家都晓得，如果咱们要拜访数据库，那么，首先就须要和数据库建设连贯，那么这个连贯由谁来建呢，答案就是 MYSQL 驱动，上面这段 maven 配置大家应该都很相熟
java 程序就是通过这个驱动包来与数据库建设网络连接。
下图示意：
从图中能够看到这样一个场景：java 程序很多个线程并发申请执行上述 sql，咱们都晓得数据库连贯是十分占用资源的，尤其是在高并发的状况下，如果每次都去建设数据库连贯就会有性能问题，也会影响一个应用程序的延展性，针对这个问题，连接池呈现了。
下图示意：
从图中可见网络连接交由线程 3 监听和读取 sql 申请，至此 MYSQL 曾经收到咱们的申请，当然 MYSQL 在建设连贯时还做了用户鉴权, 鉴权根据是: 用户名，客户端主机地址和用户明码; 在获取连贯后，解决申请时还会做 sql 申请的平安校验，依据用户的权限判断用户是否能够执行这条 sql。
3 服务层
3.1 SQL 接口
从上图中咱们晓得线程 3 负责监听并读取 sql，拿到这个 sql 之后，如何执行是一项极其简单的工作，所以 MYSQL 提供了 SQL 接口这么一个组件，线程 3 会将 sql 转交给 SQL 接口来执行如下图：
SQL 接口具体解决性能有：DDL、DML、存储过程、视图、触发器等。
3.2 SQL 解析器
接着问题来了，SQL 接口如何执行本文 sql 呢？，数据库怎么了解本文这个 sql 呢？置信懂 sql 语法的人立马就能晓得什么意思，然而 MYSQL 是个零碎不是人，它无奈间接了解 sql 的意思，这个时候要害的组件出场了，SQL 解析器的作用次要就是是解析 sql 语句，最终生成语法树，比方本文 sql 就能够拆解成如下几个局部：
- 须要从 users 表里更新数据
- 须要更新 id 字段是 10 的那行数据
- 须要把这行数据的 name 字段的值改为“zhangsan”
3.3 SQL 优化器
当通过 SQL 解析器了解了 sql 语句要干什么之后，该如何实现呢，以本文的更新语句为例，咱们能够有以下两种实现形式：
间接定位到 users 表中 id 字段等于 10 的一行数据，而后查出这行数据数据，而后设置 name 字段为“zhangsan”；
也能够通过更新 name 字段索引的形式在 name 索引上遍历 id 等于 10 的索引值，而后设置 name 字段为“zhangsan”。
下面两种路径都能实现最终后果，显然第一种门路更好一些，所以,SQL 优化器就是从泛滥实现门路当选则一条最优的门路进去，也就是咱们常说的执行打算。
3.4 执行器
通过 SQL 优化器咱们失去一套执行打算，那么，这个打算怎么执行呢？这个时候就不得不提 MYSQL 存储引擎，咱们都晓得 MySQL 和其余关系型数据库不一样的中央在于它的弹性以及能够通过插件模式提供不同品种的存储引擎，相似 java 接口的多实现，MYSQL 必定会有一套规范的存储引擎接口，而执行器就是依照执行打算一步一步的调用存储引擎接口实现 sql 执行而已，如下图：
上图专门将 binlog 标出来是为了和下文 innodb 存储引擎的 undo log、redo log 做辨别，强调 binlog 是 server 层的日志，后续 binlog 和 redo log 的两阶段形式实现事务的提交会再次提到。
3.5 查问缓存
MYSQL 服务层为谋求高效也引入了 QUERY BUFFER 这个组件，然而这个组件比拟鸡肋，缓存不仅须要 sql 全字匹配命中，而且对根底表的任何批改都会导致这些表的所有缓存生效，既不合乎当初用户变量的开发模式，大部分时候也不高效。MYSQL 从 5.7 开始不举荐应用默认敞开，8.0 中不再反对，具体起因如下图：
截图起源 MYSQL 开发者专区文档：MySQL :: MySQL 8.0: Retiring Support for the Query Cache
4 存储引擎层
4.1 概述
上文执行器拿到执行打算后，调用存储引擎的接口来实现 sql 的执行，那么存储引擎如何帮忙咱们去拜访、操作内存以及磁盘上的数据呢？咱们都晓得 MYSQL 的存储引擎有很多，实现形式各一，上面让咱们持续通过上文的 sql 来初步理解咱们罕用的 Innodb 存储引擎的外围原理和架构设计
重温一下本文 sql：
update users set name=’zhangsan’where id = 10 —- 历史 name =‘lisi’
4.2 缓冲池（buffer pool）
InnoDB 存储引擎中有一个十分重要的放在内存里的组件，就是缓冲池（Buffer Pool），这外面会缓存很多的数据，以便于当前在查问的时候，万一你要是内存缓冲池里有数据，就能够不必去查磁盘了，如下图：
缓冲池（buffer pool）在 Innodb 中的位置相似于咱们当初零碎设计中 redis 的位置，在 Innodb 中引入这一组件的就是为了高效的存取，咱们都晓得 MYSQL 查问数据很快，究其原因不止是索引查问，深层次的起因就是所有的增删改查都是在 buffer pool 这块内存上操作的，相比于操作磁盘，效率不言自明。
4.2.1 数据页、缓存页和脏页
还是拿咱们的 sql 举例，更新 id=10 的这条记录，难道从磁盘里只拉取 id=10 数据进入内存中吗？很显著不是，毕竟退出内存的记录不止这一张表，而且单表每行记录也不一样，内存治理会十分艰难的，所以，MYSQL 对数据抽象进去的一个叫数据页的逻辑概念，每页固定大小默认 16KB，能够存多条数据，并且 buffer pool 里的存储构造和数据页统一，这样内存治理就会简略的多，数据页注册元数据后加载进内存后就是缓存页。
从图中能够看到在缓存页在 sql 更新完还未刷回硬盘时数据和磁盘中的数据页是不统一的，这个时候咱们称这种缓存页为脏页。至于后续脏页如何落盘临时不提。
4.2.2 元数据
从上图咱们看到 buffer pool 中除了缓存页，还多了一个元数据内存构造，这个能够简略的了解为注销，比方因为疫情外地人回家过年会被当地政府进行注销，记录从哪来、到哪去等信息，便于管理，buffer pool 也是这样做的；然而元数据可不止记录缓存页的磁盘地址和内存地址这么简略，buffer pool 外围原理都是通过元数据来实现的
4.2.3 free 链表
buffer pool 在 MYSQL 初始化的时候，就依据配置在内存中申请了一块间断的空间，申请过后就按数据页的大小和元数据的大小进行正当的划分出很多个间断的、空的缓存页, 当须要查问数据的时候就会从磁盘读入数据页放入到缓存页当中, 然而因为脏页的存在，数据还未刷盘不能应用，那么数据页加载进哪个缓存页就是个问题。为了解决哪些缓存页是闲暇的，MYSQL 团队为 Buffer pool 设计了一个 free 链表，它是一个双向链表的数据结构，这个 free 链表里每个节点就是一个闲暇的缓存页的元数据块地址，也就是说只有一个缓存页是闲暇的，那么他的元数据块就会放入这个 free 链表中, 这样加载数据页是只须要从 free 链表中找闲暇的缓存页即可。
从图中即可看出链表的大抵构造，那么当初咱们要更新 users 表中 id=10 的记录，首先要晓得 id=10 这条记录的数据页有没有在缓存页当中，而后在决定是否是加载数据页还是间接应用缓存页，所以，buffer pool 里还有左下角这种 hash 表，用表空间 + 数据页号作为 key，缓存页地址为 value，能够疾速判断数据页是否被缓存。
4.2.4 flush 链表
本文 sql 执行更新后，这样就导致内存中的数据和磁盘上的数据不统一，这就表明这个缓存页是脏页，脏页是须要刷新到磁盘文件的。然而不可能所有缓存页都刷回磁盘，比方有的缓存页可能只是查问的时候用到了，没有别更新过，所以数据库就引入 flush 链表，flush 链表和 free 链表的实现形式一样，都是在元数据中减少两个指针做成双向链表，用来标记链表上的都是脏页，须要刷回磁盘，后续 IO 线程异步刷盘就是将 flush 链表的数据刷盘，而后把缓存页移除 flush 链表，退出 free 链表当中。
4.2.5 LRU 链表
随着不停的把磁盘上的数据页加载到闲暇的缓存页里去，free 链表中闲暇的缓存页越来越少，如果 free 链表空了，这时候就无奈从磁盘加载数据页了，这时候就须要淘汰掉一些缓存页，首先想到的就是把批改过的缓存页刷新回磁盘上，而后清空这个缓存页
具体抉择哪个缓存页进行清空呢，数据库引入 LRU 链表, 构造和 free 链表基本一致, 最近拜访的缓存页都会被挪动到 LRU 链表的头部, 这样尾部的就是少拜访的数据，然而这样的 LRU 有个问题，就是 MYSQL 的预读机制，会把不常拜访或者不拜访的数据连带着加载到内存，这样就把这一部分也放在了 LRU 头结点上，很显著不合理, 同理，全表扫描也有这个问题。
从下面能够看出，如果此时须要淘汰缓存页，就可能把热点数据提前淘汰掉。对于这种不合理的 LRU 算法 MYSQL 基于冷热数据拆散的办法对 LRU 算法进行如下优化：LRU 链表被拆分为两个局部，一部分热数据，一部分冷数据，数据页第一次加载到缓存的时候是放在冷数据表头，在 1s 后再次拜访这个缓存页，就很有可能是热数据，就会把它挪到热数据表头区域，这样设计避免了刚加载就拜访造成的假热景象。
冷热区域缓存页挪动规定如下：
- 冷数据 -> 热数据
- 冷数据区的缓存页是在 1s 后再被拜访到就挪动到热数据区的链表头部
- 热数据 -> 冷数据
- 能留在热数据区域的缓存页，证实都是缓存命中率比拟高的，会常常被拜访到。如果每个缓存页被拜访都挪动到链表头部，那这个操作将会十分的频繁。所以 InnoDB 存储引擎做了一个优化，只有在热数据区域的后 3/4 的缓存页被拜访了，才会挪动到链表头部；如果是热数据区域的前 1/4 的缓存页被拜访到，它是不会被挪动到链表头部去的。这样尽可能的缩小链表中节点的挪动了
4.2.6 小结
当初咱们理解了更新数据会先把数据加载进 buffer pool 在进行，理解 buffer pool 是如何通过冷热数据拆散的机制优化 LRU 链表，为零碎设计中缓存过期淘汰策略提供的新的解决思路。既然，数据更新是把数据载入 buffer pool 中批改，那么更新完缓存页之后数据库是如何保障事务提交、如何保证数据页和缓存页数据统一的呢
4.3 undo log
说到事务就不得不提事务是如何回滚的，innodb 是引入了 undo log 的日志组件来实现事务回滚的，以本文 sql 为例, 在数据加载进缓存页后，批改之前，会将执行的 sql 取反保留在 undo log 中，逻辑相似 sql：
update users set name=’lisi’where id = 10
当然如果是 insert 语句与之对应的就是 delete 语句，delete 语句也就对应的 insert 语句，这也就明确为什么 delete 的数据是能够回滚，而 truncate 数据之后无奈回滚的根本原因，在于 truncate 无奈生成 undo log。
上图是本问 sql 执行的大抵步骤，至于退出 buffer pool 这块上文曾经具体理解过了，就不在赘述。从图中能够看出因为 log 间接刷盘比拟损耗性能，所以引入 log buffer 进行缓存，而后在通过异步的形式把数据刷入磁盘既然数据更新之前的数据记录下来并胜利刷入磁盘，则事务的回滚就不难实现了。
当然 undo log 除了提供回滚性能，还为多版本并发管制（MVCC）提供了实现根底，实现了 MYSQL 的非阻塞读写，进步了零碎的并发性。本文也不再深刻
4.4 redo log
上面来理解一下 innodb 是如何保障 buffer pool 缓存的数据一致性问题，数据更新值内存后并不会立刻刷新至磁盘数据页，而是统一以脏页的模式保留在 buffer pool 当中，这样做有两个起因会导致效率很差，一个是内存向磁盘写数据自身效率就慢，另一个就是随机 IO 会写磁盘的工夫上附加上很多磁头寻址的工夫，所以立刻刷数据页效率很低。
Innodb 是如何躲避上述问题的呢，失常状况下，异步刷盘就曾经能够解决了刷磁盘慢的问题，然而，如果 MYSQL 零碎解体、宕机，这时候脏页还未及时刷盘，那么缓存页期间所有改变数据岂不是丢了，所以，Innodb 引入了另一个组件 redo log，专门记录数据被缓存期间做过的批改记录，而后立刻写入 redo log 磁盘文件，相比于缓存页刷盘，redo log 刷盘的数据了小多了，并且写 redo log 是程序 IO，而缓存页刷盘是随机 IO。下图示意：
这样当数据库异样宕机时，即便缓存页失落数据也不会失落，因为 redo log 曾经落盘，数据库重启的时候会更近 redo log 把磁盘上历史的数据页从新载入内存，从新按 redo log 的批改记录操作一遍就能将缓存页中的数据恢复至宕机前的状态。
如果零碎宕机时，redo log 还败落盘数据岂不是丢了，对，这种状况下数据会丢，这种 redo log 丢数据分两中状况：
第一种状况，MYSQL 有三种刷盘策略，通过 innodb_flush_log_at_trx_commit 参数进行配置
- 配置为 0：事务提交的时候不会把 redolog buffer 里的数据立刻刷入磁盘，此时如果宕机则会导致已提交的数据批改失落；
- 配置为 1：则是事务提交的时候必须把 redolog buffer 里的数据刷入磁盘，以保障事务提交后操作数据日志不丢；
- 配置为 2：则示意只是把数据交给操作系统进行刷盘，操作系统刷没刷胜利则不论，实践上操作系统刷盘是先要通过 os cache 内存缓存的，就是说数据会先在 os chache 里没有真正的落盘，这种模式下也可能导致数据失落
这第一种状况如果产生丢数据，是真的失落，所以，如果对数据库失落数据零容忍，倡议配置策略为 1
第二种状况，就是未写 commit 标记日志的状况，即下图第 9 步失落的状况，然而这种状况零碎认为事务提交失败，所以失落了并不影响数据一致性。
图中 7、8、9 三个步骤是事务提交 commit 的时候才做的（本文只用一个 sql 来解说，默认事务主动提交），redo log 记录更新记录之后，执行器会把批改记录写在 server 层的 binlog 当中，很显著这是两个文件，如果呈现上述宕机等异常情况，这两个文件的数据一致性是不能保障的，所以，为了保障两个文件的数据一致性，innodb 会在 binlog 写完之后在 redo log 中补上一个 commit 标记通知 redo log 事务胜利。事务执行胜利后操作 redo log 刷入磁盘，至此本文 sql 执行胜利。
5 总结
通过一条 update 的 sql 的更新流程，清晰的看到 MYSQL 的整体架构设计，对 Innodb 存储引擎的几大外围组件如何相互协作、配合以实现高效的数据库系统有了更清晰的意识；外围组件 buffer pool 的冷热数据拆散的缓存淘汰机制也为当前零碎的架构设计提供了新的解决思路。
作者：陆庆林