Mysql查询语句执行流程？更新语句执行流程？

查询语句执行流程

查询语句的执行流程如下：权限校验、查询缓存、分析器、优化器、权限校验、执行器、引擎。
举个例子，查询语句如下：

1. select * from user where id > 1 and name = 'seven';

复制代码

• 首先，通过连接器，客户端与MySQL服务器建立连接，并完成身份认证和权限验证过程。在此过程中，客户端需要提供用户名和密码以证明其合法性，服务器则会对这些信息进行核对。
  
• 检查是否开启缓存。MySQL 8.0之前，Query Cache 确实会缓存完全相同的查询结果，以便重复执行相同查询时直接返回缓存数据。然而，MySQL 8.0及以后版本已经完全弃用Query Cache，因此在MySQL 8.0及更高版本中这一步骤不在适用。
  
• MySQL的解析器会对查询语句进行解析，检查语法是否正确，并将查询语句转换为内部数据结构。预处理器则会根据MySQL的规则进一步检查解析树是否合法，如检查数据表或数据列是否存在等。
  
• 优化器会根据查询语句的结构、表的统计信息等因素，生成多个可能的执行计划，并通过成本估算器选出最优的执行计划。两种执行方案，先查 id > 1 还是 name = 'seven'，优化器根据自己的优化算法选择执行效率最好的方案；这一步旨在提高查询效率，降低资源消耗。
  
• 执行器按照优化器选择的执行计划，调用存储引擎的API来执行查询。存储引擎负责实际的数据存储和检索，根据执行器的请求，读取或写入数据。
  
• 存储引擎负责实际的数据存储和检索工作，根据执行器的请求，读取或写入数据。
  
• 如果开启了Query Cache且查询结果能够命中缓存，查询结果会从缓存中直接返回。而如果没有开启Query Cache或缓存没有命中，MySQL会直接返回查询结果。
  

更新语句执行过程

更新语句执行流程如下：分析器、权限校验、执行器、引擎、redo log（prepare状态）、binlog、redo log（commit状态）
举个例子，更新语句如下：

1. update user set name = 'seven' where id = 1;

复制代码

具体的执行流程如下图：

• 找存储引擎取到 id = 1 这一行记录。
  
• 根据主键索引树找到这一行，如果 id = 1 这一行所在的数据页本来就在内存池（Buffer Pool）中，就直接返回给执行器；否则，需要先从磁盘读入内存池，然后再返回。
  
• 记录Undo Log日志，对数据进行备份，便于回滚。
  
• 拿到存储引擎返回的行记录，把 name 字段设置为 “seven”，得到一行新的记录，然后再调用存储引擎的接口写入这行新记录。
  
• 将这行新数据更新到内存中，同时将这个更新操作记录到 Redo Log 里面，为 Redo Log 中的事务打上 prepare 标识。然后告知执行器执行完成了，随时可以提交事务。
  
• 生成这个操作的 Binlog，并把 Binlog 写入磁盘。
  
• 提交事务。
  
• 把刚刚写入的 Redo Log 状态改成提交（commit）状态，更新完成。
  

关于以上日志的介绍，可以看这篇文章
以上只是一个简单的case，方便我们能够简单的熟悉流程。接下来，我们对update过程中的全流程进行梳理，具体的流程如下图:

• 首先客户端发送一条 SQL 语句到 Server 层的 SQL interface。
  
• SQL interface 接到该请求后，先对该条语句进行解析，验证权限是否匹配，也就是在我们上文中讲到的执行器中在执行。
  
• 验证通过以后，分析器会对该语句分析，是否语法有错误等。
  
• 接下来是优化器生成相应的执行计划，选择最优的执行计划，然后是执行器根据执行计划执行这条语句。
  
• 执行器从Buffer Pool中获取数据页的数据，如果数据页没有，需要从磁盘中进行加载。
  
• 开启事务，修改数据之前先记录Undo Log，写入Buffer Pool的Undo Page。
  
• 开始更新数据页中的记录，被修改的数据页称为脏页，修改会被记录到内存中的 Redo Log Buffer中，再刷盘到磁盘的Redo Log文件，此时事务是 perpare阶段。
  
• 这个时候更新就完成了，当时脏页不会立即写入磁盘，而是由后台线程完成，这里会用double write来保证脏页刷盘的可靠性。
  
• 通知Server层，可以正式提交数据了， 执行器记录Binlog cache，事务提交时才会将该事务中的Binlog刷新到磁盘中。
  
• 这个时候Update语句完成了Buffer Pool中数据页的修改、Undo Log、Redo Log缓存记录，以及记录Binlog cache缓存。
  
