SQL 在 MySQL 执行都发生了什么？
系统提示音：任务提示，基于逻辑架构，解析 MySQL 的 SQL 执行流程，组建团队，逐步完成任务。完成 KPI 后解锁晋升技术负责人，完不成则降级做真牛马，单身没女人……
重生后还要卷 KPI，算了，好说歹说，我只需要把互联网世界的 MySQL 8.0 SQL 执行都涉及到哪些关键步骤列出来，再拆分出不同团队开发即可完成任务……
在盛世当 SB 领导的牛马，在这里做一回英明神武领导也挺不错，大家肯定对我心服口服。
先从全局视角分析 SQL 语句的执行流程分为以下几个步骤，如图所示：
图片
- 客户端连接器：客户端连接到 MySQL 服务器，连接器调用 Server 层的安全管理负责验证客户端的身份和权限，如果通过验证，就建立一个连接，MySQL 会创建一个专用的线程（以操作系统级别的线程实现）来为该客户端服务，并从权限表中读取该用户的所有权限信息。
- SQL 解析：SQL Interface(SQL 接口，用来接受用户的 SQL 命令，使用 Parser 解析器对 SQL 语句进行语法分析；Parse 解析器会检查 SQL 语句是否符合语法规则，并将其转换为一棵语法树。
- Optimizer 优化：在语义分析之后，MySQL 会对 SQL 语句进行查询优化。查询优化器会分析查询语句，并生成一个最优的执行计划。执行计划是指 MySQL 执行 SQL 语句的具体步骤，包括使用哪些索引、如何连接表等。
- 执行引擎Actuator执行 SQL 语句：在完成解析和优化阶段以后，MySQL 会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成。
很明显，服务层是 MySQL 中的核心组件，负责提供各种数据库操作所需的基本功能，如 SQL 语法处理、事务管理、锁管理等。
为了启动项目，我决定以一条最基础的 SELECT 查询作为突破口，去组建一个 MySQL 团队，干翻这苍穹！
SELECT 查询执行都发生了什么
总的来说查询过程如下图 2-1 所示：
图片
首先程序的请求会通过 MySQL 的 connectors 与其进行交互，请求到 Server 层后，会暂时存放在连接池（connection pool)中并由处理器（Management Serveices & Utilities）管理。
当该请求从等待队列进入到处理队列，管理器会将该请求丢给 SQL 接口（SQL Interface）。
SQL 接口接收到请求后，它会将请求进行 hash 处理并与缓存中的结果进行对比，如果完全匹配则通过缓存直接返回处理结果（8.0 已经废弃该步骤）；否则，需要完整的走一趟流程：
- 由 SQL 接口丢给后面的解析器（Parser），上面已经说到，解释器会判断 SQL 语句正确与否。
- 解释器处理完，便来到后面的优化器（Optimizer），它会产生多种执行计划,最终数据库会选择最优化的方案去执行,尽快返会结果。
- 确定最优执行计划后，SQL 语句此时便可以交由存储引擎（Engine）处理，并返回给 Server 层，由 Server 层执行 返回给客户端。
1、 Connectors 客户端/服务端通信协议
MySQL 客户端/服务端通信协议 是 “半双工” 的，在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。
一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。
因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用 SELECT * 以及加上 LIMIT 限制的原因之一。
由连接器 Connectors 来完成与 MySQL Server 建立连接，连接器 Connectors 负责让客户端和 Server 端建立连接、并从 Server 端获取权限、维持和管理连接。
mysql -hlocalhost -P3306 -uuser -ppasswd连接密码验证通过，连接器会到权限表里面查出你拥有的权限，之后这个连接里面的权限判断逻辑，都将依赖于此时读到的权限，一个用户成功建立连接后，即使管理员对这个用户的权限做了修改，也不会影响已经存在连接的权限，修改完后，只有再新建的连接才会使用新的权限设置。
2、连接管理和线程池管理
建立连接的过程通常是比较复杂的，所以使用长连接，如果客户端持续有请求，则一直使用同一个连接。
反之短连接是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。
MySQL 采用池化技术，节省了 TCP 链接创建和销毁的成本。
默认情况下，每个客户端连接都会在服务器进程中拥有一个线程，所以还有个线程池，每一个 TCP 连接从线程池中获取一个线程，省去了创建和销毁线程的开销。
嘿嘿嘿，我需要招聘几个擅长 TCP 网络编程和多线程技术的高手，这个团队就叫 「Connectors 王霸队」。
3、Parser 解析器解析 SQL
在 MySQL 8.0 之前，MySQL 会先检查查询语句是否命中缓存，如果命中缓存则直接返回缓存中的数据。
MySQL 8.0 中已移除了查询缓存功能，使用者需要自行实现相关功能，如使用 Redis、Memcached 等中间缓存系统。
为啥移除查询缓存功能呢？
查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL 库中的系统表，其查询结果都不会被缓存。比如函数 NOW() 或者 CURRENT_DATE() 会因为不同的查询时间，返回不同的查询结果，将这样的查询结果缓存起来没有任何的意义。
MySQL 查询缓存系统会跟踪查询中涉及的每个表，如果这些表 (数据或结构) 发生变化，那么和这张表相关的所有缓存数据都将失效。
正因为如此，在任何的写操作时，MySQL 必须将对应表的所有缓存都设置为失效。
如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。
Parser 解析器会对 SQL 语句进行分析，检查其是否符合语法规则。如果 SQL 语句不符合语法规则，MySQL 将会返回一个错误消息。详细的来说又可分为以下几步：
- 词法分析：主要负责从 SQL 语句中提取关键字，比如：查询的表，字段名，查询条件等等。词法分析器会将 SQL 语句分割成一个个的词法单元（token），并为每个 token 赋予一个类型（type）和值（value）。
- 语法规则：主要判断 SQL 语句是否合乎 MySQL 的语法。
- 语义分析：主要负责检查 SQL 语句的语义是否正确，比如：表名和字段名是否存在，数据类型是否匹配，函数是否合法等。语义分析器会根据数据字典（data dictionary）和目录（catalog）来验证 SQL 语句的有效性。
举个例子。
SELECT name, age FROM student WHERE id = 1;语法分析将 SQL 语句分割成以下词法单元：
图片
根据 MySQL 的语法规则，检查词法单元是否符合以下格式。
select_statement: SELECT select_expression_list FROM table_reference_list [WHERE where_condition]接着进行语义分析，比如检查表 student 是否存在、字段 name, age, id 是否属于表 student。
将 SQL 翻译成语法树，我需要招聘几个精通操作系统和编译原理的大神为了建功立业！这个团队就叫「编译大宝剑」。
4、Optimizer 优化器
一条查询 SQL 可以有很多种执行方式，最后都返回相同的结果**,优化器的作用就是找到这其中最好的执行计划**。
需要设计一个评估执行成本的优化器，预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。
SQL 语句在 Optimizer 优化阶段会经历以下步骤。
- SQL 语句重写：对 SQL 语句进行一些语法和逻辑上的变换，例如，将子查询转换为连接，将 or 条件转换为 union，将 in 条件转换为 exists 等。
- 查询分解：MySQL 会将一条复杂的 SQL 语句分解为多个简单的子查询，每个子查询可以单独优化和执行。
- 预处理：MySQL 会对 SQL 语句进行一些基本的检查和处理，例如检查语法错误，解析参数。
- 优化器：MySQL 会根据统计信息和成本模型，为 SQL 语句选择一个最佳的执行计划。执行计划包括了连接顺序，访问方法，索引选择，排序策略等。
需要注意的是，我可以让优化器使用缓存来提高查询速度。
- 表缓存：用于存储表的元数据，如表的结构定义。当查询需要表信息时，优先从表缓存中获取，避免磁盘操作。
- 线程缓存：用于复用服务器的连接线程。当一个连接关闭后，它的线程会被放回线程缓存池中，供新的连接使用。线程池意味着减少了创建和销毁线程的开销。
- 缓冲池：主要用于 InnoDB 存储引擎，缓冲池管理缓存的数据页，包括数据和索引。当需要访问这些页时，可以直接从缓冲池读取，提高访问速度。
王妮玛：为何要对 SQL 语句重写？多此一举
非也，连接查询通常比子查询更快，因为 MySQL 优化器可以生成更佳的执行计划，可以预先装载数据，更高效地处理查询。
子查询往往需要运行重复的查询，子查询生成的临时表上也没有索引，因此效率会更低。
连接查询可以利用索引加速。
王妮玛：你如何评估执行成本？
- 表依赖关系：MySQL 优化器会分析 SQL 语句中涉及到的表之间是否有依赖关系。
- 索引：分析 SQL 语句中参与条件过滤或排序的列是否有可用索引，并根据索引类型和覆盖度来选择合适的索引。
- 预估行数：根据数据字典和目录中存储的统计信息来预估每个表或每个索引范围内的行数。这些行数会影响成本模型中的 I/O 代价和 CPU 代价。
- 预估成本：优化器会根据预估行数和成本常数（cost constant）来预估每个执行计划的成本。
就这样，得到一个执行计划。
这里需要一个成本评估模型，使优化器能够精准预测最优执行路径。所以我需要招聘一些成本优化算法大牛，就叫做「成本估算榨干队」
5、调用存储引擎执行 SQL 语句
Server 层在完成解析和优化阶段以后，MySQL 会生成对应的执行计划，执行器会根据查询语句，调用存储引擎接口从磁盘读取数据，并将其存储在内存中。
引擎层负责存储数据和执行 SQL 语句。然后，执行器会对数据进行排序、分组、聚合等操作，最终生成查询结果。
比如执行 select * from student where id = 1;。
没有索引
对于没有有索引的表使用全表扫描。
- 调用 InnoDB 引擎接口取这个表的第一行，判断 id 值是不是 1，如果不是则跳过，如果是则将这行存在结果集中；
- 调用引擎接口取下一行，重复相同的判断逻辑，直到取到这个表的最后一行。
- 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。
索引扫描
假设 student 表主键是 id，执行计划是先扫描 student 表的索引 idx_score（id），然后回表获取 student 数据。
执行引擎是核心，需要对文件处理、磁盘和索引有着高技术的能力，他们需要精通操作系统、文件系统和数据结构与算法。这个团队就叫「存储引擎风火轮」。
6、过滤数据和处理
引擎层从磁盘文件获取到数据后返回给 Server 层，MySQL 会根据执行计划中的过滤条件（where，group by，having，order by，limit 等），对读取到的数据进行过滤和处理。
过滤条件可以减少返回给客户端的数据量，提高查询效率。接着把过滤后的数据返回给客户端，并释放相关的资源，客户端可以接收到结果集，并进行后续的操作。
最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL 仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等。
系统的提示音再次响起。
恭喜完成阶段性任务，晋升为异世界技术负责人。下一个任务，接续分析修改语句在 MySQl 都发生了什么，并设计 MySQL 的事务管理模块。
这是新的挑战，也是新的成长。我明白，这场冒险才刚刚开始。
异世界的事务管理，又将掀起怎样的风暴？萧剑臣是否能找到回归的路？更多精彩，敬请期待下一集！
