MySQL逻辑架构与存储

MySQL 最本质的特征在于其 逻辑架构与存储实现的完全解耦。这种分层架构决定了 MySQL 的生态灵活性，但也引出了独有的性能挑战与架构权衡。

分层

MySQL 的 宏观架构自上而下严格划分为四层，层级间通过标准的 API 接口进行交互。

整体执行流程：客户端请求 → 网络连接层处理 → Server 层解析/优化/执行 → 存储引擎层做数据读写 → 文件系统层落地磁盘。

各层职责严格解耦，Server 层不关心数据如何存储，仅通过统一 API 向存储引擎发指令；存储引擎不关心 SQL 如何解析，仅负责数据的物理读写和专属特性（事务、锁、索引等）实现。

连接层

是 MySQL 与客户端的交互入口，所有存储引擎共享，核心负责 网络通信、连接管理、安全验证，屏蔽底层网络细节，让 Server 层专注处理 SQL 逻辑。

客户端（如应用程序）通过特定的数据库驱动（如 JDBC, Python mysql-connector, Go-SQL-driver）发起请求。驱动层负责将应用层的逻辑请求封装为符合 MySQL Client/Server Protocol 的数据包。

  • TCP/IP 协议：最通用的通信方式，支持跨物理机连接。
  • Unix Domain Socket：仅限于 Linux/Unix 环境下的 本机连接。它绕过了网络协议栈（无需经过 TCP 握手和校验），通过文件系统直接通信，性能优于 TCP/IP，能显着减少 CPU 开销。
  • Named Pipes：Windows 环境下的本机通信机制，但在生产环境中较少使用。

当一个请求到达监听端口（默认 3306）时，连接层会按以下流程处理：

  • TCP 握手与认证：连接器首先处理 TCP 三次握手，随后执行基于 挑战-响应机制 的密码校验。
  • 权限快照：一旦连接建立并认证成功，连接器会读取当前用户的所有权限配置，并将其暂存在该会话的内存中。后续该连接内的所有操作权限校验，均以连接建立时刻 的快照为准。如果在管理员端修改了某个用户的权限，已经存在的连接不会感知到这一变化。只有当客户端断开重连后，新权限才会生效。
  • 连接维持与超时：
    • 连接器负责维持连接状态。若客户端在 wait_timeout 指定的时间内（默认 8 小时）无交互，服务端将自动断开连接并回收资源。
    • 由于建立连接（TCP 握手、权限验证）成本较高，工程上普遍推荐使用长连接（或连接池）来复用资源。然而，MySQL 在执行查询过程中申请的临时内存主要绑定在连接线程上，只有在连接断开时才会释放。这意味着长连接积累久了，极易导致服务器内存占用（OOM）飙升。仅依赖默认的超时机制往往不够。现代最佳实践建议采用以下两种方式之一优化内存：
      • 定期轮转：在应用层控制长连接的使用次数（如执行 1000 次查询后主动断开重连）；
      • 轻量级重置：使用 mysql_reset_connection 接口。它能在 不重新进行 TCP 握手和认证 的前提下，将连接恢复到初始状态并释放堆积的内存，实现了性能与资源的平衡。

挑战-响应机制是一种 身份认证协议，简单来说：不直接问你“密码是多少”，而是给你一道“数学题（挑战）”，只有知道正确密码的人，才能算出正确答案（响应），验证方通过核对答案确认身份。

目的：解决 明文密码传输的泄露风险——如果直接把密码从客户端发往服务端，网络中任何能抓包的节点都能直接获取密码；而挑战-响应机制中，网络上只传输“挑战内容”和“加密后的响应结果”，即使被抓包，攻击者也无法反推出原始密码。

MySQL 如何处理并发连接，直接决定了其在高并发下的性能表现。

  • 原生模型：单连接单线程：MySQL 的传统模型。每当有一个新的客户端连接进来，Server 就会分配一个独立的线程专门服务该连接。
    • 优势：实现简单，上下文隔离性好。
    • 劣势：随着连接数暴涨（例如几千个连接），线程数同步暴涨。操作系统的 上下文切换 开销会耗尽 CPU 资源，导致系统吞吐量断崖式下跌。
  • 优化一：线程缓存：这是 MySQL 原生的基础优化。当客户端断开连接时，线程不会立即销毁，而是放回缓存区。当新连接到来时，优先从缓存中复用线程。
    • 注意：这只是减少了线程 创建/销毁 的开销，并没有解决高并发下线程 运行 时的上下文切换问题。
  • 优化二：线程池：这是解决“连接数暴涨”的终极方案。
    • 原理：采用 N: M 模型。无论前端有多少个连接，后端只维持少量的活跃工作线程。空闲的连接请求会在队列中排队，等待工作线程处理。
    • 价值：将 连接管理 与 执行线程 解耦，确保 CPU 始终在全速处理 SQL 逻辑，而不是忙于调度线程。

