深入理解Mysql（一）

2024-10-19 07:03:54

一、MySQL架构及查询语句执行流程

（1）MySQL的架构

（2）查询语句的执行流程

一、MySQL架构及查询语句执行流程

首先明确一下自己的想法：了解 MySQL 架构能让你明白数据库是如何接收、处理和存储数据的。这有助于你理解为什么某些查询会快，而某些查询会慢。

Mysql整体上的架构是C/S（客户端/服务器）架构，而具体MySQL服务器的架构可以看下文分析

目标是回答出下面的问题：

① MySQL的整体架构，分为哪几个部分，使用上需要注意什么
② 一条查询语句的执行流程是什么样的

（1）MySQL的架构

客户端依据通信协议请求服务端，而MySQL这个服务器执行SQL语句命令并给出反馈，整体架构如下：

可以粗略的把MySQL服务器分为两层，上面的为Server层，主要包括连接器、查询缓存、解析器【解析器+预处理器】、优化器、执行器等，所有跨存储引擎的功能都在这一层（Server层）实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。

连接器：身份认证和权限相关(登录 MySQL 的时候)，我们在数据库层执行SQL语句时，应用程序会连接到相应的数据库服务器，把SQL语句发送给服务器处理，实际上也就是通信，其中数据库支持两种连接方式
- 长连接（持久连接）：一旦建立，会在相对较长的时间内保持打开状态，可被重复使用来执行多个数据库操作。
  - 优点：减少了连接建立和关闭的开销（因为建立数据库连接是一个相对耗时的操作，频繁地建立和关闭连接会消耗大量的系统资源和时间）
  - 缺点：如果长时间不活动，可能会导致连接超时或者被数据库服务器断开。此外，长连接可能会占用数据库服务器的资源，尤其是在连接数量较多的情况下。
  - 适用：需要频繁与数据库进行交互的应用场景，如 Web 应用服务器、长期运行的后台任务等。
  - 实现方式（java例）：
```
// 通过设置连接字符串中的参数可以实现长连接。
// autoReconnect=true 表示在连接断开时自动重新连接，有助于保持长连接的稳定性。
// connectionTimeout 和 socketTimeout 设置为 0 表示无超时限制，但在实际应用中不建议这样设置，可根据具体情况设置合适的超时时间。
   String url = "jdbc:mysql://localhost:3306/mydb?autoReconnect=true&connectionTimeout=0&socketTimeout=0";
   Connection conn = DriverManager.getConnection(url, "username", "password");
```
- 短连接（临时连接）：每次执行完一个数据库操作后就立即关闭连接。
  - 优点：不会长时间占用数据库连接资源，对于临时性的、少量的数据库操作比较高效。
  - 缺点：每次操作都需要重新建立连接，增加了连接建立和关闭的开销。
  - 适用：临时性的、少量的数据库操作场景，如一次性的数据导入 / 导出脚本、简单的命令行工具等。
  - 实现方式（java例）：
```
// 不设置额外的长连接参数，每次使用完连接后主动关闭即可实现短连接
try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "username", "password")) {
    // 执行数据库操作
} catch (SQLException e) {
    e.printStackTrace();
}
```
查询缓存：执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。在接收到查询请求后，并不会直接去数据库查询，而是在数据库的查询缓存中找是否有相对应的查询数据（某条给定的查询语句在第一次执行时，服务器会缓存这条查询语句和他返回的结果。）
- 如果存在，那么在返回查询结果之前，MySQL会检查一次用户权限。如果权限没有问题，key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接从缓存中拿到结果返回给客户端。
  - 查询不会被解析，不用生成执行计划，不会进行执行查询引擎。
  - 判断是否命中缓存是将此查询语句和缓存中的查询语句进行比对，如果完全相同，那就认为它们是相同的，就认为命中缓存了。
- 如果不存在，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中
解析器：没有命中缓存的话，SQL 语句就会经过解析器，Mysql通过将SQL语句进行解析，并生成一棵对应的解析树（AST）。MySQL解析器将使用MySQL语法分析(语法规则验证)和解析查询，比如：验证是否使用错误的关键字，或者关键字的顺序是否正确
预处理器：预处理器根据一些MySQL规则进一步检查解析树（AST）是否合法，如数据表和数据列是否存在，解析列名和别名，是否有歧义。接下来预处理器会验证用户权限(precheck)。查看用户是否有相应的操作权限
优化器：按照 MySQL 认为最优的方案去执行。例如表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序，将SQL语句转化成执行计划，一条查询可以有很多种执行方式，最后都返回相同的结果，最后找到其中最好的执行计划（Mysql使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划的成本，选择其中成本最小的一个）
执行器：Mysql根据执行计划给出的指令逐步执行。开始执行的时候，要先判断一下你对这个表有没有执行查询的权限，如果没有就会返回没有权限的错误。在此过程中，有大量的操作需要通过调用存储引擎实现的接口完成，这些接口即为handler API接口。查询中的每一个表由一个 handler 的实例表示。（实际上，在优化阶段Mysql就为每一个表创建了一个 handler 实例，优化器可以根据这些实例的接口获取表的相关信息，如表的所有列名、索引统计信息等）

总体而言，MySQL的架构分为两层

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎

这两层共同构建了MySQL，以上各个环节中包含如下三个注意事项：

连接的权限时效，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置
长连接如何使用？因为创建连接比较复杂，所以建议使用长连接，但是长连接容易OOM（Out Of Memory）
- 定期断开长连接，使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连
- 如果用的是 MySQL 5.7 或以上版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，只会将连接恢复到刚刚创建完时的状态
不建议使用查询缓存，查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空，8.0已经将该功能彻底移除了

总结而言，连接设置修改只影响后续连接，长连接虽好，但还是定期断开，否则容易OOM，不建议使用查询缓存。

（2）查询语句的执行流程

在了解了MySQL的架构之后，将正式去理解SQL语句如何执行以及是如何进行优化的。以下面的查询语句举例分析，从人员库的人员信息表里拿出一条人员数据：

use User
select * from UserInfo where name='msx' and age = '22' and sex = '男'

解析器阶段生成的解析树AST结构：

分析这条语句的执行流程：

连接器：连接数据库User，并通过输入账号密码通过连接认证【数据库权限check】
查询缓存：先执行查询缓存，如果命中数据，在返回之前先判断是否有权限【查询缓存check】，如果有则返回，没有则继续向下执行（MySQL 8.0 版本以前版本有）
分析器：若没有命中缓存，进行语法分析，提取关键字：use、select、from、where、and，判断关键字是否满足MySQL的语法
预处理器：进一步获取UserInfo表名、列名：name、age、sex，判断这些元素是否都存在，如果都存在则验证权限【权限precheck】，如果权限存在继续向下
优化器：判断先获取哪一列，产生各种方案【name->age->sex、name->sex->age、age->sex->name等】，最终会选取最优、成本最小的方案执行
执行器：执行前先判断是否有权限执行语句【表权限check】，调用handler查询相关接口，从InnoDB存储引擎中获取数据
- 调用InnoDB引擎接口取这个表的第一行，判断是否满足条件name='msx' and age='22' and sex='男' 如果不是则跳过，如果是则将这行保存在记录集中
- 调用引擎接口取“下一行”，重复的判断逻辑，直到取到这个表的最后一行
- 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端

码农公寓

一、MySQL架构及查询语句执行流程

（1）MySQL的架构

（2）查询语句的执行流程

相关文章