mysql学习笔记（二）--查询语句的执行过程

最近在学习mysql实战45讲，觉得里面的内容很受用，做一些笔记记录下：

首先是mysql的一个基础架构的解释，如下图：

　　　　　　　　　　　　　　

从上图我们可以清晰的看到，mysql的基础架构主要分为两个部分，一个是server层（负责大多数核心服务功能的实现），一个是存储引擎层（负责数据的存储与提取）。

server层是跨存储引擎的，也就是说，当客户端执行一条查询语句时，必须经过server层，不管当前查询的表指定的存储引擎是INNODB还是MYISAM。

server层主要包括以下几个部分：

（1）连接器：管理与客户端的连接，包括建立以及维持连接。可以用以下命令连接mysql数据库：

mysql -h$ip -P$port -u$user -p

　　连接又分为长连接与短连接。其实长连接是相对于通常的短连接而说的，也就是长时间保持客户端与服务端的连接状态。通常的短连接操作步骤是：连接->数据传输->关闭连接；而长连接通常就是：连接->数据传输->保持连接->数据传输->保持连接->…………->关闭连接；这就要求长连接在没有数据通信时，定时发送数据包，以维持连接状态，短连接在没有数据传输时直接关闭就行了。长连接主要用于在少数客户端与服务端的频繁通信，因为这时候如果用短连接频繁通信常会发生Socket出错，并且频繁创建Socket连接也是对资源的浪费。但是对于服务端来说，长连接也会耗费一定的资源，这是因mysql在执行过程中临时使用的内存是管理在连接对象里面的，这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是mysql异常重启了。需要专门的线程（unix下可以用进程管理）来负责维护连接状态。总之，长连接和短连接的选择要视情况而定。

（2）查询缓存：MySql查询缓存保留了查询返回给客户端的完整结果，当缓存命中的时候，服务器马上返回保存的结果(会先检查权限)，并跳过解析、优化和执行步骤。

　　当mysql接收到查询请求时，会查询缓存是否命中，若命中，则直接返回查询结果，这里的key就是查询语句，value就是查询结果。在数据频繁更新的数据库中，查询缓存一般不建议使用，因为每当有表更新操作时，所有的缓存都会失效。可以通过设置query_cache_type=demand按需使用查询缓存，如：

mysql> select SQL_CACHE * from ...

     mysql8.0版本已将该功能去掉。

缓存配置参数：

　　　　　　　　　　　　　　　　　　　　　　　　　　

　　query_cache_limit: MySQL能够缓存的最大结果,如果超出,则增加 Qcache_not_cached的值,并删除查询结果

　　query_cache_min_res_unit: 分配内存块时的最小单位大小

　　query_cache_size: 缓存使用的总内存空间大小,单位是字节,这个值必须是1024的整数倍,否则MySQL实际分配可能跟这个数值不同(感觉这个应该跟文件系统的blcok大小有关)

　　query_cache_type: 是否打开缓存 OFF: 关闭 ON: 总是打开

　　query_cache_wlock_invalidate: 如果某个数据表被锁住,是否仍然从缓存中返回数据,默认是OFF,表示仍然可以返回。

 （3）分析器：对用户输入的查询语句进行词法分析、语法分析、语义分析、构造执行树。

　　a）首先是解析器将查询分解成一个个标识，然后构造一颗“解析树”，解析器保证查询中的标识都是有效的，会检查其中的基本错误，比如字符串上面的引号没有闭合等。

　　b）然后预处理器检查解析器生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。最后，预处理器检查权限。

eg：

　　a）以下代码为当我们查询一个不存在的列如k时，应该是在预处理器阶段返回的错误；

select * from T where k=1

       b）以下代码就是在语法分析阶段，mysql发现语句不对，报错：You have an error in your SQL syntax；

mysql> elect * from t where ID=1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1

ps：语法检查阶段的具体顺序如下图：

　　　　　　　　　　　　　　　　　　　　　　　　　　　　

（4）优化器：优化器把解析树变成执行计划。一个查询通常可以有很多种执行方式，并且返回同样的结果，优化器的任务就是找到最好的方式。

　　MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划的成本，并选择其中成本最小的一个。最初，成本的最小单位是随机读取一个4K数据页的成本，后来成本计算公式变得更加复杂，并且引入了一些“因子”来估算某些操作的代价，如当执行一次where条件比较的成本。可以通过查询当前会话的last_query_cost的值来得知MySQL计算的当前查询的成本。

　　有很多种原因会导致MySQL优化器选择错误的执行计划，比如：

　　1. 统计信息不准确。

　　2. 执行计划中的成本估算不等同于实际的执行计划的成本。

　　3. MySQL的最优可能与你想的最优不一样。

　　4. MySQL从不考虑其他并发的查询，这可能会影响当前查询的速度。

　　5. MySQL也不是任何时候都是基于成本的优化，有时候也会基于一些固定的规则。

　　6. MySQL不会考虑不受其控制的成本，例如执行存储过程或者用户自定义的函数的成本。

（5）执行器：对优化器生成的执行计划进行执行操作。

　　在执行之前，会检查当前用户是否有查询权限，如果没有对应的权限，会报错如下：

mysql> select * from T where ID=10;

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

　　这里有个疑问，为什么丁奇老师讲的是权限检查是在执行器阶段进行，而网上的很多资料都表明在分析器阶段进行权限检查？？？

　　丁奇老师给出的回答是：有些时候，SQL语句要操作的表不只是SQL字面上那些。比如如果有个触发器，得在执行器阶段（过程中）才能确定。优化器阶段前是无能为力的。而通过实验，如下代码：

mysql> select * from T where k=1

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

　　可以发现，在创建了一个没有select权限的用户之后，执行以下语句，报错的确是没有权限，按照丁奇老师的说法，这个语句应该是通过了分析器（预处理器检查每个列是否存在）阶段，进入了执行器阶段进行权限检查然后报错的，但其实这个语句在分析器阶段就会报错了，与丁奇老师说的在执行器阶段进行的权限检查其实是矛盾的，虽然丁奇老师给出的回答是：这个是一个安全方面的考虑。你想想一个用户如果没有查看这个表的权限，你是会告诉他字段不对还是没权限？如果告诉他字段不对，其实给的信息太多了，因为没权限的意思还包含了：没权限知道字段是否存在。但个人还是比较信服于在分析器阶段其实就已经进行了权限检查了。

ps：结合丁奇老师以及网上的一些资料，会不会有可能进行了两次权限检查呢？？？查阅了一些资料，发现针对procedure 和 function 的create  和alter是一种特殊权限 ，他们是全局的。猜测可能是第一次是对表的权限检查，第二次在执行器阶段是进行特殊权限检查。

　　最后就是调用存储引擎的api接口获取数据并返回给客户端了。