目录

1.背景

2.mysql逻辑架构图

3.逻辑架构解读

第一层:连接层

第二层:服务层

1.Management Serveices & Utilities

2.SQL Interface:SQL接口

3.Parser:解析器

4.Optimizer:查询优化器

5.Caches 和 Buffers:查询缓存组件

第三层:存储引擎层

第四层:数据存储层

4.sql的执行过程

1.连接MySQL

2.查询缓存(MySQL 8.0及以前版本)

3.解析SQL语句

4.优化SQL语句

5.执行SQL语句

6.返回结果

7.其他

8.总结

5.profile查看sql的执行周期

1.如何使用

2.执行show profiles查询quer_id

3.查看详细执行流程

4.每一列的含义详细解读

5.Status状态值详细解读

6.mysql查询大致流程

6.总结

完美!

---

1.背景

实际开发中经常遇到需要优化sql语句,可在优化之前我们需要知道mysql的工作原理,即是如何实现数据存储和查询的,具体是如何架构的,如何查询的,这对我们优化sql有很大的帮助

2.mysql逻辑架构图

3.逻辑架构解读

MySQL的逻辑架构可以大致分为四个层次：连接层、服务层、存储引擎层和数据存储层。每个层次都有其特定的功能和作用，共同协作以提供高效的数据服务。

第一层:连接层

1.系统（客户端）访问MySQL服务器前，首先是建立TCP连接。经过三次握手建立连接成功后，MySQL服务器对TCP传输过来的账号密码做身份认证、权限获取。

2.TCP连接收到请求后，必须要分配给一个线程专门与这个客户端的交互。所以还会有个线程池，去走后面的流程。每一个连接从线程池中获取线程，省去了创建和销毁线程的开销。

第二层:服务层

服务层又可以划分为5个部分

1.Management Serveices & Utilities

系统管理和控制工具

2.SQL Interface:SQL接口

1.接收用户的SQL命令，并且返回用户需要查询的结果。比如SELECT … FROM就是调用SQL Interface

2.MySQL支持DML（数据操作语言）、DDL（数据定义语言）、存储过程、视图、触发器、自定义函数等多种SQL语言接口

3.Parser:解析器

1.在解析器中对 SQL 语句进行语法分析、语义分析。将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。如果在分解构成中遇到错误，那么就说明这个SQL语句是不合理的。

2.在SQL命令传递到解析器的时候会被解析器验证和解析，并为其创建语法树，并根据数据字典丰富查询语法树，会验证该客户端是否具有执行该查询的权限。创建好语法树后，MySQL还会对SQl查询进行语法上的优化，进行查询重写。

4.Optimizer:查询优化器

1.SQL语句在语法解析之后、查询之前会使用查询优化器确定 SQL 语句的执行路径，生成一个执行计划。

2.这个执行计划表明应该使用哪些索引进行查询（全表检索还是使用索引检索），表之间的连接顺序如何，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户。

3.它使用“选取-投影-连接”策略进行查询。例如：SELECT查询先根据WHERE语句进行选取，而不是将表全部查询出来以后再进行gender过滤。 这个SELECT查询先根据id和name进行属性投影，而不是将属性全部取出以后再进行过滤，将这两个查询条件连接起来生成最终查询结果。
 

5.Caches 和 Buffers:查询缓存组件

1.MySQL内部维持着一些Cache和Buffer，比如Query Cache用来缓存一条SELECT语句的执行结果，如果能够在其中找到对应的查询结果，那么就不必再进行查询解析、优化和执行的整个过程了，直接将结果反馈给客户端。

2.这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。

3.这个查询缓存可以在不同客户端之间共享。

4.从MySQL 5.7.20开始，不推荐使用查询缓存，并在MySQL 8.0中删除。

第三层:存储引擎层

存储引擎层，存储引擎真正的负责了 MySQL 中数据的存储和提取，服务器通过 API 与存储引擎进行通信。不同 的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

第四层:数据存储层

数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

4.sql的执行过程

MySQL执行SQL语句的执行流程是一个复杂但有序的过程，涉及多个组件和步骤。以下是MySQL执行SQL语句时的详细执行流程

1.连接MySQL

客户端（如命令行、JDBC、Navicat等）通过连接器与MySQL服务建立连接。
连接器验证用户身份和权限，确保用户有权执行后续操作。

2.查询缓存(MySQL 8.0及以前版本)

注意：在MySQL 8.0及以后的版本中，查询缓存功能已被移除，因为在实际应用中，其效果并不明显，反而可能增加系统的复杂性和开销。
如果查询缓存被启用，MySQL会检查查询缓存中是否存在与当前查询相同的语句及其结果。
如果缓存命中，则直接返回缓存中的结果给客户端，无需执行后续的解析、优化和执行过程。