• commit阶段，这个阶段是将Redo Log中事务状态标记为commit。
  
• 此时Binlog和Redo Log都已经写入磁盘，如果触发了刷新脏页的操作，先把脏页copy到double write buffer里，double write buffer 的内存数据刷到磁盘中的共享表空间 ibdata，再刷到数据磁盘上数据文件 ibd。
  

以上就是修改语句的全部流程，为什么记录完redo log，不直接提交，而是先进入prepare状态？
这里涉及到两阶段提交问题。假设先写redo log直接提交，然后写binlog，写完redo log后，机器挂了，binlog日志没有被写入，那么机器重启后，这台机器会通过redo log恢复数据，但是这个时候binlog并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
Mysql从连接到执行的全过程详解

执行流程图

MySQL 的架构可以大致划分为四个层次：连接层、服务层、存储引擎层和文件系统层。

• 连接层：负责对来自客户端的连接进行权限验证，并将连接信息存入连接池中，方便后续的连接复用。
  
• 服务层：主要负责 SQL 语句的解析与优化，还包括查询缓存和 MySQL 内置函数的实现。
  
• 存储引擎层：提供多种可插拔的存储引擎，允许我们通过不同的引擎进行数据的存取操作。存储引擎使得 MySQL 能够直接与硬盘上的数据和日志进行交互，用户可以根据需求选择合适的引擎。从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。
  
• 文件系统层：这一层主要包括日志文件、数据文件及与 MySQL 相关的其他程序。在这四个层次中，服务层和存储引擎层构成了架构的核心。服务层负责处理 MySQL 的核心逻辑，而存储引擎层则直接负责数据的存取操作。
  

也可以将其简单的分成两层：Server 层和存储引擎层，如图

• Server 层：负责建立连接、分析和执行SQL。主要包括连接器、查询缓存、分析器、优化器、执行器等。这些组件包含了MySQL的大部分主要功能。
  
• 存储引擎层：负责数据的存储和提取。
  

连接器

客户端需要通过连接器访问MySQL Server，连接器主要负责身份认证和权限鉴别的工作。也就是负责用户登录数据库的相关认证操作，例如：校验账户密码，权限等。在用户名密码合法的前提下，会在权限表中查询用户对应的权限，并且将该权限分配给用户。
如何查看有多少连接？

执行 show processlist 命令进行查看

其中”Command”列返回的内容中，“Sleep”表示MySQL相同中对应一个空闲连接。而“Query”表示正在查询的连接。
连接状态：

Command含义sleep线程正在等待客户端发数据query连接线程正在执行查询locked线程正在等待表锁的释放sorting result线程正在对结果进行排序sending data向请求端返回数据空闲连接是否一直存在

从上图可以看出有许多空闲连接，MySQL 定义了空闲连接的最大空闲时长，由 wait_timeout 参数控制的，默认值是 8 小时（28880秒），如果空闲连接超过了这个时间，连接器就会自动将它断开。

手动断开空闲的连接，使用的是 kill connection + id 的命令
最大连接数

长连接和短连接

• 长连接是指连接成功后，客户端请求一直使用是同一个连接。
  
• 短连接是指每次执行完SQL请求的操作之后会断开连接，如果再有SQL请求会重新建立连接。
  

由于短连接会反复创建连接消耗相同资源，因此多数情况下会选择长连接。但是为了保持长连接，会占用系统内存，而这些被占用的内存知道连接断开以后才会释放。这里提出了两个解决方案：

• 定期断开长连接，每隔一段时间或者执行一个占用内存的大查询以后断开连接，从而释放内存，当查询的时候再重新创建连接。
  
• 客户端主动重置连接。MySQL 5.7 或者更高的版本，通过执行 mysql_reset_connection 来重新初始化连接。此过程不会重新建立连接，但是会释放占用的内存，将连接恢复到刚刚创立连接的状态。
  

查询缓存

在建立与数据库的连接以后就可以执行SQL语句了
如果 SQL 是查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条命令，并且将执行结果按照key-value的形式缓存在内存中了。
Key 是查询的SQL语句，Value 是查询的结果。如果缓存 Key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，执行完SQL仍旧会把结果缓存起来，方便下一次调用。
Mysql的机制是只要一个表有更新操作，那么这个表的查询缓存就会被清空。如果张表不断地被使用（更新、查询），那么查询缓存会频繁地失效，获取查询缓存也失去了意义。不过可以运用在一些修改不频繁的数据表。例如：系统配置、或者修改不频繁的表。
缓存的淘汰策略是先进先出，适用于查询远大于修改的情况下， 否则建议使用Redis或者其他做缓存工具。因此大多数情况下不推荐使用查询缓存。MySQL 8.0 版本后删除了查询缓存的功能，官方认为该功能应用场景较少，所以将其删除。