在未使用线程池功能的标准社区版 MySQL 环境中，生产架构通常会在应用层与数据库之间引入 Proxy（如 ProxySQL, MyCat）。这些中间件充当了外部连接池的角色，通过 连接复用 技术，确保传输到后端 MySQL 的并发连接数始终控制在安全范围内。

Server 层

是 MySQL 的 大脑，采用 无状态 设计。它 不涉及数据的物理存储细节（那是存储引擎的事），所有存储引擎都依赖 Server 层的公共能力，核心负责 SQL 的解析、优化、执行，以及一些公共功能。

这一层是 MySQL 原生实现的，无法替换，Server 层定义了一套通用的 Handler API，任何存储引擎只要适配了这套接口，就能无缝接入。对 Server 层而言，它不需要知道底层是 B+ 树还是 LSM 树，只管调用 handler::open_table 或 handler::rnd_next。

连接建立后，SQL 语句会进入 Server 层的处理流程。

当 查询缓存 未命中时，SQL 语句首先进入编译阶段。解析器（Parser） 会对其进行 词法和语法分析，将 SQL 文本流转换为 抽象语法树（AST）

  • 词法分析 (Lexical Analysis)：将连续的 SQL 字符流拆解为具有独立意义的最小单元 —— 词元 (Token)。例如，SELECT * FROM user 会被识别为 Token_SELECT, Token_ASTERISK, Token_FROM, Token_String(user)。
  • 语法分析：基于 SQL 语法规则，将词元组合成一棵 AST。如果 SQL 语句存在语法错误（如关键字拼写错误、括号不匹配），解析器会在此阶段抛出 ERROR 1064 (42000)，流程终止。
  • 语义分析：这是 8.0 之后的强化步骤。语法正确不代表逻辑可行，解析器会进一步检查：
    • 表名和列名是否存在？
    • 是否存在列名歧义？
    • 检查用户是否拥有相应的操作权限。
  • 前面全部通过后，Parser 会将最终生成的 AST 传递给优化器

在 8.0 之前，在解析前会检查 查询缓存。但由于其粒度过粗，任何对表的 INSERT, UPDATE, DELETE 操作都会导致所有与该表相关的查询缓存 全部失效，在写操作频繁的应用中，缓存命中率极低，反而增加了维护开销和锁竞争。因此，MySQL 8.0 已彻底移除了查询缓存。

优化器 (Optimizer) 拿到 AST 后，其目标并非“最快”，而是 “成本（Cost）最低”。它会将声明式 SQL（告诉我要什么）转换为过程式执行计划（告诉我怎么做）。这是 MySQL 性能的关键，它不一定会按照 SQL 书写的顺序执行。

逻辑优化：即 等价变换，在不改变查询结果的前提下，通过重写 SQL 结构来简化计算。

  • 常量折叠：WHERE age > 10+5 自动转换为 WHERE age > 15。
  • 外连接消除：若 LEFT JOIN 的 WHERE 条件排除了 NULL 值，则自动转换为 INNER JOIN 以获取更多优化空间。
  • 谓词下推 (Predicate Pushdown)：将 WHERE 条件尽可能“推”到离数据源最近的地方。例如在 Join 前先过滤单表数据，减少参与 Join 的记录数。
  • 子查询转换：将 IN 子查询尝试转换为 SEMI-JOIN 或 EXISTS，避免相关子查询带来的性能灾难。

