跳至主要內容

执行 - Mysql语句执行流程


执行流程图

MySQL 的架构共分为两层:Server 层和存储引擎层,

Server 层:负责建立连接、分析和执行SQL。主要包括连接器、查询缓存、分析器、优化器、执行器等。这些组件包含了MySQL的大部分主要功能。
存储引擎层:负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎,不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB,从 MySQL 5.5 版本开始, InnoDB 成为了 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请求会重新建立连接。

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

  1. 定期断开长连接,每隔一段时间或者执行一个占用内存的大查询以后断开连接,从而释放内存,当查询的时候再重新创建连接。
  2. 客户端主动重置连接。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 语句做解析,这个工作交由「解析器」来完成。

解析器会做两件事情

  1. 词法分析。MySQL 会根据你输入的字符串识别出关键字出来,构建出 SQL 语法树,这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。
  2. 语法分析。根据词法分析的结果,语法解析器会根据语法规则,判断你输入的这个 SQL 语句是否满足 MySQL 语法。

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

执行SQL语句

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

  1. prepare 阶段,也就是预处理阶段;
  2. optimize 阶段,也就是优化阶段;
  3. execute 阶段,也就是执行阶段;

预处理器

预处理器的作用:

  1. 检查 SQL 查询语句中的表或者字段是否存在;
  2. 将 select * 中的 * 符号,扩展为表上的所有列;

优化器

在优化器中会确定sql语句的执行路径,比如是根据全表检索 还是根据索引检索等,使用索引检索那使用哪个索引

在查询优化器中,分为逻辑查询优化和物理查询优化两个大块

  • 物理查询优化是通过索引和表连接方式等技术进行优化
  • 逻辑查询优化是通过sql等价变换提升查询效率,直白点说就是换一种执行效率高的写法

执行器

当解析器生成查询计划,并且经过优化器以后,就到了执行器。执行器会选择执行计划开始执行,但在执行之前会校验请求用户是否拥有查询的权限,如果没有权限,就会返回错误信息,否则将会去调用MySQL引擎层的接口,执行对应的SQL语句并且返回结果。

查询语句执行流程

查询语句的执行流程如下:权限校验、查询缓存、分析器、优化器、权限校验、执行器、引擎。

举个例子,查询语句如下:

select * from user where id > 1 and name = 'seven';
  1. 首先检查权限,没有权限则返回错误;
  2. MySQL8.0以前会查询缓存,缓存命中则直接返回,没有则执行下一步;
  3. 词法分析和语法分析。提取表名、查询条件,检查语法是否有错误;
  4. 两种执行方案,先查 id > 1 还是 name = 'seven',优化器根据自己的优化算法选择执行效率最好的方案;
  5. 校验权限,有权限就调用数据库引擎接口,返回引擎的执行结果。

更新语句执行过程

更新语句执行流程如下:分析器、权限校验、执行器、引擎、redo log(prepare状态)、binlog、redo log(commit状态)

举个例子,更新语句如下:

update user set name = 'seven' where id = 1;
  1. 先查询到 id 为1的记录,有缓存会使用缓存。
  2. 拿到查询结果,将 name 更新为seven,然后调用引擎接口,写入更新数据,innodb 引擎将数据保存在内存中,同时记录redo log,此时redo log进入 prepare状态。
  3. 执行器收到通知后记录binlog,然后调用引擎接口,提交redo log为commit状态。更新完成。

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

总结

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