MySQL架构你了解多少？

MySQL是一个服务器-客户端应用，MySQL8.0服务器是由连接池、服务管理工具和公共组件、NoSQL接口、SQL接口、解析器、优化器、缓存、存储引擎、文件系统组成。MySQL还为各种编程语言提供了一套用于外部程序访问服务器的连接器。整体架构图如下所示:

MySQLConnectors:为使用MySQL服务的编程语言平台，提供了访问接口，可以根据自己实际使用的编程语言到官网下载地址下载。
MySQL Shell:是一个高级客户端和代码编辑器，以组件的形式提供，需要单独安装。除了提供的类似于 mysql 客户端的功能，还可以使用 JavaScript和 Python 调用MySQL的API，一般为开发人员使用。
连接层:对客户端连接进行权限校验并保存客户端的连接信息，通过池化技术实现线程重用，以及根据具体的配置限制连接数量。
服务管理和公共组件:提供了数据备份与恢复，安全组件，主从复制和集群管理，表分区等实用功能。
服务层:提供了NOSQL API,SQL API,SQL语句解析，SQL语句优化，SQL语句缓存等组件，并将优化后的SQL语句发送至存储引擎执行相应的操作并返回结果。
存储引擎层:一系列可插拔的存储引擎，主要负责数据的写入和读取，与底层的数据和日志文件进行交互，可以根据具体的业务需求选择不同的存储引擎，后面我们具体介绍他们之间的区别。
文件系统层:包含了MySOL发行版的文件和程序，以及具体数据库文件和日志。

1. 连接层

连接层的作用是处理客户端的连接，这个小节主要介绍MSOLServer如何管理连接，包括对可用连接接口的描述和服务器如何使用连接处理线程。

1.1 网络端口和连接管理线程

网络端口

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

连接管理线程

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

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

1.2 客户端连接线程管理

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

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

系统变量 thread_cache_size 决定了线程池缓存的大小。默认情况下，服务器在启动时会自动调整这个值，但也可以通过选项文件明确指定大小，值为0时禁用缓存，此时为每个新连接创建执行一个线程，并在连接断开时释放;
有些复杂的SQL语句在执行过程中可能会有深层递归从而消耗更多的内存，通过设置thread stack=N 调整线程堆栈大小;
要查看缓存中的线程数以及超过缓存数后新创建的线程数，通过状态变量 Threads_cached 和Threads created查看;

1.3 连接器管理

系统变量 max_connections 可以控制服务器允许同时连接的最大客户端数，当服务器达到max_connections 指定的连接数时会拒绝所有新的连接请求，同时会增加状态变量Connection_errors_max_connections 的值;
mysqld实际上允许 max_connections+1 个客户端连接。额外的连接为拥有CONNECTION_ADMIN 权限的帐户(管理员)使用，即使普通连接达到了 max_connections 的数量，管理员也可以连接到服务器进行管理操作;
在部署为主从复制的环境中，从节点的连接数也会计入 max_connections 中，如果连接达到上限主从复制将会失败;
max_connections 具体数据和服务器的硬件有关，比如可用的内存、每个连接消耗的内存，每个连接的工作负载、响应时间、可用文件描述符的数量等。

2. 服务层

数据库服务层是整个数据库服务器的核心，主要包括了服务管理和公共组件、NOSQL和SQL接口、解析器、查询优化器和缓存等部分，下面我们分别介绍每个部分的作用:

2.1 服务管理和公共组件

MySQL提供了多种功能服务以满足不同使用场景下的需要，常用的服务如下：

1. Backup & Recovery（备份与恢复）

MySQL提供了多种备份与恢复的功能，确保数据的安全性和完整性。常用的工具和方法包括：

mysqldump：逻辑备份工具，可以生成SQL脚本形式的备份。
MySQL Enterprise Backup：企业版提供的物理备份工具，支持热备份。
二进制日志：可以用来恢复最近的事务。

2. Security（安全）

MySQL提供了多种安全功能来保护数据库，包括：

用户认证和授权：通过用户名和密码进行访问控制，细粒度的权限管理。
SSL/TLS：加密通信，防止数据在传输过程中被窃听。
审计日志：记录数据库的访问和操作行为，帮助监控和审计。

3. Replication（主从复制）

MySQL的主从复制功能允许在多个服务器之间同步数据，常见的复制模式有：