物理优化：即 基于成本的优化 (CBO, Cost-Based Optimization)。优化器根据统计信息（Statistics，如行数、索引基数、数据页分布）为一个查询计算不同执行路径的 Cost（Cost = IO 成本 + CPU 成本）。

  • 访问路径选择：

    • 全表扫描：当查询条件区分度低（如 gender='male'）或表很小时，直接扫表往往比读索引回表更在廉价。
    • 索引扫描：当查询命中高区分度索引（如 id=1）时，优先走 B+ 树。

  • Join 算法选择：

    • Index Nested-Loop Join (NLJ)：利用被驱动表的索引进行循环匹配。
    • Block Nested-Loop Join (BNL)：旧版本中，若被驱动表无索引，使用内存块缓存驱动表数据进行匹配。
    • Hash Join：MySQL 8.0.18 引入的重大特性。在处理无索引的大表 Join 时，Hash Join 比传统的 BNL 快数倍甚至数十倍。

  • Join 顺序决策：并不总是“小表驱动大表”，而是“过滤后结果集（Fan-out）最小的表”作为驱动表。

执行器 (Executor) 是执行计划的消费者。MySQL 传统的执行模型是经典的 火山模型 (Volcano Model)，也称为迭代器模型。

  • 执行流程：执行器并不直接操作数据文件，它通过调用存储引擎的 Handler API 接口。

    • 调用 handler::read_first_row 获取第一行。
    • 符合 WHERE 条件？符合则放入结果集（Net Buffer），不符合则丢弃。
    • 调用 handler::read_next_row 获取下一行，循环往复，直到 handler 返回 EOF。

  • 行数统计差异：

    • Rows_examined（扫描行数）：引擎层实际扫描并返回给 Server 层的数据量。
    • Rows_sent（发送行数）：Server 层经过进一步过滤（如 Using where）后最终发送给客户端的数量。
    • 两者差距越大，说明索引的过滤性越差，Server 层做了大量无用功。

存储引擎层

这是数据的物理落地层，负责数据的实际存储和检索 以及 引擎专属的特性实现（事务、行锁、MVCC、Redo/Undo Log、索引结构等）。采用 可插拔架构，数据按表存储，每个表可以指定不同的存储引擎。

本篇不涉及日志系统

主流引擎:

  • InnoDB：默认引擎。
    • 特性：索引即数据。完整支持 ACID 事务；支持 行级锁；引入 外键约束。
    • 核心组件：
      • Buffer Pool：不仅缓存数据，还缓存索引、插入缓冲（Change Buffer）、自适应哈希索引（AHI）。
      • Doublewrite Buffer：解决“页断裂”（Partial Page Write）问题，保证数据可靠性。
  • MyISAM：MySQL5.5 前默认。堆表结构，索引存偏移量，数据追加写。只支持表锁，读写互斥，高并发下性能瓶颈严重；不支持事务；崩溃后恢复能力极其简陋。纯读/读多写极少 的静态数据场景下还有用处，如历史报表、归档数据、静态页面的数据库存储。
  • Memory：基于内存的 Hash 索引（默认）或 B-Tree 索引。数据断电丢失；表级锁。现代架构中通常被 Redis 等外部缓存或 8.0 内部的 TempTable 引擎替代。

物理隔离：各引擎的数据文件、日志机制、内存结构完全独立。

  • InnoDB：数据和索引存储在 .ibd 文件中（独立表空间模式），或者共享表空间 ibdata 文件中。
  • MyISAM：索引（.MYI）与数据（.MYD）文件物理分离。
  • Memory：仅在磁盘保留 .sdi（8.0+）或 .frm（旧版）表结构定义，数据纯内存。

动态加载：MySQL 支持在运行时通过 INSTALL PLUGIN 命令加载新的引擎，无需重启服务。

尽管 MySQL 允许单实例内混合使用不同引擎，但在生产环境中强烈不推荐。这会导致复杂的备份策略（因为无法通过单一逻辑建立一致性快照），且无法实现跨引擎的事务一致性。

存储引擎并非直接操作磁盘，而是通过内存缓冲区 (Buffer Pool)进行交互，这是数据库性能的生命线。

  • Buffer Pool：这是 InnoDB 最大的内存区域。当执行器请求“读取某行数据”时：

    • 命中 (Hit)：若数据页已在 Buffer Pool 中，直接内存读取，微秒级响应。
    • 未命中 (Miss)：触发磁盘 I/O，将原本的 16KB 数据页加载到 Buffer Pool，并基于 LRU 算法（Least Recently Used）淘汰冷数据。

  • 预读机制 (Read-Ahead)：为了减少 I/O 次数，InnoDB 可能会根据局部性原理，预测性地将相邻的数据页一并加载到 Buffer Pool 中。

