《MySql学习》 SQL 语句的执行过程

一.MySql架构

MySql 逻辑架构分为 Server层 与 engine 层

其中 Server层 又分为 连接器缓存分析器优化器执行器 五部分

engine 层 由不同的存储引擎组成

1. 连接器

连接器负责与客户端建立获取维护连接

mysql  -u "用户名" -p

可选参数

-h IP

-p 端口

登录成功后将打印欢迎信息与当前mysql的版本号

如果密码不对,将出现错误信息

Access denied for user 'root'@'localhost' (using password: YES)

权限缓存

当用户密码验证通过建立链接后将获取当前登录用户的权限信息并将其缓存即使当前用户的权限已经被修改了但是没有断开连接用户还是有旧的权限

先创建用户 tom 博主mysql版本8.0

create user tom identified by 'tom';

然后赋予tom用户test库的权限

grant all privileges on test.* to tom@'%';

使用tom用户登录

show databases;

仅能看到有权限的test库与 information_schema 数据库的元信息数据库

此时我们再用root用户授权tom用户所有库的所有权限

grant all privileges on *.* to tom@'%';

回到tom用户再次执行 ,依然只能看到两张表信息

如果我们退出后再次登录就能看到所有表的信息

长链接与短连接

长连接是指连接成功后如果客户端持续有请求则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接下次查询再重新建立一个

show processlist;

可以看到当前tom用户建立链接并且有134秒未做任何操作MySQL默认会将8小时2未做任何操作的连接断开

我们可以通过命令,查看当前设置的超时时间

 show variables like 'wait_timeout'

如果有大量的长连接将会占用大量的内存为了解决这个问题 我们可以在客户端执行 mysql_reset_connection() 函数重置所有连接但这些连接将不需要重新连接和权限验证但会对业务造成影响

1. 回滚活跃的事务并重新设置自动提交模式
2. 释放所有表锁
3. 关闭或删除所有的临时表
4. 重新初始化会话的系统变量值
5. 丢失用户定义的设置变量
6. 释放prepare语句
7. 关闭handler变量
8. 将last_insert_id()值设置为0
9. 释放get_lock获取的锁
10. 清空通过mysql_bind_param()调用定义的当前查询属性

2.查询缓存

连接建立后将来到查询缓存看看有没有执行过此SQL之前执行的SQL语句与其结果会以key-value的形式存在表中。如果有缓存直接将缓存结果返回给客户端

查询缓存在8.0之后就被删除了因为其存在意义不大。

如果对一张表的数据进行了增删改都会导致此表的缓存失效往往缓存还没用上就要失效维护成本大于存在价值

只有静态配置表适合使用

3.分析器

如果没有命中缓存将来到分析器 分析器主要做 词法分析字符串含义分析、语法分析是否符合语法规范两件事情

分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句MySQL 需要识别出里面的字符串分别是什么代表什么。MySQL 从你输入的"select"这个关键字识别出来这是一个查询语句。它也要把字符串“T”识别成“表名 T”把字符串“ID”识别成“列 ID”。做完了这些识别以后就要做“语法分析”。根据词法分析的结果语法分析器会根据语法规则判断你输入的这个 SQL 语句是否满足 MySQL 语法。
如果你的语句不对就会收到“You have an error in your SQL syntax”的错误提醒

如果你查询了一个不存在的列将在此时抛出由语法分析识别的异常要注意一点表的元数据信息不存储在引擎中

You have an error in your SQL syntax

4.优化器

通过分析器后MySQL确定了这条SQL的行动后将会对行动作出优化给出一个并不是最优的最优解

mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;

既可以先从表 t1 里面取出 c=10 的记录的 ID 值再根据 ID 值关联到表 t2再判断 t2 里面 d 的值是否等于 20。也可以先从表 t2 里面取出 d=20 的记录的 ID 值再根据 ID 值关联到 t1再判断 t1 里面 c 的值是否等于 10。这两种执行方法的逻辑结果是一样的但是执行的效率会有不同而优化器的作用就是决定选择使用哪一个方案。

经过了优化器下一站执行器真正执行语句去调用存储引擎接口

5.执行器

SQL语句执行前会从建立连接时获取到的权限判断是否有对此表的操作权限如果没有返回没有权限操作的信息

ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有就根据表的存储引擎调用相对应的存储引擎接口

如果查询的字段没有索引那么执行流程如下

1. 调用 InnoDB 引擎接口取这个表的第一行判断 ID 值是不是 10如果不是则跳过如果是则将这行存在结果集中
2. 调用引擎接口取“下一行”重复相同的判断逻辑直到取到这个表的最后一行。
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

此文章为2月DayX学习笔记内容来源于极客时间《课程名称》强烈推荐该课程/推荐该课程