数据库基础体系 · 第 14/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

MySQL 架构全景：连接层、优化器、执行器、存储引擎与日志

所属系列：数据库基础体系
所属模块：二、MySQL
标签：数据库、MySQL、架构、后端
适用范围：MySQL 8.4 的公开语义；涉及实现细节时会明确说明其性质。

MySQL 的一次查询并不是“数据库直接去读一行数据”。从客户端发出请求开始，语句会依次经过连接处理、SQL 解析、优化器、执行器和存储引擎；如果语句改变了数据，还会经过事务控制和多种日志系统。

可以先用一条主线概括：

客户端
  │ MySQL 协议
  ▼
连接层与会话状态
  │
  ▼
SQL 解析、预处理与权限检查
  │
  ▼
优化器：把 SQL 转换为执行计划
  │
  ▼
执行器：按计划驱动迭代器并处理结果
  │
  ▼
存储引擎接口
  │
  ▼
InnoDB 等存储引擎：索引、页、锁、MVCC、事务、日志

对于写事务，还存在另一条与数据页写入并行的路径：

事务修改
  ├─ InnoDB redo log：保证崩溃恢复
  ├─ undo log：支持回滚和 MVCC
  └─ binary log：记录逻辑变更，服务于复制和恢复

这几层并不等价：

优化器决定“怎么做”；
执行器负责“按计划做”；
存储引擎负责“数据如何组织、读取、修改和持久化”；
日志系统负责在不同层面记录状态变化，以支持恢复、复制、回滚或诊断。

一、先确定架构边界：Server 层与存储引擎层

MySQL 通常可以按两个大边界理解。

1. MySQL Server 层

Server 层处理与具体存储引擎相对独立的工作，包括：

接收客户端连接；
处理 MySQL 协议；
维护会话变量和事务上下文；
解析 SQL；
进行权限检查；
优化查询；
驱动执行；
管理部分日志，例如 binary log、慢查询日志和错误日志；
向客户端返回结果集或错误。

2. 存储引擎层

存储引擎负责表数据的具体存储和访问。例如：

InnoDB；
MyISAM；
MEMORY；
其他兼容的存储引擎。

存储引擎通常通过 Server 层提供的存储引擎接口参与执行。Server 层可以知道“我要读取某个表的某个索引”，但页如何组织、记录如何存放、锁如何实现，主要由引擎决定。

因此，下面两个概念必须区分：

CREATE TABLE t (
    id BIGINT PRIMARY KEY,
    value INT
) ENGINE = InnoDB;

CREATE TABLE、SQL 解析、权限检查属于 Server 层；
PRIMARY KEY 如何成为聚簇索引、数据页如何分裂、记录如何加锁，属于 InnoDB；
是否支持事务、外键和行级锁，取决于所选引擎及其能力。

同一个 SQL，在不同存储引擎上可能拥有不同的事务、锁和持久化语义。工程上不能只看 SQL 文本判断行为。

二、连接层：请求如何进入 MySQL

2.1 TCP 连接与 MySQL 协议

客户端首先通过 TCP 连接 MySQL 监听地址，也可以使用 Unix socket。连接建立后，服务端发送握手信息，客户端返回认证信息以及能力标志、字符集等参数。

一次典型的生命周期如下：

建立网络连接
  ↓
服务端握手
  ↓
客户端认证
  ↓
建立会话
  ↓
发送一个或多个命令
  ↓
返回结果集、影响行数或错误
  ↓
COM_QUIT 或连接断开

这里的“连接”与“会话”常被混用，但关注点不同：

连接是网络层的通信通道；
会话是服务端为该客户端维护的上下文。

会话中可能包含：

当前默认数据库；
会话级系统变量；
当前字符集和排序规则；
预处理语句；
临时表；
当前事务；
当前用户和权限上下文；
锁、游标或其他执行状态。

连接池复用的是连接及其会话状态，而不是一个完全无状态的 HTTP 请求。连接归还连接池前，如果没有正确清理事务、临时表、会话变量等状态，下一位使用者可能继承前一位使用者的环境。

2.2 一条连接通常对应一个串行命令流

传统 MySQL 协议下，同一连接上的命令通常按顺序处理。一个请求尚未完成时，客户端不能在同一连接上正常地交错发送另一个普通查询并期待两个结果集独立返回。

这也是连接池存在的原因之一：并发请求通常需要多个连接。

但“一个连接一次处理一个命令”不等于“服务器只有一个线程”。MySQL 可以同时处理多个客户端连接；每个连接的执行可能等待磁盘、锁或其他资源，而其他连接仍可运行。

2.3 会话变量会影响语义

例如：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

SELECT * FROM orders WHERE id = 10;

COMMIT;

事务隔离级别是会话或事务相关状态。若连接池中的连接被复用，必须确认设置何时生效、何时恢复。另一个常见状态是：

SET autocommit = 0;

在关闭自动提交后，后续语句可能持续处于同一事务中，直到显式 COMMIT 或 ROLLBACK。连接没有关闭，并不意味着事务已经结束。

三、从 SQL 到执行计划：解析、权限与优化器

严格来说，SQL 到达优化器之前还要经过词法分析、语法分析和语义处理。虽然标题重点是优化器，但不理解这些前置阶段，就无法解释“优化器到底优化什么”。

3.1 解析：把文本变成结构

例如：