根据优化器选定的执行计划，引擎层提供不同的数据检索方式：

  • 全表扫描：存储引擎从表的第一个数据页开始，逐页扫描，直到表的末尾，返回所有满足 WHERE 条件（如果下推了）的行。
  • 索引扫描：

    • 全表扫描：引擎通过链表指针从第一个数据页遍历到最后一个数据页。

      • 代价：极高的 I/O 开销，且会污染 Buffer Pool（将热数据挤出）。

    • 聚簇索引扫描 (Clustered Index)：在 InnoDB 中，主键索引的叶子节点直接存储了完整的行数据。查到索引即查到数据。

    • 二级索引扫描与回表：非主键索引的叶子节点存储的是 索引列值 + 主键 ID。

      • 回表 (Lookup)：当查询需要非索引列的数据时，引擎先查二级索引拿到主键 ID，再拿着 ID 去聚簇索引树中搜索完整行数据。

    • 覆盖索引: 如果 SELECT 的所有列都包含在二级索引中，引擎直接在二级索引树中读取数据返回，无需回表。这是 SQL 优化的核心手段之一。

存储引擎并不一次性返回所有数据，而是根据执行器的 Handler 接口调用，将获取到的每一行数据返回给执行引擎（Server 层），按行 交付。执行引擎根据 SELECT 列表的要求，对数据进行处理（例如，COUNT(*) 进行计数、ORDER BY 进行排序、GROUP BY 进行聚合）。

最终结果集由 Server 层 格式化，并写入网络缓冲区（Net Buffer），服务器的工作线程将格式化后的数据，按照网络协议（一般是 TCP/IP 协议），分批次地发送回客户端。客户端的数据库驱动程序接收数据包，解析协议，重组为应用程序可用的格式（例如 Java 中的 ResultSet 对象），应用程序通过 API 遍历结果集。

文件系统层

是 MySQL 逻辑数据与物理硬件交互的边界，所有引擎共享。该层不属于 MySQL 自身的逻辑范畴，而是直接依赖于操作系统的文件系统（如 Linux 的 XFS, Ext4）来管理文件的读写、权限与锁定。

该层负责 将上层定义的 数据库、表、索引、日志 等逻辑概念，映射为操作系统可见的 物理文件。任何存储引擎的内存数据最终都必须通过系统调用在此层写入磁盘，以保证持久性。

MySQL 对文件系统的使用并非简单的 read/write，而是针对不同文件类型采用了精细化的 I/O 策略。

  • Direct I/O (O_DIRECT)：InnoDB 引擎主要使用 O_DIRECT 模式打开数据文件和日志文件。
    • 目的：绕过操作系统的 Page Cache。
    • 原因：InnoDB 自身维护了强大的 Buffer Pool，如果再经过 OS Cache，会导致内存中存在两份相同的数据，造成内存浪费且增加 CPU 开销。同时，绕过 OS Cache 才能让数据库精确控制数据何时真正落盘（fsync），这是实现 ACID 事务持久性的前提。
  • Native AIO (Asynchronous I/O)：MySQL 强依赖操作系统的原生异步 I/O 能力（在 Linux 上即 libaio）。允许 InnoDB 线程在发起 I/O 请求后不被阻塞，继续处理其他任务，待磁盘完成操作后通过回调通知。这对提升高并发下的写入吞吐量至关重要。
  • 标准缓冲 I/O (Buffered I/O)：主要用于 Server 层的部分日志（如错误日志、慢查询日志）或某些工具操作。这些操作依赖 OS 的 Page Cache 进行合并写入，以减少磁盘寻址。

MySQL 8.0 进行了大幅度的元数据管理重构，文件结构与旧版本有显着差异。

如果让开发者在建表时直接指定数据存在哪块磁盘的哪个文件里，不仅极大增加了开发心智负担，也让运维在动态扩容时如履薄冰。

为了解决这个耦合问题，InnoDB 引入了 表空间。它就像是一位尽职的档案秘书：向上对开发者暴露逻辑上的“表”，向下对操作系统管理复杂的物理“文件”。

表空间本质上是一种存储抽象，它屏蔽了底层文件系统的碎片化和复杂性，使得数据库引擎可以在一个统一的逻辑连续地址空间中进行数据调度。

在早期，InnoDB 默认将所有表的数据、索引和元数据塞进一个共享的系统表空间（通常是 ibdata1 文件）。

