MySQL的逻辑架构--逻辑架构剖析、SQL执行流程、数据库缓冲池（buffer pool）

才疏学浅的木子

发布于 2022-11-22 10:38:13

6420

发布于 2022-11-22 10:38:13

文章被收录于专栏：CSDN文章

👨‍💻个人主页： 才疏学浅的木子 🙇‍♂️ 本人也在学习阶段如若发现问题，请告知非常感谢 🙇‍♂️ 📒 本文来自专栏： MySQL 🌈 每日一语：人的一生，好不好只有自己知道，乐不乐只有自己明白。*** 🌈

逻辑架构

逻辑架构剖析

Connectors

Connectors指的是不同语言与SQL的交互，本质上还是TCP连接

第一层：连接层

客户端访问MySQL服务器前，做的第一件事就是建立TCP连接经过三次握手建立连接成功后，MySQL服务器对TCP传输过来的账号密码做身份认证、权限获取为了避免连接无线创建与TCP频繁创建销毁带来的资源耗尽、性能下降问题。MySQL服务器有专门的TCP连接池限制连接数，采用长连接模式复用TCP连接

TCP连接收到请求后，必须分配给一个线程专门与这个客户端的交互，所以还有个线程池，每一个连接从线程池中获取线程，省去了创建和销毁线程的开销所以连接管理的职责就是负责认证、管理连接、获取权限信息

第二层：服务层

SQL Interface：SQL接口 接收用户的SQL语句，并且返回用户需要的查询结果。 MySQL支持DML、DDL、存储过程等多种SQL语言的接口
Parser：解析器 在解析器中对SQL语句进行语法分析、语义分析。将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构在SQL命令传到解析器的时候会被解析器验证和解析，并为其创建语法树，并根据数据字典丰富查询语法树，会验证该客户端是否具有执行该查询的权限。创建好语法树后，MySQL还会对SQL查询进行语法上的优化，进行查询重写。
Optimizer：优化器 SQL语句在语法解析之后、查询之前会使用查询优化器确定SQL语句的执行路径，生成一个执行计划。这个计划表明应该使用那些索引进行查询（全表检索还剩使用索引检索），表之间的连接顺序，最后会按照计划中的步骤调用存储引擎提供的方法来真正执行查询，并将结果返回给用户使用选取-投影-连接策略进行查询
Caches&Buffers：查询缓存组件 MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条SELECT语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过程，直接将结果反馈给客户端这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等这个查询缓存可以在不同客户端共享从MySQL5.7.20开始，不推荐使用查询缓存，并在MySQL8中删除

第三层：引擎层

和其他数据库相比，MySQL的架构可以在多种场景中应用并发挥良好的功能，主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。插件式存储引擎层，真正的负责了MySQL中数据的存储和提取，对物理服务器级别维护的底层数据进行操作，服务器通过API与存储引擎进行通信。

存储层

所有的数据，数据库、表的定义，表的每一行内容，索引，都是存在文件上，以文件的形式存在的，并完成与存储引擎的交互。

SQL执行流程

MySQL的SQL执行流程

MySQL的查询过程 1、查询缓存如果在查询缓存中发现了这条SQL语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。（可通过query_cache_type参数开关查询缓存 0 代表关闭OFF 1 代表开启 ON 3 代表DEMAND，sql语句有SQL_CACHE时） 2、解析器如果没有命中缓存，就要开始真正执行语句。首先，MySQL需要知道你要做什么，因此需要对SQL语句做解析。SQL语句的分析分为词法分析与语法分析 分析器先做词法分析，MySQL需要识别出里面的字符串分别是什么，代表什么，从‘select’关键字识别出这是一个查询语句，它要把字符串T识别为表名T把ID识别为列名接着是语法分析，根据词法分析结果，语法解析器会根据语法规则，判断输入的SQL语句是否满足MySQL语法 3、优化器在优化器中会确定SQL语句的执行路径，比如根据全表检索还是根据索引检索。经过了解析器，MySQL就知道你要做什么了，在开始执行前还要先经过优化器的处理。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。在查询优化器中，可以分为逻辑查询优化阶段和物理查询优化阶段 逻辑查询优化就是通过改变SQL语句的内容来使得SQL查询更加高效，同时为物理查询优化提供更多的候选执行计划。通常采用的方式是对SQL语句进行等价变换，对查询进行重写，而查询重写的数学基础就是关系代数。对条件表达式进行等价谓词重写、条件简化，对视图进行重写，对子查询进行优化，对连接语句进行了外连接消除、嵌套连接消除等。 物理查询优化基于关系代数进行的查询重写，而关系代数的每一步都对应着物理计算。在这个阶段里，对于单表和多表的连接操作，需要高效地使用索引，提升查询效率。 4、执行器在执行之前需要判断该用户是否具备权限，如果没有就会返回权限错误。如果有权限，就打开表继续执行，执行器会根据表的引擎定义，调用存储引擎API对表进行读写。存储引擎API只是抽象接口，下面还有个存储引擎层