SELECT u.name, o.total
FROM users AS u
JOIN orders AS o ON o.user_id = u.id
WHERE u.id = 42
  AND o.status = 'PAID'
ORDER BY o.created_at DESC
LIMIT 10;

解析器会识别：

SELECT 列表；
表和别名；
JOIN 以及连接条件；
WHERE 谓词；
排序表达式；
LIMIT；
字面量和参数。

解析结果可抽象成语法树或内部查询结构。这个阶段主要判断 SQL 是否符合语法以及各部分的结构关系，并不决定使用哪个索引。

例如，下面语句会在解析或语义检查阶段失败：

SELECT unknown_column
FROM users;

如果列不存在，优化器还没有机会为它选择索引。

3.2 预处理与权限检查

语义处理需要确认：

表是否存在；
列是否存在；
列类型是否允许参与运算；
名称是否有歧义；
当前用户是否具有所需权限；
视图、存储对象等引用是否有效。

权限检查并不等于“登录成功”。登录认证只是确认身份；执行某个对象上的 SELECT、UPDATE 或其他操作，还需要相应授权。

3.3 字面量与参数：解析阶段相似，优化阶段可能不同

直接拼接 SQL：

SELECT * FROM users WHERE id = 42;

和预处理语句：

PREPARE stmt FROM
    'SELECT * FROM users WHERE id = ?';

SET @id = 42;
EXECUTE stmt USING @id;

都需要经过语法和语义处理，但预处理语句使用参数标记，能够减少重复解析，并避免应用直接拼接用户输入导致的 SQL 注入风险。

参数化并不保证每次执行计划都完全相同。优化器仍可能根据表统计信息、参数类型和可用索引生成计划；实际行为还受到优化器实现和语句类型影响。不能把“使用预处理语句”理解成“永远固定执行计划”。

四、优化器：从逻辑结果到物理执行计划

4.1 优化器的输入和输出

优化器接收的是一个逻辑查询，输出的是一个或多个候选物理执行方案中被选中的计划。

逻辑语义可以写成：

过滤 users
与 orders 按 user_id 连接
过滤 orders.status
按 created_at 降序
取前 10 行

物理计划则要回答：

先访问哪张表？
对每张表使用全表扫描还是索引？
连接使用哪种算法和顺序？
条件在哪一层执行？
是否需要排序？
是否需要临时表？
是否可以只读取索引，不回表？
是否可以提前停止，例如利用 LIMIT？

优化器不改变 SQL 的逻辑结果；它改变的是满足同一语义的执行方式。

4.2 访问路径：全表扫描还是索引扫描

假设有表：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    KEY idx_user_status_created (user_id, status, created_at)
) ENGINE = InnoDB;

查询：

SELECT id, created_at, total
FROM orders
WHERE user_id = 42
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 10;

索引 (user_id, status, created_at) 的访问过程可以理解为：

在索引树中定位 user_id = 42；
在该范围内继续定位 status = 'PAID'；
按 created_at 的索引顺序读取候选记录；
由于只需要最近 10 行，可能读取少量索引项；
根据主键回到聚簇索引读取 total；
返回结果。

但索引并不自动优于全表扫描。优化器会估计：

总成本 ≈ 访问索引的成本
       + 读取数据页的成本
       + 回表成本
       + 排序成本
       + 连接成本

如果条件选择性很低，例如表中 95% 的行都是 status = 'PAID'，使用二级索引后仍需要读取大量记录，再进行大量回表，直接扫描聚簇索引可能更便宜。

选择性可以粗略理解为谓词筛选后剩余行数占总行数的比例：

s = \frac{\text{满足条件的行数}}{\text{总行数}}

id = 42 通常具有很低的 $s$ ，而 status = 'PAID' 可能具有较高的 $s$ 。选择性只是成本估计的一部分，不能单独决定计划。

4.3 联合索引与左前缀

对于索引：

(a, b, c)

通常可以有效支持：

a
a, b
a, b, c

但不能简单认为它等价于三个独立索引。查询只按 b 过滤时，通常不能像按 a 过滤那样直接定位连续的索引范围。

例如：

KEY idx_abc (a, b, c)

以下条件的性质不同：

WHERE a = 1 AND b = 2

可以先定位 a = 1，再在其范围内定位 b = 2。

WHERE b = 2

缺少最左列 a，通常无法直接利用该索引定位到一个很小的连续范围，可能退化为扫描更多索引项，甚至选择其他路径。

这不是“优化器不够聪明”，而是 B+Tree 中的排序顺序决定的：键首先按 a 排序，再按 b 排序。若不知道 a，所有不同 a 值下的 b = 2 记录会分散在树中。

4.4 连接顺序和连接算法

以：

SELECT u.id, o.id
FROM users AS u
JOIN orders AS o ON o.user_id = u.id
WHERE u.id < 100;

为例，常见的嵌套循环连接思路是：

读取一行 users
  ↓
使用 users.id 查找 orders.user_id
  ↓
输出匹配行
  ↓
读取下一行 users

如果外表 users 经过 u.id < 100 后只剩 99 行，且 orders.user_id 有索引，那么这种方案可能很便宜。

反例是：

外表过滤后有数百万行
内表没有可用连接索引

此时对每个外表行扫描内表会产生巨额成本。优化器可能改变连接顺序、使用其他连接算法，或选择先扫描更小的关系。具体可用算法和适用条件由 MySQL 版本、语句形态和优化器能力决定，不能把所有连接都简化成同一种实现。

