MySQL体系结构概览

  • 2022 年 1 月 20 日
  • 笔记

作者：IT王小二

博客：//itwxe.com

一条 SQL 语句在 MySQL 中怎么执行的呢，这篇就来认识下 MySQL 的各个组件的作用。

一、结构组件

首先需要 MySQL 安装的客官看这两篇，小二演示使用的是 Docker 的安装方式：

先上个图，小二自己画的，绝对的高清无码，嘿嘿。不足之处欢迎指正哈。

MySQL体系结构

1. 企业管理服务和工具

系统管理和控制工具，例如 MySQL 备份恢复、MySQL 复制、MySQL 集群等工具。

2. 连接池

负责监听对客户端向 MySQL Server 端的各种请求，建立连接、权限校验、维持和管理连接，通信方式是半双工模式，数据可以双向传输，但不能同时传输。

  • 单工：数据单向发送。
  • 半双工：数据双向传输，但不能同时传输。
  • 全双工：数据双向传输，可以同时传输。

那么 MySQL 是怎么保存连接得嘞？

每个成功连接 MySQL Server 的客户端请求都会创建或分配一个线程，在内存中分配一块空间存储对应的会话信息，其中包含权限等信息，该线程负责客户端与 MySQL Server 端的通信，接收客户端发送的命令，传递服务端的结果信息等。

用户的权限表在系统表空间的 mysql 库中的 user 表中，这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

一些有点用的命令。

MySQL 允许最大的连接数

show variables like '%max_connections%';

MySQL 允许最大的连接数

这个值可以在 my.cnf 文件中配置，Docker 安装完 MySQL 版本为 5.7.36 默认值为 151 个最大允许连接数。

项目中可能会遇到 MySQL: ERROR 1040: Too many connections 的异常情况，造成这种情况的原因有两个。

  • 一种是 MySQL 配置文件中 max_connections 值过小，可以在配置文件 my.cnf 中添加max_connections 参数增大最大连接数，例如 max_connections = 500。
  • 一种是访问量过高，MySQL 服务器抗不住，这个时候就要考虑增加从服务器分散读压力。

查询当前 MySQL 服务器接收所有的连接信息

show processlist;

查询当前 MySQL 服务器接收所有的连接信息

State 状态常见的就是 Sleep 和 Query，详解自行度娘、谷哥，一般也没啥人看。

  • Sleep：线程正在等待客户端发送新的请求。
  • Query：线程正在执行查询或者正在将结果发送给客户端。

数据库闲置连接超时时间

#非交互式超时时间，如 JDBC 程序
show global variables like 'wait_timeout'; 
#交互式超时时间，如数据库工具
show global variables like 'interactive_timeout';

数据库闲置连接超时时间

可以看到超时时间都是默认8小时，即当客户端状态连接后为 Sleep 的时候，如果8小时没有收到请求那么就会断开连接。

MySQL状态分析

show global status like 'Thread%';

MySQL状态分析

  • Threads_cached：服务器端缓存连接
  • Threads_connected：当前打开的连接数
  • Threads_created：创建的线程数
  • Threads_running：正在运行的线程

3. SQL接口

负责接收用户 SQL 命令，如 DML，DDL 和存储过程等，并将命令发送到其他部分，并接收其他部分返回的结果数据，将结果数据返回给客户端。

关于这个组件小二也有点懵，那么具体分发到哪些组件上面去了，翻了很多资料基本就是一句话解释的，以后翻翻高性能 MySQL 看看有详细描述没~

4. 查询缓存

首先需要说明：在 MySQL8.0 中已经删除了查询缓存，MySQL5.7 中仍然存在查询缓存。

如果开启了 MySQL 缓存的话，成功获取一个 MySQL 连接后，会先到查询缓存看看，之前是不是执行过这条语句。

如果之前执行过，那么这条语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。

如果语句不在查询缓存中，就会继续后面的执行阶段，通过存储引擎去查询。执行完成后，执行结果会被存入查询缓存中。

如果命中缓存的话那么速度确实会很快，但是…但是…MySQL 的这个缓存功能往往却比较鸡肋，为什么这么说呢？

涉及到缓存，那当然就有和源数据保持一致性的问题，或者说同步的问题。

那么想一想，MySQL 如果要保持查询查询缓存数据结果的一致性，同时那个表经常性的更新数据，那么每更新一条数据，MySQL 为了保持一致性就要把该表所有的 key 全部查询一次，那么对于一个频繁更新的表来说那么 MySQL 的压力就太大了。

所以…，MySQL 选择了最简单粗暴的方式，如果该表一更新数据，就从查询缓存删除该表所有的 key，即从查询缓存删除不该表相关的所有查询语句缓存。

那么既然说是鸡肋，那当然还是有一点点用的，只要使用得当，那么什么场景下查询缓存可以发挥那么一点点作用呢？

