MySQL学习笔记(一)基础架构：一条SQL查询语句是如何执行的
一、架构概述
下面这张是 MySQL (基于5.7)的基础架构示意图。
由图可知，MySQL由以下几部分组成：
- 连接池组件
- 管理工具和工具组件
- SQL接口组件
- 查询分析器组件
- 优化器组件
- 缓冲组件
- 插件式存储引擎
- 物理文件
进一步归类，MySQL 可以分为 Server 层和存储引擎两部分。
Server 层包括连接器、查询缓存、分析器、优化器、执行器。所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
存储引擎层负责数据地存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。
二、一条SQL查询语句的执行
当我们执行一条如下的查询语句到底发生了什么呢？
mysql> select * from T where id = 1;
为了更加清晰地展示执行一条SQL查询语句用到的哪些组件以及整个过程，将一些重要的部分抽出来简化为下面这张图。
2.1 连接器
第一步，我们要用客户端工具 mysql 来跟服务端简历连接，一般而言使用如下命令，然后输入密码。
mysql -h localhost -u root -p
-
数据库用户权限验证和更改：如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限，也就是说当一个用户成功建立连接后，用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限，只有新建的连接才会使用新的权限设置。
-
查看连接状态：连接完成后，如果没有后续的动作，这个连接就处于空闲状态，可以用
show processlist命令查看，如下图所示。参数wait_timeout是连接保持空闲状态的最长时间，超过这个时间，连接器会自动断开这个连接。
-
长连接和短连接：数据库中，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。
-
长连接对内存的消耗：由于MySQL再执行过程中临时使用的内存是管理再连接对象里面的，这些资源会在连接断开的时候才释放，所以长连接累积下来，可能导致内存占用太大，被系统强行杀掉。
-
如何解决长连接的内存占用问题：
- 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
- MySQL 5.7或更高版本，可以在每次执行一个比较大的操作后，通过执行
mysql_reset_connection来初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。
2.2 查询缓存
连接建立完成后，就可以执行 select 语句了。执行来到了第二步：查询缓存。
-
查询缓存过程：MySQL 执行过的语句及其结果可能会以 key-value 对的形式缓存再内存中。当MySQL 拿到一个查询请求后，先到查询缓存中查看，若是找到了key，然后进行权限验证，权限验证通过后就将 value 直接返回给客户端。否则，就继续后面的执行阶段，再执行完成后，将执行结果存入查询缓存。
-
大多数情况不要使用查询缓存：一般情况下，我们针对一个表不仅仅有查询操作，还会修改一张表，只要对一个表的更新就会导致这张表上的查询缓存都被清空，查询缓存失效变得非常频繁。除非这张表几乎不做更新，适合使用查询缓存。MySQL 8.0版本删除了查询缓存的功能。
2.3 分析器
如果没有命中查询缓存，就要进入下一步：对SQL语句做解析。
分析器主要干的就两件事：
- 解析器处理语法和解析查询, 生成一课对应的解析树。 这里涉及两个概念“词法分析”和“语法分析”，前者是识别字符串里的各个关键字是什么代表什么，后者会根据语法规则，判断你输入的SQL语句是否满足MySQL语法。
- 预处理器进一步检查解析树的合法。比如: 如检查是否有表权限（precheck），数据表和数据列是否存在, 别名是否有歧义等。如果通过则生成新的解析树，再提交给优化器。
- precheck：知道了该语句要“干什么”之后，会先做一次权限验证。叫做precheck。而precheck是无法对运行时涉及到的表进行权限验证的，比如使用了触发器的情况。因此在之后执行器那里也要做一次执行时的权限验证。
2.4 优化器
通过分析器，明白你要干啥后，数据库就要针对你的需求想一个最优的解决方案，也就是执行计划，这个最优方案选择的操作，这个就是优化器要做的事情了。
比如当表里有多个索引的时候，决定使用那个索引；
或者在一个语句有多表关联的时候，决定各个表的连接顺序。
2.5 执行器
MySQL 通过分析器知道了做什么，通过优化器知道了怎么做，现在通过执行器进入执行环节。
-
执行权限验证：前面提到的precheck是无法对运行时涉及到的表进行权限验证的，比如使用了触发器的情况。因此在执行器这里也要做一次执行时的权限验证。如果有权限，就打开表继续执行。
-
调用引擎接口：打开表的时候，执行器会根据表的引擎定义，去使用这个引擎提供的接口。
三、总结
一条SQL查询语句的执行流程：
- 应用程序把通过数据库连接器建立连接后把SQL语句发送给服务端，这里会进行用户权限认证。
- 连接建立完成后，如果是MySQL8.0之前的版本并且打开了查询缓存，就会先去查询缓存。缓存命中直接返回结果，否则继续之后的步骤，并在之后把结果存入查询缓存。
- 分析器对SQL语句进行解析和预处理，并对SQL语句权限做precheck，生成解析树交给优化器。
- 优化器进行查询优化处理，获得执行计划，交给执行器。
- 执行器再次进行权限验证，之后调用存储引擎接口完成查询。
- 最后将查询结果返回客户端。
