对线HR_MySQL逻辑架构?就这?

爪哇土著、JOElib

已于 2022-05-28 22:54:49 修改

阅读量458

点赞数 21

分类专栏： SQL 文章标签： mysql 架构数据库

于 2022-05-28 22:50:06 首次发布

本文链接：https://blog.csdn.net/joelib/article/details/125024597

版权

SQL 专栏收录该内容

4 篇文章 1 订阅

订阅专栏

MySQL高级篇之逻辑架构

1.逻辑架构-基本架构

1.1MySQL底层基本的结构

连接层
- 连接池
服务层
- sql接口
- 查询缓存
- 解析器
- 优化器
引擎层
- 插拔式引擎
其他
- 文件系统
- 日志

1.2MySQL底层架构视图

在这里插入图片描述

Conncetors为客户端连接器
1. 比如在Java中的JDBC
Connection Pool为连接池
1. 主要的作用是当出现多个客户端连接服务器的时候,为多连接提供条件
SQL Interface为SQL接口
1. 主要的作用是:接收SQL语句,最后再将查询到的结果返回到客户端中
Parser为解析器
1. 主要的作用是:解析SQL语法和词性的分析,最后生成一颗语法树
Optimizer为优化器
1. 主要的作用是:优化SQL代码,最后给出一个执行计划
Chaches & Buffers为查询缓存
1. 主要的作用是:以键值对的形式保存SQL执行语句及其结果
Pluggable Stroage Engine为插拔式存储引擎
1. 主要的作用是:相当于表的类型

1.3连接层

1.3.1连接池与线程池

连接池的作用
- 连接池相当于为客户端和服务器端提供对应的连接,主要用于连接作用
线程池的作用
- 线程池相当于为多个连接提供多个线程,每一个线程相互独立完成对应的任务,即线程池适用于交互的
- 有了线程池,免去了创建和销毁线程的开销

1.3.2执行原理

首相,假设我们从Connectors对应的客户端进入登录界面,按照以下语法进行登录
```
mysql -u [user_name] -h [host] -p[passordword] -P 端口号 -e sql语句
```
我们依次提供了user_name 用户名,host ip地址,password 密码,然后通过TCP/IP协议将这些信息,尽力过三次握手之后传递到服务器端
连接核实
1. 通过传递过来的三个参量,到mysql表中寻找host,user,authentication_string字段,依次匹配
2. 如果匹配成功,连接至服务器,并等待下一步的请求核实
3. 如果匹配失败,永远都无法连接至服务器

1.4服务层

1.4.1SQL Inteface

SQL Interface主要是接受我们书写的SQL语句,比如说最传统的SELECT.. FROM ...查询语句等
SQL Interface主要针对于查询,返回查询结果,其他操作有对应的接口

1.4.2Parser

在该过程中会进行请求核实
- 请求核实会先访问user表,查看该表中这个操作是否有对应的权限
  - user,host构成联合主键
  - 所以里面的权限字段针对于所有的数据库和数据表
- 再访问db表,查看该表中这个操作是否有对应的权限
  - user,host,db构成联合主键
  - 所以里面的权限字段针对于当前数据库的所有的数据表
- 再访问tables_priv表,查看该表中这个操作是否有对应的权限
  - 其中user,host,db,tables_name构成联合主键
  - 所以里面的权限针对于当前数据库的当前数据表的所有的字段
- 再访问columns_priv表,查看该表中这个操作是否有对应的权限
  - 该表的权限是最细致的,即有没有该字段中的DML权限,其中user,host,db,tables_name,columns_name构成联合主键
  - 如果该表仍没有对应的权限,就会报错
  - 所以里面的权限针对于当前数据库的但钱数据表的当前的字段
词性解析
- ```
select field1,field2 from table_name where conditon ... 
```
  词性分析中,会分析在里面每一个单词属于什么类型,比如,select属于保留字,field1属于字段名,from属于保留字,table_name属于表名,…
语法解析
- 语法分析中,会根据语法生成对应的语法树,再判断语法是否正确
- 如果在语法树中出现错误,会立刻报错

1.4.3执行器

执行器会根据执行计划开始执行,即使用全表查询还是索引查询等

1.4.4Optimizer

Optimizer是优化器,主要对我们的SQL语句进行优化,最后生成一个执行计划,给执行器参考
主要有以下两个方面的优化
- 逻辑优化
  - 逻辑上的优化可以简单理解为,某一查询语句是否可以从多行子查询变成多表查询
- 物理优化
  - 是否可以用索引查询替代全表查询

1.4.5Chaches & Buffers

Chaches & Buffers是查询缓存,自MySQL8.0后,查询缓存被弃用
查询缓存的工作原理是将sql语句以字符串的形式保存到key中,最后将查询出来的结果保存在value中,如果下次的查询在缓存中存在就可以快速命中,但是命中率极低

