Mysql系列_高级_01_Mysql内部组件结构

最新推荐文章于 2022-11-07 16:30:24 发布

二毛不在线

最新推荐文章于 2022-11-07 16:30:24 发布

阅读量100

点赞数

分类专栏： Mysql系列

本文链接：https://blog.csdn.net/qq_36520891/article/details/118614581

版权

Mysql系列专栏收录该内容

12 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL的内部组件，包括连接器的连接验证与管理，查询缓存的工作原理及优化，以及分析器、优化器和执行器在SQL执行过程中的作用。此外，还探讨了bin-log归档特性及其在数据恢复中的应用。内容涵盖MySQL权限管理、性能优化和数据安全等方面。

摘要由CSDN通过智能技术生成

Mysql内部组件结构

在这里插入图片描述

连接器

定义
负责跟客户端建立连接、获取权限、维持和管理连接的部分

建立连接和获取权限

#建立连接
mysql ‐h host[数据库地址] ‐u root[用户] ‐p root[密码] ‐P 3306

如果用户名或者密码不正确，那么就会返回"Access denied for user"错误
如果验证通过，连接器便会查出所拥有的用户权限，之后这个连接的权限判断都依赖与生产连接时读取的权限资料因此一个用户成功建立连接后，即使你用管理员账号对账号的权限做了修改，也不会影响已经存在的连接的权限，等用户再次连接时才会使用新的权限设置

维持和管理连接

#查看连接状态信息
show processlist
#查看空闲连接时长
 show global variables like "wait_timeout";
 #设置空闲连接时长
 set global wait_timeout=28800;

Command 列显示为“Sleep”，表示为空闲连接，如果空闲时间超过参数 wait_timeout设置的时间(默认为8小时)，就会自动断开连接

查询缓存

定义
Mysql用来存储之前的执行语句和查询结果的部分，以key-value的形式被存在内存中，key是查询的语句，value是查询的结果

缺点
查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空，因此对于更新比较频繁的数据库，查询缓存的命中率非常低

正确使用
在my.cnf表中将query_cache_type 设置成 DEMAND，对于默认的SQL都不执行查询缓存，使用SQL_CACHE 显式指定在静态表(基本数据不会改变的表)中使用查询缓存

my.cnf
#query_cache_type有3个值 0代表关闭查询缓存OFF，1代表开启ON，2（DEMAND）代表当sql语句中有SQL_CACHE 关键词时才缓存
query_cache_type=2

#显式指定使用查询缓存
select SQL_CACHE * from test where ID=5;
#查看当前mysql实例是否开启缓存机制
show global variables like "%query_cache_type%";
#查看运行的缓存信息
show status like '%Qcache%'; 
#临时关闭查询缓存
set global query_cache_size=0;//设置查询缓存区大小
set global query_cache_type=0;

在这里插入图片描述

Qcache_free_blocks:表示查询缓存中目前还有多少剩余的blocks，如果该值显示较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整理。
Qcache_free_memory:查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，DBA可以根据实际情况做出调整。
Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。
Qcache_inserts: 表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数，次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。
Qcache_lowmem_prunes:该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小。
Qcache_not_cached: 表示因为query_cache_type的设置而没有被缓存的查询数量。
Qcache_queries_in_cache:当前缓存中缓存的查询数量。
Qcache_total_blocks:当前缓存的block数量。

mysql8.0已经移除了查询缓存功能

分析器

定义
Mysql对执行sql进行词法分析(识别sql中各个词的含义)、语法分析(判断执行sql是否符合Mysql语法规则)并生成语法树的部分
在这里插入图片描述

优化器

定义
优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序的部分

执行器

根据表定义选择对应的执行引擎执行sql语句

bin-log归档

定义
binlog是Server层实现的二进制日志,他会记录我们的cud操作

特点

Binlog在MySQL的Server层实现（引擎共用）
Binlog为逻辑日志,记录的是一条语句的原始逻辑
Binlog不限大小,追加写入,不会覆盖以前的日志

开启bin-log

#配置my.cnf开启binlog 
log‐bin=/usr/local/mysql/data/binlog/mysql‐bin 
#注意5.7以及更高版本需要配置本项：server‐id=123454（自定义,保证唯一性）; 
#binlog格式，有3种statement,row,mixed 
binlog‐format=ROW 
#表示每1次执行写入就与硬盘同步，会影响性能，为0时表示，事务提交时mysql不做刷盘操作，由系统决定 
sync‐binlog=1

相关命令

#查看bin‐log是否开启
show variables like '%log_bin%'; 
#会多一个最新的bin‐log日志
flush logs;
#查看最后一个bin‐log日志的相关信息
show master status;
#清空所有的bin‐log日志
reset master;
#查看binlog时间内容
show binlog events in ‘binlog.000001’;
#查看binlog内容
/usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin. 000001

数据归档
在这里插入图片描述

#恢复全部数据
/usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001 |mysql ‐uroot ‐p test
#恢复指定位置数据
 /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults ‐‐start‐position="408" ‐‐stop‐position="731" /usr/local/mysql/data/binlog/mysql‐bin.000001 |mysql ‐uroot ‐p test
#恢复指定时间段数据
/usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001 ‐‐stop‐date= "2018‐03‐02 12:00:00" ‐‐start‐date= "2019‐03‐02 11:55:00"|mysql ‐uroot ‐p test