连接顺序的核心是：中间结果越早变小，后续连接成本通常越低。但如果早期过滤条件没有索引，获取这些候选行本身也可能很昂贵。

4.5 排序、临时表和 `LIMIT`

查询：

SELECT id, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 10;

如果存在符合查询条件和排序顺序的索引，执行器可以按索引顺序读取并在得到 10 行后停止。

如果没有合适索引，则可能需要：

读取满足 WHERE 的大量行；
将排序键和行引用放入排序结构；
执行排序；
取前 10 行。

LIMIT 10 并不保证只读取 10 行。只有当执行路径能够按目标顺序产生结果并提前停止时，LIMIT 才能显著减少读取量。

4.6 统计信息决定优化器对数据分布的认识

优化器通常不会逐行统计每次查询的真实结果，而是依赖表和索引统计信息估算基数、选择性和成本。

如果统计信息过期，可能出现：

估计行数远小于实际行数；
选择了不合适的连接顺序；
使用了本应放弃的索引；
排序或临时表内存估计失真。

可以使用：

ANALYZE TABLE orders;

更新统计信息。其适用前提和影响取决于表规模、存储引擎以及 MySQL 版本实现。生产环境中应观察执行时间、锁影响和计划变化，而不是把它当作无风险操作。

五、用 `EXPLAIN` 观察优化器的决定

5.1 基本示例

EXPLAIN
SELECT id, created_at, total
FROM orders
WHERE user_id = 42
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 10;

输出中的重要列包括：

id：查询块编号；
select_type：查询块类型；
table：当前访问的表；
type：访问方式的粗略分类；
possible_keys：优化器认为可能使用的索引；
key：实际选择的索引；
key_len：使用的索引键长度；
ref：用于等值匹配的列或常量；
rows：预计读取的行数；
filtered：预计通过剩余条件的百分比；
Extra：额外执行信息，例如是否需要排序、临时表或覆盖索引。

possible_keys 不是“实际使用的索引”，真正选择的是 key。rows 也不是准确结果行数，而是估计的访问行数。

5.2 `EXPLAIN ANALYZE`：把估计与实际对照

在支持的语句和版本范围内，可以使用：

EXPLAIN ANALYZE
SELECT id, created_at, total
FROM orders
WHERE user_id = 42
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 10;

它会实际执行语句，并输出迭代器的估计与实际信息，例如：

-> Limit: 10 row(s)
    (actual time=... rows=10 loops=1)
    -> Index lookup on orders using ...
       (cost=... rows=...)
       (actual time=... rows=... loops=1)

这里有两个重要风险：

它不是纯观察命令，而是会执行查询；
如果语句包含修改操作或带有副作用，必须先确认语句行为和环境。

对于 SELECT，主要风险是实际读取、锁等待和负载；对于写语句，不应在生产环境随意执行带有修改效果的分析命令。

诊断时应重点比较：

估计 rows  vs 实际 rows
估计连接输入规模 vs 实际连接输入规模
估计成本 vs 实际耗时

如果估计与实际相差数量级，优先检查统计信息、数据分布、表达式可索引性和相关列之间的相关性，而不是立即增加索引。

六、执行器：按计划真正读取和处理数据

优化器输出的是计划，执行器负责将计划变成实际动作。

6.1 执行器的基本职责

执行器通常负责：

初始化执行计划；
调用访问路径读取行；
计算表达式；
应用过滤条件；
驱动连接；
维护聚合、排序、分组和限制状态；
向客户端发送结果；
处理错误、中断和取消；
在写操作中协调事务边界和引擎调用。

现代 MySQL 内部使用迭代器式执行模型来组织许多执行算子。可以抽象为：

LimitIterator
  └── SortIterator
        └── FilterIterator
              └── IndexScanIterator

执行器不断向上层请求“下一行”：

Limit 请求下一行
  → Sort 请求子节点行
    → Filter 请求子节点行
      → IndexScan 读取一行

上层算子拿到子节点的行后，决定保留、计算、排序或继续请求。

6.2 一条单表查询的执行步骤

考虑：

SELECT id, total
FROM orders
WHERE user_id = 42
  AND total > 100;

假设使用 user_id 索引，而 total 不在该索引中，则执行过程可能是：

执行器向索引访问算子请求第一条索引记录；
索引记录中包含主键 id；
对该主键进行回表，读取聚簇索引记录；
计算 total > 100；
条件成立，生成结果行；
条件不成立，丢弃；
重复直到索引范围结束；
将结果发送给客户端。

注意：WHERE 条件不一定都在索引层完成。能否在索引层过滤，取决于索引键、条件形式和优化器选择；无法在索引中完成的条件需要执行器读取记录后再判断。

6.3 回表、覆盖索引与执行代价

InnoDB 的二级索引叶子节点通常保存索引列和主键值，而不是完整行。查询二级索引之外的列时，需要根据主键回到聚簇索引读取完整记录，这就是回表。

例如：

KEY idx_user (user_id)

查询：

SELECT id
FROM orders
WHERE user_id = 42;

可能只需读取二级索引，因为二级索引记录中已经包含主键 id。

而查询：

SELECT total
FROM orders
WHERE user_id = 42;

如果 total 不在二级索引中，就需要回表。

