MySQL架构

1_逻辑架构

MySQL架构

1.1.连接层

系统(客户端)访问MySQL服务器前,做的第一件事就是 建立TCP连接

经过三次握手建立连接成功后,必须要分配给一个线程去执行后序流程。 每一个连接 从线程池中获取线程,省去了创建和销毁线程的开销。

MySQL服务器对TCP传输过来的账号密码做 身份认证权限获取

  • 用户名或密码不对,会收到一个 Access denied for user错误,客户端程序结束执行
  • 用户名密码认证通过,会 从权限表查出账号拥有的权限与连接关联,之后的权限判断逻辑,都将依赖于此时读到的权限

1.2.服务层

1.SQL Interface

接收用户的SQL命令,并且返回用户需要查询的结果。

MySQL支持DDL(数据定义语言)、DML(数据操作语言)、存储过程、视图、触发器、自定义函数等多种SQL语言接口。

2.Parser

在解析器中对SQL语句进行语法分析、语义分析。

在SQL命令传递到解析器的时候会被解析器验证和解析

  • 将SQL语句 分解成数据结构--语法树,并根据数据字典丰富查询语法树,后续步骤SQL语句的传递 和处理就是基于这个结构的。如果在分解构成中遇到错误,那么就说明这个SQL语句是不合理的。
  • 验证该客户端是否具有执行该查询的权限

3.Optimizer

SQL语句在语法解析之后、查询之前会使用查询优化器确定SQL语句的执行路径,生成一个执行计划。

  • 这个执行计划表明应该使用哪些 索引进行查询(全表检索还是使用索引检索),表之间的 连接顺序如何, 最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询,并将查询结果返回给用户。
  • 它使用” 选取-投影-连接“策略进行查询。

4.Caches&Buffers

MySQL内部维持着一些Cache和Buffer,比如Query Cache用来缓存一条SELECT语句的执行结果,如果能够在其中找到对应的查询结果,

这样,您就不必经历查询解析、优化和执行的整个过程,并将结果直接反馈给客户端。

[En]

Then you don’t have to go through the whole process of query parsing, optimization, and execution, and feed back the results directly to the client.

  • 这个缓存机制是由一系列小缓存组成的。比如表缓存,记录缓存,key缓存,权限缓存等。
  • 该查询缓存可以在不同的客户端之间共享。
    [En]

    this query cache can be shared between different clients.*

注意:从MySQL 5.7.20开始,不推荐使用查询缓存,并在MySQL 8.0中删除。

1.3.引擎层

真正的负责了MySQL中数据的存储和提取,对物理服务器级别维护的底层数据执行操作

服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同,这样我们可以根据自己的实际需要进行选取。

2_SQL执行流程

MySQL架构

2.1.执行流程

SQL 语句在 MySQL 中的流程是:

SQL语句→查询缓存→解析器→优化器→执行器

1.查询缓存

Server如果在查询缓存中发现了这条SQL语句,就会直接将结果返回给客户端;如果没有,就进入到解析器阶段。

需要说明的是,因为查询缓存往往效率不高,所以在 MySQL8.0 之后就抛弃了这个功能。

  • MySQL中的查询缓存,不是缓存查询计划,而是 缓存查询对应的结果。这就意味着 只有相同的查询操作才会命中查询缓存。两个查询请求在任何字符上的不同(例如:空格、注释、大小写),都会导致缓存不会命中。

  • 如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表,如 mysql、information_schema、 performance_schema 数据库中的表,那这个请求就不会被缓存。

  • MySQL的缓存系统会监测涉及到的每张表,只要该表的结构或者数据被修改,那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除!

2.解析器

在解析器中对 SQL 语句进行语法分析、语义分析。

分析器先做” 词法分析“。你输入的是由多个字符串和空格组成的一条SQL语句,MySQL需要识别出里面的字符串分别是什么,代表什么。

接着,要做” 语法分析“。根据词法分析的结果,语法分析器(比如:Bison)会根据语法规则,判断你输入的这个SQL语句是否满足MySQL语法。

如果SQL语句正确,则会生成一个这样的语法树:

3.优化器

在优化器中会确定SQL语句的 执行路径,比如是根据全表检索,还是根据索引检索等。

举例:

select * from test1 join test2 using(ID)
where test1.name='zhangwei' and test2.name='mysql高级课程';
  • 方案1:可以先从表 test1里面取出 name=’zhangwei’ 的记录的 ID值,再根据 ID 值关联到表test2, 再判断test2 里面name的值是否等于’mysql高级课程’。
  • 方案2:可以先从表 test2 里面取出 name=’mysql高级课程’ 的记录的 ID值,再根据 ID值关联到 test1, 再判断 test1 里面 name 的值是否等于 zhangwei。

这两种执行方法的逻辑结果是一样的,但是执行的效率会有不同,而 优化器的作用就是决定选择使用哪一个方案

在优化器阶段完成后,确定该语句的执行计划,然后进入执行器阶段。

[En]

After the optimizer phase is complete, the execution plan for this statement is determined and then enters the executor phase.

