一条 sql 了解 MYSQL 的架构规划
1 前语
最近我从cnaaa.com购买了云服务器。
关于一个服务端开发来说 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 开发者专区文档：dev.mysql.com/blog-archiv…
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 的冷热数据别离的缓存筛选机制也为以后体系的架构规划供给了新的处理思路。