这里说的查询缓存是 server 层的，与Innodb 存储引擎中的 buffer pool的缓存无关。也就是 MySQL 8.0 版本移除的是 server 层的查询缓存，

解析 SQL

在正式执行 SQL 查询语句之前， MySQL 会先对 SQL 语句做解析，这个工作交由「解析器」来完成。
解析器会做两件事情

• 词法分析。MySQL 会根据你输入的字符串识别出关键字出来，构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。
  
• 语法分析。根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。
  
• 语义分析：语义分析主要是检查 SQL 语句中的每个对象是否符合数据库的实际情况。如表名、字段名是否存在，用户是否对相关表和列拥有执行权限，数据类型是否匹配等。
  

如果输入的 SQL 语句语法不对，就会在解析器这个阶段报错。比如把 from 写成了 form，这时 MySQL 解析器就会给报错.

执行SQL语句

每条SELECT 查询语句流程可以分为三个阶段：

• prepare 阶段，也就是预处理阶段；
  
• optimize 阶段，也就是优化阶段；
  
• execute 阶段，也就是执行阶段；
  

预处理器

预处理器的作用：

• 检查 SQL 查询语句中的表或者字段是否存在；
  
• 将 select * 中的 * 符号，扩展为表上的所有列；
  

优化器

优化器的主要任务是对 SQL 查询进行优化，生成一个最优的执行计划，从而提高查询性能。优化器的工作基于查询的解析树和元数据，它会尝试在不同的查询执行策略中选择效率最高的一个。
在查询优化器中，分为逻辑查询优化和物理查询优化两个大块

• 逻辑优化会进行一些逻辑层面的优化，主要目的是通过调整 SQL 语句的结构来提高查询效率。包括：
  
  
  
  • 消除冗余的子查询：将某些子查询转换为连接或合并查询。
    
  • 重写查询：比如将 OR 条件转换为 UNION 操作。
    
  • 查询合并：将多个查询合并成一个查询。
    
  • 移除不必要的操作：例如消除不需要的 ORDER BY 或 DISTINCT。
    
  
  
• 物理查询优化是根据数据库的具体执行引擎、索引、统计信息等做出的决策。这个阶段会根据优化器评估的成本模型选择合适的执行计划。具体的优化措施包括：
  
  
  
  • 选择合适的连接方式：比如选择 Nested Loop Join、Hash Join 或 Sort Merge Join。
    
  • 选择索引：通过选择合适的索引来加速数据访问。
    
  • 选择合适的排序方式：通过使用索引扫描或临时表来避免全表扫描。
    
  
  

优化器会使用基于成本的模型（Cost-Based Optimization）来评估每种查询执行计划的成本，选择成本最低的执行计划。其核心是通过计算不同执行计划的资源消耗（如 CPU 时间、I/O 操作等），并选出最优的执行策略。
优化器的目标是通过多种优化策略来降低查询的执行成本，生成一个尽可能高效的执行计划。它在逻辑层面和物理层面对 SQL 查询进行优化，以减少查询执行所需的资源。
执行器

当解析器生成查询计划，并且经过优化器以后，就到了执行器。
在执行之前，执行器会首先检查用户是否有权限执行相应的操作。如果没有权限，则返回错误信息。
执行器的主要工作包括：

• 表扫描：根据查询条件决定是否使用索引、是否全表扫描。
  
• 连接操作：根据优化器选择的连接方式（如嵌套循环连接、哈希连接等）执行表之间的数据合并。
  
• 排序和聚合：执行查询中的 ORDER BY、GROUP BY 等操作。
  
• 数据返回：查询结果被返回给用户，修改操作则会提交事务。
  

对于涉及数据修改的 SQL（如 INSERT、UPDATE、DELETE 等），执行器还需要管理事务的提交和回滚操作，确保数据的一致性和持久性。这些操作会与 MySQL 的日志系统（Undo Log、Redo Log、Binlog） 密切交互，确保事务的 ACID 属性。
执行器根据优化器生成的执行计划实际执行 SQL 查询，完成数据操作，返回查询结果或更新数据库状态。它是查询执行的最后环节，直接与 MySQL 的存储引擎进行交互。
总结

• 连接器：建立连接，管理连接、校验用户身份；
  
• 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
  
• 解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
  
• 执行 SQL：执行 SQL 共有三个阶段：
  
  
  
  • 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
    
  • 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划（选择使用哪个索引）；
    
  • 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；
    
  
  

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！