在查询优化器中,可以分为 逻辑查询优化阶段和 物理查询优化阶段。

4.执行器

到目前为止,我们还没有真正读或写真正的表,只是产生了一个执行计划。因此,我们进入了执行机构阶段。

[En]

Up to now, we haven’t really read or written the real table, but just produced an execution plan. So we entered the actuator phase.

在执行之前需要判断该用户 是否具备权限。如果没有,就会返回权限错误。如果具备权限,就执行 SQL 查询并返回结果。

在 MySQL8.0 以下的版本,如果设置了查询缓存,这时会将查询结果进行 缓存

2.2.执行原理

1.确认profiling 是否开启

#查看profiling是否开启
select @@profiling;
show variables like 'profiling';
#开启profiling
set profiling=1;

2.查看profiles

#查看当前会话产生的所有profile
show profiles;

3.查看profile

#显示最后一条执行计划
show profile;
#显示指定的执行计划
show profile for query 7;

3_数据库缓冲池

InnoDB 存储引擎是以页为单位来管理存储空间的,我们进行的增删改查操作其实本质上都是在访问页 面(包括读页面、写页面、创建新页面等操作)。而磁盘 I/O 需要消耗的时间很多,而在内存中进行操 作,效率则会高很多,为了能让数据表或者索引中的数据随时被我们所用,DBMS 会申请 占用内存来作为 数据缓冲池在真正访问页面之前,需要把在磁盘上的页缓存到内存中的 Buffer Pool 之后才可以访 问。

3.1.缓冲池

在 InnoDB 存储引擎中有一部分数据会放到内存中,缓冲池则占了这部分内存的大部分,它用来存储各种 数据的缓存,如下图所示:

缓存池的重要性:以让磁盘活动最小化,从而 减少与磁盘直接进行 I/O 的时间

缓存原则:位置 * 频次“原则,可以帮我们对 I/O 访问效率进行优化。

  • 位置决定效率,提供缓冲池就是为了在内存中可以直接访问数据。
  • 频次决定优先级顺序。因为缓冲池的大小是有限的,有时无法将所有数据都加载到缓冲池里,这时就涉及到优先级顺序,会优先对使用频次高的热数据进行加载 。

3.2.缓冲池读取数据

缓冲池管理器将尽最大努力保存常用数据。当数据库执行页面读取操作时,它会首先确定该页面是否在缓冲池中。

[En]

The buffer pool manager will try its best to save the frequently used data. * when the database performs a page read operation, it will first determine whether the page is in the buffer pool. *

  • 如果存在就直接读取
  • 如果不存在,则在读取页面之前,通过内存或磁盘将其存储在缓冲池中。数据库中缓存的结构和功能
    [En]

    if it does not exist, the page is stored in the buffer pool through memory or disk before it is read. The structure and function of caching in database*

3.3.缓冲池操作

查看缓冲池大小。

#查看innodb的缓冲池大小
show variables like 'innodb_buffer_pool_size';

设置缓冲池大小

#设置innodb的缓冲池大小为268435456
set global innodb_buffer_pool_size = 268435456;
innodb_buffer_pool_size = 268435456;

查看缓冲池个数

#查看innodb的缓冲池个数
show variables like 'innodb_buffer_pool_instances';

设置缓冲池个数

#设置innodb的缓冲池个数为2
innodb_buffer_pool_instances = 2

4_存储引擎

4.1.存储引擎操作

查看MySQL提供的存储引擎

show engines;

查看系统默认存储引擎

show variables like '%storage_engine%';
SELECT @@default_storage_engine;

设置系统默认存储引擎

#设置默认存储引擎为MyISAM
SET DEFAULT_STORAGE_ENGINE=MyISAM;
#修改配置文件,设置默认存储引擎为MyISAM
default-storage-engine=MyISAM

设置表的存储引擎

(1)建表时指定存储引擎

CREATE TABLE 表名(
   建表语句;
) ENGINE = 存储引擎名称;

(2)修改表的存储引擎

#修改tab表的存储引擎为InnoDB
ALTER TABLE tab  ENGINE = InnoDB;

4.2.MySQL存储引擎

1.InnoDB

InnoDB 引擎:具备外键支持功能的事务存储引擎

  • InnoDB是MySQL的 默认事务型引擎,它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。
  • InnoDB是 为处理巨大数据量的最大性能设计

除了增加和查询外,还需要更新、删除操作,那么,应优先选择InnoDB存储引擎。

除非有非常特别的原因需要使用其他的存储引擎,否则应该优先考虑InnoDB引擎。

数据文件结构:

  • 表名.frm 存储表结构(MySQL8.0时,合并在表名.ibd中)
  • 表名.ibd 存储数据和索引

2.MyISAM

MyISAM 引擎:主要的非事务处理存储引擎

  • MyISAM提供了大量的特性,包括全文索引、压缩、空间函数(GIS)等,但MyISAM 不支持事务、行级锁、外键,有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
  • 优势是访问的速度快,对事务完整性没有要求或者以SELECT、INSERT为主的应用

