Day1:一条sql查询语句是如何执行的

Mysql数据库我们一般用到最多的就是查询功能了，但是一条查询语句从客户端发送到服务器以后，我们所看到的只是数据库返回了一个结果，并不知道该语句在Mysql数据库中执行处理的过程

 

mysql> select * from jas where Id=58

---

Mysql架构

mysql分为了Server层和存储引擎层两个部分，通过解析这个图片的内容，让你能够对Mysql有一个更深的理解。通过了解执行过程，也可以在遇到问题的时候，快速定位到问题点并进行一个解决

1、Server层

Server层里面有很多模块，他包括了

• 函数
• 触发器
• 存储过程
• 视图
• 连接器：负责跟客户端建立连接、获取权限、维持和管理连接。
• 查询缓存：查询请求先访问缓存(key 是查询的语句，value 是查询的结果)。命中直接返回。不推荐使用缓存，更新会把缓存清除(关闭缓存：参数 query_cache_type 设置成 DEMAND)。8.0以后去除这个模块
• 分析器：对 SQL 语句做解析，判断sql是否正确。(词法，语法，语义解析)
• 优化器：决定使用哪个索引，多表关联（join）的时候，决定各个表的连接顺序。
• 执行器：执行语句，先判断用户有无查询权限，使用表定义的存储引擎。

2、存储引擎层

负责数据的存储和提取。

• InnoDB => 5.5.5版本就作为默认引擎；支持事务；带有redo log
• MyISAM => 不支持事务。 

---

一条sql查询语句的执行过程

开始客户端通过输入连接命令传入服务器，连接器会根据传入的连接命令去判断传入用户账号、密码、端口、ip地址等是否正确，如果通过验证，连接器就会与客户端建立一个连接，并且获取改用户的权限，保存一个快照到内存中（这个是我自己理解的，有问题可以指出），该权限用于此次连接，如果需要修改权限并且使用，需要重新建立连接才可。

此时连接上以后，服务器处于空闲状态，连接时长默认为8h，控制客户端发来一条sql查询语句。经过查询缓存模块，如果查询的表中数据没有改变，并且sql语句完全相同，那就直接命中后进行权限校验，通过即可直接返回数据。

如果没有命中，则会去到分析器进行词法和语法的分析，然后获得解析树，然后通过预处理器对解析树进一步分析，通过这个分析可以确保查询命令是否符合规范，检查命令中的表或者表中的字段是否存在，如果不存在将在分析器分析的时候抛出问题返回到客户端。

如果分析无误后，回到优化器阶段，通过优化器去提高sql语句的执行效率，会选择join联表后前后关联关系和最优的索引。

执行方案确定以后，去到执行器调用存储引擎的接口，不过在获取数据之前，会进行权限校验（注意，这里才是校验权限的位置，前面连接器是用于获取权限的），如果通过校验，就可以去调用接口获取数据辽~

组件详解

上面就是一个执行过程，那么再根据里面经过的一些流程进行一个分解，让你能够更加深入了解每一个组件具体的功能。

1、连接器

负责跟客户建立连接，获取权限，维持和管理链接

mysql -h$host -P$port -u$user -p$password(可以将密码直接写在上面，不写按回车后输入)

上面命令的mysql是客户端工具，用来与服务器建立连接。通过tcp三次握手以后，连接器开始验证身份。

if 通过验证：
    连接器查询出权限，用于后续逻辑判断
    连接时长默认8小时，这个8小时是由参数 wait_timeout 控制
else：
    收到“ERROR 1045 (28000): Access denied for user 'root'@'localhost' 
(using password: YES)”的错误提示，客户端程序mysql结束执行

上面通过验证的结果也说明了，当一个客户连接成功以后，即使你使用了管理员账号对权限进行了修改，也不会影响当前已经连接上的用户权限，只有新建的连接才会使用修改后的权限。

接成功后如果没有进行其他操作，该连接就是处于空闲状态

