MySQL 整体架构一览

MySQL 在整体架构上分为 Server 层和存储引擎层。其中 Server 层，包括连接器、查询缓存、分析器、优化器、执行器等，存储过程、触发器、视图和内置函数都在这层实现。数据引擎层负责数据的存储和提取，如 InnoDB、MyISAM、Memory 等引擎。在客户端连接到 Server 层后，Server 会调用数据引擎提供的接口，进行数据的变更。

连接器#

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

通过 show processlist; 来查询连接的状态。在用户建立连接后，即使管理员改变连接用户的权限，也不会影响到已连接的用户。默认连接时长为 8 小时，超过时间后将会被断开。

简单说下长连接：

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

劣势：在 MySQL 执行时，使用的内存被连接对象管理，由于长时间没有被释放，会导致系统内存溢出，被系统kill. 所以需要定期断开长连接，或执行大查询后，断开连接。MySQL 5.7 后，可以通过 mysql_rest_connection 初始化连接资源，不需要重连或者做权限验证。

查询缓存#

当接受到查询请求时，会现在查询缓存中查询（key/value保存），是否执行过。没有的话，再走正常的执行流程。

但在实际情况下，查询缓存一般没有必要设置。因为在查询涉及到的表被更新时，缓存就会被清空。所以适用于静态表。在 MySQL8.0 后，查询缓存被废除。

分析器#

词法分析：

如识别 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 层和数据引擎层，而每层也对应了自己的日志文件。如果选用的是 InnoDB 引擎，对应的是 redo log 文件。Server 层则对应了 binlog 文件。至于为什么存在了两种日志系统，咱们往下看。

redo log#

redo log 是 InnoDB 特有日志，为什么要引入 redo log 呢，想象这样一个场景，MySQL 为了保证持久性是需要把数据写入磁盘文件的。我们知道，在写入磁盘时，会进行文件的 IO，查找操作，如果每次更新操作都这样的话，整体的效率就会特别低，根本没法使用。

既然直接写入磁盘不行，解决方法就是先写进内存，在系统空闲时再更新到磁盘就可以了。但光更新内存不行，假如系统出现异常宕机和重启，内存中没有被写入磁盘的数据就会被丢掉，数据的一致性就出现问题了。这时 redo log 就发挥了作用，在更新操作发生时，InnoDb 会先写入 redo log 日志（记录了数据发生了怎么样的改变），同时更新内存，最后在适当的时间再写入磁盘，一般是找系统空闲的时间做。先写日志，在写磁盘的操作，就是常说到的 WAL （Write-Ahead- Logging）技术。

redo log 的出现，除了在效率上有了很大的改善，还保证了 MySQL 具有了 crash-safe 的能力，在发生异常情况下，不会丢失数据。

在具体实现上 redo log 的大小是固定的，可配置一组为 4 个文件，每个文件 1GB，更新时对四个文件进行循环写入。

write pos 记录当前写入的位置，写完就后移，当第写入第 4 个文件的末尾时，从第 0 号位置重新写入。

check point 表示当前可以擦除的位置，当数据更新到磁盘时，check point 就向后移动。

write pos 和 check point 之间的位置，就是可以记录更新操作的空间。当 write pos 追上 check point ，不在能执行新的操作，先让 check point 去写入一些数据。

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

binlog#

binlog 则是 Server 层的日志，主要用于归档，在备份，主备同步，恢复数据时发挥作用，常见的日志格式有 row, mixed, statement 三种。具体的使用方法可以参见 Binlog 恢复日志这篇。

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

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

1. 所有者不同，binlog 是 Server 层，所有引擎都可使用。redo log 是 InnoDB 特有的。
2. 类型不同，binlog 是逻辑日志，记录的是语句的原始逻辑（比 statement）。redo log 是物理日志，记录某个数据页被做了怎样的修改。
3. 数据写入的方式不同，binog 日志会一直追加，而 redo log 是循环写入。
4. 功能不同，binlog 用于归档，而 redo log 用于保证 crash-safe.