异步复制：主库提交事务后，不等待从库确认即可继续处理其他事务。
半同步复制：主库提交事务后，至少等待一个从库确认。
组复制：多主复制模式，提供更高的可用性和一致性。

4. Cluster（MySQL集群）

MySQL集群（NDB Cluster）是一种分布式数据库解决方案，适用于高可用性和高吞吐量的场景。主要特点包括：

数据分片：将数据分布在多个节点上，支持横向扩展。
高可用性：自动故障转移，确保数据的持续可用。
实时性：适用于需要实时数据处理的应用。

5. Partitioning（表分区）

表分区功能可以将一张大表拆分成多个更小的、独立的部分（分区），提高查询和管理的效率。常见的分区类型有：

范围分区：基于列值范围分区。
列表分区：基于列值列表分区。
哈希分区：基于列值的哈希函数分区。

6. Instance Manager（实例管理）

实例管理工具帮助用户管理MySQL实例，包括启动、停止、重启等操作。主要工具有：

mysqld_multi：管理多个MySQL实例的启动和停止。
MySQL Instance Manager：用于监控和管理MySQL实例的工具。

7. Administrator（MySQL管理员）

MySQL管理员工具提供了数据库的管理和维护功能，包括：

MySQL Workbench：图形化管理工具，支持数据建模、SQL开发、服务器配置等。
mysqladmin：命令行管理工具，支持查看服务器状态、创建用户、执行备份等操作。

8. Migration Toolkit（迁移工具包）

MySQL提供了多种迁移工具，帮助用户从其他数据库系统迁移到MySQL。常用的迁移工具包括：

MySQL Workbench Migration Wizard：支持从Oracle、Microsoft SQL Server、PostgreSQL等数据库迁移数据到MySQL。
MySQL Shell：提供导入和导出数据的功能，支持从其他数据库系统迁移数据。

2.2 NoSQL接口与SQL接口

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

