硬核万字图解 MySQL 表空间、Tables、Index、双写缓冲、Redo Log、Undo Log 原理

在数据库领域MySQL 的 InnoDB 存储引擎以其高性能、高可靠性和事务支持著称。MySQL innoDB 引擎架构可以分为两大块分别是内存架构In-Memory Structure和磁盘架构On-Disk Structure。图 1书接上回《MySQL InnoDB 架构 Buffer Pool、Change Buffer、自适应哈希索引、Log Buffer》我们掌握了 InnoDB 引擎的内存架构。数据最终要持久化到磁盘其磁盘架构设计融合了复杂的存储结构和精巧的机制本文将深入剖析其核心模块的设计原理并通过图片辅助理解。MySQL 到底是怎么管理和存储各种各样的数据呢比如创建一张表、索引、表中的每一行数据、查询过程中临时存储的数据都存在哪里又如何管理这一切都归功于 MySQL 的 Tablespaces 表空间的设计内容较多本篇就关于以下类型 Tablespaces 表空间作用、Tables、Index、Doublewrite Buffer、Redo Log、Undo Log 和实现原理展开Tablespace表空间系统表空间System Tablespace独立表空间File-Per-Table Tablespaces通用表空间General Tablespaces撤销表空间Undo Tablespaces临时表空间Temporary TablespacesTables表Row Formats 行格式主键自增主键Indexes索引Doublewrite Buffer双写缓冲为什么需要双写缓冲双写缓冲架构设计Redo Log重做日志Undo Log撤销日志Tablespaces 表空间表空间可以看做是 InnoDB 存储引擎逻辑结构的最高层所有的数据都存放在表空间中称之为表空间tablespace。从物理文件的分类来看日志文件Undo Log、Redo Log。系统表空间System Tablespace文件 ibdata1。Undo tablespace 。独立表空间File-Per-Table Tablespaces通用表空间General Tablespaces临时表空间文件Temporary Tablespaces所以表空间根据不同的场景也分了多种类型我分别介绍下……系统表空间System Tablespace默认配置下会有一个初始大小为 10MB名为 ibdata1 的文件。该文件就是默认的表空间文件tablespace file。系统表空间是 Change Buffer 的存储区域。如果表是在系统表空间而非独立表空间或通用表空间中创建的它也可能包含表和索引数据。增加系统表空间大小的最简单方法是将其配置为自动扩展。为此在innodb_data_file_path设置中为最后一个数据文件指定autoextend属性并重启服务器。innodb_data_file_pathibdata1:10M:autoextend为避免系统表空间过大可考虑使用独立表空间或通用表空间存储数据。独立表空间是默认的表空间类型在创建InnoDB表时会隐式使用。独立表空间File-Per-Table Tablespaces独立表空间顾名思义就是用户创建的表空间如果开启独立表空间参数那么一个表空间会对应磁盘上的一个物理文件每张表对应一个文件支持事务独立管理。其实表空间文件内部还是组织为更复杂的逻辑结构自顶向下可分为 segment段、extent区和 page页。page 则是表空间数据存储的基本单位innodb 将表文件xxx.ibd按 page 切分依类型不同page 内容也有所区别最为常见的是存储数据库表的行记录。表空间下一级称为 segment。segment 与数据库中的索引相映射。Innodb 引擎内每个索引对应两个 segment管理叶子节点的 segment 和管理非叶子节点 segment。创建索引中很关键的步骤便是分配 segmentInnodb 内部使用 INODE 来描述 segment。segment 的下一级是 extentextent 代表一组连续的 page默认为 64 个 page大小 1MB。InnoDB 存储引擎的逻辑存储结构大致如图 2 所示。图 2默认情况下 InnoDB 存储引擎有一个共享表空间 ibdata1即所有数据都存放在这个表空间内。如果用户启用了参数innodb_file_per_table则每张表内的数据可以单独放到一个表空间内。如果启用了innodb_file_per_table的参数需要注意的是每张表的表空间内存放的只是数据、索引和插入缓冲 Bitmap 页.其他类的数据如回滚undo信息插入缓冲索引页、系统事务信息二次写缓冲Double write buffer等还是存放在原来的系统表空间内。通用表空间General Tablespaces通用表空间是一种共享的InnoDB表空间通过CREATE TABLESPACE语法创建。通用表空间提供以下功能类似于系统表空间通用表空间是一种共享表空间能够存储多张表的数据。通用表空间在内存占用上可能优于独立表空间。服务器会在表空间生命周期内将表空间元数据保留在内存中。相较于相同数量的表分散在多个独立表空间中更少的通用表空间内存储多张表能减少表空间元数据的内存消耗。通用表空间通过CREATE TABLESPACE语法创建。CREATE TABLESPACE tablespace_name [ADD DATAFILE file_name] [FILE_BLOCK_SIZE value] [ENGINE [] engine_name]通用表空间有什么不足通用表空间限制有以下限制现有的表空间无法更改为通用表空间。不支持创建临时通用表空间。通用表空间不支持临时表。不支持将表分区放置在通用表空间中。在复制环境中如果源和副本位于同一主机上则不支持使用ADD DATAFILE子句因为这会导致源和副本在同一位置创建同名的表空间而这是不被支持的。撤销表空间Undo TablespacesMySQL InnoDB 引擎的 Undo Tablespaces撤销表空间是磁盘架构设计中用于管理事务回滚日志Undo Log的核心组件。余彦瑭InnoDB 引擎的 Undo Tablespaces撤销表空间有啥用Undo 日志Undo Log主要用于事务异常时的数据回滚在磁盘上 undo 日志保存在 Undo Tablespaces 中。事务回滚与 MVCC 支持Undo 表空间存储的 Undo Log 记录了事务对数据的修改前镜像用于事务回滚时恢复数据原状实现多版本并发控制MVCC支持非锁定一致性读。分离系统表空间负载在 MySQL 5.7 之前Undo Log 默认存储在系统表空间ibdata1中。随着事务频繁操作ibdata1文件会无限增长且无法自动回收空间。5.7 及更高版本引入独立 Undo 表空间通过物理隔离减轻系统表空间压力提升性能。MySQL 8.0 默认创建 2 个 Undo 表空间文件undo_001和undo_002每个初始大小为 16MB通过参数innodb_undo_tablespaces可调整数量范围 2-127每个文件初始 16MB支持自动扩展和截断回收。余彦瑭“Undo 表空间的逻辑层级管理是咋样的”回滚段Rollback Segments每个 Undo 表空间包含 128 个回滚段由innodb_rollback_segments控制每个回滚段管理 1024 个 Undo 段Undo Segments。Undo 页与日志记录Undo 段由多个 16KB 的页组成按事务类型分为 Insert Undo 段仅用于回滚和 Update Undo 段用于 MVCC前者事务提交后立即释放后者需等待无活跃读视图时清除。通过多 Undo 表空间与回滚段的分区设计理论上支持高达数万级并发事务例如128 表空间 × 128 回滚段 × 1024 Undo 段。如下图所示。关键说明每个 Undo 表空间包含128 个回滚段每个回滚段管理1024 个 Undo 段按事务类型分类Undo 段由16KB 页组成存储具体日志记录余彦瑭说说 Undo Log 与 MVCC 的协作机制Undo Log 与 MVCC 的协作机制如下图所示运作原理事务修改前将旧数据写入 Undo Log读事务通过 Read View 判断可见性多版本数据通过 Undo Log 链回溯访问余彦瑭“系统表空间与 Undo 表空间存储有啥区别”特性Undo 表空间系统表空间历史方案存储内容仅 Undo Log数据字典、双写缓冲、Undo Log 等混合内容空间管理支持自动截断避免文件膨胀无法自动回收需手动调整或重建性能影响减少 I/O 竞争提升并发处理能力高频事务易导致文件过大性能下降版本支持MySQL 5.7 默认方案MySQL 5.6 及更早版本临时表空间Temporary TablespacesInnoDB 临时表空间分为会话临时表空间和全局临时表空间分别承担不同角色会话临时表空间Session Temporary Tablespaces用途存储用户显式创建的临时表CREATE TEMPORARY TABLE以及优化器生成的内部临时表如排序、分组操作。生命周期会话断开时自动截断并释放回池文件扩展名为.ibt默认位于#innodb_temp目录。分配机制首次需要创建磁盘临时表时从预分配的池中分配默认池包含 10 个表空间文件每个会话最多分配 2 个表空间用户临时表与优化器内部临时表各一。全局临时表空间Global Temporary Tablespace用途存储用户临时表的回滚段Rollback Segments支持事务回滚操作。文件配置默认文件名为ibtmp1初始大小 12MB支持自动扩展由参数innodb_temp_data_file_path控制路径与属性。回收机制服务器重启时自动删除并重建意外崩溃时需手动清理。Temporary Tablespaces 物理结构图示说明全局临时表空间ibtmp1存储用户临时表的回滚段会话临时表空间#innodb_temp目录下预分配 10 个.ibt文件池默认配置每个会话最多激活 2 个临时表空间用户临时表 优化器内部临时表。会话级临时表空间生命周期关键点首次需要磁盘临时表时从池中分配会话断开连接后立即归还空间文件物理保留但内容截断类似内存池机制临时表空间使用查询流程前面说过临时表空间可存储用户显式创建的临时表CREATE TEMPORARY TABLE以及优化器生成的内部临时表如排序、分组操作。那它的查询过程是怎样的呢Tables表余彦瑭“在 MySQL 如何创建一张表”InnoDB表通过CREATE TABLE语句创建例如CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINEInnoDB;默认情况下InnoDB表创建于每表独立的表空间中。若要在InnoDB系统表空间中创建InnoDB表需在创建表前禁用innodb_file_per_table变量。比如在数据库test中创建一个表show_index在 mysql 的 dataDirectory 目录下就回出现一个名为show_index.ibd的数据文件。在单个表的数据文件中数据就是以多个页的形式进行排列。MySQL 默认配置下每 16K即为一个页。InnoDB 表以B树组织数据每个表对应一个聚簇索引Clustered Index数据行的物理存储顺序与主键顺序一致。若未显式定义主键InnoDB 会隐式生成一个 6 字节的 Row ID 作为主键。Row Formats 行格式余彦瑭表中的每一行数据是怎么存储的表的InnoDB行格式决定了其行在磁盘上的物理存储方式。InnoDB支持四种行格式每种格式具有不同的存储特性。支持的行格式包括REDUNDANT、COMPACT、DYNAMIC和COMPRESSED。其中DYNAMIC行格式为默认格式。余彦瑭它们有啥区别REDUNDANT和COMPACT行格式支持的最大索引键前缀长度为 767 字节而DYNAMIC和COMPRESSED行格式则支持 3072 字节的索引键前缀长度。在复制环境中若源服务器上的innodb_default_row_format变量设置为DYNAMIC而副本上设置为COMPACT则以下未明确指定行格式的 DDL 语句在源服务器上执行成功但在副本上会失败。Primary Keys 主键建议为创建的每个表定义一个主键。在选择主键列时应选择具有以下特征的列重要的查询语句使用的列。列不能为空。从不包含重复值的列。一旦插入后极少甚至从不更改值的列。例如在包含人员信息的表中你不会将主键设在(firstname, lastname)上因为可能有多个人员拥有相同的姓名姓名列可能留空且有时人们会更改姓名。面对如此多的限制条件通常没有明显的一组列适合作为主键因此你会创建一个带有数字 ID 的新列作为主键。最好的方式就是使用趋势递增的数字作为主键。你也可以 在InnoDB表中使用AUTO_INCREMENT的列来定义主键自动生成。AUTO_INCREMENT 实现原理是什么会锁全表码自增锁模式通过innodb_autoinc_lock_mode变量在启动时配置。自增主键锁“传统”锁模式innodb_autoinc_lock_mode 0“传统”锁模式所有“INSERT 类”语句在向具有AUTO_INCREMENT列的表中插入时都会获得一个特殊的表级AUTO-INC锁。此锁通常保持到语句的末尾而不是事务的末尾以确保在给定的INSERT语句序列中自动增量值按可预测和可重复的顺序分配并确保任何给定语句分配的自动增量值是连续的。“连续”锁模式innodb_autoinc_lock_mode 1“连续”锁模式“批量插入”使用特殊的AUTO-INC表级锁并保持到语句结束。这适用于所有INSERT ... SELECT、REPLACE ... SELECT和LOAD DATA语句。这种锁模式确保在存在INSERT语句且行数未知并且自增值在语句执行过程中分配的情况下任何“INSERT-类似”语句分配的所有自增值都是连续的并且操作对基于语句的复制是安全的。innodb_autoinc_lock_mode 2“交错”锁模式在这种锁模式中没有“INSERT-like”语句使用表级AUTO-INC锁并且多个语句可以同时执行。这是最快且最可扩展的锁模式但在使用基于语句的复制或从二进制日志重放 SQL 语句的恢复场景时是不安全的。在此锁定模式下自动增量值在整个并发执行的“INSERT-like”语句中保证是唯一的且单调递增。然而由于多个语句可以同时生成数字即数字的分配在语句之间交错进行任何给定语句插入的行生成的值可能不是连续的。Indexes索引InnoDB 的索引分为聚簇索引和二级索引Secondary Index均采用 B树结构聚簇索引也称 Clustered Index。是指关系表记录的物理顺序与索引的逻辑顺序相同。由于一张表只能按照一种物理顺序存放一张表最多也只能存在一个聚集索引。叶子节点直接存储行数据。二级索引也叫 Secondary Index。指的是非叶子节点按照索引的键值顺序存放叶子节点存放索引键值以及对应的主键键值。MySQL 里除了 INNODB 表主键外其他的都是二级索引。叶子节点存储主键值需通过主键回表查询数据。下图是一个聚集索引的 B Tree 图。1 个 B Tree Node占据一个页。在索引页页的主要记录部分(User Records)存放的Recordrecord headerindex keypage pointer。在数据页则是按表创建时的row_format类型存放完整数据行记录。 row_format 类型分别有Compact、Redundant、Compressed和Dynamic。因此在聚集索引中非叶子节点都为索引页叶子节点为数据页在辅助索引中非叶子节点和叶子节点都为索引页。不同的是叶子节点里记录的是聚集索引中的主键 ID 值。INNODB 表的二级索引如下图所示图片来自「一树一溪」注意在索引页的 Record 中的page pointer指向的是页而非具体的记录行。并且 Record 的index key为指向的 page records 的起始键值。如果主键较长二级索引会占用更多空间因此拥有较短的主键是有利的。在表空间文件的一个页的结构上内容布局为在聚集索引中数据页内除了按照主键大小进行记录存放以外在File header中有两个字段fil_page_prev和fil_page_next, 分别记录了上一页/下一页的偏移量offset用以实现数据页在 B Tree 叶子位置的双向链表结构。数据如何被查找检索呢通过 B Tree 结构可以明显看到通过 B Tree 查找可以定位到索引最后指向的数据页并不能找到具体的记录本身。这时数据库会将该页加载到内存中然后通过Page Directory进行二分查找。余彦瑭“索引使用单调递增和 UUID 有什么区别吗”这个问题问的好我们一定要杜绝使用 UUID 生成的数据作为索引。顺序主键如自增 ID插入时数据页填充率高减少页分裂。我们根据上文知道索引是有序排列的一个 Btree单调递增天然有序这样才能高效的使用索引查询数据。什么是覆盖索引优化-- 示例表结构 CREATETABLEusers ( idINT PRIMARY KEY, nameVARCHAR(50), age INT, INDEX idx_name_age (name, age) ); -- 覆盖索引查询 SELECTid, name, age FROMusersWHEREname Alice;原理查询字段全部包含在二级索引中时无需回表。执行计划Extra列显示Using index。余彦瑭“排序索引Sorted Indexes是什么”B 树有序性所有索引聚簇/二级均按索引键值排序存储支持高效范围查询和排序操作。页内排序单个数据页内的记录按主键顺序存储页之间通过双向链表连接。所以我们么可以使用索引看来优化排序查询。-- 利用索引排序 SELECT * FROM users ORDER BY id DESC LIMIT 10;避免 Filesort若ORDER BY子句与索引顺序一致执行计划显示Using index。Doublewrite Buffer 双写缓冲InnoDB 是 MySQL 中一种常用的事务性存储引擎它具有很多优秀的特性。其中Doublewrite Buffer 是 InnoDB 的一个重要特性之一。为什么需要 Doublewrite BufferInnoDB 页大小为 16KB而操作系统如 Linux页大小为 4KB单次写入需拆分 4 个 OS 页。可以使用如下命令查看 MySQL 的 Page 大小SHOW VARIABLES LIKE innodb_page_size;而 MySQL 程序是跑在 Linux 操作系统上的MySQL 中一页数据刷到磁盘要写 4 个文件系统里的页。需要注意的是这个操作并非原子操作比如我操作系统写到第二个页的时候Linux 机器断电了这时候就会出现问题了。造成”页数据损坏“。并且这种”页数据损坏“靠 redo 日志是无法修复的。Redo log 中记录的是对页的物理操作而不是页面的全量记录而如果发生 partial page write部分页写入问题时出现问题的是未修改过的数据此时重做日志(Redo Log)无能为力。Doublewrite Buffer 的出现就是为了解决上面的这种情况虽然名字带了 Buffer但实际上 Doublewrite Buffer 是内存磁盘的结构。Doublewrite Buffer 是一种特殊文件 flush 技术带给 InnoDB 存储引擎的是数据页的可靠性。它的作用是在把页写到磁盘数据文件之前InnoDB 先把它们写到一个叫 doublewrite buffer双写缓冲区的共享表空间内在写 doublewrite buffer 完成后InnoDB 才会把页写到数据文件的适当的位置。如果在写页的过程中发生意外崩溃InnoDB 在稍后的恢复过程中在 doublewrite buffer 中找到完好的 page 副本用于恢复。架构设计Doublewrite Buffer 采用内存磁盘双层结构关键组件如下内存结构容量固定为128 个页2MB每个页 16KB。数据页刷盘前通过memcpy拷贝至内存 Doublewrite Buffer。磁盘结构位于系统表空间ibdata分为2 个区extent1/extent2共 2MB。数据以顺序写方式写入避免随机 I/O 开销。工作流程如下图所示如上图所示当有数据修改且页数据要刷盘时第一步记录 Redo log。第二步脏页从 Buffer Pool 拷贝至内存中的 Doublewrite Buffer。第三步Doublewrite Buffer 的内存里的数据页会 fsync 刷到 Doublewrite Buffer 的磁盘上分两次写入磁盘共享表空间中(连续存储顺序写性能很高)每次写 1MB第四步Doublewrite Buffer 的内存里的数据页再刷到数据磁盘存储 .ibd 文件上离散写时序图如下崩溃恢复如果第三步前发生了崩溃可以通过第一步记录的 Redo log 来恢复。如果第三步完成后发生了崩溃 InnoDB 存储引擎可以从共享表空间中的 Double write 中找到该页的一个副本将其复制到独立表空间文件再应用 Redo log 恢复。在正常的情况下MySQL 写数据页时会写两遍到磁盘上第一遍是写到 doublewrite buffer第二遍是写到真正的数据文件中这就是“Doublewrite”的由来。Doublewrite Buffer 通过 两次写 机制在内存和磁盘间构建冗余副本成为 InnoDB 保障数据完整性的基石。其架构设计平衡了性能与可靠性尤其在高并发或异常宕机场景下表现突出。Redo Log 重做日志重新回顾下 MySQL InnoDB 的内存和磁盘架构设计图。我们的目光是关注点在于左侧内存架构的 Log Buffer 以及右侧磁盘架构的 Redo Log 文件。图 1余彦瑭Redo Log 有啥用呢姐姐你可知道在数据库系统中持久性Durability是事务 ACID 特性的核心要求之一。其核心问题是如何确保提交的事务在崩溃后不丢失直接修改磁盘数据页的随机 I/O 性能低下且无法保证崩溃瞬间数据的完整性。InnoDB 的解决方案是引入Redo Log重做日志通过顺序写日志 内存缓冲的组合设计实现高性能的持久化保障。余彦瑭“说说看 Redo Log 如何保证已提交的事务不丢失”当数据库意外崩溃时如何保证已提交事务不丢失InnoDB 通过WALWrite-Ahead Logging机制解决这一核心问题其实现依赖两大核心组件Log Buffer内存中的日志缓冲区Redo Log磁盘上的顺序写日志文件通过二者的协同InnoDB 在保证 ACID 持久性的同时将随机写转换为顺序写实现性能与可靠性的完美平衡。Log Buffer 是一个内存层的环形缓冲区。关键字段说明buf指向环形缓冲区的内存地址write_lsn原子变量实现多线程无锁写hdr_no块序号用于崩溃恢复时定位日志位置余彦瑭“李老师当事务生成 Redo 记录后关键步骤有哪些”当事务生成 Redo Record 后/* 源码路径storage/innobase/log/log0buf.cc */ void log_buffer_write(log_t log, byte* record, size_t len) { // 1. 获取互斥锁短时锁 mutex_enter(log.mutex); // 2. 分配连续空间跨块处理 lsn_t start_lsn log.assign_lsn(len); // 3. 复制日志到缓冲区 memcpy(log.buf write_offset, record, len); // 4. 无锁更新 write_lsn log.update_write_lsn(start_lsn len); // 5. 唤醒刷盘线程 os_event_set(log.flusher_event); }WAL 机制全流程如下图所示Undo Logs 撤销日志MySQL InnoDB 引擎的事务隔离性由锁来实现。原子性、一致性、持久性通过数据库的 redo log 和 undo log 来完成。余彦瑭“Undo Log 的本质作用是什么”Undo Log 是 InnoDB 实现事务原子性Atomicity和多版本并发控制MVCC的核心组件主要解决两大关键问题事务回滚允许事务失败时恢复到修改前的状态原子性读一致性提供非锁定读取Non-Locking Read的历史版本MVCC与 Redo Log 形成鲜明对比特性Redo LogUndo Log目的保证持久性保证原子性和隔离性写入方向顺序写随机写回滚段中存储内容物理日志页修改逻辑日志行修改前的值生命周期事务提交后保留到检查点事务提交后保留到无读视图引用清理机制Checkpoint 截断Purge 线程异步清理当事务修改数据时 Undo Log 如何生成呢关键代码逻辑row_upd_rec_in_place函数/* 存储位置storage/innobase/row/row0upd.cc */ void row_upd_rec_in_place(...) { // 1. 创建 Undo Log Record trx_undo_report_row_operation(...); // 2. 写入回滚段 trx_undo_report_update_impl(...); // 3. 设置行回滚指针 row_upd_rec_set_roll_ptr(...); }时序图如下所示余彦瑭过期 Undo Log 该如何处理呢姐姐问得好Purge 线程负责清理已提交事务的过期 Undo Log。就这样InnoDB 通过三大日志机制构建完整事务系统设计哲学启示分层解耦Redo 处理物理持久化Undo 处理逻辑回滚Binlog 处理逻辑复制空间换时间Undo 保留历史版本换取无锁读能力延迟处理艺术Purge 机制避免事务提交时的同步清理开销好了今天就到这。