MySQL朴素解释

将MySQL知识总结成文是一件我一直想做的事情，之前一直不知道以什么线索什么切入点来着手做这件事，最近在软件工程上领悟到了一些事情，突然就有了灵感。

本文将站在MySQL使用者的角度详细解析MySQL，读者需要有最起码的关系型数据库的基本知识。本文从MySQL的架构设计开始，讲解作为一款功能完善的关系型数据库软件，在实现与设计上会遇到什么问题，MySQL是如何解决的，流程是怎么样的。

本文基于MySQL8.0及以上版本，旧版本可能不会提及或者一笔带过，同时本文也只介绍InnoDB引擎，其他引擎可能不会提及或者一笔带过。

架构

作为一款功能完善的数据库软件。很自然，它采用了分层分模块的设计。架构如下：

每一层每个组件都有自己的职责。这里根据用户执行SQL时组件应用的先后顺序介绍。具体功能如下：

连接层

 负责处理客户端（应用程序、命令行工具、其他服务等）与 MySQL 服务器的网络连接、通信协议、身份认证和权限验证。

Connnectors

这是给开发者准备的分发到各端的MySQL client，比如Java的：

<!-- https://mvnrepository.com/artifact/com.mysql/mysql-connector-j -->
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.2.0</version>
</dependency>

Connection Pool

连接池管理和复用客户端连接。当客户端发起连接请求时，连接层会验证用户名、密码和主机权限。验证通过后，连接池会分配或创建一个线程（通常是每个连接对应一个线程，现代版本也支持线程池技术）来处理该连接上的所有请求。使用连接池避免了为每个请求频繁创建和销毁连接的开销，提高了并发性能。

服务器层

这是 MySQL 的“大脑”，负责 SQL 语句的解析、分析、优化、执行以及所有内置函数的实现（日期、数学、加密等）。它不关心数据是如何存储的。

SQL Interface

SQL 接口层是 MySQL 服务层的核心组件之一，扮演着连接客户端请求与数据库引擎的“翻译官”和“调度中心”角色。它负责接收、解析、分发 SQL 命令，并最终将结果返回给客户端。是源码中sql目录下一组接口的总称，包含DDL、DML，存储过程，视图、触发器等等。

其入口在，sql_parse.cc

bool dispatch_command(THD *thd, const COM_DATA *com_data,
                      enum enum_server_command command) {}

1. 协议解析

文本协议（Text Protocol）：普通SQL语句（如SELECT * FROM users）
二进制协议（Binary Protocol）：预处理语句（Prepared Statements），提高效率并防SQL注入

2. SQL命令路由

对于不同的命令需要路由到不同的方法处理

比如：

case COM_STMT_PREPARE: {
  /* Clear possible warnings from the previous command */
  thd->reset_for_next_command();
  Prepared_statement *stmt = nullptr;

  DBUG_EXECUTE_IF("parser_stmt_to_error_log", {
    LogErr(INFORMATION_LEVEL, ER_PARSER_TRACE,
           com_data->com_stmt_prepare.query);
  });
  DBUG_EXECUTE_IF("parser_stmt_to_error_log_with_system_prio", {
    LogErr(SYSTEM_LEVEL, ER_PARSER_TRACE, com_data->com_stmt_prepare.query);
  });

  if (!mysql_stmt_precheck(thd, com_data, command, &stmt))
    mysqld_stmt_prepare(thd, com_data->com_stmt_prepare.query,
                        com_data->com_stmt_prepare.length, stmt);
  break;
}

Parser 分析器

分析器包括三个部分，词法分析（识别关键字、标识符等）与语法分析（检查 SQL 语句是否符合语法规则），语义分析（预校验）。负责将sql语句解析成解析树Parse Tree。这个过程实际上和编译过程是差不多的。

词法分析 ——> 语法分析 ——> 语义分析

1. 词法分析Lexer

和大多数编译器一样使用lex，词法分析由sql_lex.cc实现，职责是sql字节流转换成token序列：

SELECT * FROM users;  =>  (SELECT, *, FROM, users, ;)

关键字校验会在这里完成，比如 selct 1 由于select关键字错误就不能通过校验。

2. 语法分析Parser（Bison 语法）

和大多数编译器一样使用Yacc，由sql_yacc.yy定义语法规则，由gen_lex_token.cc自动生成的 Token 映射，职责是构建语法树，存储于lex结构。
语法校验会在此处完成，比如 select * from where id =1 由于确实表名无法通过语法校验。

3. 语义分析（Semantic Analysis）

语义分析在语法分析后、优化器前执行。其核心任务是 验证 SQL 的语义合法性，并完成初步逻辑优化。他有以下职责：

• 元数据验证（Metadata Validation）

校验表与列是否存在（比如select了一个不存在的表或者列），消除歧义（比如两个表存在同名字段，但是select的时候没有指定表字段），数据类型基础兼容性检查（比如将一个string插入到了int，有些时候只警告）。

• 语义规则校验（Semantic Rules）

早期权限检查（表权限），聚合函数约束（比如在select的字段不在group by里面）和子查询关联性校验

• 逻辑重写（Logical Rewriting）

常量折叠（比如将sql中的1+1直接优化为2），冗余条件移除（比如将where 1=1条件移除），视图展开（比如SELECT * FROM v (v=定义SELECT * FROM t)，重写为SELECT * FROM t）,派生条件下推（SELECT * FROM (SELECT * FROM t) dt WHERE id>100 重写为SELECT * FROM t WHERE id>100），布尔简化（WHERE NOT (a > 5)重写为WHERE a <= 5）

• 存储过程/函数参数验证
• 分区表元数据校验（校验分区表是存在）
• Generated Column 依赖分析

比如：

ALTER TABLE t ADD COLUMN gen_col INT AS (col1 + col2);
-- 预处理阶段验证表达式合法性

会校验生成列gen_col所依赖的(col1 + col2)是否合法。

生成列：

generated_column DEFINED AS (expression) [VIRTUAL | STORED] 

生成列有两类虚拟列与存储列。虚拟列在每次查询的时候都会重新生成，而存储列会存储到库中，只在表达式中包含的列插入或者删除的时候会变更。这在定义别名字段与计算类似总价这样的字段上很有用。

我们可以通过EXPLAIN命令来查看语法树结构（8.0以后）：

EXPLAIN FORMAT=tree SELECT * FROM t WHERE age > 18;

Optimizer 优化器

前文我们已经得到了一颗语法树了。优化器的职责是通过将语法树转换与选择合适的执行计划交由后续的执行器区去执行，它基于关系代数和代价模型，在众多可能的执行路径中选择最优解。我们的sql优化通常指的就是通过分析当前sql瓶颈，让优化器生成和选择更快的执行计划。

优化器优化可以拆解为以下三步：

逻辑优化 ——> 物理优化 ——> 执行计划

逻辑优化（Logical Optimization）

逻辑优化的目标是基于关系代数等价变换优化查询结构

• 子查询优化

将子查询转换为join，比如SELECT * FROM t1 WHERE id IN (SELECT id FROM t2)转换为SELECT t1.* FROM t1 left join t2 on t1.id = t2.id

• 谓词下推

尽早过滤数据减少计算量，WHERE条件移到JOIN前。比如SELECT t1.* FROM t1 left join t2 on t1.id = t2.id where t2.age > 18转换为 SELECT t1.* FROM t1 left join t2 on t1.id = t2.id and t2.age > 18

• 条件化简

简化冗余表达式。比如where age > 18 and age > 20 转换成 where age > 20

• 外连接化简

当条件允许将外连接转内连接，LEFT JOIN + NOT NULL约束 → INNER JOIN。

• 常量传播

传播常量值减少计算，比如where age=18 and next_age = a+1 转换成 where age=18 and next_age = 19

物理优化（Physical Optimization）

物理优化的目标是根据代价模型为逻辑计划选择最优物理操作算法。

• 表访问方式

表访问方式有两类，索引访问和全表扫描。

MySQL 优化器会根据统计信息，如表的行数、索引的键分布情况等，估算使用不同索引时的查询代价，包括索引扫描的代价、回表访问数据行的代价等，从而选择最合适且代价最小的索引进行数据访问。例如，对于等值查询，若索引的区分度较高，使用索引扫描的代价可能比全表扫描低得多，则会选择该索引。

当没有合适的索引，或者使用索引的代价反而高于全表扫描时，优化器会选择全表扫描的方式访问表中的数据。比如，查询返回的数据量占表数据量的比例较大时，全表扫描可能更高效。

• Join优化

Join优化有两个方向，一是连接算法的选择，二是连接顺序的选择。

Join表连接有三种算法：嵌套循环连接（Nested Loop）、Hash连接和BKA连接。
优化器会根据表的大小、内存可用性等因素选择最适合的连接算法。例如，当内存足够且其中一个表较小可全部加载到内存时，哈希连接可能是一个较好的选择；而对于较大的表，嵌套循环连接可能更合适。而BKA（Batched Key Access Join） 是一种优化表连接的算法，结合了索引访问和连接缓冲区技术，通过索引访问被驱动表（Inner Table），同时利用连接缓冲区（Join Buffer）暂存驱动表（Outer Table）的数据，减少磁盘I/O，BKA会先缓存一批驱动表的记录，再批量请求被驱动表的匹配数据，降低随机访问的开销，BKA适用于内连接（INNER JOIN）、外连接（OUTER JOIN）和半连接（SEMI-JOIN），包括嵌套外连接，并且要求被驱动表对连接字段有索引，否则退化为其他算法（如BNL），最好实在分布式存储引擎中，BKA可将批量键请求发送到数据节点，减少网络传输次数。

多表连接的排列顺序也会影响查询速度。
MySQL 优化器采用基于代价的优化方式来确定表的连接顺序。它会尝试不同的连接顺序组合，并根据每个组合的估算代价，包括连接操作需要处理的数据量、I/O 成本等，选择代价最低的连接顺序。连接顺序的不同可能会导致查询性能的巨大差异，优化器会综合考虑各表的数据量、连接条件等因素来确定最优顺序。

• 索引下推、覆盖索引和最左前缀匹配原则

索引下推：在某些情况下，MySQL 优化器会将索引的谓词下推到存储引擎层，让存储引擎在数据访问时提前过滤掉不满足条件的数据行，减少回表的次数，从而提高查询效率。例如，在查询条件中有对某个字段的范围查询时，若该字段上有索引，优化器会将范围条件下推到存储引擎，使存储引擎在扫描索引时就能过滤掉不符合条件的记录。

覆盖索引 ：如果查询中所需的所有字段都在某个索引中，优化器会直接使用该覆盖索引进行查询，而无需回表访问数据行，大大减少了数据访问量和 I/O 操作，提升了查询性能。这一点是由于Innodb使用的B+树索引不需要回表就能获取到数据的特性提供了这样的能力。比如：select age from t where id=1,我们正好有一个索引是建在id和age上的联合索引，这使得age字段不需要回表就能直接获取到。

最左前缀匹配原则：假设有一个复合索引（index_col1_col2_col3）建立在表的 col1、col2、col3 这三个字段上，且这三个字段在索引中的顺序依次是 col1、col2、col3。当查询条件中的字段组合符合从左到右的前缀顺序时，就可以利用该索引。

• 分组聚合算法

Group By优先利用覆盖索引（Covering Index）减少回表查询。在无法使用索引的情况下，优化器会评估是否通过排序（Using filesort）或临时表（Using temporary）完成分组：
排序优先 ：若分组字段有序（如已存在排序索引），优化器可能选择排序后按顺序分组，减少内存消耗。
临时表动态选择 ：对于小结果集，直接构建内存临时表；若超出tmp_table_size限制，则转为磁盘临时表，可能影响性能

• 执行计划改进