2.3 Parser(语法分析器）

语法分析器的主要作用是将客户端发来的SQL语句中的关键字和自定义字段进行提取、解析，最终将 SOL语句转换为一棵解析树，分析的过程中包含词法分析和语法分析;词法分析，主要是对关键字进行提取，比如 select/update/delete/create...;语法分析，主要判断 SQL 语句是否满足语法规则，如果语法错误则异出异常，也就是我们常见的ERROR 1064(42000):You have anerror in your SOL syntax。

2.4 Optimizer(查询优化器)

通过语法校验的SQL语句将进入查询优化器处理阶段，查询优化器会将解析树转化为查询计划，一般情况下，一条查询可以有很多种执行方案，查询优化器会根据执行计划匹配合适的索引，选择最佳的执行方案，最终把确定要执行的SOL交给执行器调用存储引擎API。

2.5 aches& Buffers(缓存)

MVSQL的缓存主要的作用是为了提升查询的效率，当服务器接收到一个 select 查询语句时，会先进入缓存查询当前SQL语句在缓存中是否存在，缓存以 key和 value 的形式存储，key是具体的SOL语句，value是结果的集合，如果命中缓存，直接返回结果，无法命中缓存，则进入分析器进行正常查询流程。
这里需要说明的是，缓存数据对应的数据在被更新之后将会失效，尤其在写多读少的场景中，缓存会频繁失效与新增，命中率非常低，因此MySOL5.6之后服务层缓存功能默认关闭，而且在MySQL8.0中服务层缓存被官方删除。

2.6 SQL语句的执行流程

3. 存储引擎

存储引擎是处理不同表类型SQL操作的MySQL组件。MySQL服务器采用可插拔的存储引擎架构，在服务器运行时可以动态的加载和卸载。
查看当前服务器支持哪些存储引擎可以使用 SHOW ENGINES 语句， Engine 表示:存储引擎的名称，Support:表示当前服务器是否支持，值分别为:YES、NO 和 DEFAULT 分别表示，支持、不支持和当前设置或默认引擎，如下所示:

1 mysql> SHOW ENGINES\G #查看当前服务⽀持的存储引擎
2 *************************** 1. row ***************************
3 Engine: MEMORY
4 Support: YES
5 Comment: Hash based, stored in memory, useful for temporary tables
6 Transactions: NO
7 XA: NO
8 Savepoints: NO
9 *************************** 2. row ***************************
10 Engine: MRG_MYISAM
11 Support: YES
12 Comment: Collection of identical MyISAM tables
13 Transactions: NO
14 XA: NO
15 Savepoints: NO
16 *************************** 3. row ***************************
17 Engine: CSV
18 Support: YES
19 Comment: CSV storage engine
20 Transactions: NO
21 XA: NO
22 Savepoints: NO
23 *************************** 4. row ***************************
24 Engine: FEDERATED
25 Support: NO
26 Comment: Federated MySQL storage engine
27 Transactions: NULL
28 XA: NULL
29 Savepoints: NULL
30 *************************** 5. row ***************************
31 Engine: PERFORMANCE_SCHEMA
32 Support: YES
33 Comment: Performance Schema
34 Transactions: NO
35 XA: NO
36 Savepoints: NO
37 *************************** 6. row ***************************
38 Engine: MyISAM
39 Support: YES
40 Comment: MyISAM storage engine
41 Transactions: NO
42 XA: NO
43 Savepoints: NO
44 *************************** 7. row ***************************
45 Engine: InnoDB
46 Support: DEFAULT
47 Comment: Supports transactions, row-level locking, and foreign keys
48 Transactions: YES
49 XA: YES
50 Savepoints: YES
51 *************************** 8. row ***************************
52 Engine: ndbinfo
53 Support: NO
54 Comment: MySQL Cluster system information storage engine
55 Transactions: NULL
56 XA: NULL
57 Savepoints: NULL
58 *************************** 9. row ***************************
59 Engine: BLACKHOLE
60 Support: YES
61 Comment: /dev/null storage engine (anything you write to it disappears)
62 Transactions: NO
63 XA: NO
64 Savepoints: NO
65 *************************** 10. row ***************************
66 Engine: ARCHIVE
67 Support: YES
68 Comment: Archive storage engine
69 Transactions: NO
70 XA: NO
71 Savepoints: NO
72 *************************** 11. row ***************************
73 Engine: ndbcluster
74 Support: NO
75 Comment: Clustered, fault-tolerant tables
76 Transactions: NULL
77 XA: NULL
78 Savepoints: NULL
79 11 rows in set (0.01 sec)

这里不对这些存储引擎作展开说明，后面会通过博客专题单独展示，引擎的内容比较重要，我单独博客介绍。先给出这些引擎的特性：

Notes:

1. Implemented in the server, rather than in the storage engine.

2. Compressed MyISAM tables are supported only when using the compressed row format.

Tables using the compressed row format with MyISAM are read only.

3. Implemented in the server via encryption functions.

4. Implemented in the server via encryption functions; In MySQL 5.7 and later, data-at-rest

encryption is supported.

5. Implemented in the server via encryption functions; encrypted NDB backups as of NDB

8.0.22; transparent NDB file system encryption supported in NDB 8.0.29 and later.

6. Support for FULLTEXT indexes is available in MySQL 5.6 and later.

7. Support for geospatial indexing is available in MySQL 5.7 and later.

8. InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.

9. See the discussion later.

4. 文件系统层

MySQL的系统文件层主要负责数据的存储、管理和访问。了解这些系统文件的结构和作用，可以帮助我们更好地管理和优化MySQL数据库。

1. 数据目录

数据目录是MySQL存储数据库文件的地方。默认情况下，数据目录包含数据库文件、日志文件和配置文件等。可以通过配置文件中的datadir参数来指定数据目录的位置。

2. 配置文件（my.cnf 或 my.ini）

配置文件包含MySQL服务器的各种配置参数，如网络设置、存储引擎配置、缓存大小等。常见的配置文件位置：

在Unix/Linux系统上，通常是/etc/my.cnf或/etc/mysql/my.cnf。
在Windows系统上，通常是C:\ProgramData\MySQL\MySQL Server x.x\my.ini。

3. 日志文件

日志文件记录了数据库的各种操作和事件，帮助数据库管理员进行故障排查和性能优化。常见的日志文件包括：

错误日志（Error Log）：记录MySQL服务器的启动、停止以及运行过程中的错误信息。默认文件名是hostname.err。
查询日志（General Query Log）：记录所有执行的SQL语句，默认未开启。可以通过general_log和general_log_file参数开启并指定文件名。
慢查询日志（Slow Query Log）：记录执行时间超过指定阈值的SQL语句，用于优化查询性能。可以通过slow_query_log和slow_query_log_file参数开启并指定文件名。
二进制日志（Binary Log）：记录所有更改数据的SQL语句，用于数据恢复和主从复制。默认文件名是hostname-bin。