这解释了覆盖索引的含义：查询所需列都能从某个索引中得到，不必读取聚簇索引记录。覆盖索引可能减少随机访问，但索引更宽会增加存储、写入和缓存压力，不能无条件扩大索引。

七、存储引擎：InnoDB 如何管理页、索引、事务和并发

7.1 InnoDB 的核心职责

InnoDB 不只是“把行写入磁盘”。它还负责：

表和索引的物理组织；
页和区等存储结构；
Buffer Pool 缓存；
聚簇索引和二级索引；
redo log；
undo log；
MVCC；
行锁和间隙相关锁；
崩溃恢复；
外键等引擎能力。

MySQL 8.4 中，InnoDB 是默认存储引擎。默认不表示所有表一定使用 InnoDB，仍应通过建表语句或元数据确认：

SHOW TABLE STATUS LIKE 'orders';

7.2 页是 InnoDB 读写的基本单位

InnoDB 将表和索引组织为页。常见默认页大小是 16 KiB，但页大小属于实例配置和版本能力范围，不应在不了解实例配置时硬编码假设。

一次查询读取一行，底层通常不是只从磁盘读取这一行，而是把包含该记录的页加载到 Buffer Pool。随后同页其他记录可以复用缓存。

因此：

SQL 行访问
  ≠
磁盘只读取一行

更接近：

逻辑行访问
  → 定位索引页
  → Buffer Pool 命中或从磁盘加载页
  → 在页内查找记录

7.3 聚簇索引与二级索引

InnoDB 表数据组织在聚簇索引中。通常：

显式主键是聚簇索引；
没有主键时，InnoDB 会选择合适的非空唯一索引；
如果不存在这样的索引，InnoDB 会生成隐藏的聚簇索引标识。

聚簇索引叶子节点包含完整行记录。二级索引叶子节点包含二级索引列以及主键值。

假设：

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    UNIQUE KEY uk_email (email)
) ENGINE = InnoDB;

查询：

SELECT name
FROM users
WHERE email = 'a@example.com';

可能经过：

uk_email 二级索引
  → 找到 email 和主键 id
  → 根据 id 访问聚簇索引
  → 读取 name

查询：

SELECT id
FROM users
WHERE email = 'a@example.com';

则可能只需读取唯一二级索引，因为 id 已作为主键值存在于二级索引记录中。

7.4 Buffer Pool：缓存页，而不是缓存 SQL 结果

Buffer Pool 是 InnoDB 的内存缓存，主要缓存：

数据页；
索引页；
undo 页；
自适应哈希索引相关结构等内部数据。

它与已删除的查询缓存不是一回事。MySQL 8.0 起移除了 Query Cache；MySQL 8.4 也不应把 Buffer Pool 理解为“按 SQL 缓存结果”。

一个读请求可能有以下两种路径：

索引页在 Buffer Pool
  → 直接访问页

索引页不在 Buffer Pool
  → 发起磁盘 I/O
  → 页加载到 Buffer Pool
  → 访问记录

Buffer Pool 命中只能说明页在内存中，不代表查询一定很快。仍可能存在大量回表、锁等待、排序、网络传输或 CPU 计算。

7.5 MVCC：快照读如何避免互相阻塞

InnoDB 使用多版本并发控制（MVCC）支持一致性读。一个版本可以抽象为：

当前记录版本
  → 由 undo 记录指向旧版本
  → 旧版本继续指向更旧版本

事务读取时，会根据自己的读视图判断某个版本是否可见。如果当前版本对该事务不可见，InnoDB 沿 undo 链查找较旧版本，直到找到可见版本或确认记录不可见。

这解释了一个常见现象：

事务 A 更新一行但未提交
事务 B 执行普通 SELECT

事务 B 通常可以读取符合其一致性视图的旧版本，而不是直接读取事务 A 的未提交值。

但以下语句属于锁定读，不应与普通快照读混为一谈：

SELECT *
FROM orders
WHERE id = 10
FOR UPDATE;

锁定读需要读取当前版本并申请锁，可能等待其他事务释放锁。

MVCC 不意味着“所有读都不阻塞”，也不意味着“写写冲突可以被消除”。它主要解决一致性读与并发修改之间的可见性问题。

7.6 隔离级别影响读视图和锁行为

常见隔离级别包括：

READ UNCOMMITTED；
READ COMMITTED；
REPEATABLE READ；
SERIALIZABLE。

在 InnoDB 中，默认隔离级别通常是 REPEATABLE READ，但生产系统应通过实际配置确认。

以两个事务为例：

-- 事务 A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;
-- 暂不提交

-- 事务 B
START TRANSACTION;
UPDATE accounts
SET balance = balance - 10
WHERE id = 1;

如果 A 只是普通 SELECT，B 的更新是否被 A 阻塞，和 A 使用的是快照读还是锁定读有关。若 A 执行：

SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;

则 A 会申请排他性锁兼容性要求更高的锁，B 可能等待。

不能只看到“同一行被访问”就断定一定阻塞，也不能只看到“使用了 MVCC”就断定完全不会阻塞。

八、事务：从逻辑操作到持久化提交

8.1 事务的基本状态

一个事务可以抽象为：

未开始
  ↓
活动中
  ├─ COMMIT → 已提交
  └─ ROLLBACK → 已回滚

隐式事务边界会受到 autocommit、DDL、显式事务语句以及具体语句类型影响。最容易控制的方式是显式写出边界：

START TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

如果第二条更新失败，应用必须根据错误处理策略执行：

ROLLBACK;

否则可能留下未结束事务，占用锁和维护旧版本。

8.2 原子性、隔离性和持久性分别由什么支撑

以 InnoDB 为例：

原子性：事务提交前的修改可以通过 undo 回滚；事务失败时不应只保留部分逻辑修改；
隔离性：锁和 MVCC 控制并发事务的可见性与冲突；
持久性：提交相关修改通过 redo 等机制保证崩溃后可以恢复；
一致性：是约束、事务语义、应用逻辑和引擎机制共同形成的结果，不是某一个日志单独保证的。

若一张表使用不支持事务的引擎，把多个写语句放进 START TRANSACTION 并不会自动获得 InnoDB 的原子提交语义。

九、日志体系：redo、undo、binlog 与诊断日志

“MySQL 日志”不是一种文件，而是一组服务不同目标的记录。

日志	主要记录对象	主要用途
redo log	InnoDB 页的物理/低层变化信息	崩溃恢复、持久化
undo log	事务修改前的旧版本或回滚信息	回滚、MVCC
binary log	Server 层产生的逻辑事件	复制、时间点恢复
relay log	副本接收的 binary log 内容	副本重放
slow query log	慢查询及相关信息	性能诊断
general query log	连接和命令活动	调试与审计式观察
error log	服务错误、启动、关闭和重要状态	故障诊断

它们记录的内容和生命周期不同，不能互相替代。

9.1 redo log：崩溃恢复的基础

InnoDB 通常先修改 Buffer Pool 中的页，而不是每次修改都立即把数据页同步写回表空间。为防止内存页尚未落盘时发生宕机，InnoDB 记录 redo log。

核心原则是 WAL（Write-Ahead Logging，预写日志）：

先确保对应的 redo 记录持久化
再允许脏数据页晚些时候写回

故障前：

Buffer Pool 中的数据页：已修改但未落盘
redo log：记录了如何重做修改

重启恢复时：

读取 redo log
  → 找出需要重做的修改
  → 将修改重新应用到数据页
  → 恢复到一致状态

redo log 主要解决“已提交修改在崩溃后不能丢失”的问题；它不是给用户直接查询的业务变更日志，也不是用于复制的 binary log。

9.2 undo log：回滚和旧版本

执行更新时，InnoDB 不仅生成新版本，也要保留足够信息：

旧值
  → 记录到 undo
新值
  → 写入当前记录

事务回滚时，undo 可用于撤销修改。其他事务进行一致性读时，也可能通过 undo 找到对自己可见的旧版本。

因此，长事务会带来两个后果：

旧版本不能及时清理；
undo 链和相关历史可能持续增长。

一个只执行过一次查询、但一直不提交的事务，也可能因为持有较老的读视图而影响历史版本清理。诊断长事务时，应查看事务状态，而不是只看当前是否正在执行 SQL。

9.3 binary log：复制和时间点恢复

binary log 位于 Server 层，记录会改变数据库状态的事件。它主要服务于：

主从或源副本复制；
基于日志的增量恢复；
按时间点恢复；
变更审计的一部分。

binary log 的事件格式可配置为：

STATEMENT：记录 SQL 语句；
ROW：记录行变化；
MIXED：根据情况混合使用。

这些模式各有边界。ROW 通常更直接地表达实际行变化，但日志量可能不同；STATEMENT 可能受非确定性函数、数据分布和环境差异影响。生产环境选择时必须结合复制一致性要求、日志量和恢复流程验证。

查看 binary log 需要使用专门工具，例如：

mysqlbinlog --base64-output=DECODE-ROWS -vv binlog.000001

命令适用于能够访问 binlog 文件的环境；远程或托管环境可能需要使用服务商提供的导出方式。不要直接把二进制文件当文本解析。

9.4 redo log 与 binlog 为什么都需要

假设一个事务更新了 InnoDB 表：

事务修改
  ↓
InnoDB 生成 undo 和 redo
  ↓
Server 生成 binlog 事件
  ↓
事务提交

只依赖 redo：

InnoDB 自己可以恢复页；
但复制线程和时间点恢复无法以标准方式读取 InnoDB 页级日志。

只依赖 binlog：

可以知道逻辑上执行了什么变更；
但数据页可能已经部分写入，崩溃时不能仅靠逻辑日志高效恢复存储引擎内部状态。

两者目标不同，因此需要协调提交顺序。对同时写入 InnoDB 和 binary log 的事务，MySQL 使用两阶段提交思想协调引擎日志与 Server 层 binlog，使“事务已提交”这一状态尽量保持一致。

实现细节涉及 prepare、binlog 写入和提交阶段；不应把它简化成“先写 binlog 再写 redo”或反过来，因为实际持久化行为还受到刷盘策略、组提交和配置影响。

9.5 relay log：副本的中间日志

复制副本通常由接收线程从源端读取 binary log，并写入本地 relay log；SQL 线程或复制应用线程再读取 relay log 并应用变更。

源库 binary log
  ↓ 网络传输
副本 relay log
  ↓
副本应用
  ↓
副本数据文件和自身日志

relay log 使网络接收和本地应用可以解耦。副本延迟可能来自：

网络传输；
relay log 写入；
应用线程执行速度；
锁冲突；
大事务；
副本 I/O 或 CPU 限制。