共享表空间虽然集中，但带来了灾难性的空间回收问题——当你删除一张拥有千万级数据的表时，ibdata1 文件的体积并不会缩小，操作系统无法回收这些释放的磁盘空间。为了解决这个问题，InnoDB 引入了 独立表空间，让每张表拥有自己独立的 .ibd 文件。这样一来，执行 DROP TABLE 时，对应的物理文件被直接删除，磁盘空间瞬间释放。

既然有了独立的文件，我们能否直接把数据一条条往文件里塞？

这里遇到了一个经典的计算机硬件痛点：磁盘的随机 I/O 极度缓慢。根据局部性原理，如果我们在物理上将相关联的数据连续存放，就能大幅提升预读和顺序 I/O 的效率。

为了实现连续性，InnoDB 对物理文件进行了宏观切分，引入了 区（Extent） 的概念。

一个区是一段物理上连续分配的空间，默认大小为 1MB。如果把表空间看作是一个巨大的图书馆，区就是其中一排排标准尺寸的书架。

然而，表中的数据分为两类：构成 B+ 树叶子节点的 业务数据，和构成非叶子节点的 索引数据。如果把它们混在一个区里，扫描数据时就会产生大量不必要的跳跃。

这就引出了逻辑分组的概念——段（Segment）。

段并不直接对应某一块连续的物理磁盘，它是一个逻辑概念，用于管理状态相似的区。每创建一个索引，InnoDB 会自动生成两个段：

  • 数据段（Data Segment）：管理所有的叶子节点区。
  • 索引段（Index Segment）：管理所有的非叶子节点区。

如果每创建一个段就直接分配一个 1MB 的区，那么对于只有几条配置信息的小表来说，空间浪费是极其严重的。

因此，InnoDB 在分配策略上做了一个精妙的妥协：在段刚建立时，先从表空间共用的“碎片区（Fragment Extent）”中按单页（16KB）分配，直到用满 32 页后，才会判定这是一个“大段”，从此开始按完整的区（1MB）进行分配。

区虽然保证了宏观的连续性，但 1MB 的粒度对于内存与磁盘之间的数据交换来说太大了。因此，区被进一步等分为 64 个连续的 页（Page），默认大小为 16KB。

页不仅是物理空间，更是内存与磁盘进行 I/O 操作的基本单位。

在业务数据所在的 Index 页中，行记录是紧密堆叠的。当我们需要在这一页中查找一条特定的数据时，难道要从头到尾遍历吗？

为了在 16KB 的页内实现极速查找，InnoDB 抽取了数据行的主键，在页的尾部构建了一个称为“槽（Slot）”的稀疏索引数组。每 4 到 8 行数据对应一个槽。

当查询落在某页时，InnoDB 直接对这个连续的槽数组进行 二分查找，瞬间将搜索范围缩小到几行记录，最后再进行极其短暂的线性遍历。

经过层层剖析，我们终于来到了真正存储数据的行（Row）。

对于开发者而言，一行数据就是几个业务字段。但在 InnoDB 眼里，要维持事务的隔离级别（MVCC）和数据的组织，就必须在每一行强行植入隐藏的元数据：

  • DB_TRX_ID：最后一次修改该行的事务 ID。
  • DB_ROLL_PTR：回滚指针，指向 Undo Log 中该行的历史版本。
  • DB_ROW_ID：隐藏的自增主键（只有当表既没有主键也没有非空唯一索引时才生成）。

既然系统会自动生成 DB_ROW_ID，为什么还要强制指定主键？

如果不指定主键，所有没有主键的表都会 共享同一个全局的 dict_sys.row_id 序列。在极高并发的插入场景下，这个全局共享变量会受到操作系统互斥锁（Mutex）的保护。意味着你整个数据库里所有没有主键的表，在并发写入时会互相阻塞，成为性能的绝对瓶颈。

底层环境的选择直接决定了 MySQL 的 I/O 上限。

  • 文件系统选择：XFS vs Ext4：推荐 XFS。相比 Ext4，XFS 在处理大文件、高并发 I/O 以及 稀疏文件 方面表现更优，且删除大文件时瞬间完成，不会像 Ext4 那样引起 I/O 抖动。
  • 磁盘调度算法：推荐 noop / deadline / none。在 SSD/NVMe 时代，物理设备已具备极高的并发处理能力，应将调度策略设置为 none（NVMe）或 noop，不再需要操作系统层面的电梯算法（CFQ）进行复杂的排序合并，避免无谓的 CPU 开销。