两阶段提交#

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

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

1. 执行器通过 InooDB 引擎去 ID 所在行，ID 为主键。引擎通过树搜索找到该行，如果该行所在数据页在内存中，返回给执行器。否则先从磁盘读入内存，然后再返回。

2. 执行器拿到引擎给的数据，将 C 值加 1，等到新的一行，然后通过引擎接口重新写入新数据。

3. 引擎将该行更新到内存中，同时将该更新操作记录到 redo log 中，并更改 redo log 的状态为 prepare 状态。然后告知执行器，在合适的时间提交事务。

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

5. 执行器调用引擎到的提交事务接口，将刚刚写入的 redo log 改成 commit 状态，更新完成。

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

在更新内存后，将写入 redo log 拆分了成两个步骤：prepare 和 commit，就是常说的两阶段提交。用于保证当有意外情况发生时，数据的一致性。

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

1. 先写 redo log 后写 binlog. 假设在写入 redo log 后，MySQL 发生异常重启，此时 binlog 没有写入。在重启后，由于 redolog 已经写入，此时数据库的内容是没有问题的。但此时，如果想要拿 binlog 进行备份或恢复，发现会少了最后一条的更新逻辑，导致数据不一致。

2. 先写 binlog 后写 redo log. binlog 写入后，MySQL 异常重启，redo log 没有写入。此时重启后，发现 redo log 没有成功写入，认为这个事务无效，而此时 binlog 却多了一条更新语句，拿去恢复后自然数据也是不一致的。

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

1. 在写 redo log prepare 阶段奔溃，时刻 A 的位置。重启后，发现 redo log 没写入，回滚此次事务。
2. 如果在写 binlog 时奔溃，重启后，发现 binlog 未被写入，回滚操作。
3. binlog 写完，但在提交 redo log 的 commit 状态时发生 crash.
   1. 如果 redo log 中事务完整，有了 commit 标识，直接提交。
   2. 如果 redo log 中只有完整的 prepare, 判断对应 binlog 是否完整。
      1. 完整，提交事务
      2. 不完整，回滚事务。

如何判断 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 的 redo log, 拿 XID 去 binlog 找对应的事务做判断。

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

1. 对于正常运行的 instance 来说，内存中页被修改后，和磁盘的数据页不一致，称为脏页。而落盘的过程，是把内存中的数据页写入磁盘。
2. 对于 crash 场景，InnoDB 判断一个数据页是否丢失了更新，会将其读到内存，然后让 redo log 更新内存内容。更新完成后，内存页就变成脏页，然后回到第一种情况的状态。

redo log buffer 和 redo log 的关系？

在一个事务的更新过程中，存在多个 SQL 语句，所以是要写多次日志的。
但在写的过程中，生产的日志要先保存起来，但在 commit 前，不能直接写到 redo log 中。
所以通过内存中 redo log buffer 先存 redo log 的日志。在 commit 时，将 buffer 中的内容写入 redo log.

总结#

在文章开始部分，说明了 MySQL 的整体架构分为 Server 层和引擎层，并简要说明了一条语句的执行过程。接着 MySQL 在 5.5 后选用 InnoDB 作为默认的引擎，就是因为比原生的 MyISAM 多了事务以及 crash-safe 的能力。

而 crash-safe 就是由 redo log 实现的。与 redo log 类似的日志文件还有 binlog，是 Server 引擎的日志，用于归档和备份数据。

最后提到了，为了保证数据的一致性，将 redo log 和 binlog 放入相同的事务中，也就是常提到的两阶段提交操作。

参考#

题外话：最近在系统的学习 MySQL，推荐一个比较好的学习材料就是<<丁奇老师的 MySQL 45 讲>>，链接已经附在文章末尾。

文章中很多知识点就是从中学来，加入自己的理解并整理的。

大家在购买后，强烈推荐读一读评论区的内容，价值非常高，不少同学问出了自己在思考时的一些困惑。

丁奇 MySQL 45 讲链接