mysql 的sql 执行逻辑 sql语句在mysql中的执行过程

《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. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。