mysql优化（2）——sql语句的执行过程

最新推荐文章于 2024-10-02 06:14:43 发布

hello_wang_world

最新推荐文章于 2024-10-02 06:14:43 发布

阅读量422

点赞数

文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/hello_wang_world/article/details/121709130

版权

sql语句的执行过程

Mysql 内部的组件结构

上一篇讲解我们mysql 索引数据结构，如果想了解的去看一下mysql优化——数据结构的讲解（1）
mysql 使用的是插拔式设计结构;mysql server 层和store 层；store 层的引擎是可选择，选择不同的存储结构也不同，前面我介绍过Innodb 和myisam的结构；
内部大概的结构分两层 server 层和store 层；
在这里插入图片描述
大体mysql就是上面的结构** server层的缓存结构在后面的版本8升级当中就没有了，6，7还是有的但是默认也是不开启的这个我后面会讲一下他为什么升级掉了还有就是它为啥默认不开启** 下面我们逐步介绍每一层；

server 层

主要由连接器，查询缓存，词语分析器，优化器，执行器等组成；主要涵盖了像我么使用mysql的函数，核心服务，存储过程，触发器等都在这里实现；

store 层

这是一个引擎层，我们现在开发中主要就是使用Myisam 和Innodb ,开发中百分至九十五使用Innodb 这个我想我在上节文档里讲的大家也能知道为啥；所以这层主要就是存储数据并且按照结构化顺序化存储；
下面我们重点讲解下 server 层的执行步骤，和做了哪些操作；

连接器

顾名思义就是用来连接mysql,我们通常用的Navicat ，jdbc 都属于客户端；
在cmd 上链接数据库
在这里插入图片描述
还有我们的数据使用Navicat连接，我们看到几个mysql自带的默认库《mysql》下的user 这个就是我们的用户表和权限管理

当我修改了上面的权限我们需要重新连接才能生效
顺便写几个比较查用mysql的命令

create user 'whj1'@'host'  identified by '123456' # 创建用户 这个命令创建完的用户没有任何的权限
grant all  privileges on *.* to 'whj' # 给这个用户分配权限
flush privileges # 刷新数据库
update user set password=password('1234567') where user='whj' # 修改这个用户的密码 这个语句版本8以下使用
alter user 'whj'@'localhost' identified with mysql_native_password by '1234567' # 版本8可以使用这个来修改密码
show grants for whj @'localhost' # 查看账号的权限 
show databases #查看有多少个库
use study #打开库
show tables  # 查看表—— 只有打开了才能查看

下面我讲一下mysql 的长连接和短链接
我们可以通过show processlist；这个命令查看下面哪个是空闲链接如图
在这里插入图片描述
上面的休眠一般是默认休眠八小时，当八小都没有一个操作执行mysql 就会断开连接；也可以通过下面的命令设置

show global variables like 'wait_timeout' # 查询等待超时的时间 默认时28800 
show global variable like 'interactive_timeout' # 查询交互超时时间  默认 28800
set global wait_timeout 480 ## 修改默认时间测试
set global interactive_timeout 480  ## 需要这两个值同时修改才能生效

在这里插入图片描述
当休眠和交互超过了我们设置的6分钟在执行命令就会如下产生尝试重连，连接成功后才能执行sql命令

在数据库中我们的长连接就是在数据库连接成功后在长时间的sql 或者其他命令中都是使用的一个连接，而短链接就是在我们sql或者其他命令执行几次的时候就会出现断链，然后在重新连接出现一个新的连接供命令使用；这种长短就是通过我们修改上面两个超时的时间值来决定的，时间长的叫长连接，时间短的为短链接；一般在实际开发中我们使用的都是长连接交给我们的数据连接池去管理的；所以一般我们不需要修改这类的mysql 的值；但是长连接有个弊端，就是长连接会导致mysql的内存升的快，是因为我们连接管理都生成在内存中，其实这个问题我们java 代码中也给我解决了就是在我们连接数据库使用数据连接池的时候需要去配置一些超时的参数，会定期断开查询时重连来避免这类错误

查询缓存

查询缓存我简单的介绍一下，这个功能在5.8的版本已经跟它拜拜了；说白了之个功能在以前可能比较好，它是使用sql 语句作为key,对比查到value;但是到现在这个功能就是鸡肋的，为啥这么讲；查询缓存当某个表背更新了，他不会指定跟新唯一的，因为你想的key是sql语句所以他只能去更能所有的缓存，这样我们的性能会被损耗，我们一直有表更新，缓存也一直在被更新，命中缓存就非常低；还有我们缓存也可以使用我们的redis 的去代替用代码去实现更新而不是用mysql 的机制，去缓存去更新缓存；所以这个基本没人用。

词语分析器

我们的查询通过连接器，如果没有查询缓存，会直接到达词语分析器；进行sql语句的分析错误的，如果没有错误的字符，再会进行拆分；
词语分析器原理
在这里插入图片描述

优化器

优化器：当我们的sql 的语句中使用到了索引，优化器就是用来判断是否是由索引，或者有多个索引的时候我们使用哪个索引，还有就是当我们由多表联查的时候决定连接的顺序都需要优化器来优化；下一个章我会讲一下怎么优化使用哪个索引；
这里我们创建一个案例
users 表

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `love` varchar(255) DEFAULT NULL,
  `id` int NOT NULL AUTO_INCREMENT,
  `work_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

work 表

DROP TABLE IF EXISTS `work`;
CREATE TABLE `work` (
  `id` int NOT NULL AUTO_INCREMENT,
  `work_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

 select  *from users t  JOIN  `work` d USING(id) where t.work_id=2 and d.work_name='xx科技2'

上面语句的意思就是两表查询的交集；
理解：1.就是先查询出users表work_id=2的数据记录id 在使用记录Id关联出work 数据再去判断 work_name
2.先查询出work 表根据work_name判断出数据，然后记录id关联出users 再去判断 work_id ；
但是选择1方式还是选择2方式使用过优化器计算得到一个coat 的值然后来推算使用哪一个这个后面章节我会去查看给你们看

执行器

执行器会等到优化器，优化后的执行逻辑调用执行器，执行器会对表作为判断你是否有查询的权限，如果没有的权限，则会报错；有权限他会根据你的引擎去执行，默认的都是Innodb；

bin-log 归档

bin-log: 这个其实就是会记录我们在mysql 中的cud的操作当我们不小心删除了我们数据我们可以通过这个binlog 来恢复；
它是存在于我们的server 层
bin-log 是逻辑日志，记录一条语句的逻辑
不限大小，不会被覆盖只会追加
在使用binlog 我们需要开启这个日志；
第一步找到配置文件my.cf

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

binlog命令——mysql 客服端中执行

 show variables like '%log_bin%'; 查看bin‐log是否开启
 flush logs; 会多一个最新的bin‐log日志
 show master status; 查看最后一个bin‐log日志的相关信息
 reset master; 清空所有的bin‐log日志
 /usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults /usr/local/mysql/data/binlog/mysql‐bin.000001
|mysql ‐uroot ‐p study # 恢复study所有的数据
/usr/local/mysql/bin/mysqlbinlog ‐‐no‐defaults ‐‐start‐position="408" ‐‐stop‐position="731"
/usr/local/mysql/data/binlog/mysql‐bin.000001 |mysql ‐uroot ‐p study # 恢复指定位置

/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 study # 指定日期恢复