MySQL学习记录 —— 이십사 MySQL连接层和服务层

---

1、整体架构

MySQL8.0服务器是由连接池、服务管理工具和公共组件、NoSQL接口、SQL接口、解析器、优化器、缓存、存储引擎、文件系统组成。

客户端发来请求时，连接层会检查用户账户密码是否正确，连接数是否达到上限；接着来到服务层，接收并解析客户端发来的SQL语句，并且服务层的优化器会按照数据库的最高效的方式来处理SQL语句，8.0之后缓存的作用已经不大了，所以就不关注这方面，以及还有服务管理和公共组件，就像一个大饭店，还有休息区，娱乐区这样的。

当把这些SQL语句转化成高效的SQL语句后，就交给存储引擎层去处理。

MySQL Connectors：为使⽤MySQL服务的编程语言平台，提供了访问接口，可以根据自己实际使用的编程语言到官网下载。

MySQL Shell：是⼀个高级客户端和代码编辑器，以组件的形式提供，需要单独安装。除了提供的类似于 mysql 客户端的功能，还可以使用 JavaScript 和 Python 调用MySQL 的API，一般为MySQL数据库的开发人员使用。

连接层：对客户端连接进行权限校验并保存客户端的连接信息，通过池化技术实现线程重用，以及根据具体的配置限制连接数量。

服务管理和公共组件：提供了数据备份与恢复，安全组件，主从复制和集群管理，表分区等实用功能。

2、连接层

1、网络端口和连接管理线程

MySQL是一个网络服务，通过IP+端口号就可以找到网络上指定的一个MySQL服务。

OS中，一个进程申请一个端口提供对外服务，客户端访问端口，OS将请求转发给这个进程。一个进程也可以申请多个端口。

一台服务器能够侦听多个网络端口上的客户端连接，开放多个端口，只需在选项文件名中指定多个端口即可。

[mysqld]
port=3306
port=3305

连接层通过连接管理器线程处理端口上的客户端连接请求。

在所有平台上，用一个管理器线程处理所有的TCP/IP连接请求；在Unix上，管理器线程还可以处理其它Unix socket连接请求；在 Windows 上，使用一个管理器线程处理通过Shared-memory方式连接请求，使用另⼀个管理器线程处理Named-pipe方式连接请求；在所有平台上，可以额外启用一个端口用于接受针对管理的TCP/IP 连接请求，管理端口的连接可以使用处理"普通" TCP/IP 请求的管理器线程，也可以通过选项文件配置单独的线程。不同的连接请求用的连接协议也不同。

2、客户端连接线程管理

连接管理器线程在接收到每个客户端连接后，把请求转发到真正的执行线程，每个请求都对应一个执行线程，该线程处理连接的身份验证和具体请求。执行线程使用线程池技术进行缓存，当一个请求需要处理时，先从线程池中查找是否有可用的线程，如果没有则新创建⼀个，当连接结束时，如果线程池没有满，则把当前线程放入线程池，主要的作用是提高线程的复用，减少创建线程造成的系统开销从而提高效率。

通过以下几个系统变量和状态变量控制和监视服务器管理客户端连接的线程：

系统变量 thread_cache_size 决定了线程池缓存的大小。默认情况下，服务器在启动时会自动调整这个值，但也可以通过选项文件明确指定大小，值为 0 时禁用缓存，此时为每个新连接创建执行一个线程，并在连接断开时释放。

有些复杂的SQL语句在执行过程中可能会有深层递归从而消耗更多的内存，通过设置thread_stack=N 调整线程堆栈大小。

要查看缓存中的线程数以及超过缓存数后新创建的线程数，通过状态变量 Threads_cached和Threads_created 查看。

3、连接量管理

系统变量 max_connections 可以控制服务器允许同时连接的最大客户端数，当服务器达到max_connections 指定的连接数时会拒绝所有新的连接请求，同时会增加状态变量Connection_errors_max_connections 的值。

mysqld实际上允许 max_connections+1 个客户端连接。额外的连接为拥有CONNECTION_ADMIN 权限的帐账户（管理员）使用，即使普通连接达到了 max_connections 的数量，管理员也可以连接到服务器进行管理操作，主要是为了让所有的连接都能够高效地运作，让停下来无法运行的线程及时地被处理掉。

在部署为主从复制的环境中，从节点的连接数也会计入 max_connections 中，如果连接达到上限主从复制将会失败；

max_connections 具体数据和服务器的硬件有关，比如可用的内存、每个连接消耗的内存，每个连接的工作负载、响应时间、可用文件描述符的数量等等。

3、服务层

1、服务管理和公共组件

Backup & Recovery：备份与恢复，有全量备份和增量备份。全量是指把数据库所有内容都备份一遍，增量是指每隔一段时间把这段时间内的备份。全量和增量组合着备份。
Security：安全
Replication：主从复制
Cluster：MySQL集群
Partitioning：表分区
Instance Manager：实例管理
Administrator：MySQL管理员
Migration Toolkit：迁移工具包

2、NoSQL接口与SQL接口以及Parser语法分析器

两个接口主要负责接收客户端发送的各种SQL语句和命令，并将SQL发送到其他部分，然后把接收到的结果返回给客户端。

客户端发来的都是文本形式，两个接口会把它们发给Parset语法分析器进行解析。Parset将客户端发来的SQL语句中的关键字和自定义字段进行提取、解析，最终将 SQL 语句转换为一棵解析树，分析的过程中包含词法分析和语法分析。

词法分析，主要是对关键字进行提取，比如 select/update/delete/create… ；语法分析，主要判断 SQL 语句是否满足语法规则，如果语法错误则异出异常，也就是我们常见的ERROR 1064 (42000): You have an error in your SQL syntax。

对于一个命令会形成这样的语法树

3、优化器和缓存

经过Parser后，来到优化器。查询优化器会将解析树转化为查询计划，一般情况下，一条查询可以有很多种执行方案，查询优化器会根据执行计划匹配合适的索引，选择最佳的执行方案，最终把确定要执行的SQL交给执行器调用存储引擎API。

select sn, name from student where name = 'xx' and id = 1;
# 优化后
select sn, name from student where id = 1 and name = 'xx';

不过写得太低效优化器也没办法优化得很好。所以用户写的时候也要尽量高效。

MySQL的缓存主要的作用是为了提升查询的效率，当服务器接收到一个 select 查询语句时，会先进入缓存查询当前SQL语句在缓存中是否存在，缓存以 key 和 value 的形式存储，key是具体的SQL语句，value是结果的集合，如果命中缓存，直接返回结果，无法命中缓存，则进入分析器进行正常查询流程。但缓存数据对应的数据在被更新之后将会失效，尤其在写多读少的场景中，缓存会频繁失效与新增，命中率非常低，所以MySQL 8.0开始就没有这个功能了。

4、SQL语句执行流程

结束。