利用统计信息动态调整 ：MySQL 的优化器统计信息会随着时间而发生改变，当表结构或数据发生变化时，优化器会根据新的统计信息重新评估和调整执行计划，以确保选择最优的查询策略。

对 ORDER BY 和 LIMIT 的优化 ：当查询中包含 ORDER BY 和 LIMIT 子句时，优化器会尽量选择合适的索引来避免对数据进行排序操作，或者减少排序的数据量。如果存在合适的索引，优化器会利用索引的顺序来满足 ORDER BY 的要求，同时结合 LIMIT 条件尽可能早地终止数据扫描，减少不必要的数据处理。

• 高级优化（8.0+）

直方图统计：优化器利用列数据分布选择索引。

ANALYZE TABLE orders UPDATE HISTOGRAM ON price;

不可见索引：测试索引效果而不影响生产

CREATE INDEX idx_temp ON orders(price) INVISIBLE;

跳跃扫描：早年版本的mysql如果表t1有一个联合索引idx_u1(gender,age)，但是查询的时候却没有gender这列，只有age。比如select * from t1 where age > 30那么这条sql由于不满足联合索引的最左前缀原则，那么无法使用上这个联合索引。跳跃扫描就是为了解决这一问题而出现的，假设gender字段只有m和f。那么会使用跳跃扫描优化：

select * from t1 where (gender = 'm' or gender = 'f') and age > 30 

函数索引：早年版本的mysql如果字段使用了函数或者做了运算时无法使用到索引的。现在我们可以为这些字段创建函数索引。
比如：

select * from u where mouth(birthday) = 10 ;
select * from u where (height+weight) = 100 ;

现在我们可以为其创建函数索引来优化：

alter table u add index `idx_month` (mouth(birthday));
alter table u add index `idx_fat` (height+weight);

函数索引的本质是对不可见生成列建立索引。

执行计划分析

可以通过以下语句查看执行计划详情

EXPLAIN FORMAT=JSON 
SELECT * FROM orders JOIN customers ON orders.cid = customers.id;

输出类似下面的结果：

{
  "query_block": {
    "cost_info": {
      "query_cost": "1.2"  // 总代价估算
    },
    "nested_loop": [{
      "table": {
        "table_name": "orders",
        "access_type": "range",  // 访问类型
        "rows": 1000,           // 预估行数
        "cost": 0.8
      }
    }, {
      "table": {
        "table_name": "customers",
        "access_type": "eq_ref",
        "rows": 1,
        "cost": 0.4
      }
    }]
  }
}

如果我们需要分析每一步优化：

SET optimizer_trace="enabled=on";
SELECT ...; -- 我们需要分析的语句
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

Trace输出关键阶段：

1. join_preparation - 逻辑优化
2. join_optimization - 物理优化
3. join_execution - 执行计划

优化器与语义预处理器对比

优化器与语义预处理器的协作

SELECT * FROM v WHERE col=10; -- v是视图

1. 预处理：展开视图 → SELECT * FROM t WHERE col=10
2. 优化器：决定是否使用 col 的索引

Executor 执行器

经过上面优化器的选择，MySQL已经选择出了优的执行计划。接下来需要执行器执行该计划。MySQL 执行器（Executor）是查询处理流程的最终执行者，负责将优化器生成的执行计划转化为物理操作，与存储引擎交互获取数据并返回结果。

执行器按照执行计划执行SQL语句，引擎层只存取数据。像join（联表），aggregation（聚合）和union（合并）等等这样的特性都是由服务器层的执行器完成的。

迭代器模型（8.0）

MySQL 8.0 的执行器采用了基于 Volcano 迭代模型 （又称火山模型）的迭代器模型，这是其查询执行的核心设计之一。

1. Volcano 迭代模型

MySQL 8.0 的执行器将每个查询操作（如扫描表、连接、聚合等）抽象为一个迭代器 （RowIterator），通过统一的接口实现数据逐行处理。迭代器的核心方法包括 init() 初始化和 read() 读取下一行数据，形成典型的“拉取”式执行模式

2. 迭代器接口与协议

每个迭代器遵循标准协议：

• open()：初始化执行操作（如打开表或构建哈希表）。
• next()：返回下一行数据（通过 read() 实现）。
• close()：释放资源

这种设计简化了执行流程，并支持多种操作（如扫描、连接、排序）的模块化组合

比如：

// 索引扫描+过滤+排序
IndexScanIterator -> FilterIterator -> SortingIterator

Handler API

执行器通过Handler API与存储引擎交互存取数据。

// 存储引擎访问抽象接口
class handler {
  virtual int rnd_init(bool scan)=0;
  virtual int rnd_next(uchar *buf)=0;
  virtual int index_read(...)=0;
  ....
};

Query Cache

查询缓存命中要求苛刻，在8.0被彻底删除了，这里不再介绍。现如今系统都是用应用缓存代替查询缓存的。

引擎层

真正负责数据的存储和提取。服务层通过定义好的存储引擎 API 与存储引擎层进行通信。存储引擎处理磁盘 I/O 操作、管理数据文件和索引文件。

这是 MySQL 架构最显著的特点之一。MySQL 支持多种存储引擎，不同的引擎有不同的特性和适用场景。用户可以根据表的具体需求（事务、性能、特性）为不同的表选择合适的存储引擎。引擎可以在创建表时指定，甚至可以针对同一数据库中的不同表使用不同的引擎。

服务层通过统一的 API（ handler 类）与存储引擎交互。存储引擎只要实现了这些接口，就可以“插入”到 MySQL 中工作。

上面我们已经正式通过执行器开始执行计划，此时正式需要与存储引擎交互了。这里只介绍InnoDB引擎。

InnoDB

InnoDB是MySQL默认的存储引擎。他有以下特性

• 支持 ACID 事务。
• 支持行级锁，并发性能好。
• 支持外键约束。
• 支持分区表，可以拆分大表，降低表规模。
• 使用聚集索引 (主键索引的叶子节点直接存储行数据)。
• 提供 crash-safe 恢复能力（Write-Ahead Logging - WAL 机制，即 redo log）。
• 适用于需要事务、高并发读写、数据一致性要求高的场景（如 OLTP）。

表的存储方式

innodb每个表都是一个.ibd文件。执行器访问的数据就是从这些表文件中读取的。

表空间 (Tablespace, .ibd 文件)
|
|---> 段 (Segment, 表空间内部的主要组织结构)
     |
     |---> 区 (Extent, 是 InnoDB 进行空间分配的基本单位)
          |
          |---> 页 (Page, 默认16KB，管理磁盘最小单元，也是读写基本单位)
               |
               |---> 行 (Rows, 表的实际数据行)
                    |---> 聚集索引 (Clustered Index / PRIMARY KEY，数据按照主键排序后存储).
                    |---> 二级索引 (Secondary Indexes，存储该索引的列值 + 对应行的聚集索引键值，也就是主键索引值) - 

我们所说的回表，就是在二级索引获取到聚集索引之后还需要再次通过聚集索引才能取出该行数据。

• 表空间（Tablespace）

InnoDB 存储数据的基本容器。它是一个或多个物理文件（通常是 .ibd 文件）的逻辑集合。

系统表空间，即ibdata1文件，在古早版本用来存储用户表的元数据、双写日志、undolog等。
临时表空间，即ibtmp1文件，用来存储临时表。
独立表空间是默认存储方式，每个表的元数据、数据和索引都存在自己的.ibd文件。便于管理且减少了对系统表空间的争用，支持表压缩。
通用表空间：如果非得多个表存在同一个表空间也是可以的，直接使用CREATE TABLESPACE创建。
Undo 表空间即undo_001和undo_002文件。存储undolog

• 段

 表空间内部的主要组织结构，包括数据段、索引段等，用于管理表和索引的数据。一个段是分配给表结构中特定逻辑部分（如一个索引）的一系列区。

叶子节点段：存储 B+树索引的实际数据行（对于聚集索引）或索引键值+主键（对于二级索引）。
非叶子节点段：存储 B+树索引的中间节点（用于导航）。
回滚段：存储 Undo 日志记录（用于事务回滚和 MVCC）。

• 区

段由多个连续的区组成。区是 InnoDB 进行空间分配的基本单位。每个区固定为 1MB（在大多数页大小为 16KB 的系统中）。对于页大小为 4KB 的系统，区包含 256 个页；对于 16KB 的页，区包含 64 个页。 一次性分配一个区（1MB）给段，可以减少频繁的小块分配操作，提高效率。

• 页

区由连续的页组成。页是 InnoDB 磁盘管理的最小单元，也是内存与磁盘之间数据传输的基本单位（I/O 单位）。 页是磁盘管理的最小单位，默认大小为16KB，用于存储实际的行数据。页大小在服务器初始化的时候可以通过innodb_page_size调整，但是一旦实例创建就不能调整了。

索引页（INDEX）： 存储 B+树索引节点（叶子节点或非叶子节点），包含用户数据行或索引键+指针。
Undo 日志页（UNDO_LOG）： 存储 Undo 日志记录。
系统页（INODE）： 存储文件段（Segment）及其关联的区（Extent）和碎片页（Fragment Page）的信息。
插入缓冲区位图页（IBUF_BITMAP）： 跟踪插入缓冲区（Change Buffer）对索引页的更改。
插入缓冲区空闲列表页（IBUF_FREE_LIST）： 管理插入缓冲区（Change Buffer）使用的页。
系统页（SYS）： 存储各种内部系统数据。
事务系统页（TRX_SYS）： 存储事务系统相关信息。
文件空间头部页（FSP_HDR）： 存储表空间级别的元数据，如空间 ID、大小、可用区链表头等。
扩展描述符页（XDES）： 跟踪区（Extent）的使用情况（属于哪个段，空闲状态等）。每个 XDES 页描述 256 个区（256MB）。
BLOB 页： 存储超出行大小限制的 TEXT 或 BLOB 类型列的数据（当使用 COMPACT 或 REDUNDANT 行格式时，溢出存储）。
压缩页（COMPRESSED）： 存储使用 COMPRESSED 行格式压缩后的数据。

索引页结构如下：

低地址 0x0000 ┌─────────────——————───────————————───┐
             │     File Header(文件头)              │
             ├───────────────────——————──————————──┤
             │     Page Header(页头)                │
             ├────────────────────——————————————───┤
             │   Infimum/Supremum(最小与最大记录)    │
             ├────────────────────——————————————───┤ 
             │                                     │
             │      User Records(用户记录)          │ → 记录增长方向
             │      (增长方向 ↓)                    │
             │                                     │
             │                                     │
             │      Free Space（空闲空间）           │
             │                                     │
             ├─────────────────────——————————————──┤ ← 页目录增长方向 ←
             │   Page Directory(页字典)             │
             │      (增长方向 ↑)                    │
             ├──────────────────────——————————————─┤
高地址 0x3FFF │     File Trailer(页尾)              │
             └─────────────────────————————————─——─┘

文件头 (File Header - 38B)： 页号、前后页指针、LSN、校验和等元数据、定位页在表空间中的位置
页头 (Page Header - 56B)：槽数量(PAGE_N_DIR_SLOTS)、堆顶指针、记录数、垃圾空间大小、页在B+树中的层级（叶子节点为0）
系统记录：Infimum：逻辑最小记录（固定位置）、Supremum：逻辑最大记录（固定位置）两者为页内记录提供边界锚点
用户记录区域：按索引顺序组织的单向链表。每条记录包含记录头（删除标志、下条记录指针、事务ID）和实际列数据（包含主键和所有列值）；新记录插入到空闲空间顶部
空闲空间：未使用的空间区域，随着记录插入向下（高地址方向）扩展、删除记录空间会被回收重用
页目录：从页尾部向上（低地址方向）增长、每个槽（2字节）指向关键记录位置（槽0 → Supremum，槽1 → 最大键记录...）主要目的是支持页内二分查找用来加速页记录的查找。每个槽代表一组记录的最大键记录。
文件尾 (File Trailer - 8B)：存储页的LSN副本用于校验页完整性（写盘/读盘时验证）

 当在页内查找一条记录时，首先对 Page Directory 进行二分查找，快速定位到记录所在的大致区间（两个Slot之间），然后只需要在很小的记录片段（通常是4-8条记录）内进行线性遍历即可找到目标记录或确认不存在。这是 InnoDB 实现高效点查和范围查询的关键机制之一。
 注意：空间用完后会触发页分裂。用户记录并不是连续的，是通过单链表组织的。