1.4.5.1查询缓存为什么鸡肋

命中率极低,当我们的查询语句多一个空格或少一个空格就会查询不到

SELECT field1,field2 FROM table_name WHERE conditon;
SELECT  field1,field2 FROM table_name WHERE conditon;
#上面两个查询语句的结果肯定是一样的,但是底层存储的是字符串,两个的长度不同,系统认为不是一样的,命中不了

遇到时间函数的时候,往往也会失效,每一次的时间都不相同
```
SELECT NOW() FROM DUAL;
```

有时候可能发生意外的错误,比如

#假设查询缓存中有如下的缓存,结果是 '123'
SELECT field1 FROM table_name WHERE field2 = 1;

DELETE FROM table_name WHERE field2 = 1;
SELECT field1 FROM table_name WHERE field2 = 1;
#查询出来的结果仍然是'123',因为查询缓存+执行流程带来的错误

1.4.5.2查询缓存的删除的代码证实

SELECT @@profiling; #用于记录sql的执行状态的一个会话用户变量,默认情况下该变量都是OFF状态
SET SESSION profiling = 1; #将其打开 
-- 0 为关 1 为开
SELECT * FROM table_name;
SELECT * FROM table_name;

SHOW PROFILES; #查看所有操作所耗费的时间
SHOW PROFILE FOR QUERY ?; #? 表示上述所有操作中的第?行操作
#通过分别运行两个环境就可以看出是否删除
-- 此外,还可以查看其他属性,如cpu | block io等,在FOR之前添加即可

1.4.5.3MySQL5.7环境下如何打开查询缓存

#在my.cnf中修改如下
[mysqld]
query_cache_type = 1;
-- 1表示打开查询缓存
-- 2表示选择性打开查询缓存
-- 0表示关闭查询缓存 (默认是关闭的)

#重启mysql服务
restart mysqld;

1.5引擎层

引擎层针对的主要是存储引擎,后面会补充,否则太多了

1.6存储层

存储层针对的主要是 文件系统,后面会补充

2.逻辑架构-数据缓冲池

2.1数据缓冲池的作用

如果没有数据缓冲池,我们的数据都是存储在磁盘上的,针对Innodb该存储引擎,底层是以聚簇索引的形式存储元素的,当我们读取数据时,我们要多次和磁盘进行交互,即多次进行IO操作,效率极低
如果我们预先将数据加载到数据缓冲池,缓冲池在内存中,服务器直接从内存中取数据,效率极高

2.2查询缓存和数据缓冲池相等吗?

明显是不相同的,针对于查询语句,而数据缓冲池的作用是加载数据,两个概念截然不同

2.3缓冲池的作用原理

缓冲池作为内存里面的一项,缓冲池会预先在磁盘中读取使用频率高的数据到缓冲池中
1. 缓冲池读取数字的单位是一个数据页,数据页的大小是16kb
当我们查询语句查询某些数据时,我们会直接从缓冲池中取出
如果缓冲池里面没有,缓冲池会继续经行IO操作,将所需要的数据读取到缓冲池中,供我们读取

2.4缓冲池的预读特性

如果读取完某个数据页,缓冲池仍然有剩余的空间,会把数据页旁边的数据页也读取进来,该过程涉及到 索引的底层,以后再讲,太多了

2.5缓冲池的操作

2.5.1缓冲池默认大小的设置

SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; #查看缓冲池默认大小

#设置当前全局系统变量的方式改变[重启服务即失效]
SET GLOBAL innodb_buffer_pool_size = size; #size指代具体的大小,可以自行设置

#修改my.cnf配置文件的值,实现永久改变
[server]
innodb_buffer_pool = size

2.5.2多个缓冲池的实例

[server]
innodb_buffer_pool_instance = size #size 表示缓冲池实例的个数
#注意的是,如果缓冲池的总大小小于1G,设置多个实例是无效的

SHOW VARIABLES LIKE 'innodb_buffer_pool_instance'; #查看缓冲池实例个数
-- 注意的是:缓冲池的大小是总大小,分实例并不会干预总大小,分3个,每个的大小就是size / 3

2.5.3引申问题

先明确的是 更新数据并不是直接更新磁盘上的数据,而是更新缓冲池上的数据,缓冲池更新完的数据会定期与磁盘进行交互,避免频繁的交互导致效率降低
假如,我们交互到一半宕机了,剩下的数据没有更新到我们的磁盘中,我们称这一类数据为脏页(脏数据),我们如果在MYISAM引擎下是无法处理的,但是在INNODB下的是可以处理的,引出了事务的概念,以后再讲
假如,我们交互到一半宕机了,剩下的数据没有更新到我们的磁盘中,我们称这一类数据为脏页(脏数据),我们如果在MYISAM引擎下是无法处理的,但是在INNODB下的是可以处理的,引出了事务的概念,以后再讲