架构缺陷——索引下推 (ICP)

职责的过度割裂

MySQL 的分层架构存在一个看起来有点“愚蠢”的问题，而这个问题导致了 MySQL 严重的性能损耗。

如上文所说，Server 层就像是一个“大脑”，它负责 SQL 解析、优化器生成执行计划、以及最终的 WHERE 条件过滤。引擎层就是“手脚”，它通过实现 handler 接口提供基础的数据存取服务。

早期接口非常原始，类似于“给我下一行数据”或“给我索引范围内的数据”。

以查询 SELECT * FROM user WHERE name LIKE 'Chen%' AND age = 20 为例（联合索引为 idx_name_age）

  • Server 层 分析发现可以使用索引 name 进行范围查询。
  • Server 层 向引擎层发送指令：扫描所有 name 以 ‘Chen’ 开头的索引记录。此时，age=20 这个条件被保留在 Server 层，未传递给引擎。
  • 引擎层 通过 B+ 树定位到 name='Chen' 的记录。虽然引擎在读取索引叶子节点时明明可以看到 age 的值（例如 age=30），但由于接口协议未包含该条件，引擎 不会过滤。
  • 引擎层 被迫执行 回表 操作，根据主键去聚簇索引读取完整的行数据，返回给 Server 层。
  • Server 层 接收完整行数据后，再进行 IF age == 20 的判断。若不匹配，则丢弃。

这种设计导致了大量的 无效回表。引擎层明明手握索引中的 age 数据，却因为职责划分过细，必须先进行昂贵的随机 I/O 读取整行数据，仅仅是为了让 Server 层做一个本可以在索引层就完成的判断。

接口的进化：Handler 层的变革 (ICP)

索引下推 ICP（Index Condition Pushdown） 是 MySQL 架构优化的一个里程碑，它打破了 Server 层与引擎层之间僵化的界限，允许引擎层利用“手头已有的索引数据”进行预先过滤，从而将大量的随机磁盘 I/O 扼杀在萌芽状态。

MySQL 5.6 引入 ICP ，本质上是对 Handler 接口协议 进行了扩展。

改动前的交互模式：Handler：：read_range( start='Chen', end='Chen~' )

语义：引擎，请取出该范围内的所有行记录。

改动后的交互模式：Handler：：read_range( start='Chen', end='Chen~', condition="age=20" )

语义：引擎，在该范围内查找时，请 顺便 检查 age 是否等于 20。如果不匹配，直接跳过该索引节点，不需要回表。

底层实现细节：在代码层面，MySQL 在 handler 类中增加了一个 pushed_idx_cond 成员变量。当开启 ICP 时，优化器会将部分 WHERE 条件通过这个变量“下推”给存储引擎。引擎在遍历 B+ 树索引记录时，会先计算 pushed_idx_cond，只有满足条件的记录，才会被加入到结果集或进行回表操作。

引擎层直接过滤掉不匹配的记录，不再向 Server 层传输无用数据。回表操作通常涉及离散的磁盘读取，ICP 极大地减少了这种昂贵的操作。

三种条件过滤级别：

  • Index Key (索引定位)

    • 示例：col_A > 10 (索引第一列)

    • 机制：用于确定 B+ 树搜索的 起止范围。

    • 执行者：引擎层。这是搜索的起点，不涉及过滤，而是涉及定位。

  • Index Filter (索引下推 ICP)

    • 示例：col_B = 20 (索引第二列，但在第一列范围查询后)

    • 机制：根据“最左前缀原则”，范围查询后的列无法用于快速定位，但它们依然存在于索引树中。

    • 执行者：引擎层（开启 ICP 后）。

    • 行为：引擎在读取索引记录时直接比对。如果不匹配，直接读取下一条索引记录，不回表。

    • 标识：在 EXPLAIN 输出的 Extra 列中显示 Using index condition。

  • Table Filter (表级过滤)

    • 示例：col_C = 30 (完全不在索引中)

    • 机制：数据不在当前的 B+ 树索引上，引擎层“看不到”该字段。

    • 执行者：Server 层。

    • 行为：引擎层必须先 回表，读取完整数据行返回给 Server 层，由 Server 层进行最终过滤。

    • 标识：在 EXPLAIN 输出的 Extra 列中显示 Using where（注意：Using where 并不一定代表回表，但在有 ICP 的对比下，它通常意味着 Server 层参与了过滤）。