聚集索引组织表： 这是 InnoDB 最核心的特性。InnoDB 表的数据行不是按插入顺序堆叠存储的，而是按照主键（Primary Key）的值排序后，存储在 B+树索引的叶子节点中。这个主键索引就是聚集索引。如果没有显式定义主键，InnoDB 会选择一个唯一的非空索引（如果有）作为聚集索引。如果没有任何唯一非空索引，InnoDB 会在内部生成一个隐藏的 6 字节 DB_ROW_ID 作为主键，并据此创建聚集索引。基于主键的查询非常快（只需遍历 B+树），范围扫描高效，数据物理有序存储。

二级索引： 除了聚集索引外的所有索引都是二级索引。 二级索引的叶子节点存储的是该索引的列值 + 对应行的聚集索引键值（而不是指向数据行的物理指针）。通过二级索引查找数据时，需要先在二级索引 B+树中找到对应的聚集索引键值，然后再用这个键值去聚集索引 B+树中查找实际的数据行（称为回表查询）。设计良好的覆盖索引（索引包含查询所需的所有列）可以避免回表，提高查询速度。

• 行

页内部存储的是用户表的实际数据行。行的物理存储方式由行格式决定。不同的行格式在存储效率、支持和功能上有所不同。行格式如下：

REDUNDANT：古老的格式，兼容性最好，但空间效率最低。
COMPACT (默认)：比 REDUNDANT 更节省空间，存储了行的元数据（列是否 NULL、可变长列长度等）。
DYNAMIC (推荐)：现代默认格式（MySQL 5.7+）。对于可变长列（VARCHAR, VARBINARY, TEXT, BLOB），它只在行内存储一个 20 字节的指针指向溢出页存储的实际数据（如果数据太大放不下行内）。这避免了 COMPACT/REDUNDANT 中可能导致行内存储碎片化的 768 字节前缀问题。支持更大的行（页大小限制更少）。支持索引键前缀最大 3072 字节。
COMPRESSED：在 DYNAMIC 行格式的基础上增加了页级别的压缩（使用 zlib）。需要独立的表空间 (innodb_file_per_table=ON)。节省磁盘空间，但增加 CPU 开销。

dynamic行格式如下：

┌──────────────────────────────────────────────────────────────────────┐
│                InnoDB 数据页中的一行 (DYNAMIC 格式，8.0默认)            │
├──────────┬──────────┬───────────┬─────────┬───────────┬──────────────┐
│ 记录头    │ 事务ID    │ 回滚指针   │ 列1数据  │ 列2数据    │ ...       │
│ (5字节)   │ (6字节)   │ (7字节)   │ (固定)   │ (可变长度) │           │
├──────────┼──────────┼───────────┼─────────┼───────────┼───────────┤
│ 头信息    │ DB_TRX_ID│DB_ROLL_PTR│ 固定长度 │ 长度+数据  │ 可变长度列│
│ (包含:    │          │           │ 列数据   │ (若未溢出) │ 的20字节  │
│ 列数量、  │          │           │         │           │ 溢出指针  │
│ 记录状态) │          │           │         ├───────────┤ (若溢出)  │
│ NULL位图  │          │           │         │ 20字节指针│           │
│          │          │           │         │ (若溢出)  │           │
└──────────┴──────────┴───────────┴─────────┴───────────┴───────────┘
                                                  │
                                                  │ 当可变长度列数据 > 阈值（约8KB）时
                                                  ▼
┌───────────────────────────────────────────────────┐
│                   溢出页 (Off-page)                │
├───────────────────────────────────────────────────┤
│ 实际的长数据内容 (BLOB/TEXT/VARCHAR)                │
│ 可能跨多个页                                        │
└───────────────────────────────────────────────────┘

记录头 (5字节)：包含元信息：列数量、下一条记录位置、记录状态（已删除/正常）、记录类型（DYNAMIC=4）
系统列：DB_TRX_ID (6字节)：最后修改的事务ID,DB_ROLL_PTR (7字节)：MVCC 回滚指针
数据存储规则：固定长度列（如 INT, DATE）直接内联存储、可变长度列（如 VARCHAR, BLOB）若数据 ≤ 页阈值（≈页大小一半，默认8KB）则直接内联存储（长度前缀+数据）若数据 > 阈值 → 行内只存 20字节指针（指向溢出页）
溢出页特性：溢出数据存储为单链表，每页保留部分元信息，实际数据长度存储在指针中（行内不存768字节前缀）
NULL位图：精确显示位置和计算方式（每8列占用1字节）

逻辑存储特性

• 多版本并发控制（MVCC） ：InnoDB通过维护数据的多个版本实现并发事务的隔离性，版本信息存储在回滚段（Undo Log） 中，支持事务回滚和一致性非锁定读。在后面的解决方案和流程将详细介绍。
• 缓冲池（Buffer Pool） ：InnoDB将热点数据和索引缓存到内存中，加速数据访问，减少磁盘I/O。在后面的解决方案和流程将详细介绍。

事务与日志

• Redo Log ：用于持久化事务操作，确保崩溃恢复时数据的一致性。在后面的解决方案和流程将详细介绍。
• Undo Log ：除了支持MVCC外，还用于事务回滚操作。在后面的解决方案和流程将详细介绍。

系统文件

文件

.ibd (InnoDB Data File / Tablespace File)

• 功能： 存储 特定 InnoDB 表的数据和索引。
• 说明： 当 innodb_file_per_table 参数启用时（MySQL 5.6 及以后版本默认开启），每个 InnoDB 表会有自己独立的 .ibd 文件。这个文件包含了该表的所有行数据、二级索引以及表的元数据（如表结构定义信息）。如果该参数关闭，所有表的数据和索引会存储在共享的系统表空间文件（通常是 ibdata1）中。

.dblwr (Doublewrite Buffer Files)

• 功能： 提供 数据页写入的崩溃安全机制，防止因部分页写入（Partial Page Writes）导致的数据损坏。
• 说明： InnoDB 在将数据页（通常是 16KB）写入数据文件（.ibd 或 ibdata1）之前，会先将这些页连续地写入到双写缓冲区文件（通常是 ib_*.dblwr）。如果 MySQL 在写入过程中崩溃，恢复时 InnoDB 可以检查数据文件中的页是否完整（校验和验证）。如果发现损坏的页（部分写入），则可以从双写缓冲区中恢复该页的正确副本。这是保证 InnoDB 存储引擎数据完整性的关键机制。通常有两个文件（如 ib_16384_0.dblwr, ib_16384_1.dblwr）用于循环写入。

binlog.00000x (Binary Log File)

• 功能： 记录 所有对数据库内容进行修改的 SQL 语句（DDL 如 CREATE/ALTER/DROP 和 DML 如 INSERT/UPDATE/DELETE）或行更改事件，以二进制格式存储。
• 说明： 这是 MySQL 二进制日志的实际数据文件之一。文件名后缀的数字（000018）是顺序递增的。主要用于：
• 主从复制 (Replication)： 从库（Slave）通过读取主库（Master）的 binlog 文件来重放变更，实现数据同步。
• 时间点恢复 (Point-in-Time Recovery - PITR)： 结合全量备份和 binlog，可以将数据库恢复到备份之后的任意时间点。
• 审计： 可以解析 binlog 来查看数据库的历史变更。

binlog.index (Binary Log Index File)

• 功能： 记录 当前有效的二进制日志文件列表。
• 说明： 这是一个纯文本文件，里面按顺序列出了 MySQL Server 当前正在使用或保留的所有 binlog 文件的完整路径（如 ./binlog.000016, ./binlog.000017, ./binlog.000018）。MySQL 服务器启动、进行复制或执行 FLUSH LOGS 等操作时，会参考这个文件来确定下一个 binlog 文件名以及当前有哪些 binlog 文件可用。

relay-bin.xxxxxx 中继日志数据文件

• 功能： 存储从主库接收到的、尚未被 SQL 线程执行的二进制日志事件（Binary Log Events）的原始数据。

• 说明：

  • 格式与 Binlog 相同： 这些文件在物理格式上与主库的 binlog.0000x 等二进制日志文件完全一致。它们包含的是从主库传输过来的原始二进制日志事件的副本。
  • 数据中转站： 这是 I/O Thread（接收线程）的工作成果。I/O Thread 连接到主库，读取主库的 Binlog 事件，并将这些事件按接收顺序顺序写入到本地的 relay-log.xxxxxx 文件中。
  • SQL Thread 的读取源： SQL Thread（应用线程）的工作就是读取 relay-log.xxxxxx 文件中的事件，解析它们（识别出是 INSERT、UPDATE、DELETE 等语句或行更改事件），并在从库数据库上重放（Replay） 这些操作，从而使从库的数据与主库保持同步。
• 位置信息： SQL Thread 当前正在读取和执行的事件位置记录在 relay-log.info 文件中（注意，这是另一个关键文件，记录的是应用进度，不是文件列表）。

relay-log.index中继日志索引文件

• 功能： 记录当前从库服务器上存在的、按顺序排列的所有中继日志数据文件（relay-log.xxxxxx）的完整路径列表。

• 说明：

  • 文件列表目录： 这是一个纯文本文件。它的内容就是一行一个当前有效的中继日志文件的绝对路径或相对路径（相对于数据目录）。
  • 顺序至关重要： 文件中的路径严格按照中继日志文件创建的时间顺序排列。第一个文件是最早创建的，包含最旧的事件；最后一个文件是最新创建的，I/O Thread 正在写入它。
  • I/O Thread 写入依据： I/O Thread 在需要写入新事件时，会检查这个索引文件以确定当前正在写入的是哪个 relay-log.xxxxxx 文件。如果当前文件写满了，I/O Thread 会根据索引文件中的最后一个文件名递增序号来创建下一个新文件，并将新文件名追加到索引文件中。
  • SQL Thread 读取依据： SQL Thread 在启动或需要切换到下一个中继日志文件时，会读取这个索引文件以确定接下来应该读取哪个 relay-log.xxxxxx 文件（按照文件列表中的顺序）。
  • 崩溃恢复的关键： 当从库服务器重启时，I/O Thread 和 SQL Thread 都需要依赖这个索引文件来找到它们各自上次停止工作时对应的中继日志文件位置，从而继续正确地进行复制。
  • 与 relay-log.info 的区别： relay-log.index 记录的是有哪些文件以及它们的顺序。而 relay-log.info 记录的是 SQL Thread 当前在哪个文件的哪个位置执行事件（以及 I/O Thread 在主库 Binlog 的哪个位置读取）。两者都是复制状态信息的关键文件。

ib_buffer_pool (InnoDB Buffer Pool Dump File)

• 功能： 保存 InnoDB 缓冲池 (Buffer Pool) 的状态信息，用于服务器重启时的预热加速。
• 说明： 在 MySQL 正常关闭时（或在运行时手动执行 SET GLOBAL innodb_buffer_pool_dump_now=ON;），InnoDB 会将缓冲池中当前缓存的热点数据页（Page）的 ID 信息（space ID 和 page number）保存到这个文件中。当 MySQL 下次启动时（或在运行时手动执行 SET GLOBAL innodb_buffer_pool_load_now=ON;），InnoDB 会读取这个文件，并异步地将这些页预加载回缓冲池，从而加快数据库启动后达到最佳性能的速度。这避免了数据库重启后缓冲池“冷启动”的问题。

