mysql的四层架构_MySQL 整体架构介绍

MySQL 的整体架构划分为服务器级和存储引擎级两个层级。其中服务器级包含了多种组件如连接器查询缓存分析器优化器执行器等它们共同实现了存储过程触发器视图内置函数的功能。在数据层面负责存储与提取操作的是InnoDBMyISAMMemory等多种数据库引擎。当客户端成功与服务器级建立连接后服务器会通过调用各数据引擎提供的相关接口来完成对数据库中信息的增删改查等基本操作。

连接器

负责和客户端建立连接，获取用户权限以及维持和管理连接。

用于通过调用processlist指令来获取链接状态信息。一旦用户建立连接，在线状态下即便管理员修改相关用户的权限设置，在线的用户不受影响。系统预设的有效时长是8小时，在此时间段之后链接将被切断。

简单说下长连接：

优势：在连接时间内，客户端一直使用同一连接，避免多次连接的资源消耗。

在 MySQL 运行过程中，默认情况下会使用连接对象负责管理使用的内存资源。由于这些资源长时间不释放可能会导致内存溢出问题进而被系统强制终止（kill）。因此为了防止这种情况发生必须定期断开长期未使用的高负载连接或者在执行大数据量查询后及时关闭相关连接。从 MySQL 5.7 版本开始可以通过 $mysql_rest_connection 初始化机制来创建新的连接资源这样就无需重新建立连接并进行权限验证等操作从而提升了系统的安全性与稳定性。

查询缓存

当系统接收到一个查询请求时，在缓存区域中首先查找(key/value存储)该(key/value)是否存在。如果不存在，则继续按照正常流程处理。

通常情况下，在实际应用中设置查询缓存并非必要

分析器

词法分析：

如识别 select，表名，列名，判断其是否存在等。

语法分析：

判断语句是否符合 MySQL 语法。

优化器

确定索引的使用，join 表的连接顺序等，选择最优化的方案。

执行器

在执行具体语句之前（即在具体语句执行之前），系统会先进行权限的检查。检查通过后（即权限检查通过后），系统将利用数据引擎提供的接口来执行查询操作。如果设置了慢查询模式，则会在相关日志记录中显示 rows_examined 来表示扫描的行数。在某些特定场景（如索引）中（即在某些特定情况下），执行器会调用一次相应的接口来处理请求；然而，在数据引擎内部可能还会进行多次查询操作（即可能多次调用数据库引擎）。需要注意的是，在这些情况下（如索引场景），虽然执行器只调用了一次接口（即只发送了一条请求指令），但在数据引擎内部却可能导致了多行的数据被扫描（即实际从数据库读取的数据量可能超过 rows_examined 的值）。因此，在数据引擎中可能扫描了更多行数据

未在执行阶段进行权限预检的原因：例如，在触发器等情况下，则必须等到执行阶段才能确认权限；而优化器阶段则无法进行相应的验证。

使用 profiling 查看 SQL 执行过程

打开 profiling 分析语句执行过程：

mysql> select @@profiling;

+-------------+

|@@profiling|

+-------------+

|0|

+-------------+

1 row in set, 1 warning (0.00 sec)

mysql> set profiling=1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

执行查询语句：

mysql> SELECT * FROM s limit 10;

+------+--------+-----+-----+

|s_id|s_name|age|sex|

+------+--------+-----+-----+

|1|z|12|1|

|2|s|14|0|

|3|c|14|1|

+------+--------+-----+-----+

3 rows in set (0.00 sec)

获取 profiles;

mysql> show profiles;

+----------+------------+--------------------------+

|Query_ID|Duration|Query|

+----------+------------+--------------------------+

|1|0.00046600|SELECT * FROM s limit 10|

+----------+------------+--------------------------+

mysql> show profile;

+----------------------+----------+

|Status|Duration|

+----------------------+----------+

|starting|0.000069|

| checking permissions | 0.000008 | 权限检查

| Opening tables | 0.000018 | 打开表

| init | 0.000019 | 初始化

| System lock | 0.000010 | 锁系统

| optimizing | 0.000004 | 优化查询

|statistics|0.000013|

| preparing | 0.000094 | 准备

| executing | 0.000016 | 执行

|Sending data|0.000120|

|end|0.000010|

|query end|0.000015|

|closing tables|0.000014|

