MySQL随笔01_一条SQL语句是如何执行的

一、MySQL基础架构示意图

file

二、MySQL分层

总体来讲，MySQL可以分为二层：Server层 和 存储引擎 两部分，如下图所示。
不同的存储引擎共用一个Server层。

file

三、各部分组件概述

连接器

负责与客户端建立连接、获取权限、维持和管理连接。比如常规的连接MySQL Server的命令如下

mysql -h$ip -P$port -u$user -p
回车-->输入密码

命令中的mysql是客户端工具，用来与服务端建立连接，通过TCP协议进行连接。
MySQL客户端 与 Server连接器连接成功后进行身份认证，若是认证通过，连接器则会到权限表查询用户所拥有的权限。
一旦客户端与Server之间成功建立连接，当前连接将会保持已查询到的用户权限，即使是管理员账号对用户权限做了修改，也不会影响已经存在的连接权限，即新权限对新的连接生效。
连接完成后，若是没有后续动作，则连接处于空闲状态，可以通过 show processlis 命令查看连接的状态。Command 列即为连接当前所处的状态。
客户端若是长时间没有使用，连接器会自动断开连接，可通过参数 wait_timeout 来设置，默认为8小时。
若是客户端与Server之间的连接被连接器超时断开后，客户端再次发送请求时，则会收到“丢失连接”错误，若是要继续操作则需要进行重连。
关于数据库的连接
长连接 指连接成功后，如果客户端持续有请求，则一直使用同一个连接。
短连接 指每次执行完很少的几次查询就断开连接，下次查询再重新创建一个连接。
建立连接通常比较复杂以及耗费资源，因此，在使用过程中要尽量减少建立连接的动作，即尽量使用长连接。
关于使用长连接时，有些时候MySQL占用内存增长很快的问题
原因 MySQL在执行过程中临时使用的内存是管理在连接对象里面的，被占用的内存资源是在连接断开的时候才释放。
后果 长连接随着使用时间不断累积下来，可能导致内存占用太大，被系统强制杀掉(OOM)，从表现上来看就是MySQL异常重启了。
解决方案
1). 定期断开长连接。使用一段时间、或者程序中判断执行过一个占用内存大的查询后，断开连接，之后查询再重连。
2). 如果使用的是MySQL 5.7或更高版本，可以在每次执行一个比较大的操作后，通过 mysql_reset_connection 来重新初始化连接资源(释放连接所占用的内存资源)。
这个重置连接的操作，不需要重连和重新做权限验证，但是可以将连接恢复到新创建完连接时的状态。

查询缓存

在客户端与MySQL Server建立连接后，就可以进行DML等操作了。

MySQL中，对于之前执行过的语句及其结果可能会以 key-value 对的形式被直接缓存在内存中，key为查询语句，value是查询结果。
MySQL收到查询请求时，会先到查询缓存查找：
若命中缓存key，则直接返回value结果给客户端，MySQL不需要继续执行后面的操作——效率高；
若语句不在查询缓存中，则会执行后面的各执行阶段。执行完成后，执行结果被保存到查询缓存中。
大多数情况下不建议使用查询缓存——>>查询缓存的使用往往 弊大于利。
为什么?——查询缓存的失效非常频繁，只要有对一个表有更新，则这个表上的所有查询缓存都会被清空。对于更新频繁的数据库来说，查询缓存的命中率非常低。
MySQL提供了按需使用的方式，通过 query_cache_type 参数来设置是否开启查询缓存。(注意：MySQL 8.0 版本直接将查询缓存功能去掉了)
query_cache_type 参数值选项：
DEMAND对于默认的SQL语句都不使用查询缓存。
SQL_CACHE对于确定要使用查询缓存的语句显示指定。如下命令

mysql> select SQL_CACHE * from T where ID=10；

分析器

分析器对客户端提交过来的SQL语句进行解析。
首先，进行词法分析。对语句字符串进行拆解，识别出数据库保留关键字、表、列等。
其次，对词法分析的的结果做语法分析。语法分析器根据语法规则，判断输入的SQL是否满足MySQL语法。如果SQL语句不对，则会收到“语法错误”提醒。

优化器

通过分析器，MySQL就知道要干什么了。但是在执行之前，需要经过优化器的处理。
优化器的处理范畴：

在表里有多个索引的时候，决定使用哪个索引；
在一个关联查询语句中，决定各个表的连接顺序等。
优化器的作用——决定使用哪种方案进行查询。
优化器如何选择索引，是否会选错索引等问题后续讲解。

执行器

MySQL通过【分析器】知道了要干什么，通过【优化器】知道了该怎么做，于是执行阶段开始执行语句。
分析器——知道要干什么
优化器——知道该怎么做
执行器——执行语句
执行器在执行语句时，需要做权限检查，有权限则打开表继续执行，打开表时，执行器根据表的引擎定义，去选择该引擎提供的接口。
在数据库的慢查询日志中，有一个 rows_examined 字段，表示这个语句执行过程中扫描了多少行，该值在执行器每次调用引擎获取数据行的时候进行累加。
在有的场景下，执行器调用一次，在引擎内部则扫描了多行，因此——引擎扫描行数与 rows_examined并不完全相同。

四、小结

MySQL逻辑架构简述，通过一个SQL的完整执行流程的各个阶段有一个初步印象。

五、思考题

如果表T中没有字段K，而执行如下语句，则会报“不存在这个列”的错误，这个错误是在上面提到的哪个阶段报出来的呢？

select * from T where k=1;

解答：分析器。Oracle会在分析阶段判断语句是否正确，表是否存在，列是否存在等，MySQL也这样。

六、评论区笔记与问题

笔记1

1，连接
连接管理模块，接收请求；连接进程和用户模块，通过，连接线程和客户端对接
2，查询
查询缓存 Query Cache
分析器，内建解析树，对其语法检查，先from，再on，再join，再where......；检查权限，生成新的解析树，语义检查（没有字段k在这里）等
优化器，将前面解析树转换成执行计划，并进行评估最优
执行器，获取锁，打开表，通过meta数据，获取数据
3，返回结果
返回给连接进程和用户模块，然后清理，重新等待新的请求。

笔记2

1、mysql主要组成部分：连接器，分析器，优化器，执行器，存储引擎。
2、连接器：验证账号密码，维持链接，超时自动断开，链接过程复杂，建议使用长链接，连接比较占用内存，需要定时断开，5.7之后可以使用mysql_reset_connection。
3、分析器：验证语法的合规性，把sql转换成mysql内部识别的语句，表明转换成对应的id。
4、优化器：判断sql内部的执行顺序。
5、执行器：验证操作库表是否有权限，调存储引擎接口查询数据。
6、慢查询日志rows_examined，记录查询的时候扫描了多少行，相同表有肯能次数不同。

问题1

1、客户端连接MySQL时，有时候会突然断开，然后又自动恢复的情况，修改 <code>connection_timeout </code> 参数？
connection_timeout 指的是“连接过程中”的等待时间；
wait_timeout 指的是“连接完成后，使用过程中”的等待时间。

问题2

1.MySQL的框架有几个组件, 各是什么作用?
2.Server层和存储引擎层各是什么作用?
3.you have an error in your SQL syntax 这个保存是在词法分析里还是在语法分析里报错?
4.对于表的操作权限验证在哪里进行?
5.执行器的执行查询语句的流程是什么样的?

本随笔源课程:https://time.geekbang.org/column/article/68319

本文由博客一文多发平台 OpenWrite 发布！