一文搞懂 MySQL 核心架构：Server 层与存储引擎全拆解

殷紫川
0 阅读31分钟

很多开发者日常使用MySQL，只停留在CRUD层面，遇到慢查询、事务不生效、死锁、主从数据不一致等问题时，往往无从下手。本质原因是没有真正理解MySQL的核心架构，不清楚一条SQL从客户端发出到最终执行完成，到底经历了哪些环节。MySQL的逻辑架构采用分层设计，核心分为Server层和存储引擎层两大模块，清晰的边界划分让MySQL具备了极强的扩展性，也是其能成为最流行关系型数据库的核心原因之一。

一、MySQL整体逻辑架构总览

MySQL的逻辑架构严格分为两层，核心职责边界清晰，插件式的设计让不同存储引擎可以无缝接入，共用一套Server层逻辑。

  • Server层：负责处理客户端连接、SQL语法解析、语义校验、SQL优化、执行计划生成、内置函数调用、跨存储引擎的通用逻辑处理，所有存储引擎共用一套Server层逻辑。
  • 存储引擎层：负责数据的存储与提取，基于插件式架构设计，实现了Server层定义的存储引擎API，不同存储引擎具备不同的功能特性，核心负责事务、锁、MVCC、索引、崩溃恢复等与数据存储强相关的逻辑。

Server层不关心数据的底层存储方式，仅通过统一的API与存储引擎交互，这也是MySQL可以灵活切换存储引擎的核心原因。

二、Server层核心组件全拆解

Server层是MySQL的核心控制中枢，一条SQL的所有通用逻辑都在这一层完成，我们按SQL的执行顺序，逐个拆解每个核心组件的底层设计与工作逻辑。

2.1 连接器：MySQL的门户

连接器是客户端访问MySQL的第一道关卡，核心负责客户端的连接管理、身份认证、权限校验、会话管理。

客户端与MySQL服务端的连接本质是TCP连接，经过三次握手建立连接后，连接器会按顺序完成以下工作：

  1. 身份认证：校验客户端传入的用户名、密码、主机地址，验证不通过直接返回Access denied for user错误，断开连接。
  2. 权限获取：认证通过后，连接器会从mysql.user等系统表中读取该用户的所有权限，当前连接的所有权限判断，都基于此时读取的权限数据。核心特性：连接建立完成后，即使管理员修改了该用户的权限，也不会影响当前已建立的连接，只有新创建的连接才会加载新的权限配置。
  3. 连接管理：连接器负责维护客户端的连接状态，管理连接的生命周期。
-- 查看当前连接的id
SELECT CONNECTION_ID();
-- 查看所有连接的详细信息
SHOW FULL PROCESSLIST;
-- 查看空闲连接超时时间，单位秒
SHOW VARIABLES LIKE 'wait_timeout';
-- 修改当前会话的空闲超时时间为3600秒
SET SESSION wait_timeout = 3600;

MySQL的wait_timeout参数控制空闲连接的最大等待时间，默认值为8小时，超过这个时间的空闲连接，连接器会自动断开。而长连接虽然可以减少TCP三次握手/挥手的开销，但会存在内存累积问题：MySQL在执行过程中使用的内存，会管理在连接对象中，这些资源只有在连接断开时才会释放，长连接持续运行会导致内存占用持续上涨，最终被系统OOM杀掉。

对应的解决方案：

  1. 定期断开空闲时间过长的长连接，释放内存资源。
  2. MySQL 5.7及以上版本，可通过mysql_reset_connection()函数重置连接资源，无需断开重连即可释放内存。
  3. 合理配置wait_timeout参数，避免空闲连接长期占用资源。

2.2 查询缓存：已正式下线的模块

MySQL 8.0.4版本正式移除了查询缓存模块，此前的版本中，查询缓存会将SQL语句与查询结果做KV缓存，相同SQL可直接返回缓存结果。但该模块存在致命缺陷：只要对一个表执行更新操作，这个表的所有查询缓存都会被清空，写多读少的场景下命中率极低，反而会带来额外的缓存维护开销，因此官方直接移除，日常开发无需再关注。

2.3 解析器：SQL的语法拆解与校验

解析器的核心工作分为词法分析和语法分析两步，负责将SQL字符串转换为MySQL可识别的结构化数据。

  • 词法分析：将客户端传入的SQL字符串，拆解成一个个Token，比如将select * from t1 where id = 1拆解为关键字select、标识符*、关键字from、表名t1、关键字where、列名id、运算符=、常量1，识别每个Token的类型与含义。
  • 语法分析：根据MySQL官方定义的SQL语法规则，对词法分析得到的Token序列进行语法校验，判断SQL是否符合语法规范。如果SQL语法错误，会抛出常见的You have an error in your SQL syntax错误。
-- 错误SQL：把from写成了form，语法错误，解析器会直接报错
SELECT * FORM t1 WHERE id = 1;

需要明确的是，解析器只关心SQL的语法是否正确，不关心表、列是否真实存在，哪怕表名不存在，只要语法正确，解析器就会通过。

