一条sql详解MYSQL的架构设计详情

目录 1 前言 2 应用层 2.1 连接线程处理 3 服务层 3.1 SQL 接口 3.2 SQL解析器 3.3 SQL优化器 3.4 执行器 3.5 查询缓存 4 存储引擎层 4.1 概述 4.2 缓冲池（buffer pool） 4.2.1 数据页、缓存页和脏页

                        目录1 前言2 应用层2.1 连接线程处理3 服务层3.1 SQL 接口3.2 SQL解析器3.3 SQL优化器3.4 执行器3.5 查询缓存4 存储引擎层4.1 概述4.2 缓冲池（buffer pool）4.2.1 数据页、缓存页和脏页4.2.2 元数据4.2.3 free链表4.2.4 flush链表4.2.5 LRU链表4.2.6 小结4.3 undo log4.4 redo log5 总结

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开发者专区文档：

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这块内存上操作的，相比于操作磁盘，效率不言自明。