|freeing items|0.000032|

|cleaning up|0.000026|

+----------------------+----------+

15 rows in set, 1 warning (0.00 sec)

查询具体的语句：

mysql> show profile for query 1;

+----------------------+----------+

|Status|Duration|

+----------------------+----------+

|starting|0.000069|

|checking permissions|0.000008|

|Opening tables|0.000018|

|init|0.000019|

|System lock|0.000010|

|optimizing|0.000004|

|statistics|0.000013|

|preparing|0.000094|

|executing|0.000016|

|Sending data|0.000120|

|end|0.000010|

|query end|0.000015|

|closing tables|0.000014|

|freeing items|0.000032|

|cleaning up|0.000026|

+----------------------+----------+

15 rows in set, 1 warning (0.00 sec)

MySQL 日志模块

如前所述，在MySQL体系中主要包含两个主要组件：一个是服务器层（简称Server），另一个是数据引擎层（称为Data Engine）。每个层级都独立维护着各自的日志记录文件。当采用InnoDB引擎时，则会生成一种称为redo log的专用日志文件。服务器层级则会自主管理并存储binlog文件。那么问题就来了：为什么要设计出两种不同的日志系统呢？

redo log

重做日志是一种专为InnoDB数据库设计的独特机制。那么为什么要引入重做日志呢？设想一个场景：MySQL为了确保数据的一致性和持久性，在执行存储过程时会将数据写入磁盘文件。我们都知道，在进行数据存储时会涉及大量的IO操作和查找操作。如果每次更新操作都需要这样的处理的话，则会导致整体运行效率显著下降。

由于直接将数据直接存储于硬盘不可行，则采用临时存放在内存后再进行硬盘更新的方法来解决问题

生成 redo log 之后不仅提升了运行效率 同时确保 MySQL 系统具备在崩溃时自动恢复的能力 在发生异常时也不会丢失任何数据

其中redo log被配置为固定大小，并支持配置一个包含4个文件的集合，在更新过程中依次读取并写入这四个文件

将记录当前写入位置的变量命名为pos，并将其值移动至下一位置。当完成对四个文件中的最后一个文件进行结尾处的操作后，将pos重新设置为编号为0的位置。

checkpoint标识可擦除的当前位置，在数据被写入磁盘时该 checkpoint将逐步向前推进。

其间的相对位置即用于记录更新操作的空间区域，在 write pos 追赶至 check point 时不再允许执行新的操作；此时应提前将数据通过检查点机制写入存储中以避免冲突。

可以将 innodb_flush_log_at_trx_commit 设置成 1，开启 redo log 持久化的能力。

binlog

binlog 属于服务器层的日志系统，在数据归档存储方面具有重要作用，在备份操作、主从同步过程中发挥重要作用，并在数据恢复、主从同步等操作中发挥重要作用。具体的操作步骤可参考《Binlog 恢复日志》这篇文章。

可以通过 sync_binlog=1 开启 binlog 写入磁盘。

这里对 binlog 和 redo 进行下区分：

拥有不同的所有者，在本系统中，BinLog由服务器层管理，并且所有的引擎均可使用。Redo日志则是InnoDB特有的功能。

类型不同，在数据库系统中将 binlog 视为一种逻辑日志，并主要记录了操作语句的原始逻辑信息，并且具有更高的层次性（相较于 statement）。而 redo log 则被视为一种物理日志，并详细描述了某个数据页在经历何种修改操作。

数据写入的方式不同，binog 日志会一直追加，而 redo log 是循环写入。

功能不同，binlog 用于归档，而 redo log 用于保证 crash-safe.

两阶段提交

下面执行器和 InnoDB 执行 Update 时内部流程：

以更新 update T set c=c+1 where ID=2; 语句为例：

该执行器主要依靠InooDB引擎处理位于特定ID行的数据。其中包含主键字段的每一行都会被引擎识别并进行处理操作。InooDB引擎采用树状索引方法定位目标记录；若目标数据页已加载至内存中则可直接返回结果；否则系统需自磁盘设备加载相应数据至内存后才能完成任务并返回结果

执行器接收引擎提供的数据信息，并对计数器变量 C 进行递增操作；在处理完当前行后，在引擎接口中发送更新指令以记录新的数据状态

