MySQL逻辑架构及SQL执行流程

MySQL逻辑架构

MySQL逻辑架构说明图

MySQL内部的逻辑架构如上图所示，下面对图中的每个组件按执行顺序进行简单说明：

客户端连接器： 主要给外部客户端（即不同的开发语言）提供与SQL的交互功能；
连接池： 为客户端提供了多个与服务器交互的线程（功能与java中的线程池作用类似）；
SQL接口： 用于接收查询的SQL指令并返回查询得到的结果；
查询缓存： 以key-value键值对的方式缓存当前SQL的查询结果 （MySQL8.0以后被删除）；
解析器： 对SQL语句进行词法分析，语法解析并生成语法树；
优化器： 对解析后的SQL语句进行优化，提高执行效率；
插拔式存储引擎： 用于与最底层的文件系统进行交互（插拔式即可替换的）；
文件系统： 这个不用解释，就是用于存储数据文件的物理文件；
日志： 用于记录操作日志，以便于撤销、回滚，防止系统崩溃或者宕机导致的数据丢失问题；

通常将 数据库管理系统（DataBase Management System） 分为三层：
1）连接层（对应上图中的②连接池）
2）服务层（对应上图中的③SQL接口、④查询缓存、⑤解析器和⑥优化器）
3）引擎层（对应上图中的⑦插拔式存储引擎）

连接层

客户端与MySQL服务器通常首先建立TCP连接。连接成功后MySQL服务器需要对客户端传过来的账号和密码进行身份和权限认证。
在开发中，MySQL需要同时与多个客户端建立多个连接，因此，MySQL服务器中通过专门的TCP连接池限制同时连接的客户端数量，其采用长连接模式以复用现有的TCP连接。
除此之外，每个TCP连接建立成功后执行的操作都不相同，因此不可能多个TCP连接共用一个线程，所以MySQL服务器中还有一个线程池以供每个TCP连接与服务器进行交互。每个连接都从线程池中获取线程与MySQL服务器进行交互，省去了创建和销毁线程的开销。

因此，连接层的主要功能是负责认证、获取权限信息及管理连接；

服务层

SQL接口（SQL Interface）

根据客户端的SQL命令，按如下顺序查询结果：

如果未开启查询缓存的话，则进去解析器按正常流程执行；
如果开启查询缓存的情况下，先去查询缓存中是否有需要的数据（即是否能命中）：
- 如果命中，则直接返回查询结果；
- 如果未命中，则进入解析器按正常流程执行；

查询缓存（Caches & Buffer）

MySQL内部维持着一些cache和buffer，如：Query Cache用来缓存一条select语句的执行结果，如果再次执行完全相同的命令时，就不必再进行解析、优化和执行的过程了，直接将结果返回即可。
这个缓存机制是由一系列小缓存组成的。如：表缓存、记录缓存、key缓存和权限缓存等。
查询缓存可以在 不同客户端之间共享 。
从MySQL5.7.20开始，不推荐 使用缓存，并在 MySQL 8.0中删除。原因在下节中进行详细说明。

解析器（Parser）

解析器对SQL语句进行语法分析和语义分析，将SQL语句分解成数据结构，并将这个结构传递到后续的步骤中，以后SQL语句的传递和处理就是基于这个结构的。如果在分解过程中遇到错误，则说明这个SQL是不合理的。
在SQL命令传递到解析器的时候会被解析器验证和解析，并为其创建语法树，然后根据字典方法查询语法树，并验证该客户端是否具有执行该查询的权限。创建好语法树后，MySQL还会对SQL查询进行语法上的优化，进行查询重写。

优化器（Optimizer）

SQL语句在语法解析之后和查询之前会使用查询优化器确定SQL语句的执行路径，生成一个 执行计划 。
这个执行计划表明应该使用 哪些索引 进行查询（全表检索还是使用索引检索），表之间的连接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。
它使用 选取-投影-连接 策略进行查询，如：
```
	select id,name from student where gender='男';
```
这个select查询先根据where语句进行选取，而不是将表全部查询出来以后再进行gender过滤；
这个select查询先根据id和name进行属性投影，而不是将属性全部取出来以后再进行过滤，将这两个查询条件连接起来生成最终的查询结果。

引擎层

插拔式存储引擎（Puggable Storage Engines）

插拔式存储引擎 架构将查询处理和其他的任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎，并且开源的MySQL还允许开发人员设置自己的存储引擎。

插拔式存储引擎 真正的负责了MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作，服务器通过API与存储引擎进行通信。不同的存储引擎具有不同的功能，因此可以根据自己的实际需要进行选择。

MySQL 8.0 默认支持的存储引擎如下：

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

SQL执行流程

下面对上图中的流程进行说明：

1. 查询缓存