3.解析SQL语句

SQL接口接收客户端发送的SQL语句。
解析器对SQL语句进行词法分析和语法分析，将其分解成一系列的标记（tokens），并构建语法树（parse tree）。
在此过程中，解析器会检查SQL语句的语法是否正确，如关键词是否正确、表名和列名是否存在等。

4.优化SQL语句

优化器接收解析器生成的语法树，并对其进行优化处理。
优化器会考虑多种执行计划，并选择一种最优的执行计划。
优化过程包括选择索引、决定表的连接顺序、确定数据访问方式等。

5.执行SQL语句

执行器根据优化器生成的最优执行计划，调用存储引擎的API来执行SQL语句。
存储引擎负责数据的实际存取操作，如读取数据、更新数据、删除数据等。
执行过程中，执行器会调用相应的存储引擎接口，如InnoDB的接口，来完成数据的存取操作。

6.返回结果

执行器将执行结果返回给客户端。
如果执行的是查询语句，则返回查询结果集；如果执行的是更新语句（如INSERT、UPDATE、DELETE等），则返回操作影响的行数等信息。

7.其他

在执行过程中，MySQL还会进行权限校验，确保用户有权限执行当前操作。
如果SQL语句涉及多个表，MySQL还会进行表的连接操作，以获取最终的结果集。

8.总结

MySQL还支持多种存储引擎，每种存储引擎都有其特定的功能和性能特点，用户可以根据实际需求选择合适的存储引擎。
综上所述，MySQL执行SQL语句的执行流程是一个涉及多个组件和步骤的复杂过程，包括连接MySQL、查询缓存（MySQL 8.0及以前版本）、解析SQL语句、优化SQL语句、执行SQL语句和返回结果等步骤。在这个过程中，MySQL会充分利用其内部机制和优化技术来提高执行效率和性能。

5.profile查看sql的执行周期

1.如何使用

# 查看 profile 是否开启：,profiling= ON表示已开启,OFF表示关闭
#如果没有开启，可以执行 set profiling=1 开启
show variables like '%profiling%';

#执行查询语句
SELECT * from ta where  ta.remark='ta_1' and ta.name='张无忌' and ta.price=30;

#执行 show profiles 命令，可以查看最近的几次查询。
show profiles;

#根据 Query_ID,可以进一步执行 show profile cpu,block io for query Query_id 来查看 sql 的具体执行步骤。
show profile cpu,block io for query 111

2.执行show profiles查询quer_id

3.查看详细执行流程

4.每一列的含义详细解读

在 MySQL 中，SHOW PROFILE 语句是一个强大的工具，它可以帮助你诊断和分析 SQL 查询的性能瓶颈。通过使用 SHOW PROFILE，你可以查看查询执行过程中的详细时间消耗，包括 CPU 时间、I/O 等待时间等。当你指定 CPU, BLOCK IO 时，你专注于查询的 CPU 消耗和块 I/O 等待时间。

然而，需要注意的是，SHOW PROFILE 功能从 MySQL 5.6.7 版本开始引入，但在 MySQL 8.0 中被标记为废弃（deprecated），并在未来的版本中可能会移除。如果你正在使用 MySQL 8.0 或更高版本，建议使用性能模式（Performance Schema）或查询优化器的其他工具来进行性能分析。

不过，为了回答你的问题，以下是对 SHOW PROFILE CPU, BLOCK IO FOR QUERY Query_id 中可能涉及的几个字段的详细解读（请注意，具体的字段可能会根据 MySQL 的版本和配置有所不同）：

Query_ID: 这是一个唯一标识符，用于标识你正在分析的查询。当你对特定的查询执行 SHOW PROFILE 时，需要指定这个查询的 ID。

Status: 这个字段描述了查询执行过程中的各个阶段或状态。比如，它可能包括“opening tables”（打开表）、“init”（初始化）、“optimizing”（优化）、“executing”（执行）等阶段。
Duration: 表示该状态持续的时间，通常以微秒（microseconds）为单位。这个时间告诉你查询在该阶段花费了多少时间。

CPU_user: 在该状态下，用户 CPU 时间的总量（即，不是系统 CPU 时间）。这反映了 CPU 用于执行用户代码（如查询逻辑）的时间。
CPU_system: 在该状态下，系统 CPU 时间的总量。这通常涉及系统调用和内核态代码的执行时间。

Block_ops_in: 在该状态下，从存储系统读入的数据块数量。这个指标可以帮助你了解查询的 I/O 需求。

Block_ops_out: 在该状态下，写入存储系统的数据块数量。这同样是一个重要的 I/O 性能指标。

Bytes_received_via_socket: 如果查询涉及网络通信（如在分布式数据库系统中），这个字段表示通过套接字接收的字节数。

