搞懂 MySql 的架构和执行流程

1、MySQL 的三层架构
2、SQL 的执行流程
- 2.1、连接器
- 2.2、解析器
- 2.3、预处理器
- 2.4、优化器
- 2.5、执行器
- 2.6、存储引擎
3、关于Select 的两个顺序

1、MySQL 的三层架构

MySQL的三层结构包括：

连接层：负责与MySQL客户端之间的通信，提供如连接处理，身份验证等功能。
服务层：在MySQL数据库系统处理底层数据之前的所有工作，都是在服务层完成的。包括权限判断，SQL 解析，行计划优化，query cache的处理以及所有内置的函数（如日期，时间，数学运算，加密）等等。
引擎层：负责存储和获取所有存储在MySQL中的数据。

2、SQL 的执行流程

在这里插入图片描述
查询缓存 在5.7版本和之前的版本存在，由于命中率较低，比较鸡肋等原因，5.8版本已经不存在了，这里就不过多讨论查询缓存。

2.1、连接器

连接器：尝试连接MySql，建立连接。
客户端访问 MySQL 服务器之前，首先会建立 TCP 连接，经过三次握手建立连接成功后， MySQL 服务器对 TCP 传输过来的账号密码做身份认证和权限获取。

用户名或密码不对，会收到一个 Access denied for user 错误，客户端程序结束执行。
用户名密码认证通过，会从权限表查询账号拥有的权限与连接关联，之后的权限判断逻辑，都将依赖于此时读到的权限。
多个客户端可以和一个MySQL服务器建立连接，每个客户端和一个MySQL服务器可以建立多个连接。MySQL 服务器里有专门的 TCP连接池限制连接数，采用长连接模式复用TCP连接。

2.2、解析器

解析器：解析Sql语句，会把一条sql语句解析语法树。（包含词法解析和语法解析）

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

在这里插入图片描述

2.3、预处理器

预处理器的任务是根据MySQL的相关规则，对解析器生成的SQL语句解析树进行进一步的校验和处理。包括检查数据库中的数据表、数据列是否存在，并对别名进行校验，检查别名是否存在重名和歧义等情况。

预处理器的目的是确保SQL语句的合法性和正确性，以及优化查询性能。通过预处理操作，可以避免在查询执行过程中出现错误，提高查询的效率和稳定性。

2.4、优化器

在这里插入图片描述

一条查询可以有很多种执行方式，最后都会返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

SQL 语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个 执行计划.
这个执行计划表明应该使用哪些索引进行查询（全表检索还是使用索引检索），表之间的连接顺序如何，最后会按执行计划中的步骤，调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。

它使用 选取-投影-连接策略进行查询。例如：

SELECT id,name,age FROM tb_user WHERE gender = '男';
-- 该查询语句先根据 WHERE 进行选取，而不是将表全部查询出来以后再进行 gender 过滤。
-- 同时，该查询语句先根据 id,name,age 进行属性投影，而不是将属性全部取出后再进行过滤，将这两个查询条件 连接 起来，生成最终的结果。

查询优化器，分为逻辑查询优化阶段、物理查询优化阶段。

逻辑查询优化：通过改变 SQL 语句的内容来使得 SQL 查询更高效，同时为物理查询优化提供更多的候选执行计划。通常采用的方式是对 SQL 语句进行 等价变换，对查询进行 重写，而查询重写的数学基础就是关系代数。对条件表达式进行等价谓词重写、条件简化、对视图进行重写，对子查询进行优化，对连接语义进行了外连接消除、嵌套连接消除等。
物理查询优化：基于关系代数进行的查询重写，而关系代数的每一步都对应着物理计算，这些物理计算往往存在多种算法，因此需要计算各种物理路径的代价，从中选择代价最小的作为执行计划。在这个阶段中，对于单表和多表连接的操作，需要高效地使用索引，提升查询效率。

2.5、执行器

执行器，负责根据优化器生成的执行计划，执行SQL语句，并返回结果集。其主要功能包括：

① 与存储引擎交互：执行器通过调用存储引擎的API来操作数据，执行查询、更新、插入等操作。不同的存储引擎有不同的接口和实现方式，执行器需要与存储引擎紧密配合，确保数据操作的正确性和高效性。
② 权限校验：在执行SQL语句之前，执行器会进行权限校验，确保当前用户有权执行该语句。如果权限不足，执行器会返回相应的错误信息。
③ 执行计划执行：根据优化器生成的执行计划，执行器会按照计划逐步执行SQL语句，访问相关的数据表，执行相应的操作，并生成结果集。

2.6、存储引擎

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

3、关于Select 的两个顺序

关键字的顺序不能颠倒：

SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT …

SELECT 语句的执行顺序

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

FROM：首先，MySQL会根据FROM子句找到需要查询的表。
JOIN：接着，如果有JOIN操作，MySQL会根据JOIN类型（如INNER JOIN、LEFT JOIN等）和其他JOIN条件，将多个表连接在一起。
WHERE：然后，MySQL会对连接后的表应用WHERE子句中的过滤条件，筛选出满足条件的记录。
GROUP BY：如果有GROUP BY子句，MySQL会按照指定的列进行分组。
HAVING：接着，MySQL会对分组后的结果应用HAVING子句中的过滤条件，进一步筛选分组。
SELECT：然后，MySQL会对筛选后的记录应用SELECT子句，选择需要的列。
DISTINCT：如果使用了DISTINCT关键字，MySQL会去除选择列中的重复值。
ORDER BY：如果有ORDER BY子句，MySQL会按照指定的列进行排序，返回结果集。
LIMIT：最后，如果使用了LIMIT子句，MySQL会限制返回的数据行数。
以上是MySQL中SELECT语句的一般执行顺序，具体执行过程可能会因为查询优化和其他因素而有所变化。

# 举例：
SELECT DISTINCT <select_list>
FROM <left_table> <join_type>
JOIN <right_table> ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number># 执行顺序如下1   FROM <left_table>
2   ON <join_condition>
3   <join_type> JOIN <right_table>
4   WHERE <where_condition>
5   GROUP BY <group_by_list>
6   HAVING <having_condition>
7   SELECT
8   DISTINCT <select_list>
9   ORDER BY <order_by_condition>
10  LIMIT <limit_number>

-- 举例说明
SELECT DISTINCT id,name,count(*) as num  # 顺序5 
FROM tb_users u inner join tb_team t on u.team_id = t.id  # 顺序1 
WHERE u.age > 18   # 顺序2 
GROUP BY t.id      # 顺序3
HAVING num > 2     # 顺序4
ORDER BY num DESC  # 顺序6
LIMIT 3    # 顺序7