MySQL中的执行原理

了解查询语句底层执行的过程：select @@profiling；或者show variables like '%profiling%'查看是否开启了计划。开启它可以让mysql手机在SQL执行时所使用的资源情况

查看最近一条

查看指定一条

show profile [type,type] for query n limit row_count [offset offset] type： ALL --显示所有参数的开销信息 BLOCK IO --显示IO相关开销 CONTEXT SWITCHES --上下文切换相关开销 CPU --显示CPU相关开销信息 IPC --显示发送和接收相关开销信息 MEMORY --显示内存相关开销信息 PAGE FAULTS --显示页面错误相关开销信息 SOURCE --显示和Source_function,Source_file，Source_line相关开销信息 SWAPS --显示交换次数相关开销信息

数据库缓冲池（buffer pool）

InnoDB存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作其实本质上都是在访问页面。而磁盘I/O需要消耗的时间很多，而在内存中进行操作，效率则会高很多，为了能让数据表或者索引数据被我们所用，DBMS会申请占用内存来作为数据缓冲池，在真正访问页面之前，需要把磁盘上的页缓存到内存中的buffer pool之后才能访问，从而让磁盘活动最小化，减少与磁盘直接进行I/O的时间。

缓冲池vs查询缓存

缓存原则 位置 * 频次这个原则，可以帮我们对I/O访问效率进行优化首先位置决定效率，提供缓冲池就是为了在内存中可以直接访问数据其次，频次决定优先级顺序。因为缓冲池的大小是有限的，会优先对使用频次高的热数据进行加载
缓冲池的预读特性 缓冲池的作用就是提升I/O效率，而我们进行读取数据的时候存在一个局部性原理，也就是说我们使用了一些数据，大概率还会使用它周围的一些数据，因此采用预读机制提前加载，可以减少未来可能的磁盘I/O操作

查询缓存

查询缓存是提前把查询结果缓存起来，这样下次不需要执行就能拿到结果。需要说明的是，在MySQL中的查询缓存，不是缓存查询计划，而是查询对应的结果。因为命中条件苛刻，而且只要数据表发生变化，查询缓存就会失效，因此命中率低。缓冲池服务于数据库整体的I/O操作，它们的共同点都是通过缓存的机制来提升效率

缓存池如何读取数据

缓冲池管理器会尽量将使用的数据保存起来，在数据库进行页面操作读操作的时候，首先会判断该页是否存在缓冲池中，如果存在就直接读取，如果不存在，就会通过内存或磁盘将页面放到缓冲池中再进行读取。

如果我们执行SQL语句的时候更新了缓冲池中的数据，那么这些数据会马上同步到磁盘吗? 实际上，当我们对数据库中的记录进行修改的时候，首先会修改缓冲池中页里面的记录信息，然后数据库会以一定的频率刷新到磁盘。缓冲池会采用一种叫做checkpoint的机制将数据回写到磁盘上。当缓冲池不够用时，需要释放掉一些不常用的页，此时就可以强行采用checkpoint的方式，将不常用的脏页回写到磁盘上，然后再从缓冲池中将这些页释放掉。

查看/设置缓冲池的大小

可以使用innodb_buffer_pool_size变量来查看缓冲池的大小

show variables like ‘innodb_buffer_pool_size’

修改缓冲池大小

set global innodb_buffer_pool_size =

多个Buffer Pool实例

Buffer Pool的本质是InnoDB向操作系统申请的一块连续的内存空间，在多线程环境下，访问Buffer pool中的数据都需要加锁处理。在Buffer pool特别大而且多线程并发访问特别高的情况下，单一的Buffer Pool可能影响处理的请求速度。所以在Buffer Pool特别大的时候，我们可以把它们差分成若干个小的Buffer Poll，它们独立出去，独立申请内存空间，独立的管理各种链表，从而提高并发处理能力