Mysql查询语句执行流程？更新语句执行流程？
<h2 id="查询语句执行流程">查询语句执行流程</h2><p>查询语句的执行流程如下：权限校验、查询缓存、分析器、优化器、权限校验、执行器、引擎。</p>
<p>举个例子，查询语句如下：</p>
<pre><code class="language-sql">select * from user where id > 1 and name = 'seven';
</code></pre>
<ol>
<li>首先，通过<strong>连接器</strong>，客户端与MySQL服务器建立连接，并完成身份认证和权限验证过程。在此过程中，客户端需要提供用户名和密码以证明其合法性，服务器则会对这些信息进行核对。</li>
<li>检查是否开启缓存。MySQL 8.0之前，Query Cache 确实会缓存完全相同的查询结果，以便重复执行相同查询时直接返回缓存数据。然而，MySQL 8.0及以后版本已经完全弃用Query Cache，因此在MySQL 8.0及更高版本中这一步骤不在适用。</li>
<li>MySQL的<strong>解析器</strong>会对查询语句进行解析，检查语法是否正确，并将查询语句转换为内部数据结构。<strong>预处理器</strong>则会根据MySQL的规则进一步检查解析树是否合法，如检查数据表或数据列是否存在等。</li>
<li><strong>优化器</strong>会根据查询语句的结构、表的统计信息等因素，生成多个可能的执行计划，并通过成本估算器选出最优的执行计划。两种执行方案，先查 id > 1 还是 name = 'seven'，优化器根据自己的优化算法选择执行效率最好的方案；这一步旨在提高查询效率，降低资源消耗。</li>
<li><strong>执行器</strong>按照优化器选择的执行计划，调用存储引擎的API来执行查询。存储引擎负责实际的数据存储和检索，根据执行器的请求，读取或写入数据。</li>
<li><strong>存储引擎</strong>负责实际的数据存储和检索工作，根据执行器的请求，读取或写入数据。</li>
<li>如果开启了Query Cache且查询结果能够命中缓存，查询结果会从缓存中直接返回。而如果没有开启Query Cache或缓存没有命中，MySQL会直接返回查询结果。</li>
</ol>
<h2 id="更新语句执行过程">更新语句执行过程</h2>
<p>更新语句执行流程如下：分析器、权限校验、执行器、引擎、redo log（prepare状态）、binlog、redo log（commit状态）</p>
<p>举个例子，更新语句如下：</p>
<pre><code class="language-sql">update user set name = 'seven' where id = 1;
</code></pre>
<p>具体的执行流程如下图：</p>
<p><img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202412142135632.png" alt="" loading="lazy"></p>
<ol>
<li>找存储引擎取到 id = 1 这一行记录。</li>
<li>根据主键索引树找到这一行，如果 id = 1 这一行所在的数据页本来就在内存池（Buffer Pool）中，就直接返回给执行器；否则，需要先从磁盘读入内存池，然后再返回。</li>
<li>记录Undo Log日志，对数据进行备份，便于回滚。</li>
<li>拿到存储引擎返回的行记录，把 name 字段设置为 “seven”，得到一行新的记录，然后再调用存储引擎的接口写入这行新记录。</li>
<li>将这行新数据更新到内存中，同时将这个更新操作记录到 Redo Log 里面，为 Redo Log 中的事务打上 prepare 标识。然后告知执行器执行完成了，随时可以提交事务。</li>
<li>生成这个操作的 Binlog，并把 Binlog 写入磁盘。</li>
<li>提交事务。</li>
<li>把刚刚写入的 Redo Log 状态改成提交（commit）状态，更新完成。</li>
</ol>
<p>关于以上日志的介绍，可以看这篇文章</p>
<p>以上只是一个简单的case，方便我们能够简单的熟悉流程。接下来，我们对update过程中的全流程进行梳理，具体的流程如下图:</p>
<p><img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202412142152877.png" alt="" loading="lazy"></p>
<ol>
<li>首先客户端发送一条 SQL 语句到 Server 层的 SQL interface。</li>
<li>SQL interface 接到该请求后，先对该条语句进行解析，验证权限是否匹配，也就是在我们上文中讲到的执行器中在执行。</li>
<li>验证通过以后，分析器会对该语句分析，是否语法有错误等。</li>
<li>接下来是优化器生成相应的执行计划，选择最优的执行计划，然后是执行器根据执行计划执行这条语句。</li>
<li>执行器从Buffer Pool中获取数据页的数据，如果数据页没有，需要从磁盘中进行加载。</li>
<li>开启事务，修改数据之前先记录Undo Log，写入Buffer Pool的Undo Page。</li>
<li>开始更新数据页中的记录，被修改的数据页称为脏页，修改会被记录到内存中的 Redo Log Buffer中，再刷盘到磁盘的Redo Log文件，此时事务是 perpare阶段。</li>
<li>这个时候更新就完成了，当时脏页不会立即写入磁盘，而是由后台线程完成，这里会用double write来保证脏页刷盘的可靠性。</li>
<li>通知Server层，可以正式提交数据了， 执行器记录Binlog cache，事务提交时才会将该事务中的Binlog刷新到磁盘中。</li>
<li>这个时候Update语句完成了Buffer Pool中数据页的修改、Undo Log、Redo Log缓存记录，以及记录Binlog cache缓存。</li>
<li>commit阶段，这个阶段是将Redo Log中事务状态标记为commit。</li>
<li>此时Binlog和Redo Log都已经写入磁盘，如果触发了刷新脏页的操作，先把脏页copy到double write buffer里，double write buffer 的内存数据刷到磁盘中的共享表空间 ibdata，再刷到数据磁盘上数据文件 ibd。</li>
</ol>
<p>以上就是修改语句的全部流程，为什么记录完redo log，不直接提交，而是先进入prepare状态？<br>
这里涉及到两阶段提交问题。假设先写redo log直接提交，然后写binlog，写完redo log后，机器挂了，binlog日志没有被写入，那么机器重启后，这台机器会通过redo log恢复数据，但是这个时候binlog并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。</p>
<h2 id="mysql从连接到执行的全过程详解">Mysql从连接到执行的全过程详解</h2>
<h3 id="执行流程图">执行流程图</h3>
<p><img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202412142107073.png" alt="" loading="lazy"></p>
<p>MySQL 的架构可以大致划分为四个层次：连接层、服务层、存储引擎层和文件系统层。</p>
<ul>
<li><strong>连接层</strong>：负责对来自客户端的连接进行权限验证，并将连接信息存入连接池中，方便后续的连接复用。</li>
<li><strong>服务层</strong>：主要负责 SQL 语句的解析与优化，还包括<strong>查询缓存</strong>和 MySQL 内置函数的实现。</li>
<li><strong>存储引擎层</strong>：提供多种可插拔的存储引擎，允许我们通过不同的引擎进行数据的存取操作。存储引擎使得 MySQL 能够直接与硬盘上的数据和日志进行交互，用户可以根据需求选择合适的引擎。从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。</li>
<li><strong>文件系统层</strong>：这一层主要包括日志文件、数据文件及与 MySQL 相关的其他程序。在这四个层次中，服务层和存储引擎层构成了架构的核心。服务层负责处理 MySQL 的核心逻辑，而存储引擎层则直接负责数据的存取操作。</li>
</ul>
<p>也可以将其简单的分成两层：Server 层和存储引擎层，如图</p>
<p><img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com%5Cimgs%5C202404261822009.png" alt="" loading="lazy"></p>
<ul>
<li>Server 层：<strong>负责建立连接、分析和执行SQL</strong>。主要包括连接器、查询缓存、分析器、优化器、执行器等。这些组件包含了MySQL的大部分主要功能。</li>
<li>存储引擎层：<strong>负责数据的存储和提取</strong>。</li>
</ul>
<h3 id="连接器">连接器</h3>
<p>客户端需要通过连接器访问MySQL Server，连接器主要负责<strong>身份认证和权限鉴别</strong>的工作。也就是负责用户登录数据库的相关认证操作，例如：校验账户密码，权限等。在用户名密码合法的前提下，会在权限表中查询用户对应的权限，并且将该权限分配给用户。</p>
<h4 id="如何查看有多少连接">如何查看有多少连接？</h4>
<p>执行 <strong>show processlist</strong> 命令进行查看<br>
<img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261822996.png" alt="" loading="lazy"><br>
其中”Command”列返回的内容中，“Sleep”表示MySQL相同中对应一个空闲连接。而“Query”表示正在查询的连接。</p>
<h4 id="连接状态">连接状态：</h4>
<table>
<thead>
<tr>
<th>Command</th>
<th>含义</th>
</tr>
</thead>
<tbody>
<tr>
<td>sleep</td>
<td>线程正在等待客户端发数据</td>
</tr>
<tr>
<td>query</td>
<td>连接线程正在执行查询</td>
</tr>
<tr>
<td>locked</td>
<td>线程正在等待表锁的释放</td>
</tr>
<tr>
<td>sorting result</td>
<td>线程正在对结果进行排序</td>
</tr>
<tr>
<td>sending data</td>
<td>向请求端返回数据</td>
</tr>
</tbody>
</table>
<h4 id="空闲连接是否一直存在">空闲连接是否一直存在</h4>
<p>从上图可以看出有许多空闲连接，MySQL 定义了空闲连接的最大空闲时长，由 wait_timeout 参数控制的，默认值是 8 小时（28880秒），如果空闲连接超过了这个时间，连接器就会自动将它断开。<br>
<img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261822013.png" alt="" loading="lazy"></p>
<p>手动断开空闲的连接，使用的是 kill connection + id 的命令</p>
<h4 id="最大连接数">最大连接数</h4>
<p><img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261822989.png" alt="" loading="lazy"></p>
<h4 id="长连接和短连接">长连接和短连接</h4>
<ul>
<li>长连接是指连接成功后，客户端请求一直使用是同一个连接。</li>
<li>短连接是指每次执行完SQL请求的操作之后会断开连接，如果再有SQL请求会重新建立连接。</li>
</ul>
<p>由于短连接会反复创建连接消耗相同资源，因此多数情况下会选择长连接。但是为了保持长连接，会占用系统内存，而这些被占用的内存知道连接断开以后才会释放。这里提出了两个解决方案：</p>
<ol>
<li><strong>定期断开长连接</strong>，每隔一段时间或者执行一个占用内存的大查询以后断开连接，从而释放内存，当查询的时候再重新创建连接。</li>
<li><strong>客户端主动重置连接</strong>。MySQL 5.7 或者更高的版本，通过执行 mysql_reset_connection 来重新初始化连接。此过程不会重新建立连接，但是会释放占用的内存，将连接恢复到刚刚创立连接的状态。</li>
</ol>
<h3 id="查询缓存">查询缓存</h3>
<p>在建立与数据库的连接以后就可以执行SQL语句了</p>
<p>如果 SQL 是查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条命令，并且将执行结果按照key-value的形式缓存在内存中了。</p>
<p>Key 是查询的SQL语句，Value 是查询的结果。如果缓存 Key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，执行完SQL仍旧会把结果缓存起来，方便下一次调用。</p>
<p>Mysql的机制是只要一个表有更新操作，那么这个表的查询缓存就会被清空。如果张表不断地被使用（更新、查询），那么查询缓存会频繁地失效，获取查询缓存也失去了意义。不过可以运用在一些修改不频繁的数据表。例如：系统配置、或者修改不频繁的表。</p>
<p>缓存的淘汰策略是先进先出，适用于查询远大于修改的情况下， 否则建议使用Redis或者其他做缓存工具。因此大多数情况下不推荐使用查询缓存。MySQL 8.0 版本后删除了查询缓存的功能，官方认为该功能应用场景较少，所以将其删除。</p>
<blockquote>
<p>这里说的查询缓存是 server 层的，与Innodb 存储引擎中的 buffer pool的缓存无关。也就是 MySQL 8.0 版本移除的是 server 层的查询缓存，</p>
</blockquote>
<h3 id="解析-sql">解析 SQL</h3>
<p>在正式执行 SQL 查询语句之前， MySQL 会先对 SQL 语句做解析，这个工作交由「解析器」来完成。</p>
<p>解析器会做两件事情</p>
<ol>
<li><strong>词法分析</strong>。MySQL 会根据你输入的字符串识别出关键字出来，构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。</li>
<li><strong>语法分析</strong>。根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。</li>
<li><strong>语义分析</strong>：语义分析主要是检查 SQL 语句中的每个对象是否符合数据库的实际情况。如<strong>表名、字段名</strong>是否存在，用户是否对相关表和列拥有执行权限，<strong>数据类型</strong>是否匹配等。</li>
</ol>
<p><img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261822078.png" alt="" loading="lazy"></p>
<p>如果输入的 SQL 语句语法不对，就会在解析器这个阶段报错。比如把 from 写成了 form，这时 MySQL 解析器就会给报错.<br>
<img src="https://seven97-blog.oss-cn-hangzhou.aliyuncs.com/imgs/202404261822006.png" alt="" loading="lazy"></p>
<h3 id="执行sql语句">执行SQL语句</h3>
<p>每条SELECT 查询语句流程可以分为三个阶段：</p>
<ol>
<li>prepare 阶段，也就是预处理阶段；</li>
<li>optimize 阶段，也就是优化阶段；</li>
<li>execute 阶段，也就是执行阶段；</li>
</ol>
<h4 id="预处理器">预处理器</h4>
<p>预处理器的作用：</p>
<ol>
<li>检查 SQL 查询语句中的表或者字段是否存在；</li>
<li>将 select * 中的 * 符号，扩展为表上的所有列；</li>
</ol>
<h4 id="优化器">优化器</h4>
<p>优化器的主要任务是对 SQL 查询进行优化，生成一个最优的执行计划，从而提高查询性能。优化器的工作基于查询的解析树和元数据，它会尝试在不同的查询执行策略中选择效率最高的一个。</p>
<p>在查询优化器中，分为逻辑查询优化和物理查询优化两个大块</p>
<ul>
<li>
<p>逻辑优化会进行一些<strong>逻辑层面</strong>的优化，主要目的是通过调整 SQL 语句的结构来提高查询效率。包括：</p>
<ul>
<li><strong>消除冗余的子查询</strong>：将某些子查询转换为连接或合并查询。</li>
<li><strong>重写查询</strong>：比如将 OR 条件转换为 UNION 操作。</li>
<li><strong>查询合并</strong>：将多个查询合并成一个查询。</li>
<li><strong>移除不必要的操作</strong>：例如消除不需要的 ORDER BY 或 DISTINCT。</li>
</ul>
</li>
<li>
<p>物理查询优化是根据数据库的具体执行引擎、索引、统计信息等做出的决策。这个阶段会根据优化器评估的<strong>成本模型</strong>选择合适的执行计划。具体的优化措施包括：</p>
<ul>
<li><strong>选择合适的连接方式</strong>：比如选择 Nested Loop Join、Hash Join 或 Sort Merge Join。</li>
<li><strong>选择索引</strong>：通过选择合适的索引来加速数据访问。</li>
<li><strong>选择合适的排序方式</strong>：通过使用索引扫描或临时表来避免全表扫描。</li>
</ul>
</li>
</ul>
<p>优化器会使用<strong>基于成本的模型（Cost-Based Optimization）</strong>来评估每种查询执行计划的成本，选择成本最低的执行计划。其核心是通过计算不同执行计划的资源消耗（如 CPU 时间、I/O 操作等），并选出最优的执行策略。</p>
<p>优化器的目标是通过多种优化策略来降低查询的执行成本，生成一个尽可能高效的执行计划。它在逻辑层面和物理层面对 SQL 查询进行优化，以减少查询执行所需的资源。</p>
<h4 id="执行器">执行器</h4>
<p>当解析器生成查询计划，并且经过优化器以后，就到了执行器。</p>
<p>在执行之前，执行器会首先检查用户是否有权限执行相应的操作。如果没有权限，则返回错误信息。</p>
<p>执行器的主要工作包括：</p>
<ul>
<li><strong>表扫描</strong>：根据查询条件决定是否使用索引、是否全表扫描。</li>
<li><strong>连接操作</strong>：根据优化器选择的连接方式（如嵌套循环连接、哈希连接等）执行表之间的数据合并。</li>
<li><strong>排序和聚合</strong>：执行查询中的 ORDER BY、GROUP BY 等操作。</li>
<li><strong>数据返回</strong>：查询结果被返回给用户，修改操作则会提交事务。</li>
</ul>
<p>对于涉及数据修改的 SQL（如 INSERT、UPDATE、DELETE 等），执行器还需要管理事务的提交和回滚操作，确保数据的一致性和持久性。这些操作会与 MySQL 的日志系统（Undo Log、Redo Log、Binlog） 密切交互，确保事务的 ACID 属性。</p>
<p>执行器根据优化器生成的执行计划实际执行 SQL 查询，完成数据操作，返回查询结果或更新数据库状态。它是查询执行的最后环节，直接与 MySQL 的存储引擎进行交互。</p>
<h3 id="总结">总结</h3>
<ol>
<li>连接器：建立连接，管理连接、校验用户身份；</li>
<li>查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；</li>
<li>解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；</li>
<li>执行 SQL：执行 SQL 共有三个阶段：
<ul>
<li>预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。</li>
<li>优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划（选择使用哪个索引）；</li>
<li>执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；</li>
</ul>
</li>
</ol>
</div>
<div id="MySignature" role="contentinfo">
<p>本文来自在线网站：seven的菜鸟成长之路，作者：seven，转载请注明原文链接：www.seven97.top</p><br><br>
来源：https://www.cnblogs.com/sevencoding/p/18993024
頁:
[1]