因此“源库已经写入 binlog”不等于“副本已经可读到对应数据”。

十、写事务的完整数据流

下面用 InnoDB 更新语句串起各层：

START TRANSACTION;

UPDATE orders
SET status = 'PAID'
WHERE id = 1001;

COMMIT;

步骤 1：连接层接收命令

客户端通过已认证的连接发送 START TRANSACTION。Server 层建立或更新当前会话的事务状态。

步骤 2：解析和优化

执行 UPDATE 时，Server 层：

解析表名、列名和条件；
检查权限；
选择访问路径；
生成更新执行计划。

若 id 是主键，优化器通常可以使用主键索引定位目标行，但最终仍由 InnoDB 负责记录读取和加锁。

步骤 3：执行器驱动 InnoDB

执行器请求存储引擎：

按 id=1001 定位记录
  ↓
申请所需锁
  ↓
读取当前记录
  ↓
生成新值 status='PAID'

如果另一个事务已经持有冲突锁，当前语句可能等待；等待超时或死锁检测也可能导致错误。

步骤 4：InnoDB 修改内存页并记录 undo、redo

InnoDB 可能：

将旧 status 记录到 undo
修改 Buffer Pool 中的数据页
产生 redo log
标记数据页为脏页

此时数据页未必已经写回表空间文件。

步骤 5：生成 binary log

Server 层根据配置和日志格式记录事务变更事件。若启用复制，副本未来会依据这些事件重放。

步骤 6：提交

COMMIT 需要协调：

InnoDB 事务状态；
redo 的持久化要求；
binary log 的写入和持久化要求；
锁释放；
客户端成功响应。

成功返回 COMMIT 的持久性强度受相关刷盘配置影响。不能仅凭“SQL 返回成功”推导出每个日志和数据页都已经以相同方式同步落盘。

十一、故障路径：崩溃时会发生什么

11.1 事务提交前崩溃

假设事务修改已进入 Buffer Pool，但还未提交：

数据页：可能已修改
redo：可能已有记录
事务状态：未提交

重启时，InnoDB 会进行崩溃恢复：

根据 redo 重做必要的页修改；
识别未完成的事务；
对未提交事务执行回滚或清理；
让引擎回到一致状态。

重做与回滚解决的是不同方向：

redo 恢复“应该保留的低层修改”；
undo 撤销“未提交事务不应对外可见的修改”。

11.2 binlog 与引擎状态不一致的风险

如果 Server 层认为事务已记录到 binlog，而 InnoDB 状态未按同一事务完成，复制和恢复就可能产生分歧。因此 MySQL 需要协调两类日志的提交过程。

这也是为什么只测试“正常重启”不够。真正的持久性验证还需要考虑：

进程崩溃；
主机断电；
存储设备缓存行为；
binlog 和 redo 的刷盘策略；
复制恢复；
时间点恢复链路。

11.3 备份不能只复制数据文件

在线复制 InnoDB 数据文件并不自动构成一致备份。因为数据页可能处于不同时间点，未完成事务、redo、undo 和 binlog 也可能需要协调。

可恢复备份通常必须明确：

备份时点；
是否一致；
是否包含或关联 binlog；
如何恢复到某一时间点；
如何验证恢复结果。

“有备份文件”不等于“已经验证可恢复”。

十二、慢查询日志、通用日志与错误日志

12.1 慢查询日志

慢查询日志用于记录超过阈值的查询，通常可用于定位：

执行时间过长；
锁等待时间过长；
未使用索引或扫描行数过大；
排序、临时表和回表代价过高。

慢查询日志记录的是诊断信息，不是执行计划本身。要理解原因，通常还要结合：

EXPLAIN ...
EXPLAIN ANALYZE ...
SHOW WARNINGS;

以及运行时状态、锁等待和表统计信息。

12.2 通用查询日志

通用日志记录连接和命令活动，适合短时间调试连接问题、确认客户端到底发送了什么。

它可能产生大量 I/O 和日志数据，生产环境长期开启需要谨慎。它也不应被当作高性能审计系统或完整业务审计记录。

12.3 错误日志

错误日志用于观察：

启动和关闭；
配置错误；
崩溃；
InnoDB 恢复；
复制或组复制相关错误；
警告和重要运行状态。

发生“连接失败”时，错误日志可能比客户端返回的通用错误更接近根因，但仍需结合网络、认证、权限和资源情况。

十三、一个可验证的端到端示例

以下示例明确使用：

MySQL 8.4；
InnoDB；
显式事务；
单实例，不涉及复制；
普通 SELECT 与锁定读分别验证。

13.1 准备数据

DROP TABLE IF EXISTS accounts;

CREATE TABLE accounts (
    id BIGINT PRIMARY KEY,
    owner VARCHAR(50) NOT NULL,
    balance DECIMAL(12, 2) NOT NULL
) ENGINE = InnoDB;

INSERT INTO accounts (id, owner, balance) VALUES
(1, 'Alice', 1000.00),
(2, 'Bob',    500.00);

建表后：

SHOW CREATE TABLE accounts;

预期可以看到：

ENGINE=InnoDB
PRIMARY KEY (`id`)

13.2 验证普通快照读

连接 A：

START TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

此时连接 A 未提交。

连接 B：

START TRANSACTION;

SELECT balance
FROM accounts
WHERE id = 1;

