mysql-SQL底层执行原理详解

目录

 1.mysql-SQL执行过程架构图

 1.1 连接器

 1.2 查询缓存

 1.3 分析器

1.4 优化器

1.5 执行器

 2. bin-log归档

2.1 bin-log配置

 2.2 恢复数据实战

 2.3 配置bin-log报错

---

 1.mysql-SQL执行过程架构图

CentOS7卸载mysql_xiaoweiwei99的博客-CSDN博客_centos卸载mysql

1. CLIENT层就是平常使用的navicat等客户端工具。

2. SERVER层包含连接器，查询缓存，分析器，优化器，执行器，常用的函数，存储过程，视图等都在这一层

        查询缓存（mysql提供的一个鸡肋功能，8.0以后取消），可以手动开启

        分析器：词法，语法分析

        优化器：生成执行计划

        执行器：调用ENGINE层接口查询结果

3. ENGINE层包含INNODB(mysql5.5.5版本后的默认引擎),MYISAM,MEMORY等引擎

 1.1 连接器

负责连接客户端，权限获取，维持连接等功能。权限一般放置在一张user表中

连接器加载登录信息-->user表信息-->session，如果有root用户修改user表信息，session无法感知，需要重启应用

-- 连接器常用命令
mysql -h host[数据库地址] -u root[用户] -p root[密码] -P 3306 

例子：mysql -h 127.0.0.1 -u root -p

CREATE USER 'username'@'host' IDENTIFIED BY 'password'; //创建新用户
grant all privileges on *.* to 'username'@'%'; //赋权限,%表示所有(host)
flush privileges //刷新数据库
update user set password=password(”123456″) where user=’root’;(设置用户名密码)
show grants for root@"%"; 查看当前用户的权限

-- 查看当前用户的权限
show grants for geng@"%";

-- 查看活动链接等待时间
show global variables like "wait_timeout";
-- 设置全局服务器关闭非交互连接之前等待活动的秒数
set global wait_timeout=28800; 

-- 查看当前查询进程
show processlist;

 1.2 查询缓存

 此功能mysql8.0已经取消，配置文件文件已经找不到配置参数，查询缓存的配置方式：

在my.cnf文件中配置query_cache_type参数

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

• 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数量。

鸡肋功能，缓存功能适用于万年不变的表中。

-- 查看缓存常用命令
show databases; 显示所有数据库
use mysql; 打开数据库：
show tables; 显示数据库mysql中所有的表；
describe user; 显示表mysql数据库中user表的列信息）；

-- 在查询缓存开启状态下指定SQL_CACHE查询缓存
select SQL_CACHE * from test where ID=5;
-- 查看当前mysql实例是否开启缓存机制
show global variables like "%query_cache_type%";
-- 查看运行的缓存信息
show status like'%Qcache%'; 

-- 查看启动日志报错
tail -n 100 /var/log/mysqld.log|grep -E 'Warning|ERROR'

 1.3 分析器

 分析器分成6个步骤：

1.词法分析 2.语法分析 3.语义分析 4.生成执行树 5.生成执行计划6.计划执行

mysql词法分析使用mysqllex完成，语法分析使用bison完成。java的词法分析也有antlr4，IDEA也实现了这个插件，叫做antlr v4,可以下载后分库分表或者分布式事务中使用。

暂时没搞懂有什么真实应用场景。

需要自己定义一个词法分析器的文件，例如：ArcSql.g4,然后右键点击test ANTLR Rule按钮即可。

应用场景：分库分表

 

1.4 优化器

优化器决定了要使用的索引，各个表的连接顺序

1.5 执行器

执行器需要调用引擎接口查询结果，会先第一行判断是不是需要的数据，不是则继续查找，最后把结果集返回

 2. bin-log归档

2.1 bin-log配置

配置log-bin，如果是设置在安装目录一般不会报错，如果是设置在其他目录，需要关闭一下selinux，请参考2.3配置bin-log报错

1.配置my.cnf

log_bin=/usr/local/mysql/data/binlog/mysql‐bin
server-id=123454
binlog-format=ROW
sync-binlog=1

1.1 新建mysql-bin.index

在目录/usr/local/mysql/data/binlog下新建一个mysql-bin.index，不知道是不是高版本的原因

1.2 赋权限

chown -R mysql /usr/local/mysql/data/binlog/mysql‐bin

2.重启mysql

[root@localhost ~]# systemctl start mysqld.service #开启服务
[root@localhost ~]# systemctl stop mysqld.service #关闭服务
[root@localhost ~]# systemctl status mysqld.service #查看服务状态

3. 查看bin-log是否开启

[root@localhost /]# tail -n 100 /var/log/mysqld.log|grep -E 'Warning|ERROR' #查看启动日志报错

[root@localhost ~]# mysql -u root -p #进入mysql命令窗口

mysql> show variables like '%log_bin%'; 查看bin‐log是否开启
mysql> flush logs; 会多一个最新的bin‐log日志
mysql> show master status; 查看最后一个bin‐log日志的相关信息
mysql> reset master; 清空所有的bin‐log日志

 配置bin-log成功效果如下：

 2.2 恢复数据实战

恢复数据分为以下几个步骤：

1. flush logs 重新生成一个日志文件mysql-bin.000002，新文件名记录一下。

2. truncate 表

3. 恢复全部数据

/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001（在恢复之前的日志名字） |mysql -uroot -p test(数据库名，然后再输入密码)

mysql> reset master; 清空所有的bin-log日志
mysql> show variables like '%log_bin%'; 查看bin-log是否开启
mysql> flush logs; 会多一个最新的bin-log日志
mysql> show master status; 查看最后一个bin-log日志的相关信息

./usr/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 查看log内容


从bin-log恢复数据
恢复全部数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p test(数据库名，然后再输入密码)
恢复指定位置数据（这个位置可以从上面的查看log内容中获取）
/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(数据库)

演示效果如下：

 2.3 配置bin-log报错

在配置完bin-log以后报错，看到报错日志说是mysql_bin.index文件没找到，网上说的赋权也不管用。

解决方案，临时关闭selinux，重启服务器：

[root@localhost ~]# getenforce

Enforcing

[root@localhost ~]# setenforce 0

[root@localhost ~]# getenforce

Permissive

[root@localhost ~]#systemctl restart mysqld

参考资料：centos mysql log设置_在centos7下设置mysql的binlog_weixin_39806808的博客-CSDN博客

 

提示： unknown variable 'log‐bin=/usr/local/mysql/data/binlog/mysql‐bin'

[root@localhost /]# tail -n 100 /var/log/mysqld.log|grep -E 'Warning|ERROR'
2022-02-17T08:46:58.172363Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-02-17T08:46:58.347096Z 0 [ERROR] unknown variable 'log‐bin=/usr/local/mysql/data/binlog/mysql‐bin'
2022-02-17T08:46:58.347109Z 0 [ERROR] Aborting

解决方法：log‐bin中间的-是下划线log_bin