Bytes_sent_via_socket: 同样，如果查询涉及网络通信，这个字段表示通过套接字发送的字节数。

Context_switches: 在该状态下发生的上下文切换次数。上下文切换是操作系统在不同任务之间切换执行的过程，高频率的上下文切换可能是性能瓶颈的指示。
请注意，并非所有这些字段都会在所有版本的 MySQL 中出现，具体取决于你的 MySQL 版本和配置。

备注:

由于 SHOW PROFILE 在 MySQL 8.0 中被废弃，建议迁移到使用 Performance Schema 或其他现代的性能分析工具来分析和优化你的 SQL 查询。这些工具提供了更丰富的性能和诊断数据，并且是 MySQL 官方推荐的未来发展方向。
 

5.Status状态值详细解读

Status列在SHOW PROFILE输出中提供了查询执行过程中各个阶段的描述。这些阶段（或状态）可能会根据查询的复杂性和MySQL服务器的配置而有所不同。以下是一些常见的Status值及其详细解读：

starting：查询开始执行的阶段。这通常是一个非常短暂的状态，表示查询已经开始执行但尚未进入主要处理阶段。

checking permissions：MySQL正在检查执行查询所需的权限。这个阶段对于确保数据安全和隐私至关重要。

Opening tables：MySQL正在打开查询中引用的表。这包括从存储引擎加载表结构、索引等信息。

Init：初始化查询执行环境的阶段。这可能包括分配内存、准备数据结构等。

System lock：在某些情况下，MySQL需要获取系统级别的锁来执行查询。这个阶段涉及等待和获取这些锁。

Optimizing：查询优化器正在评估查询的不同执行计划，并选择成本最低的计划。这是查询执行过程中非常关键的一步，因为它直接影响查询的性能。

Statistics：收集查询执行所需的统计信息，如索引的使用情况、表的大小等。这些信息对于查询优化器来说至关重要。

Preparing：准备查询执行所需的资源，如分配内存、打开文件等。

Executing：查询正在执行中。这个阶段可能包括从表中检索数据、应用WHERE子句、执行JOIN操作等。

Sending data：MySQL正在将查询结果发送给客户端。这个阶段可能会持续较长时间，特别是当查询结果集很大时。

End：查询执行结束的阶段。这表示查询已经完成了所有工作，并准备将控制权返回给MySQL服务器或客户端。

Query end：与End类似，但更具体地表示查询本身的结束。这通常意味着查询已经完成了所有计算和数据检索工作。

Closing tables：关闭查询中打开的表。这包括释放表结构、索引等信息所占用的资源。

Freeing items：释放查询执行过程中分配的资源，如内存、文件句柄等。

Cleaning up：清理查询执行后留下的任何残留物。这可能包括删除临时表、释放锁等。

需要注意的是，上述列表并不包含SHOW PROFILE输出中可能出现的所有Status值，因为实际的值可能会根据MySQL的版本、配置以及查询的具体情况而有所不同。

此外，对于CPU, BLOCK IO选项，SHOW PROFILE会特别关注这些阶段中与CPU使用和块I/O操作相关的性能数据。然而，Status列本身并不直接提供CPU或块I/O的详细信息；相反，它提供了查询执行过程中各个阶段的描述，而CPU和块I/O的详细信息则通过其他列（如CPU_user、CPU_system、Block_ops_in、Block_ops_out等）来展示。

6.mysql查询大致流程

通俗的理解如下:

mysql 客户端通过协议与 mysql 服务器建连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果， 否则进行语句解析,也就是说，在解析查询之前，服务器会先访问查询缓存(query cache)——它存储 SELECT 语句以及 相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅 仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。
语法解析器和预处理：首先 mysql 通过关键字将 SQL 语句进行解析，并生成一颗对应的“解析树 ”。mysql 解析 器将使用 mysql 语法规则验证和解析查询；预处理器则根据一些 mysql 规则进一步检查解析数是否合法。
查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式， 最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
然后，mysql 默认使用的 BTREE 索引，并且一个大致方向是:无论怎么折腾 sql ，至少在目前来说，mysql 最多只 用到表中的一个索引。

6.总结

想要写错高效的sql语句,我们需要对mysql的逻辑架构和sql的执行过程有所了解;

当然,并不是让大家去背诵这个架构和过程,而是让大家去理解一下,并思考一下这样做是否合理;

其实数据库的功能很简单,就是如果存储数据和查找数据,难就难在如何存储大量的数据并快速查找出来;

大家可以假想如果让你来开发一个数据库,你会怎么设计,把mysql的逻辑架构设计看做是你设计数据库的思路,这样或许你就可以用理解的方式去认知mysql.

完美!