在 InnoDB 的一致性读语义下，连接 B 通常读取事务视图可见的已提交版本，而不是连接 A 尚未提交的 900.00。

然后连接 A：

ROLLBACK;

连接 B 再次查询时，仍应看到已提交的 1000.00。

这里的关键不是“数据库复制了一行旧数据”，而是连接 B 通过当前记录和 undo 版本链构造了对自己可见的版本。

13.3 验证锁定读

连接 A：

START TRANSACTION;

SELECT balance
FROM accounts
WHERE id = 1
FOR UPDATE;

连接 A 持有该记录的锁。

连接 B：

START TRANSACTION;

UPDATE accounts
SET balance = balance - 50
WHERE id = 1;

此时连接 B 可能等待连接 A 释放冲突锁。可以在另一个管理连接观察：

SHOW PROCESSLIST;

或者使用 InnoDB 事务与锁相关的监控视图查看等待关系。具体可用视图和字段应以当前实例的数据字典为准。

连接 A：

COMMIT;

连接 B 才可能继续执行。若等待超过配置的锁等待超时，或检测到死锁，连接 B 会收到错误，应用必须回滚并决定是否重试。

13.4 观察执行计划

EXPLAIN
SELECT id, owner, balance
FROM accounts
WHERE id = 1;

由于 id 是主键，通常会看到基于主键的点查访问路径。若要观察实际执行，使用：

EXPLAIN ANALYZE
SELECT id, owner, balance
FROM accounts
WHERE id = 1;

该命令会真正执行查询，因此不应把它当成只读取元数据的无副作用操作。

十四、常见误解与失败表现

14.1 “用了索引，查询就一定快”

错误原因包括：

索引选择性太低；
回表次数过多；
返回结果本身很多；
排序仍然无法利用索引；
统计信息过期；
查询被锁等待拖慢。

诊断时要同时看：

访问方式
预计和实际行数
回表规模
排序/临时表
锁等待
磁盘与 Buffer Pool 命中

14.2 “`EXPLAIN` 里有 `key`，所以实际只读了很少数据”

key 只表示选择了某个索引。仍需结合：

rows；
filtered；
Extra；
EXPLAIN ANALYZE 的实际行数；
结果集大小和网络传输。

14.3 “Buffer Pool 缓存了查询结果”

Buffer Pool 缓存的是 InnoDB 页，不是按 SQL 文本缓存结果。相同 SQL 的结果可能因事务快照、权限、会话变量和数据变化而不同。

14.4 “有 redo log 就不需要 binlog”

redo 主要服务 InnoDB 崩溃恢复；binlog 主要服务复制和逻辑恢复。二者不是同一种日志，也不能相互替代。

14.5 “有 binlog 就能恢复所有历史状态”

binlog 恢复通常需要：

一个一致的全量备份；
从备份时点开始连续可用的 binlog；
正确的日志位置或时间范围；
与业务一致的恢复验证。

缺少基线备份或日志链断裂时，仅有部分 binlog 不能凭空构造完整数据库。

14.6 “事务提交了，所以数据页已经写回表文件”

事务提交成功的持久性依赖日志刷盘和相关配置。数据页可以稍后由后台线程写回；WAL 的意义正是允许数据页延迟落盘，同时依靠 redo 保证恢复能力。

14.7 “普通 SELECT 永远不会阻塞”

普通一致性读通常不需要像锁定读那样申请记录锁，但它仍可能受到：

元数据锁；
表结构变更；
资源争用；
I/O；
CPU；
连接和线程资源；
特定执行路径中的锁或内部等待

影响。是否阻塞必须看具体语句和运行状态。

十五、诊断时如何沿着架构定位问题

遇到一条查询慢、写入失败或提交异常时，可以按数据流反向检查。

15.1 先判断问题在哪一段

客户端到服务端
  → 连接、认证、网络、连接池

服务端解析
  → 语法、对象、权限、字符集

优化器
  → 访问路径、连接顺序、统计信息

执行器
  → 实际扫描、过滤、排序、聚合、结果传输

存储引擎
  → 页 I/O、Buffer Pool、锁、MVCC、事务

日志和恢复
  → redo、undo、binlog、刷盘、复制

15.2 查询慢的最小诊断路径

对读查询，可以先执行：

EXPLAIN
SELECT ...;

再根据实际支持情况执行：

EXPLAIN ANALYZE
SELECT ...;

随后检查：

预计行数和实际行数是否严重偏离；
是否发生全表扫描；
是否出现大量回表；
是否需要排序或临时表；
是否被锁等待；
统计信息是否合理。

只有在确定问题属于访问路径后，才适合讨论新增或调整索引。若根因是锁等待，增加索引可能改变锁持有范围，却不一定解决事务设计问题；若根因是返回数据量过大，索引也无法消除网络传输成本。

15.3 写入失败的诊断路径

写事务报错时，应区分：

语法或权限错误；
唯一键、外键、检查约束错误；
锁等待超时；
死锁；
存储空间不足；
redo、binlog 或磁盘相关错误；
事务边界和连接复用错误。

发生死锁时，正确策略通常不是盲目延长超时，而是：

记录错误和涉及的事务；
回滚失败事务；
检查不同代码路径获取锁的顺序；
缩短事务；
在确认操作幂等或可安全重放后进行有限重试。

十六、把整套架构压缩成一个判断框架

面对任意一条 SQL，可以依次问：