如何查看空闲状态呢？

• 通过mysql> show processlist命令看到
• 使用navicat，点击工具-->服务器监控-->MySQL就可以看到

数据库的长短连接

• 长连接：如果客户端有持续请求，则一直使用同一个连接
• 短链接：执行完几次查询立即断开，下次重新建立

其实通过介绍就可以看出来，短链接会对资源消耗比较严重，因为mysql建立连接的过程比较复杂。但是使用长连接以后内存占用会涨的比较快，这是因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候释放。如果长期累积的话，就会造成内存占用太大，被系统强行杀掉（OOM），从现象看就是MySQL异常重启。

        OOM，全称“Out Of Memory”，翻译成中文就是内存“用完了”

解决方案

• 定期断开长连接。使用一段时间，或者进行了一个大查询后需要断开连接重连（有几次后台崩了都不知道什么原因，现在处理后程序也可以稳定运行了）
• MySQL5.7以后的版本，可以直接使用mysql_reset_connection()
• 注意，这个是mysql c API函数，无法直接执行

初始化连接资源，不需要重连和重新做权限验证，但是会将链接恢复到刚刚连接的状态。
造成影响如下：

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

2、查询缓存

建立连接以后，就可以执行select语句

MySQL拿到一个查询语句后，会先到查询缓存中看看，之前是否执行过该条语句。如果有记录，mysql就不需要往下执行复杂的操作，可以直接检查权限后返回结果。

听起来还挺实用的，不过为啥会在5.7.20不推荐，MySQL8.0以后取消掉了呢？

1. 这是因为查询缓存失效的条件为 ==>只要表中的数据更新，这个表上的查询缓存都会被全部清空。对于一个更新操作频繁的表来说，你可能用不上查询缓存就被删除了
2. 如果没有命中，就浪费了一次查询查询缓存的时间

3、分析器

如果没有命中查询缓存，就要开始真正执行语句了。在执行之前，MySQL需要知道你想要做什么，这时候就需要分析器对这个命令进行解析

• 首先进行的是“词法解析”。将接收到的sql语句，识别出里面的关键字，表名，字段名，别名是否使用正确
• 接着进行“语法分析”。判断传入的语句是否满足MySQL语法。如果语句不对就会收到报错。
• 后续会提到”语义解析“

《高性能mysql》里提到解析器和预处理器。 解析器处理语法和解析查询, 生成一课对应的解析树。 预处理器进一步检查解析树的合法。

比如: 数据表和数据列是否存在, 别名是否有歧义等。如果通过则生成新的解析树，再提交给优化器。 

4、优化器

分析完语句正确后，mysql知道需要做什么后，就需要经过优化处理。

优化器的作用：

1. 表中有多个索引的时候，选择使用哪个索引
2. 语句中存在表关联（join）的时候，会决定表的连接顺序

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、执行器

MySQL通过分析器知道了需要做什么，通过优化器知道了应该怎么做，这时候进去执行器阶段开始执行我们的sql查询语句

注意！执行开始之前，需要判断一下登录的用户有没有对表操作的权限，如果没有，则会返回没有权限的错误。（在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限）

precheck：sql执行过程中可能会有触发器这种在运行时才能确定的过程，分析器工作结束后的precheck是不能对这种运行时涉及到的表进行权限校验的，所以需要在执行器阶段进行权限检查。另外正是因为有precheck这个步骤，才会在报错时报的是用户无权，而不是 k字段不存在（为了不向用户暴露表结构）。

如果有权限，那么就会根据这个表使用的引擎，去调用存储引擎提供的接口，遍历完所有满足条件的数据返回给客户端。

至此，这个sql语句就算是执行完成了。

问题与解决

1、为什么对权限的检查不在优化器之前做？

        有些时候，SQL语句要操作的表不只是SQL字面上那些。比如如果有个触发器，得在执行器阶段（过程中）才能确定。优化器阶段前是无能为力的

2、