MySQL高级篇之逻辑架构
1.逻辑架构-基本架构
1.1MySQL底层基本的结构
-
连接层
- 连接池
-
服务层
- sql接口
- 查询缓存
- 解析器
- 优化器
-
引擎层
- 插拔式引擎
-
其他
- 文件系统
- 日志
1.2MySQL底层架构视图
-
Conncetors
为客户端连接器- 比如在Java中的JDBC
-
Connection Pool
为连接池- 主要的作用是当出现多个客户端连接服务器的时候,为多连接提供条件
-
SQL Interface
为SQL接口- 主要的作用是:接收SQL语句,最后再将查询到的结果返回到客户端中
-
Parser
为解析器- 主要的作用是:解析SQL语法和词性的分析,最后生成一颗语法树
-
Optimizer
为优化器- 主要的作用是:优化SQL代码,最后给出一个执行计划
-
Chaches & Buffers
为查询缓存- 主要的作用是:以键值对的形式保存SQL执行语句及其结果
-
Pluggable Stroage Engine
为插拔式存储引擎- 主要的作用是:相当于表的类型
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协议将这些信息,尽力过三次握手之后传递到服务器端 -
连接核实
- 通过传递过来的三个参量,到
mysql
表中寻找host,user,authentication_string
字段,依次匹配 - 如果匹配成功,连接至服务器,并等待下一步的请求核实
- 如果匹配失败,永远都无法连接至服务器
- 通过传递过来的三个参量,到
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缓冲池的作用原理
- 缓冲池作为内存里面的一项,缓冲池会预先在磁盘中读取
使用频率高
的数据到缓冲池中- 缓冲池读取数字的单位是
一个数据页,数据页的大小是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
下的是可以处理的,引出了事务
的概念,以后再讲
3.底层执行顺序
注意:这个图非常的重要,需要默或者通过自己的理解画出来