1. 谁在接收它？

连接层是否正常？当前会话使用什么数据库、字符集、事务模式和隔离级别？

2. 它在语义上是否合法？

表、列、类型、权限和对象引用是否正确？

3. 优化器打算怎么做？

使用哪个索引？先访问哪张表？是否排序？预计读取多少行？

4. 执行器实际上做了多少工作？

实际读取多少行？过滤掉多少行？回表多少次？是否等待锁？

5. 存储引擎如何实现这些工作？

数据位于哪些页？是否命中 Buffer Pool？是否需要聚簇索引访问？是否涉及 MVCC 或锁？

6. 如果它修改数据，如何保证故障后的状态？

undo 如何回滚和提供旧版本？redo 如何恢复页？binlog 如何支持复制和逻辑恢复？提交时两者如何协调？

这套框架也解释了为什么“SQL 看起来很短”并不代表执行简单：一条 UPDATE 可能同时涉及索引定位、锁竞争、页修改、undo、redo、binlog 和提交等待；一条 SELECT 可能同时涉及优化器估算、索引扫描、回表、Buffer Pool、MVCC、排序和网络传输。

MySQL 的架构价值，正在于把这些职责分层：Server 层理解 SQL 和协议，优化器选择路径，执行器驱动计划，存储引擎管理数据与并发，日志系统承接恢复、复制和诊断。排查问题时沿着这些边界观察，通常比只盯着 SQL 文本或单个“是否走索引”结论更可靠。

系列导航与关联阅读

官方资料

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。

MySQL 架构全景：连接层、优化器、执行器、存储引擎与日志

一、先确定架构边界：Server 层与存储引擎层

1. MySQL Server 层

2. 存储引擎层

二、连接层：请求如何进入 MySQL

2.1 TCP 连接与 MySQL 协议

2.2 一条连接通常对应一个串行命令流

2.3 会话变量会影响语义

三、从 SQL 到执行计划：解析、权限与优化器

3.1 解析：把文本变成结构

3.2 预处理与权限检查

3.3 字面量与参数：解析阶段相似，优化阶段可能不同

四、优化器：从逻辑结果到物理执行计划

4.1 优化器的输入和输出

4.2 访问路径：全表扫描还是索引扫描

4.3 联合索引与左前缀

4.4 连接顺序和连接算法

4.5 排序、临时表和 LIMIT

4.6 统计信息决定优化器对数据分布的认识

五、用 EXPLAIN 观察优化器的决定

5.1 基本示例

5.2 EXPLAIN ANALYZE：把估计与实际对照

六、执行器：按计划真正读取和处理数据

6.1 执行器的基本职责

6.2 一条单表查询的执行步骤

6.3 回表、覆盖索引与执行代价

七、存储引擎：InnoDB 如何管理页、索引、事务和并发

7.1 InnoDB 的核心职责

7.2 页是 InnoDB 读写的基本单位

7.3 聚簇索引与二级索引

7.4 Buffer Pool：缓存页，而不是缓存 SQL 结果

7.5 MVCC：快照读如何避免互相阻塞

7.6 隔离级别影响读视图和锁行为

八、事务：从逻辑操作到持久化提交

8.1 事务的基本状态

8.2 原子性、隔离性和持久性分别由什么支撑

九、日志体系：redo、undo、binlog 与诊断日志

9.1 redo log：崩溃恢复的基础

9.2 undo log：回滚和旧版本

9.3 binary log：复制和时间点恢复

9.4 redo log 与 binlog 为什么都需要

9.5 relay log：副本的中间日志

十、写事务的完整数据流

步骤 1：连接层接收命令

步骤 2：解析和优化

步骤 3：执行器驱动 InnoDB

步骤 4：InnoDB 修改内存页并记录 undo、redo

步骤 5：生成 binary log

步骤 6：提交

十一、故障路径：崩溃时会发生什么

11.1 事务提交前崩溃

11.2 binlog 与引擎状态不一致的风险

11.3 备份不能只复制数据文件

十二、慢查询日志、通用日志与错误日志

12.1 慢查询日志

12.2 通用查询日志

12.3 错误日志

十三、一个可验证的端到端示例

13.1 准备数据

13.2 验证普通快照读

13.3 验证锁定读

13.4 观察执行计划

十四、常见误解与失败表现

14.1 “用了索引，查询就一定快”

14.2 “EXPLAIN 里有 key，所以实际只读了很少数据”

14.3 “Buffer Pool 缓存了查询结果”

14.4 “有 redo log 就不需要 binlog”

14.5 “有 binlog 就能恢复所有历史状态”

14.6 “事务提交了，所以数据页已经写回表文件”

14.7 “普通 SELECT 永远不会阻塞”

十五、诊断时如何沿着架构定位问题

15.1 先判断问题在哪一段

15.2 查询慢的最小诊断路径

15.3 写入失败的诊断路径

十六、把整套架构压缩成一个判断框架

1. 谁在接收它？

2. 它在语义上是否合法？

3. 优化器打算怎么做？

4. 执行器实际上做了多少工作？

5. 存储引擎如何实现这些工作？

4.5 排序、临时表和 `LIMIT`

五、用 `EXPLAIN` 观察优化器的决定

5.2 `EXPLAIN ANALYZE`：把估计与实际对照

14.2 “`EXPLAIN` 里有 `key`，所以实际只读了很少数据”