数据文件结构:

  • 表名.frm 存储表结构
  • 表名.MYD 存储数据 (MYData)
  • 表名.MYI 存储索引 (MYIndex)

InnoDB和MyISAM对比:

Archive

CSV

Memory

… …

Original: https://www.cnblogs.com/gmkun/p/mysql_fw.html
Author: 九虑
Title: MySQL架构

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/505434/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

  • 项目管理构建工具——Maven(基础篇)

    项目管理构建工具——Maven(基础篇) 在前面的内容中我们学习了JDBC并且接触到了jar包概念 在后面我们的实际开发中会接触到很多jar包,jar包的导入需要到互联网上进行就会…

    数据库 2023年6月14日
    0111
  • 数据库

    建库操作 #创建数据库(默认字符集编码) create database test20210420 #创建数据库的时候指定字符集编码以及字符校验规则 create database…

    数据库 2023年5月24日
    0120
  • 设计模式之(5)——原型模式

    上篇文章中我们提到单例模式可以避免重复创建消耗资源的对象,但是却不得不共用对象。若是对象本身也不让随意访问修改时,怎么办?那么我们就可以采用原型模式来创建新的实例。 定义:原型模式…

    数据库 2023年6月14日
    0131
  • English words 929 9:00

    本文来自博客园,作者:ukyo–BlackJesus,转载请注明原文链接:https://www.cnblogs.com/ukzq/p/16743070.html Or…

    数据库 2023年6月11日
    0141
  • Python学习笔记(十一)– Django API RESTful

    (1)路由;(2)视图类(提供给用户访问相当于原来的视图函数);(3)序列化类(提供给视图类使用,把对象序列化成Json) 注意:使用rest_framework,需先 setti…

    数据库 2023年6月16日
    0149
  • Linux的一些的常用命令

    小杰笔记: 记录一下Linux的一些常见命令: 1:Linux关机与重启的命令: 2:切换目录与查看目录的文件: 3:文件目录的创建与删除: 4:文件的复制 删除与移动: 5:如何…

    数据库 2023年6月6日
    0106
  • 定时调度的线程池

    定时调度线程池 当我们需要定时进行线程的调度 @Slf4j public class Test5 { public static void main(String[] args) …

    数据库 2023年6月11日
    0116
  • 2022-08-17 DQL—-子查询,日期格式

    子查询、日期格式 DQL查询语言 子查询 根据结果集中的行数,子查询可以分为以下几类: [En] According to the number of rows in the re…

    数据库 2023年5月24日
    0130
  • fiddler的mock数据与二次开发示例

    fiddler的使用记录 fiddler了解 上官网下载工具,然后安装使用,https://www.telerik.com/fiddler,如果对该工具不熟悉,还有直白的教程,看过…

    数据库 2023年6月6日
    0160
  • 使用postman Mock后端响应

    使用postman Mock后端响应 接口文档开发与评审后,前后端各自依照标准进行开发,此时前端人员有以下选择: 使用工具自己mock构造后端数据验证已开发页面 在项目中自己编写添…

    数据库 2023年6月6日
    0146
  • Isilon 的OneFs常见操作命令(一)

    1背景知识: Isilon的oneFS是基于Free BSD的,FreeBSD 是一种类UNIX操作系统,因此有些类似Linux操作系统的常见命令可以直接使用,但有些又略微差别,需…

    数据库 2023年6月14日
    0395
  • IDEA中Git的使用

    Git在IDEA中的使用 JAVA技术交流群:737698533 创建和导入 创建一个新项目到Gitee上 首先创建一个仓库,勾选上初始化 获取新创建仓库的路径 然后随便在一个文件…

    数据库 2023年6月16日
    0125
  • Virtualbox中ubuntu18配置静态ip地址及DNS

    python开发环境:VirtualBox+Ubuntu18.04 每当我重启电脑重新打开VirtualBox,Ubuntu中的IP地址都会改变,这样,我的开发软件的连接解析地址也…

    数据库 2023年6月6日
    0152
  • 基于Redis&MySQL接口幂等性设计

    基于Redis&MySQL接口幂等性设计 欲把相思说似谁,浅情人不知。 幂等性即多次调用接口或方法不会改变业务状态,可以保证重复调用的结果和单次调用的结果一致。 前端重复提…

    数据库 2023年6月14日
    0120
  • 【黄啊码】MySQL入门—4、掌握这些数据筛选技能比你学python还有用-1

    大家好!我是黄啊码,今天没继续select * 了吧,如果还继续,那接下来的课程先别学,回去好好把之前的课程重复复习一遍,学明白了我们再会?废话不多说,学今天的课程之前我们先来说说…

    数据库 2023年6月16日
    0109
  • MySQL的undo日志—MVCC前置知识

    undo日志 前面学习了redo日志,redo日志保证的是崩溃时事务持久性。我们可以从redo日志恢复到系统崩溃以前。 undo日志就是为了保证事务回滚时事务所作所为都能回到事务执…

    数据库 2023年5月24日
    0168
亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球