ib_logfile0, ib_logfile1 (InnoDB Redo Log Files)

• 功能： 存储 InnoDB 的重做日志 (Redo Log)，记录事务对数据页所做的物理修改。
• 说明： 这是 InnoDB 实现 崩溃恢复 (Crash Recovery) 和 Write-Ahead Logging (WAL) 机制的核心。所有未提交和已提交的事务所做的修改都会先顺序写入重做日志文件（物理日志，记录页的变化）。通常配置为两个文件（ib_logfile0, ib_logfile1）循环写入。当系统崩溃重启时，InnoDB 会读取重做日志，将尚未刷新到数据文件（.ibd / ibdata1）的修改重新应用（Redo），确保已提交事务的数据不会丢失，同时回滚未提交的事务（Undo）。它们对保证事务的持久性 (Durability) 至关重要。

ibdata1 (InnoDB System Tablespace File)

• 功能： 存储 InnoDB 的共享系统数据。
• 说明： 这是 InnoDB 的核心共享表空间文件（默认名，可以有多个）。它通常包含：
• 数据字典 (Data Dictionary)： 存储关于表、列、索引等对象的元数据。
• 双写缓冲区 (Doublewrite Buffer Storage)： 双写缓冲区的实际数据存储区域（元信息在 .dblwr 文件）。
• 变更缓冲区 (Change Buffer)： 缓存对非唯一二级索引的修改，提升插入/更新性能。
• Undo 日志 (Undo Logs)： 如果 undo 表空间没有分离（MySQL 5.7 及之前默认，8.0 可配置），则 undo 日志（用于事务回滚和 MVCC）也存储在这里。
• 用户表数据和索引： 如果 innodb_file_per_table=OFF，所有用户 InnoDB 表的数据和索引也会存储在这里（不推荐）。
• 注意： 在 MySQL 8.0 中，默认行为是将 Undo 日志分离到独立的 Undo 表空间文件（undo_001, undo_002），并且 innodb_file_per_table=ON 是默认设置，所以 ibdata1 主要存储系统结构信息、双写缓冲区和变更缓冲区。它的初始大小由 innodb_data_file_path 定义，并且通常会随着使用自动扩展（如果配置为自动扩展）。

ibtmp1 (InnoDB Temporary Tablespace File)

• 功能： 存储 InnoDB 临时表、磁盘内部临时表以及一些排序操作产生的临时数据。
• 说明： 用于处理超出内存限制的临时数据操作。例如，复杂的 GROUP BY, ORDER BY, DISTINCT, UNION 操作如果无法在内存中完成，或者用户创建的显式 InnoDB 临时表（CREATE TEMPORARY TABLE ... ENGINE=InnoDB），其数据会写入这个临时表空间。关键特性： 该文件在 MySQL 服务器启动时创建，在正常关闭时删除并重建。如果 MySQL 异常崩溃，该文件可能会被保留下来（但里面的数据已无效），下次启动时 InnoDB 会尝试清理并重建它。它的初始大小由 innodb_temp_data_file_path 定义，并且会根据需要自动扩展（可能变得非常大），但重启后会重置回初始大小。

mysql.ibd (mysql System Database Tablespace)

• 功能： 存储 mysql 系统数据库的数据和索引。
• 说明： mysql 数据库包含了 MySQL 服务器的核心系统表，如用户账号、权限信息 (user, db, tables_priv, ...)、存储过程、事件、时区信息等。在 MySQL 8.0 之前，这些表主要使用 MyISAM 引擎（文件为 .MYD, .MYI, .frm）。从 MySQL 8.0 开始，mysql 系统表默认全部迁移到 InnoDB 引擎。因此，mysql.ibd 文件就是存储这些关键系统表（如 user, global_grants, tables_priv 等）实际数据的 InnoDB 表空间文件。它对于 MySQL 的身份验证、授权和元数据管理至关重要。

undo_001, undo_002 (InnoDB Undo Tablespace Files)

