Mysql-Sql是如何执行的

目录 显示

一图胜千言，开局一张图：

连接器

连接器是 MYSQL 的门户，它有以下作用：

  1. 当客户端通过连接器完成 TCP 的3次握手，建立长连接。长连接的超时时间是通过 wait_timeout 控制的，默认 8 小时。
  2. 连接器再负责验证你的用户身份，并获得相应的权限。没有对应 sql 的权限就会报错。

SHOW PROCESSLIST 命令，它可以显示当前 MySQL 服务器上所有正在运行的线程（thread）和进程（process）的信息。通过这个命令，我们可以了解到哪些用户正在连接数据库、正在执行哪些 SQL 语句、这些语句的执行状态以及执行时间等。

最大连接数

MySQL数据库有一个重要的参数 max_connections，它限制了同时可以连接到MySQL服务器的客户端数量。超过这个限制，新的连接请求就会被拒绝。
为什么要限制连接数呢？

  • 资源限制： 每个连接都会占用系统资源，如内存、CPU等。如果连接数过多，会消耗过多的系统资源，影响数据库的性能。

  • 安全考虑： 过多的连接可能会导致安全风险，例如遭受 DDoS 攻击。

    查看MySQL最大连接数

SHOW VARIABLES LIKE 'max_connections';

修改MySQL最大连接数

  1. 修改配置文件：

  • 找到配置文件： 一般在MySQL安装目录下的 my.cnf 或 my.ini 文件中。

  • 修改参数： 找到 max_connections 参数，将其值修改为期望的最大连接数。例如：

    max_connections = 1000

  • 重启MySQL服务： 修改配置文件后，需要重启MySQL服务使配置生效。

  1. 使用命令行修改：

    SET GLOBAL max_connections = 1000;
    • 注意： 这种方式修改的配置仅在当前会话有效，重启MySQL后会失效。

综上所述，我们在使用最大连接数时，要注意以下事项：

  • 最大连接数的设置：
    • 不要设置过大： 过大的连接数会占用过多的系统资源，影响数据库性能。
    • 根据实际需求设置： 根据系统的硬件配置和应用的并发量来合理设置。
  • 其他影响连接数的因素：
    • 操作系统限制： 操作系统对进程数、文件描述符等也有限制。
    • MySQL配置： 其他一些参数，如 wait_timeout、interactive_timeout 等，也会影响连接数。

其他相关参数

  • wait_timeout： 一个连接在空闲状态下保持打开的最大秒数。
  • interactive_timeout： 一个交互式连接在空闲状态下保持打开的最大秒数。
  • max_connections_per_hour: 每小时允许的最大连接数。
  • connections: 当前的连接数。

查询缓存

MySQL查询缓存（Query Cache）是一种性能优化机制，它会将执行过的SELECT语句及其结果集存储在内存中。当再次执行相同的查询时，MySQL会直接从缓存中返回结果，从而避免了再次执行查询的开销。

实际上查询缓存在高版本已经被弃用了，着是因为查询缓存往往弊大于利：

  • 缓存污染: 更新任意数据会导致缓存失效。如果数据更新频繁，缓存中的数据可能很快过期，导致缓存命中率降低，甚至返回错误的结果。
  • 内存占用: 查询缓存会占用大量的内存，如果缓存过大，可能会影响其他服务的性能。
  • 不适用于复杂的查询: 对于包含变量、函数、子查询等复杂的查询，查询缓存的效果并不理想。
  • 缓存失效机制不够灵活: MySQL的缓存失效机制相对简单，无法满足所有场景的需求。

正确的方式是不用 MySQL 自己的缓存，而是在客户端的应用层做缓存（比如Redis），这样能更好根据业务控制缓存的命中。同时如果是读写分离的设计，也能提高读的性能，完全不需要用 MySQL 的缓存机制。

缓存相关的命令

# 启动缓存
SET GLOBAL query_cache_type = 1;
# 设置缓存大小
SET GLOBAL query_cache_size = 16M;
# 查询缓存状态
SHOW STATUS LIKE 'Qcache%';

分析器

MySQL 分析器是数据库执行 SQL 语句的第一道关卡，它负责将我们写的 SQL 语句“翻译”成数据库能够理解的内部指令。

假设我们执行以下 SQL 语句：

SELECT * FROM users WHERE age > 18;

分析器会进行如下处理：

  • 1. 词法分析:
    • 将 SQL 语句拆分成一个个单词（token），比如 SELECT、FROM、WHERE 等关键字，以及表名、字段名等标识符。
  • 2. 语法分析:
    • 根据 MySQL 的语法规则，检查拆分出来的单词是否符合语法要求。比如，SELECT 语句后面必须跟着要查询的字段，FROM 后面必须跟着表名等。 如果语法错误，会抛出相应的错误信息。
  • 3. 生成解析树:
    • 将经过语法分析的 SQL 语句构建成一棵树状结构，称为解析树。解析树清晰地表示了 SQL 语句的结构和各个部分之间的关系。
  • 4. 语义分析:
    • 检查解析树中的语义是否正确。比如，检查表名(user)和字段名(age)是否存在，用户是否有权限访问这些对象等。

注意，第四步中权限检查，是检查库和表的权限。在连接器中也有权限检查，连接器检查的是客户端是否具有访问 MySQL 服务器的权限。两者是截然不同的。

优化器

MySQL 优化器是一个智能的组件，它负责为我们执行的 SQL 语句选择最优的执行计划。这个执行计划就像是一份详细的施工图纸，告诉数据库系统如何高效地获取数据。

优化器会从以下因素进行优化

  • 索引： 索引是优化器做出决策的重要依据。索引可以大大加快数据查找的速度。比如某些索引失效场景。
  • 数据分布： 表中的数据分布会影响索引的选择和查询方式。
  • 统计信息： MySQL会维护一些统计信息，比如表的行数、索引的基数等，这些信息会影响优化器的决策。
  • 系统参数： MySQL的一些参数，比如 join_buffer_size、sort_buffer_size 等，也会影响优化器的行为。

我们可以通过执行计划，查看优化器优化的结果。使用 EXPLAIN 命令 命令可以查看 MySQL 为一条 SQL 语句生成的执行计划。执行计划中包含了索引的使用情况、表扫描的方式、连接类型等信息。发现一个执行计划可视化工具，分享给大家。

但是，优化器并不是万能的，不能完全依靠优化器来对 SQL 进行优化，实际上它是有很大的局限性。我们应该自己就优化好 SQL。

  • 统计信息不准确： 如果统计信息不准确，优化器可能会做出错误的决策。
  • 复杂的查询： 对于非常复杂的查询，优化器可能难以找到最优的执行计划。
  • 优化器的算法： 优化器的算法是基于启发式的，不能保证一定找到全局最优解。

执行器

MySQL 执行器接收到优化器生成的执行计划，根据这个计划一步步执行，最终返回查询结果。

执行器的工作流程

  1. 接收执行计划: 执行器从优化器获取到一个经过优化的执行计划。
  2. 打开表: 调用存储引擎的接口，根据执行计划，打开需要操作的表。
  3. 逐行扫描: 按照执行计划中指定的顺序，逐行读取数据。
  4. 进行运算: 根据 SQL 语句中的条件进行过滤、排序、聚合等操作。
  5. 返回结果: 最终，执行器将符合条件的数据返回给客户端。
标签
SystemCaller
SystemCaller

https://gravatar.com/noisily745e35dad0

文章: 47

相关文章

留下评论

您的邮箱地址不会被公开。 必填项已用 * 标注