引擎将该行数据加载至内存中，并将其更新操作记录在redo log中；随后将其状态设置为prepare阶段；引擎随后通知执行器，在适当的时间段内提交事务。

执行器生成这个操作的 binlog，并将 binlog 写入磁盘。

执行机构调用核心组件至事务提交接口中，在该操作中将已记录的 redo log字段设置为 commit 状态，并已完成所有更新操作。

浅色为执行器执行，深色为引擎执行。

完成内存更新后, 将重做日志的录入分为两个步骤: prepare 和 commit (即通常所说的两阶段提交). 这样做是为了确保在发生意外情况时数据的一致性.

这里假设下，如果不采用两阶段提交会发生什么？

依次记录redo log后再记录binlog。假设在完成redo log的记录后MySQL出现故障并重新启动，在此过程中由于redolog已经记录了相关信息而导致整个数据库的状态是正确的。但在此时若试图利用binlog进行备份或恢复操作则会发现缺少最后一次更新的数据信息从而导致整体数据存在不一致性

依次记录主日志和重传日志。主日志完成记录后 MySQL 发生异常重启导致重传日志未能完成记录。随后进行重启操作发现重传日志未能成功完成记录则该事务被视作无效操作与此同时主日志中多出一条更新指令用于修复问题从而导致恢复后的数据与原有数据不一致。

再分析下两阶段提交的过程：

在处理 redolog preparation 阶段时发生故障，在关注时刻 A 的位置信息之后重启操作。重启后检查发现 redolog 未完成记录，并导致当前事务重滚。

2.如果在写 binlog 时奔溃，重启后，发现 binlog 未被写入，回滚操作。

3.binlog 写完，但在提交 redo log 的 commit 状态时发生 crash

如果 redo log 中事务完整，有了 commit 标识，直接提交。

如果 redo log 中只有完整的 prepare, 判断对应 binlog 是否完整。

完整，提交事务

不完整，回滚事务。

如何判断 binlog 是否完整？

statement 格式 binlog，会有 COMMIT; 标识

row 格式的 binlog，会有 XID event. 标识

在 5.6 后，还有 binlog-checksum 参数，验证 binlog 正确性。

如何将 redo log 和 binlog 关联表示同一个操作？

结构中有一个共同的数据字段，XID. 在崩溃恢复时，会按顺序扫描 redo log:

如果有 prepare，又有 commit 的 redo log，直接提交。

如果仅在prepare阶段而没有commit操作，并且没有redemption log的情况下，则需要用XID去查找binlog中的对应事务进行判断。

数据写入后，最终落盘和 redo log 有无关系？

在正常运行的状态下，在对象 level 上（即实例 level），当内存中的页面发生更改时（即被更改），不再与磁盘中的数据页面保持同步（即不再匹配），这种情况被称为脏页（即 dirty page）。而复核过程则是将内存中的数据页面保留在目标磁盘上进行更新（即写入）。

对于 crash 情况，在发生时 InnoDB 会判断一个数据页是否丢失了更新，并将其加载到内存，并更新 redo 日志中的内存内容。完成更新操作后，在内存中形成的脏页将使系统处于第一种情况的状态。

redo log buffer 和 redo log 的关系？

在一个事务的更新过程中，存在多个 SQL 语句，所以是要写多次日志的。

在编写过程中, 必须在生成后进行保存; 不可以直接存入redo log。

因此，在内存区域中使用redo log buffer 用于将redo log日志预先存储于系统中。当提交操作进行时，会将buffer中的所有内容记录到redo log中。

总结

在文章开头部分进行阐述时提到MySQL的整体架构被分为Server层和引擎层，并简单阐述了一条语句是如何执行的过程。接着，在MySQL 5.5版本之后选择InnoDB作为默认引擎的原因在于它相比起原有的MyISAM不仅增加了事务处理功能，并且具备更强的故障恢复能力。

而 crash-safe 则基于 redo log 实现了这一功能。与 redolog 相似的日志文件还有 binlog，在 Server 引擎中被用于归档和备份数据存储。

最近提到了的是关于如何确保数据一致性的技术细节。具体来说，在数据库管理中，我们会将redo log和binlog归入同一个事务内，并采用双阶段提交机制来实现这一点。

以下是对MySQL整体架构的详细讲解。如需获取更多相关信息，请参考脚本之家的相关资源