• 功能： 专门存储 InnoDB 的 Undo 日志。
• 说明： 这是 MySQL 8.0 的默认行为和推荐配置。 Undo 日志用于两个主要目的：
• 事务回滚 (Rollback)： 如果事务执行失败或被显式回滚 (ROLLBACK)，需要撤销其对数据的修改。
• 多版本并发控制 (MVCC)： 提供数据的历史版本，使其他事务在一致性读（如 SELECT ... FOR SHARE/UPDATE 的非锁定读）时能看到事务开始时的数据快照，避免脏读，提高并发性。
• 在 MySQL 5.7 及之前，Undo 日志默认存储在共享的系统表空间 ibdata1 中。从 MySQL 8.0 开始，默认创建两个独立的 Undo 表空间文件 (undo_001, undo_002) 来存储 Undo 日志。这样做的好处包括：
• 更容易管理 Undo 日志的大小（每个文件有固定上限）。
• 避免 ibdata1 文件无限增长。
• 支持在线调整 Undo 表空间的数量 (CREATE UNDO TABLESPACE / DROP UNDO TABLESPACE）。
• 支持将 Undo 表空间放在不同的存储设备上。
• 这些文件的数量和大小由 innodb_undo_tablespaces (已废弃，8.0 中主要通过 CREATE/DROP UNDO TABLESPACE 管理) 和 innodb_max_undo_log_size / innodb_undo_log_truncate 参数控制。

总结分类

核心数据存储

.ibd (用户表数据/索引), ibdata1 (系统数据/元数据), mysql.ibd (系统表数据/索引)

事务日志 (保证 ACID)

• Redo Log (崩溃恢复/持久性)： ib_logfile0, ib_logfile1
• Undo Log (回滚/MVCC)： undo_001, undo_002 (8.0+ 默认) 或 ibdata1 (旧版)

复制与恢复日志

binlog.0000x (数据变更记录), binlog.index (binlog 文件列表)， relay-log.xxxxxx (从库中继日志数据文件)，relay-log.index (从库中继日志索引文件): 是目录和顺序的维护者。

崩溃安全机制 

.dblwr (防止部分页写入损坏)

性能优化

• ib_buffer_pool (缓冲池预热)
• ibdata1 中的 Change Buffer (非唯一索引更新优化)

临时数据处理

ibtmp1 (临时表/排序)

解决方案

对于实现一个关系型数据库，需要解决两个主要问题：如何满足范式要求和如何实现事务。

范式

数据库范式是一系列用于设计关系型数据库表结构的规则和标准。它们的主要目的是：

1. 减少数据冗余： 避免相同的数据在数据库中重复存储多次。
2. 消除数据操作异常： 防止在插入、更新或删除数据时出现不一致或错误。
3. 提高数据一致性： 确保数据库中的数据是准确且相互关联的。
4. 简化数据结构： 使数据库结构更清晰、更易于理解和维护。

由于现代后端软件的表设计基本上都是反范式的。这里只做简单介绍。

第一范式（1NF）

• 核心要求： 确保列的原子性，即表中的每一列都是不可再分的基本数据项。
• 解决的问题： 消除重复组。

MySQL的每一个列都有其具体的数据类型，可以设置NOT NULL， CHECK（8.0+）自定义约束等等，以此来满足第一范式的要求。

第二范式（2NF）

• 前提： 表必须首先满足 1NF。
• 核心要求： 消除非主属性对候选键的“部分函数依赖”。
• 解决的问题： 主要针对具有组合主键的表，消除部分依赖导致的数据冗余和更新异常。

MySQL通过设置主键来满足第二范式要求。

第三范式（3NF）

• 前提： 表必须首先满足 2NF。
• 核心要求： 消除非主属性对候选键的“传递函数依赖”。
• 解决的问题： 进一步消除数据冗余和操作异常。

比如：

数据冗余： 同一个客户（如C001）的“客户姓名”和“客户等级”在该客户的每个订单中都重复出现。
更新异常： 如果张三从黄金会员升级到铂金，需要更新他所有历史订单记录。漏掉一条就不一致。
插入异常： 无法添加一个尚未下过订单的新客户信息。
删除异常： 如果删除了某个客户的最后一个订单，该客户的姓名和等级信息也会丢失。

那么我们可以将用户与订单拆分为两个表，通过ID关联。MySQL通过表空间的独立性与表的独立性来满足第三范式。

我们的数据库设计一般只会做到满足第三范式

巴斯-科德范式（BCNF）

• 前提： 表必须首先满足 3NF。
• 核心要求： 消除主属性对候选键的部分函数依赖和传递函数依赖。
• 解决的问题： 解决 3NF 未能完全消除的一些极其罕见但理论上存在的异常情况，通常涉及多个候选键或复合候选键。

MySQL通过设置外键来满足此范式要求，用UNIQUE键来保证键唯一性。

第四范式

• 前提： 表必须首先满足 BCNF。
• 核心要求： 消除非平凡的多值依赖。
• 解决的问题： 处理更复杂的关系，特别是当实体间存在多对多关系，且这些关系相互独立时。

其实就是多对多关系的设计。同样的，MySQL通过主键，外键来满足第四范式要求。

事务

数据库事务有四个基本特性：ACID。MySQL的InnoDB引擎支持事务，为了实现事务做了许多设计许多工作。MySQL默认是全部开启事务的，默认事务是自动提交的，我们可以手动执行了set autocommit = 0;关闭自动提交或者执行begin;或 start transaction;也会关闭自动提交。

原子性（atomicity）

一个事务被视为一个不可分割的最小工作单元。事务中的所有操作（例如，多个SQL语句）要么全部成功完成（提交 - Commit），要么全部不执行（回滚 - Rollback）。不存在事务只部分执行成功的情况。

InnoDB引擎通过undo log机制，记录提交前的数据，使用redo log机制记录已提交的数据。使得commit落盘之前可以回滚，在commit将脏页落盘即可。

undo log通过轮转写入`undo_001`和`undo_002`文件保证不丢失。

隔离性（isolation）

并发执行的事务之间应该相互隔离，一个事务的执行不应影响其他同时执行的事务。当多个事务并发执行时，系统需要确保每个事务都感觉不到其他事务在并发执行，就像它们是串行（一个接一个）执行一样。这是为了防止并发操作导致的数据不一致问题。

并发事务会导致以下三种问题：

• 脏读 (Dirty Read)： 事务A读取了事务B修改但尚未提交的数据。如果事务B随后回滚，事务A读取到的就是“脏”的（无效的）数据。
• 不可重复读 (Non-repeatable Read)： 事务A在同一个事务内多次读取同一行数据，在两次读取之间，事务B修改并提交了该行数据，导致事务A两次读取的结果不一致。
• 幻读 (Phantom Read)： 事务A在同一个事务内多次执行相同的范围查询，在两次查询之间，事务B插入或删除了符合该查询条件的行并提交，导致事务A第二次查询看到了之前没有的“幻影行”或丢失了之前看到过的行。

数据库通过并发控制机制来实现隔离性，主要方法是锁（Locking） 和多版本并发控制（MVCC - Multi-Version Concurrency Control）。MVCC 主要解决读-写冲突，提供非阻塞的一致性读（快照读）；锁机制（特别是 Next-Key Locks）主要解决写-写冲突和配合实现防止幻读。两者协同工作实现不同隔离级别。

锁： 事务在访问数据前加锁（读锁/共享锁，写锁/排他锁），阻止其他事务进行冲突操作（如写锁阻止其他事务读写），直到锁释放（通常在事务结束）。

• 行锁 (Record Locks): 锁定索引记录（即使表没有显式索引，InnoDB 也会创建隐藏聚簇索引）。
• 间隙锁 (Gap Locks): 锁定索引记录之间的间隙，防止其他事务在间隙中插入新记录。主要用于防止幻读，在 REPEATABLE READ 隔离级别下使用。
• 临键锁 (Next-Key Locks): 行级锁 + 间隙锁的组合，锁定一个索引记录及其前面的间隙。是 InnoDB 在 REPEATABLE READ 级别下默认的加锁方式，有效防止幻读。
• 意向锁 (Intention Locks): 表级锁，表示事务稍后将在表中的某些行上请求哪种类型的锁（共享意向锁 IS / 排他意向锁 IX）。用于快速判断表级冲突，提高效率。
• 元数据锁（meta data lock，MDL）： 在表元数据存在独立表空间而非系统表空间之后，元数据锁已经和表锁的功能重叠了，目前的表锁已经用不上了。和表锁一样，有s锁和x锁两种。

MVCC： MySQL通过undo log串起版本链。通过为数据维护多个版本（快照）来实现。读操作读取事务开始时的数据快照（或特定时间点的快照），不受同时进行的写操作影响。写操作创建新版本。这种方式避免了读操作阻塞写操作（反之亦然），提高了并发性。

• 隐藏列： 每行数据有两个隐藏系统列，DB_TRX_ID (6字节)，记录最后一次修改该行数据的事务ID。DB_ROLL_PTR (7字节)：指向该行数据在 Undo Log 中的上一个版本（旧版本）的指针。通过这个指针可以构建数据的版本链。

• ReadView (读视图/一致性视图)： 在事务执行第一个快照读（如 SELECT）时创建（REPEATABLE READ级别在整个事务期间使用同一个Read View；READ COMMITTED级别在每次快照读时创建新的Read View）。Read View 包含：

  • m_ids: 创建Read View时系统中活跃（未提交）的事务ID列表。
  • min_trx_id: m_ids 中的最小值。
  • max_trx_id: 创建Read View时系统应分配的下一个事务ID（即当前最大事务ID+1）。
  • creator_trx_id: 创建该Read View的事务自身的ID（对于只读事务可能为0）。
• 可见性判断规则 (判断行数据的某个版本对当前事务是否可见)： 遍历数据行的版本链（通过 DB_ROLL_PTR），找到第一个满足以下条件的版本：

事务的隔离级别

为了平衡隔离性（数据一致性）和并发性（性能），SQL标准定义了不同的隔离级别。

• 读未提交 (Read Uncommitted)： 最低级别，直接读数据页最新版本数据。允许脏读、不可重复读、幻读。性能最高，一致性最差。 实际生产中我重来没有见过使用这一级别。
• 读已提交 (Read Committed)： 解决脏读。事务只能读取已提交的数据。但仍可能发生不可重复读和幻读。(多数数据库默认级别，如 Oracle, PostgreSQL)。每次快照读都生成新的 Read View。能看到最新已提交的数据（解决脏读，允许不可重复读和幻读）。 使用行级锁（Record Locks），只在语句执行期间持有锁（语句结束可能释放部分锁）。不持有间隙锁（允许幻读）。可以想象当另一个事务修改了数据之后，由于数据变更，第二次读的时候数据就变化了，不可重复读就发生了，也可以想象当另一个事务插入或者删除了一条记录之后，第二次读的时候就会读取到这些数据，幻读就发生了。
• 可重复读 (Repeatable Read)： InnoDB的默认级别。解决脏读和不可重复读。在同一个事务内多次读取同一数据的结果是一致的。但在某些实现（如MySQL InnoDB）下，通过MVCC可以避免大部分幻读，但严格标准下仍可能发生幻读（尤其是在范围查询+插入/删除时）。事务内第一次快照读时生成 Read View，后续所有读操作复用同一个 Read View。保证在同一个事务内多次读取同一数据的结果一致（解决不可重复读）。 使用临键锁 (Next-Key Locks)。锁定记录本身及其前面的间隙，有效防止其他事务在锁定范围内插入新记录（从而在当前读（SELECT ... FOR UPDATE/SHARE）和写操作时防止幻读）。对于快照读，由于 MVCC 和固定 Read View，本身不会看到新插入的“幻影行”。
• 串行化 (Serializable)： 最高级别。强制事务串行执行（或等效效果），完全避免脏读、不可重复读、幻读。一致性最强，但并发性能最差。 所有普通的 SELECT 语句都会被自动转换为 SELECT ... FOR SHARE（加共享锁），使用更强的锁（Next-Key Locks），读写操作相互阻塞严重。

这里需要解释间隙锁是如何解决插入幻读，临键锁是如何同时解决插入和删除幻读的。比如我们有sql如下：

SELECT ... WHERE id BETWEEN 5 AND 10

对于间隙锁来说，他会锁住5到10之间的所有空隙以阻止插入。对于临键锁来说，它会在锁住5到10之间的空隙的同时锁住所有5到10已存在的行，同时阻止插入与删除，这样就彻底解决了幻读。

持久性（durability）

 一旦一个事务成功提交（Commit），它对数据库所做的修改就是永久性的。即使发生系统故障（如断电、崩溃），这些修改也不会丢失。提交后，数据必须被安全地存储到持久化存储介质（通常是磁盘）。

MySQL通过Redo Log 、 Doublewrite Buffer 和 Write-Ahead Logging (WAL, 预写日志)。

Write-Ahead Logging (WAL): 这是持久性的核心原则。即，修改操作先记录操作日志（这里是redo log），再做实际修改。

Redo Log（ib_logfile0/1文件）:

• 记录的是数据的物理变化（例如“在某个数据页的某个偏移量处写入某些字节”），采用顺序追加写入的方式，速度极快。
• 事务提交 (COMMIT) 时，InnoDB 会强制将包含该事务修改的 Redo Log 记录刷新到磁盘（innodb_flush_log_at_trx_commit 参数控制刷新策略，默认 1 表示每次提交都刷盘，保证最强持久性）。即使此时数据页本身还在内存（Buffer Pool）中未被刷盘。
• 发生崩溃重启后，InnoDB 会读取 Redo Log，重放 (Redo) 所有已提交事务的修改（即使这些修改还没写到数据文件），以及撤销 (Undo) 所有未提交事务的修改（利用 Undo Log），从而将数据库恢复到崩溃前的一致状态。

Doublewrite Buffer (双写缓冲区，.dblwr文件):

• 解决的问题： 部分写失效 (Partial Page Write)。操作系统写磁盘是以块（如 4K）为单位的，而 InnoDB 数据页是 16K。如果在写入一个 16K 数据页的过程中系统崩溃，可能导致页只写了一部分（如 4K），造成数据页损坏。仅靠 Redo Log 无法修复这种物理损坏（因为 Redo Log 记录的是变化，需要基于一个完好的原始页来重放）。
• 原理： 在将脏页刷写到磁盘的实际数据文件位置之前，InnoDB 会先将这些脏页拷贝到 Doublewrite Buffer（内存中的一块区域 + 磁盘上的共享表空间中的一个连续区域）。然后，首先将 Doublewrite Buffer 中的脏页顺序、连续地写入磁盘上的 Doublewrite 区域。之后，再将脏页离散地写入到它们在表空间中的实际位置。
• 崩溃恢复： 如果系统在写实际数据文件位置时崩溃，重启后发现某个页损坏，InnoDB 可以从 Doublewrite 区域中找到该页的一个完好副本，用它来覆盖损坏的页。然后再应用 Redo Log 来恢复该页的最新状态。

一致性（consistency）

事务执行前后，数据库必须从一个有效的状态转换到另一个有效的状态。事务的执行不能破坏数据库的完整性约束（Integrity Constraints）。这些约束包括：

• 实体完整性： 主键唯一且非空。
• 参照完整性： 外键引用必须有效（引用存在的主键值或为NULL）。
• 用户定义的完整性： 业务规则，如账户余额不能为负数、年龄必须在合理范围内等。

原子性、隔离性、持久性是数据库内部提供的基础机制，它们共同确保事务执行不会破坏数据库的内部一致性状态（如主键唯一、外键约束、数据类型等）。

数据库约束 (Declarative Constraints)： InnoDB 在 Server 层和引擎层强制实施：

• 主键约束 (Primary Key)： 通过聚簇索引保证唯一且非空。
• 唯一约束 (Unique Key)： 通过唯一索引保证。
• 外键约束 (Foreign Key)： InnoDB 支持外键，会在数据修改时检查引用完整性（需要父表存在对应记录或为NULL）。
• 数据类型/长度约束： 由 Server 层检查。
• NOT NULL 约束： 由 Server 层和引擎层检查。

应用层逻辑： 一致性最终也依赖于应用层的事务设计逻辑。数据库保证了“如果数据在事务开始前是一致的，并且事务本身的操作逻辑是正确的（符合业务规则），那么事务结束后数据也将是一致的”。例如，转账事务中确保 A 账户余额 >= 0 和 A 减少额 = B 增加额，这需要应用在事务中编写正确的扣款和加款逻辑，数据库本身无法知晓这些业务规则。数据库层面提供了事务机制保证业务一致性，但是一致的前提是使用上了数据库的这些特性（开启了事务）和业务逻辑正确。

大数据表拆分

互联网企业数据量很大， MySQL单表数据达到一定规模之后会性能下降。于是大数据表的横向拆分就成了一种必然趋势。

分区表

MySQL分区表是一种逻辑上是一张表，物理上数据存储在多个独立文件/段中的技术。它将一张大表的数据，根据预定义的规则（分区键），水平切分成多个更小、更易管理的部分，这些部分称为分区。对应用程序是透明的，应用程序像操作普通表一样操作分区表（SELECT, INSERT, UPDATE, DELETE 等语句无需修改）。

分区表的优势：

• 提升管理性： 更容易管理超大表的数据（如按时间删除旧分区比DELETE删除大量数据高效得多，直接DROP PARTITION）。

• 提升查询性能：

  • 分区修剪： 查询优化器能够识别查询条件是否匹配分区键。如果匹配，它只会扫描相关的一个或少数几个分区，而跳过其他无关分区，大幅减少 I/O 和数据扫描量。
  • 并行查询： 某些查询（特别是聚合查询）可以在多个分区上并行执行（取决于存储引擎和查询类型）。
• 提高可用性： 单个分区的损坏或维护（如备份、恢复）可能不影响其他分区上数据的访问（虽然 MySQL 本身不提供分区级别的强隔离可用性，但操作上更方便）。

分区类型：

• RANGE 分区： 基于分区键值属于某个连续范围进行分区（最常用，尤其适合时间序列数据）。例如按年、月分区。如下，一年一个分区。

CREATE TABLE sales (
    order_date DATE,
    ... other columns ...
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN (2023),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

• LIST 分区： 基于分区键值匹配一个离散值列表进行分区。例如按地区、状态码分区。

CREATE TABLE employees (
    region_id INT,
    ... other columns ...
) PARTITION BY LIST (region_id) (
    PARTITION p_north VALUES IN (1, 2, 3),
    PARTITION p_south VALUES IN (4, 5, 6),
    PARTITION p_west VALUES IN (7, 8, 9)
);

• HASH 分区： 对分区键应用一个散列函数，根据计算结果均匀分布数据到不同分区。主要目的是均衡负载。

CREATE TABLE website_logs (
    user_id INT,
    log_time DATETIME,
    ... other columns ...
) PARTITION BY HASH(user_id) PARTITIONS 4;

• KEY 分区： 类似于 HASH 分区，但使用 MySQL 服务器内置的哈希函数（或直接使用主键/唯一键），且分区键只能是整数或 NULL。
• COLUMNS 分区： RANGE 和 LIST 分区的扩展，允许使用多个列作为分区键，并支持非整数类型（如 DATE, DATETIME, CHAR, VARCHAR）。

注意： 选择合适的分区键至关重要，它应出现在经常用于过滤 (WHERE) 或连接 (JOIN ON) 的查询条件中，才能有效利用分区修剪。 分区表上的主键或唯一键必须包含分区键的所有列。这是为了保证约束在整个表范围内的唯一性。 不恰当的分区（如分区键选择不当或分区数过多）可能导致性能下降（元数据开销、打开表句柄开销等）。

由于主键或者唯一键得包含分区键，所以在实际生产中通常需要类似ID+createtime(不能修改)才能达到分区目的，这点限制也使得该功能被人诟病。因为MySQL无法做到主键和唯一键在整个逻辑表上是唯一的，只能做到在分区表上是唯一的，这就要求用户在设置分区的时候将分区键页包含到主键里面。

MySQL每个表分区都是一个独立的.ibd文件，通常是 表名#P#分区名.ibd。例如，表 sales 有一个名为 p2023 的分区，其数据文件可能叫 sales#P#p2023.ibd。在读写数据的时候InnoDB只需读写相关分区表就行，大大提高了效率。

一个按年分区的案例：

1. 初始分区: 创建带年份分区和 MAXVALUE 的表

CREATE TABLE sales_data (
    id INT NOT NULL AUTO_INCREMENT,
    create_time DATETIME NOT NULL, -- 关键分区列
    product_id INT,
    amount DECIMAL(10, 2),
    PRIMARY KEY (id, create_time) -- 主键必须包含分区键 sale_date
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(create_time)) (
    PARTITION p2020 VALUES LESS THAN (2021), -- 存放 2020 年及更早（如果没有更早分区）
    PARTITION p2021 VALUES LESS THAN (2022), -- 存放 2021 年数据
    PARTITION p2022 VALUES LESS THAN (2023), -- 存放 2022 年数据
    PARTITION p2023 VALUES LESS THAN (2024), -- 存放 2023 年数据
    PARTITION p_max VALUES LESS THAN MAXVALUE -- 存放 2024 年及以后的数据
);

2. 每年就行二次分区：在每年年初，自动创建去年分区来存放去年的数据，并将 p_max 重置为存放当年年及以后的数据。

ALTER TABLE sales_data REORGANIZE PARTITION p_max  INTO (
    PARTITION p2025 VALUES LESS THAN (2025 + 1), 
    PARTITION p_max VALUES LESS THAN MAXVALUE
)

其他方案

其他方案多为应用层方案，或者通过代理达到分区效果。常见的方案有多租户与分库分表。

多租户

常见于需要隔离用户资源的情况。对于应用层，用户使用同一套服务，但是对于他们所访问的资源就行隔离。他有两个层面的隔离，一是表隔离，二是库隔离。

表隔离：各个租户的表结构是一致的，使用的数据库也是同一个，但是表名区分不同租户数据。一种方式是表名接上租户id后缀或者前缀，用户访问资源的时候带上租户id，这样在应用层就可以动态的路由到不同的表了。

库隔离：在数据库层面就行隔离，如果用户需要独享数据库实例或者需要为用户提供的是一整个环境上的隔离的时候会使用到，这个和各大办公软件提供企业版本类似，每个企业会有自己的环境。此方案的常用做法是是多数据源的动态路由，用户带上租户id之后，应用根据id访问他们专属的数据源。

多租户也可以缓解大客户与小客户的资源竞争，比如库中一半的数据来自某大客户，那么隔离该客户的环境是一种合理的解决方式。

分库分表

分库分表是常见的方案，在大数据场景需要使用到。常见的实现是shardingsphere和Mycat。由于shardingsphere的灵活性，这里推荐使用。

分库：分库的主要目的是实现读写分离、负载均衡等需求。对于Mycat，其实现原理是应用提供连接一个代理服务，代理服务负责访问底层数据库，应用如果想路由到底层库，需要在SQL上添加标识注释:

/*!mycat:dataNode = dn1 */ 
SELECT * FROM t

对于shardingsphere，它提供两个层面的访问，一是在应用层使用Sharding-JDBC，二和Mycat一样应用连接Sharding-Proxy服务，由该服务访问底层数据库。Sharding-jdbc可以通过Hint API来自定义路由到什么库什么表。

hintManager.setDatabaseShardingValue(3);
hintManager.setMasterRouteOnly();

分表：分表主要是为了拆分大表，降低单表数据规模。底层都是通过分片键路由算法将SQL路由到物理表，表分片逻辑与分片算法有关。

注意： shardingsphere支持XA事务（分布式事务），两段提交。

主备与容灾

现代数据库还有两个需求：主备与容灾（高可用）。对应要实现的就是主从复制与高可用。

复制（Replication）

主从复制 (Master-Slave Replication) 是许多应用需求的基础，比如读写分离、容灾等等。

MySQL的主从复制依赖binlog机制：主节点将数据变更（写操作）记录到二进制日志（Binary Log, Binlog） 中，- 从节点（一个或多个）通过 I/O 线程连接到主节点，请求并接收 Binlog 事件，从节点将接收到的 Binlog 事件写入本地的中继日志（Relay Log），从节点的 SQL 线程读取 Relay Log 中的事件，并在本地数据库上重放（执行）这些事件，从而保持与主节点的数据同步（通常是异步的）。

那么对于从节点我们可以赋予其作为只读节点的功能、或者作为备份节点亦或者作为备用主节点。

复制类型（影响数据一致性和性能）

• 异步复制（默认）： 主节点提交事务后立即返回给客户端，不等待从节点确认。性能最好，但数据丢失风险最高（主节点宕机时，未传送到从节点的数据会丢失）。
• 半同步复制（Semisynchronous Replication）： 主节点提交事务时，至少需要等待一个从节点确认已收到 Binlog 事件（不一定已应用）后才返回给客户端。平衡了数据一致性和性能，降低了数据丢失风险，业务上一般选择此方式。
• 组复制（Group Replication - MGR 核心）： 基于 Paxos 协议的同步复制变体，确保事务在组内大多数节点上达成一致后才提交。提供强一致性（数据无损），但写延迟较高。

集群（Cluster）

主从复制结合故障转移（Failover）和故障恢复（Failback） 可以达到高可用的目的，需要额外的机制来检测故障和执行切换。

MySQL Group Replication (MGR)

MySQL 组复制（Group Replication）是 MySQL 官方提供的基于原生复制技术构建的高可用、高扩展、强一致性的分布式数据库服务解决方案。它代表了 MySQL 复制技术的一个重要演进方向，旨在提供开箱即用的多主（Multi-Primary）或单主（Single-Primary）复制架构。

MGR作为 MySQL Server 的一个插件（group_replication.so）实现，与 MySQL 生态（如 InnoDB, MySQL Router, MySQL Shell）深度集成。

 基于 Paxos 协议，组内所有节点会对所有事务（包括冲突检测结果）达成一个全局一致的顺序。只有在大多数节点（N/2+1）同意该事务且它在全局顺序中被确定时，发起该事务的节点才会真正提交它，其他节点随后按顺序应用该事务。

工作模式：

• 单主模式：只有一个节点（Primary）可以处理读写事务，其他节点（Secondaries）处于只读状态，自动复制 Primary 的数据。 如果 Primary 故障，组复制会自动从剩余的在线节点中选举出一个新的 Primary（基于 Paxos）。客户端需要感知故障转移（通常借助中间件如 MySQL Router）。避免多主模式下的写冲突问题，简化应用逻辑
• 多主模式: 组内所有节点都可以处理读写事务，真正的“多点写入”。需要应用层处理潜在的写冲突（虽然组复制在事务认证阶段会检测冲突并回滚其中一个事务，但应用需要重试），对网络要求更高。对写扩展性要求极高，且应用能优雅处理冲突或冲突概率极低的场景。

总之，由于实现了Paxos共识协议，使得MySQL具有了分布式的能力。可以自动进行故障转移，强数据一致性等等分布式系统要求的能力。

MySQL InnoDB Cluster

MySQL InnoDB Cluster 是 MySQL 官方提供的一个完整、集成的、开箱即用的高可用和可扩展数据库解决方案。它并不是一个独立的产品，而是将多个成熟技术栈无缝整合形成的“全家桶”，旨在简化 MySQL 高可用集群的部署、管理和运维。

正如组复制时构建再复制之上的，Cluster是构建再组复制上的。组复制提供的数据复制、强一致性、自动故障检测与故障转移和多点写入的能力。再此基础上mysql为了实现客户端无感路由提供了MySQL Router中间件，为了更加方便的官管理集群，提供了mysql shell。三者结合就是MySQL InnoDB Cluster的基础。

MySQL Shell：提供强大的命令行交互环境 (mysqlsh)。

• AdminAPI： 这是核心！MySQL Shell 内置了专门用于管理 InnoDB Cluster 的 dba 和 cluster 对象及其方法。通过简单的 JavaScript 或 Python 脚本，即可完成集群的创建、配置、节点添加/移除、状态监控、故障切换等几乎所有管理操作，极大简化了复杂性。
• 分布式恢复协调： 简化新节点加入或故障节点恢复时的数据同步过程（通常利用 MySQL 的 Clone 插件进行高效数据拷贝，也就是说复制过程不再使用低效的binlog一条条复制）。通过直接复制数据页和Redo Log实现快速克隆。

MySQL Router：是一个轻量级应用程序，部署在应用服务器和数据库集群之间智能路由中间件。

• 连接路由： 根据配置的路由策略（读/写、读负载均衡），自动将应用程序的连接请求引导到集群中合适的 MySQL 实例上（例如，写请求到 Primary，读请求到 Secondary 或 Primary）。
• 故障转移透明化： 当集群发生故障转移（Primary 切换）时，MySQL Router 能够自动检测到新 Primary，并将后续的写请求无缝路由到新主节点。对应用程序来说，数据库连接看起来基本是持续可用的（需要应用具备基本的连接重试机制），极大降低了应用层处理故障转移的复杂性。
• 负载均衡： 对读请求可以在可用的 Secondary 节点上进行负载均衡。

第三方方案

HAProxy：HAProxy（High Availability Proxy）是一款专注于 高性能、高可用性 的负载均衡器，支持 TCP（四层）和 HTTP（七层）流量分发。通过TCP层进行代理可以实现MySQL 负载均衡。

ProxySQL：ProxySQL 是一款高性能的 MySQL 中间件代理，核心目标是为 MySQL/MariaDB 集群提供高可用、查询优化及流量管理功能。其设计注重灵活性和动态性，支持在线配置更新和复杂路由规则。

• 接收客户端 MySQL 协议请求，解析连接和 SQL 语句
• 执行 规则匹配：根据 mysql_query_rules 表定义的规则（如正则匹配 SQL 语句、用户名、Schema）决定路由路径
• 支持查询重写、缓存检查、黑名单过滤等操作
• 维护与后端 MySQL 的长连接池，复用连接避免频繁建立/断开开销，显著提升吞吐量
• 管理后端 MySQL 节点分组（如写组 hostgroup_id=0、读组 hostgroup_id=1），结合负载均衡策略（如轮询、最少连接）分发请求
• 实时探测后端节点健康，异常节点自动隔离，恢复后重新纳入

流程

前文我们已经介绍了各个组件的作用。MySQL为了实现数据库标准都做了哪些工作，如何实现这些标准的。

下面将详细介绍MySQL的各个流程。

服务器启动与崩溃恢复

1. 配置文件加载

配置的应用优先级是：命令行参数，~/.my.cnf（用户目录下的配置）、/etc/my.cnf和/etc/mysql/my.cnf（全局配置）。优先级高的配置会覆盖掉优先级底的配置。

windows下配置文件是安装目录下的my.ini。 使用命令 mysqld --verbose --help | grep -A 1 "Default options" (Linux) 或 mysqld --verbose --help (Windows) 可以查看 MySQL 服务器在启动时会读取哪些配置文件。

我们需要格外注意这些配置：

• datadir：数据目录，windows默认安装目录下的data目录，linux默认/var/lib/mysql，mac默认/usr/local/var/mysql，这是存储所有数据库文件（表结构、数据、索引、日志文件等）的核心位置。这是最重要的路径之一。
• port：端口默认3306
• socket：socket文件路径，这是本地客户端（与 MySQL 服务器在同一台机器上）用来连接服务器的文件路径。linux默认/var/run/mysqld/mysqld.sock，mac默认/tmp/mysql.sock，windows不支持。
• log-error： 这是 MySQL 记录启动、运行和关闭过程中的错误、警告和关键信息的主要日志文件。Ubuntu默认/var/log/mysql/error.log，windows默认{datadir}{hostname}.err，mac默认/usr/local/var/mysql/{hostname}.err
• pid-file：进程ID文件，linux默认/var/run/mysqld/mysqld.pid，windows默认{datadir}{hostname}.pid，mac默认/usr/local/var/mysql/{hostname}.pid。

2. 初始化核心组件

   • 内存分配：初始化innodb_buffer_pool，线程缓存等。
   • InnoDB引擎初始化：检查表空间文件（ibdata1旧版本，新版本的表空间在各自表文件内）和日志文件（ib_logfile*，redolog文件）。执行崩溃恢复（Crash Recovery）。
   • 权限系统加载：从 mysql 数据库（user、db 等表）加载用户权限到内存。

崩溃恢复过程（应用 Redo Log 重做已提交事务、回滚未提交事务（使用 Undo Log））：

• 定位恢复起点（Checkpoint LSN）：InnoDB定期将内存中的脏页（修改过的数据页）刷盘，并在日志中记录最后一次刷盘的位置（LSN，Log Sequence Number）。从Redo Log中读取最近的检查点LSN（存储在ib_logfile0的头部），从此位置开始重放日志。
• Redo log重放（也称前滚）：Redo Log记录的是物理操作（如“在Page 5偏移量100处写入字节'ABC'”），首先我们根据定位到的起点向后读取操作，然后将操作应用到对应的数据页（无论该页是否在崩溃前已刷盘），恢复脏页与undolog，读取过程跳过LSN之前的redolog。
• Undo Log回滚（后滚）：Undo Log记录的是逻辑操作（如“DELETE操作的反向是INSERT”）。首先扫描Undo Log，找到所有崩溃时活跃的事务（状态为ACTIVE，未提交的事务），然后按事务生成逆向操作，撤销其对数据的修改，最后回滚完成后，Purge线程删除不再需要的Undo日志。
• 验证与修复：由于刷盘过程存在崩溃风险，所以脏页落盘时是先将脏页写入双写缓存文件（.dblwr）再刷脏页到库中的。当脏页不完整的时候（比如刷了页16k，刷了4k就断电了），需要从双写缓存中恢复该脏页重新刷盘。

注意： 这里由几个要点需要注意，一是脏页不论有没有落盘，重复应用同一redolog都不会导致错误，因为做了幂等校验，页头有FIL_PAGE_LSN记录了该页最后的LSN。二是落盘的脏页并不代表事务已提交，事务未提交的脏页在buffer pool满了之后也会落盘，这是undolog在崩溃恢复时的应用场景。

mysql8.0可以设置innodb_parallel_redo_threads加速redolog重放过程。innodb_flush_log_at_trx_commit可以设置redolog文件的刷盘周期，默认是1，每次commit都刷盘，可选0，每秒刷盘（可能会丢失一秒数据），也选2，写入OS缓存由操作系统选择何时刷盘。

3. 启动网络服务

绑定端口，默认是3306；创建socket文件，如果需要的话。

4. 后台线程启动

5. 插件与功能初始化

加载插件，如半同步复制、审计插件，，启动事件调度器（若 event_scheduler=ON）， 初始化复制模块（如配置了主从复制）。

6. 准备接受客户端连接

创建连接管理器，分配线程或线程池处理请求。启动监听，等待客户端连接。

7. 完成启动

写入 PID 文件（如 /var/run/mysqld/mysqld.pid），写入启动完成的日志。

客户端连接

1. TCP连接

客户端发起TCP连接（端口时启动流程中监听的3306端口），就行tcp连接的三次握手。

2. 服务器响应连接请求

3. 客户端身份认证（其实是这相当于MySQL应用层的握手）

   • 协议初始化：服务器向客户端发送带有 随机数（Nonce） 和标识支持的认证方式（如 caching_sha2_password、mysql_native_password）的握手包（Initial Handshake Packet）
   • 客户端构造并发送认证信息：客户端结合用户名、密码和 Nonce 计算哈希值，生成认证响应包（Authentication Packet）发送给服务器。
   • 服务器响应认证结果：服务器对比存储的密码哈希（如 mysql.user 表）与客户端计算的哈希值
4. 连接参数协商

客户端发送连接参数（如字符集 utf8mb4、时区 +08:00、SQL 模式等）、SSL/TLS 加密进行TLS握手等等。

5. 连接完成

服务器返回 OK Packet，确认连接就绪。服务器为该连接分配专属线程（或线程池资源）及内存缓冲区。客户端进入命令交互模式。

查询语句

经过上面的连接过程，我们的客户端已经连接上了MySQL服务器，并且进入的命令交互模式。解下来我们看看一条查询SQL是如何执行的。

由于查询缓存已移除，这里的过程不包含查询缓存命中。

1. 查询缓存（8.0移除，如果sql和key对的上就直接返回查询结果）
2. 分析器处理

在架构章节我们介绍过，分析器包含词法分析、语法分析和语义分析，最初生成抽象语法树（AST,lex结构）供优化器使用。

• 词法分析将SQL语句拆分成关键字与token，如果关键字错误则会抛错给客户端
• 语法分析会检查语法的合法性，比如查询语句没有表，where之和没有条件等等会抛错给客户端
• 语义分析进行语义合法性校验与初步的优化，比如表与不存在，select的字段不在group by里面就会抛错给客户端。对于1+1这样的值运算也会简化计算好。

3. 优化器处理

优化器会生成最优的执行计划。在上面的章节有介绍，这些优化包括两个部分：逻辑优化与物理优化。

• 逻辑优化会通过关系代数等价变换改写失SQL，比如将子查询改写成连表、将where条件提前到join条件，化简表达式与常量传播等等。
• 物理优化会基于代价模型选择最优的物理操作算法，比如根据统计得出是否需要使用索引和用哪个索引，使用什么连表算法与什么连表顺序等等。

4. 执行器执行

执行器调用存储接口取数，这里会检查用户是否有权限访问，然后调用存储引擎API取数。

5. 存储引擎取数

读取Buffer Pool：如果Buffer Pool存在目标页则直接取数返回，如果不存咋则需要将该页先加载到Buffer Pool在取数返回。

Buffer Pool主要包括以下三个部分：

• 页数组 (Array of Frames)

 Buffer Pool 本质上是一个由固定大小的连续内存块（称为 frames）组成的数组。每个 Frame 的大小通常等于 InnoDB 页的大小（默认 16KB）。因此，Buffer Pool 的总大小（由 innodb_buffer_pool_size 配置）必须是 (innodb_page_size) * N，其中 N 是 Frame 的数量。每个 Frame 用于存放一个从磁盘读取的完整 InnoDB 页（数据页或索引页）的副本。

• 控制块 (Control Blocks / Buffer Control Blocks)

这是Buffer Pool 之外的额外内存。每个 Frame 都有一个对应的控制块（位于 Buffer Pool 内存区域之外单独分配的内存中）。控制块包含了管理其对应 Frame 中缓存页所需的所有元数据：

• space_id 和 page_no： 标识该页属于哪个表空间（Tablespace）以及在该表空间中的具体页号。
• 指向 frame 的指针： 指向实际存储页数据的内存位置。
• 页状态： 例如 FREE（空闲，未使用）、CLEAN（干净，与磁盘一致）、DIRTY（脏，已被修改，与磁盘不一致）、READ_AHEAD（预读页）等。
• 访问信息： 访问时间戳、访问计数等（用于 LRU 算法）。
• 锁和闩锁信息： 管理对该页的并发访问控制。
• 刷新信息： 记录 LSN（Log Sequence Number）等，用于控制脏页刷盘。
• 链表指针： 最重要的部分之一，包含了指向各种管理链表（见下文）中前驱（prev）和后继（next）节点的指针。控制块通过这些指针将自己“链接”到不同的链表中。
• 管理链表 (Management Lists)

这些链表是逻辑上的，通过控制块中的指针连接起来，用于高效地管理 Buffer Pool 中的页。

Free List:  管理所有当前空闲的 Frame（即 Frame 中的页处于 FREE 状态）。当需要从磁盘加载一个新页到 Buffer Pool 时，InnoDB 优先从 Free List 的头部获取一个空闲 Frame。当页从 Buffer Pool 中被淘汰（非脏页）或刷盘后，其对应的 Frame 会被放回 Free List（通常放在头部）。Free List是 Buffer Pool 分配空间的直接来源。如果 Free List 为空，就需要触发页淘汰（从 LRU List 中移除旧页）。

LRU List（Least Recently Used List）：最近最少未使用链。管理所有已被加载到 Buffer Pool 中的页（包括 CLEAN 和 DIRTY 页），并按照最近最少使用原则进行排序，用于决定当 Free List 为空时淘汰哪些页。 

InnoDB 使用一种优化的 LRU 算法，将 LRU List 逻辑上划分为两个区域（Sublist），Young Sublist / New Sublist (5/8): 包含最近被访问过的“热”页。位于 LRU List 的头部。Old Sublist / Old Blocks Sublist (3/8): 包含相对较旧的页。位于 LRU List 的尾部。innodb_old_blocks_pct 参数控制 Old Sublist 的比例（默认 37%，即约 3/8）。

• 工作流程：

  1. 新页插入： 新从磁盘读入的页（或通过预读读入的页）不会直接放到 Young 区头部，而是插入到 Old Sublist 的头部 (即整个 LRU List 的 midpoint，即 Young 和 Old 的交界处)。
  2. 访问 Old 区页： 当位于 Old Sublist 中的页第一次被访问（例如查询读取）时，它会被立即移动到 Young Sublist 的头部。这使得该页迅速成为“热”页。
  3. 访问 Young 区页： 当位于 Young Sublist 中的页被访问时，它不会每次都移动到绝对头部。InnoDB 有一个优化机制：只有当该页距离 Young Sublist 头部超过一定距离（由 innodb_old_blocks_time 或其他启发式规则影响）时，才会将其移动到头部。这避免了频繁移动非常热的页的开销。
  4. 页淘汰： 当 Free List 为空需要空间时，InnoDB 从 LRU List 的尾部（即 Old Sublist 的尾部） 开始扫描寻找可淘汰的页。优先淘汰那些 CLEAN 的页（因为不需要写盘）。如果找到 CLEAN 页，将其从 LRU List 和任何其他链表中移除，对应的 Frame 放入 Free List。如果找不到 CLEAN 页，后台刷新线程可能会被触发加速刷脏。

这种分区的 LRU 策略主要为了解决全表扫描（或大型索引扫描）带来的缓存污染问题。一次性的全表扫描会加载大量只访问一次的页。把它们放在 Old 区头部，如果它们没有被再次访问（通常不会），它们会很快从尾部被淘汰掉，而不会把真正的“热”数据（在 Young 区）挤出去。只有被重复访问的页（表明它可能是热点）才会被提升到 Young 区。

Flush List：脏链。管理所有脏页（即状态为 DIRTY，内容已被修改但尚未写回磁盘的页）。这个链表按照页被修改的最早时间（由修改该页的事务产生的日志的 LSN （Log Sequence Number））排序。当一个干净的页在 Buffer Pool 中被修改后，它会被加入到 Flush List 中（同时仍然保留在 LRU List 中）。当脏页被后台刷新线程（Page Cleaner Thread）成功写回磁盘后，其状态变为 CLEAN，并从 Flush List 中移除（但仍然保留在 LRU List 中），后台线程优先刷新 Flush List 中 LSN 最小的脏页（即最早修改的页），这保证了前文中崩溃恢复流程中从redolog文件获取到的Checkpoint LSN之前的数据必然是已经落盘了的。

注意：Flush List是按修改发生的顺序（LSN顺序），不是按最近访问顺序（LRU顺序）。

MySQL使用hash加速查找Buffer Pool数据：MySQL通过space_id + page_no进行Page Hash可以定位到页在Buffer Pool中的位置，如果找到则直接获取并更新访问信息，如果找不到则先检查free list有空白页，如果有空白页则需要通过space_id+page_no从磁盘中加载数据到该空白页并为该页创建和初始化控制块链入LRU List，如果没有空白页则需要LRU淘汰一个页出来空出位置，再加载再链入。

• 索引检索过程

索引页查找过程根据需要对树和遍历路径上的页进行加锁和闩锁防止其他线程改变树结构，先加Index S latch，随着查找过程加Page S latch，找到叶子页之后对该页加Page X latch。

索引页是按照B+树结构组织的。通过搜索树的方式在获取到数据叶子页之后获取，在页内部使用二分查找获取到具体的记录，如果是二级索引且覆盖索引的情况可以直接获取行数据，如果不是覆盖的起来则需要通过主键回表查询行数据。

mysql的Limit深分页瓶颈与优化：

• 原因： 在我们查询到的数据结果集本身很大的时候，深分页会强制数据库引擎执行了大量无用的工作（扫描、排序、回表、传输）来定位真正需要的那一小部分数据（count 条）。

select * from order limit 1000000,10

由于本身结果集要查询全表数据，然后从第一页开始向后滑动（沿着b+树next指针），直到100万跳记录之后，取出后面10条。

• 优化： 目前市面上的通用解法是使用ES这样的专业搜索引擎来处理。如果不能使用搜索引擎以下是一些可以优化的点：
  一是添加搜索条件以降低结果规模
  二是使用游标查询，比如按照ID排序之后，下一页带上一页的最大ID作为条件使得sql无需offset

-- 第一页 (按 id 升序)
SELECT * FROM your_table
WHERE ... -- 可选条件
ORDER BY id ASC -- 必须利用索引排序
LIMIT 20;

-- 假设上一页最后一条记录的 id 是 last_id
-- 下一页
SELECT * FROM your_table
WHERE ... AND id > last_id  -- 关键：使用游标条件
ORDER BY id ASC
LIMIT 20;

三是利用覆盖索引延迟关联，当select的字段可以用上二级索引的情况可以改写sql减少回表加速查询

SELECT t.*
FROM your_table t
JOIN (
    SELECT id -- 主键
    FROM your_table
    WHERE ... -- 条件
    ORDER BY ... -- 排序
    LIMIT 1000000, 20 -- 大 offset
) AS tmp ON t.id = tmp.id
ORDER BY ...; -- 通常需要再次排序以保持顺序

归根结底是避免频繁读取磁盘

更新语句

更新语句流程是本文最复杂的流程，不过前文我们已经解释了大量的前置流程，这里可以相对简化。更新语句操作执行过程需要先查询出记录再对记录进行更新操作。

1.事务开启

前文有提到，MySQL的事务一直开启，只有autocommit还是手动commit的区别。如果我们执行了set autocommit = 0;那么会关闭自动提交，需要手动提交事务。或者执行了begin;或start transcation; 也是需要手动执行commit;或 rollback;。在事务内第一次执行增删改操作时，MySQL会给事务分配一个事务号trx_id（全局递增的数字）。

2.词法分析、语法分析和优化器优化，执行器执行

目的是生成AST(抽象语法树)，也就是物理执行计划。最后由服务器提交到InnoDB引擎执行该计划。前文有提到这里不再赘述。

需要注意，在执行器执行修改的时候就会生成binlog。

3.查询要修改的数据

前文查询语句详解了查询操作，这里不再赘述。

4.锁

元数据锁 -> 表级锁 -> 行锁。锁是保证原子操作的核心。

首先对表元数据加S锁，然后对表加IX意向排他锁（由于表元数据已经和独立表空间在一起了，所以整个锁基本上用不上），最后获取行锁（行锁、间隙锁，邻键锁）

获取元数据锁可以因为ddl其他事务的ddl操作而阻塞，获取行锁可能因为其他事务正在修改而阻塞。行锁和表锁使用同一个数据结构：

一个行锁信息如下：

MySQL online DDL：为了高效修改表结构，缩短锁表时间，MySQL优化了ddl机制。online DDL机制将锁执行过程分为三个阶段：

• 准备阶段（Prepare）：短暂获取MDL排他锁
• 执行阶段（Execution）：降级为MDL共享锁，执行变更
• 提交阶段（Commit）：升级为MDL排他锁提交执行变更

online ddl对不同的操作就行细分，不同的操作可应用不同的算法以提高DDL执行效率：

LOCK=NONE（无锁，理想）、LOCK=SHARED（共享锁）、LOCK=EXCLUSIVE（排他锁）。

• INSTANT (8.0+): 仅修改元数据（毫秒级），不重建数据，全程几乎不阻塞 DML。

  • 仅修改数据字典中的元数据（不重建数据）。在毫秒内完成。支持：

    • 添加/删除/重命名列（有限制）
    • 修改默认值或 ENUM 定义
    • 表重命名。

• INPLACE: 原地重建数据页（可能需磁盘空间），允许并发 DML，但提交阶段短暂阻塞。

  • 避免完整的表复制，但可能原地重建表。分为：

    • 无需重建 (No-rebuild)：仅更改元数据（例如，删除索引、重命名列）。
    • 需要重建 (Rebuild)：重组聚集索引（例如，添加主键、更改 NULL 约束）。在执行期间使用行日志重放 DML 更改。

• COPY: 创建临时表复制全量数据，全程锁表阻塞 DML，需双倍磁盘空间。

  • 创建临时表，复制数据，并替换原表。锁定表以进行写入。不支持的操所需要（例如，更改列数据类型）。

算法优先级为 INSTANT → INPLACE → COPY，MySQL 自动选择最优支持项。

5.修改数据与日志生成

上面锁住了之后就可以安全的修改数据了。有三个地方的数据需要写入：数据页（buffer pool）、undolog、redolog。

• 数据页（缓冲页）

如果修改前后这行数据的大小没有变化，比如status从1变为0，那么对该条数据进行就地更新即可。如果大小发生了变化，那么删除原记录插入一条新记录，如果插入超出了页大小还会触发页分裂SMO操作，此时会锁住整颗B+树。

更新后的新记录的trx_id指向当前事务id，roll_pointer指向undolog。此时该页需要链上Flush List。

• undo log

undo log记录页修改前的数据。有两种undolog：

对于insert，undolog只需要根据ID删除新插入的数据。

如果是delete或者update则需要记录完整的修改信息：

undo log有两个功能，一是进行回滚，二是实现MVCC。
对于MVCC的实现，行记录会有一个roll_pointer指向之前版本的undolog，而undolog也有roll_pointer指向之前版本的undolog，这样就构成了版本链。

那么对于RR隔离级别（MySQL默认，可重复读），当我们第一次select的时候，获取到最后一个已提交的数据生成ReadView（通过版本链重建），然后之后的所有相同的select都使用这同一个ReadView，即使有其他的事务在生成视图后改变了数据也不更新ReadView。脏页行数据的活跃事务是当前事务那么可以直接通过脏页数据生成ReadView。

而对于RC级别隔离级别（pgsql和Oracle的默认，读已提交），在每次读取数据的时候都获取最新的已提交的事务数据生成ReadView，所以会出现不可重复读。

• redo log （实现WAL，write ahead log）

redolog记录修改后的数据，结构大约如下：

MTR（mini-transaction）:

一条修改语句可能包含多条redolog记录，因为尽管sql只有一条，但是会有多种情况，比如行大小变化需要删除旧记录插入新记录，要对上一条记录的next指针进行修改，数据页的Page Directory和Page Header可能发生变化，可能会触发页分裂与页合并等等情况。这样一组redolog记录被称为mini-transaction（mtr）,每个mtr必须是原子的，要求要么全部成功要么全部失败，redolog通过一个特殊类型的redolog来标识一个mtr已经完成了。需要注意的是undolog也会产生redolog记录，用来崩溃恢复undolog。

redolog生成后存入log buffer缓存中，它是一个连续的内存空间，很小，默认只有16MB（innodb_log_buffer_size），每个redolog block只有512b。由于其很小所以其落盘也很频繁。

对于redo log block它的结构和也类似，也有header和trailer，里面存有一个LSN（Log Sequence Number），每个mtr都有一个唯一的LSN。

此时事务在内存中修改哦完成了，由于还没有提交，那么数据不需要落盘，如果丢失了那就相当于事务回滚了。但是并不代表不会落盘，毕竟不论是log buffer、undolog还是buffer pool，其内存大小都是有限的，满了就会自动落盘。

6.事务提交

前文为我们的事务已经在内存中完成了。接下来事务提交的主要工作主要是落盘：redolog和binlog，对应需要落盘的文件是ib_logfile0, ib_logfile1 (两个文件交替操作) 和 binlog.xxxxxx 、binlog.index

落盘逻辑的内部XA事务（两段提交）：

• prepare阶段：redo log 落盘，落盘完成后该事务状态改为trx_prepared、如果失败那么会回滚，如果成功那么进入commit阶段
• commit阶段：服务器将binlog落盘，事务状态改为trx_not_started。那么提交成功

那么崩溃恢复阶段如果发现事务为active那么直接回滚，如果为not_started则认为事务已提交，如果是prepare那么需要根据binlog是否落盘来决定是回滚或者提交，如果已经写入，那么状态改为not_started，如果没有则回滚。

事务的物理落盘控制：

• innodb_flush_log_at_trx_commit：redolog的落盘策略，0每秒落盘一次，1每次提交落盘，2由操作系统决定，默认是1。
• sync_binlog：binlog的落盘策略，0操作系统决定，1每次提交。默认值是1。

7.主备复制

前文我们的事务已经在本地完成了，但是在返回之前还需要向备机同步数据。

前文我们已经介绍了mysql的复制能力，这里稍做补充：

• 异步复制：不需要等待备机响应，直接返回结果。
• 半同步复制：指定数量的备机中继日志（relay log）落盘后响应主机，返回结果。
• 同步复制：指定数量备机中继日志重放成功后响应主机，返回结果。

如果一直没有响应那么超时直接返回执行成功结果。一般会选择半同步复制，兼顾效率与安全。

8.返回执行结果

上面工作完成后，我们就可以将响应包装返回为客户端了。

9.脏页落盘

前文我们已经已经响应结果给客户端了。但是我们只是日志落盘了而数据并没有落盘。后台PageCleaner线程会将buffer pool中脏链的脏页数据刷入到磁盘表中。但是不能直接刷入，因为可能刷一半宕机了，那么新数据没有完整落盘，旧数据又被覆盖了一半。这时候我们需要双写落盘。

双写落盘

• 先将脏页写入doublewrite buffer，然后这个doublewrite buffer写到双写文件中（.dblwr文件）
• 然后再将脏页写入到表文件中（.ibd文件）

这样，如果页损坏，就可以使用doublewrite buffer中的页恢复。这也是WAL思想的应用。

参考：

1. MySQL :: MySQL 8.4 Reference Manual
2. mysql-server
3. 数据库内核月报
4. 深入解读MySQL InnoDB存储引擎Update语句执行过程