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 的存储引擎举行交互。
查询语句实行流程

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

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 8.0 已删除该模块；
  
• 分析 SQL，通太过析器对 SQL 查询语句举行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句范例；
  
• 实行 SQL：实行 SQL 共有三个阶段：
  
  
  
  • 预处置处罚阶段：查抄表或字段是否存在；将 select * 中的 * 符号扩展为表上的全部列。
    
  • 优化阶段：基于查询资源的思量， 选择查询资源最小的实行操持（选择利用哪个索引）；
    
  • 实行阶段：根据实行操持实行 SQL 查询语句，从存储引擎读取记载，返回给客户端；