并非所有情况都能利用 ICP：

  • 仅适用于二级索引：ICP 的核心目的是减少回表。对于 InnoDB 的聚簇索引（主键索引），因为数据本身就和索引在一起，不存在“回表”概念，所以 ICP 意义不大。
  • 存储引擎限制：主要支持 InnoDB 和 MyISAM 引擎。
  • 引用限制：如果过滤条件中引用了存储函数，或者触发了某些复杂的逻辑，引擎层可能无法处理，依然需要由 Server 层处理。
  • 虚拟列：对于建立在虚拟列上的二级索引，ICP 同样有效。

分层的代价——双写一致性

分层架构除了会带来上述 的 “效率损耗” 问题，还引入了一个“分布式事务问题”，只不过它不是发生在多台服务器之间，而是发生在一个单机系统的 Server 层 和 引擎层 之间。

两个“大脑”，两份“账本”

在 MySQL 的分层架构中，Server 层和引擎层各自维护了一份日志：

  • Binlog（归档日志）：属于 Server 层（上层）。它是下游（从库、数据分析）的数据源头，决定了 “主从是否一致”。
  • Redolog（重做日志）：属于 InnoDB 引擎层（底层）。它是崩溃恢复的唯一依据，决定了 “本机是否不丢数据”。

由于这两份日志分别由不同的组件写入，如果不能保证它们 原子性 地写入成功，就会出现 数据一致性问题。

假设我们执行一个 UPDATE 操作：

  • 场景 A（先写 Redolog，后写 Binlog）：Redolog 写完后系统宕机，Binlog 没来得及写。

    • 结果：重启后，主库通过 Redolog 恢复了这行数据。但因为 Binlog 缺失，从库永远收不到这条更新。
    • 判定：主从不一致（主库多，从库少）。

  • 场景 B（先写 Binlog，后写 Redolog）：Binlog 写完后系统宕机，Redolog 没来得及写。

    • 结果：重启后，主库因为 Redolog 缺失，事务回滚（数据消失）。但从库收到了 Binlog，执行了更新。
    • 判定：主从不一致（主库少，从库多）。

补丁：两阶段提交 (2PC)

为了缝合 Server 层与引擎层之间的裂痕，MySQL 借鉴了分布式系统的思路，引入了 两阶段提交 (Two-Phase Commit, 2PC) 机制。

这是一种 跨层协作协议：

  1. Prepare 阶段：引擎层将 Redolog 写入磁盘，并标记为“准备好”。
  2. Commit 阶段：Server 层写入 Binlog，然后通知引擎层将 Redolog 标记为“提交”。

引入 2PC 后，MySQL 重启时的恢复逻辑不再只看引擎层，而是要 综合两层日志 进行判断：以 Binlog 是否完整为最高准则。

只要 Binlog 已经完整写入（哪怕 Redolog 还在 Prepare 状态），MySQL 也会强行提交该事务，从而保证主库与从库的数据最终一致。

本篇不涉及 2PC 的具体执行流程、崩溃恢复的详细规则以及为了解决 2PC 性能问题而引入的 组提交 机制

从 SQL 到磁盘：一条数据的物理落盘之旅

理解了上述所有组件，现在我们可以将这套精密的机械运转起来，推演一条数据从插入到落盘的全过程：

  1. 寻址逻辑入口：引擎首先通过系统表空间找到 user 表的元数据，提取出聚集索引 B+ 树的 Root Page 编号。
  2. 树的遍历：顺着 Root Page，根据主键（如果没有，分配全局互斥的 DB_ROW_ID），利用二分查找快速向下定位到目标叶子节点所在的物理数据页。
  3. 页内调配：检查该数据页剩余空间。如果足够，更新 Page Directory，直接将行记录写入。
  4. 空间伸缩：如果空间不足，触发页分裂。引擎会向该段管理的“未满区（Not Full Extent）”申请新的 16KB 页。如果区也满了，则向表空间的“空闲区（Free Extent）”申请整整 1MB 的新空间。
  5. 持久化防线：所有修改先写入 Redo Log 并在内存中标记为脏页。在合适的时机，经过 Doublewrite Buffer 护航，最终安全地落入磁盘的 .ibd 文件中。
数据库 > MySQL
MySQL逻辑架构与存储
https://gavinmo1.github.io/2026/03/06/MySQL逻辑架构与存储/
作者
Gavin
发布于
2026年3月6日
许可协议