如果说在项目中不想引入 Redis，那么这个查询缓存能不能在某些方面加快一些查询速度呢，当然是可以的。

既然频繁更新的表不适用查询缓存，那么我们开发中几个月才会更新一次的表不就正好合用吗，例如常见的系统配置表、字典表等，同时 MySQL 也正好提供了按需使用的策略方式。

怎么按需使用呢？

首先把查询缓存开启按需配置，查询 show global variables like "%query_cache_type%"; 如果结果为 OFF，那么就需要在 MySQL 配置文件 my.cnf 中配置如下参数后重新启动 MySQL 即可。

# 查询缓存开启，OFF 关闭，ON 开启
query_cache_type = ON

# 缓存策略，0代表关闭查询缓存 OFF，1代表开启 ON，2代表 DEMAND，DEMAND代表当sql语句中有SQL_CACHE 关键词时才缓存
query_cache_type=2

如果你和小二一样的的方式安装的 MySQL，docker 安装默认没有 my.cnf 文件的，那么需要自己在 docker 映射目录 /itwxe/dockerData/mysql/conf 下面创建文件 my.cnf，添加如下内容使用 docker restart mysql 重启容器即可开启查询缓存。

[mysqld]
query_cache_type = ON
query_cache_type = 2

添加 my.cnf 后重启容器，登录 MySQL 后可以看到查询缓存已经开起来了。

查询缓存按需开启

那么接下来只需要在需要缓存结果的查询语句上面加上一个 SQL_CACHE 显示指定即可，例如：

SELECT SQL_CACHE * FROM test_innodb WHERE id = 6；

查看一下缓存的运行信息。

show status like'%Qcache%';

缓存的运行信息

结果说明：

  • Qcache_free_blocks：表示缓存中相邻内存块的个数。数目大说明可能有碎片。
  • Qcache_free_memory：查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，可以根据实际情况做出调整。缺省从图中可以看到默认大小为 1Mb(图中以b为单位)，可以在 my.cnf 中通过 query_cache_size = 20M 指定缓存大小。
  • Qcache_hits：表示有多少次命中缓存。
  • Qcache_inserts：表示多少次未命中然后插入，意思是新的查询 SQL 请求在缓存中未找到，需要执行查询处理，执行查询处理后把结果 insert 到查询缓存中。
  • Qcache_lowmem_prunes：该参数记录有多少条查询因为内存不足而被移除出查询缓存，通过这个值可以适当的调整缓存大小。
  • Qcache_not_cached：表示因为 query_cache_type 的设置而没有被缓存的查询数量。
  • Qcache_queries_in_cache：当前缓存中缓存的查询数量。
  • Qcache_total_blocks：缓存中块的数量。

各个参数和缓存的效果可以多查询几遍试试就明白啦，鸡肋还是有一点点用的，哈哈，当然你要是已经有了 Redis 等缓存中间件就不需要查询缓存啦，同时 MySQL8.0 中也已经移除查询缓存功能。

5. 解析器

负责将接收到的 SQL 命令解析和验证。解析器主要功能：

  • 将 SQL 语句分解成数据结构，并将这个结构传递到后续步骤，以后 SQL 语句的传递和处理就是基于这个结构的。说人话就是将我们写的 SQL 语句分解成 MySQL 认识的语法往下传递。
  • 如果在分解构成中遇到错误，那么就说明这个 sql 语句是不合理的。说人话就是看看我们写的 SQL 语句有没有语法错误。

6. 查询优化器

负责 SQL 语句在查询之前对查询进行优化，这个过程会使用 optimizer trace 优化查询 SQL，然后计算各种可以使用的索引和全表扫描的查询成本相比较，选择最优的查询方式。

optimizer trace 工具会在后面的文章中说到，MySQL 到底通过什么规则计算的查询成本的，为什么有时候明明有可以使用的索引最后还是走的全表扫描，在后面章节小二会提到哦，欢迎各位客官关注。

7. 可拔插存储引擎

存储引擎就是如何管理操作数据(存储数据、更新数据、查询数据等)的一种方法，当然在 MySQL 中。而可拔插就可以理解为 MySQL 提供了一个接口，只要遵循规则即可以自定义实现存储引擎，Java中接口与实现类的关系。

8. 文件系统

文件系统主要是将数据库的数据存储在操作系统的文件系统之上，并完成与存储引擎的交互。例如数据库文件，表文件和各种日志文件(bin log、redo log、undo log等)。

二、一条SQL语句的执行过程概览

一条SQL语句的执行过程概览

相信仔细看了查询缓存的都不会问小二为啥新增、修改、删除都要走查询缓存了吧，嘿嘿。总览到此结束，接下来下一篇就讲讲 Explain 执行计划。

都读到这里了，来个 点赞、评论、关注、收藏 吧！

VirMach 便宜 VPS

QNews

QNews