2.4 预处理器：SQL的语义校验与权限验证

解析器只保证SQL语法正确，而SQL的语义是否合法、权限是否足够，由预处理器负责完成，这也是很多开发者容易混淆的环节。

预处理器的核心工作：

  1. 语义校验：验证SQL中涉及的表、视图、列是否真实存在，验证表别名、列别名是否存在歧义。如果SQL中引用了不存在的列，预处理器会抛出Unknown column 'xxx' in 'field list'错误。
  2. 权限校验：对当前用户执行的SQL做表级、列级的权限校验，如果用户没有对应操作权限，会直接抛出权限拒绝错误，无需走到后续执行环节。
-- 先创建测试表
CREATE TABLE IF NOT EXISTS t1 (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(32) NOT NULL,
  age INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 错误SQL：语法正确，但是t1表没有mobile列，预处理器会报语义错误
SELECT id, name, mobile FROM t1 WHERE age > 18;

2.5 优化器：SQL的执行大脑

预处理器处理完成后，合法的SQL会交给优化器，优化器的核心作用是：基于SQL的语义，生成多种可能的执行计划，然后基于成本模型（CBO） 选择成本最低的执行计划，作为最终的执行方案。

MySQL 5.5之后，优化器以基于成本的CBO模型为主，成本的核心计算维度为IO成本和CPU成本：

  • IO成本：从磁盘读取数据页到内存的成本，InnoDB默认一个16KB数据页的IO成本为1.0。
  • CPU成本：读取数据后，判断是否符合条件、排序、分组等操作的CPU成本，默认读取一行数据的CPU成本为0.2。

优化器会计算所有可能执行计划的总成本，选择总成本最低的方案，核心工作场景包括：

  1. 索引选择：当SQL中有多个可用索引时，判断哪个索引的查询成本最低。
  2. 表连接顺序选择：多表关联查询时，确定驱动表与被驱动表的关联顺序。
  3. 语句重写：将外连接转为内连接、简化where条件、合并等值条件等，优化语义。
  4. 子查询优化：把子查询转为关联查询或半连接，减少嵌套层级。
-- 创建测试表
CREATE TABLE IF NOT EXISTS t_user (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_name VARCHAR(32) NOT NULL,
  age INT NOT NULL,
  city VARCHAR(32) NOT NULL,
  INDEX idx_age (age),
  INDEX idx_city (city)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入测试数据
INSERT INTO t_user (user_name, age, city) VALUES
('张三', 20, '北京'),
('李四', 22, '上海'),
('王五', 25, '北京'),
('赵六', 30, '深圳'),
('钱七', 28, '北京');

-- 查看优化器的索引选择，有idx_age和idx_city两个可用索引
EXPLAIN SELECT * FROM t_user WHERE age BETWEEN 20 AND 30 AND city = '北京';

执行上述EXPLAIN语句，会发现优化器选择了idx_city索引而非idx_age，原因是city = '北京'是等值查询，过滤性更好，扫描行数更少，总成本更低。当优化器出现索引选择偏差时，可通过FORCE INDEX强制指定索引。

2.6 执行器：SQL的最终执行者

优化器生成执行计划后，会将执行计划交给执行器，执行器的核心工作是：根据执行计划，调用存储引擎提供的API，完成SQL的执行，最终将结果返回给客户端。

以查询语句为例，执行器的核心执行逻辑：

  1. 执行器首先做最终的权限校验，针对存储过程、视图等运行时才能确定的场景，完成权限校验。
  2. 权限校验通过后，根据执行计划的类型，调用存储引擎对应的接口。
  3. 全表扫描场景：执行器调用存储引擎的"读取表的第一行"接口，判断该行是否符合where条件，符合则加入结果集，循环调用"读取下一行"接口，直到所有行读取完毕。
  4. 索引查询场景：执行器调用存储引擎的"读取符合条件的第一行"接口，循环读取下一行，直到所有符合条件的行读取完毕。
  5. 所有符合条件的行处理完成后，执行器将结果集封装返回给客户端。

MySQL 8.0.18及以上版本支持EXPLAIN ANALYZE，可查看执行器的实际执行耗时、扫描行数、匹配行数等详细信息：

EXPLAIN ANALYZE SELECT * FROM t_user WHERE city = '北京' AND age > 22;

需要明确的是，执行器只负责调用存储引擎的统一API，完全不感知存储引擎的内部实现，比如InnoDB的事务、锁、MVCC等逻辑，执行器都不会参与。

三、存储引擎层核心设计

存储引擎是MySQL的底层数据存储组件，基于插件式架构设计，Server层通过统一的存储引擎API与存储引擎交互，不同存储引擎实现这套API后，即可接入MySQL提供不同的存储特性。

MySQL 5.5版本之后，默认存储引擎为InnoDB，也是生产环境99%场景的首选，本文重点讲解InnoDB的核心设计。

3.1 InnoDB核心架构

InnoDB的架构分为内存结构和磁盘结构两大模块，所有数据操作先在内存中完成，再异步刷新到磁盘，以此减少磁盘IO，提升性能。

3.1.1 InnoDB内存结构

内存结构是InnoDB的性能核心，所有数据读写都优先操作内存，再通过后台线程异步刷新到磁盘。

3.1.1.1 缓冲池（Buffer Pool）

Buffer Pool是InnoDB最重要的内存组件，默认占用服务器物理内存的50%-70%，核心作用是缓存磁盘上的数据页和索引页。磁盘IO的速度比内存慢几个数量级，所有数据读写都优先操作Buffer Pool中的缓存页，以此减少磁盘IO。

Buffer Pool的核心设计：

  • 数据页：InnoDB的最小存储单元是页，默认大小16KB，Buffer Pool中缓存的就是一个个16KB的数据页，包括数据页、索引页、undo页、插入缓冲页等。

  • 优化版LRU算法：Buffer Pool采用LRU（最近最少使用）算法管理缓存页，但对传统LRU做了核心优化，将LRU链表分为young区（热数据区，默认占5/8）和old区（冷数据区，默认占3/8），解决全表扫描导致热数据被淘汰的问题。

    • 新读取的数据页，首先会放到old区的头部，而非整个LRU链表的头部。
    • 只有当这个数据页在old区被再次访问，且距离第一次访问的时间超过innodb_old_blocks_time（默认1秒），才会被移动到young区的头部。
    • 全表扫描的大量数据页只会被访问一次，不会进入young区，不会冲掉热数据，完美解决了传统LRU的缺陷。
3.1.1.2 更改缓冲区（Change Buffer）

Change Buffer是Buffer Pool的一部分，核心作用是针对非唯一二级索引的DML操作（INSERT、UPDATE、DELETE）做缓存优化。

需要明确的是，Change Buffer仅适用于非唯一二级索引，唯一索引和主键索引无法使用，原因是唯一索引的DML操作需要校验唯一性，必须将数据页加载到内存中才能完成校验，无法做缓存。

Change Buffer的核心逻辑：

  1. 对非唯一二级索引执行DML操作时，如果对应的索引页不在Buffer Pool中，InnoDB不会立即从磁盘加载索引页，而是将修改操作记录到Change Buffer中。
  2. 后续有查询操作访问到该索引页时，会将索引页加载到Buffer Pool，再将Change Buffer中对应的修改合并到索引页中，保证数据一致性。
  3. InnoDB后台线程会在系统空闲时，批量将Change Buffer中的修改合并到磁盘的索引页中，将大量随机IO合并为顺序IO，极大提升DML操作的性能。

Change Buffer适用于写多读少的业务场景，读多写少的场景下，写入后立即查询会触发即时合并，反而会带来额外开销。

3.1.1.3 自适应哈希索引（AHI）

AHI是InnoDB基于Buffer Pool中的热点索引页，自动构建的内存哈希索引，无需人工干预，核心作用是加速热点数据的查询。哈希索引的查询时间复杂度为O(1)，比B+树索引的O(logn)更快，InnoDB会监控二级索引的查询频率，对高频访问的索引页自动构建哈希索引。

AHI是内存结构，不会持久化到磁盘，完全由InnoDB自动管理，默认开启，可通过innodb_adaptive_hash_index参数控制。

3.1.1.4 日志缓冲区（Log Buffer）

Log Buffer是用来缓存redo log的内存区域，默认大小16MB，核心作用是减少redo log的磁盘IO次数。数据修改对应的redo log不会每次都直接写入磁盘，而是先写入Log Buffer，再在特定时机由后台线程批量刷新到磁盘。

Log Buffer刷新到磁盘的核心时机：

  1. 事务提交时，会将Log Buffer中的redo log刷新到磁盘，由innodb_flush_log_at_trx_commit参数控制。
  2. Log Buffer占用空间超过一半时，后台线程自动刷新到磁盘。
  3. 每秒一次，后台线程自动将Log Buffer中的redo log刷新到磁盘。
  4. 数据库正常关闭时，将Log Buffer全部内容刷新到磁盘。

3.1.2 InnoDB磁盘结构

InnoDB的磁盘结构负责数据的持久化存储，所有内存数据最终都会刷新到磁盘，保证数据不丢失。

3.1.2.1 表空间

表空间是InnoDB数据存储的逻辑容器，分为多种类型：

  • 系统表空间：对应ibdata1文件，默认大小12MB，自动扩展，存储InnoDB的数据字典、双写缓冲区、Change Buffer，以及未开启独立表空间的表数据和索引。
  • 独立表空间：MySQL 5.6.6之后默认开启，由innodb_file_per_table参数控制，开启后每个表对应一个单独的.ibd文件，存储该表的数据、索引。独立表空间删除表后会直接释放磁盘空间，方便单表备份与恢复，避免单文件膨胀。
  • undo表空间：专门存储undo日志的表空间，MySQL 8.0默认有两个undo表空间，支持动态扩容与收缩，undo日志用于事务回滚和MVCC。
  • 临时表空间：存储用户创建的临时表，以及优化器执行查询时生成的内部临时表，对应ibtmp1文件。
3.1.2.2 redo log：崩溃恢复的核心

redo log是InnoDB存储引擎特有的物理日志，记录的是"在某个数据页上做了什么修改"，核心作用是保证事务的持久性，实现崩溃恢复（crash-safe）。

Buffer Pool中的数据页是16KB，每次修改几个字节就要刷新整个页到磁盘，是随机IO，性能极差；而redo log是顺序写入，仅记录修改内容，是顺序IO，性能极高，这也是redo log存在的核心意义。

redo log的核心特性：

  • 物理日志：记录数据页的物理修改，而非逻辑操作，崩溃恢复时可直接根据redo log恢复数据页，速度极快。
  • 循环写入：redo log是固定大小的，比如配置两个4GB的文件，写完第一个写第二个，第二个写完回到第一个循环写入。
  • write pos：当前redo log的写入位置，持续后移，写到文件末尾则回到开头。
  • check point：当前redo log的刷新位置，即对应修改已持久化到磁盘的数据页的位置，check point之前的redo log可被覆盖。
  • 崩溃恢复：MySQL意外宕机重启时，InnoDB会扫描redo log，将check point之后的所有修改重新应用到数据页，保证已提交的事务修改不丢失，实现crash-safe能力。
3.1.2.3 undo log：事务回滚与MVCC的核心

undo log是InnoDB存储引擎特有的逻辑日志，记录的是数据修改前的状态，核心作用有两个：事务回滚、MVCC多版本并发控制。

undo log的核心逻辑：

  • 执行修改操作时，InnoDB会先把修改前的数据记录到undo log中，比如执行UPDATE t1 SET name = '李四' WHERE id = 1，会将修改前的name='张三'记录写入undo log。
  • 事务需要回滚时，InnoDB可根据undo log将数据恢复到修改前的状态，保证事务的原子性。
  • 其他事务需要读取记录的历史版本时，InnoDB可根据undo log的版本链，生成对应的历史数据，实现MVCC，解决读写冲突，提升并发性能。

undo log是逻辑日志，记录的是反向操作，当没有事务需要读取历史版本时，InnoDB的purge线程会自动清理无用的undo log，释放空间。

3.2 事务ACID的底层实现

ACID是事务的四大核心特性，InnoDB对ACID的实现完全基于上述核心组件：

  • 原子性（Atomicity） ：基于undo log实现，事务回滚时通过undo log将数据恢复到修改前的状态。
  • 一致性（Consistency） ：事务的最终目标，由原子性、隔离性、持久性共同保证，结合数据库的主键、唯一键等约束实现。
  • 隔离性（Isolation） ：基于锁机制和MVCC实现，解决并发事务的冲突问题。
  • 持久性（Durability） ：基于redo log实现，事务提交时redo log必须刷新到磁盘，即使数据库宕机，重启后可通过redo log恢复数据。

3.3 事务隔离级别与MVCC底层实现

SQL标准定义了四个事务隔离级别，从低到高分别是：读未提交（Read Uncommitted）、读已提交（Read Committed, RC）、可重复读（Repeatable Read, RR）、串行化（Serializable）。

InnoDB默认的隔离级别是可重复读（RR），并且在RR级别下通过Next-Key Lock（临键锁）解决了幻读问题，这是InnoDB与其他数据库的核心区别之一。

3.3.1 并发事务的三大问题

  • 脏读：事务A读取到了事务B未提交的修改，若事务B回滚，事务A读取到的就是无效的脏数据。
  • 不可重复读：事务A在执行过程中多次读取同一条记录，中间事务B修改了这条记录并提交，事务A两次读取的结果不一致，核心是修改与删除。
  • 幻读：事务A在执行过程中用相同的where条件查询，第一次查询得到N条记录，中间事务B插入了符合条件的新记录并提交，事务A第二次查询得到N+1条记录，核心是插入。

四个隔离级别对三大问题的解决能力：

隔离级别脏读不可重复读幻读
读未提交存在存在存在
读已提交解决存在存在
可重复读解决解决InnoDB解决
串行化解决解决解决

3.3.2 MVCC的底层实现

MVCC（多版本并发控制）是InnoDB实现隔离级别的核心机制，核心作用是让读写操作不加锁，读写不冲突，极大提升数据库的并发性能。

MVCC的核心实现依赖三个组件：隐藏列、undo log版本链、Read View。

1. 行记录的隐藏列

InnoDB的每行记录都包含三个隐藏列：

  • DB_TRX_ID：6字节，最近一次修改这条记录的事务ID，事务ID是InnoDB自动分配的严格递增ID。
  • DB_ROLL_PTR：7字节，回滚指针，指向这条记录对应的undo log，通过该指针可找到记录修改前的历史版本。
  • DB_ROW_ID：6字节，隐藏行ID，仅当表没有主键也没有非空唯一键时，InnoDB才会生成该列作为聚簇索引的主键。
2. undo log版本链

每次对一条记录做修改，都会将修改前的记录写入undo log，然后通过DB_ROLL_PTR指针将这些undo log串联起来，形成版本链。版本链的头部是最新的记录，尾部是最旧的历史版本。

3. Read View与可见性规则

Read View是事务执行快照读时生成的读视图，定义了当前事务能看到哪些数据版本，核心包含四个字段：

  • m_ids：生成Read View时，当前系统中所有活跃（未提交）的事务ID集合。
  • min_trx_id：m_ids中的最小事务ID，即当前系统中未提交事务的最小ID。
  • max_trx_id：生成Read View时，系统中将要分配给下一个事务的ID，即当前最大事务ID+1。
  • creator_trx_id：生成该Read View的当前事务的ID。

InnoDB通过以下规则判断版本链中的数据版本是否对当前事务可见：

  1. 若数据版本的DB_TRX_ID == creator_trx_id，说明是当前事务自己修改的，可见。
  2. 若数据版本的DB_TRX_ID < min_trx_id，说明该版本的事务在生成Read View之前已提交，可见。
  3. 若数据版本的DB_TRX_ID >= max_trx_id，说明该版本的事务在生成Read View之后才开启，不可见。
  4. 若数据版本的DB_TRX_ID在min_trx_id和max_trx_id之间，判断是否在m_ids中：在则说明事务未提交，不可见；不在则说明已提交，可见。

若当前版本不可见，就通过DB_ROLL_PTR指针找到上一个历史版本，重复上述判断，直到找到可见版本，或遍历完版本链无可见版本则返回空。

4. RC与RR隔离级别的核心差异

读已提交（RC）和可重复读（RR）的核心区别，是Read View的生成时机不同：

  • 读已提交（RC） ：事务中每次执行快照读，都会生成一个新的Read View，因此每次都能看到其他事务已提交的最新修改，会出现不可重复读。
  • 可重复读（RR） ：事务中第一次执行快照读时生成Read View，整个事务生命周期内的所有快照读都复用这个Read View，因此看到的数据版本始终一致，解决了不可重复读问题。
-- 创建测试表
CREATE TABLE IF NOT EXISTS t_account (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(32) NOT NULL,
  balance INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入测试数据
INSERT INTO t_account (name, balance) VALUES ('张三', 1000);

-- 演示1：读已提交（RC）级别，不可重复读
-- 事务A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- 第一次查询，balance=1000
SELECT * FROM t_account WHERE id = 1;

-- 事务B，执行修改并提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
UPDATE t_account SET balance = 2000 WHERE id = 1;
COMMIT;

-- 回到事务A，第二次查询，balance=2000，两次结果不一致
SELECT * FROM t_account WHERE id = 1;
COMMIT;

-- 演示2：可重复读（RR）级别，解决不可重复读
-- 事务A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- 第一次查询，balance=1000，生成Read View
SELECT * FROM t_account WHERE id = 1;

-- 事务B，执行修改并提交
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
UPDATE t_account SET balance = 2000 WHERE id = 1;
COMMIT;

-- 回到事务A，第二次查询，balance仍为1000，两次结果一致
SELECT * FROM t_account WHERE id = 1;
COMMIT;

3.3.3 幻读的解决方案：Next-Key Lock

InnoDB在RR隔离级别下，通过Next-Key Lock（临键锁）解决了幻读问题。临键锁是InnoDB的行锁算法，由两部分组成：

  • 记录锁（Record Lock） ：锁定索引中的某一条具体记录，仅锁行，不锁间隙。
  • 间隙锁（Gap Lock） ：锁定索引记录之间的间隙，防止其他事务在间隙中插入新记录，从根源上解决幻读。
  • 临键锁：记录锁+间隙锁，锁定一个左开右闭的区间，InnoDB默认的行锁算法。

需要明确的是，临键锁仅在RR隔离级别下生效，RC级别下只有记录锁，没有间隙锁，因此会出现幻读问题。

3.4 两阶段提交：redo log与binlog的一致性保证

binlog是Server层的逻辑日志，所有存储引擎都可生成，记录的是SQL的执行逻辑，核心作用是主从复制和数据归档恢复。很多开发者会混淆binlog与redo log，这里做明确区分：

特性redo logbinlog
所属层级InnoDB存储引擎层MySQL Server层
日志类型物理日志，记录数据页的修改逻辑日志，记录SQL的执行逻辑
写入方式循环写入，固定大小追加写入，写满生成新文件
核心作用崩溃恢复，保证事务持久性主从复制，数据归档恢复
适用范围仅InnoDB存储引擎所有存储引擎

为了保证redo log与binlog的一致性，MySQL事务提交时采用两阶段提交（2PC） 机制，这是MySQL崩溃恢复和主从数据一致性的核心。

两阶段提交的完整流程：

  1. 执行器调用InnoDB接口，将数据修改更新到Buffer Pool。
  2. InnoDB生成对应的redo log，写入Log Buffer，标记为prepare状态。
  3. 执行器生成对应的binlog，写入binlog缓存，然后将binlog刷新到磁盘。
  4. 执行器调用InnoDB的事务提交接口，将redo log标记为commit状态，事务提交完成。

两阶段提交完美解决了日志一致性问题：

  • 若在prepare阶段之后、写binlog之前宕机，重启后发现redo log是prepare状态、binlog未写入，会回滚该事务，两个日志保持一致。
  • 若在binlog写完之后、commit之前宕机，重启后发现redo log是prepare状态、binlog已完整写入，会提交该事务，两个日志保持一致。

四、一条SQL的完整执行流程

我们以一条update语句为例，将Server层与存储引擎层的所有组件串联起来，完整拆解SQL从客户端发出到结果返回的全流程。

以UPDATE t_user SET balance = balance - 100 WHERE id = 1为例，完整执行步骤：

  1. 客户端通过TCP协议与MySQL建立连接，连接器完成身份认证、权限读取，维护连接状态。
  2. 客户端发送update SQL到服务端，连接器将SQL交给解析器。
  3. 解析器对SQL做词法分析和语法分析，校验SQL语法是否正确，语法错误则直接返回报错。
  4. 预处理器对SQL做语义校验，验证表、列是否存在，校验用户的update权限，语义错误或权限不足则返回报错。
  5. 优化器接收合法SQL，生成多种执行计划，基于成本模型选择主键索引作为最优路径，生成最终执行计划。
  6. 执行器接收执行计划，完成最终权限校验，调用InnoDB存储引擎的接口执行更新操作。
  7. InnoDB找到id=1的记录，若不在Buffer Pool中则从磁盘加载到内存，对该记录加排他锁。
  8. InnoDB将修改前的记录写入undo log，用于事务回滚和MVCC。
  9. InnoDB更新Buffer Pool中的数据，生成对应的redo log写入Log Buffer，标记为prepare状态。
  10. 执行器生成update操作对应的binlog，写入缓存后刷新到磁盘完成持久化。
  11. 执行器调用InnoDB的事务提交接口，InnoDB将Log Buffer中的redo log刷新到磁盘，标记为commit状态，事务正式提交，释放排他锁。
  12. 执行器将更新的影响行数返回给客户端，整个SQL执行完成。

五、实战案例：基于架构原理解决实际问题

5.1 案例1：MySQL长连接内存暴涨问题

现象：业务系统使用长连接访问MySQL，运行一段时间后MySQL内存占用持续上涨，最终被系统OOM杀掉。 根因：MySQL的连接对象会管理执行过程中使用的所有内存资源，这些资源仅在连接断开时释放，长连接持续执行大查询、大事务，会导致内存持续累积无法释放。 解决方案：

  1. 定期断开空闲时间过长的长连接，释放内存资源。
  2. 执行完大查询、大事务后，调用mysql_reset_connection()函数重置连接，释放内存无需重连。
  3. 合理配置wait_timeout参数，避免空闲连接长期占用资源。

5.2 案例2：索引创建后未生效，优化器选错索引

现象：SQL的where条件字段已创建索引，但explain显示全表扫描，查询性能极差。 根因：优化器基于成本模型选择索引，出现该问题的常见原因包括：索引过滤性太差、表统计信息不准确、对索引字段做了函数操作或隐式类型转换导致索引失效。 解决方案：

  1. 避免对索引字段做函数操作、隐式类型转换，比如将DATE(create_time) = '2024-01-01'改为create_time >= '2024-01-01' AND create_time < '2024-01-02'。
  2. 执行ANALYZE TABLE t_user;重新统计表的统计信息，让优化器获取准确的成本数据。
  3. 若优化器仍选错索引，使用FORCE INDEX强制指定索引。

5.3 案例3：事务不生效问题排查

现象：业务代码配置了事务，但执行异常后数据未回滚，事务失效。 根因：事务是存储引擎层实现的，常见失效原因包括：表的存储引擎为MyISAM（不支持事务）、事务中执行了DDL语句（隐式提交事务）、异常被捕获未抛出导致切面未感知、事务传播行为配置错误。 解决方案：

  1. 执行SHOW CREATE TABLE t_user;确认表的存储引擎为InnoDB，若不是则执行ALTER TABLE t_user ENGINE=InnoDB;修改。
  2. 避免在事务中执行DDL语句，DDL会隐式提交事务，无法回滚。
  3. 异常捕获后若需回滚，需手动触发事务回滚，不要静默吞掉异常。
  4. 合理配置事务传播行为，默认使用REQUIRED传播行为。

六、Java实战代码实现

基于JDK 17、Spring Boot 3.2.4、MyBatis-Plus 3.5.7实现用户转账的事务操作。

6.1 pom.xml依赖配置

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.2.4</version>
        <relativePath/>
    </parent>
    <groupId>com.jam.demo</groupId>
    <artifactId>mysql-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mysql-demo</name>
    <description>MySQL架构实战Demo</description>
    <properties>
        <java.version>17</java.version>
        <mybatis-plus.version>3.5.7</mybatis-plus.version>
        <fastjson2.version>2.0.52</fastjson2.version>
        <guava.version>33.1.0-jre</guava.version>
        <lombok.version>1.18.34</lombok.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>${mybatis-plus.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springdoc</groupId>
            <artifactId>springdoc-openapi-starter-webmvc-ui</artifactId>
            <version>2.5.0</version>
        </dependency>
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba.fastjson2</groupId>
            <artifactId>fastjson2</artifactId>
            <version>${fastjson2.version}</version>
        </dependency>
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>${guava.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

6.2 application.yml配置

spring:
  application:
    name: mysql-demo
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/jam_demo?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
springdoc:
  swagger-ui:
    path: /swagger-ui.html
    enabled: true
  api-docs:
    enabled: true
    path: /v3/api-docs
mybatis-plus:
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      id-type: auto

6.3 实体类User.java

package com.jam.demo.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;

import java.io.Serial;
import java.io.Serializable;

/**
 * 用户实体类
 *
 * @author ken
 */
@Data
@TableName("t_user")
@Schema(description = "用户实体")
public class User implements Serializable {

    @Serial
    private static final long serialVersionUID = 1L;

    @TableId(type = IdType.AUTO)
    @Schema(description = "用户ID", example = "1")
    private Integer id;

    @Schema(description = "用户名", example = "张三")
    private String userName;

    @Schema(description = "年龄", example = "25")
    private Integer age;

    @Schema(description = "所在城市", example = "北京")
    private String city;

    @Schema(description = "账户余额", example = "1000")
    private Integer balance;
}

6.4 Mapper接口UserMapper.java

package com.jam.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.jam.demo.entity.User;
import org.apache.ibatis.annotations.Mapper;

/**
 * 用户Mapper接口
 *
 * @author ken
 */
@Mapper
public interface UserMapper extends BaseMapper<User> {
}

6.5 转账参数VO UserTransferVo.java

package com.jam.demo.vo;

import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;

import java.io.Serial;
import java.io.Serializable;

/**
 * 转账参数VO
 *
 * @author ken
 */
@Data
@Schema(description = "用户转账参数")
public class UserTransferVo implements Serializable {

    @Serial
    private static final long serialVersionUID = 1L;

    @Schema(description = "转出用户ID", requiredMode = Schema.RequiredMode.REQUIRED, example = "1")
    private Integer fromUserId;

    @Schema(description = "转入用户ID", requiredMode = Schema.RequiredMode.REQUIRED, example = "2")
    private Integer toUserId;

    @Schema(description = "转账金额", requiredMode = Schema.RequiredMode.REQUIRED, example = "100")
    private Integer amount;
}

6.6 统一返回结果ResultVo.java

package com.jam.demo.vo;

import io.swagger.v3.oas.annotations.media.Schema;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serial;
import java.io.Serializable;

/**
 * 统一返回结果类
 *
 * @author ken
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
@Schema(description = "统一返回结果")
public class ResultVo<T> implements Serializable {

    @Serial
    private static final long serialVersionUID = 1L;

    @Schema(description = "响应码", example = "200")
    private Integer code;

    @Schema(description = "响应消息", example = "操作成功")
    private String message;

    @Schema(description = "响应数据")
    private T data;

    public static <T> ResultVo<T> success(T data) {
        return new ResultVo<>(200, "操作成功", data);
    }

    public static <T> ResultVo<T> success(String message, T data) {
        return new ResultVo<>(200, message, data);
    }

    public static <T> ResultVo<T> fail(String message) {
        return new ResultVo<>(500, message, null);
    }

    public static <T> ResultVo<T> fail(Integer code, String message) {
        return new ResultVo<>(code, message, null);
    }
}

6.7 服务接口UserService.java

package com.jam.demo.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.jam.demo.entity.User;
import com.jam.demo.vo.UserTransferVo;

/**
 * 用户服务接口
 *
 * @author ken
 */
public interface UserService extends IService<User> {

    /**
     * 用户账户转账
     *
     * @param transferVo 转账参数
     * @return 转账是否成功
     */
    boolean transferAccount(UserTransferVo transferVo);
}

6.8 服务实现类UserServiceImpl.java

package com.jam.demo.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.google.common.collect.Lists;
import com.jam.demo.entity.User;
import com.jam.demo.mapper.UserMapper;
import com.jam.demo.service.UserService;
import com.jam.demo.vo.UserTransferVo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;

import jakarta.annotation.Resource;
import java.util.List;

/**
 * 用户服务实现类
 *
 * @author ken
 */
@Slf4j
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {

    @Resource
    private TransactionTemplate transactionTemplate;

    @Override
    public boolean transferAccount(UserTransferVo transferVo) {
        if (ObjectUtils.isEmpty(transferVo)) {
            log.warn("转账参数为空");
            return false;
        }
        Integer fromUserId = transferVo.getFromUserId();
        Integer toUserId = transferVo.getToUserId();
        Integer amount = transferVo.getAmount();
        if (ObjectUtils.isEmpty(fromUserId) || ObjectUtils.isEmpty(toUserId)) {
            log.warn("转账用户ID不能为空");
            return false;
        }
        if (fromUserId.equals(toUserId)) {
            log.warn("转账用户不能是同一个用户");
            return false;
        }
        if (ObjectUtils.isEmpty(amount) || amount <= 0) {
            log.warn("转账金额必须大于0");
            return false;
        }

        return Boolean.TRUE.equals(transactionTemplate.execute(new TransactionCallback<Boolean>() {
            @Override
            public Boolean doInTransaction(TransactionStatus status) {
                try {
                    LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<User>()
                            .in(User::getId, Lists.newArrayList(fromUserId, toUserId));
                    List<User> userList = list(queryWrapper);
                    if (CollectionUtils.isEmpty(userList) || userList.size() != 2) {
                        log.warn("转账用户不存在，fromUserId:{}, toUserId:{}", fromUserId, toUserId);
                        return false;
                    }

                    User fromUser = userList.stream().filter(user -> fromUserId.equals(user.getId())).findFirst().orElse(null);
                    User toUser = userList.stream().filter(user -> toUserId.equals(user.getId())).findFirst().orElse(null);
                    if (ObjectUtils.isEmpty(fromUser) || ObjectUtils.isEmpty(toUser)) {
                        log.warn("转账用户信息不存在");
                        return false;
                    }
                    if (fromUser.getBalance() < amount) {
                        log.warn("转出用户余额不足，当前余额:{}, 转账金额:{}", fromUser.getBalance(), amount);
                        return false;
                    }

                    fromUser.setBalance(fromUser.getBalance() - amount);
                    toUser.setBalance(toUser.getBalance() + amount);
                    boolean updateResult = updateBatchById(Lists.newArrayList(fromUser, toUser));
                    if (!updateResult) {
                        log.error("更新用户余额失败");
                        status.setRollbackOnly();
                        return false;
                    }
                    log.info("转账成功，fromUserId:{}, toUserId:{}, amount:{}", fromUserId, toUserId, amount);
                    return true;
                } catch (Exception e) {
                    log.error("转账发生异常，回滚事务", e);
                    status.setRollbackOnly();
                    return false;
                }
            }
        }));
    }
}

6.9 控制器UserController.java

package com.jam.demo.controller;

import com.jam.demo.entity.User;
import com.jam.demo.service.UserService;
import com.jam.demo.vo.UserTransferVo;
import com.jam.demo.vo.ResultVo;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.Parameter;
import io.swagger.v3.oas.annotations.tags.Tag;
import jakarta.annotation.Resource;
import org.springframework.web.bind.annotation.*;

import java.util.List;

/**
 * 用户控制器
 *
 * @author ken
 */
@RestController
@RequestMapping("/user")
@Tag(name = "用户管理", description = "用户相关操作接口")
public class UserController {

    @Resource
    private UserService userService;

    @GetMapping("/list")
    @Operation(summary = "查询所有用户", description = "获取所有用户列表")
    public ResultVo<List<User>> getUserList() {
        List<User> userList = userService.list();
        return ResultVo.success(userList);
    }

    @GetMapping("/{id}")
    @Operation(summary = "根据ID查询用户", description = "根据用户ID获取用户详情")
    public ResultVo<User> getUserById(
            @Parameter(description = "用户ID", required = true) @PathVariable Integer id) {
        User user = userService.getById(id);
        return ResultVo.success(user);
    }

    @PostMapping("/add")
    @Operation(summary = "新增用户", description = "创建新用户")
    public ResultVo<Boolean> addUser(@RequestBody User user) {
        boolean saveResult = userService.save(user);
        return ResultVo.success(saveResult);
    }

    @PutMapping("/update")
    @Operation(summary = "更新用户", description = "更新用户信息")
    public ResultVo<Boolean> updateUser(@RequestBody User user) {
        boolean updateResult = userService.updateById(user);
        return ResultVo.success(updateResult);
    }

    @DeleteMapping("/{id}")
    @Operation(summary = "删除用户", description = "根据用户ID删除用户")
    public ResultVo<Boolean> deleteUser(
            @Parameter(description = "用户ID", required = true) @PathVariable Integer id) {
        boolean deleteResult = userService.removeById(id);
        return ResultVo.success(deleteResult);
    }

    @PostMapping("/transfer")
    @Operation(summary = "用户转账", description = "用户之间账户余额转账，带事务控制")
    public ResultVo<Boolean> transferAccount(@RequestBody UserTransferVo transferVo) {
        boolean transferResult = userService.transferAccount(transferVo);
        if (transferResult) {
            return ResultVo.success("转账成功", true);
        }
        return ResultVo.fail("转账失败");
    }
}

6.10 项目启动类MysqlDemoApplication.java

package com.jam.demo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * 项目启动类
 *
 * @author ken
 */
@SpringBootApplication
@MapperScan("com.jam.demo.mapper")
public class MysqlDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(MysqlDemoApplication.class, args);
    }
}

总结

MySQL的核心架构分为Server层和存储引擎层，Server层负责通用的SQL处理逻辑，存储引擎层负责数据的存储与底层特性实现，清晰的分层边界是MySQL具备极强扩展性的核心原因。

avatar
文章
阅读
粉丝