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 的存储引擎进行交互。查询语句执行流程查询语句的执行流程如下权限校验、查询缓存、分析器、优化器、权限校验、执行器、引擎。举个例子查询语句如下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 logprepare状态、binlog、redo logcommit状态举个例子更新语句如下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 查询语句从存储引擎读取记录返回给客户端