如上文所述，如果在查询缓存中命中了当前SQL语句，则直接将结果返回给客户端，否则进入解析阶段。
但是由于查询缓存的效率往往不高，因此在MySQL8.0之后就删除了这个功能。
其原因如下：

当查询语句不能完全匹配时，查询缓存将无法命中，因为查询缓存要求SQL语句完全相同（包括空格、注释、大小写等）
当数据表的内容（或表结构）发生改变后由于查询结果也会随之改变，因此即使能够命中，返回的内容也不相同，导致缓存失效
当SQL语句中包含某些函数（如：NOW()）时，也不能命中，因为两次调用的时间不同，将返回不同的结果，也会导致缓存失效

鉴于查询缓存的上述问题，如果想要使用查询缓存，一般建议在静态表（即极少被更新的表）中使用。
而MySQL也提供了这种按需开启的方式。具体可以通过将my.cnf中的参数query_cache_type设置为DEMAND，表示只有SQL语句中有SQL_CACHE关键词时才缓存。

# query_cache_type有3个状态：0(或OFF)-关闭，1(或ON)-开启，2(或DEMAND)-按需开启
query_cache_type = 2
# 或者
query_cache_type = DEMAND

通过设置按需开启查询缓存，如果不加SQL_CACHE关键词则不使用查询缓存

# 使用查询缓存
select SQL_CACHE * from student where id=3;

# 不使用查询缓存
select * from student where id=3;

#或者（不使用查询缓存）
select SQL_NO_CACHE * from student where id=3;

2. 解析器

例1 SQL语句举例

select id,name from student where id=3

分析器先做 词法分析。对输入的SQL语句进行分析，识别出里面的字符串分别是什么，代表什么（如例1中，select是查询关键字，from是关键字，student是表名，等等）

然后做 语法分析。根据词法分析的结果，语法分析器（如：Bison）会根据语法规则，判断你输入的这个SQL语句是否满足MySQL语法

如果SQL语句正确，则会生成一个语法树：

3. 优化器

经过解析器，MySQL知道你要做什么了，但是在开始执行之前，还要先经过优化器的处理。因为， 一条查询语句可以有很多中执行方式，最后返回的结果都相同。优化器的作用就是找到这其中最好的执行计划。

比如：优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联的时候决定各个表的连接顺序，还有表达式简化、子查询转为连接、外连接转为内连接等。

举例：如下语句是执行两个表的连接：

select * from test1 join test2 using(ID)
where test1.name='zhangwei' and test2.name='mysql高级课程;

方案1：
可以先从表test1里面取出name='zhangwei'的记录的ID值，再根据ID值关联到表test2，再判断test2里面name的值是否等于 'mysql高级课程'。

方案2：
可以先从表test2中取出name='mysql高级课程'的记录的ID值，再根据ID值关联到test1，再判断test1中的name值是否等于 'zhangwei'。

这两种执行方法的逻辑结果是一样的，但是执行效率会有不同，而优化器的作用就是决定选择使用哪一个方案，优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行阶段。

在查询优化器中，可以分为逻辑查询优化阶段和物理查询优化阶段。

逻辑查询优化就是通过改变SQL语句的内容来使得SQL查询更高效，同时为物理查询优化提供更多的候选执行计划。通常采用的方式是对SQL语句进行等价变换，对查询进行重写，而查询重写的数学基础就是关系代数。对条件表达式进行等价谓词重写、条件简化，对视图进行重写，对子查询进行优化，对连接语义进行了外连接消除、嵌套连接消除等。

物理查询优化是基于关系代数进行的查询重写，而关系代数的每一步都对应着物理计算，这些物理计算往往存在多种算法，因此需要计算各种物理路径的代价，从中选择最小的作为执行计划。在这个阶段里，对于单表和多表连接的操作，需要高效地使用索引，提升查询效率。

4. 执行器

截止到现在，才仅仅是产出了一个执行计划，还没有真正地去读写数据表。于是就进入了执行器阶段。

在执行之前需要判断该用户是否具有权限。如果没有，则返回权限错误，否则执行SQL查询并返回结果。

打开表的时候，执行器就会根据表的引擎定义，调用存储引擎API对表进行读写。存储引擎API只是抽象接口，下面还有个存储引擎层，具体实现还是要看表选择的存储引擎。

比如：表test中，ID字段没有索引，那么执行器的执行流程是这样的：

调用InnoDB引擎接口读取这个表的第一行，判断ID是否为1，如果不是则跳过，否则将这行存在结果集中；
调用引擎接口读取下一行，重复相同的判断逻辑，直到读到这个表的最后一行。
执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

至此，这个语句就执行完成了。对于有索引的表，执行逻辑也差不多。

SQL语句在MySQL中的流程是： SQL语句 --> 查询缓存 --> 解析器 --> 优化器 --> 执行器

目录CONTENT

MySQL逻辑架构及SQL执行流程

MySQL逻辑架构及SQL执行流程